MySQL数据类型

# 数据类型分类

分类	数据类型	说明
数值类型	BIT(M)	位类型：M指定位数，默认值为1，范围为1-64
数值类型	BOOL	布尔类型：使用1表示真，使用0表示假
数值类型	TINYINT [UNSIGNED]	占用1字节，默认为有符号
数值类型	SMALLINT [UNSIGNED]	占用2字节，默认为有符号
数值类型	MEDIUMINT [UNSIGNED]	占用3字节，默认为有符号
数值类型	INT [UNSIGNED]	占用4字节，默认为有符号
数值类型	BIGINT [UNSIGNED]	占用8字节，默认为有符号
数值类型	FLOAT[(M,D)] [UNSIGNED]	M指定显示长度，D指定小数位数，占用4字节
数值类型	DOUBLE[(M,D)] [UNSIGNED]	M指定显示长度，D指定小数位数，占用8字节
数值类型	DECIMAL(M,D) [UNSIGNED]	M指定显示长度，D指定小数位数，每4个字节表示9个数字，小数点占用1字节
文本、二进制类型	CHAR(L)	固定长度字符串：L指定字符串长度，最大为255
文本、二进制类型	VARCHAR(L)	可变长度字符串：L指定字符串长度上限，最多占用65535字节
文本、二进制类型	BLOB	用于存储二进制数据
文本、二进制类型	TEXT	用于存储大文本数据
时间日期	时间日期	DATE / DATETIME
时间日期	TIMESTAMP	时间戳：以YYYY-MM-DD HH:MM:SS格式进行显示
字符串类型	ENUM	枚举类型：ENUM类型的取值范围需要在定义字段时进行指定，设置字段值时只允许从成员中选取单个值，其所需的存储空间由定义ENUM类型时指定的成员个数决定
字符串类型	SET	集合类型：SET类型的取值范围需要在定义字段时进行指定，设置字段值时可以从成员中选取一个或多个值，其所需的存储空间由定义SET类型时指定的成员个数决定

# 数值类型

# tinyint

表中包含一个tinyint类型的列，默认其为有符号类型，-128~127范围内的数据时都能成功插入，否则会插入失败。

# bit

MySQL的BIT数据类型用于存储位字段值，它可以存储固定数量的二进制位（0或1），适用于存储标志、开关等二进制数据。以下是关于BIT数据类型的详细信息：

BIT(M) 数据类型可以存储 M 位的二进制数，其中 M 的取值范围是 1 到 64 1。
如果不指定 M，则默认为 1。这意味着 BIT 和 BIT(1) 是相同的

例如：增加online列，表示当前的状态

mysql> alter table student add online bit comment '状态' after gender;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(32) | YES  |     | NULL    |       |
| age    | int         | YES  |     | NULL    |       |
| gender | varchar(2)  | YES  |     | NULL    |       |
| online | bit(1)      | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

1
2
3
4
5
6
7
8
9
10
11
12
13

# float

语法： FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

M：指定显示长度，默认为1，范围是1-24。
D：指定小数位数，默认为0，范围是0-30。
UNSIGNED：无符号类型，默认为有符号类型。
ZEROFILL：填充0

mysql> alter table student add grade float(5, 1) comment '成绩' after online;
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc student;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(32) | YES  |     | NULL    |       |
| age    | int         | YES  |     | NULL    |       |
| gender | varchar(2)  | YES  |     | NULL    |       |
| online | bit(1)      | YES  |     | NULL    |       |
| grade  | float(5,1)  | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> insert into student value('cen', 20, '男', 1, 99.5);
Query OK, 1 row affected (0.00 sec)

mysql> select *from student;
+----------+------+--------+----------------+-------+
| name     | age  | gender | online         | grade |
+----------+------+--------+----------------+-------+
| zahngsan |   22 | 男     | NULL           |  NULL |
| lisi     |   24 | 男     | NULL           |  NULL |
| lisi     |   24 | 男     | NULL           |  NULL |
| cen      |   20 | 女     | NULL           |  NULL |
| John     |   30 | 女     | NULL           |  NULL |
| NULL     | NULL | NULL   | 0x01           |  NULL |
| cen      |   20 | 男     | 0x01           |  99.5 |
+----------+------+--------+----------------+-------+
7 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

注意：当向一个 FLOAT 字段插入值时，如果该值超出了字段定义的精度范围，则会根据其内部规则进行四舍五入或者截断。

# decimal

在MySQL中，decimal和float类型用于存储数值数据，它们之间的区别主要在于精度和存储空间。 decimal和float类型的使用方式一样，但decimal的精度比float更高。

