MySql数据类型

MySQL支持多种类型，大致将其分为三类：数值、日期/时间和字符(字符串)类型。合理地定义字段的类型对 SQL 的优化是非常重要的。

下面会介绍一些常用的数据类型，关于更多的数据类型，可以参看官方的介绍：
https://dev.mysql.com/doc/refman/8.0/en/data-types.html

一、数值型

1.1整值型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT 或 INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值

MySql 中整值数据类型默认是有符号的，也就是说支持负数。下面以 INT 类型为例测试一下

创建一个数据库：

CREATE TABLE t_int (
    num INT
);

插入一条数据（负数）并查看数据结果：

如何使整值类型是无符号的呢？其实很简单，只要在列名后面加上 UNSIGNED 约束即可。下面也通过一个例子测试一下

创建一个数据表：

CREATE TABLE t_int2 (
    num INT UNSIGNED
);

插入一条数据（负数）并查看数据结果：

通过上面的图可以看出来在设置num字段为无符号的时候，数据表报错，不再允许插入负数数据。

通过查看数据表结构的时候还会发生一个变化，就是 INT的长度由默认的 11（自己可以测试一下） 变成了 10。那么 INT 类型的长度有什么用呢？首先可以确定的是，这个长度与范围无关，因为范围是数据类型决定的。 INT 类型的长度的作用是用于 0 填充，也就是说如果查询结果值的长度小于其 INT的长度，前面使用 0 填充，默认是不填充的。可以使用 ZEROFILL 约束来设置开启 0 填充。如果设置了 ZEROFILL 约束，那么这个整值数据类型变成无符号的。

通过一个例子测试一下

创建一个数据表：

CREATE TABLE t_int3 (
    num INT ZEROFILL
);

查看数据表结构：

插入一条数据（只能是有符号的）并查看数据结果：

1.2浮点型

类型	大小	范围（有符号）	范围（无符号）	用途
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值

浮点类型数值，默认也是有符号的，同样支持负数类型的数据，这里就不作测试了。FLOAT 与 DOUBLE 的精确写法是 FLOAT(M, D) 和 DOUBLE (M, D) ，M 和 D 默认是省略的。

M：表示整数位长度加上小数位的最大长度，超会报错
D：表示小数部位允许的的长度，如果不到用 0 填充，超出部分四舍五入进位或舍位

通过一个例子说明，以 FLOAT 为例

创建测试表：

# 允许整数位与小数位的最大长度为5，小数位长度为2
CREATE TABLE t_float (
    num FLOAT(5, 2)
);

插入一条数据（小数位在允许的长度内）并查看数据：

插入一条数据（小数位超出允许的长度）并查看数据：

插入一条数据（整数位加小数位超出允许的最大长度）并查看数据：

一般我们在使用浮点类型数据的时候，不用设置 M 和 D 的值，默认的情况下 MySql 自己会根据用户插入数据的精度来决定精度。只有在一些特殊需求的情况下才需要设置 M 和 D。

1.3定点型

类型	大小	范围（有符号）	范围（无符号）	用途
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

DECIMAL 类型的数据，M 默认为 10，D 默认为 0。其使用方法与浮点型类似，实际使用的并不多，所以这里就不作具体介绍了。

二、字符串型

2.1字符型

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYTEXT	0-255字符	短文本字符串
TEXT	0-65 535字符	长文本数据
MEDIUMTEXT	0-16 777 215字符	中等长度文本数据
LONGTEXT	0-4 294 967 295字符	极大文本数据

CHAR 和 VARCHAR 类似，都可以用来存储字符类型的数据，区别是 CHAR 用来存储定长的字符串， 如果不指定长度，CHAR 类型的数据长度默认为 1， VARCHAR 用来存储变长的字符串，且必须要指定初始化长度。

关于定长与变长的概念，我们举例说明，假设 CHAR 与 VARCHAR 的长度都是允许存储 10 个字节，将“张三”这条数据分别插入对应的数据类型，那么“张三”在 CHAR 中占用全部的 10 个字节，6 个字节的数据与 4 个字节的空格填充，而在 VARCHAR 中只占用 6 个字节。

我们应该根据数据的特征正确的选择 CHAR 与 VARCHAR 。定长数据比如：身份证号，手机号，性别等，应该选择 CHAR，变长数据比如：昵称，密码等，应该选择 VARCHAR。

TEXT 对应着四种类型，这里就不作具体解释了。

还有一点需要注意的是 MySql 5.0 以上的版本， CHAR(M) 和 VARCHAR(M) 中的 M 不再是字节而是字符，但是其长度仍然不能超过对应的字节数。下面通过举例说明

创建测试表：

CREATE TABLE t_varchar(
    name VARCHAR(3)
);

插入一条数据（字节数大于3）并查看数据：

查看张三丰的字节数：

“张三丰”占 9 个字节的空间大小，在设置 VARCHAR 大小的时候是 3， 因此VARCHAR 的大小是字符而不是字节。

2.2二进制型

类型	大小	用途
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
BLOB	0-65 535字节	二进制形式的长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据

二进制类型还有 BINARY 和 VARBINARY，表用小的二进制字节串。它们两个的区别与 CHAR 和 VARCHAR类似， BINARY 表示定长的二进制字符串，VARBINARY 表示变长的二进制字符串。

BLOB 类型是一个二进制大对象，可以容纳可变数量的数据。可用于存储图片，文件等，但是事实上我们并不这么做，因为大二进制数据在读取的时候速度很慢。一般都是在数据库中存储这些文件的路径，通过路径获得对应的文件。

2.3枚举型

枚举类型比较特殊，类似于 Java 中的枚举，要求插入的数据必须是列表中指定的值之一，并且支持英文大小写的转换，否则会报错。下面通过一个案例来说明

创建测试表：

CREATE TABLE t_enum(
    sex ENUM('f', 'm')
);

插入三条数据（列表中指定的数据）并查看数据：

插入一条数据（没有列表中指定的数据）并查看数据：

三、日期型

类型	大小 (字节)	范围	格式	用途
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 -01-01 00:00:00	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

通过上面的图，很容易看出它们的区别。只有 DATETIME 与 TIMESTAMP都可以用于表示精确的时间，区别是字节大小不一样，并且TIMESTAMP 会跟随设置的时区变化而变化，而 DATETIME 保存的是绝对不会变化的值。

PS：基础知识很重要，希望大家能够重视。