MySQL5.7

xiaohudou
274 阅读14分钟

1 基本信息

1.4 新特性

  • SQL mode changes. 默认会设置为STRICT_TRANS_TABLES，NO_ENGINE_SUBSTITUTION删除了几个，其中NO_ENGINE_SUBSTITUTION表示如果新建表的引擎不存在，则提示报错 ，如果不设置NO_ENGINE_SUBSTITUTION，指定表的引擎不存在时默认使用InnoDB；STRICT_TRANS_TABLES表示批量 Insert是如果第一条失败则都失败。
  • Online ALTER TABLE. 不需要再通过复制表就可以直接修改索引名称。
  • JSON support. 支持JSON类型，
  • 空间类型
  • 移除YEAR(2)

7 备份与恢复

7.1 备份与恢复类型

11 Data Types

包含：

  • 数值类型
  • 时间日期类型
  • 字符类型，String，Char，Byte
  • 空间类型
  • JSON类型

11.1 数据类型概述

11.1.1 数值类型

对于整数，M表示显示的最大宽度；
对于浮点与定点数，M表示可以存储的总位数；
如果对表字段设定ZEROFILL，该字段默认加上无符号类型属性；
默认为有符号类型；
序列号SERIAL：无符号，Not NULL，自增，BIGINT，UNIQUE

Warning
2个数字做减法，其中有一个为无符号类型，则结果也为无符号类型。但设置 NO_UNSIGNED_SUBTRACTION （SQL mode）时，结果为有符号类型。

  • BIT [(M))]
    BIT类型，M表示位数，默认为1，M范围为[1,64]
  • TINYINT [(M)] [UNSIGNED] [ZEROFILL]
    小整数（8bit），有符号范围为[-128, 127]，无符号范围为[0,255]
  • BOLL BOOLEAN
    TINYINT(1)的同义词，值为0表示false，非0表示true
mysql> SELECT IF(0, 'true', 'false');
+------------------------+
| IF(0, 'true', 'false') |
+------------------------+
| false                  |
+------------------------+

mysql> SELECT IF(1, 'true', 'false');
+------------------------+
| IF(1, 'true', 'false') |
+------------------------+
| true                   |
+------------------------+

mysql> SELECT IF(2, 'true', 'false');
+------------------------+
| IF(2, 'true', 'false') |
+------------------------+
| true                   |
+------------------------+

然而，TRUE与FALSE仅仅分表表示1与0，实例如下：

mysql> SELECT IF(0 = FALSE, 'true', 'false');
+--------------------------------+
| IF(0 = FALSE, 'true', 'false') |
+--------------------------------+
| true                           |
+--------------------------------+

mysql> SELECT IF(1 = TRUE, 'true', 'false');
+-------------------------------+
| IF(1 = TRUE, 'true', 'false') |
+-------------------------------+
| true                          |
+-------------------------------+

mysql> SELECT IF(2 = TRUE, 'true', 'false');
+-------------------------------+
| IF(2 = TRUE, 'true', 'false') |
+-------------------------------+
| false                         |
+-------------------------------+

mysql> SELECT IF(2 = FALSE, 'true', 'false');
+--------------------------------+
| IF(2 = FALSE, 'true', 'false') |
+--------------------------------+
| false                          |
+--------------------------------+

