MySQL DataBase Data Types

MySQL数据类型概览

经常使用的MyQL数据类型注意事项

Posted by qin4zhang on June 30, 2019

注意

想法及时记录，实现可以待做。

待定大纲：

通过阅读大牛的博客、文章，或者查看相关的数据结构，总结归纳MySQL的使用数据结构，以Innodb存储引擎为准

MySQL的数据类型

本文记录MySQL的数据类型，更多用法，可以参考MySQL文档

数值类型

数值类型的语法

对于整型，M 表示最大的显示宽度，最大值为255。 对于浮点型和固定浮点型，M 是可以存储的总位数。 如果你给数值类型的字段指定了ZEROFILL,MySQL 自动将 UNSIGNED 属性添加到列中。 允许UNSIGNED属性的数值数据类型也允许SIGNED。但是，这些数据类型默认情况下是SIGNED的，因此SIGNED属性无效。

  • BIT[(M)] 位值类型。 M表示每个值的位数，从1到64。如果省略M，则默认值为1。

  • TINYINT[(M)] [UNSIGNED] [ZEROFILL] 一个非常小的整数。有符号范围是-128至127。无符号范围是0至255。

  • BOOL, BOOLEAN 这些类型是TINYINT（1）的同义词。零值被认为是错误的。非零值视为正确：

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既不等于1也不等于0。

  • SMALLINT[(M)] [UNSIGNED] [ZEROFILL] 一个小整数。有符号范围是-32768到32767。无符号范围是0到65535

  • MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] 一个中等大小的整数，有符号的范围是-8388608到8388607，无符号的范围是0到16777215

  • INT[(M)] [UNSIGNED] [ZEROFILL] 普通大小的整数。有符号范围是-2147483648至2147483647。无符号范围是0至4294967295。

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

  • BIGINT[(M)] [UNSIGNED] [ZEROFILL] 一个大整数。有符号范围是-9223372036854775808至9223372036854775807。无符号范围是0至18446744073709551615。

  • DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL] 打包的“精确”定点数。 M是位数的总和（精度），D是小数点后的位数（小数位数）。 小数点和（对于负数）-号不计入M。如果D为0，则值没有小数点或小数部分。 DECIMAL的最大位数（M）为65。支持的小数位数（D）的最大值为30。如果省略D，则默认值为0。如果省略M，则默认值为10。

  • FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] 一个小的（单精度）浮点数。 允许值为-3.402823466E + 38至-1.175494351E-38、0和1.175494351E-38至3.402823466E + 38。 这些是基于IEEE标准的理论限制。 实际范围可能会略小，具体取决于您的硬件或操作系统。

M是总位数，D是小数点后的位数。 如果省略M和D，则将值存储到硬件允许的极限。 单精度浮点数的精度约为小数点后7位。

  • DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] 普通大小（双精度）浮点数。 允许值为-1.7976931348623157E + 308至-2.2250738585072014E-308、0和2.2250738585072014E-308至1.7976931348623157E + 308。 这些是基于IEEE标准的理论限制。 实际范围可能会略小，具体取决于您的硬件或操作系统。

M是总位数，D是小数点后的位数。 如果省略M和D，则将值存储到硬件允许的极限。 双精度浮点数精确到大约15个小数位。

整型的精确值 - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT

MySQL支持SQL标准整数类型INTEGER（或INT）和SMALLINT。 作为对该标准的扩展，MySQL还支持整数类型TINYINT，MEDIUMINT和BIGINT。 下表显示了每种整数类型所需的存储空间和范围。

MySQL支持的整数类型的必需存储和范围

类型 存储（Bytes） 有符号最小值 无符号最小值 有符号最大值 无符号最大值
TINYINT 1 -128 0 127 255
SMALLINT 2 -32768 0 32767 65535
MEDIUMINT 3 -8388608 0 8388607 16777215
INT 4 -2147483648 0 2147483647 4294967295
BIGINT 8 -2^{63} 0 2^{63}-1 2^{64}-1

定点类型（精确值）-DECIMAL，NUMERIC

DECIMAL和NUMERIC类型存储精确的数值数据值。 当保留精确度很重要时，例如使用货币数据，则使用这些类型。 在MySQL中，NUMERIC被实现为DECIMAL，因此以下有关DECIMAL的说明同样适用于NUMERIC。

MySQL以二进制格式存储DECIMAL值。

在DECIMAL列声明中，可以（通常是）指定精度和小数位数。 例如：

salary DECIMAL(5,2)

在此示例中，5是精度，2是小数位。精度表示值存储的有效位数，小数位数表示小数点后可以存储的位数。

标准SQL要求DECIMAL（5,2）能够存储任何具有五位数字和两位小数的值，因此可以存储在薪水列中的值的范围是-999.99到999.99。

在标准SQL中，语法DECIMAL（M）等效于DECIMAL（M，0）。 类似地，语法DECIMAL等效于DECIMAL（M，0），其中允许实现决定M的值。MySQL支持DECIMAL语法的这两种变体形式。 M的默认值为10。

如果小数位数为0，则DECIMAL值不包含小数点或小数部分。

DECIMAL的最大位数为65，但是给定DECIMAL列的实际范围可能受给定列的精度或小数位数的限制。 当为这样的列分配的值在小数点后的位数比指定刻度允许的位数多时，该值将转换为该刻度。 （精确的行为是特定于操作系统的，但是通常效果是将其截断为允许的位数。）

浮点类型（近似值）-FLOAT，DOUBLE

