第3章 MySQL数据类型
MySQL数据类型
- 1、数字数据类型
- 1.1 整数类型
- 1.2 定点类型
- 1.3 浮点类型
- 1.4位值类型
- 1.5 超出范围和溢出处理
- 1.5.1 超出范围处理
- 1.5.2 溢出处理
- 2、日期和时间数据类型
- 3、字符串数据类型
- 3.1 char和varchar类型
- 3.2 binary和varbinary类型
- 3.3 blob 和 text类型
- 3.4 enum类型
- 3.4.1 创建和使用enum列
- 3.4.1 枚举文字的索引编号
- 3.5 set类型
- 4、JSON数据类型
- 4.1、JSON操作函数
- 4.2 JSON值和非JSON值转换
1、数字数据类型
在MySQL中，数据类型分为4类：
- 整数类型：integer、int、smallint、tinyint、mediumint、bigint。
- 定点数类型：decimal、numerical
- 浮点数类型：float、double
- 位置类型：bit
1.1 整数类型
| 类型 | 存储空间（字节) |
|---|---|
| tinyint | 1 |
| smalint | 2 |
| mediumint | 3 |
| int/integer（同义词，可以互换使用） | 4 |
| bigint | 8 |
1.2 定点类型
decimal 和 numerical类型用于保存对精度要求很高的数据。在MySQL中，numerical被实现为decimal，所以适用于decimal数据类型的数据同样适用于numerical数据类型的数据。
在decimal列声明中，可以指定精度和小数位数。
#在decimal列声明中指定精度为5，小数位为2
salary decimal(5,2)
-
精度表示为值存储的有效位数。
-
小数位数表示可以存储在小数点后的位数。
-
如decimal小数位数为0，则值不包含小数点或小数部分，但定义为decimal数据类型的列的实际取值范围可能会受到所定义的精度或小数位数的限制。
-
如定义为decimal数据类型的列分配的值的小数点后的位置超过所允许的位数，那么数值会被截断到允许的位数
类型 存储空间（字节） 有符号的最小值 无符号的最小值 有符号的最大值 无符号的最大值 decimal 对于 decimal(M,D)，如果 M>D，则为 M+2,否则为 D+2 取决于M和D的值 取决于M和D的值 取决于M和D的值 取决于M和D的值
1.3 浮点类型
| 类型 | 存储空间（字节) |
|---|---|
| float | 4 |
| double | 8 |
- 如要使程序具有最大的可移植性，定义浮点数类型为 float 或 double precision数据类型，且不指定精度或位数。
1.4位值类型
- bit类型，它的表示方式为bit(m)，m的取值范围为 1~64。 bit类型存储的是二进制字符串。
- bit类型的数据范围为 bit(1) ~ bit(64),十进制：0 ~ 2 64 − 1 2^{64} -1 264−1 ;
- tinyint unsigned类型的数值取值范围为 0~ 255。
- bit类型占用存储空间近似为(m+7)/8字节；
- tinyint类型的存储空间为 1字节。
- bit(1) 和tinyint，存储空间是一致的。
1.5 超出范围和溢出处理
MySQL在数值列中存储超出列数据类型允许范围的值时，其结果取决于当时启动的SQL模式：
- 启用严格的SQL模式，MySQL会根据SQL标准拒绝超出返回的值显示错误提示信息，表名插入操作失败。
- 未启用限制模式，MySQL会将插入的值截断为列数据类型返回的边界值存储。
- 当把超出定义范围的值分配给定义为整数的列时，MySQL把列数据类型范围的边界值存储到列中。
- 当为浮点或定点类型的列分配的值超出指定（或默认）精度和小数位数所隐含的范围时，MySQL将把列数据类型范围的边界值存储到列中。
1.5.1 超出范围处理
create table t1(i1 tinyint, t2 tinyint);#启用严格SQL模式
set sql_mode = 'tradtitinl';
mysql> insert into t1(i1,i2) values (256,256);
ERROR 1264 (22003): Out of range value for column 'i1' at row 1#未启用严格SQL模式
set sql_mode = '';
mysql> insert into t1(i1,i2) values (256,256);
Query OK, 1 row affected, 2 warnings (0.00 sec)#查询上面的插入语句时出现的警告信息
mysql> show warnings;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'i1' at row 1 |
| Warning | 1264 | Out of range value for column 'i2' at row 1 |
+---------+------+---------------------------------------------+
2 rows in set (0.00 sec)
1.5.2 溢出处理
数值表达式计算期间的溢出会导致错误。
#有符号 bigint的最大值为 9223372036854775807
mysql> select 9223372036854775807+1;
ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807 + 1)'#想操作成功，可以将值转换为无符号数select cast(9223372036854775807 as unsigned) + 1;
+-------------------------------------------+
| cast(9223372036854775807 as unsigned) + 1 |
+-------------------------------------------+
| 9223372036854775808 |
+-------------------------------------------+
1 row in set (0.00 sec)#还可以修改数值的数据类型，把数值类型修改为浮点型
mysql> select 9223372036854775808.0 + 1;
+---------------------------+
| 9223372036854775808.0 + 1 |
+---------------------------+
| 9223372036854775809.0 |
+---------------------------+
1 row in set (0.00 sec)#在默认情况下，unsigned整数之间的减法（其中一个类型为unsigned）会产生一个无符号数。若干如果产生一个负数，则会报错
mysql> select cast(0 as unsigned) -1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'#如果 no_unsignde_subtraction 启用了SQL模式，则不会出现异常
mysql> set sql_mode = 'no_unsigned_subtraction';
Query OK, 0 rows affected (0.00 sec)
mysql> select cast(0 as unsigned) -1;
+------------------------+
| cast(0 as unsigned) -1 |
+------------------------+
| -1 |
+------------------------+
1 row in set (0.00 sec)
2、日期和时间数据类型
表示时间值的日期和时间类型 ：datetime、date、timestamp、time和year。每种时间类型都有一个有效值范围和一个“零”值，当指定不符合规则的日期或时间数据时，MySQL将使用 “零” 值代替。
- MySQL以标准格式输出给定的日期或时间类型的值。如果格式不恰当，可能会出现不可预测的结果。
- MySQL可以以多种格式解释用户数输入的值，但是日期部分必须按 年-月-日，不能使用 月-日-年 或 日-月-年的顺序。如果将其它字符串转换为 年-月-日 的顺序，可以使用 str_to_date() 函数进行转换。
- 对于包含2位数年份值的不明确日期，MySQL会使用一下规则进行解释：
- 70-99 范围内的年份值转换为 1979-1999。
- 00-69 范围内的年份值转换为 2000-2069。
- 默认情况下，当MySQL遇到无效的日期或时间类型的值时，会将该值转换为该类型的 ‘’零‘’值。如果时间类型的值超出范围，其值会被剪裁到时间范围的响应断点。
- MySQL允许将 “零” 值 ‘0000-00-00’ 存储为 “虚拟日期”。在某些情况下，比使用null值方便，并且使用更少的数据和索引空间。可以禁止 ’0000-00-00’ 存储为 “虚拟日期”，启用no_zero_date模式。
| 类型 | 存储（字节） | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| date | 3 | 1000-01-01 到 9999-12-31 | YYYY-MM-DD | 日期值 |
| time | 3 | ‘-838:59:59’到’838:59:59’ | HH:MM:SS | 时间值 |
| year | 1 | 1901到2155 | YYYY | 年份值 |
| datetime | 8 | 1000-01-01 00:00:00 到 9999:12:31 23:59:59 | YYYY-MM-DD HH:MM:SS | 日期和时间值 |
| timestamp | 4 | 1970-01-01 00:00:00 到 2038-1-19 11:14:07 | YYYY-MM-DD HH:MM:SS | 日期和时间值 |
3、字符串数据类型
在MySQL中，字符串数据类型：char、varchar、text、binary、varbinary、blob、enum和set。对于数据类型定义为 char、varchar和text的列，MySQL以字符为单位定义长度规范。对于数据类型定义为 binary、varbinary和blob的列，MySQL以字节为单位定义长度规范。
当列定义为char、varchar、enum和set的数据类型，同时还可以指定列的字符集，防止乱码问题。
#创建表指定字段类型时这是字符集格式create table mytable(-> c1 varchar(255) character set utf8,-> c2 text character set latin1 collate latin1_general_cs->);
Query OK, 0 rows affected, 1 warning (0.02 sec)
3.1 char和varchar类型
在MySQL中，char和varchar类型相似，但存储和索引方式有所不同，最大长度和是否保留尾随空格方面也不同。
char 和 varchar类型的声明需要规定存储的最大字符数。
- char
- char类型当度可以是 0~255的任意值。
- 插入类型的列存储值时，会用空格向右填充到指定的长度
- 设置启动 SQL模式（“set sql_mode = ‘pad_char_to_full_length’; ”）时，char类型被检索到的值，其尾随的空格不会删除。
- varchar
- varchar类型列中的值是可变长度的字符串。
- 长度可以值 0~65535的值。
- varchar的有效最大长度受最大行大小和使用的字符集约束。
- varchar值存储1字节或2字节的长度字节。
- 内容字符长度不超过255字段，则长度字段使用1字节。
- 内容字符长度都超过255字段，则长度字段是用2字节。
- 未启用严格SQL模式
- char或varchar类型的列分配的值超过该列的最大长度，则该值将被截断并生成警告信息。
- 非空格字符的截断，可以使用严格的SQL模式提示错误（而不是警告）并禁止插入值。
- 对于varchar类型的列，无论使用那种SQL模式，超出规定长度的尾随空格在插入之间都会被截断并生成警告信息。
- 对于char列，无论使用那种SQL模式，都会插入的值中截断多余的尾随空格。
char和varchar类型的差异
| 内容 | char(4) | 占用字节 | varchar(4) | 占用字节 |
|---|---|---|---|---|
| ‘’ | ’ ’ | 4字节 | ‘’ | 1字节 |
| ‘ab’ | 'ab ’ | 4字节 | ‘ab’ | 3字节 |
| ‘abcd’ | ‘abcd’ | 4字节 | ‘abcd’ | 5字节 |
| ‘abcdefgh’ | ‘abcd’ | 4字节 | ‘abcd’ | 5字节 |
#char和varchar类型的差异
MySQL server version for the right syntax to use near 'tablw tb(t varchae(4) ,b char(4))' at line 1
mysql> create table tb(-> t varchar(4),-> b char(4)-> );
Query OK, 0 rows affected (0.02 sec)mysql> insert into tb(t,b) values('ab ','ab ');
Query OK, 1 row affected (0.01 sec)mysql> select concat('[',t,']'),concat('[',b,']') from tb;
+-------------------+-------------------+
| concat('[',t,']') | concat('[',b,']') |
+-------------------+-------------------+
| [ab ] | [ab] |
+-------------------+-------------------+
1 row in set (0.00 sec)
3.2 binary和varbinary类型
在MySQL中，binary和varbinary类型很相似。与char、varchar类型不同，它们存储的是字符串不是字节串，binary和varbinary类型是以 字节为单位来测量长度的，不是以字符为单位。
- binary
- 当binary类型值被存储时，用右填充模式充值到指定的长度，填充值0x00（0字节）。
- 当插入数据时，使用0x00向右填充，并且没有为检索删除尾随字节。
- varbinary
- 与binary类型不同的是，varbinary类型的值是可变长度的字符串。
- 在插入值时不会使用0x00填充，查询的时候也不会丢弃任何字节。
- 在对列值进行比较的时候，所有的字节都是有效的，并且0x00 < space。
#binary类型的值被存储时，填充值“0x00”影响列值存储
mysql> create table t(-> c binary(3)-> );
Query OK, 0 rows affected (0.02 sec)mysql> insert into t set c = 'a';
Query OK, 1 row affected (0.01 sec)# hex()函数值以十六进制形式显示
mysql> select hex(c), c='a',c='a\0\0'from t;
+--------+-------+-----------+
| hex(c) | c='a' | c='a\0\0' |
+--------+-------+-----------+
| 610000 | 0 | 1 |
+--------+-------+-----------+
1 row in set (0.01 sec)
3.3 blob 和 text类型
- blob类型的值是一个二进制的大对象，可以容纳可变数量的数据。
- tinyblob、blob、mediumblob 和 longblob 类型的区别只是存储的最大长度不相同。
- blob类型的值被视为二进制字符串（字节字符串），具有二进制字符集和排序规则，基于列值中字节的数值对值进行排序和比较。
- text类型的值被视为非二进制字符串（字符串），具有二进制之外的字符集，并且根据字符集的排序规则对值进行排序和比较。
| 类型 | 存储范围 | 使用用途 |
|---|---|---|
| text | 0~65525字节 | 长文本数据 |
| mediumblob | 0~16777215字节 | 二进制形式的中等长度文本数据 |
| mediumtext | 0~16777215字节 | 中等长度文本数据 |
| longblob | 0~4294967295字节 | 二进制形式的极大文本数据 |
| longtext | 0~4294967295字节 | 极大文本数据 |
3.4 enum类型
enum类型（枚举类型）的列值表示一个字符串对象，其值选自在表创建时列规范的枚举值。
优点：
- 在列具有有限的数据集合的情况下压缩数据空间。输入的字符串会自动编码为数字。
- 可读的查询和输出。在查询时，实际存储的数字被转换为相应的字符。
3.4.1 创建和使用enum列
枚举值必须是带引号的字符串
# 例
mysql> create table mytable(-> name varchar(40),-> size enum('x-small','small','medium')-> );
Query OK, 0 rows affected (0.02 sec)mysql> insert into mytable (name,size) values('','x-small'),('','small'),('',1);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql> update mytable set size = 'x-small' where size = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select name,size from mytable;
+------+---------+
| name | size |
+------+---------+
| | x-small |
| | x-small |
| | x-small |
+------+---------+
3 rows in set (0.00 sec)
在给字段使用enum类型的时候，MySQL服务会自动给枚举的字符串添加索引编号
3.4.1 枚举文字的索引编号
每个枚举值都有一个索引编号，列规范中列出的元素都分配有索引号，索引编号从1开始。空字符串的索引编号为0，nul的索引编号为null。枚举列最多只能有 65535 个不同的元素。
3.5 set类型
-
set类型（集合类型）的列值表示可以有零个或多个字符串对象。
-
一个set类型的列最多可以有64个不同的成员值，并且每个值必须从创建表是指定的值列表中选择。
-
set类型由多个逗号分隔的列值集合组成，set类型成员本身不应包含逗号。
#指定set('one','two') not null 的列可以具一下值：
''
'one'
'two'
'one','two'
将数据存储set列中，则对于指定的列set(‘a’,‘b’,‘c’,‘b’)
| set类型字段值 | 十进制值 | 二进制值 |
|---|---|---|
| ‘a’ | 1 | 0001 |
| ‘b’ | 2 | 0010 |
| ‘c’ | 3 | 0011 |
| ‘d’ | 4 | 0100 |
无论插入值时元素的顺序是怎么的，查询展示时是根据创建表指定的顺序列出的，无论插入值的次数是有多，该值中的每个元素只出现一次。
#一列被指定为 set('a','b','c','d')
mysql> create table myset(col set('a','b','c','d'));
Query OK, 0 rows affected (0.01 sec)mysql> insert into myset (col) values-> ('a,b'),('d,a'),('a,d,d'),('d,a,d');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql> select col from myset;
+------+
| col |
+------+
| a,b |
| a,d |
| a,d |
| a,d |
+------+
4 rows in set (0.00 sec)
4、JSON数据类型
JSON数据类型的优点：
- 存储在JSON类型列中的JSON文档会被自动验证，无效的文档会产生错误。
- 存储在JSON类型列中的JSON文档会被转换为允许快速读取文档元素的内部格式。
- 在MySQL9中，优化器可以执行JSON类型列的局部就地更新，而不用删除旧文档在将新文档写入该列。
在MySQL中，JSON类型列的值被写为字符串。如果字符串不符合JSON数据格式，则会产生错误。
#插入无效JSON值
mysql> create table t1(jdoc json);
Query OK, 0 rows affected (0.01 sec)mysql> INSERT INTO t1 (jdoc) VALUES ('{"key1": "value1", "key2": "value2"}');
Query OK, 1 row affected (0.01 sec)mysql> insert into t1 values('1[1,2');
ERROR 3140 (22032): Invalid JSON text: "The document root must not be followed by other values." at position 1 in value for column 't1.jdoc'.
4.1、JSON操作函数
-
json_type()函数：用于返回对应的数据库类型。
mysql> select json_type('["a","b","c"]'); +----------------------------+ | json_type('["a","b","c"]') | +----------------------------+ | ARRAY | +----------------------------+ 1 row in set (0.01 sec)mysql> select json_type('"a"'); +------------------+ | json_type('"a"') | +------------------+ | STRING | +------------------+ 1 row in set (0.00 sec)#不是JSON格式时，会抛出异常 mysql> select json_type('a'); ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_type: "Invalid value." at position 0. -
json_array()函数（参数可以为空）：用于返回参数值的JSON数组
mysql> select json_array('a',false,1); +-------------------------+ | json_array('a',false,1) | +-------------------------+ | ["a", false, 1] | +-------------------------+ 1 row in set (0.01 sec)mysql> select json_array(); +--------------+ | json_array() | +--------------+ | [] | +--------------+ 1 row in set (0.00 sec) -
json_object函数（参数可以为空）：用于返回键-值对（Key-Value Pair）的JSON对象
mysql> select json_object('key1','a','key2','b'); +------------------------------------+ | json_object('key1','a','key2','b') | +------------------------------------+ | {"key1": "a", "key2": "b"} | +------------------------------------+ 1 row in set (0.00 sec) -
json_merge_preserve()函数：用于获取两个或多个JSON文档并返回组合结果
mysql> select json_merge_preserve('["a",123]','{"key": "value"}'); +-----------------------------------------------------+ | json_merge_preserve('["a",123]','{"key": "value"}') | +-----------------------------------------------------+ | ["a", 123, {"key": "value"}] | +-----------------------------------------------------+ 1 row in set (0.00 sec) -
json_merge_parch()函数： 用于返回多个JSON类型合并之后的对象
mysql> select json_merge_patch('{"a": 3, "b": 2}','{"c": 3,"a": 4}'); +--------------------------------------------------------+ | json_merge_patch('{"a": 3, "b": 2}','{"c": 3,"a": 4}') | +--------------------------------------------------------+ | {"a": 4, "b": 2, "c": 3} | +--------------------------------------------------------+ 1 row in set (0.00 sec) -
json_extract()函数：用于提取JSON对象值
mysql> select json_extract('{"id": 8, "name": "clay"}',"$.name"); +----------------------------------------------------+ | json_extract('{"id": 8, "name": "clay"}',"$.name") | +----------------------------------------------------+ | "clay" | +----------------------------------------------------+ 1 row in set (0.01 sec)mysql> select json_extract('{"id": 8, "name": "clay"}',"$.*"); +-------------------------------------------------+ | json_extract('{"id": 8, "name": "clay"}',"$.*") | +-------------------------------------------------+ | [8, "clay"] | +-------------------------------------------------+ 1 row in set (0.00 sec) -
json_extract()函数：用于提取JSON数组值
mysql> select json_extract('["a","b","c"]','$[1]'); +--------------------------------------+ | json_extract('["a","b","c"]','$[1]') | +--------------------------------------+ | "b" | +--------------------------------------+ 1 row in set (0.00 sec)mysql> select json_extract('["a","b","c"]','$[1 to 2]'); +-------------------------------------------+ | json_extract('["a","b","c"]','$[1 to 2]') | +-------------------------------------------+ | ["b", "c"] | +-------------------------------------------+ 1 row in set (0.00 sec) -
json_replace()函数：用于替换值
mysql> select json_replace('{"id": 10, "name": "clay"}', '$.name', 'clay2'); +---------------------------------------------------------------+ | json_replace('{"id": 10, "name": "clay"}', '$.name', 'clay2') | +---------------------------------------------------------------+ | {"id": 10, "name": "clay2"} | +---------------------------------------------------------------+ 1 row in set (0.00 sec) -
json_set()函数：用于设置值（替换旧值，并插入不存在的新值）
mysql> select json_set('{"id": 11, "name": "clay"}','$.name2', 'caly2'); +-----------------------------------------------------------+ | json_set('{"id": 11, "name": "clay"}','$.name2', 'caly2') | +-----------------------------------------------------------+ | {"id": 11, "name": "clay", "name2": "caly2"} | +-----------------------------------------------------------+ 1 row in set (0.00 sec) -
json_insert()函数：用于插入值（插入值，但不替代已经存在的旧值）
mysql> select json_insert('[1,2,3,4]', '$[5]', 5); +-------------------------------------+ | json_insert('[1,2,3,4]', '$[5]', 5) | +-------------------------------------+ | [1, 2, 3, 4, 5] | +-------------------------------------+ 1 row in set (0.00 sec)mysql> select json_insert('[1,2,3,4]', '$[1]', 4); +-------------------------------------+ | json_insert('[1,2,3,4]', '$[1]', 4) | +-------------------------------------+ | [1, 2, 3, 4] | +-------------------------------------+ 1 row in set (0.00 sec) -
json_remove()函数：删除JSON数据，删除指定值后的JSON文档
mysql> select json_remove('[1,2,3,4]', '$[3]'); +----------------------------------+ | json_remove('[1,2,3,4]', '$[3]') | +----------------------------------+ | [1, 2, 3] | +----------------------------------+ 1 row in set (0.00 sec)mysql> select json_remove('{"id": 13, "name": "clay"}', '$.name'); +-----------------------------------------------------+ | json_remove('{"id": 13, "name": "clay"}', '$.name') | +-----------------------------------------------------+ | {"id": 13} | +-----------------------------------------------------+ 1 row in set (0.00 sec)
4.2 JSON值和非JSON值转换
| 数据类型 | CAST(其他类型转换为JSON) | CAST(JSON转换为其他类型) |
|---|---|---|
| JSON | 无 | 无 |
| utf8字符类型（utf8mb4、utf8、ascii） | 该字符串被解析为JSON值 | JSON值被序列化为utf8mb4字符串 |
| 其他字符类型 | 其他编码的字符被隐式转换为utf8编码并按照utf编码进行处理 | JSON值被序列化为utf8mb字符串，然后转换为其他字符编码 |
| null | 结果null转换为JSON类型的值 | 不使用 |