最后2段示例结果是因为2既不等于0也不等于1，也间接说明TRUE，FALSE的值为1，0。

  • SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
    小整数（16bit），有符号范围为[-32768,327670]，无符号范围为[0,65535]

  • MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
    中等大小整数（24bit），有符号范围为[-8388608,8388607]，无符号范围为[0,16777215]

  • INT[(M)] [UNSIGNED] [ZEROFILL]
    整数（32bit），有符号范围为[-2147483648,2147483647]，无符号范围为[0,4294967295]

  • INTEGER[(M)] [UNSIGNED] [ZEROFILL]
    INT的同义类型

  • BIGINT[(M)] [UNSIGNED] [ZEROFILL]
    大整数（64bit），有符号范围为[-9223372036854775808,9223372036854775807]，无符号范围为[0,18446744073709551615]
    SERIAL：BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
    关于BITINT字段你应该注意如下事项：

    • 所有算术运算都是使用有符号的BITINT或者DOUBLE，所以不应该使用超过9223372036854775807 (63 bits)的无符号BITINT,BIT运算可以使用64bit；另外BITINT转换为DOUBLE出现舍入可能导致结果错误。
      在以下情况下，MYSQL可以处理BITINT：
      • 存储无符号整数数值
      • MIN(col_name)或者MAX(col_name)
      • 使用操作符(+,-,*等)，2个操作数都为整数
    • 可以存储一个字符串（能转换为整数）到BITINT，MYSQL会执行字符串到整形的转换。
    • 运算溢出，如果对2个很大的数进行相乘，可能获取到非预期的值。

  • DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
    定点数，十进制数。M为总位数（精度），D为小数点后位数（复数），M不包含小数点与符号位（-）。如果D为0，则没有小数点或小数部分。十进制数的最大位数（M）为65，D支持的最大值为30，如果省略D，则默认为0，如果省略M，则默认为10。
    设定为无符号，则不允许负数。
    针对十进制字段的所有基本操作（+，-，*，/）以65位精度来完成。

  • FLOAT[(M,D)] [UNSIGNED：] [ZEROFILL]
    单精度浮点数。值范围-3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. 这是理论限值，实际范围依赖于CPU硬件与操作系统。
    FLOAT(M,D)是非标准的MYSQL扩展。
    如果指定了UNSIGNE，则不允许负数。
    M表示总位数，D表示小数点后位数。如果省略M与D,则可存储的大小依赖于硬件。单精度浮点数精确到约7位小数。D，则不允许负数。
    使用FLOAT时，因为MYSQL中以DOUBLE精度来运算，所以可能会出现一些非预期的问题。

  • DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
    双精度浮点数。范围为：-1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308.这是理论限值，实际范围依赖于CPU硬件与操作系统。
    M表示总位数，D表示小数点后位数。如果省略M与D,则可存储的大小依赖于硬件。单精度浮点数精确到约15位小数。
    DOUBLE(M,D)是非标准的MYSQL扩展。
    如果指定了UNSIGNE，则不允许负数。

11.1.2 时间日期类型

时间类型概述。关于时间类型的属性与存储要求的更新信息，请参见11.3节 “Date and Times Types”，11.8节“Date Type Storage Requirements”；关于时间的运算操作，见12.7节“Date and Times Functions”。
对于DATE与DATETIME范围描述中,“supported”意味着即使早期版本的值可能有效，但没有保证。
MYSQL允许TIME，DATETIME，TIMESTAMP的秒带有小时部分，即微秒（6位）精度，通过type_name(fsp)来定义微妙，例如：

create table t1 (t TIME(3), dt datatime(6));

mysql> select * from t1;
+--------------+----------------------------+
| t            | dt                         |
+--------------+----------------------------+
| 11:38:59.000 | 2019-08-03 11:39:05.000000 |
+--------------+----------------------------+
1 row in set (0.00 sec)

fsp值范围为[0,6]，0表示没有微秒，如果忽略，默认为0。（与MYSQL标准不同的是，标准默认是6，这是为了兼容早期版本）
任何TIMESTAMP或者DATETIME表字段都有自动初始化与更新属性。

  • DATE
    范围为‘1000-01-01’到‘9999-12-31’。MYSQL以‘YYYY-MM-DD’显示DATE字段，但允许使用字符串或者数值来赋值。
  • DATETIME[(fsp)]
    范围为‘1000-01-01 00:00:00.000000’到‘9999-12-31 23:59:59.999999’。MYSQL以‘YYYY-MM-DD hh:mm:ss[.fsp]’显示DATETIME字段，但允许使用字符串或者数值来赋值。 通过设置DEFAULT与ON UPDATE属性可以自动初始化以及自动更新为当前时间，将在11.35节“Automatic Initialization and Updating For TIMESTAMP and DATETIME”中描述。
  • TIMESTAMP[(fsp)]
    时间戳。范围为：'1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC。TIMESTAMP存储的是从('1970-01-01 00:00:00' UTC)开始的秒数。TIMESTAMP不能表示'1970-01-01 00:00:00'，因为该值等于0（从起始时间计算的秒数），而0被保留用来表示'0000-00-00 00:00:00'。
    （TODO 还有小部分未翻译）
  • TIME[(fsp)]
    范围：'-838:59:59.000000' to '838:59:59.000000'。MYSQL以‘hh:mm:ss[.fraction]’显示TIME字段，但允许使用字符串或者数值来赋值。
    选项fsp范围为[0,6]用来指定微秒部分，如果不指定默认为0。
  • YEAR[(4)]
    范围：1901到2155, and 0000。MYSQL以‘YYYY’显示YEAR字段，但允许使用字符串或者数值来赋值。

