Logo
活死人の行知路

MySQL类型

📅 | 📝 1445 字
#MySQL

从之前的SQL练习中看到存储不同的数据需要不同的数据类型，那这些字段该如何选择类型呢？

目标

  • 了解不同类型以及使用场景

1字符串类型

1.1CHAR 和 VARCHAR

char：用来保存固定长度的字符，使用时后面通常会声明固定的长度，范围是0~255。比如char(10)表示最多可以容纳10个字符。

varchar：可变长度，范围是0~65535。

通过实例验证两种类型区别：

1. 创建库表

mysql> create database test1;
Query OK, 1 row affected (0.02 sec)

mysql> use test1
Database changed
mysql> create table tbl_1(one char(10), two varchar(10));
Query OK, 0 rows affected (0.03 sec)

#2. 各自写入超过10个字符的数据

mysql> insert into tbl_1 (one) values ('abcabcabcabc');
ERROR 1406 (22001): Data too long for column 'one' at row 1
mysql>
mysql> insert into tbl_1 (two) values ('abcabcabcabc');
ERROR 1406 (22001): Data too long for column 'two' at row 1
mysql>

会发现两条语句都不能插入数据。

3. 写入少于10个字符的数据

mysql> insert into tbl_1 (one) values ('abc');
Query OK, 1 row affected (0.02 sec)

mysql> insert into tbl_1 (two) values ('abc');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tbl_1;
+------+------+
| one  | two  |
+------+------+
| abc  | NULL |
| NULL | abc  |
+------+------+
2 rows in set (0.01 sec)

4. 写入值携带空格

mysql> insert into tbl_1 values ('a  ', 'a  ');
Query OK, 1 row affected (0.01 sec)

mysql> select concat(one, '|') as one, concat(two, '|') as two from tbl_1;
+------+------+
| one  | two  |
+------+------+
| abc| | NULL |
| NULL | abc| |
| a|   | a  | |
+------+------+
3 rows in set (0.01 sec)

我们会发现在写入数据时char类型的字段不会保留空格。

1.2TEXT 和 BLOB

TEXT 和 BLOB都是保存纯文本数据类型，区别是TEXT保存字符数据， BLOB 保存二进制数据，并且BLOG有个特点，就是可以将图片转换成二进制保存到数据库中，但通常不建议这么使用，会影响数据库性能，在使用临时表查询结果中，如果有TEXT 和 BLOB会使服务器导致磁盘上的表而不是内存中的表，因为内存存储引擎不支持这些数据类型，这会导致检索速度非常慢。

1.3ENUM 和 SET

枚举数据类型。

1. 写入字段是创建表时所枚举出来的值

mysql> create table tbl_2 (name varchar(10), gender enum('男', '女'));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into tbl_2 select 'a', '男';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into tbl_2 select 'b', '女';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into tbl_2 select 'c', '其他';
ERROR 1265 (01000): Data truncated for column 'gender' at row 1

ENUM 类型每个枚举值都有一个索引号，比如第一个指”男“的索引号是1，第二个值的索引号是2，空值索引值为0，通过索引号来查询：

mysql> select * from tbl_2 where gender = 1;
+------+--------+
| name | gender |
+------+--------+
| a    | 男     |
+------+--------+
1 row in set (0.01 sec)

由于索引号的存在，官方也不建议适应数字所谓枚举值，因为这样可能会混淆枚举值和索引号。

2. SET 类型允许将零个或多个枚举值放在一起：

mysql> create table tbl_3(one set('a', 'b', 'c', 'd'));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into tbl_3 values('a,b,c'), ('a,a'), ('b,d,b');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tbl_3;
+-------+
| one   |
+-------+
| a,b,c |
| a     |
| b,d   |
+-------+
3 rows in set (0.00 sec)

3. 写入枚举值之外的值

mysql> insert into tbl_3 values ('a,b,e');
ERROR 1265 (01000): Data truncated for column 'one' at row 1

1.4如何选择

以下是您提供的数据库字段类型及应用场景转换为Markdown格式的表格：

类型应用场景
CHAR固定长度的字符串
VARCHAR可变长度的字符串
TEXT长文本（通常不建议使用，会影响查询效率）
BLOG二进制形式的长文本 （通常不建议使用，会影响查询效率）
ENUM枚举场景，并且只使用一个枚举值
SET枚举场景，使用多个枚举值（使用较少）

2整数类型

2.1INT

用来存放整数类型，这是最常见的类型，比如数量等。在开发中也常见将主键设置为INT类型。

通过实例验证：

1. 创建库表

mysql> create database test2;
Query OK, 1 row affected (0.03 sec)

