首页 > 编程笔记 > MySQL笔记 阅读：7

MySQL数据类型大全（超级详细，新手必看）

MySQL 支持多种数据类型，主要有数值类型、日期/时间类型和字符串类型：

MySQL数值类型

1、整数类型

数值类型主要用来存储数字。MySQL 提供了多种数值数据类型，不同的数据类型提供不同的取值范围，可以存储的值的范围越大，其所需要的存储空间也会越大。

MySQL 主要提供的整数类型有 TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）、BIGINT，如下表所示。整数类型的属性字段可以添加 AUTO_INCREMENT 自增约束条件。

表：MySQL中的整数类型
类型名称 说明 存储需求
TINYINT 很小的整数 1 字节
SMALLINT 小的整数 2 字节
MEDIUMINT 中等大小的整数 3 字节
INT（INTEGER） 普通大小的整数 4 字节
BIGINT 大整数 8 字节

从表中可以看到，不同整数类型存储所需的字节数是不同的，占用字节数最小的是 TINYINT 类型，占用字节最大的是 BIGINT 类型，占用字节越多的类型所能表示的数值范围越大。

根据占用字节数，可以求出每一种数据类型的取值范围。例如，TINYINT 需要 1 字节（8 bits）来存储，那么 TINYINT 无符号数的最大值为 2^8−1（255）、TINYINT 有符号数的最大值为 2^7−1（127）。其他整数类型的取值范围计算方法相同，如下表所示。

表：不同整数类型的取值范围
数据类型 有符号 无符号
TINYINT -128~127 0~255
SMALLINT -32768~32767 0~65535
MEDIUMINT -8388608~8388607 0~16777215
INT（INTEGER） -2147483648~2147483647 0~4294967295
BIGINT -9223372036854775808~9223372036854775807 0~18446744073709551615

【实例】创建表 tmp1，其中字段 x、y、z、m、n 的数据类型依次为 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，SQL 语句如下： 
CREATE TABLE tmp1 ( x TINYINT,  y SMALLINT,  z MEDIUMINT,  m INT,  n BIGINT );
语句执行成功之后，使用 DESC 查看表结构，结果如下： 
mysql> DESC tmp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| x      | tinyint     | YES  |     | NULL    |       |
| y      | smallint    | YES  |     | NULL    |       |
| z      | mediumint   | YES  |     | NULL    |       |
| m      | int         | YES  |     | NULL    |       |
| n      | bigint      | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
不同的整数类型有不同的取值范围，并且需要不同的存储空间，因此应该根据实际需要选择合适的数据类型，这样有利于提高查询的效率和节省存储空间。

2、小数类型

MySQL 中使用浮点数和定点数来表示小数：
下表列出了 MySQL 中的小数类型和存储需求。

表：MySQL 中的小数类型
类型名称 说明 存储需求
FLOAT 单精度浮点数 4 字节
DOUBLE 双精度浮点数 8 字节
DECIMAL(M,D), DEC 压缩的“严格”定点数 M+2 字节

DECIMAL 类型不同于 FLOAT 和 DOUBLE，它实际是以串存放的，可能的最大取值范围与 DOUBLE 一样，但其有效的取值范围由 M 和 D 的值决定。如果改变 M 而固定 D，则其取值范围将随 M 的变大而变大。从上表中可以看到，DECIMAL 的存储空间并不是固定的，而由其精度值M决定的，其占用 M+2 字节。

FLOAT 类型的取值范围如下：
DOUBLE 类型的取值范围如下：
【实例】创建表 tmp2，其中字段 x、y、z 的数据类型依次为 FLOAT、DOUBLE 和 DECIMAL(5,1)，SQL 语句如下： 
CREATE TABLE tmp2 ( x FLOAT,  y DOUBLE,  z DECIMAL(5,1) );
向表中插入数据 5.12、5.15 和 5.123，SQL 语句如下： 
mysql>INSERT INTO tmp2 VALUES(5.12, 5.15, 5.123);
在插入数据时，MySQL 给出了一个警告信息，使用 SHOW WARNINGS; 语句查看警告信息： 
mysql> SHOW WARNINGS;
+--------+-------------+----------------------------------------+
| Level  | Code        | Message                                |
+--------+-------------+----------------------------------------+
| Note   | 1265        | Data truncated for column 'z' at row 1 |
+--------+-------------+----------------------------------------+
可以看到，给出 z 字段数值被截断的警告。使用 SELECT * FROM tmp2 语句查看数据输入结果，结果如下： 
mysql> SELECT * FROM tmp2;
+------+------+------+
| x    | y    | z    |
+------+------+------+
| 5.1  | 5.2  | 5.1  |
+------+------+------+

MySQL日期与时间类型

