下载掌阅APP，畅读海量书库
立即打开
· 数字数据类型
· 日期和时间数据类型
· 字符串数据类型
· JSON数据类型
本章将讲解MySQL提供的多种数据类型（包括MySQL新提供的数据类型）以及数据库对每种数据类型出现异常时的处理。
在MySQL中，数字数据类型又分为如下4个类别：
· 整数类型：integer、int、smallint、tinyint、mediumint、bigint。
· 定点类型：decimal、numeric。
· 浮点类型：float、double。
· 位值类型：bit。
MySQL支持SQL标准整数类型int和smallint，也支持标准扩展的整数类型tinyint、mediumint和bigint。表3-1显示了每种整数类型所需的存储空间和取值范围，我们可以根据实际情况选用合适的整数类型。
表3-1 整数类型所需的存储空间和取值范围
decimal和numeric类型用于保存对精度要求很高的数据，比如货币数据。在MySQL中，numeric被实现为decimal，所以适用于decimal数据类型的数据同样适用于numeric数据类型的数据。
在decimal列声明中，可以指定精度和小数位数。
例3.1 在decimal列声明中指定精度为5，小数位数为2，SQL语句如下：
salary decimal(5,2)
精度表示为值存储的有效位数，小数位数表示可以存储在小数点后的位数。decimal(5,2)可以用于存储具有5位数字和两位小数的任何数值，因此定义为此数据类型的salary列的取值范围为-999.99～999.99。如果decimal小数位数为0，则值不包含小数点或小数部分，但定义为decimal数据类型的列的实际取值范围可能会受到所定义的精度或小数位数的限制。如果给定义为decimal数据类型的列分配的值的小数点后的位数超过所允许的位数，那么数值会被截断到允许的位数。表3-2是decimal数据类型的详细说明。
表3-2 decimal数据类型的详细说明
float和double数据类型表示的是浮点数据类型。在MySQL中，单精度浮点数使用4字节，双精度浮点数使用8字节。表3-3是float和double数据类型的详细说明。
表3-3 float和double数据类型的详细说明
需要注意的是，如果要使程序具有最大的可移植性，那么在定义浮点数据的类型时，应该定义为float或double precision数据类型，且不要指定精度或位数。
即bit类型，它的表示方式为bit(m)，m的取值范围为1～64。bit类型存储的是二进制字符串。
bit类型的数据范围为bit(1)～bit(64)，换算成十进制，其值的取值范围为0～2 64 -1；tinyint unsigned类型的数值取值范围为0～255（十进制数）。bit类型占用的存储空间近似为(m+7)/8字节，而tinyint类型的存储空间为1字节。当使用bit(1)和tinyint时，它们占用的存储空间是一致的。
当MySQL在数值列中存储超出列数据类型允许范围的值时，其结果取决于当时启用的SQL模式：
· 如果启用了严格的SQL模式，那么MySQL会根据SQL标准拒绝超出范围的值并显示错误提示信息，表明插入操作失败。
· 如果没有启用限制模式，那么MySQL会将插入的值截断为列数据类型范围的边界值来存储。
· 当把超出定义范围的值分配给定义为整数的列时，MySQL将把列数据类型范围的边界值存储到列中。
· 当为浮点或定点类型的列分配的值超出指定（或默认）精度和小数位数所隐含的范围时，MySQL将把列数据类型范围的边界值存储到列中。
例3.2 假设表t1具有以下定义：
create table t1 (i1 tinyint, i2 tinyint unsigned);
（1）启用严格SQL模式后，会出现超出范围的错误。
使用如下命令启用数据库的严格SQL模式：
mysql> set sql_mode = 'traditional';
在启用数据库的严格SQL模式下，插入超过数据类型范围的数值会被拒绝插入并抛出异常：
mysql> insert into t1 (i1, i2) values(256, 256);
ERROR 1264 (22003): Out of range value for column 'i1' at row 1mysql> select * FROM
t1;Empty set (0.00 sec)
（2）如果未启用严格SQL模式，则会发出警告，但是不会报错。
使用如下语句设置未启用严格SQL模式：
mysql> set sql_mode = '';
如下语句是在未启用严格SQL模式的情况下插入超过数据类型范围的数值：
mysql> insert into t1 (i1, i2) values(256, 256);
如下语句是在查询上面的插入语句时出现的警告信息：
如下语句用来检验当插入的数值超出数据类型的范围时，数值是否会插入数据表中：
从结果可知，如果不启用严格SQL模式，在数值超过数据类型范围的情况下依然会把数据添加到数据表中，只不过发出了警告，同时实际插入的数值是数据类型范围的边界值，在本例中有边界，即为最大值。
数值表达式计算期间的溢出会导致错误。
例3.3 有符号bigint值的最大值是9223372036854775807，因此以下语句会产生错误：
mysql> select 9223372036854775807 + 1;
ERROR 1690 (22003): bigint value is out of range in '(9223372036854775807 + 1)'
在这种情况下，要想操作成功，可以将结果值转换为无符号数。
发生溢出的原因是结果值超过了数据类型范围的上限，所以处理上面这种错误的另一种方法就是修改数值的数据类型，例如把数值类型修改为浮点类型，因为浮点类型的取值范围大，对于此例不会出现异常情况：
在默认情况下，unsigned整数值之间的减法（其中一个类型为unsigned）会产生一个无符号结果值。如果结果值是负数，则会导致错误：
mysql> set sql_mode = '';
Query OK, 0 rows affected (0.00 sec)
mysql> select cast(0 as unsigned) - 1;
ERROR 1690 (22003): bigint unsigned value is out of range in '(cast(0 as unsigned)
- 1)'
如果no_unsigned_subtraction启用了SQL模式，则不会出现执行语句的异常：
表示时间值的日期和时间类型有这样几种：datetime、date、timestamp、time和year。每种时间类型都有一个有效值范围和一个“零”值，当指定不符合规则的日期或时间数据时，MySQL将使用“零”值来替换。
使用日期和时间类型时，需要注意以下事项：
· MySQL以标准格式输出给定的日期或时间类型的值。如果使用日期或时间类型的格式不恰当，可能会出现不可预测的结果。
· MySQL尝试以多种格式解释用户输入的值，但是日期部分必须按年-月-日的顺序给出（例如'21-06-01'），不能使用月-日-年或日-月-年的顺序（例如'01-06-21'、'06-01-21'）。如果要将其他顺序的字符串转换为年-月-日的顺序，可以使用str_to_date()函数进行转换。
· 对于包含2位数年份值的不明确日期，MySQL会使用以下规则进行解释：
➢ 70—99范围内的年份值转换为1970—1999。
➢ 00—69范围内的年份值转换为2000—2069。
· 默认情况下，当MySQL遇到无效的日期或时间类型的值时，会将该值转换为该类型的“零”值。如果时间类型的值超出范围，那么此值会被剪裁到时间范围的相应端点。
· MySQL允许将“零”值'0000-00-00'存储为“虚拟日期”。在某些情况下，这比使用null值更方便，并且使用更少的数据和索引空间。要禁止存储'0000-00-00'为“虚拟日期”，可以启用no_zero_date模式（set session sql_mode='strict_trans_tables,no_zero_date';）。
表3-4是所有日期和时间类型格式的详细说明。
表3-4 所有日期和时间类型格式的详细说明
在MySQL中，字符串数据类型有这样几种：char、varchar、text、binary、varbinary、blob、enum和set。对于数据类型定义为char、varchar和text的列，MySQL以字符为单位定义长度规范。对于数据类型定义为binary、varbinary和blob的列，MySQL以字节为单位定义长度规范。
当列定义为char、varchar、enum和set的数据类型时，同时还可以指定列的字符集，尤其在存储中文时，建议指定字符集格式为utf8，以防止出现乱码问题。
例3.4 创建表指定字段类型时设置字符集格式。创建一个名为c1的列，该列的字符集为utf8，以及一个名为c2的列，该列的字符集为latin1，SQL语句如下：
create table mytable
(
c1 varchar(255) character set utf8,
c2 text character set latin1 collate latin1_general_cs
);
在MySQL中，char和varchar类型很相似，但是它们被存储和检索的方式有所不同，而且在最大长度和是否保留尾随空格方面也不相同。
char和varchar类型的声明需要规定存储的最大字符数。例如，char(50)最多可容纳50个字符。char类型的列规定的长度可以是0～255的任何值。char类型的列存储值时，会用空格向右填充到指定的长度。当设置启用SQL模式（即执行“set sql_mode= 'pad_char_to_full_length';”）时，char类型列被检索到的值，其尾随的空格不会被删除。
varchar类型列中的值是可变长度的字符串。长度可以指定为0～65535的值。varchar的有效最大长度受最大行大小和使用的字符集约束。与char值相比，varchar值存储1字节或2字节的长度字段。如果内容字段不超过255字节，则长度字段使用1字节；如果内容长度超过255字节，则长度字段使用2字节。
如果未启用严格SQL模式并且为char或varchar类型的列分配的值超过该列的最大长度，则该值将被截断并生成警告信息。对于非空格字符的截断，可以使用严格的SQL模式提示错误（而不是警告）并禁止插入值。对于varchar类型的列，无论使用哪种SQL模式，超出列规定长度的尾随空格在插入之前都会被截断并生成警告信息。对于char列，无论使用哪种SQL模式，都会从插入的值中截断多余的尾随空格。
表3-5展示了char和varchar类型的差别（假设该列使用单字节字符集）。
表3-5 char和varchar类型的差别
如果给定的值存储在char(4)和varchar(4)列中，则从列中检索到的值并不总是相同的，因为char类型列检索时会从列中删除尾随空格。
例3.5 说明char和varchar类型的差异的示例SQL语句及其执行结果如下：
在MySQL中，binary和varbinary类型很相似。与char、varchar类型不同的是，它们存储的是字符串而不是字节串，而且binary和varbinary类型是以字节为单位来测量长度的，而不是以字符为单位。
当binary类型值被存储时，用右填充模式填充值到指定的长度，填充值是0x00（0字节）。也就是说当插入数据时，使用0x00向右填充，并且没有为检索删除尾随字节。比如，binary(3)列插入'a '时变成'a\0\0'，插入'a\0'时变成'a\0\0'，两个插入的值在检索时保持不变。
例3.6 binary类型的值被存储时，填充值“0x00”影响列值存储的示例SQL语句及其执行结果如下：
如上示例说明新增的值'a'最终被填充成'a\0\0'存储。
和binary类型不同的是，varbinary类型列中的值是可变长度的字节串。在插入值时不会使用0x00填充，查询的时候也不会丢弃任何字节。在对列值进行比较的时候，所有的字节都是有效的，并且0x00<space（space对应的是0x20）。
blob类型的值是一个二进制的大对象，可以容纳可变数量的数据。tinyblob、blob、mediumblob和longblob类型的区别仅在于它们可以存储的值的最大长度不相同。
blob类型的值被视为二进制字符串（字节字符串），具有二进制字符集和排序规则，基于列值中字节的数值对值进行排序和比较。text类型的值被视为非二进制字符串（字符串），具有除二进制之外的字符集，并且根据字符集的排序规则对值进行排序和比较。表3-6是这几种类型数据的存储范围和使用用途说明。
表3-6 字符串数据的存储范围和使用用途说明
各个类型可存储的最大长度根据实际情况进行选择。
enum类型（即枚举类型）的列值表示一个字符串对象，其值选自在表创建时列规范的枚举值。enum类型具有以下优点：
· 在列具有有限的数据集合的情况下压缩数据空间。输入的字符串会自动编码为数字。
· 可读的查询和输出。在查询时，实际存储的数字被转换为相应字符串。
枚举值必须是带引号的字符串。
例3.7 创建和使用enum列的示例SQL语句及其执行结果如下：
示例说明：在给字段使用enum类型的时候，MySQL服务会自动给枚举的字符串添加索引编号，因此在操作过程中可以直接操作枚举值，也可以操作枚举索引编号。
使用枚举的时候，需要注意下面两个问题：
· 容易混淆。enum类型的字段在底层其实是用整型来存储的，比如用enum类型来存储'3'、'2'、'1'这样的数字字符串时，其对应的索引编号是1、2、3，不熟悉的开发人员很容易混淆。
· 字段值修改频繁的字段不适合使用enum类型。比如例3.7中的size字段，要增加一个尺寸，就要修改字段，给字段的enum类型增加一个尺寸枚举值。如果每增加一个枚举就要修改字段，这样维护成本较高，所以遇到这种情况不建议使用enum类型。
每个枚举值都有一个索引编号，列规范中列出的元素都分配有索引编号，索引编号从1开始。空字符串的索引编号为0，因此可以使用以下select语句查找enum类型列是否分配了无效值的行：
mysql> select * from tb_name where enum_col=0;
表3-7是枚举值和索引编号的规则说明。
表3-7 枚举值和索引编号的规则说明
使用enum类型列需要注意的是，最多只能有65535个不同的元素，即最多只能有65535个不同的枚举值。
如果要检索枚举的索引编号，可以通过如下语句去查询：
mysql> select enum_col+0 from tbl_name;
set类型（集合类型）的列值表示可以有零个或多个字符串对象。一个set类型的列最多可以有64个不同的成员值，并且每个值必须从创建表时指定的值列表中选择。set类型由多个逗号分隔的列值集合组成，set类型成员值本身不应包含逗号。
例3.8 指定set('one', 'two') not null的列可以具有以下任何值：
''
'one'
'two'
'one,two'
如果要将数字存储到set列中，则对于指定的列set('a','b','c','d')，其成员应具有如表3-8所示的十进制值和二进制值。
表3-8 set类型字段值的十进制值和二进制值
无论插入值时元素的顺序是怎样的，查询展示时都是根据创建表指定的顺序列出的，而且无论插入值的次数是多少，该值中的每个元素都只会出现一次。
例3.9 查询包含多个set元素的值，示例SQL语句如下：
# 一列被指定为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
最终查询的结果如下：
需要注意的是，当插入的值不为系统所支持时，该值将被忽略并发出警告。
例3.10 往set列中插入的值不为系统所支持时，SQL语句及其执行结果如下：
如上示例插入的值不被系统所支持时，系统发出了警告，只将支持的数据插入表中。
MySQL支持JSON数据类型，JSON数据类型具有如下优势：
· 存储在JSON类型列中的JSON文档会被自动验证，无效的文档会产生错误。
· 存储在JSON类型列中的JSON文档会被转换为允许快速读取文档元素的内部格式。
· 在MySQL 8中，优化器可以执行JSON类型列的局部就地更新，而不用删除旧文档后再将整个新文档写入该列。
在MySQL中，JSON类型列的值被写为字符串。如果字符串不符合JSON数据格式，则会产生错误。
例3.11 插入无效的JSON值，示例SQL语句及其执行结果如下：
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,'.
从示例中可以看出，第3条语句插入的值明显不是JSON数据格式，所以抛出了异常。
用于返回对应的数据类型。
例3.12 json_type()函数的使用，示例SQL语句及其执行结果如下：
由此可见，当值是JSON格式时，json_type函数可以查询出该值的数据类型，当值不是JSON格式时，则会抛出异常。
用于返回参数值的JSON数组。
例3.13 json_array()函数的使用，示例SQL语句及其执行结果如下：
这两条查询分别用于将一个字符串“a”转换为JSON数组和获取空的JSON数组。
用于返回键-值对（Key-Value Pair）的JSON对象。
例3.14 json_object函数的使用，示例SQL语句及其执行结果如下：
将字符串转换为JSON对象，其中包含两个键-值对，即"key1": "a"，"key2": "b"。
用于获取两个或多个JSON文档并返回组合结果。
例3.15 json_merge_preserve()函数的使用，示例SQL语句及其执行结果如下：
将两个JSON数组合并成一个JSON对象。
用于返回多个JSON数据合并之后的对象。
例3.16 json_merge_patch()函数的使用，示例SQL语句及其执行结果如下：
将3个JSON对象合并为1个。
用于提取JSON对象值。
例3.17 json_extract()函数的使用，示例SQL语句及其执行结果如下：
第一条查询用于提取JSON对象中“name”键对应的值。
第二条查询用于提取JSON对象中所有的键-值对。
用于提取JSON数组值。
例3.18 json_extract()函数的使用，示例SQL语句及其执行结果如下：
用于替换值。
例3.19 json_replace()函数的使用，示例SQL语句及其执行结果如下：
将JSON字符串中名为“name”的属性值替换为“clay2”。
用于设置值（替换旧值，并插入不存在的新值）。
例3.20 json_set()函数的使用，示例SQL语句及其执行结果如下：
用于插入值（插入新值，但不替换已经存在的旧值）。
例3.21 json_insert()函数的使用，示例SQL语句及其执行结果如下：
删除JSON数据，删除指定值后的JSON文档。
例3.22 json_remove()函数的使用，示例SQL语句及其执行结果如下：
在MySQL中操作JSON类型时，除了前面给出的各种函数之外，在实际开发中经常还会出现JSON类型和其他数据类型转换的情况，表3-9是JSON类型和其他数据类型转换时遵循的规则说明。
表3-9 JSON类型和其他数据类型转换遵循的规则说明
1．创建一张用户信息表，要求性别字段的数据类型为enum类型。
2．说出float和double数据类型的区别。