FLOAT和DOUBLE类型表示近似数字数据值。 MySQL将四个字节用于单精度值，并将八个字节用于双精度值。

对于FLOAT，SQL标准允许在括号中的关键字FLOAT后面的位中指定精度（而不是指数的范围）的可选规范； ; 即FLOAT（p）。 MySQL还支持此可选的精度规范，但是FLOAT（p）中的精度值仅用于确定存储大小。 从0到23的精度导致4字节单精度FLOAT列。 从24到53的精度将导致8字节的双精度DOUBLE列。

MySQL允许使用非标准语法：FLOAT（M，D）或REAL（M，D）或DOUBLE PRECISION（M，D）。 在此，（M，D）表示总共可以存储多达M位的值，其中D位可以在小数点后。 例如，显示为FLOAT（7,4）的列显示为-999.9999。 MySQL在存储值时执行四舍五入，因此，如果将999.00009插入FLOAT（7,4）列，则近似结果为999.0001。

位值类型-BIT

BIT数据类型用于存储位值。 BIT（M）类型可以存储M位的值。 M的范围是1到64。

要指定位值，可以使用b’value’表示法。 值是使用零和一写入的二进制值。 例如，b’111’和b’10000000’分别代表7和128。

如果您为BIT（M）列分配的值小于M位长，则该值将在左侧填充零。例如，将值b’101’分配给BIT（6）列实际上与分配b’000101’相同。

数值类型属性

MySQL支持扩展，可以选择在整数类型的基本关键字之后的括号中指定整数数据类型的显示宽度。 例如，INT（4）指定显示宽度为四位数的INT。 应用程序可以使用此可选的显示宽度来显示整数值，该整数值的宽度小于为列指定的宽度，方法是用空格左键填充它们。 （也就是说，此宽度存在于结果集返回的元数据中。是否使用该宽度取决于应用程序。）

显示宽度不限制可以存储在列中的值的范围。 也不会阻止宽于列显示宽度的值正确显示。 例如，指定为SMALLINT（3）的列通常具有-32768到32767的SMALLINT范围，并且使用三位数以上的数字完整显示三位数所允许的范围之外的值。

与可选的（非标准）ZEROFILL属性结合使用时，默认的空格填充将替换为零。例如，对于声明为INT（4）ZEROFILL的列，将值5检索为0005。

注意

对于表达式或UNION查询中涉及的列，将忽略ZEROFILL属性。

如果将大于显示宽度的值存储在具有ZEROFILL属性的整数列中，则当MySQL为某些复杂的联接生成临时表时，您可能会遇到问题。 在这些情况下，MySQL假定数据值适合列显示宽度。

所有整数类型都可以具有可选的（非标准）UNSIGNED属性。 无符号类型可用于仅允许一列中使用非负数，或者在您需要该列的较大的较高数字范围时使用。 例如，如果INT列为UNSIGNED，则该列的范围大小相同，但其端点从-2147483648和2147483647向上移动到0和4294967295。

时间日期类型

用于表示时间值的日期和时间数据类型为DATE，TIME，DATETIME，TIMESTAMP和YEAR。 每个时间类型都有一个有效值范围，以及一个“零”值，当您指定MySQL无法代表的无效值时可以使用该值。

日期和时间数据类型语法

用于表示时间值的日期和时间数据类型为DATE，TIME，DATETIME，TIMESTAMP和YEAR。

对于DATE和DATETIME范围说明，“受支持”表示尽管较早的值可能有效，但不能保证。

  • DATE 一个日期。支持的范围是“ 1000-01-01”至“ 9999-12-31”。 MySQL以“ YYYY-MM-DD”格式显示DATE值，但允许使用字符串或数字将值分配给DATE列。

  • DATETIME[(fsp)] 日期和时间组合。 支持的范围是“ 1000-01-01 00：00：00.000000”到“ 9999-12-31 23：59：59.999999”。 MySQL以’YYYY-MM-DD hh：mm：ss [.fraction]’格式显示DATETIME值，但允许使用字符串或数字将值分配给DATETIME列。

可以给出介于0到6之间的可选fsp值，以指定小数秒精度。值为0表示没有小数部分。如果省略，则默认精度为0。

  • TIMESTAMP[(fsp)] 时间戳。 范围是’1970-01-01 00：00：01.000000’UTC到’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’， “零” TIMESTAMP值。

  • TIME[(fsp)] 时间。范围是“ -838：59：59.000000”至“ 838：59：59.000000”。 MySQL以’hh：mm：ss [.fraction]’格式显示TIME值，但允许使用字符串或数字将值分配给TIME列。

  • YEAR[(4)] 4位数字格式的年份。 MySQL以YYYY格式显示YEAR值，但允许使用字符串或数字将值分配给YEAR列。值显示为1901至2155或0000。

DATE，DATETIME和TIMESTAMP类型

DATE，DATETIME和TIMESTAMP类型相关。本节描述了它们的特征，它们如何相似以及如何不同。

DATE类型用于具有日期部分但没有时间部分的值。 MySQL检索并以“ YYYY-MM-DD”格式显示DATE值。支持的范围是“ 1000-01-01”至“ 9999-12-31”。

DATETIME类型用于包含日期和时间部分的值。 MySQL检索并以’YYYY-MM-DD hh：mm：ss’格式显示DATETIME值。支持的范围是“ 1000-01-01 00:00:00”到“ 9999-12-31 23:59:59”。

TIMESTAMP数据类型用于包含日期和时间部分的值。 TIMESTAMP的UTC范围为’1970-01-01 00:00:01’至UTC’2038-01-19 03:14:07’。