mysql> 
mysql> create table if not exists wages(
    -> f1 float(10, 8),
    -> f2  decimal(10, 8)
    -> );
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| employee       |
| student        |
| wages          |
+----------------+
3 rows in set (0.01 sec)

mysql> use wages
ERROR 1049 (42000): Unknown database 'wages'

mysql> insert into wages values(10.888888, 10.888888);
Query OK, 1 row affected (0.01 sec)

mysql> insert into wages values(10.88888888, 10.88888888);
Query OK, 1 row affected (0.00 sec)

mysql> desc wages;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| f1    | float(10,8)   | YES  |     | NULL    |       |
| f2    | decimal(10,8) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> select *from wages;
+-------------+-------------+
| f1          | f2          |
+-------------+-------------+
| 10.88888836 | 10.88888800 |
| 10.88888931 | 10.88888888 |
+-------------+-------------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

可见decimal类型的精度比float高很多。

# 字符串类型

# char

语法： CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

M：指定字符的最大长度，默认为1，范围是0-255。
CHARACTER SET charset_name：指定字符集，默认为latin1。
COLLATE collation_name：指定排序规则，默认为latin1_swedish_ci。

mysql> create table if not exists message( mes char(20) );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into message values('我们是总冠军！！！');
Query OK, 1 row affected (0.00 sec)

mysql> select * from message;
+-----------------------------+
| mes                         |
+-----------------------------+
| 我们是总冠军！！！          |
+-----------------------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13

# varchar

语法： VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]

M：指定字符的最大长度，默认为1，范围是0-65535。
CHARACTER SET charset_name：指定字符集，默认为latin1。
COLLATE collation_name：指定排序规则，默认为latin1_swedish_ci。 VARCHAR和CHAR的区别是：
VARCHAR可以存储长度为0-65535字节的字符串，而CHAR只能存储长度为0-255的字符串。
VARCHAR可以存储变长的字符串，而CHAR可以存储定长的字符串。

char和varchar的优缺点如下：

char类型的数据是定长的，因此磁盘空间比较浪费，但是效率高（直接访问定长的空间）。
varchar类型的数据是变长的，因此磁盘空间比较节省，但是效率低（需要先读取存储字符串的长度，再访问指定长度的空间）。

如果要存储的数据是定长的，那就使用char类型进行存储，比如身份证号码、手机号、md5等。如果要存储的数据是变长的，那就使用varchar类型进行存储，比如名字、地址等。

# 时间日期类型

# date

语法： DATE DATE用于存储日期，格式为YYYY-MM-DD。 DATE类型用于存储日期，格式为YYYY-MM-DD。

# datetime

语法： DATETIME DATETIME用于存储日期和时间，格式为YYYY-MM-DD HH:MM:SS。 DATETIME类型用于存储日期和时间，格式为YYYY-MM-DD HH:MM:SS。

# timestamp

语法： TIMESTAMP TIMESTAMP用于存储日期和时间，格式为YYYY-MM-DD HH:MM:SS。 TIMESTAMP类型用于存储日期和时间，格式为YYYY-MM-DD HH:MM:SS。如果发现TIMESTAMP类型不是自动更新的，如下：

+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| t1    | date      | YES  |     | NULL    |       |
| t2    | datetime  | YES  |     | NULL    |       |
| t3    | timestamp | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+

1
2
3
4
5
6
7

可以改为以下方式创建表，可以恢复正常：

mysql> create table if not exists time( 
  -> t1 date, t2 datetime, 
  -> t3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 
  ->);
Query OK, 0 rows affected (0.03 sec)

mysql> desc time;
+-------------+-----------+------+-----+-------------------+-----------------------------------------------+
| Field       | Type      | Null | Key | Default           | Extra                                         |
+-------------+-----------+------+-----+-------------------+-----------------------------------------------+
| t1          | date      | YES  |     | NULL              |                                               |
| t2          | datetime  | YES  |     | NULL              |                                               |
| t3          | timestamp | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------------+-----------+------+-----+-------------------+-----------------------------------------------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14

mysql> select* from time;
+------------+---------------------+---------------------+
| t1         | t2                  | update_time         |
+------------+---------------------+------+--------------+
| 2020-01-01 | 2024-12-01 08:00:00 | 2025-03-05 10:23:02 |
+------------+---------------------+------+--------------+
1 row in set (0.00 sec)

