如果你学习了MySQL进阶部分，那你就学习了MySQL进阶部分，恭喜你！

2023-12-21 09:37:21

视图(View)

视图是一种虚拟存在的表，也由行和列组成，但视图并不实际存在于数据库中。行和列的数据来自于定义视图的查询中所使用的表，并且还是在使用视图时动态生成的。相当于临时对象，不占用内存，但一直存在着，便于更加高效地查找数据。

  • 优点
    • 简化查询
      • 视图可以从原有的表上选取对用户有用的信息，那些对用户没用，或者用户没有权限了解的信息，都可以直接屏蔽掉，作用类似于筛选。
      • 也就是说，既可以为用户授予权限，也可以直接让用户使用他们需要的信息，让用户去对视图进行查询似乎也是个不错的选择
      • 而且*一直存在，可以重复利用
    • **减小数据库改动的影响
      • 原来的表变了，我们创建视图的时候是基于对原表的查询，所以我们只需要将原表的变动的地方通过改变视图的query代码，适配修改后的原表（虽然是q视图，但视图也是以原表为基础的），比如原表的列名已经从A变成到了B，在视图中原来为SELECT A FROM invoices 发现这表里…已经没有A这个列名了，只有修改之后的B，所以我们重写SELECT B AS A FROM invoices 就把原表B字段的内容q到，并且改名为A，这样之后所有关于视图的q语句中的SELECT A 就不用修改为SELECT B了
      • 视图相当于接口，为数据库表提供了一种抽象化，我们原表发生了改变，为了能够顺利q，我们要改变接口，让我们能q到原本的数据，如果我们所有的q都是关于视图的，那么我们的q不会受基础表改动的影响，只需改改视图的代码即可，无需改变之前关于视图的q代码
    • 限制基础表访问，加强数据安全性
  • 创建视图
    • CREATE OR REPLACE VIEW 视图名 AS SELECT...如有重名的视图（也就是重复创建），就用新视图replace旧视图。后面的SELECT就是普通的创建表的方法
  • 删除视图
    • DROP VIEW 视图名
    • 视图如果需要保存的话，建议保存到sql文件中。
  • 可更新视图++UPDATABLE VIEWS++
    • 没有DISTINCT、聚合函数、GROUP BY、HAVING、UNION的视图叫做可更新视图
    • 我们可以INSERT、UPDATE、DELETE可更新视图。如
      DELETE FROM clients_balance(视图名) WHERE invoice_id=1
      UPDATE ...（视图） SET …=…（字段）
      `` WHERE …（筛选）
      ++别忘记DELETE依旧是删除符合条件的（视图的）记录！++
  • WITH OPTION CHECK：防止行消失
    • 我们UPDATE、DELETE某一行的数据后，有可能会不符合视图的查询要求，此时该行就不会被查询返回，效果就是被删除了。我们不想在不被通知的情况下就没了一行，所以在创建视图的最后一行加入WITH OPTION CHECK子句。该子句的作用是：如果我们的行被删除，那么会报错提醒我们，而不会去运行删行

存储过程(Procedure)

1.我们不会在应用程序中写SQL，它会让应用代码混乱并且难以维护，并且需要修改应用代码以满足编译条件。::md，怎么我现在还是只会myBatis啊，还是要学更多的东西啊！::所以我们应该将SQL代码从应用代码里剥离出来，在*存储过程(Stored procedure) *或 *函数(function)*中写