时间类型不能使用SUM(),AVG()聚合函数，因为转换为数值的时候遇到第一个非数字字符后丢失后面的所有内容。为了规避这个问题，先转换为数字，然后执行聚合函数，然后再转换回时间类型。

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name;
SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;

11.1.3 字符类型

  • [NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
    一个固定长度的字符串，在存储时总是用空格填充到指定的长度。m以字符表示列长度。m的范围是0到255。如果省略m，则长度为1。

    检索char值时会删除尾随空格，除非启用pad-char-to-full-length SQL模式。

  • [NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]
    可变长度字符串。m以字符表示最大列长度。m的范围是0到65535。使用1-2个字节来存储长度字段，如果不超过255长度，则一个字符，如果超过这2个字节。

    MySQL遵循标准的SQL规范，不会从varchar值中删除尾随空格。

11.2 数字类型

11.2.1 整数类型
11.2.2 定点数类型（DECIMAL, NUMERIC）

小数和数字类型存储精确的数字数据值。当需要保持精确的精度时，例如货币数据时，可以使用这些类型。在MySQL中，numeric被实现为decimal，因此下面关于decimal的注释同样适用于numeric。
MySQL以二进制格式存储十进制值。见第12.22节“Precision Math”。
在十进制列声明中，可以（通常）指定精度和小数位数。例如：

salary DECIMAL(5,2)

在本例中，5表示精度，2表示刻度。精度表示为值存储的有效位数，小数位数表示小数点后可以存储的位数。
标准SQL要求decimal（5,2）能够存储5位和2位小数的任何值，因此可以存储在salary列中的值范围为-999.99到999.99。

11.6 JSON

14 InnoDB 存储引擎

14.6 InnoDB 磁盘数据结构

14.6.1 表格
14.6.1.1 TODO
14.6.1.2 TODO
14.6.1.3 TODO
14.6.1.4 InnoDB中的自动增量处理
InnoDB自动增量锁模式

Insert的集中方式：

  • “INSERT-like” 语句，所有INSERT语句，包含：INSERT, INSERT ... SELECT, REPLACE, REPLACE ... SELECT, and LOAD DATA. Includes “simple-inserts”, “bulk-inserts”, and “mixed-mode” .
  • Simple inserts 可确定插入行数数量的，插入单行或多行，包含 INSERT and REPLACE，但不包含INSERT ... ON DUPLICATE KEY UPDATE.
  • Bulk inserts 不确定插入行数的批量添加。包含INSERT ... SELECT, REPLACE ... SELECT, LOAD DATA语句。
  • Mixed-mode inserts 批量插入中有部分是自增，有部分是指定的，比如：
    INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
    另外也包含INSERT ... ON DUPLICATE KEY UPDATE，因为在更新阶段可能会使用或不使用自动增量列的分配值。

（之所以先列出上面4中Insert方式，是因为不同锁模式对不同Insert方式影响不一样s）

参数innodb_autoinc_lock_mode设置自动增量锁模式，0：传统；1：连续；2：交错。

  • innodb_autoinc_lock_mode = 0 (“traditional” lock mode)
    针对上面4中Insert方式，都是基于表级别的AUTO-INC锁，语句执行完就释放锁，不用等事务完成时才释放锁。但锁表性能相对较差，好处是分配的ID连续，确保以可预测和可重复的顺序为给定的插入语句序列分配自动增量值，并确保由任何给定语句分配的自动增量值连续的。

    基于语句的复制的情况下，这意味着在从属服务器上复制SQL语句时，自动递增列的值与主服务器上的值相同。多个insert语句的执行结果是确定性的，从系统复制与主系统相同的数据。如果多个insert语句生成的自动增量值是交错的，那么两个并发insert语句的结果将是不确定的，并且不能使用基于语句的复制可靠地传播到从属服务器。

  • innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)
    这是默认锁定模式。
    对于“bulk inserts”，使用表级别的AUTO-INC锁，一次只能执行一个保存auto-inc锁的语句。

    对于“Simple inserts”，使用互斥量（一个轻量级锁mutex），分配过程中持有锁并分配预先需要的数量，分配后释放而不用等到整条语句执行完成，这有助于提高性能。如果另一个事务持有auto-inc锁，“简单插入”将等待auto-inc锁。

    这种锁定模式确保，在不预先知道行数的INSERT语句（以及在语句进行时分配自动递增数的情况下）的情况下，由任何“insert like”语句分配的所有自动递增值都是连续的，可以保证基于语句的主从复制是安全的。

    简单地说，这种锁定模式显著提高了可伸缩性，同时可以安全地用于基于语句的复制。此外，与“传统”锁模式一样，由任何给定语句分配的自动增量数是连续的。对于任何使用自动增量的语句，与“传统”模式相比，语义没有任何变化，但有一个重要的例外。

    “mixed-mode inserts”是例外情况。用户为多行“简单插入”中的某些（而不是全部）行的自动增量列提供显式值。对于此类插入，InnoDB分配的自动增量值比要插入的行数更多。但是，自动分配的所有值都是连续生成（因此高于）最近执行的前一条语句生成的自动增量值。“超额”数字丢失。

  • innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)
    在这种锁模式下，不使用表级auto-inc锁而是都是要信号量，多个语句可以同时执行。这是最快和最可扩展的锁模式，但在使用基于语句的复制或恢复方案时，如果从二进制日志重放SQL语句，则不安全。

    在这种锁定模式下，自动增量值保证在所有并发执行的“insert-like”语句中都是唯一的且单调地递增的。但是，由于多个语句可以同时生成数字（也就是说，数字的分配是跨语句交错的），因此为任何给定语句插入的行生成的值可能不是连续的。

    如果唯一执行的语句是“简单插入”，其中要插入的行数是提前知道的，那么为单个语句生成的数字中没有间隙，除了“混合模式插入”。但是，当执行“大容量插入”时，任何给定语句分配的自动增量值中可能存在间隙。