mysql> use test2
Database changed
mysql> create table tbl_1(id int);
Query OK, 0 rows affected (0.03 sec)

2.插入默认有符号最大值

mysql> insert into tbl_1 select 2147483647;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

3. 最大值+1

mysql> insert into tbl_1 select 2147483648;
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql>

4. 创建无符号INT类型

mysql> create table tbl_2(id int unsigned);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into tbl_2 select 2147483648;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

此时默认有符号最大值加1后的值就可以正常插入

5. 无符号整型支持的最大值是 4294967295

mysql> insert into tbl_2 select 4294967295;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into tbl_2 select 4294967296;
ERROR 1264 (22003): Out of range value for column 'id' at row 1

2.2TINYINT

特点：占用空间少，仅有1字节，但存放数据也有限，无符号数字只能存储0255，有符号可以存储-128127。

2.3SMALLINT

比TINYINT存放的数据多一点，范围是0~65525。

2.4MEDIUMINT

占用空间为3字节。

2.5BIGINT

占用空间8字节。

2.6如何选择

类型大小 (Bytes)有符号范围无符号范围
TINYINT1$[-128, 127]$$[0, 255]$
SMALLINT2$[-32768, 32767]$$[0, 65535]$
MEDIUMINT3$[-8388608, 8388607]$$[0, 16777215]$
INT4$[-2147483648, 2147483647]$$[0, 4294967295]$
BIGINT8$[-2^{63}, 2^{63}-1]$$[0, 2^{64}-1]$

3浮点类型

3.1FLOAT 单精度浮点数

表示近似的数据值。

通过实例验证：

1. 创建库表

create database test3;
use test3
create table tbl_1(a float);

2. 插入记录

insert into tbl_1 select 3.14;
mysql> select * from tbl_1;
+------+
| a    |
+------+
| 3.14 |
+------+

3.2DOUBLE 双精度浮点数

  • 占用8字节

3.3DECIMAL 定点类型

  • 用于存储精确的数据，比如金额数量。

通过实例验证：

1. 创建库表

create database test4;
use test4
create table tbl_1(a decimal(5, 2));

decimal(5, 2) 表示精度为5，刻度为2，可以用5位有效数字，2位小数。

2. 插入记录

insert into tbl_1 select -999.99;
insert into tbl_1 select 999.99;
select * from tbl_1;
+------+
| a    |
+------+
| 3.14 |
+------+

mysql> insert into tbl_1 select -10000.99;
ERROR 1264 (22003): Out of range value for column 'a' at row 1

3. 写入超过刻度的数据

mysql> insert into tbl_1 select 3.141;
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> select * from tbl_1;
+---------+
| a       |
+---------+
| -999.99 |
|  999.99 |
|    3.14 |
+---------+
3 rows in set (0.00 sec)

会发现超过刻度的数据只保留两2位小数。

4. 浮点数与定点数区别

mysql> create table tbl_2(a float, b decimal(8, 4));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into tbl_2 select 7777.7777, 7777.7777;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into tbl_2 select 66.6666, 66.6666;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from tbl_2;
+---------+-----------+
| a       | b         |
+---------+-----------+
| 7777.78 | 7777.7777 |
| 66.6666 |   66.6666 |
+---------+-----------+
2 rows in set (0.01 sec)

从实验可以看到float的数据存储到表中会存在误差，使用中禁止使用float类型来保存于金钱相关的数据。

5. 默认定点类型精度

mysql> create table tbl_3(a  decimal);

Query OK, 0 rows affected (0.13 sec)