2.存储过程是一个包含一堆SQL代码的数据库对象，在应用程序中我们调用的就是数据库对象来存储和管理SQL代码。就是数据库语言层面的代码封装与重用。但其实存储过程也好函数也好，都不是mapper代理开发，我目前还不知道前二者该如何使用

  • 创建存储过程

    • 首先我们要明确存储过程一般由多个sql语句组成，所以为了让这些代码块整合成一个整体，我们需要用DELIMITER $$重新设置分隔符（默认是;），*使创建存储过程这一大段代码成为一条语句。
    • CREATE PROCEDURE get_invoices_with_balance()创建存储过程的开头，之后（）中会有参数的出现++存储过程的名字基本是小写字母+_组成++
    • BEGIN 代码块 END $$BEGIN和END关键字之间的内容称为存储过程的主体body，代码块中每条语句都要用;结尾，这是mysql规定的要求，而$$之前的代码同属一个分割区域。
    • 最后，因为我们默认的分隔符是; 为了让后面的语句正确，我们要重新设置恢复原来的默认分隔符DELIMITER ;
      DELIMITER $$ CREATE PROCEDURE get_invoices_with_balance()
      BEGIN SELECT *
      FROM invoices WHERE (invoice_total-payment_total)>0;
      END $$ DELIMITER ;

  • 其他方面：

    • 用CALL get_invoices_with_balance()调用过程，但是mysql规定该过程空格内必须要填充内容，起码要填写NULL，这里是实参位置。
    • 创建存储过程的简单方法，点Stored Procedure,Create…那里直接有模板，就是稍微繁琐了一点。直接写主体，右下角Apply是应用，revert是撤销alter
    • 删除存储过程：DROP PROCEDURE IF EXISTS get_invoices_with_balance()加上IF EXISTS是为了防止重复删除，重复删除会导致错误，通常与CREATE连用放在首行。可以连续创建procedure并且不会重复创建和删除

  • 参数

    • 创建语句：CREATE PROCEDURE get_clients_by_state(p_state CHAR(2))，在空格内设置形参p_state，后面是形参的类型char，长度为2的字符。我们通常使用VARCHAR可变字符串，但是如果知道参数的长度固定，建议直接如上述代码声明好固定长度，这样占用的空间会更少。::与之前学的不同，mysql是先写形参，再写类型，其余的与我们学的类似。多个参数之间逗号隔开，在代码块中也可以任意使用形参::

  • 带有默认值的参数

    • 在mysql中，参数的默认值是NULL。但它没法像C++一样直接赋值，因为mysql必须要有实参。所以，设置带默认值的参数就相当于设置NULL时执行的存储过程
    • 用条件语句IF p_state IS NULL THEN +SET p_state='CA'+END IF;作为一条语句。
    • 如果默认值要求返回所有数据的话，可以用*IFNULL(a,b)*语句。如果a是空值，则返回b值。代码如下：
      BEGIN SELECT * FROM clients c
      WHERE c.state = IFNULL(p_state,c.state); END $$
      ++这样就实现了如果参数为空则满足WHERE永真，就返回表中所有的数据++

  • 参数验证

    • 不需要太多，从用户端接受信息时再应用较多的验证。这个验证就是说明一个条件，当参数满足这个条件时(一般是错误的)，我们就用SIGNAL语句标志或者引发错误。结构是SIGNAL SQLSTATE ''引号里面是一个包含错误代码的字符串常值，在网上可以查到很多类型。
    • 最后设置错误信息SET MESSAGE_TEXT = 'Invalid amount';总体代码如下：
      BEGIN IF amount <= 0 THEN
      SIGNAL SQLSTATE '22003' SET MESSAGE_TEXT = ‘Invalid amount’;
      END IF; ... END $$

  • 参数分类

    • 输入、输出、输入输出参数
    • 默认状况下存储过程的所有系数都是输入参数，用于给过程传递值。在参数前加OUT前缀使其变成输出参数，就可以从过程中获取到这些值。
    • 输出参数在存储过程的赋值：SELECT a,b INTO A,B FROM...将读取到的a,b分别赋值给A，B两个输出参数。实际调用过程是：
      • 先将A,B初始化为0SET @A=0;SET @B=0;这里@A表示A是一个用户自定义变量。
      • 再调用存储过程CALL XXX(3,@A,@B);需要传递这些变量
      • 最后SELECT @A,@B;得到查询后的表
        %% 也就是说，输入参数起到局部变量的作用，只是经过赋值后应用于存储过程。输出参数起到（部分）全局变量的作用，它可以承载着存储过程中产生的任意值。

  • 变量

    • 用户变量
      • 以“@”开始，形式为"@变量名"。::mysql中用户变量不用事前申明，在用的时候直接用“@变量名”使用::
      • 通常用于有输出参数的存储过程，在整个客户会话过程中被保存，在客户与MySQL断线之后，这些变量又被清空。用户变量和mysql客户端是绑定的，只对当前用户使用的用户端生效。当前会话过程就是数据库连接过程
      • 第一种用法：set @num=1; 或set @num:=1;
      • 第二种用法：select @num:=1; 或 select @num:=字段名 from 表名 where ……常用于输出用户变量。使用set时可以用“=”或“：=”，但是使用select时必须用“：=赋值
    • 系统变量：分为全局变量与会话变量
    • 全局变量(Global)
      • 定义时，以如下两种形式出现，SET GLOBAL 变量名= 或者 SET @@GLOBAL.变量名=
      • 全局变量影响服务器的整体操作，对所有客户端生效，只有具有super权限才可以设置全局变量
      • 全局变量在MYSQL服务器启动时由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。全局变量作用于server的整个生命周期，但是不能跨重启，即重启后所有设置的全局变量均失效，需要更改相应的配置文件使其继续生效。
      • 输出所有全局变量：SHOW GLOBAL VARIABLES
    • 会话变量：(Session/Local)
      • 定义时，以如下两种形式出现，SET SESSION 变量名= 或者 SET @@SESSION.变量名=或者SET 变量名=如果变量之前什么符号都没有，则默认为session会话变量，其中session可以由local替代。
      • 服务器为每个连接的客户端维护一系列会话变量。每次建立一个新的连接时，MySQL使用相应全局变量的当前值对客户端的会话变量进行初始化，::这一部分就是早就定义好的会话变量，如果客户再定义一个会话变量，那么也需要和用户变量一样需要手动初始化，与用户变量没有什么差别::也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。
      • 但修改设置会话变量不需要特殊权限，客户端只能更改自己的会话变量，不能更改其他客户端的会话变量会话变量的作用域与用户变量一样，仅限于当前连接。当前连接断开后，其设置的所有会话变量均失效。
      • 所以，在我眼中，会话变量赋予了全局变量在客户端层面多样性的实现，全局变量一改则所有客户端都改，但会话变量只是对当前客户端连接过程的修改。
    • 本地/局部变量：可以在存储过程或函数内begin到end语句块之间定义，随着执行任务完成，本地变量也会消亡。通常用于计算任务，如
      CREATE PROCEDURE get_risk_factor () BEGIN
      DECLARE risk_factor DECIMAL(9,2)DEFAULT 0; DECLARE invoices_total DECIMAL(9,2);
      DECLARE invoices_count INT; //声明本地变量
      SELECT COUNT（*），SUM(invoice_total) INTO invoices_count,invoices_total
      FROM invoices; //本地变量赋值
      SET risk_factor=invoices_total/invoices_count*5; //通过本地变量计算
      SELECT risk_factor; END