DATETIME或TIMESTAMP值可以包含尾随的小数秒部分，精度最高为微秒（6位数）。 特别是，存储在DATETIME或TIMESTAMP列中的值中的任何小数部分都会存储而不是丢弃。 包括小数部分在内，这些值的格式为’YYYY-MM-DD hh：mm：ss [.fraction]’，DATETIME值的范围为’1000-01-01 00：00：00.000000’至’9999 -12-31 23：59：59.999999”，并且TIMESTAMP值的范围为“ 1970-01-01 00：00：01.000000”到“ 2038-01-19 03：14：07.999999”。 小数部分应始终与其余时间用小数点分隔； 没有其他小数秒定界符被识别。

TIME类型

MySQL检索并以’hh：mm：ss’格式显示TIME值（或对于大型小时值显示为’hhh：mm：ss’格式）。 TIME值的范围可能是’-838：59：59’到’838：59：59’。 小时部分可能是如此之大，因为“时间”类型不仅可以用来表示一天中的某个时间（必须少于24小时），而且可以用来表示经过的时间或两个事件之间的时间间隔（可能远大于 24小时，甚至是负面的）。

年份类型

YEAR类型是一个1字节类型，用于表示年份值。可以将其声明为YEAR，其隐式显示宽度为4个字符，或者等效为YEAR（4），具有显式的显示宽度。

MySQL以YYYY格式显示YEAR值，范围为1901至2155，以及0000。

字符串数据类型

字符串数据类型为CHAR，VARCHAR，BINARY，VARBINARY，BLOB，TEXT，ENUM和SET。

字符串数据类型语法

字符串数据类型为CHAR，VARCHAR，BINARY，VARBINARY，BLOB，TEXT，ENUM和SET。

在某些情况下，MySQL可能会将字符串列更改为与CREATE TABLE或ALTER TABLE语句中给定的类型不同的类型。

对于字符串列（CHAR，VARCHAR和TEXT类型）的定义，MySQL以字符单位解释长度规范。 对于二进制字符串列（BINARY，VARBINARY和BLOB类型）的定义，MySQL以字节为单位解释长度规范。

CHAR和VARCHAR类型

CHAR和VARCHAR类型相似，但存储和检索方式不同。它们的最大长度以及是否保留尾随空格也不同。

声明的CHAR和VARCHAR类型的长度表示您要存储的最大字符数。例如，CHAR（30）最多可容纳30个字符。

CHAR列的长度固定为创建表时声明的长度。 长度可以是0到255之间的任何值。存储CHAR值时，将在它们的右边填充空格以达到指定的长度。 检索CHAR值时，除非启用了PAD_CHAR_TO_FULL_LENGTH SQL模式，否则将删除尾部空格。

VARCHAR列中的值是可变长度的字符串。 长度可以指定为0到65535之间的值。 VARCHAR的有效最大长度取决于最大行大小（65535字节，在所有列之间共享）和所使用的字符集。

与CHAR相比，VARCHAR值存储为1字节或2字节长的前缀以及数据。 长度前缀指示值中的字节数。 如果值要求不超过255个字节，则一列使用一个长度字节；如果值可能需要不超过255个字节，则一列使用两个长度字节。

如果未启用严格的SQL模式，并且您为CHAR或VARCHAR列分配的值超过了该列的最大长度，则该值将被截断以适合并生成警告。 对于非空格字符的截断，可以通过使用严格的SQL模式导致发生错误（而不是警告）并抑制该值的插入。

对于VARCHAR列，无论使用哪种SQL模式，插入前都会截断超出列长度的尾随空格，并生成警告。 对于CHAR列，无论SQL模式如何，都将以静默方式执行从插入值中截断多余尾随空格的操作。

存储VARCHAR值时不会对其进行填充。 根据标准SQL，在存储和检索值时保留尾随空格。

下表通过显示将各种字符串值存储到CHAR（4）和VARCHAR（4）列中的结果来说明CHAR和VARCHAR之间的区别（假设该列使用单字节字符集，如latin1）：

值 CHAR(4) 存储要求 VARCHAR(4) 存储要求
’’ ’ ‘ 4 bytes ’’ 1 byte
‘ab’ ‘ab ‘ 4 bytes ‘ab’ 3 bytes
‘abcd’ ‘abcd’ 4 bytes ‘abcd’ 5 bytes
‘abcdefgh’ ‘abcd’ 4 bytes ‘abcd’ 5 bytes

显示在表的最后一行中的值仅在不使用严格的SQL模式时才适用。如果启用了严格模式，则不会存储超过列长度的值，并且会导致错误。

InnoDB将长度大于或等于768字节的固定长度字段编码为可变长度字段，可以将其存储在页面外。 例如，如果字符集的最大字节长度大于3（与utf8mb4相同），则CHAR（255）列可以超过768个字节。

如果给定值存储在CHAR（4）和VARCHAR（4）列中，则从这些列检索的值并不总是相同的，因为检索时会从CHAR列中删除尾随空格。 以下示例说明了这种差异：

mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO vc VALUES ('ab  ', 'ab  ');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;
+---------------------+---------------------+
| CONCAT('(', v, ')') | CONCAT('(', c, ')') |
+---------------------+---------------------+
| (ab  )              | (ab)                |
+---------------------+---------------------+
1 row in set (0.06 sec)

CHAR，VARCHAR和TEXT列中的值根据分配给该列的字符集排序规则进行排序和比较。