MySQL 中有多种表示日期的数据类型，主要有 DATETIME、DATE、TIMESTAMP、TIME 和 YEAR。

例如，当只记录年信息的时候，可以只使用 YEAR 类型，而没有必要使用 DATE 类型。每一个类型都有合法的取值范围，当指定确实不合法的值时，系统将“零”值插入数据库中。

下表列出了 MySQL 中的日期与时间类型：

表：日期与时间数据类型
类型名称 日期格式 日期范围 存储需求
YEAR YYYY 1901～2155 1 字节
TIME HH:MM:SS −838:59:59 ～838:59:59 3 字节
DATE YYYY-MM-DD 1000-01-01～9999-12-3 3 字节
DATETIME YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00～9999-12-31 23:59:59 8 字节
TIMESTAMP YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 UTC～2038-01-19 03:14:07 UTC 4 字节

1) YEAR类型

YEAR 是一个单字节类型，用于表示年，在存储时只需要 1 字节。可以使用以下 3 种格式指定 YEAR 值：
提示，两位整数范围与两位字符串范围稍有不同。例如，要插入 2000 年，读者可能会使用数字格式的 0 表示 YEAR，实际上，插入数据库的值为 0000，而不是所希望的 2000。只有使用字符串格式的 '0' 或 '00'，才可以被正确地解释为 2000。非法 YEAR 值将被转换为 0000。

【实例】创建数据表 tmp3，定义数据类型为 YEAR 的字段 y，向表中插入值 2010、“2010”、“2166”。
① 创建表 tmp3，SQL语句如下： 
CREATE TABLE tmp3(y YEAR );

② 向表中插入数据： 
mysql> INSERT INTO tmp3 values(2010),('2010');

③ 再次向表中插入数据： 
mysql> INSERT INTO tmp3 values ('2166');
ERROR 1264 (22003): Out of range value for column 'y' at row 1

④ 上述语句执行之后，MySQL 给出了一条错误提示，使用 SHOW 查看错误信息： 
mysql> SHOW WARNINGS;
+--------+-------------+---------------------------------------------+
| Level  | Code        | Message                                     |
+--------+-------------+---------------------------------------------+
| Error  | 1264        | Out of range value for column 'y' at row 1; |
+--------+-------------+---------------------------------------------+
可以看到，插入的第 3 个值 2166 超过了 YEAR 类型的取值范围，此时不能正常执行插入操作。

⑤ 查看插入结果： 
mysql> SELECT * FROM tmp3;
+------+
| y    |
+------+
| 2010 |
| 2010 |
+------+
由结果可以看到，当插入值为数值类型的 2010 或者字符串类型的“2010”时，都正确地储存到了数据库中；而当插入值为“2166”时，由于它超出了 YEAR 类型的取值范围，因此不能插入。

2) TIME类型

TIME 类型用在只需要时间信息时，在存储时需要 3 字节，格式为： 
HH:MM:SS
其中，HH 表示小时，MM 表示分钟，SS 表示秒。

TIME 类型的取值范围为 −838:59:59～838:59:59，小时部分取值范围如此大的原因是，TIME 类型不仅可以表示一天的时间（必须小于 24 小时），还可以表示某个事件过去的时间或两个事件之间的时间间隔（可以大于 24 小时，甚至可以为负）。

可以使用以下两种格式指定TIME值：
为 TIME 列分配简写值时应注意，如果 TIME 值中没有冒号，那么 MySQL 在解释值时，假定最右边的两位表示秒（MySQL 解释 TIME 值为过去的时间而不是当天的时间）。例如，读者可能认为“1112”和1112都表示 11:12:00（11点12分），但 MySQL 将它们解释为 00:11:12（11分12秒）。同样地，“12”和12被解释为 00:00:12。相反，如果 TIME 值中使用冒号，则肯定被看作当天的时间，也就是说，“11:12”表示 11:12:00，而不是 00:11:12。

【实例】创建数据表 tmp4，定义数据类型为 TIME 的字段 t，向表中插入值“10:05:05”、“23:23”、“2 10:10”、“3 02”和“10”。
① 创建表 tmp4： 
CREATE TABLE tmp4( t TIME );

② 向表中插入数据： 
mysql> INSERT INTO tmp4 values('10:05:05 '), ('23:23'), ('2 10:10'), ('3 02'), ('10');

③ 查看插入结果： 
mysql> SELECT * FROM tmp4;
+----------+
| t        |
+----------+
| 10:05:05 |
| 23:23:00 |
| 58:10:00 |
| 74:00:00 |
| 00:00:10 |
+----------+
由结果可以看到，“10:05:05”被转换为10:05:05；“23:23”被转换为 23:23:00；“2 10:10”被转换为 58:10:00，“3 02”被转换为 74:00:00；“10”被转换成 00:00:10。

