第11章、数据类型

数字类型

MySQL 支持标准 SQL 中所有数据类型。
所有的这些类型包括：

精确的数据类型：INTEGER,SMAILLINT,DECIMAL,NUMERIC；
近似的数据类型：FLOAT,REAL,DOUBLE PRECISION;

关键字 INT 是 INTEGER 的别名，关键字 DEC 和 FIXED 是 DECIMAL的别名。
在 MyISAM/MEMORY/InnoDB和NDB表中支持BIT 数据类型，BIT 数据类型用于存储 bit 值。

整型

MySQL 支持 SQL 标准整数类型INTEGER（或INT）和SMAILLINT。在此之外，MySQL 还扩展了数据类型：TINYINT、MEDIUMINT、BIGINT。下表显示了每种整数类型所需的存储空间和范围。

类型	存储空间（字节）	最小值（signed)	最大值（signed）	最大值（unsigned）
tinyint	1	-2^7	2^7-1	2^8-1
smallint	2	-2^15	2^15-1	2^16-1
meduimint	3	-2^23	2^23-1	2^24-1
int	4	-2^31	2^31-1	2^32-1
bigint	8	-2^63	2^63-1	2^64-1

精确小数类型

DECIMAL 和 NUMERIC 类型是精确类型。比如货币，需要使用精确类型存储。在MySQL中用DECIMAL实现了NUMERIC，所在下文中有关DECIMAL的描述对NUMERIC也适用。MySQL使用二进制存储DECIMAL的值。

在小数列声明中，精度和小数位数可以（通常是）指定：salary DECIMAL(5,2)
在本例中，5表示数字个数（不含小数点），2表示小数点后的位数。本例中的数据范围是：-999.99至999.99。
在标准 SQL 中当为精度为0时可以省略，即DEMICAL(5)等同于DEMICAL(5,0)。

近似小数类型

FLOAT和DOUBLE类型表示近似的数值数据值。MySQL使用四个字节作为单精度值，八个字节作为双精度值。

BIT数据类型

BIT(M) 允许存储M个BIT值，M的范围是1至64。

数字类型属性

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

显示宽度不限制可以存储在列中的值的范围，也不会阻止比列显示宽度更宽的值被正确显示。

所有的整数类型都可以有一个可选的（非标准的）UNSIGNED，当你需要更大的范围时可供选择。同样的浮点类型（精确，非精确）也可以使用 UNSIGNED，但是与整型不同的是，浮点型的 UNSIGNED 不会改变数据的范围。

超出范围和溢出处理

当存储的值超出额定范围后，存储的结果视MySQL的模式决定。
当启用了严格模式，则会按照标准的SQL拒绝数据的写入。若未启用严格模式，则将数据裁剪并写入。

日期类型

DATE, DATETIME, TIMESTAMP

DATE, DATETIME, TIMESTAMP 是互相关联的。这章节将会阐述，它们哪些点是相同的，另外又有哪些点是有差异的。

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

DATETIME 同时有日期部分和时间部分。MySQL 以'YYYY-MM-DD HH:MM:SS'的格式，支持的范围从'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'。

TIMESTAMP 也同时有日期部分和时间部分。它的范围是：'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC。

DATETIME 和 TIMESTAMP 精确到微秒。
DATETIME 和 TIMESTAMP 数据类型具备自动初始化和更新功能（举例：业务上通常需要的创建时间和更新时间，在MySQL层面上可以自动完成）。

对应 TIMESTAMP 类型，存储时会自动转到 UTC 时区，在检索时则会自动从 UTC时区转到当前时区。（ DATETIME不会执行该操作）。默认情况下，每个连接的当前时区是服务器的时间。时区可以在每个连接的基础上设置。只要时区设置保持不变，您将获得与您存储的值相同的值。如果您存储 TIMESTAMP ，然后更改时区并检索该值，则检索到的值与您存储的值不同。发生这种情况是因为同一时区未用于双向转换。当前时区的修改参见系统变量：time_zone。

MySQL 允许指定为字符串的值的“宽松”格式，其中任何标点符号可用作日期部分或时间部分之间的分隔符。虽然 MySQL 提供了宽松的存储环境，我们最好仍按照默认的格式进行编码，以避免可读性差引起的干扰。