定义变量的两种方式：
1.set/select…into：对局部变量赋值，对其他变量申明+赋值
2.DECLARE var INT DEFAULT 0declare语句专门用于定义局部变量，主要用于存储过程

  • 函数
    • 相比与存储过程，函数只能返回单一值，不能返回带有行列的结果集。
    • 先创建CREATE FUNCTION risk(id INT)，再确认返回值类型，RETURNS INT(可以是任意数据类型)，这是函数和存储过程创建的主要区别。
    • 在RETURN函数之后，设置函数属性。每个MySQL函数都必须至少有一个属性。
      • DETERMINISTIC：确定性。与数据库中的具体数值变化无关，表示函数具有确定的变量之间的关系。比如涉及到基于业务规则的数学公式的计算，特定的输入对象对应着特定不变的输出对象。但是，涉及到客户或者其他变动的因素，其函数的返回值如果会因为未来的不确定因素改变，则函数为不确定性函数。
      • READS SQL DATA：读取SQL数据。函数中会配置选择语句用于读取一些数据。
      • MODIFIES SQL DATA：修改数据。函数中有插入、更新或者删除数据的操作。
    • 在END之前我们永远需要返回某个值，RETURN IFNULL(risk_factor,0)
    • 在建立自定义函数之后，我们可以像用内置函数一样使用自定义函数。也可以用DROP FUCTION IF EXISTS risk;删除函数，只写名字就好
      CREATE FUNCTION get_risk_factor_for_client (
      client id INT )
      RETURNS INTEGER //确认返回值类型 READS SQL DATA //读取sql数据
      BEGIN risk=…
      RETURN risk; END

触发器(Trigger)