提示，在使用“D HH”格式时，小时一定要使用双位数值，如果是小于 10 的小时数，应在前面加 0。

3) DATE类型

DATE 类型用在仅需要日期值时，没有时间部分，在存储时需要 3 字节，日期格式为： 
YYYY-MM-DD
其中，YYYY 表示年，MM 表示月，DD 表示日。

在给 DATE 类型的字段赋值时，可以使用字符串类型或者数字类型，只要符合 DATE 的日期格式即可。

① 以“YYYY-MM-DD”或者“YYYYMMDD”字符串格式表示的日期，取值范围为 1000-01-01～9999-12-3。例如，输入“2012-12-31”或者“20121231”，插入数据库的日期都为 2012-12-31。

② 以“YY-MM-DD”或者“YYMMDD”字符串格式表示的日期，这里 YY 表示两位的年值。MySQL 使用以下规则解释两位年值：
  1. 00~69 范围的年值被转换为 2000~2069；
  2. 70~99 范围的年值被转换为 1970~1999。

例如，输入“12-12-31”，插入数据库的日期为 2012-12-31；输入“981231”，插入数据的日期为 1998-12-31。

③ 以 YY-MM-DD 或者 YYMMDD 数字格式表示的日期。与前面类似，00~69 范围的年值被转换为 2000~2069，70~99 范围的年值被转换为 1970~1999。

例如输入 12-12-31，插入数据库的日期为 2012-12-31；输入 981231，插入数据库的日期为 1998-12-31。

④ 使用 CURRENT_DATE 或者 NOW()，插入系统当前日期。

【实例】创建数据表 tmp5，定义数据类型为 DATE 的字段 d，向表中插入“YYYY-MM-DD”和“YYYYMMDD”字符串格式的日期。
① 创建表tmp5： 
MySQL> CREATE TABLE tmp5(d DATE);

② 向表中插入“YYYY-MM-DD”和“YYYYMMDD”格式的日期： 
MySQL> INSERT INTO tmp5 values('1998-08-08'),('19980808'),('20101010');

③ 查看插入结果： 
MySQL> SELECT * FROM tmp5;
+------------+
| d          |
+------------+
| 1998-08-08 |
| 1998-08-08 |
| 2010-10-10 |
+------------+
可以看到，各个不同类型的日期值都被正确地插入数据表中。

【实例】向表 tmp5 中插入“YY-MM-DD”和“YYMMDD”字符串格式的日期。
① 删除表中的数据： 
DELETE FROM tmp5;

② 向表中插入“YY-MM-DD”和“YYMMDD”格式的日期： 
mysql> INSERT INTO tmp5 values ('99-09-09'),( '990909'), ('000101') ,('111111');

③ 查看插入结果： 
mysql> SELECT * FROM tmp5;
+-------------+
| d           |
+-------------+
| 1999-09-09  |
| 1999-09-09  |
| 2000-01-01  |
| 2011-11-11  |
+-------------+

【实例】向表 tmp5 中插入 YYYYMMDD 和 YYMMDD 数字格式日期。
① 删除表中的数据： 
DELETE FROM tmp5;

② 向表中插入 YYYYMMDD 和 YYMMDD 格式的日期： 
mysql> INSERT INTO tmp5 values (19990909),(990909), ( 000101) ,( 111111);

③ 查看插入结果： 
mysql> SELECT * FROM tmp5;
+-------------+
| d           |
+-------------+
| 1999-09-09  |
| 1999-09-09  |
| 2000-01-01  |
| 2011-11-11  |
+-------------+

【实例】向表 tmp5 中插入系统当前日期。
① 删除表中的数据： 
DELETE FROM tmp5;

② 向表中插入系统当前日期： 
mysql> INSERT INTO tmp5 values( CURRENT_DATE() ),( NOW() );

③ 查看插入结果： 
mysql> SELECT * FROM tmp5;
+-------------+
| d           |
+-------------+
| 2024-08-09  |
| 2024-08-09  |
+-------------+
CURRENT_DATE 只返回当前日期值，不包括时间部分；NOW() 函数返回日期和时间值，在保存到数据库时，只保留日期部分。

4) DATETIME类型

MYSQL 中的 DATETIME 类型用在需要同时包含日期和时间信息时，在存储时需要 8 字节，日期格式为： 
YYYY-MM-DD HH:MM:SS
其中，YYYY 表示年，MM 表示月，DD 表示日，HH 表示小时，MM 表示分钟，SS 表示秒。

在给 DATETIME 类型的字段赋值时，可以使用字符串类型或者数字类型，只要符合 DATETIME 的日期格式即可。

① 以“YYYY-MM-DD HH:MM:SS”或者“YYYYMMDDHHMMSS”字符串格式表示的值，取值范围为 1000-01-01 00:00:00~9999-12-3 23:59:59。