TIME

TIME 以HH:MM:SS或HHH:MM:SS格式化数据。TIME 的范围从 '-838:59:59' 到 '838:59:59'。小时部分如此大的原因是，TIME并不是只用于1天内的场景。也可能用于两次事件间的间隔事件，这可能远大于24小时。

当录入的值超出范围后，将会自动剪切到最近的有效范围内的值。

YEAR

YEAR 以 YYYY格式，范围从 1901 至 2155，或 0000。
若是4位则范围是，1901~2155。
若是录入1_99，则169转化为2001-2069，70_{99则转化为1970}1999。
若是录入数字0，则转化为0000。若是录入字符0或00，则转化为2000。

关于MySQL默认值了解即可（不能依赖默认值），我们需要严格控制录入的值。
在 MySQL 5.7 中已经禁用了YEAR(2)；

自动初始化和更新 TIMESTAMP 与 DATETIME

表格中的 TIMESTAMP 或 DATETIME 的列可以初始化当前时间作为默认值，或设置为修改自动更新，或两者共存。

当指定的列中没有值时，使用当前时间作为默认时间。
当该行中任何其他列的值从其当前值更改时，自动更新的列会自动更新为当前时间戳。

要设置自动更新属性时，在列的定义时使用以下语句（顺序不相关）：

DEFAULT CURRENT_TIMESTAMP

ON UPDATE CURRENT_TIMESTAMP

CURRENT_TIMESTAMP有以下的同义词： CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, 和 LOCALTIMESTAMP()。

要 MySQL 5.7中，新增DATETIME列的支持设置默认值和自动更新。在之前的版本，你如果尝试设置将会得到一个错误。

字符串类型

CHAR VARCHAR

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

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

CHAR列长度可以是0到255之间的任何值。CHAR 存储值时，它们将空格填充到指定的长度。当CHAR检索到值时，除非PAD_CHAR_TO_FULL_LENGTH启用了SQL模式，否则将删除尾随空格。

VARCHAR列中的值是可变长度的字符串。长度可以指定为0到65535之间的值。

与CHAR不同，VARCHAR会存储一个长度为1字节或2字节的前缀，用于存储长度值，用以表明存储的字节数。

若需存储的字节数少于255，则前缀的长度为1。
否则，前缀的长度为2。

以下表格说明 CHAR 和 VARCHAR 在存储值时候的差异：

image.png

显示为存储在表格最后一行的值仅适用于不使用严格模式的情况 ; 如果MySQL在严格模式下运行，则超出列长度的值不会被存储，并会导致错误。

这些特性会很有用：

比如在列的可选值是 Y/N时，选择CHAR(1)比VARCHAR(1)节省一个字节空间。

比如既有的数据中存在一些以空格结尾的数据，那么可以先将列改为CHAR保存。然后再修改为VARCHAR。

BINARY VARBINARY

BINARY和VARBINARY类型与CHAR和VARCHAR相似，除了它们包含二进制字符串而非非二进制字符串。也就是说，它们包含字节串而不是字符串。

BINARY的填充字节是0x00。字节在比较中很重要，包括ORDER BY与DISTINCT操作。0x00与空格在比较时是不一样的，0x00要小于空格。

如果您打算使用BINARY数据类型来存储二进制数据，并且您要求检索到的值与存储的值完全相同，那么您应该仔细考虑前面的填充和剥离特征。

TEXT BLOB

BLOB 是二进制字符串。
它有四个BLOB 类型TINYBLOB，BLOB， MEDIUMBLOB，和LONGBLOB。

TEXT 是非二进制字符串
它有四个TEXT类型 TINYTEXT，TEXT， MEDIUMTEXT，和LONGTEXT。

对于TEXT和BLOB列，插入时没有填充，并且在select时没有字节被删除。

以下是使用这两者的限制点：

使用临时表处理的查询结果中的实例BLOB或 TEXT列导致服务器在磁盘上而不是在内存中使用表，因为 MEMORY存储引擎不支持这些数据类型（请参见第8.4.4节“内部临时在MySQL中使用表“）。使用磁盘会导致性能损失，因此只有在确实需要时才在查询结果中包含 BLOB或TEXT列。例如，避免使用 SELECT *，它会选择所有列。
max_sort_length排序时仅使用列的第一个字节。缺省值为 max_sort_length1024.通过增加max_sort_length服务器启动或运行时的值，可以在排序或分组中增加更多字节。任何客户端都可以更改其会话max_sort_length 变量的值：

