3.2 数据库中的数据类型
3.2.1 整数类型
整数类型是数据库中最基本的数据类型。标准SQL中支持INTEGER和SMALLINT这两种数据类型。MySQL数据库除了支持这两种类型以外，还扩展支持了TINYINT、MEDIUMINT和BIGINT。表3-1从不同整数类型的字节数、取值范围等方面进行对比。
表3-1 整数类型
从表3-1中可以看到，INT类型和INTEGER类型的字节数和取值范围都是一样的。其实，在MySQL中INT类型和INTEGER类型是一样的。TINYINT类型占用的字节最小，只需要1个字节。因此，其取值范围是最小的。BIGINT类型占用的字节最大，需要8个字节，因此，其取值范围是最大的。
不同类型的整数类型的字节数不同，根据类型所占的字节数可以算出该类型的取值范围。例如，TINYINT的空间为1个字节，1个字节是8位，那么TINYINT无符号数的最大值为28-1，即为255。TINYINT有符号数的最大值为27-1，即为127。同理可以算出其他不同整数类型的取值范围。
字段选择哪个整数类型取决于该字段的范围。如果字段的最大值不超过255，那么选择TINYINT类型就足够了。取值很大时，根据最大值的范围选择INT类型或BIGINT类型。现在常用的整数类型是INT类型。
【示例3-1】INT的创建。
使用命令“HELP INT”可以查看INT的数据范围，如图3-3所示。
图3-3 INT类型帮助文档
首先创建一个含有INT类型字段的表，再使用INSERT语句插入符合范围的数据，如果插入的数据超出了规定的范围，就会插入失败，如图3-4、图3-5所示。
图3-4 创建表
图3-5 在表里插入数据再查询
3.2.2 浮点数类型和定点数类型
数据表中用浮点数类型和定点数类型来表示小数。浮点数类型包括单精度浮点数（FLOAT型）和双精度浮点数（DOUBLE型）。定点数类型就是DECIMAL型。下面从这三种类型的字节数、取值范围等方面进行对比，如表3-2所示。
表3-2 浮点数和定点数类型
从表3-2可以看到，DECIMAL型的取值范围与DOUBLE相同，但是DECIMAL的有效值范围由M和D决定；而且DECIMAL型的字节数是M+2，也就是说，定点数的存储空间是根据其精度决定的。
【示例3-2】FLOAT、DOUBLE和DECIMAL的创建（见图3-6、图3-7、图3-8）。
图3-6 创建含有FLOAT、DOUBLE和DECIMAL类型字段的数据表
图3-7 插入数据
图3-8 查询数据
由图3-6、图3-7、图3-8可见，FLOAT、DOUBLE数据类型存储数据时存储的是近似值，DECIMAL存储的是字符串，因此提供了更高的精度。在金融系统中，表示货币金额的时候，会优先考虑DECIMAL数据类型；在一般的价格体系中，比如购物平台中货品的标价，一般选择FLOAT类型就可以。
3.2.3 日期与时间类型
日期与时间类型是为了方便在数据库中存储日期和时间而设计的，数据库有多种表示日期和时间的数据类型。其中，YEAR类型表示年，DATE类型表示日期，TIME类型表示时间，DATETIME和TIMESTAMP表示日期和时间。下面从这5种日期与时间类型的字节数、取值范围和零值等方面进行对比，如表3-3所示。
表3-3 日期与时间类型
从表3-3可以看到，每种日期与时间类型都有一个有效范围。如果插入的值超过了这个范围，系统就会报错，并将零值插入到数据库中。不同的日期与时间类型均有不同的零值，表3-3中已经详细列出。
【示例3-3】日期和时间类型的使用。
在图3-9中，先创建一个包含日期和时间类型的表，再插入相关数据，最后查询展示数据，由此示例可以了解日期和事件类型的使用。在实际应用中，我们有时在线申请工作或者补助的时候需要填写出生年月，后来的数据就会存储成日期和时间类型；事实上，我们在大部分平台上的任何操作，后来服务器都会记录操作的日期和时间，在数据库中存储，比如购物日期时间、发货日期时间、收货时间。
图3-9 日期时间类型数据插入和查询
3.2.4 字符串类型
字符串类型是在数据库中存储字符串的数据类型。字符串类型包括CHAR、VARCHAR、BLOB、TEXT、ENUM和SET。
1. CHAR类型和VARCHAR类型
CHAR类型和VARCHAR类型都在创建表时指定了最大长度，其基本形式如下：
字符串类型（M）
其中，“字符串类型”参数指定了数据类型为CHAR类型还是VARCHAR类型；M参数指定了该字符串的最大长度为M。例如，CHAR(4)就是数据类型为CHAR类型，其最大长度为4。
CHAR类型的长度是固定的，在创建表时就指定了。其长度可以是0~255的任意值。例如，CHAR(100)就是指定CHAR类型的长度为100。
VARCHAR类型的长度是可变的，在创建表时指定了最大长度。定义时，其最大值可以取0~65535之间的任意值。指定VARCHAR类型的最大值以后，其长度可以在0到最大长度之间。例如，VARCHAR(100)的最大长度是100，但是不是每条记录都要占用100个字节，而是在这个最大值范围内使用多少就分配多少。VARCHAR类型实际占用的空间为字符串的实际长度加1，这样即可有效节约系统的空间。
下面向CHAR(5)与VARCHAR(5)中存入不同长度的字符串，将数据库中的存储形式和占用的字节数进行对比，如表3-4所示。
表3-4 CHAR(5)与VARCHAR(5)的对比
表3-4显示，CHAR(5)所占用的空间都是5个字节，这表示CHAR(5)的固定长度就是5个字节。VARCHAR(5)所占的字节数是在实际长度的基础上加1，因为字符串的结束标识符占用了1个字节。从表3-4的第三行可以看到，VARCHAR将字符串'abc'最后的空格保留着。
【示例3-4】字符串类型的使用。
创建记录电影名字的表格，名字的字段用VARCHAR类型，如果字符串的长度超过了定义的长度，就无法插入，并显示出错信息，如图3-10、图3-11、图3-12、图3-13所示。
图3-10 创建包含字符类型字段的表
图3-11 插入超过定义长度的数据
图3-12 插入符合定义长度的字符数据
图3-13 查看插入字符数据
2. TEXT类型
TEXT类型是一种特殊的字符串类型，包括TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT，其长度和存储空间的对比如表3-5所示。
表3-5 各种TEXT类型的对比
从表3-5可以看出，各种TEXT类型的区别在于允许的长度和存储空间不同。因此，在这几种TEXT类型中，根据需求选取既能满足需要又节省空间的类型即可。
3. ENUM类型
ENUM类型又称为枚举类型。在创建表时，ENUM类型的取值范围以列表的形式指定，其基本形式如下：
属性名 ENUM('值1', '值2', …, '值n')
其中，“属性名”参数指字段的名称，“值n”参数表示列表中的第n个值。ENUM类型的值只能取列表中的一个元素。其取值列表中最多能有65535个值。列表中的每个值独有一个顺序排列的编号，MySQL中存入的是这个编号，而不是列表中的值。
如果ENUM类型加上了NOT NULL属性，其默认值为取值列表的第一个元素。如果不加NOT NULL属性，ENUM类型将允许插入NULL，而且NULL为默认值。
4. SET类型
在创建表时，SET类型的取值范围就以列表的形式指定了，其基本形式如下：
属性名 SET('值1', '值2', …, '值n')
其中，属性名参数指字段的名称，“值n”参数表示列表中的第n个值，这些值末尾的空格将会被系统直接删除。其基本形式与ENUM类型一样。SET类型的值可以取列表中的一个元素或者多个元素的组合。取多个元素时，不同元素之间用逗号隔开。SET类型的值最多只能是由64个元素构成的组合。
3.2.5 二进制类型
二进制类型是存储二进制数据的数据类型，包括BINARY、VARBINARY、BIT、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。二进制类型之间的对比如表3-6所示。
表3-6 二进制类型
1. BINARY和VARBINARY类型
BINARY类型和VARBINARY类型都是在创建表时指定最大长度，其基本形式如下：
字符串类型（M）
其中，“字符串类型”参数指定数据类型为BINARY类型还是VARBINARY类型；M参数指定该二进制数的最大字节长度为M。这与CHAR类型和VARCHAR类型相似。例如，BINARY(10)就是指数据类型为BINARY类型，其最大长度为10。
BINARY类型的长度是固定的，在创建表时就指定了，不足最大长度的空间由″\0″补全。例如，BINARY(50)就是指定BINARY类型的长度为50。
VARBINARY类型的长度是可变的，在创建表时指定了最大的长度，其长度可以在0到最大长度之间，在这个最大值范围内使用多少就分配多少。
2. BIT类型
BIT类型在创建表时指定最大长度，其基本形式如下：
BIT（M）
其中，“M”指定该二进制数的最大字节长度为M，M的最大值为64。例如，BIT(4)就是指数据类型为BIT类型，长度为4。若字段的类型BIT(4)存储的数据是0~15，因为变成二进制之后15的值为1111，则其长度为4。如果插入的值为16，其二进制数为10000，长度为5，超过了最大长度，因此大于16的数是不能插入BIT(4)类型字段中的。
3. BLOB类型
BLOB类型是特殊的二进制类型。BLOB用来保存数据量很大的二进制数据，如图片等。BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。这几种BLOB类型的区别是能够保存的最大长度不同。LONGBLOB的长度最大，TINYBLOB的长度最小。
BLOB类型与TEXT类型类似，不同在于BLOB类型用于存储二进制数据，BLOB类型数据根据其二进制编码进行比较和排序，而TEXT类型是文本模式进行比较和排序的。
3.2.6 JSON类型及MySQL 8 JSON增强
JSON是一种轻量级的数据交换格式。相比格式化JSON以字符串形式存储在数据库中，使用JSON类型有如下好处：
（1）对存储在JSON列的JSON文档进行原子化验证；
（2）优化存储格式。
在MySQL中，存储JSON文档的空间与LONGBLOB和LONGTEXT大致相当。对于JSON类型的列无法设置默认值。
1. 创建JSON值
JSON数组包括在方括号“[]”之间，例如：
["abc", 10, null, true, false]
JSON对象是一系列键值对，包括在“{}”之间，例如：
{"k1": "value", "k2": 10}
JSON数组和对象可以嵌套，例如：
[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}
在MySQL中，JSON值是以字符串形式写入的，写入时MySQL会对字符串进行解析，如果不符合JSON格式，那么写入将失败。
【示例3-5】JSON类型的使用。
创建一个包含JSON类型的表，然后向表中插入数据，如果插入的字符串不是合法的JSON，操作会失败，如图3-14、图3-15、图3-16、图3-17所示。
图3-14 创建表
图3-15 插入JSON数据成功
图3-16 插入JSON数据失败
图3-17 查询数据
2. JSON函数
JSON类型支持SQL函数。表3-7列举了当前MySQL支持的JSON函数。
表3-7 JSON函数
在以上函数中，MySQL 8新添加的有->>、JSON_PRETTY()、JSON_STORAGE_SIZE()、JSON_STORAGE_FREE()、JSON_TABLE()和JSON_MERGE_PATCH()。JSON_MERGE()函数被改名为JSON_MERGE_PRESERVE()。除此之外，MySQL 8中新增加了两个聚合函数JSON_ARRAYAGG()和JSON_OBJECTAGG()。
【示例3-6】演示JSON_MERGE_PATCH()函数的使用。
JSON_MERGE_PATCH()函数合并时，遵循以下规则：
（1）如果第一个或第二个参数不是JSON对象，那么合并结果为第二个参数，如图3-18所示。
图3-18 JSON合并演示1
（2）如果参数均为对象，合并时会去掉重复的键值，保留最后一个，并且去除字面为null的参数，如图3-19所示。
图3-19 JSON合并演示2
【示例3-7】演示聚合函数JSON_ARRAYAGG()的使用。
该函数返回结果集组成的数组，如图3-20所示。
图3-20 JSON聚合函数
由于篇幅有限，其他函数就不一一演示了，读者可结合表3-7以及官方文档自行深入研究。
3. JSON值部分更新
在MySQL 8中，优化器支持JSON文档的部分、就地更新。更新需要满足以下条件：
（1）更新的列必须声明为JSON类型。
（2）更新语句需要使用JSON_SET()、JSON_REPLACE()或JSON_REMOVE()函数。
（3）输入列与目标列需为同一列。
（4）所有的操作都是替换原先已有的值，不可新增。
（5）新值的长度不可超过原先的值。
通过设置binlog_row_value_options变量值为PARTIAL_JSON，部分更新操作将会被写入二进制日志中。
3.2.7 Spatial数据类型
Spatial数据即空间数据，又称为几何数据，用来表示物体的位置、形态、大小分布等各方面的信息，是对现实世界中存在的具有定位意义的事物和现象的定量描述。
开放地理空间信息联盟简称为OGC，发布了空间数据文档。遵循此文档，MySQL实现了空间扩展。作为几何类型SQL环境的子集，该扩展空间实现了空间特性的生成、存储和分析。
MySQL包含的空间数据类型有几何体（GEOMETRY）、点（POINT）、线（LINESTRING）和多边形（POLYGON），其中几何体可以存储任何类型的几何数据，而其他三种只能存储对应类型的几何数据。
另外，MySQL还包含其他集合类型的空间数据类型：多点（MULTIPOINT）、多线（MULTILINESTRING）、多多边形（MULTIPOLYGON）以及几何集合（GEOMETRYCOLLECTION）。