例如，输入“2012-12-31 05:05:05”或者“20121231050505”，插入数据库的 DATETIME 值都为 2012-12-31 05: 05: 05。

② 以“YY-MM-DD HH:MM:SS”或者“YYMMDDHHMMSS”字符串格式表示的日期，这里 YY 表示两位的年值。与前面相同，00~69范围的年值被转换为 2000~2069，70~99 范围的年值被转换为 1970~1999。

例如，输入“12-12-31 05:05:05”，插入数据库的 DATETIME 为 2012-12-31 05:05:05；输入“980505050505”，插入数据库的DATETIME为1998-05-05 05:05:05。

③ 以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。

例如，输入 20121231050505，插入数据库的 DATETIME 为 2012-12-31 05:05:05；输入 981231050505，插入数据的 DATETIME 为 1998-12-31 05:05:05。

【实例】创建数据表 tmp6，定义数据类型为 DATETIME 的字段 dt，向表中插入“YYYY-MM-DD HH:MM:SS”和“YYYYMMDDHHMMSS”字符串格式日期和时间值。
① 创建表 tmp6： 
CREATE TABLE tmp6(  dt DATETIME );

② 向表中插入“YYYY-MM-DD HH:MM:SS”和“YYYYMMDDHHMMSS”格式的日期： 
mysql> INSERT INTO tmp6 values('1998-08-08 08:08:08'),('19980808080808'),('20101010101010');

③ 查看插入结果： 
mysql> SELECT * FROM tmp6;
+------------------------+
| dt                     |
+------------------------+
| 1998-08-08 08:08:08    |
| 1998-08-08 08:08:08    |
| 2010-10-10 10:10:10    |
+------------------------+
可以看到，各个不同类型的日期值都被正确地插入数据表中。

【实例】向表 tmp6 中插入“YY-MM-DD HH:MM:SS”和“YYMMDDHHMMSS”字符串格式的日期和时间值。
① 删除表中的数据： 
DELETE FROM tmp6;

② 向表中插入“YY-MM-DD HH:MM:SS”和“YYMMDDHHMMSS”格式的日期： 
mysql> INSERT INTO tmp6 values('99-09-09 09:09:09'),('990909090909'),('101010101010');

③ 查看插入结果： 
mysql> SELECT * FROM tmp6;
+-------------------------+
| dt                      |
+-------------------------+
| 1999-09-09 09:09:09     |
| 1999-09-09 09:09:09     |
| 2010-10-10 10:10:10     |
+-------------------------+

【实例】向表 tmp6 中插入 YYYYMMDDHHMMSS 和 YYMMDDHHMMSS 数字格式的日期和时间值。
① 删除表中的数据： 
DELETE FROM tmp6;

② 向表中插入 YYYYMMDDHHMMSS 和 YYMMDDHHMMSS 数字格式的日期和时间： 
mysql> INSERT INTO tmp6 values(19990909090909), (101010101010);

③ 查看插入结果： 
mysql> SELECT * FROM tmp6;
+--------------------------+
| dt                       |
+-------------------------+
| 1999-09-09 09:09:09     |
| 2010-10-10 10:10:10     |
+-------------------------+

【实例】向表 tmp6 中插入系统当前日期和时间。
① 删除表中的数据： 
DELETE FROM tmp6;

② 向表中插入系统当前日期和时间： 
mysql> INSERT INTO tmp6 values( NOW() );

③ 查看插入结果： 
mysql> SELECT * FROM tmp6;
+-------------------------+
| dt                      |
+-------------------------+
| 2024-08-09 17:07:30     |
+-------------------------+
NOW() 函数返回当前系统的日期和时间，格式为“YYYY-MM-DD HH:MM:SS”。

最终需要说明的是，MySQL 允许“不严格”语法，任何标点符号都可以用作日期部分或时间部分之间的间隔符。例如，“98-12-31 11:30:45”、“98.12.31 11+30+45”、“98/12/31 11*30*45”和“98@12@31 11^30^45”是等价的，这些值都可以被正确地插入数据库中。

5) TIMESTAMP类型

TIMESTAMP 的显示格式与 DATETIME 相同，显示宽度固定在 19 个字符，日期格式为 YYYY-MM-DD HH:MM:SS，在存储时需要 4 字节。

TIMESTAMP 的取值范围小于 DATETIME 的取值范围，为“1970-01-01 00:00:01”UTC~“2038-01-19 03:14:07”UTC。其中，UTC（Coordinated Universal Time）为世界标准时间，因此插入的数据要保证在合法的取值范围内。