所有MySQL归类均为PAD SPACE类型。 这意味着将比较所有CHAR，VARCHAR和TEXT值，而不考虑任何尾随空格。 在这种情况下，“比较”不包括LIKE模式匹配运算符，对于后者，尾随空格很重要。 例如：

mysql> CREATE TABLE names (myname CHAR(10));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO names VALUES ('Jones');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT myname = 'Jones', myname = 'Jones  ' FROM names;
+------------------+--------------------+
| myname = 'Jones' | myname = 'Jones  ' |
+------------------+--------------------+
|                1 |                  1 |
+------------------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT myname LIKE 'Jones', myname LIKE 'Jones  ' FROM names;
+---------------------+-----------------------+
| myname LIKE 'Jones' | myname LIKE 'Jones  ' |
+---------------------+-----------------------+
|                   1 |                     0 |
+---------------------+-----------------------+
1 row in set (0.00 sec)

这不受服务器SQL模式的影响。

对于那些删除尾部填充字符或比较忽略它们的情况，如果一列具有要求唯一值的索引，则将仅尾部填充字符数不同的值插入列中会导致重复键错误。 例如，如果表包含’a’，则尝试存储’a ‘会导致重复键错误。

BINARY和VARBINARY类型

BINARY和VARBINARY类型类似于CHAR和VARCHAR，不同之处在于它们存储二进制字符串而不是非二进制字符串。 即，它们存储字节字符串而不是字符串。 这意味着它们具有二进制字符集和排序规则，并且比较和排序基于值中字节的数字值。

BINARY和VARBINARY的最大允许长度与CHAR和VARCHAR相同，不同之处在于BINARY和VARBINARY的长度以字节而不是字符为单位。

BINARY和VARBINARY数据类型不同于CHAR BINARY和VARCHAR BINARY数据类型。 对于后一种类型，BINARY属性不会导致该列被视为二进制字符串列。 而是使用列字符集（如果未指定列字符集，则使用表默认字符集）的二进制（_bin）排序规则，并且列本身存储非二进制字符串，而不是二进制字节字符串。 例如，如果默认字符集为latin1，则将CHAR（5）BINARY视为CHAR（5）CHARACTER SET latin1 COLLATE latin1_bin。 这与BINARY（5）不同，后者存储具有二进制字符集和排序规则的5字节二进制字符串。 有关二进制字符集的二进制排序规则与非二进制字符集的_bin排序规则之间的区别的信息

BLOB 和 TEXT 类型

BLOB是一个二进制大对象，可以容纳可变数量的数据。 四种BLOB类型是TINYBLOB，BLOB，MEDIUMBLOB和LONGBLOB。 这些仅在它们可以容纳的值的最大长度上有所不同。 四种TEXT类型是TINYTEXT，TEXT，MEDIUMTEXT和LONGTEXT。 这些对应于四种BLOB类型，并且具有相同的最大长度和存储要求。

BLOB值被视为二进制字符串（字节字符串）。 它们具有二进制字符集和排序规则，并且比较和排序基于列值中字节的数字值。 TEXT值被视为非二进制字符串（字符字符串）。 它们具有二进制以外的字符集，并且根据字符集的排序对值进行排序和比较。

如果未启用严格的SQL模式，并且您为BLOB或TEXT列分配的值超过了该列的最大长度，则该值将被截断以适合并生成警告。 对于非空格字符的截断，可以通过使用严格的SQL模式引起错误（而不是警告）并抑制插入值。

无论SQL模式如何，从要插入TEXT列的值截断多余的尾随空格总是会产生警告。

对于TEXT和BLOB列，插入时没有填充，选择时也不会删除字节。

如果对TEXT列建立索引，则索引条目比较将在末尾加空格。 这意味着，如果索引要求唯一值，则仅尾随空格数量不同的值将发生重复键错误。 例如，如果表包含’a’，则尝试存储’a’会导致重复键错误。 对于BLOB列，情况并非如此。

在大多数方面，您可以将BLOB列视为VARBINARY列，该列可以根据需要任意大。同样，您可以将TEXT列视为VARCHAR列。 BLOB和TEXT在以下方面不同于VARBINARY和VARCHAR：

  • 对于 BLOB 和 TEXT 列上的索引，必须指定索引前缀长度。对于 CHAR 和 VARCHAR，前缀长度是可选的。
  • BLOB和TEXT列不能具有默认值。

ENUM 类型

ENUM是一个字符串对象，其值是从允许值的列表中选择的，这些值在表创建时在列规范中明确枚举。

ENUM类型具有以下优点：

  • 在列的一组可能值有限的情况下，压缩数据存储。您指定为输入值的字符串会自动编码为数字。
  • 可读的查询和输出。这些数字将转换回查询结果中的相应字符串。

创建和使用ENUM列

枚举值必须是带引号的字符串文字。例如，您可以创建一个带有ENUM列的表，如下所示：

CREATE TABLE shirts (
    name VARCHAR(40),
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),
  ('polo shirt','small');
SELECT name, size FROM shirts WHERE size = 'medium';
+---------+--------+
| name    | size   |
+---------+--------+
| t-shirt | medium |
+---------+--------+
UPDATE shirts SET size = 'small' WHERE size = 'large';
COMMIT;

在此表中插入100万行且值为“ medium”将需要100万字节的存储空间，而如果将实际字符串“ medium”存储在VARCHAR列中则需要600万字节。

枚举文字的索引值

每个枚举值都有一个索引：

  • 列规范中列出的元素分配有索引号，从1开始。
  • 空字符串错误值的索引值为0。这意味着您可以使用以下SELECT语句查找分配了无效ENUM值的行：
