第3章 MySQL支持的数据类型
3.1 数值类型：包括整数和小数
数值类型主要用来存储数字，不同的数值类型提供不同的取值范围，可以存储的值范围越大，所需要的存储空间也越大。MySQL支持所有标准SQL中的数值类型，其中包括严格数据类型（INTEGER、SMALLINT、DECIMAL、NUMERIC）和近似数值类型（FLOAT、REAL、DOUBLE PRECISION）。MySQL还扩展了TINYINT、MEDIUMINT和BIGINT等3种不同长度的整数类型，并增加了BIT类型，用来存储位数据。
对于MySQL中的数值类型，还要做如下说明：
- 关键字INT是INTEGER的同义词。
- 关键字DEC和FIXED是DECIMAL的同义词。
- NUMERIC和DECIMAL类型被视为相同的数据类型。
- DOUBLE视为DOUBLE PRECISION的同义词，并在REAL_AS_FLOAT SQL模式未启用的情况下，将REAL也视为DOUBLE PRECISION的同义词。
1、整数类型
说明：
对于整数类型，MySQL还支持在类型名称后面加小括号(M)，而小括号中的M表示显示宽度，M的取值范围是(0, 255)。int(M)这个M在字段的属性中指定了unsigned（无符号）和zerofill（零填充）的情况下才有意义。表示当整数值不够M位时，用0填充。如果整数值超过M位但是没有超过当前数据类型的范围时，就按照实际位数存储。当M宽度超过当前数据类型可存储数值范围的最大宽度时，也是以实际存储范围为准。
MySQL8之前，int没有指定(M)，默认显示(11)。最多能存储和显示11位整数。从MySQL 8.0.17开始，整数数据类型没有zerofill的情况下不推荐使用显示宽度属性，默认显示int。
#演示整数类型
#创建一个表格，表格的名称“t_int”，
#包含两个字段i1和i2，分别是int和int(2)类型
#create table t_int(i1 int,i2 int(2));
create table t_int(
i1 int,
i2 int(2) #没有unsigned zerofill，(2)没有意义
);
#查看当前数据库的所有表格
show tables;
show tables from 数据库名;
#查看表结构
desc 表名称;
desc t_int;
mysql> desc t_int;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| i1 | int | YES | | NULL | |
| i2 | int | YES | | NULL | |
+-------+------+------+-----+---------+-------+
2 rows in set (0.01 sec)
#创建一个表格，表格的名称“t_int2”，
#包含两个字段i1和i2，分别是int和int(2)类型
create table t_int2(
i1 int,
i2 int(2) unsigned zerofill
);
mysql> desc t_int2;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| i1 | int | YES | | NULL | |
| i2 | int(2) unsigned zerofill | YES | | NULL | |
+-------+--------------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
#添加数据到表格中
insert into 表名称 values(值列表);
insert into t_int values(1234,1234);
insert into t_int2 values(1234,1234);
#查询数据
select * from 表名称;
select * from t_int;
select * from t_int2;
#添加数据到表格中
insert into 表名称 values(值列表);
insert into t_int values(1,1);
insert into t_int2 values(1,1);
insert into t_int values(12222228854225548778455,12222228854225548778455);
mysql> insert into t_int values(12222228854225548778455,12222228854225548778455);
ERROR 1264 (22003): Out of range value for column 'i1' at row
2、bit类型
bit类型，如果没有指定(M)，默认是1位。这个1位，那么表示只能存1位的二进制值。这里(M)是表示二进制的位数。M范围从1到64。
对于位类型字段，之前版本直接使用SELECT语句将不会看到结果，而在MySQL8版本中默认以“0X”开头的十六进制形式显示，可以通过BIN()函数显示为二进制格式。
#演示bit类型，存储二进制，只有0和1
#创建一个表格
create table t_bit(
b1 bit, #没有指定(M)，默认是1位二进制
b2 bit(4) #能够存储4位二进制0000~1111
);
#查看表结构
desc t_bit;
mysql> desc t_bit;
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| b1 | bit(1) | YES | | NULL | |
| b2 | bit(4) | YES | | NULL | |
+-------+--------+------+-----+---------+-------+
2 rows in set (0.01 sec)
#添加记录
insert into t_bit values(1,1);
#查看数据
select * from t_bit;
mysql> select * from t_bit;
+------------+------------+
| b1 | b2 |
+------------+------------+
| 0x01 | 0x01 | #0x开头表示十六进制
+------------+------------+
1 row in set (0.00 sec)
#显示二进制值，需要使用bin函数
select bin(b1),bin(b2) from t_bit;
mysql> select bin(b1),bin(b2) from t_bit;
+---------+---------+
| bin(b1) | bin(b2) |
+---------+---------+
| 1 | 1 |
+---------+---------+
1 row in set (0.00 sec)
#添加记录
insert into t_bit values(2,2);
mysql> insert into t_bit values(2,2);
#values()中是十进制值，需要转为二进制存储，2对应10，超过1位，b1存不下
ERROR 1406 (22001): Data too long for column 'b1' at row 1
#添加记录
insert into t_bit values(1,8);
#查看数据
select * from t_bit;
mysql> select * from t_bit;
+------------+------------+
| b1 | b2 |
+------------+------------+
| 0x01 | 0x01 |
| 0x01 | 0x08 |
+------------+------------+
2 rows in set (0.00 sec)
#显示二进制值，需要使用bin函数
select bin(b1),bin(b2) from t_bit;
mysql> select bin(b1),bin(b2) from t_bit;
+---------+---------+
| bin(b1) | bin(b2) |
+---------+---------+
| 1 | 1 |
| 1 | 1000 |
+---------+---------+
2 rows in set (0.00 sec)
#添加记录
insert into t_bit values(1,16); #16的二进制10000
mysql> insert into t_bit values(1,16);
ERROR 1406 (22001): Data too long for column 'b2' at row 1
3、小数类型
MySQL中使用浮点数和定点数来表示小数。浮点数有两种类型：单精度浮点数（FLOAT）和双精度浮点数（DOUBLE），定点数只有DECIMAL。浮点数和定点数都可以用(M，D)来表示。
- M是精度，表示该值总共显示M位，包括整数位和小数位，对于FLOAT和DOUBLE类型来说，M取值范围为0
255，而对于DECIMAL来说，M取值范围为065。 - D是标度，表示小数的位数，取值范围为0~30，同时必须<=M。
浮点型FLOAT(M，D) 和DOUBLE(M，D)是非标准用法，如果考虑到数据库迁移，则最好不要使用，而且从MySQL 8.0.17开始，FLOAT(M，D) 和DOUBLE(M，D)用法在官方文档中已经明确不推荐使用，将来可能被移除。另外，关于浮点型FLOAT和DOUBLE的UNSIGNED也不推荐使用了，将来也可能被移除。FLOAT和DOUBLE类型在不指定（M，D）时，默认会按照实际的精度来显示。DECIMAL类型在不指定（M，D）时，默认为（10，0），即只保留整数部分。例如，定义DECIMAL（5,2）的类型，表示该列取值范围是-999.99~999.99。如果用户插入数据的小数部分位数超过D位，MySQL会四舍五入处理，但是如果用户插入数据的整数部分位数超过“M-D”位，则会报“Out of range”的错误。
DECIMAL实际是以字符串形式存放的，在对精度要求比较高的时候（如货币、科学数据等）使用DECIMAL类型会比较好。浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围，它的缺点是会引起精度问题。
#演示小数类型
#创建表格
create table t_double(
d1 double,
d2 double(5,2) #-999.99~999.99
);
#查看表结构
desc t_double;
#添加数据
insert into t_double values(2.5,2.5);
#查看数据
select * from t_double;
mysql> select * from t_double;
+------+------+
| d1 | d2 |
+------+------+
| 2.5 | 2.50 |#d2字段小数点后不够2位用0补充
+------+------+
1 row in set (0.00 sec)
#添加数据
insert into t_double values(2.5526,2.5526);
insert into t_double values(2.5586,2.5586);
mysql> select * from t_double;
+--------+------+
| d1 | d2 |
+--------+------+
| 2.5 | 2.50 |
| 2.5526 | 2.55 |#小数点后有截断现象，并且会四舍五入
| 2.5586 | 2.56 |#小数点后有截断现象，并且会四舍五入
+--------+------+
3 rows in set (0.00 sec)
#添加数据
insert into t_double values(12852.5526,12852.5526);
#d2字段整数部分超过(5-2=3)位，添加失败
mysql> insert into t_double values(12852.5526,12852.5526);
ERROR 1264 (22003): Out of range value for column 'd2' at row 1
#创建表格
create table t_decimal(
d1 decimal, #没有指定(M,D)默认是(10,0)
d2 decimal(5,2)
);
#查看表结构
desc t_decimal;
mysql> desc t_decimal;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| d1 | decimal(10,0) | YES | | NULL | |
| d2 | decimal(5,2) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
#添加数据
insert into t_decimal values(2.5,2.5);
#查看数据
select * from t_decimal;
mysql> select * from t_decimal;
+------+------+
| d1 | d2 |
+------+------+
| 3 | 2.50 | #d1字段小数点后截断
+------+------+
1 row in set (0.00 sec)
insert into t_decimal values(12852.5526,12852.5526);
把小数赋值给整数类型的字段时，会截断小数部分，考虑四舍五入
insert into t_int2 values(1.5,1.5);
3.2 字符串类型
MySQL的字符串类型有CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM、SET等。MySQL的字符串类型可以用来存储文本字符串数据，还可以存储二进制字符串。
文本字符串类型：
二进制字符串类型：
1、char和varchar
CHAR(M)为固定长度的字符串， M表示最多能存储的字符数，取值范围是0~255个字符，如果未指定(M)表示只能存储1个字符。例如CHAR(4)定义了一个固定长度的字符串列，其包含的字符个数最大为4，如果存储的值少于4个字符，右侧将用空格填充以达到指定的长度，当查询显示CHAR值时，尾部的空格将被删掉。
create table temp(
c1 char,
c2 char(3)
);
insert into temp values('男','女');#成功
insert into temp values('哈哈哈','哈哈哈');#失败
ERROR 1406 (22001): Data too long for column 'c1' at row 1
insert into temp values('男','哈哈哈');#成功
VARCHAR(M)为可变长度的字符串，M表示最多能存储的字符数，M的范围由最长的行的大小（通常是65535）和使用的字符集确定。例如utf8mb4字符编码单个字符所需最长字节值为4个字节，所以M的范围是[0, 16383]。而VARCHAR类型的字段实际占用的空间为字符串的实际长度加1或2个字节，这1或2个字节用于描述字符串值的实际字节数，即字符串值在[0,255]个字节范围内，那么额外增加1个字节，否则需要额外增加2个字节。
create table temp(
name varchar #错误
);
create table temp(
name varchar(3) #最多不超过3个字符
);
insert into temp values('哈哈哈');
insert into temp values('哈哈哈哈哈');#ERROR 1406 (22001): Data too long for column 'name' at row 1
insert into temp values('好');
drop table temp;
create table temp(
name varchar(65535)
);
#ERROR 1074 (42000): Column length too big for column 'name' (max = 21845); use BLOB or TEXT instead
因为当前的表是UTF8，一个汉字占3个字节
例如，身份证号、手机号码、QQ号、用户名username、密码password、银行卡号等固定长度的文本字符串适合使用CHAR类型，而评论、朋友圈、微博不定长度的文本字符串更适合使用VARCHAR类型。
另外，存储引擎对于选择CHAR和VARCHAR是有影响的。
- 对于MyISAM存储引擎，最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化，从而使数据检索更快，用空间换时间。
- 对于InnoDB存储引擎，使用可变长度的数据列，因为InnoDB数据表的存储格式不分固定长度和可变长度，因此使用CHAR不一定比使用VARCHAR更好，但由于VARCHAR是按照实际的长度存储的，比较节省空间，所以对磁盘I/O和数据存储总量比较好。
2、Enum和Set类型
无论是数值类型、日期类型、普通的文本类型，可取值的范围都非常大，但是有时候我们指定在固定的几个值范围内选择一个或多个，那么就需要使用ENUM枚举类型和SET集合类型了。比如性别只有“男”或“女”；上下班交通方式可以有“地铁”、“公交”、“出租车”、“自行车”、“步行”等。枚举和集合类型字段声明的语法格式如下：
字段名ENUM（‘值1’，‘值2’，…‘值n’）
字段名 SET（‘值1’，‘值2’，…‘值n’）
ENUM类型的字段在赋值时，只能在指定的枚举列表中取值，而且一次只能取一个。枚举列表最多可以有65535个成员。ENUM值在内部用整数表示，每个枚举值均有一个索引值， MySQL存储的就是这个索引编号。例如，定义ENUM类型的列(‘first’, ‘second’, ‘third’)。
SET类型的字段在赋值时，可从定义的值列表中选择1个或多个值的组合。SET列最多可以有64个成员。SET值在内部也用整数表示，分别是1，2，4，8……，都是2的n次方值，因为这些整数值对应的二进制都是只有1位是1，其余是0。
演示枚举类型：
create table temp(
gender enum('男','女'),
hobby set('睡觉','打游戏','泡妞','写代码')
);
insert into temp values('男','睡觉,打游戏'); #成功
insert into temp values('男,女','睡觉,打游戏'); #失败
#ERROR 1265 (01000): Data truncated for column 'gender' at row 1
insert into temp values('妖','睡觉,打游戏');#失败
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
insert into temp values('男','睡觉,打游戏,吃饭');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
#文本类型中的枚举和集合
#枚举：固定的几个字符串值，从中选择一个
#集合：固定的几个字符串值，从中选择任意几个
create table t_enum_set(
gender enum('男','女'),
hobby set('游戏','睡觉','打代码','运动')
);
#查看表结构
desc t_enum_set;
mysql> desc t_enum_set;
+--------+------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------------------------+------+-----+---------+-------+
| gender | enum('男','女') | YES | | NULL | |
| hobby | set('游戏','睡觉','打代码','运动') | YES | | NULL | |
+--------+------------------------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
#添加数据
insert into t_enum_set
values('男','游戏');
#查看数据
select * from t_enum_set;
#添加数据
insert into t_enum_set
values('男,女','游戏,睡觉');
mysql> insert into t_enum_set
-> values('男,女','游戏,睡觉');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
#添加数据
insert into t_enum_set
values('男','游戏,睡觉');
#添加数据
insert into t_enum_set
values('妖','游戏,睡觉');
mysql> insert into t_enum_set
-> values('妖','游戏,睡觉');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
#添加数据
insert into t_enum_set
values('男','游戏,睡觉,做饭');
mysql> insert into t_enum_set
-> values('男','游戏,睡觉,做饭');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
insert into t_enum_set
values(2, 2);
mysql> select * from t_enum_set;
+--------+-----------+
| gender | hobby |
+--------+-----------+
| 男 | 游戏 |
| 男 | 游戏,睡觉 |
| 女 | 睡觉 |
+--------+-----------+
3 rows in set (0.00 sec)
insert into t_enum_set
values(2, 5);
#5 可以看出 1和4的组合，00001 和 0100，0101
insert into t_enum_set
values(2, 7);
mysql> select * from t_enum_set;
+--------+------------------+
| gender | hobby |
+--------+------------------+
| 男 | 游戏 |
| 男 | 游戏,睡觉 |
| 女 | 睡觉 |
| 女 | 游戏,打代码 |
| 女 | 游戏,睡觉,打代码 |
+--------+------------------+
5 rows in set (0.00 sec)
insert into t_enum_set
values(2, 15);
mysql> select * from t_enum_set;
+--------+-----------------------+
| gender | hobby |
+--------+-----------------------+
| 男 | 游戏 |
| 男 | 游戏,睡觉 |
| 女 | 睡觉 |
| 女 | 游戏,打代码 |
| 女 | 游戏,睡觉,打代码 |
| 女 | 游戏,睡觉,打代码,运动 |
+--------+-----------------------+
6 rows in set (0.00 sec)
insert into t_enum_set
values(2, 25);
mysql> insert into t_enum_set
-> values(2, 25);
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
3、BINARY和VARBINARY类型
BINARY和VARBINARY类似于CHAR和VARCHAR，只是它们存储的是二进制字符串。
BINARY (M)为固定长度的二进制字符串，M表示最多能存储的字节数，取值范围是0~255个字节，如果未指定(M)表示只能存储1个字节。例如BINARY (8)，表示最多能存储8个字节，如果字段值不足(M)个字节，将在右边填充'\0'以补齐指定长度。
VARBINARY (M)为可变长度的二进制字符串，M表示最多能存储的字节数，总字节数不能超过行的字节长度限制65535，另外还要考虑额外字节开销，VARBINARY类型的数据除了存储数据本身外，还需要1或2个字节来存储数据的字节数。VARBINARY类型和VARCHAR类型一样必须指定(M)，否则报错。
#演示二进制字符串类型binary和varbinary
#创建表格
create table t_binary(
b1 binary, #没有指定(M)，默认是(1)
b2 varbinary #没有指定(M)，报错，必须指定(M)
);
ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server
version for the right syntax to use near ')' at line 4
create table t_binary(
b1 binary, #默认(1)，最多能存储一个字节
b2 binary(6), #最多能存储6个字节，不够6个用\u0000补全
b3 varbinary(6) #(6)，最多能存储6个字节
);
#查看表结构
desc t_binary;
mysql> desc t_binary;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| b1 | binary(1) | YES | | NULL | |
| b2 | binary(6) | YES | | NULL | |
| b3 | varbinary(6) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
#添加数据
insert into t_binary
values('a','a','a');
#查看数据
select * from t_binary;
#显示16进制形式的值
mysql> select * from t_binary;
+------------+----------------+------------+
| b1 | b2 | b3 |
+------------+----------------+------------+
| 0x61 | 0x610000000000 | 0x61 |
+------------+----------------+------------+
1 row in set (0.00 sec)
#'a'的编码值是97（十进制），对应十六进制（61）
#0x610000000000 补够6个字节
#添加数据
insert into t_binary
values('哈','哈','哈');
mysql> insert into t_binary
-> values('哈','哈','哈');
ERROR 1406 (22001): Data too long for column 'b1' at row 1
#'尚'无论在GBK还是UTF8编码下都不可能是1个字节
#添加数据
insert into t_binary
values('a','哈哈哈','哈哈哈');
mysql> select * from t_binary;
+------------+----------------+----------------+
| b1 | b2 | b3 |
+------------+----------------+----------------+
| 0x61 | 0x610000000000 | 0x61 |
| 0x61 | 0xC9D0B9E8B9C8 | 0xC9D0B9E8B9C8 | #此时客户端是GBK，哈哈哈编码为6个字节
+------------+----------------+----------------+
2 rows in set (0.00 sec)
#添加数据
insert into t_binary
values('a','很开心真好','今天天气真的真好');
mysql> insert into t_binary
-> values('a','很开心真好','今天天气真的很好');
ERROR 1406 (22001): Data too long for column 'b2' at row 1