14.6.1.5 TODO
14.6.1.6 TODO
14.6.2 索引
14.6.2.1 聚集索引与辅助索引

每个innodb表都有一个称为聚集索引的特殊索引，用于按索引大小顺序存储行的数据。

  • 如果有主键，则为聚集索引
  • 如果没有主键，找第一个UNIQUE索引列作为聚集索引
  • 如果也没有UNIQUE索引，则使用隐藏的ROW_ID列为聚集索引

14.7 Innodb 锁与事务模型

14.7.1 锁
  • Shared and Exclusive Locks
  • Intention Locks
  • Record Locks
  • Gap Locks
  • Next-Key Locks
  • Insert Intention Locks
  • AUTO-INC Locks
  • Predicate Locks for Spatial Indexes
Shared and Exclusive Locks （共享锁与排他锁）

Innodb实现了标准的行锁，共享锁（S）与排他锁（X）

  • 共享锁（S）允许持有锁的事务读取行。
  • 排他锁（X）允许持有锁的事务更新或删除行。

如果事务T1在R行上持有共享锁，那么来自某些不同事务T2的请求对R行进行如下处理：

  • 读请求:t2可以立即获取到S锁。结果，T1和T2都在R上保持S锁。
  • 写请求:t2不能获取到X锁。

如果事务T1在R行上持有独占（X）锁，则不能立即授予来自某个不同事务T2的请求R上任何一种类型的锁的权限。相反，事务t2必须等待事务t1释放对行r的锁。

Intention Locks (意向锁)

InnoDB 支持粒度锁即允许行锁与表锁共存。例如LOCK TABLES ... WRITE语句在指定的表上设置表级别的独占锁。为了实现多粒度锁，InnoDB使用intention locks。Intention locks是表级别的锁，指示稍后需要对表中的行使用哪种类型的锁（X或者S），有2种意向锁：

  • intention shared lock (IS)表示事务打算在指定行上设置共享锁。
  • intention exclusive lock (IX)表示事务打算在指定的行上设置独占锁。
avatar
文章
阅读
粉丝