数字类型 （Numeric Data Types）
Integer Type (Exact Value)
Difference is in range and storage. Choose unsigned to maximise the maximum value if not using negative values.
- TINYINT：一个微小整数，支持 -128到127（SIGNED），0到255（UNSIGNED），需要1个字节存储
- SMALLINT：一个小整数，支持 -32768到32767（SIGNED），0到65535（UNSIGNED)，需要2个字节存储
- MEDIUMINT：一个中等整数，支持 -8388608到8388607（SIGNED），0到16777215（UNSIGNED），需要3个字节存储
- INT：一个整数，支持 -2147493648到2147493647（SIGNED），0到4294967295（UNSIGNED），需要4个字节存储
- INTEGER：同INT
- BIGINT：一个大整数，支持 -9223372036854775808到9223372036854775807（SIGNED），0到18446744073709551615（UNSIGNED），需要8个字节存储
注意，默认为SIGNED，只有显式地声明为UNSIGNED，才是UNSIGNED。
| Type | Storage (Bytes) | Minimum Value Signed | Minimum Value Unsigned | Maximum Value Signed | Maximum Value Unsigned |
|---|---|---|---|---|---|
TINYINT |
1 | -128 |
0 |
127 |
255 |
SMALLINT |
2 | -32768 |
0 |
32767 |
65535 |
MEDIUMINT |
3 | -8388608 |
0 |
8388607 |
16777215 |
INT |
4 | -2147483648 |
0 |
2147483647 |
4294967295 |
BIGINT |
8 | -2$^{63}$ | 0 |
2$^{63}$-1 | 2$^{64}$-1 |
See https://dev.mysql.com/doc/refman/5.6/en/integer-types.html for more details.
显示宽度
当我们声明一个类型为int的列时候，需要指定它的宽度：
CREATE TABLE `user_tab` (
`userid` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(128) NOT NULL DEFAULT '',
`age` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`userid`),
UNIQUE KEY `name` (`name`),
KEY `age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4
注意，这里int(m) 里的m是表示使用SELECT以查询结果集时，显示该列中值的宽度。值得一提的是，这个m并不影响实际的取值范围。
我们只需要记住两点：
- 无论N等于多少，int永远占4个字节。
- N表示的是显示宽度：当当前值的长度未达到设置的显示宽度时，不足的宽度用0（如果该整型字段设置了unsigned zerofill。）或者空格补足
- 比如当前行在这个列的值为1，而N为5，则使用select显示这个值时，就是00001）
- 如果当前值的长度超过设置的显示宽度时（比如当前行在这个列的值为11111111，而N为5，则使用select显示这个值时，就是11111）不过，这里还有个前提，该整型字段设置了unsigned zerofill。
- 如果该整型字段未设置unsigned zerofill，则直接显示原始值，不做补零操作
实验，在没有设置 unsigned zerofill 时：
mysql> drop table if exists test_int_width;
Query OK, 0 rows affected (0.00 sec)
mysql> create table test_int_width (
-> a int(5),
-> b int(5),
-> c int(5) ,
-> d int(8)
-> ) engine=innodb charset=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> insert into test_int_width values(1, 1, 1, 1111111111);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from test_int_width;
+------+------+------+------------+
| a | b | c | d |
+------+------+------+------------+
| 1 | 1 | 1 | 1111111111 |
+------+------+------+------------+
1 row in set (0.00 sec)
在设置了 unsigned zerofill 之后：
mysql> create table test_int_width (
-> a int(5),
-> b int(5) unsigned,
-> c int(5) unsigned zerofill,
-> d int(8) unsigned zerofill
-> ) engine=innodb charset=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> insert into test_int_width values(1, 1, 1, 1111111111);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from test_int_width;
+------+------+-------+------------+
| a | b | c | d |
+------+------+-------+------------+
| 1 | 1 | 00001 | 1111111111 |
+------+------+-------+------------+
1 row in set (0.00 sec)
定点类型 - Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC
定点类型在数据库中存放的是精确值（相对比的是，浮点类型在数据库中存放的是近似值）。
decimal(m,d)：参数m （要求m < 65） 指定总个数，参数d （要求d<30且 d<m）指定小数位。
| 类型名称 | 说明 | 存储需求 |
|---|---|---|
| DECIMAL(M,D) | 压缩的“严格”定点数 | M+2个字节（在不指定M、D时默认为decimal(10, 0)） |
See https://dev.mysql.com/doc/refman/5.6/en/fixed-point-types.html for more details.
实验：
drop table if exists test_decimal;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table test_decimal (
-> float_num float(10, 2),
-> double_num double(20, 2),
-> decimal_num decimal(20, 2)
-> ) engine=innodb charset=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> insert into test_decimal values(1234567.66, 1234567899000000.66, 1234567899000000.66);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_decimal values(1234567.66, 12345678990000000.66, 12345678990000000.66);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_decimal;
+------------+----------------------+----------------------+
| float_num | double_num | decimal_num |
+------------+----------------------+----------------------+
| 1234567.62 | 1234567899000000.80 | 1234567899000000.66 |
| 1234567.62 | 12345678990000000.00 | 12345678990000000.66 |
+------------+----------------------+----------------------+
2 rows in set (0.00 sec)
看到float、double类型存在精度丢失问题，即写入数据库的数据未必是插入数据库的数据，而decimal无论写入数据中的数据是多少，都不会存在精度丢失问题，这就是我们要引入decimal类型的原因，decimal类型常见于银行系统、互联网金融系统等对小数点后的数字比较敏感的系统中。
最后讲一下decimal和float/double的区别，个人总结主要体现在两点上：
- float/double在DB中存储的是近似值，而decimal则是以字符串形式进行保存的
- decimal(M,D)的规则和float/double相同，但区别在float/double在不指定M、D时默认按照实际精度来处理；而decimal在不指定M、D时默认为decimal(10, 0)
浮点类型 - Floating-Point Types (Approximate Value) - FLOAT, DOUBLE
| MySQL数据类型 | 含义 |
|---|---|
| float(m,d) | 单精度浮点型 8位精度（4字节），m总个数，d小数位数（在不指定M、D时默认按照实际精度来处理） |
| double(m,d) | 双精度浮点型 16位精度（8字节） m总个数，d小数位数（在不指定M、D时默认按照实际精度来处理） |
注意，当指定一个字段的类型为float或者double时，需要指定总个数和小数位数。
设一个字段定义为float(5,3)，如果插入一个为123.45678的数，则实际存入DB的是123.457（即存入时四舍五入，并忽略多出的小数部分）。但如果插入了一个多于指定的总个数的位数的数（会直接出现Out of range value for column 错误）。
实验：
mysql> drop table if exists test_float;
Query OK, 0 rows affected (0.01 sec)
mysql> create table test_float (
-> num float(5, 2)
-> ) engine=innodb charset=utf8;
Query OK, 0 rows affected (0.01 sec)
# 存入时，发生了忽略多出的小数部分的操作
mysql> insert into test_float values(1.233);
Query OK, 1 row affected (0.00 sec)
# 存入时，发生了四舍五入
mysql> insert into test_float values(1.237);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_float values(10.233);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_float values(100.233);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_float values(1000.233);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into test_float values(10000.233);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into test_float values(100000.233);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql>
mysql> select * from test_float;
+--------+
| num |
+--------+
| 1.23 |
| 1.24 |
| 10.23 |
| 100.23 |
+--------+
4 rows in set (0.00 sec)
See https://dev.mysql.com/doc/refman/5.6/en/floating-point-types.html for more details.
Bit-Value Type - BIT
See https://dev.mysql.com/doc/refman/5.6/en/bit-type.html for more details.
Date and Time Data Types
| MySQL数据类型 | 字节长度 | 含义 |
|---|---|---|
| date | 3 | 以YYYY-MM-DD的格式显示，比如：2009-07-19 |
| time | 3 | 以HH:MM:SS的格式显示。比如：11:22:30 |
| datetime | 8 | 以YYYY-MM-DD HH:MM:SS的格式显示，比如：2009-07-19 11:22:30 |
| timestamp | 4 | 2015-05-01 11:12:00 |
| year | 4 | 2015 |
MySQL的时间类型的知识点比较简单，这里重点关注一下datetime与timestamp两种类型的区别：
- 上面列了，datetime占8个字节，timestamp占4个字节
- 由于大小的区别，datetime与timestamp能存储的时间范围也不同，datetime的存储范围为1000-01-01 00:00:00——9999-12-31 23:59:59，timestamp存储的时间范围为19700101080001——20380119111407
- datetime默认值为空，当插入的值为null时，该列的值就是null；timestamp默认值不为空，当插入的值为null的时候，mysql会取当前时间
- datetime存储的时间与时区无关，timestamp存储的时间及显示的时间都依赖于当前时区
mysql> drop table if exists test_time;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table test_time (
-> date_value date,
-> time_value time,
-> year_value year,
-> datetime_value datetime,
-> timestamp_value timestamp
-> ) engine=innodb charset=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> insert into test_time values(now(), now(), now(), now(), now());
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from test_time;
+------------+------------+------------+---------------------+---------------------+
| date_value | time_value | year_value | datetime_value | timestamp_value |
+------------+------------+------------+---------------------+---------------------+
| 2020-04-25 | 22:06:54 | 2020 | 2020-04-25 22:06:54 | 2020-04-25 22:06:54 |
+------------+------------+------------+---------------------+---------------------+
1 row in set (0.00 sec)
See https://dev.mysql.com/doc/refman/5.6/en/date-and-time-types.html for more details.
String Data Types
Spatial Data Types
String
| MySQL数据类型 | 含义 |
|---|---|
| char(n) | 固定长度（无论字符实际长度是多少，都会按照指定的长度存储，不够的用空格补足），n指定字符长度，最大为255（因此最多255个字符）。 |
| varchar(n) | 可变长度字符串，固定长度，最多65535个字符 |
| tinytext | 可变长度，最多255个字符 |
| text | 可变长度，最多65535个字符 |
| mediumtext | 可变长度，最多2的24次方-1个字符 |
| longtext | 可变长度，最多2的32次方-1个字符 |
char和varchar
char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格（否则添加的空格在被查询时会被移除掉），varchar则无这个要求。
实验：
mysql> drop table if exists test_string;
Query OK, 0 rows affected (0.01 sec)
mysql> create table test_string (
-> char_value char(5),
-> varchar_value varchar(5)
-> ) engine=innodb charset=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> insert into test_string values('a', 'a');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_string values(' a', ' a');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_string values('a ', 'a ');
Query OK, 1 row affected (0.01 sec)
mysql> insert into test_string values(' a ', ' a ');
Query OK, 1 row affected (0.00 sec)
mysql> select length(char_value), length(varchar_value) from test_string;
+--------------------+-----------------------+
| length(char_value) | length(varchar_value) |
+--------------------+-----------------------+
| 1 | 1 |
| 2 | 2 |
| 1 | 2 |
| 2 | 3 |
+--------------------+-----------------------+
4 rows in set (0.01 sec)
这验证了我们的结论，对char类型的数据，插入时会被包含空格以存储，但是在被查询处理时，这些空格不会被包含char(n) 固定长度，其中n指定了长度。对于类型为char(4)的数据，不管是存入的数据包含几个字符，最终该数据都会占用4个字节。
varchar是存入的实际字符数+1个字节（n<=255）或2个字节（n>255），所以varchar(4)，存入一个占用3个字符的字符串，将占用4个字节。
char类型的字符串检索速度要比varchar类型的快。
varchar和text
- varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（当 n<=255 时）或2个字节（当 n>255 时），text是实际字符数+2个字节。
- text类型不能有默认值。
- varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text
- 在都创建索引的情况下，text的索引似乎不起作用。
varchar
我们来探究一下varchar型数据实际占用空间大小是如何计算的以及最大可容纳的字符串为多少，首先要给出结论：
- 实际占用空间大小和当前使用的编码方式有关。
- 最大可容纳的字符串也和当前使用的编码方式有关。
先写一段SQL创建表，utf8的编码格式：
drop table if exists test_varchar;
create table test_varchar (
varchar_value varchar(100000)
) engine=innodb charset=utf8;
执行报错：
Column length too big for column 'varchar_value' (max = 21845); use BLOB or TEXT instead
按照提示，我们把大小改为21845，执行依然报错：
Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
改为21844就不会有问题（这其实是因为，MySQL要求一个行的定义长度不能超过65535 Bytes （64KB）），因此在utf8编码下我们可以知道varchar(M)，M最大=21844。
那么gbk呢：
drop table if exists test_varchar;
create table test_varchar (
varchar_value varchar(100000)
) engine=innodb charset=gbk;
同样的报错：
Column length too big for column 'varchar_value' (max = 32767); use BLOB or TEXT instead
把大小改为32766，也是和utf8编码格式一样的报错：
Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
可见gbk的编码格式下，varchar(M)最大的M=32765。
text和blob
最后讲一讲text和blob两种数据类型，它们的设计初衷是为了存储大数据使用的，因为之前说了，MySQL单行最大数据量为64KB。
先说一下text，text和varchar是一组既有区别又有联系的数据类型，其联系在于当varchar(M)的M大于某些数值时，varchar会自动转为text：
- M>255时，转为tinytext
- M>500时，转为text
- M>20000时，转为mediumtext
所以过大的内容varchar和text没有区别，同时varchar(M)和text的区别在于：
- 单行64K即65535字节的空间，varchar只能用63352/65533个字节，但是text可以65535个字节全部用起来
- text可以指定text(M)，但是M无论等于多少都没有影响
- text不允许有默认值，varchar允许有默认值
varchar和text两种数据类型，使用建议是能用varchar就用varchar而不用text（存储效率高），varchar(M)的M有长度限制，之前说过，如果大于限制，可以使用mediumtext（16M）或者longtext（4G）。
至于text和blob，简单过一下就是text存储的是字符串而blob存储的是二进制字符串，简单说blob是用于存储例如图片、音视频这种文件的二进制数据的。
二进制数据（Blob）
- Blob 和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而 Blob 是以二进制方式存储，不分大小写。
- Blob 存储的数据只能整体读出。
- TEXT可以指定字符集，Blob 不用指定字符集。
enum类型
enum类型又称为枚举类型，在创建表时，enum类型的取值范围就以列表的形式指定了。其基本形式如下：属性名 enum(‘值1’,‘值2’,…,‘值n’)，其中属性名参数指定字段的名称，‘值n’参数表示列表中的第n个值，这些值末尾的空格将会被系统直接删除。注意：
- enum类型的值只能去列表中的一个元素，其取值列表中最多只能有65535个值。列表中的每个值都有一个顺序排列的编号，MySQL中存入的是这个编号，而不是列表中的值。
- 如果enum类型加上了not null属性，其默认值为取值列表中的第一个元素。如果不加not null属性，enum类型将允许插入null，而且null为默认值。
Reference
- https://dev.mysql.com/doc/refman/5.6/en/data-types.html
- https://dev.mysql.com/doc/refman/8.0/en/data-types.html
- https://blog.csdn.net/bzhxuexi/article/details/43700435
- https://www.cnblogs.com/xrq730/p/8446246.html