数字类型 （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，则以空格补于其后。
- 存入时，如果字符串结尾存在空格，则在存入时，结尾的空格仍会被保留
- 但是，在查询时，查询出的结果是已经去掉了字符串后存在的空格的新字符串
- 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