mysql> SELECT * FROM tbl_name WHERE enum_col=0;
  • NULL值的索引为NULL。
  • 术语“索引”在这里是指枚举值列表中的位置。它与表索引无关。

例如，指定为ENUM（’Mercury’，’Venus’，’Earth’）的列可以具有此处显示的任何值。还显示每个值的索引。

值 索引
NULL NULL
’’ 0
‘Mercury’ 1
‘Venus’ 2
‘Earth’ 3

一个ENUM列最多可以包含65,535个不同的元素。 （实际限制是小于3000。）在一个表的ENUM和SET列中，一个表最多可以包含255个唯一元素列表定义。

如果在数字上下文中检索ENUM值，则返回列值的索引。例如，您可以像这样从ENUM列中检索数值：

mysql> SELECT enum_col+0 FROM tbl_name;

期望数值参数的函数，例如SUM（）或AVG（），可在必要时将其强制转换为数字。对于ENUM值，在计算中使用索引号。

枚举文字的处理

创建表时，将从表定义中的ENUM成员值中自动删除尾随空格。

检索时，将使用列定义中使用的字母大小写显示存储在ENUM列中的值。 请注意，可以为ENUM列分配一个字符集和排序规则。 对于二进制或区分大小写的归类，在为列分配值时考虑字母大小写。

如果将数字存储到ENUM列中，则该数字将视为可能值的索引，并且存储的值是具有该索引的枚举成员。 （但是，这不适用于将所有输入视为字符串的LOAD DATA。）如果用数字引号，但如果枚举值列表中没有匹配的字符串，则仍将其解释为索引。 由于这些原因，不建议使用枚举值定义为数字的ENUM列，因为这很容易造成混淆。 例如，以下列具有枚举成员，其字符串值分别为“ 0”，“ 1”和“ 2”，但数字索引值为1、2和3：

numbers ENUM('0','1','2')

如果存储2，则将其解释为索引值，并变为’1’（带有索引2的值）。 如果存储“ 2”，则它与枚举值匹配，因此将其存储为“ 2”。 如果存储“ 3”，则它与任何枚举值都不匹配，因此将其视为索引并变为“ 2”（索引为3的值）。

mysql> INSERT INTO t (numbers) VALUES(2),('2'),('3');
mysql> SELECT * FROM t;
+---------+
| numbers |
+---------+
| 1       |
| 2       |
| 2       |
+---------+

要确定ENUM列的所有可能值，请使用SHOW COLUMNS FROM tbl_name LIKE’enum_col’并在输出的Type列中解析ENUM定义。

空或NULL枚举值

在某些情况下，枚举值也可以是空字符串（’‘）或NULL：

  • 如果将无效值插入到ENUM中（即，在允许值列表中不存在的字符串），则会插入空字符串，作为特殊错误值。 此字符串可以通过将数字值设为0来与“正常”空字符串区分开。有关枚举值的数字索引的详细信息，请参见枚举文字的索引值。
  • 如果声明一个ENUM列允许NULL，则NULL值是该列的有效值，默认值为NULL。如果将ENUM列声明为NOT NULL，则其默认值是允许值列表的第一个元素。

SET类型

SET是一个字符串对象，可以具有零个或多个值，每个值都必须从创建表时指定的允许值列表中进行选择。 由多个set成员组成的SET列值用用逗号（，）分隔的成员指定。 这样的结果是SET成员值本身不应包含逗号。

For example, a column specified as SET(‘one’, ‘two’) NOT NULL can have any of these values:

''
'one'
'two'
'one,two'

SET列最多可包含64个不同的成员。在一个表的ENUM和SET列中，一个表最多可以包含255个唯一元素列表定义。

定义中的重复值会导致警告，如果启用了严格的SQL模式，则会导致错误。

创建表时，会从表定义中的SET成员值中自动删除尾随空格。

检索时，将使用列定义中使用的字母大小写来显示存储在SET列中的值。 请注意，可以为SET列分配字符集和排序规则。 对于二进制或区分大小写的归类，在为列分配值时考虑字母大小写。

MySQL以数字方式存储SET值，而存储值的低位对应于第一个set成员。 如果在数字上下文中检索SET值，则检索到的值具有与组成列值的set成员相对应的位set。 例如，您可以像这样从SET列中检索数值：

mysql> SELECT set_col+0 FROM tbl_name;

如果将数字存储到SET列中，则以该数字的二进制表示形式设置的位将确定列值中的set成员。 对于指定为SET（’a’，’b’，’c’，’d’）的列，成员具有以下十进制和二进制值。

SET成员 十进制值 二进制值
‘a’ 1 0001
‘b’ 2 0010
‘c’ 4 0100
‘d’ 8 1000

如果为该列分配的值9为二进制二进制数1001，那么将选择第一个和第四个SET值成员’a’和’d’，结果值为’a，d’。

对于包含多个SET元素的值，插入该值时这些元素以什么顺序列出都无所谓。 给定元素在值中列出多少次也无关紧要。 以后检索该值时，该值中的每个元素都会出现一次，并按照在创建表时指定它们的顺序列出这些元素。 假设将一列指定为SET（’a’，’b’，’c’，’d’）：

mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));

如果插入值’a，d’，’d，a’，’a，d，d’，’a，d，a’和’d，a，d’：

mysql> INSERT INTO myset (col) VALUES 
-> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

然后，所有这些值在检索时都显示为“ a，d”：

mysql> SELECT col FROM myset;
+------+
| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
+------+
5 rows in set (0.04 sec)

如果将SET列设置为不支持的值，那么将忽略该值并发出警告：