触发器是在插入、更新、删除语句前后自动执行的一堆SQL代码，通常用于保持数据的一致性

  • 创建触发器

    • CREATE TRIGGER payments_after_insert，起名的原则最好是 表名-时序-操作。
    • AFTER INSERT ON payments：声明时序、操作以及触发器作用的表。时序也可以为BEFORE，操作也可以为DELETE``UPDATE
    • FOR EACH ROW：表示该触发器会作用于每一个受影响的行
    • 关键字NEW/OLD:NEW.字段名-会返回我们刚刚插入的行的字段对应的值，用于插入后的触发器。OLD.字段名-用于更新或删除行时返回之前的行的字段对应的值。
      ++注意，触发器不能修改自身所在的表，否则在修改该表时触发器生效又继续修改该表，该表的修改又会使用触发器，会无限循环下去++
      DELIMITER $$ CREATE TRIGGER payments_after_insert
      AFTER INSERT ON payments FOR EACH ROW
      BEGIN UPDATE invoices //any table except payments
      SET payment_total = payment_total + NEW.amount //NEW.amount：刚刚在payment中插入行的amount字段
      WHERE invoice_id = NEW.invoice_id; //别忘了id对应，否则全部的值都会被触发器更新
      END S$ DELIMITER ;

  • 查看触发器：SHOW TRIGGERS查看当前数据库中的所有触发器，还可以在其基础上设置查询特定格式的触发器，如SHOW TRIGGERS LIKE 'payments%'以payments开头的触发器。

  • 删除触发器：DROP TRIGGER IF EXISTS payments_after_insert注意删除触发器使用的是DROP

  • 触发器另一个作用：审计，我们可以添加一个audit审计表，在触发器中将操作的数据、时间等信息插入到审计表中，自动记录用户的操作。

事件(Events)

事件是根据计划(schedule)执行的任务或一堆代码，可以只执行一次，也可以按照某种规律执行，通过事件，我们可以自动化数据库维护工作任务，比如定时清理，定时更新数据、生成汇总报告

  • 打开MySQL事件调度器
    • 事件调度器是定时触发执行的，也可以看成是“临时触发器”。注意，事件是一些任务或者代码，其本身只能在事件调度器启动后才能被定时触发，事件是由一个特定的线程来管理的，这个线程就是事件调度器
    • 通过动态设定全局变量event_scheduler的值即可动态地控制事件调度器的启用。即先SHOW VARIABLES LIKE 'events%'; 找到事件调度器对应的变量，再SET GLOBAL event_scheduler = ON或者1 ;但是，该全局变量如果都设置为ON，那么会造成系统资源的浪费，在此我建议只更改会话变量，在之后的大型库中再对全局变量进行修改。
  • 创建事件
    • CREATE EVENT yearly_delete_stale_audit_rows名称如此，每年删除过期审计行
    • 执行一次ON SCHEDULE AT '2022-07-25'或者定期执行ON SCHEDULE EVERY 1 YEAR STARTS '2022-07-25' ENDS '2022-08-26'“1 YEAR”可以改为任何时间段，STARTS/ENDS都是可选择的
    • DO BEGIN...END$$这个DO很容易忘
  • 其他
    • 查找事件：SHOW EVENTS LIKE 'yearly%'
    • 删除事件：DROP EVENT IF EXISTS ...该页面进阶部分的删除全部是DROP，DELETE只用于删除表中的记录
    • 修改事件：ALTER EVENT...
      • 第一种用法：和CREATE EVENT结构一样
      • 第二种用法：暂时启用或者禁用一个事件，如ALTER EVENT a DISABLE/ENABLE

事务(Transaction)

%% 事务是代表单个工作单元的一组SQL语句，这些语句作为一个单元一起成功或失败。

ACID:1.Atomicity原子性：不可被分割
2.Consistency一致性：通过使用事务，数据库将始终保持一致的状态,其执行的结果将使数据库从一种一致性状态变迁到另一种一致性状态，数据库的完整性没有被破坏。
3.Isolation隔离性：多事务互相隔离，并且有锁，一次最多只有一个事务可以更新目标行，不能多事务同时更新一个行
4.Durability持久性：一旦事务被提交，其产生的作用是永久性的，如果出现停电等特殊情况，事务的作用仍然保持

  • 创建事务
    • START TRANSACTION;申明接下来的语句都属于同一事务
    • COMMIT;提交事务：当MySQL看到commit时，它就会把所有的更改都写入数据库，如果有语句更改失败，MySQL会自动撤销之前的更改，此时称作事务被退回了。
    • ROLLBACK;回滚事务：手动退回事务以便于检查错误，将commit换成rollback
    • 单行逐步执行：上方工具栏Query中的Execute Current Statement想要执行哪一步就把光标移到哪里，在执行上面的语句，应该可以检查一些debug