mysql> SET max_sort_length = 2000;
mysql> SELECT id, comment FROM t
    -> ORDER BY comment;

ENUM

枚举是一个字符串对象，其值是从表创建时列规范中显式枚举的允许值列表中选择的值。
它有以下的好处：

紧凑的存储特性，字符串会被映射为数字存储；
良好的可读性；

真实场景下很少用到？

SET

SET 由0~64个以,分隔的字符串构成的，要注意成员值本身是不包含逗号的。关于64的限制，参考Section C.10.5, “Limits Imposed by .frm File Structure”
。

若在SET中出现重复值，则会产生警告。（若开启严格模式，则是直接抛异常）。SET字符串尾部的空格将会自动被移除。

数据类型默认值

除了一个例外情况外，默认值都必须是常量；它不可以是方法或者表达式；这意味着，你不能设置日期类型的列设置默认值为：NOW()或CURRENT_DATE。
截止MySQL5.7唯一的例外情况是，你可以设置日期类型的列默认值为：CURRENT_TIMESTAMP。

BLOB、TEXT、JSON、GEOMETRY的列不能设置默认值；

如果列中没有指定明确的默认值，则MySQL的默认值规则如下：

如果列中允许NULL值，则默认值为NULL
如果列中不允许NULL值，则MySQL不指定默认值；

当一个明确表明为NOT NULL的列，被INSERT或者UPDATE的数据为NULL时。MySQL则根据对应模式进行处理：

如果启用严格模式，则将抛出异常并回滚事务；
如果未启用严格模式，MySQL将列设置为列数据类型的隐式默认值。

举例，假定创建数据库表如下：

CREATE TABLE t (i INT NOT NULL);

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

在本例中 i 没有明确的默认值。

当启用严格模式时，三条指令均将抛出异常并回滚。
当未启用严格模式时，仅第三条指令会抛出异常并回滚。前两个语句插入隐式默认值，但第三个失败，因为缺省值（i）不能产生值。

对于已存在的数据库表，你可以使用SHOW CREATE TABLE来显示隐式的默认值。

隐式默认值定义如下：

对于数字类型其默认值是：0；对于启动了自增序列的数字类型，则默认值是下一个数值；
对于除了TIMESTAMP之外的日期类型，其默认值是用0填充的字符串。比如：DATETIME其默认值是0000-00-00 00:00:00。对于TIMESTAMP的默认值，则是当前的时间戳对应值。
对于除了枚举类型之外的字符串值，其默认值是空字符串；对于枚举类型，其默认值则是枚举类型中的第一个值。

小知识： SERIAL DEFAULT VALUE 是对应数字类型下 NOT NULL AUTO_INCREMENT UNIQUE 的别名。

数据类型存储要求

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

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

本节包括MySQL支持的每个数据类型的存储要求的指导方针和信息。

在MySQL内部能够支持的最大单行的大小是65535字节，即便存储引擎支持更大也不行。当然这个数据不包括：BLOB、TEXT类型（对于 BLOB、TEXT 列而言，它只在65535中贡献9至12字节）；对于 BLOB、TEXT类型，真实的数据内容并不存储在行缓冲区，故它们的值所占用的内存不受行缓冲区的限制。不同的引擎存储于分配数据的方式均有差异。

INNODB 存储引擎

这部分内容请直接参见Section 14.8.1.2, “The Physical Row Structure of an InnoDB Table”。

其他的存储引擎，暂不展开；

选择正确的列类型

为了优化存储，你需要选择最精确的数据类型；以范围1~9999的整型数据为例，MEDIUMINT UNSIGNED是最佳的选择；既能满足存储需求，又可以使用最少的存储量（参见上文的存储字节）；

如果对精度不敏感，则使用DOUBLE是不错的选择；如果需要高精度，BIGINT是不错的替代，这使您可以用64位整数进行所有计算，然后根据需要将结果转换回浮点值。

其它数据库SQL与MySQL的类型对照表