mysql> INSERT INTO myset (col) VALUES ('a,d,d,s');
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'col' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.04 sec)

mysql> SELECT col FROM myset;
+------+
| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
+------+
6 rows in set (0.01 sec)

空间数据类型

MySQL具有与OpenGIS类相对应的空间数据类型。

一些空间数据类型保存单个几何值：

  • 几何(GEOMETRY)
  • 点(POINT)
  • 线(LINESTRING)
  • 多边形(POLYGON)

GEOMETRY可以存储任何类型的几何值。其他单值类型（POINT，LINESTRING和POLYGON）将其值限制为特定的几何类型。

其他空间数据类型包含值的集合：

  • 多点(MULTIPOINT)
  • 多线(MULTILINESTRING)
  • 多边形集(MULTIPOLYGON)
  • 几何集合(GEOMETRYCOLLECTION)

GEOMETRYCOLLECTION可以存储任何类型的对象的集合。其他集合类型（MULTIPOINT，MULTILINESTRING和MULTIPOLYGON）将集合成员限制为具有特定几何类型的成员。

例如：要创建一个名为geom的表，该表具有一个名为g的列，该列可以存储任何几何类型的值，请使用以下语句：

CREATE TABLE geom (g GEOMETRY);

可以在NOT NULL空间列上创建SPATIAL索引，因此，如果您计划为该列建立索引，则将其声明为NOT NULL：

CREATE TABLE geom (g GEOMETRY NOT NULL);

JSON数据类型

与将JSON格式的字符串存储在字符串列中相比，JSON数据类型具有以下优点：

  • 自动验证存储在JSON列中的JSON文档。无效的文档会产生错误。
  • 优化的存储格式。 存储在JSON列中的JSON文档将转换为内部格式，以允许快速读取文档元素。 当服务器稍后必须读取以该二进制格式存储的JSON值时，无需从文本表示形式解析该值。 二进制格式的结构使服务器可以直接通过键或数组索引查找子对象或嵌套值，而无需读取文档中它们之前或之后的所有值。

JSON列不能具有非NULL的默认值。

除JSON数据类型外，还有一组SQL函数可用于启用对JSON值的操作，例如创建，操作和搜索。以下讨论显示了这些操作的示例。

还提供了一组用于处理GeoJSON值的空间函数。

JSON列与其他二进制类型的列一样，不会直接建立索引；相反，您可以在生成的列上创建索引，以从JSON列提取标量值。

MySQL优化器还在与JSON表达式匹配的虚拟列上寻找兼容的索引。

创建JSON值

JSON数组包含一个用逗号分隔并包含在[和]字符中的值的列表：

["abc", 10, null, true, false]

JSON对象包含一组键值对，以逗号分隔并用{和}字符括起来：

{"k1": "value", "k2": 10}

如示例所示，JSON数组和对象可以包含字符串或数字的标量值，JSON空文字或JSON布尔值true或false文字。 JSON对象中的键必须是字符串。 还允许使用时间（日期，时间或日期时间）标量值：

["12:18:29.000000", "2015-07-29", "2015-07-29 12:18:29.000000"]

JSON数组元素和JSON对象键值中允许嵌套：

[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}

在MySQL中，JSON值被写为字符串。 MySQL会解析在需要JSON值的上下文中使用的任何字符串，如果该字符串作为JSON无效，则会产生错误。 这些上下文包括将值插入具有JSON数据类型的列，并将参数传递给需要JSON值的函数（在MySQL JSON函数的文档中通常显示为json_doc或json_val），如以下示例所示：

  • 如果该值是有效的JSON值，则尝试将值插入JSON列成功，但如果不是，则尝试失败：
mysql> CREATE TABLE t1 (jdoc JSON);
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 VALUES('[1, 2,');
ERROR 3140 (22032) at line 2: Invalid JSON text:
"Invalid value." at position 6 in value (or column) '[1, 2,'.
  • JSON_TYPE（）函数需要一个JSON参数，并尝试将其解析为JSON值。如果有效，则返回值的JSON类型，否则返回错误：
mysql> SELECT JSON_TYPE('["a", "b", 1]');
+----------------------------+
| JSON_TYPE('["a", "b", 1]') |
+----------------------------+
| ARRAY                      |
+----------------------------+

mysql> SELECT JSON_TYPE('"hello"');
+----------------------+
| JSON_TYPE('"hello"') |
+----------------------+
| STRING               |
+----------------------+

mysql> SELECT JSON_TYPE('hello');
ERROR 3146 (22032): Invalid data type for JSON data in argument 1
to function json_type; a JSON string or JSON type is required.

MySQL使用utf8mb4字符集和utf8mb4_bin归类处理在JSON上下文中使用的字符串。 其他字符集中的字符串将根据需要转换为utf8mb4。 （对于ascii或utf8字符集中的字符串，不需要转换，因为ascii和utf8是utf8mb4的子集。）

作为使用文字字符串编写JSON值的替代方法，存在用于从组件元素组成JSON值的函数。 JSON_ARRAY（）接受（可能为空）值列表，并返回包含这些值的JSON数组：

mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW())              |
+----------------------------------------+
| ["a", 1, "2015-07-27 09:43:47.000000"] |
+----------------------------------------+

JSON_OBJECT（）接受键值对的列表（可能为空），并返回包含这些对的JSON对象：

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
+---------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc') |
+---------------------------------------+
| {"key1": 1, "key2": "abc"}            |
+---------------------------------------+

JSON_MERGE（）接受两个或多个JSON文档并返回合并的结果：