【实例】创建数据表 tmp7，定义数据类型为 TIMESTAMP 的字段 ts，向表中插入值“19950101010101”、“950505050505”、“1996-02-02 02:02:02”、“97@03@03 03@03@03”和121212121212、NOW()。
① 创建数据表tmp7： 
CREATE TABLE tmp7( ts TIMESTAMP);

② 向表中插入数据： 
INSERT INTO tmp7 values ('19950101010101'),
('950505050505'),
('1996-02-02 02:02:02'),
('97@03@03 03@03@03'),
(121212121212),
( NOW() );

③ 查看插入结果： 
mysql>SELECT * FROM tmp7;
+---------------------+
| ts                  |
+---------------------+
| 1995-01-01 01:01:01 |
| 1995-05-05 05:05:05 |
| 1996-02-02 02:02:02 |
| 1997-03-03 03:03:03 |
| 2012-12-12 12:12:12 |
| 2024-08-09 17:08:25 |
+---------------------+
由结果可以看到：
提示，TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同之外，还有一个最大的区别就是：DATETIME 在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；而 TIMESTAMP 值的存储是以 UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。查询时，不同时区显示的时间值是不同的。

MySQL字符串类型

字符串类型用来存储字符串数据，除此之外，还可以存储其他数据，比如图片和声音的二进制数据。MySQL支持两类字符型数据：文本字符串和二进制字符串。

1、文本字符串类型

文本字符串不仅可以进行区分或者不区分大小写的字符串比较，还可以进行模式匹配查找。下表列出了 MySQL 中的文本字符串类型。

表：MySQL 中文本字符串类型
类型名称 说明 存储需求
CHAR(M) 固定长度非二进制字符串 M 字节，1≤M≤255
VARCHAR(M) 变长非二进制字符串 L+1 字节，在此 L≤M 和 1≤M≤255
TINYTEXT 非常小的非二进制字符串 L+1 字节，在此 L<2^8
TEXT 小的非二进制字符串 L+2 字节，在此 L<2^16
MEDIUMTEXT 中等大小的非二进制字符串 L+3 字节，在此 L<2^24
LONGTEXT 大的非二进制字符串 L+4 字节，在此 L<2^32
ENUM 枚举类型，只能有一个枚举字符串值 1 或 2 字节，取决于枚举值的数目（最大值为 65535）
SET 一个设置，字符串对象可以有 0 个或多个 SET 成员 1、2、3、4 或 8 字节，取决于集合成员的数量（最多为 64 个成员）

VARCHAR 和 TEXT 类型与下面要讲的 BLOB 都是变长类型，其存储需求取决于列值的实际长度（在表中用 L 表示），而不是取决于类型的最大可能尺寸。

例如，一个 VARCHAR(10) 列能保存最大长度为 10 个字符的字符串，实际的存储需要是字符串的长度 L 加上 1 字节（记录字符串的长度）。对于字符串“abcd”，L 是 4 字节而存储要求是 5 字节。

下面具体介绍这些数据类型的作用以及如何在查询中使用这些类型。

1) CHAR和VARCHAR类型

CHAR(M) 为固定长度字符串，在定义时指定字符串列长，在保存时在右侧填充空格，以达到指定的长度。M 表示列长度，其范围是 0~255 个字符。

例如，CHAR(4) 定义了一个固定长度的字符串列，其包含的字符个数最大为 4。当检索到 CHAR 值时，尾部的空格将被删除。

VARCHAR(M) 是长度可变的字符串，M 表示最大列长度，其范围是 0~65535。VARCHAR 的最大实际长度由最长的行的大小和使用的字符集确定，而其实际占用的空间为字符串的实际长度加 1。

例如，VARCHAR(50) 定义了一个最大长度为 50 的字符串，如果插入的字符串只有 10 个字符，则实际存储的字符串为 10 个字符和一个字符串结束字符。VARCHAR 在值保存和检索时尾部的空格仍保留。

【实例】将不同字符串保存到 CHAR(4) 列和 VARCHAR(4) 列，并说明 CHAR 和 VARCHAR 之间的差别，如下表所示。

表：CHAR(4)与VARCHAR(4)存储区别
插入值 CHAR(4) 存储需求 VARCHAR(4) 存储需求
“ ” “ ” 4 字节 “ ” 1 字节
“ab” “ab ” 4 字节 “ab” 3 字节
“abc” “abc ” 4 字节 “abc” 4 字节
“abcd” “abcd” 4 字节 “abcd” 5 字节
“abcdef” “abcd” 4 字节 “abcd” 5 字节

对比结果可以看到，CHAR(4) 定义了固定长度为 4 的列，不管存入的数据长度为多少，所占用的空间均为 4 字节；VARCHAR(4) 定义的列所占的字节数为实际长度加 1。

查询时，CHAR(4) 和 VARCHAR(4) 的值并不一定相同，再看一个例子：