mysql> show create table tbl_3;
+-------+----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                               |
+-------+----------------------------------------------------------------------------------------------------------------------------+
| tbl_3 | CREATE TABLE `tbl_3` (
  `a` decimal(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

发现它会默认转换为 decimal(10,0)。

3.4场景选择

类型大小 (Bytes)有符号范围无符号范围
FLOAT$4$$(-3.402823466E+38, -1.175494351E-38), 0, (1.175494351E-38, 3.402823466351E+38)$$0, (1.175494351E-38, 3.402823466E+38)$
DOUBLE$8$$(-1.7976931348623157E+308, -2.2250738585072014E-308), 0, (2.2250738585072014E-308, 1.7976931348623157E+308)$$0, (2.2250738585072014E-308, 1.7976931348623157E+308)$
DECIMAL精度 $+ 2$依赖于精度（Precision）和标度（Scale）依赖于精度（Precision）和标度（Scale）

补充说明：

  1. FLOAT 和 DOUBLE：属于近似值类型（浮点数），在涉及财务或非误差允许的计算（如金额）时，不建议使用，因为会存在精度损失。
  2. DECIMAL：属于精确值类型（定点数）。它以字符串形式存储，通常用于银行账目等对精度要求极高的场景。
    • 精度 (M)：总位数（不包括小数点）。
    • 标度 (D)：小数点后的位数。
    • 例如 DECIMAL(10,2) 表示总共 10 位，其中 2 位是小数。

4时间类型

4.1DATE

  • 日期类型。存储的年月日

1. 创建库表

create database test5;
use test5
create table tbl_1(a date);

2. 插入记录

insert into tbl_1 select '2026-05-21';
mysql> select * from tbl_1;
+------------+
| a          |
+------------+
| 2026-05-21 |
+------------+
1 row in set (0.01 sec)

4.2TIME

  • 时间类型，存储的时分秒

插入记录

create table tbl_2(a time);
insert into tbl_2 select '20:08:08';
mysql> select * from tbl_2;
+----------+
| a        |
+----------+
| 20:08:08 |
+----------+
1 row in set (0.00 sec)

插入超过范围的记录

mysql> insert into tbl_2 select '838:08:08';
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into tbl_2 select '839:08:08';
ERROR 1292 (22007): Incorrect time value: '839:08:08' for column 'a' at row 1

4.3YEAR

  • 存储4位格式的年份
mysql> create table tbl_3(a year);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into tbl_3 select '2026';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from tbl_3;
+------+
| a    |
+------+
| 2026 |
+------+
1 row in set (0.00 sec)

插入超过范围的记录

mysql> insert into tbl_3 select '2156';
ERROR 1264 (22003): Out of range value for column 'a' at row 1
mysql>

4.4DATETIME

  • 存储年月日时分秒的数据

4.5TIMESTAMP

  • 存储年月日时分秒的数据，但是带有时区的属性，它会随服务器时区变化。
create table tbl_4(a datetime, b timestamp);
insert into tbl_4 values(now(), now());
select * from tbl_4;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2026-05-21 12:39:53 | 2026-05-21 12:39:53 |
+---------------------+---------------------+
1 row in set (0.00 sec)

可以看到写入的时间是一样的。

现在调整数据库时区，然后查询数据:

mysql> set time_zone='+05:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tbl_4;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2026-05-21 12:39:53 | 2026-05-21 09:39:53 |
+---------------------+---------------------+
1 row in set (0.00 sec)

会发现两个字段的值变了，这就表示了timestamp会随着时区而变化。

记录数据更新时间和创建时间：

create table tbl_5(
    id int primary key auto_increment comment '主键', 
    name varchar(10),
    create_time timestamp not null default CURRENT_TIMESTAMP comment '创建时间', 
    update_time timestamp not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '更新时间'
) engine=innodb charset=utf8mb4;
insert into tbl_5(name) select 'aa';
mysql> select * from tbl_5;
+----+------+---------------------+---------------------+
| id | name | create_time         | update_time         |
+----+------+---------------------+---------------------+
|  1 | aa   | 2026-05-21 12:47:55 | 2026-05-21 12:47:55 |
+----+------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> update tbl_5 set name='bb' where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tbl_5;
+----+------+---------------------+---------------------+
| id | name | create_time         | update_time         |
+----+------+---------------------+---------------------+
|  1 | bb   | 2026-05-21 12:47:55 | 2026-05-21 12:49:30 |
+----+------+-----------
  • CURRENT_TIMESTAMP 表示新增数据时如果不指定时间则默认使用当前时间。
  • ON UPDATE CURRENT_TIMESTAMP 表示如果记录出现更新，则修改更新时间为当前时间。

4.6选择

根据您提供的第三张图片，关于日期和时间类型的 Markdown 表格如下：

类型大小 (Bytes)范围格式用途
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:01’ UTC 到 ‘2038-01-19 03:14:07’YYYY-MM-DD hh:mm:ss混合日期和时间值

核心区别：

  1. DATETIME vs TIMESTAMP：
    • 范围：DATETIME 范围更广（公元 1000 年开始），而 TIMESTAMP 只能支持到 2038 年（受限于 $32$ 位存储）。
    • 时区：TIMESTAMP 与时区相关，会根据数据库服务器的时区设置进行转换，而 DATETIME 存储后是什么样，读出来就是什么样，不受时区影响。
    • 空间：TIMESTAMP 仅占 $4$ 字节，比 DATETIME 的 $8$ 字节更省空间。
  2. TIME：它的范围其实超过了 24 小时（$-838$ 到 $838$），因为它不仅可以表示一天内的时间，还可以用来记录两个事件之间的时间间隔（持续时间）。

更新日志

  • 2026-05-21 重新执行所有操作语句。