mysql> SELECT JSON_MERGE('["a", 1]', '{"key": "value"}');
+--------------------------------------------+
| JSON_MERGE('["a", 1]', '{"key": "value"}') |
+--------------------------------------------+
| ["a", 1, {"key": "value"}]                 |
+--------------------------------------------+

JSON值的规范化，合并和自动包装

当解析一个字符串并发现它是一个有效的JSON文档时，也会对其进行规范化：具有与文档中较早发现的键重复的键的成员将被丢弃（即使值不同）。 以下JSON_OBJECT（）调用产生的对象值不包括第二个key1元素，因为该键名早于该值：

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');
+------------------------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def') |
+------------------------------------------------------+
| {"key1": 1, "key2": "abc"}                           |
+------------------------------------------------------+

数据类型的默认值

数据类型规范可以具有显式或隐式默认值。

显示的默认处理

给一个列显示的指定默认值，比如：

CREATE TABLE t1 (
  i     INT DEFAULT -1,
  c     VARCHAR(10) DEFAULT '',
  price DOUBLE(16,2) DEFAULT '0.00'
);

除了一个例外，DEFAULT子句中指定的默认值必须为文字常量； 它不能是函数或表达式。 例如，这意味着你不能将日期列的默认值设置为诸如NOW()或CURRENT_DATE之类的函数的值。 唯一的例外是，对于TIMESTAMP和DATETIME列，可以将CURRENT_TIMESTAMP指定为默认值。

无法为BLOB，TEXT，GEOMETRY和JSON数据类型分配默认值。

隐式的默认处理

如果数据类型规范不包含任何显式的DEFAULT值，则MySQL按照以下方式确定默认值：

如果列可以将NULL作为值，则该列使用显式的DEFAULT NULL子句定义。

如果该列不能使用NULL作为值，则MySQL定义该列时不带显式DEFAULT子句。

对于将数据输入到没有显式DEFAULT子句的NOT NULL列中的情况，如果INSERT或REPLACE语句不包含该列的值，或者UPDATE语句将该列设置为NULL，则MySQL根据有效的SQL模式处理该列：

  • 如果启用了严格的SQL模式，则事务表将发生错误，并且该语句将回滚。 对于非事务表，会发生错误，但是如果此错误发生在多行语句的第二行或后续行中，则该错误之前的所有行均已插入。
  • 如果未启用严格模式，则MySQL将列设置为列数据类型的隐式默认值。

假设表t定义如下：

CREATE TABLE t (i INT NOT NULL);

在这种情况下，i 没有明确的默认值，因此在严格模式下，以下每个语句都会产生错误，并且不会插入任何行。 当不使用严格模式时，只有第三条语句会产生错误。 为前两个语句插入了隐式默认值，但是第三个语句失败，因为DEFAULT（i）无法产生值：

INSERT INTO t VALUES();
INSERT INTO t VALUES(DEFAULT);
INSERT INTO t VALUES(DEFAULT(i));

对于给定的表，SHOW CREATE TABLE语句显示哪些列具有显式的DEFAULT子句。

隐式默认值定义如下：

  • 对于数字类型，默认值为0，但对于用AUTO_INCREMENT属性声明的整数或浮点类型，默认值为序列中的下一个值。
  • 对于除TIMESTAMP以外的日期和时间类型，默认值为该类型的适当“零”值。如果启用了explicit_defaults_for_timestamp系统变量，则对于TIMESTAMP也是如此。否则，对于表中的第一个TIMESTAMP列，默认值为当前日期和时间。
  • 对于ENUM以外的其他字符串类型，默认值为空字符串。对于ENUM，默认值为第一个枚举值。

数据类型存储要求

磁盘上表数据的存储要求取决于几个因素。 不同的存储引擎表示数据类型，并以不同的方式存储原始数据。 表数据可能会被压缩，无论是针对列还是整行，都会使表或列的存储需求的计算复杂化。

尽管磁盘上的存储布局有所不同，但内部MySQL API可以通信和交换有关表行的信息，它们使用适用于所有存储引擎的一致数据结构。

本节包含有关MySQL支持的每种数据类型的存储要求的准则和信息，包括使用固定大小表示形式的数据类型的存储引擎的内部格式和大小。 信息按类别或存储引擎列出。

表的内部表示形式的最大行大小为65,535字节，即使存储引擎能够支持更大的行也是如此。 该项不包括BLOB或TEXT列，它们仅占该大小的9到12个字节。 对于BLOB和TEXT数据，该信息内部存储在与行缓冲区不同的内存区域中。 不同的存储引擎根据它们用于处理相应类型的方法，以不同的方式处理此数据的分配和存储。

数值类型存储要求

数据类型 存储要求
TINYINT 1 byte
SMALLINT 2 bytes
MEDIUMINT 3 bytes
INT, INTEGER 4 bytes
BIGINT 8 bytes
FLOAT(p) 4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53
FLOAT 4 bytes
DOUBLE [PRECISION], REAL 8 bytes
DECIMAL(M,D), NUMERIC(M,D) Varies; see following discussion
BIT(M) approximately (M+7)/8 bytes

DECIMAL（和NUMERIC）列的值使用二进制格式表示，该格式将九个十进制（基数10）数字打包为四个字节。 每个值的整数和小数部分的存储分别确定。 九个数字的每个倍数需要四个字节，而“剩余”位需要四个字节的一部分。 下表给出了多余数字的存储要求。

剩余位 字节数
0 0
1 1
2 1
3 2
4 2
5 3
6 3
7 4
8 4

日期和时间类型的存储要求

