数字类型 （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 |
0 |
2 |
2 |
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，则以空格补于其后。
- 存入时，如果字符串结尾存在空格，则在存入时，结尾的空格仍会被保留
- 但是，在查询时，查询出的结果是已经去掉了字符串后存在的空格的新字符串
- 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 个字节。
在只考虑 single-byte character set (like latin1)，对于 varchar 类型的数据占用的字节数，是
- 当 n <= 255 时，是
存入的实际字符数 + 1个字节 - 当 n > 255 时，是
存入的实际字符数 + 2个字节
所以 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 型数据实际占用空间大小是如何计算的，以及最大可容纳的字符串为多少。
首先给出结论：
-
实际占用空间大小
- 和当前使用的编码方式（Character Set）有关
-
最大可容纳的字符长度，和下面两个因素有关
- Character Set: The maximum effective length of a
VARCHARis subject to the maximum row size in MySQL, which is 65,535 bytes (for the entire row) and also depends on the character set used. For example, if you use a single-byte character set likelatin1, the maximum length of aVARCHARcan technically be close to 65,535. However, for multi-byte character sets likeutf8mb4, which requires up to 4 bytes per character, the maximum length would be less. - Row Size Limit: Because the total length of all columns in a MySQL table cannot exceed 65,535 bytes, using a
VARCHARwith a very high limit might restrict the addition of other columns or limit the lengths that can be used in otherVARCHARcolumns within the same table. - In practical terms, it’s common to see
VARCHARlengths defined up to 255, 512, 1024, or 2048 characters when using multi-byte character sets, to ensure compatibility and performance within the typical row size limitations.
- Character Set: The maximum effective length of a
varchar 型数据 的 最大可容纳的字符长度 - 实验 1
以下是对于 varchar 型数据 的 最大可容纳的字符长度 实验。
utf8 编码
先写一段 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
经测试，latin1 的编码格式下，varchar(M)，最大的 M 为 21844。
latin1 编码
仍然尝试创建：
drop table if exists test_varchar;
create table test_varchar
(
varchar_value varchar(100000)
) engine = innodb
charset = latin1;
执行报错：
[1074] Column length too big for column 'varchar_value' (max = 65535); use BLOB or TEXT instead
按照提示，我们把大小改为 65535，执行依然报错：
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
经测试，latin1 的编码格式下，varchar(M)，最大的 M 为 65532。
gkb 编码
那么 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，仍然报错：
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。
varchar 型数据 的 最大可容纳的字符长度 - 实验 2
# 创建表
drop table if exists test_varchar;
create table test_varchar
(
varchar_value varchar(3)
) engine = innodb
charset = utf8mb4;
# 添加数据
insert into test_varchar (varchar_value)
values ("测试啊");
# 计算占用的存储字节数
select length(varchar_value) from test_varchar;
#|length(varchar_value)
1|9
# 添加数据
insert into test_varchar (varchar_value)
values ("测试啊啊");
Data truncation: Data too long for column 'varchar_value' at row 1
说明，varchar(n) 表示该字段最多只能存储 n 个字符，不考虑编码方式。
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