并发(Concurrency)和锁定(Locking)

并发：多个用户同时访问相同数据的情况，在一个用户修改其他用户正在检索或修改的数据时，并发往往会出现问题

默认状态下的锁定
  • 对于同一个数据，当我们执行第一个事务更新该数据时，MySQL在我们更新的数据上设置了一个锁。之后的事务想要更改该数据必须等到第一个事务完成（要么提交要么退回）
  • 所以说一个事务试图修改一行或者多行，它就会给这些行上锁，防止其他事务修改这些行，默认情况可以解决大部分问题
常见的并发问题
  • 丢失更新(Lost Updates)
    • 当两个事务同时进行时，默认状态下的第一个事务就不复存在，就不会有默认锁出现。
    • 没有锁时，较晚提交的事务会覆盖较早事务做的更改，所以较早事务做的更新就会自动丢失
    • 所以要设置锁！
  • 脏读(Dirty Reads):
    • ::读取未提交数据::
    • A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚(https://so.csdn.net/so/search?q=%E5%9B%9E%E6%BB%9A&spm=1001.2101.3001.7020)操作，那么A事务读取到的数据就是脏数据。
    • ::就是说，在b更改这个数据之后，a读到了更改后的数据，但是后来b因为某些原因发生事务的回滚，之前的操作全部撤销，这时候a原先读取的数据就是脏数据::
  • 不可重复读(Non-repeating Reads)/不一致读
    • ::前后多次读取，数据内容不一致::
    • 也就是说，在事务A多次读取之间，事务B修改了其中的数据，让事务A前后读取的数据不重复了，MySQL规定不能读取到不重复的数据
    • 不可重复读侧重于修改数据，数据本身发生改变
  • 幻读(Phantom Reads)phantom:ghost
    • ::前后多次读取，数据总量不一致
    • 事务A在执行读取操作，需要两次统计数据的总量，前一次查询数据总量后，此时事务B执行了新增数据的操作并提交后，这个时候事务A读取的数据总量和之前统计的不一样，就像产生了幻觉一样，平白无故的多了几条数据，成为幻读。
    • 幻读侧重于新增或删除，是数据的总量发生改变
    • 有时候把幻读作为不可重复读，把插入和删除作为更新的一部分

事务的隔离级别

%% 读取未提交、读取已提交、可重复读（默认）、可串行化（可序化） %%
更低的隔离级别更容易并发，会有更多用户同时接触到同一数据，另一方面，低级的隔离换来高效的性能，因为我们需要添加的锁少了 %% 更高的隔离级别降低了性能以及可扩展性，在存储和CPU方面都需要额外的资源，给服务器加重负担

  • 查看隔离级别：SHOW VARIABLES LIKE 'transaction_isolation';
  • 更改隔离级别：SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;，该语句不涉及变量，所以只为下一个事务设置隔离级别，如果是该会话或连接SET后就要加SESSION而不能省略，如果是全局设置级别就要在SET后面加GLOBAL
  • 应用程序连接到数据库中的顺序：连接MySQL——更改隔离级别——执行事务——断开连接，确保其他事务不会受到影响
Mysql两种事务提交方式：
  • 自动提交（默认）
    • MySQL 在自动提交模式下，每个 SQL 语句都是一个独立的事务，所有的命令都会被自动COMMIT给数据库并使之永久化。执行一个语句之后，MySQL立刻将其存储到磁盘中。如果想明确地执行事务，需要禁用自动提交模式并告诉MySQL你想让它在何时提交或回滚有关的修改。
    • autocommit=1，默认被设置为ON的状态，阻止在此事务范围内回滚
  • 手动提交
    • set autocommit = 0;只对当前的mysql命令行窗口有效，必须使用commit/rollback
    • 当MySQL开启事务处理， 即执行了START TRANSACTION或BEGIN命令（-删除后回滚）后，所有的 DML（INSERT | UPDATE | DELETE） 语句都需显示执行COMMIT命令(autocommit=OFF情况下)。其他的诸如 DDL（CREATE | DROP | ALTER）语句的，都是隐式提交的
读取未提交(Read uncommitted)
  • 最快的隔离级别，不设置任何锁，并且忽略其他事务设置的锁。
  • 适用于批量的粗略报告或者数据很少更新的情况
  • 允许读取尚未提交的数据变更,可能会导致脏读、幻读或不可重复读
读取已提交(Read committed)
  • 允许读取并发事务已经提交的数据,可以阻止脏读,但是幻读或不可重复读仍有可能发生。
可重复读(Repeatable read)
  • 对同一字段的多次读取结果都是一致的,除非数据是被本身事务自己所修改,可以阻止脏读和不可重复读,但幻读仍有可能发生
可串行化、可序化(Serializable)
  • 所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰,也就是说,该级别可以防止脏读、不可重复读以及幻读
  • 进行可序化事务时，如果有其他事务正在修改，那么该事务会自动等待其他所有事务执行完毕后再执行
  • 同样的，如果用户数量增多，请求越多，所耗费的时间也约久，只有在防止幻读的时候才建议使用可串行化
死锁(Deadlocks)
  • 无论是什么隔离级别，在我们用事务更新某记录时，这个事务将锁定此纪录，保证其他事务无法同时更新此纪录
  • 但是如果遇到作用相似但语句顺序相反的事务，事务1的语句1执行会限制事务2的语句2，事务2的语句1执行会限制事务1的语句2，这时两个事务无法继续执行，报错，死锁产生
  • 如何解决？
    • 如果因为死锁事务被退回，设置可以重新恢复，或者提示用户“操作失败，请重试”
    • 尽可能减小死锁发生：
      • 更新多条记录时遵循相同的顺序
      • 简化事务，缩小事务运行时长，安排非高峰段执行大型事务，避免与较多活跃用户产生干扰

数据类型

字符串类型char/varchar
  • char：存储固定长度的字符串
  • varchar
    • 存储可变长度字符串
    • 使用时最好划分一下范围，比如短字符串统一用varchar(50)，更长的字符串用varchar(255)，这样可以简化数据库的维护
    • varchar最大长度为65535字符(2^15-1)，64KB
  • 文本：
    • 中文本串MEDIUMTEXT，最多存储1600万个字符，16MB，有利于存储JSON对象、SCV字符串和短中篇书。
    • 长文本串LONGTEXT，4GB
    • 微文本类型TINYTEXT，255bytes
    • 文本类型TEXT，64KB，和varchar是一样大的，但是建议使用varchar类型，因为varchar可以被编入索引，索引可以为查询提速
  • 其他
    • 如编程，字符串可以存储数值，常用于存储邮政编码和电话号码这些比较长且不涉及数学运算的数字
    • 英文字母占用1字节，欧洲和中东语言占用2字节，亚洲语言占用3字节，所以，MySQL在不确定语言的情况下，会为char提供最大字节数。如char(10)，MySQL会为那一列留出30个字节。
整数类型(Integer Types)
  • TINYINT微整型：只占用一个字节 1b ，[-128,127]
  • UNSIGNED TINYINT无符号微整型：[0,255]，常用于存储小正数，也可以列标记为无符号，防止负数被意外存储进数据库
  • SMALLINT小整型，占用两个字节 2b ，[-32k,32k]
  • MEDIUMINT中整型，占用三个字节，[-8M,8M]
  • INT整型，占用四个字节，[-2B,2B]
  • BIGINT大整数，占用8个字节，[-9Z,9Z]
    ++范围大致了解，不会的随时上网查MySQL整数类型表++

数值类型的另一个属性：补零ZEROFILL，相当于两步，设置宽度+补零
如INT(4)=>0001总是会有四位，便于排版对齐

有理数(Rational)
  • DECIMAL(p,s)/DEC/NUMERIC/FIXED小数型：用于储存定点数类型，小数点后的位数固定，比如货币值。需要提供2个参数——精度（最大位数）和小数位数。
  • DOUBLE 8b/FLOAT 4b:被用于科学计算，这些类型并不存储准确值，而是取近似值，所以可以存储非常大或非常小的数字
布尔类型(Boolean Types)

BOOL/BOOLEAN：true/false，一样的

枚举和集合类型(Enum and Set Types)
  • Enum枚举
    • 将某列的值限制在某些字符串范围中，比如“规格”列中我们只能填写“大”、“中”、“小”三个值。通常添加于某列：用设计模式打开产品表，添加列，类型改为ENUM('small','medium','large')，之后只能写这三个值(不论大小写)
    • 尽量避免使用，更改过于麻烦，应用范围也很狭窄。更好的办法是建立单独的一张规格参考表，id对应规格大小，需要的话直接select搜就行了。称这种表为查询表(look-up table)
  • Set集合无非就是在枚举的基础上允许存储多个值，也不建议使用，后续会有方法讲解。
日期和时间类型(Date and Time Types)
  • DATE用于存储一个没有时间成分的日期
  • TIME存储时间值
  • TIMESTAMP时间戳：记录某行插入或最近更新的时间但是只有4b，只能存储到2038年以前的日期，被称为“2038年问题”
  • DATETIME日期时间型，也可以作为时间戳，8b可以存储到2038年之后
  • YEAR四位数年份
二进制长对象类型(Blob Types)
  • 存储大型二进制数据，如图像、视频、PDF、word文档
  • TINYBLOB：255b二进制数据
  • BLOB：65KB
  • MEDIUMBLOB：16MB
  • LONGBLOB：4GB
  • 一般来说，最好不要把文件存在数据库当中，因为RDBMS是为了处理结构化关系型数据而设计的，而不是二进制数据（文件）如果我们存储文件，数据库大小会迅速增加、弱化数据备份功能、出现性能问题、在数据库读取存储图像还必须额外写代码
JSON文档

JSON(JavaScript Object Notation,JS对象简谱)是一种通过网络存储和数据传输的轻量级数据交换格式，它在网络和移动应用中被大量使用。在大多数情况下，移动应用程序通过JSON将数据发送到后端。JSON相当于简单的、完全独立于编程语言的数据库
++有关详细的JSON我们之后再学哦！已经涉及到前后端和JS了++

  • JSON的组成：

    • 由一对大括号组成，大括号里可以有一个或者多个键值对。键是字符串，带上英文双引号。
    • 数组用*方括号[ ]*表示
    • 值的形式有字符串、数值、JSON对象、数组、true、false、null七种类型，不存在函数、日期、undefined等其余形式
    • 如果产品各有一些独特的属性，那么添加多个列之后，会有很多列与产品没什么关系，所以用JSON表示“属性”列，存储每个产品的多个键值对。

  • 方法一：标准JSON创建
    UPDATE products SET properties = ’ //MySQL设置JSON对象需要再加’’
    { “dimensions”:[1,2,3],//数组
    "weight":10, “manufacturer”:{ “name”:“sony” }//嵌套对象
    } ’
    `` WHERE product_id = 1;

  • 方法二：关于JSON的函数
    UPDATE products SET properties = JSON_OBJECT(
    'weight',10, ‘dimensions’,JSON_ARRAY(1,2,3),//数组
    'manufacturer',JSON_OBJECT('name','sony') )
    `` WHERE product_id = 1;
    ++应用了MySQL内部函数，所以不需要遵顼什么"":等，直接用英文单引号就行，（MySQL中单引号双引号等效）一个键、一个值依次交替++

  • 查找JSON某一键值对的值

    • 那肯定是根据键查找了！我们用SELECT JSON_EXTRACT(properties,'$.weight')FROM products，该函数第一个参数就是一个JSON对象，第二个参数是一条路径，用字符串表示。$表示当前的JSON文档 .访问单独的属性或者键。
    • 列路径运算符->：
      • 正常：SELECT properties ->'$.weight'
      • 访问数组的话和编程一样：properties->'$.dimensions[0]'，0表示第一个数
      • 访问嵌套数组properties->'$.manufacturer.name'用点运算符访问嵌套对象的单个属性，但是如果此时的值是字符串，那么返回的结果为字符串如"sony"，如果我们只想要其中的内容就要用->>去掉双引号。

  • 更改或添加JSON键值对的值：
    UPDATE products SET properties = JSON_SET(
    properties, ‘ . w e i g h t ′ , 20 , ‘ ‘ ′ .weight',20, `` ' .weight′,20,‘‘′.age’,10
    `` )
    %% 该函数第一个参数是要修改的JSON对象，然后键值对依次排列。注意，我们可以任意添加新的键值对如上’$.age’

  • 删除JSON键值对：
    UPDATE products SET properties = JSON_REMOVE(
    properties, ‘ . w e i g h t ′ , ‘ ‘ ′ .weight', `` ' .weight′,‘‘′.age’
    `` )

这两个函数，都是拾取一个JSON对象，修改并返回一个新的JSON对象

文章来源:https://blog.csdn.net/qq_65052774/article/details/135051279
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。