对于TIME，DATETIME和TIMESTAMP列，在MySQL 5.6.4之前创建的表所需的存储与从5.6.4以后创建的表不同。 这是由于5.6.4中的更改，允许这些类型具有小数部分，这需要从0到3个字节。

数据类型 5.6.4之前的存储要求 5.6.4的存储要求
YEAR 1 byte 1 byte
DATE 3 bytes 3 bytes
TIME 3 bytes 3 bytes + 小数部分的存储
DATETIME 8 bytes 5 bytes + 小数部分的存储
TIMESTAMP 4 bytes 4 bytes + 小数部分的存储

从MySQL 5.6.4开始，YEAR和DATE的存储保持不变。 但是，TIME，DATETIME和TIMESTAMP的表示方式有所不同。 DATETIME的打包效率更高，非小数部分需要5个字节，而不是8个字节，并且所有三个部分的小数部分都需要0到3个字节，具体取决于存储值的小数秒精度。

小数的精度 存储要求
0 0 bytes
1,2 1 byte
3,4 2 bytes
5,6 3 bytes

例如，TIME(0)，TIME(2)，TIME(4)和TIME(6)分别使用3、4、5和6个字节。 TIME和TIME(0)等效，并且需要相同的存储空间。

字符串的存储要求

在下表中，M表示非二进制字符串类型的声明的列长度，以字符表示，对于二进制字符串类型，M表示字节。 L表示给定字符串值的实际长度（以字节为单位）。

数据类型 存储要求
CHAR(M) 紧凑的InnoDB行格式系列优化了可变长度字符集的存储。否则，M×w个字节，<= M <= 255，其中w是字符集中最大长度字符所需的字节数。
BINARY(M) M 字节, 0 <= M <= 255
VARCHAR(M), VARBINARY(M) 如果列值需要0到255个字节，则为L + 1个字节；如果值可能需要超过255个字节，则为L + 2个字节
TINYBLOB, TINYTEXT L + 1 个字节，其中 L < 2^8
BLOB, TEXT L + 2 个字节，其中 L < 2^16
MEDIUMBLOB, MEDIUMTEXT L + 3 个字节，其中 L < 2^24
LONGBLOB, LONGTEXT L + 4 个字节，其中 L < 2^32
ENUM(‘value1’,’value2’,…) 1或2个字节，取决于枚举值的数量（最大65,535个值）
SET(‘value1’,’value2’,…) 1、2、3、4或8个字节，具体取决于集合成员的数量（最多64个成员）

可变长度字符串类型使用长度前缀加数据存储。 长度前缀需要一到四个字节，具体取决于数据类型，并且前缀的值为L（字符串的字节长度）。 例如，存储MEDIUMTEXT值需要L个字节来存储值，再加上三个字节来存储值的长度。

要计算用于存储特定CHAR，VARCHAR或TEXT列值的字节数，必须考虑用于该列的字符集以及该值是否包含多字节字符。 特别是，在使用utf8 Unicode字符集时，必须记住，并非所有字符都使用相同数量的字节。 utf8mb3和utf8mb4字符集每个字符分别最多需要三个和四个字节。 对于用于不同类别utf8mb3或utf8mb4字符的存储的细分。

VARCHAR，VARBINARY和BLOB和TEXT类型是可变长度类型。对于每种存储需求，取决于以下因素：

  • 列的实际长度值
  • 列的最大可能长度
  • 用于该列的字符集，因为某些字符集包含多字节字符

例如，VARCHAR(255)列可以容纳最大长度为255个字符的字符串。 假设该列使用latin1字符集（每个字符一个字节），则实际需要的存储量是字符串的长度（L），再加上一个字节来记录字符串的长度。 对于字符串’abcd’，L为4，存储要求为5个字节。 如果改为声明同一列使用ucs2双字节字符集，则存储要求为10个字节：’abcd’的长度为8个字节，并且该列需要两个字节来存储长度，因为最大长度大于255。 （最大510字节）。

可以存储在VARCHAR或VARBINARY列中的有效最大字节数受65,535字节的最大行大小的限制，该大小在所有列之间共享。 对于存储多字节字符的VARCHAR列，有效最大字符数要少。 例如，utf8mb3字符每个字符最多需要三个字节，因此使用utf8mb3字符集的VARCHAR列可以声明为最多21,844个字符。

InnoDB将长度大于或等于768字节的固定长度字段编码为可变长度字段，可以将其存储在页面外。 例如，如果字符集的最大字节长度大于3（与utf8mb4相同），则CHAR（255）列可以超过768个字节。

为列选择正确的类型

为了获得最佳存储，在所有情况下都应尝试使用最精确的类型。 例如，如果整数列用于1到99999范围内的值，则MEDIUMINT UNSIGNED是最佳类型。 在代表所有必需值的类型中，此类型使用最少的存储量。

使用DECIMAL列进行的所有基本计算（+，-，*和/）均以65位十进制（以10为基数）的精度完成。

如果精度不太重要，或者速度是最高优先级，则DOUBLE类型可能就足够了。 为了获得高精度，您始终可以转换为存储在BIGINT中的定点类型。 这使您能够使用64位整数进行所有计算，然后根据需要将结果转换回浮点值。

CATALOG
    FEATURED TAGS
    开源 MySQL DataBase Linux Java JVM GC ES Elastic Search TSDB Time Series DataBase Redis memory Spring Boot Spring Java8 Maven POM HashMap Map LinkedHashMap ThreadPoolExecutor JUC AbstractQueuedSynchronizer AQS 分布式
    LINKS