【实例】创建表 tmp8，定义字段 ch 和 vch 的数据类型分别为 CHAR(4)、VARCHAR(4)，向表中插入数据“ab ”。
① 创建表 tmp8： 
CREATE TABLE tmp8(ch  CHAR(4),  vch  VARCHAR(4));

② 插入数据： 
INSERT INTO tmp8 VALUES('ab  ', 'ab  ');

③ 查询插入结果： 
mysql> SELECT concat('(', ch, ')'), concat('(',vch,')') FROM tmp8;
+----------------------+---------------------+
| concat('(', ch, ')') | concat('(',vch,')') |
+----------------------+---------------------+
| (ab)                 | (ab  )              |
+----------------------+---------------------+
1 row in set (0.00 sec)
从查询结果可以看到，ch 字段在保存“ab ”时将末尾的两个空格删除了，而 vch 字段保留了末尾的两个空格。

2) TEXT类型

TEXT 用于保存非二进制字符串，如文章内容、评论等。当保存或查询 TEXT 列的值时，不删除尾部空格。

TEXT 类型分为 4 种：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。不同的 TEXT 类型的数据长度不同：

3) ENUM类型

ENUM 是一个字符串对象，其值为表创建时在列规定中枚举的一列值。

使用 ENUM 对象的语法格式如下： 
字段名 ENUM('值1','值2',...,'值n')
其中，“字段名”指将要定义的字段，“值 n”指枚举列表中的第 n 个值。

ENUM 类型的字段在取值时，只能在指定的枚举列表中取，而且一次只能取一个。如果创建的成员中有空格，则其尾部的空格将自动被删除。

ENUM 值在内部用整数表示，并且每个枚举值均有一个索引值：列表值所允许的成员值从 1 开始编号，MySQL 存储的就是这个索引编号。枚举最多可以有 65535 个元素。

例如，定义 ENUM 类型的列 ('first'，'second'，'third')，该列可以取的值和每个值的索引如下表所示。

表：ENUM类型的取值范围
值 索引
NULL NULL
" 0
first 1
second 2
third 3

ENUM 值依照列索引顺序排列，并且空字符串排在非空字符串前，NULL 排在其他所有的枚举值前，这一点也可以从上表中看到。

在这里，有一个方法可以查看列成员的索引值。例如创建表 tmp9，定义 ENUM 类型的列 enm('first', 'second', 'third')，查看列成员的索引值。
① 创建表 tmp9： 
CREATE TABLE tmp9( enm ENUM('first','second','third') );

② 插入各个列值： 
INSERT INTO tmp9 values('first'),('second') ,('third') , (NULL);

③ 查看索引值： 
mysql> SELECT enm, enm+0 FROM tmp9;
+-------+-------+
| enm   | enm+0 |
+-------+-------+
| first | 1     |
| second| 2     |
| third | 3     |
| NULL  | NULL  |
+-------+-------+
可以看到，这里的索引值和表 1 中的相同。

ENUM 列总有一个默认值：
【实例】创建表 tmp10，定义 INT 类型的字段 soc，ENUM 类型的字段 level，并且取值列表为 ('excellent','good', 'bad')，向表 tmp10 中插入数据 (70,'good')、(90,1)、(75,2)、(50,3)、(100,'best')。
① 创建数据表： 
CREATE TABLE tmp10 (soc INT, level enum('excellent', 'good','bad') );

② 插入数据： 
INSERT INTO tmp10 values(70,'good'), (90,1),(75,2),(50,3);

③ 再次插入数据： 
mysql>INSERT INTO tmp10 values (100,'best');
ERROR 1265 (01000): Data truncated for column 'level' at row 1
这里系统提示错误信息，可以看到，由于字符串值'best'不在 ENUM 列表中，因此阻止将它插入表中。

④ 查询插入结果： 
mysql>SELECT * FROM tmp10;
+-----+-----------+
| soc | level     |
+-----+-----------+
|  70 | good      |
|  90 | excellent |
|  75 | good      |
|  50 | bad       |
+-----+-----------+
由结果可以看到，因为 ENUM 列表中的值在 MySQL 中都是以编号序列存储的，所以插入列表中的值“good”与插入其对应序号“2”的结果是相同的，而“best”不是列表中的值，所以不能进行插入操作。

4) SET类型

SET 是一个字符串对象，可以有 0 个或多个值。SET 列最多可以有 64 个成员，其值为表创建时规定的一个列值。

指定包括多个 SET 成员的 SET 列值时，各成员之间用逗号（,）隔开。语法格式如下： 
SET('值1','值2',...,'值n')
与 ENUM 类型相同，SET 值在内部用整数表示，列表中每一个值都有一个索引编号。当创建表时，SET 成员值的尾部空格将自动被删除。与 ENUM 类型不同的是，ENUM 类型的字段只能从定义的多个列值中选择一个值插入，而 SET 类型的列可从定义的多个列值中选择多个字符的联合。

