深入浅出MySQL-02-【MySQL支持的数据类型】

前言

环境：

• Windows11
• MySQL-8.0.35

1.数值类型

MySQL中的数值类型，如下：

整数型，例如 int(5) 表示数值宽度小于5位的时候在数字前面填满宽度，如果不显示宽度，则默认为 int(11)。一般配合zerofill使用，也就是数字位数不够的时候，在前面的空间填充字符“0”。

注意：我自己测试的，应该是数据库版本问题，int类型不指定宽度，默认是 int(10) 的宽度，下面的测试也可以看出。

测试zerofill和int的配合示例，如下：

可以发现，字段id1和id2前面的空间都填充了0，可能有疑问，如果实际插入的值超过了设置的宽度呢？答案是不会对插入的值有任何影响，还是按照实际的值和精度保存，此时宽度没有任何意义了，左边也没有空间并不会填充任何0，看下面测试：

id2的int宽度虽然设置 5，但是实际插入的宽度都是 8了，并没有影响还是插入了。

所有的整数类型都有一个可选属性UNSIGNED（无符号），如果字段设置了这个属性，取值范围是正常值的下限取0，上线取原来值的2倍。例如tinyint的有符号取值范围是 -128~127，而无符号的取值范围是 0 ~ 255。

如果一个列指定了 zerofill，MySQL默认为该列加上 UNSIGNED属性。

整数还有一个属性，AUTO_INCREMENT，只能用于整数类型，一般从1开始，每行+1。一个表中最多只能有一个 AUTO_INCREMENT的列。对于任何想要使用这个属性的列，应该定义为NOT NULL，并定义为PRIMARY KEY 或者定义为 UNIQUE 键。

对于小数，MySQL分为 浮点数 和 定点数，浮点数包括 float 单精度 和 double 双精度，而定点数只有 decimal 一种表示。定点数在MySQL内部是以字符串的形式保存的，比浮点数更准确。

浮点数 和 定点数，类型后面的 (M,D)，表示该数值一共显示M位数字（整数位+小数位），D表示精度，也就是几位小数。M和D又称为精度和标度。

浮点数不指定精度（精度和标度）时，会按照实际来显示，而decimal不指定精度（精度和标度），默认整数位和小数位为（10,0）。

示例：

都插入1.234的示例截图如下：

虽然插入成功了，但是decimal类型的插入返回了警告信息，报告id3被截断。

三个字段去掉精度，都插入1.23示例截图如下：

仔细看上图，插入的时候依然返回了警告，同上上图中的警告是一样的，id3的值被截断。

三个字段去掉精度，再次都插入1.67的示例截图：

上面的表格示例以及截图说明：

• 浮点数不写精度和标度，按照实际精度和标度值展示，指定精度和标度的，标度超出会按照四舍五入后的结果插入。
• decimal不写精度默认和标度，就是 （10,0），标度超出的会进行四舍五入，然后截断并返回警告。

上面测试的都是标度超出的处理结果，如果精度超出了会怎么样呢？如下：

对于 位类型 ，这里不多做解释，自行百度。

2.日期时间类型

MySQL中支持的日期时间类型，如下：

这些类型的区别：

• 表示年月日，使用DATE。
• 表示年月日时分秒，使用DATETIME或者TIMESTAMP，但是两者存在区别，后续说明。
• 表示时分秒，使用TIME。
• 表示年份，使用YEAR，比DATE占用空间少。YEAR有2位和4位格式的年表示，默认4位的，允许的值是1901~2155和0000。2位格式中，允许的值是 70~69，表示1970到2069年。MySQL版本从5.5.27开始不再支持2位的表示了
• DATE、TIME 和 DATETIME是经常使用的三种时间格式。

TIMESTAMP 和 DATETIME的区别，百度看了下，很详细，其实主要还是 TIMESTAMP和时区有关且范围比较小。这里不做详细测试和说明了。

3.字符串类型

MySQL中的字符串类型：

3.1.CHAR和VARCHAR类型

很类似，都是保存较短的字符串，主要区别是存储方式不同：

• CHAR的长度固定为创建的时候生命的长度，可以从0~255的任意整数。
• VARCHAR的长度为可变长度，可以从0~65535之间的任意整数。
• 检索的时候，CHAR列删除尾部的空格，例如 char(4) 插入 'ab '，实际存入的是 ‘ab’，后面的两个空格没有了。而VARCHAR则保留这些空格，varchar(4) 插入 'ab '，实际存入的就是 'ab '。

3.2.ENUM类型

枚举类型，取值范围需要在创建表的时候通过枚举方式显示指定，测试如下：

如上图可以看出：

• ENUM类型是忽略大小写的，存入M和f的时候，都转成了大写。
• 对于插入一个ENUM范围中不存在的值的时候，没有返回警告和报错，而是插入ENUM范围中的第一个值。需要特别注意。
• ENUM类型只允许从值的集合中选取单个值，不能一次取多个值。

3.3.SET类型

• 和ENUM类型相似，最主要区别是SET类型一次可以取多个值，而ENUM只能选取一个值。
• SET类型的取值多个，只要是范围内的就可以，不是范围内的不允许插入。
• 如果多个取值中存在重复的，例如 ‘a,b,a’，最终写入的结果会去重，也就是 ‘a,b’。需要特别注意

4.JSON类型

MySQL自5.7.8版本开始支持JSON类型，之前都是通过VARCHAR或者TEXT来保存JSON格式的数据，JSON类型特点：

• JSON类型字段会自动校验是否为JSON格式，如果不是就会报错。
• MySQL提供了一组操作JSON数据的内置函数，可以方便的提取各类数据，可以修改特定的键值。
• 优化的存储格式，存储在JSON列中的JSON数据，被转换成内部的存储格式，允许快速读取。

一个JSON元素中，可以是六中类型元素的任意组合，这六中类型是NUMBER、STRING、BOOLEAN、NULL、ARRAY 和 OBJECT。

其中BOOLEAN使用true和false的字面值文本表示。NULL使用NULL的文本AR表示。字符串和日期类型都是使用双引号引起来表示。ARRAY要用中括号引起来。OBJECT的KV要用大括号引起来，其中KEY也要用双引号引起来。下面是几个正确的示例：

// array
["abc", 10, null, true, false]
// object
{"k1": "value1", "k2": 10}
// 
["12:18:29.000000", "2024-04-23", "2024-04-23 16:47:00.000000"]

ARRAY 和 OBJECT 还可以互相嵌套，这个不难理解。

如上图，正确的json数据被插入，错误的会直接报错。

通过 json_type函数可以查看插入的json数据是哪种类型，如下图：

json数据类型对于大小写是敏感的（因为json的默认排序规则是utf8mb4_bin），‘x’和‘X’是不同的两个json数据，常见的null、true、false必须是小写的才合法。

通过json_valid函数判断一个json是否合法，如下：

如果json数据中的value中字符串包含双引号或者单引号，需要使用反斜杠进行转义，例如插入value为 ab"c 的值，如下：

如上，双反斜杠表示一个反斜杠。类似json_valid 和 json_type等函数，这都是MySQL提供的json相关的函数操作，后续会说明。