mysql> update time1 set t1 = '2024-11-29';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select* from time1;
+------------+---------------------+---------------------+
| t1         | t2                  | update_time         |
+------------+---------------------+------+--------------+
| 2024-11-29 | 2024-12-01 08:00:00 | 2025-03-05 10:23:36 |
+------------+---------------------+------+--------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# enum和set类型

enum和set类型用于存储枚举值和集合值，它们都是字符串类型。 enum类型用于存储枚举值，它的值是枚举类型的常量，比如性别、颜色等。 set类型用于存储集合值，它的值是多个枚举类型的常量的组合，比如爱好、权限等。 enum类型和set类型都是字符串类型，它们都是用于存储枚举值和集合值的类型。

mysql> create table if  not exists votes ( name varchar(20), gender enum('男', '女'), hobby set('羽毛球', '写代码', '乒乓球', '足球'));
Query OK, 0 rows affected (0.03 sec)

mysql> desc votes;
+--------+---------------------------------------------------+------+-----+---------+-------+
| Field  | Type                                              | Null | Key | Default | Extra |
+--------+---------------------------------------------------+------+-----+---------+-------+
| name   | varchar(20)                                       | YES  |     | NULL    |       |
| gender | enum('男','女')                                   | YES  |     | NULL    |       |
| hobby  | set('羽毛球','写代码','乒乓球','足球')              | YES  |     | NULL    |       |
+--------+---------------------------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into votes values('cen', '男', '写代码,乒乓球');
Query OK, 1 row affected (0.01 sec)

mysql> insert into votes values('cen', 1, '写代码');
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from votes;
+------+--------+---------------------+
| name | gender | hobby               |
+------+--------+---------------------+
| cen  | 男     | 写代码,乒乓球       |
| cen  | 男     | 写代码              |
| cen  | 男     | 羽毛球              |
+------+--------+---------------------+
3 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

如上述示例中发现：

enum中可以使用数字来表示，从1开始记录下标，比如1表示男，2表示女。
set的数字表示位图，从低位到高位对应从左向右表示。

例如：

mysql> insert into votes values('cen', 1, 7);
Query OK, 1 row affected (0.00 sec)

--- 7 => 1111

mysql> select * from votes;
+------+--------+-------------------------------+
| name | gender | hobby                         |
+------+--------+-------------------------------+
| cen  | 男     | 写代码,乒乓球                 |
| cen  | 男     | 写代码                        |
| cen  | 男     | 羽毛球                        |
| cen  | 男     | 乒乓球                        |
| cen  | 男     | 羽毛球,写代码,乒乓球          |
+------+--------+-------------------------------+
5 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

set的查找

mysql> select * from votes;
+------+--------+-------------------------------+
| name | gender | hobby                         |
+------+--------+-------------------------------+
| cen  | 男     | 写代码,乒乓球                 |
| cen  | 男     | 写代码                        |
| cen  | 男     | 羽毛球                        |
| cen  | 男     | 乒乓球                        |
| cen  | 男     | 羽毛球,写代码,乒乓球          |
+------+--------+-------------------------------+
5 rows in set (0.00 sec)

-- 查找性别为男的
mysql> select * from votes where gender='男';
+------+--------+-------------------------------+
| name | gender | hobby                         |
+------+--------+-------------------------------+
| cen  | 男     | 写代码,乒乓球                 |
| cen  | 男     | 写代码                        |
| cen  | 男     | 羽毛球                        |
| cen  | 男     | 乒乓球                        |
| cen  | 男     | 羽毛球,写代码,乒乓球          |
+------+--------+-------------------------------+
5 rows in set (0.00 sec)

-- 查找爱好为写代码的
mysql> select * from votes where find_in_set('写代码',hobby);
+------+--------+-------------------------------+
| name | gender | hobby                         |
+------+--------+-------------------------------+
| cen  | 男     | 写代码,乒乓球                 |
| cen  | 男     | 写代码                        |
| cen  | 男     | 羽毛球,写代码,乒乓球          |
+------+--------+-------------------------------+
3 rows in set (0.00 sec)

-- 查找爱好为写代码的，但是不仅仅包含写代码
mysql> select * from votes where find_in_set('写代码',hobby) and hobby<> '写代码';
+------+--------+-------------------------------+
| name | gender | hobby                         |
+------+--------+-------------------------------+
| cen  | 男     | 写代码,乒乓球                 |
| cen  | 男     | 羽毛球,写代码,乒乓球          |
+------+--------+-------------------------------+
2 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

上次更新: 2025/03/14, 11:20:46

← 表的操作表的约束→