如果插入 SET 字段中的列值有重复，则 MySQL 自动删除重复的值；插入 SET 字段的值的顺序并不重要，MySQL 会在将这些值存入数据库时按照定义的顺序显示；如果插入了不正确的值，默认情况下，MySQL 将忽视这些值，并给出警告。

【实例】创建表 tmp11，定义 SET 类型的字段 s，取值列表为 ('a', 'b', 'c', 'd')，插入数据 ('a')、('a,b,a')、('c,a,d')、('a,x,b,y')。
① 创建表 tmp11： 
CREATE TABLE tmp11 ( s SET('a', 'b', 'c', 'd'));

② 插入数据： 
INSERT INTO tmp11 values('a'),( 'a,b,a'),('c,a,d');

③ 再次插入数据： 
mysql>INSERT INTO tmp11 values ('a,x,b,y');
ERROR 1265 (01000): Data truncated for column 's' at row 1
由于插入了 SET 列不支持的值，因此 MySQL 给出错误提示。

④ 查看插入结果： 
mysql> SELECT * FROM tmp11;
+-------+
| s     |
+-------+
| a     |
| a,b   |
| a,c,d |
+-------+
从结果中可以看到，对于 SET 来说：

2、二进制字符串类型

前面讲解了存储文本的字符串类型，接下来讲解 MySQL 中存储二进制数据的字符串类型。

MySQL 中的二进制字符串类型有 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB，如下表所示。

表：MySQL中的二进制字符串类型
类型名称 说明 存储需求
BIT(M) 位字段类型 大约 (M+7)/8 字节
BINARY(M) 固定长度二进制字符串 M 字节
VARBINARY(M) 可变长度二进制字符串 M+1 字节
TINYBLOB(M) 非常小的 BLOB L+1 字节，在此 L<2^8
BLOB(M) 小的 BLOB L+2 字节，在此 L<2^16
MEDIUMBLOB(M) 中等大小的 BLOB L+3 字节，在此 L<2^24
LONGBLOB(M) 非常大的 BLOB L+4 字节，在此 L<2^32

1) BIT类型

BIT(M) 是位字段类型。M 表示每个值的位数，范围为 1~64。如果 M 被省略，则默认为 1。

如果为 BIT(M) 列分配的值的长度小于 M 位，就在值的左边用 0 填充。例如，为 BIT(6) 列分配一个值 b'101'，其效果与分配 b'000101' 相同。

BIT 数据类型用来保存位字段值。例如，以二进制的形式保存数据 13（13 的二进制形式为 1101），则需要位数至少为 4 位的 BIT 类型，即可以定义列类型为 BIT(4)，大于二进制 1111 的数据是不能插入 BIT(4) 类型的字段中的。

【实例】创建表 tmp12，定义 BIT(4) 类型的字段 b，向表中插入数据 2、9、15。
① 创建表 tmp12： 
CREATE TABLE tmp12( b BIT(4) );

② 插入数据： 
mysql> INSERT INTO tmp12 VALUES(2), (9), (15);

③ 查询插入结果： 
mysql> SELECT BIN(b+0) FROM tmp12;
+------------+
| BIN(b+0)   |
+------------+
| 10         |
| 1001       |
| 1111       |
+------------+
b+0 表示将二进制的结果转换为对应数字的值，BIN() 函数将数字转换为二进制。从结果中可以看到，成功地将 3 个数插入表中。

默认情况下，MySQL 不可以插入超出该列允许范围的值，因而要确保插入的数据值在指定的范围内。

2) BINARY和VARBINARY类型

BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字节字符串。其语法格式如下： 
列名称 BINARY(M)或者VARBINARY(M)
BINARY 类型的长度是固定的，指定长度之后，如果插入值长度没有达到指定长度，则将在它们右边填充“\0”以达到指定长度。例如，指定列数据类型为 BINARY(3)，当插入“a”时，存储的内容实际为“a\0\0”；当插入“ab”时，实际存储的内容为“ab\0”。不管存储的内容是否达到指定的长度，其存储空间均为指定的值 M。

VARBINARY 类型的长度是可变的，指定好长度之后，插入值的长度可以在 0 到最大值之间。例如，指定列数据类型为 VARBINARY(20)，如果插入值的长度只有 10，则实际存储空间为 10 加 1，即实际占用的空间为字符串的实际长度加 1。

【实例】创建表 tmp13，定义 BINARY(3) 类型的字段 b 和 VARBINARY(3)类型的字段 vb，并向表中插入数据‘5’，比较两个字段的存储空间。
① 创建表tmp13： 
CREATE TABLE tmp13(b binary(3),  vb varbinary(3));

② 插入数据： 
INSERT INTO tmp13 VALUES(5,5);

③ 查看两个字段存储数据的长度： 
mysql> SELECT length(b), length(vb) FROM tmp13;
+-----------+---------------+
| length(b) | length(vb)    |
+-----------+---------------+
|       3   |         1     |
+-----------+---------------+
可以看到，b 字段的数据长度为 3，而 vb 字段的数据长度仅为插入的一个字符的长度 1。

3) BLOB类型

BLOB 是一个二进制大对象，用来存储可变数量的数据。

BLOB 类型分为 4 种：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB，如下表所示。

表：BLOB类型的存储范围
数据类型 存储范围
TINYBLOB 最大长度为 255（2^8-1）B
BLOB 最大长度为 65535（2^16-1）B
MEDIUMBLOB 最大长度为 16777215（2^24-1）B
LONGBLOB 最大长度为 4294967295（2^32-1）B 或 4GB

BLOB 列存储的是二进制字符串（字节字符串），TEXT 列存储的是非二进制字符串（字符字符串）。BLOB 列没有字符集，并且排序和比较基于列值字节的数值；TEXT 列有一个字符集，并且根据字符集对值进行排序和比较。

MySQL如何选择数据类型

MySQL 提供了大量的数据类型，为了优化存储、提高数据库性能，在任何情况下均应使用最精确的类型，即在所有可以表示该列值的类型中，选择存储量最少的那种类型。

1) 整数和浮点数

如果不需要小数部分，就使用整数来保存数据；如果需要表示小数部分，就使用浮点数类型。对于浮点数据列，存入的数值会对该列定义的小数位进行四舍五入。

例如，某列的值的范围为 1~99999，若只需存储整数，则 MEDIUMINT UNSIGNED 是最好的类型；若需要存储小数，则使用 FLOAT 类型。

浮点类型包括 FLOAT 和 DOUBLE 类型。DOUBLE 类型的精度比 FLOAT 类型的精度更高，因此当存储精度要求较高时，应选择 DOUBLE 类型。

2) 浮点数和定点数

浮点数 FLOAT、DOUBLE 相对于定点数 DECIMAL 的优势是：在长度一定的情况下，浮点数能表示更大的数据范围。由于浮点数容易产生误差，因此当对精确度要求比较高时，建议使用 DECIMAL 来存储。

DECIMAL 在 MySQL 中是以字符串存储的，用于定义货币等对精确度要求较高的数据。在数据迁移中，float(M,D) 是非标准 SQL 定义，数据库迁移可能会出现问题，最好不要这样使用。

另外，两个浮点数在进行减法和比较运算时也容易出问题，因此在进行计算的时候一定要小心。进行数值比较时，最好使用 DECIMAL 类型。

3) 日期与时间类型

MySQL 对于不同种类的日期和时间有很多数据类型，比如 YEAR 和 TIME。如果只需要记录年份，则使用 YEAR 类型即可；如果只记录时间，则使用 TIME 类型。

如果同时需要记录日期和时间，则可以使用 TIMESTAMP 或者 DATETIME 类型。由于 DATETIME 的取值范围大于 TIMESTAMP，因此存储范围较大的日期时最好使用 DATETIME。

TIMESTAMP 有一个 DATETIME 不具备的属性，默认情况下当插入一条记录但没有指定 TIMESTAMP 这个列值时，MySQL 会把 TIMESTAMP 列设为当前的时间。因此，当需要在插入记录的同时插入当前时间，使用 TIMESTAMP 会更方便。另外，TIMESTAMP 在空间上比 DATETIME 更有效。

4) CHAR与VARCHAR之间的特点与选择

CHAR 和 VARCHAR 的区别如下：
存储引擎对于选择 CHAR 和 VARCHAR 的影响：

5) ENUM和SET

ENUM 只能取单值，它的数据列表是一个枚举集合。它的合法取值列表最多允许有 65535 个成员。因此，在需要从多个值中选取一个时，可以使用 ENUM。例如，性别字段适合定义为 ENUM 类型，每次只能从“男”或“女”中取一个值。

SET 可取多值。它的合法取值列表最多允许有 64 个成员。空字符串也是一个合法的 SET 值。在需要取多个值的时候，适合使用 SET 类型，比如要存储一个人的兴趣爱好，最好使用 SET 类型。

ENUM 和 SET 的值是以字符串形式出现的，但在 MySQL 内部，是以数值的形式存储它们的。

6) BLOB和TEXT

BLOB 是二进制字符串，TEXT 是非二进制字符串，两者均可存放大容量的信息。BLOB 主要存储图片、音频信息等，而 TEXT 只能存储纯文本文件。

相关文章