MySQL数据库基础、表的操作与数据类型完整指南

平时做技术实践时，很多问题不是概念不会，而是细节没串起来。拿“MySQL数据库基础、表的操作与数据类型”来说，它看着像小点，放到项目里常会牵出环境、配置、兼容性和维护成本。下面按实际采用顺序，把思路、关键写法和容易踩坑的地方讲清楚，便于大家直接对照操作。

MySQL 从零入门（二）：表的操作与数据类型详解

前言

结合项目来看，在上一篇《MySQL 从零入门（一）：环境搭建、数据库基础与库操作详解》中，我们完成了 MySQL 5.7 的部署，同时学习了数据库的新建、查看、修改、删除以及备份恢复。

但是，仅仅新建数据库还不能完成实际业务的数据管理。

比如，在开发一个学生管理系统时，我们可能需保存以下信息：

这就涉及两个问题：

1. 如何设计、新建和修改数据库中的表？
2. 不同字段应该采用什么数据类型？

实际处理时，比如，年龄应该采用整数类型，成绩可能需小数类型，姓名需字符串类型，而入学日期应该采用日期类型。

本篇围绕这两个问题展开。

一、MySQL 表的基本概念

1.1 数据库与表的关系

结合项目来看，在 MySQL 里，一个数据库能够包含多张表，而每张表能够保存多条记录。

比如：

MySQL Server
│
└── school（数据库）
    │
    ├── student（学生表）
    │   ├── id
    │   ├── name
    │   ├── age
    │   └── score
    │
    ├── teacher（教师表）
    │   ├── id
    │   └── name
    │
    └── course（课程表）
        ├── id
        └── course_name

其中：

• 数据库（Database）：组织和管理一组相关的数据对象。
• 表（Table）：按照行和列组织数据。
• 字段（Column）：定义某一类数据的属性。
• 记录（Row）：表中的一行具体数据。

比如：

+------+--------+------+
| id   | name   | age  |
+------+--------+------+
| 1001 | 张三   |   18 |
| 1002 | 李四   |   19 |
+------+--------+------+

这张表包含三个字段，两条记录。

这里需留意，表的结构和表中的数据并不是同一个概念。

比如：

ALTER TABLE student
MODIFY name VARCHAR(60);

这是修改表结构。

而：

UPDATE student
SET name = '王五'
WHERE id = 1001;

这是修改表中的数据。

两者操作的对象不同。

1.2 准备实验环境

首先进入 MySQL。

落到代码里，由于当前采用 Docker 部署 MySQL 5.7，同时采用 mycli 客户端，因此在 Ubuntu 终端执行：

mycli -h 127.0.0.1 -P 3306 -u root

连接成功后，新建专用实验数据库：

CREATE DATABASE IF NOT EXISTS lab03
CHARACTER SET utf8
COLLATE utf8_general_ci;

切换数据库：

USE lab03;

查看当前数据库：

SELECT DATABASE();

预期结果：

+------------+
| DATABASE() |
+------------+
| lab03      |
+------------+

后续第三章实验均在 lab03 中完成，避免影响已有数据库。

二、CREATE TABLE：新建数据表

2.1 基本语法

MySQL 采用 CREATE TABLE 新建数据表。

基本语法如下所示：

CREATE TABLE table_name (
    field1 datatype,
    field2 datatype,
    field3 datatype
)
CHARACTER SET 字符集
COLLATE 校验规则
ENGINE=存储引擎;

逐个解释：

其中字符集、校验规则和存储引擎能够省略。

若没有指定字符集和校验规则，通常会采用当前数据库的默认设置。

若没有指定存储引擎，则采用服务器默认存储引擎。

MySQL 5.7 默认采用 InnoDB。

2.2 新建轻松的 student 表

CREATE TABLE student (
    id INT,
    name VARCHAR(20),
    age INT
);

这里出现了两个数据类型：

INT

整数类型，能够存储整数。

比如：

18
20
1001

VARCHAR(20)

可变长度字符串类型，最多能够保存 20 个字符。

比如：

张三
李四
ZhangSan

这里需留意，VARCHAR(20) 中的 20 是字符数，而不直接表示 20 字节。

对于采用 UTF-8 编码的字符串，一个汉字可能需三个字节。

2.3 为什么字段之间需逗号？

观察：

CREATE TABLE student (
    id INT,
    name VARCHAR(20),
    age INT
);

括号中的每一项都是一个字段定义。

逗号用来分隔不同字段。

最后一个字段：

age INT

后面不需逗号。

整个 SQL 用分号 ; 结束。

2.4 查看新建结果

执行：

SHOW TABLES;

其中：

• SHOW：查看。
• TABLES：当前数据库中的表。

如果新建成功，会看到：

+-----------------+
| Tables_in_lab03 |
+-----------------+
| student         |
+-----------------+

三、新建 users 表：字段注释与存储引擎

3.1 案例

我们采用了下面的建表语句：

CREATE TABLE users (
    id INT,
    name VARCHAR(20) COMMENT '用户名',
    password CHAR(32) COMMENT '密码是32位的md5值',
    birthday DATE COMMENT '生日'
)
CHARACTER SET utf8
ENGINE=MyISAM;

这里出现了三个新知识点：

• COMMENT：字段注释。
• CHAR：定长字符串。
• DATE：日期类型。

另外，还指定了 MyISAM 存储引擎。

3.2 COMMENT：字段注释

比如：

name VARCHAR(20) COMMENT '用户名'

其中：

COMMENT '用户名'

用来给字段添加说明。

注释不会改变字段本身的存储逻辑，常用来提高表结构的可读性。

3.3 CHAR 类型

password CHAR(32)

表示一个定长字符类型，声明长度为 32 个字符。

与 VARCHAR 不同，CHAR 适用来长度相对固定的数据。

3.4 DATE 类型

birthday DATE

表示日期类型。

常用格式：

YYYY-MM-DD

比如：

2006-10-12

其中：

• YYYY：年份。
• MM：月份。
• DD：日期。

3.5 ENGINE：存储引擎

指定：

ENGINE=MyISAM

表示采用 MyISAM 存储引擎。

MySQL 5.7 中，MyISAM 表通常对应三个文件：

users.frm
users.MYD
users.MYI

它们分别用来：

落到代码里，因为我们采用 Docker，能够直接在 Ubuntu 终端查看容器内的数据目录：

docker exec mysql57 ls /var/lib/mysql/lab03/

应该能够看到对应文件。

注意，这些是 MySQL 管理的数据文件，不能随意修改或删除。

落到代码里，另外，MyISAM 不兼容事务。实际后端业务系统通常优先采用 InnoDB。

四、DESC：查看表结构

4.1 基本语法

新建完表之后，能够采用：

DESC users;

其中 DESC 是 DESCRIBE 的缩写，表示查看表结构。

它与：

ORDER BY age DESC

中的 DESC 用法不同。后者代表降序排序。

4.2 查看结果

执行：

DESC users;

预期结果类似：

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| password | char(32)    | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

每一列的含义：

需留意：

int(11) 中的 11 是 MySQL 5.7 的整数显示宽度，不代表整数占用 11 字节。

普通 INT 通常占用 4 字节。

另外：

NULL

和：

''

并不相同。

NULL 表示未知或缺失的值，而 '' 表示空字符串。

如果想要查看字段注释，还能够执行：

SHOW FULL COLUMNS FROM users;

五、ALTER TABLE：修改表结构

在真实项目中，表结构经常需调整。

比如：

• 新增字段；
• 修改字段类型；
• 删除字段；
• 修改表名；
• 修改字段名。

这些操作都能够借助 ALTER TABLE 完成。

5.1 准备实验数据

我们有这样一个案例：

INSERT INTO users VALUES
(1, 'a', 'b', '1982-01-04'),
(2, 'b', 'c', '1984-01-04');

这里：

• INSERT INTO：插入数据。
• users：表名。
• VALUES：指定插入的值。

每组括号对应一条记录。

查询：

SELECT * FROM users;

预期结果：

+------+------+----------+------------+
| id   | name | password | birthday   |
+------+------+----------+------------+
| 1    | a    | b        | 1982-01-04 |
| 2    | b    | c        | 1984-01-04 |
+------+------+----------+------------+

5.2 ADD：添加字段

假设现在需增加一列，用来保存图片路径。

执行：

ALTER TABLE users
ADD assets VARCHAR(100)
COMMENT '图片路径'
AFTER birthday;

逐个解释：

然后执行：

DESC users;

应该会出现新增的 assets 字段。

查询数据：

SELECT * FROM users;

理解这一步时，能够发现原有记录没有丢失，只是新增字段目前为 NULL。

这是因为该字段允许 NULL，而且没有指定其他默认值。

5.3 MODIFY：修改字段类型

假设原来的 name 只允许保存 20 个字符，现在希望增加到 60 个字符。

执行：

ALTER TABLE users
MODIFY name VARCHAR(60);

这里：

• MODIFY：修改列定义。
• name：需修改的字段。
• VARCHAR(60)：新的字段类型。

执行：

DESC users;

此时 name 应该变为：

varchar(60)

需留意，MODIFY 重新定义字段时，应完整保留需的约束、默认值和注释等属性。

比如，为了保留字段注释，能够写成：

ALTER TABLE users
MODIFY name VARCHAR(60)
COMMENT '用户名';

5.4 DROP：删除字段

下面删除 password 字段：

ALTER TABLE users
DROP password;

也能够写成：

ALTER TABLE users
DROP COLUMN password;

其中：

• DROP：删除。
• COLUMN：列。

注意：

删除字段不仅会删除字段定义，还会删除这一列原本保存的数据。

所以，必须确认当前操作的是实验表。

执行完成后：

DESC users;

能够发现 password 字段已经不存在。

5.5 RENAME：修改表名

将 users 表修改为 employee：

ALTER TABLE users
RENAME TO employee;

其中：

• RENAME：重命名。
• TO：指定新名称。
• employee：新表名。

执行：

SHOW TABLES;

能够发现原来的 users 已经变成 employee。

然后：

SELECT * FROM employee;

仍然能够查询到原有数据。

这说明修改表名通常不会删除表中的记录。

5.6 CHANGE：修改字段名

假设需将：

name

修改为：

xingming

执行：

ALTER TABLE employee
CHANGE name xingming VARCHAR(60);

语法能够理解为：

CHANGE 旧字段名 新字段名 新字段定义;

比如：

CHANGE name xingming VARCHAR(60)

表示将 name 字段修改成 xingming，同时采用 VARCHAR(60) 类型。

为了保留原注释，也能够写成：

ALTER TABLE employee
CHANGE name xingming VARCHAR(60)
COMMENT '用户名';

最后执行：

DESC employee;

确认结果。

5.7 MODIFY 与 CHANGE 的区别

比如：

ALTER TABLE employee
MODIFY xingming VARCHAR(100);

这是修改字段长度。

而：

ALTER TABLE employee
CHANGE xingming real_name VARCHAR(100);

则是修改字段名称。

六、DROP TABLE：删除表

6.1 基本语法

DROP TABLE table_name;

比如：

DROP TABLE student;

表示删除 student 表。

也能够采用：

DROP TABLE IF EXISTS student;

若表不存在，IF EXISTS 能够避免通常的“未知表”错误。

6.2 DROP TABLE 和 DROP DATABASE 的区别

比如：

DROP TABLE student;

只针对指定的表。

而：

DROP DATABASE lab03;

会删除整个实验数据库。

这里需特别注意：DROP TABLE 是 DDL 操作，在 MySQL 5.7 中通常会发生隐式提交，不能依靠普通事务回滚来恢复。

因此，重要表应在删除前做好备份。

七、MySQL 数据类型概述

学习完对表的操作后我们进入下一个内容：MySQL 数据类型。

新建表时，需为每个字段指定数据类型。

比如：

CREATE TABLE student (
    id INT,
    name VARCHAR(20),
    score DECIMAL(5,2),
    birthday DATE
);

为什么要区分类型？

因为不同的数据具有不同特点。

• id 是整数。
• name 是字符串。
• score 需保存小数。
• birthday 是日期。

若采用不合适的数据类型，可能导致存储空间浪费、精度丢失或数据越界。

7.1 数据类型分类

MySQL 5.7 常用的数据类型如下所示：

下面能够单独新建实验数据库：

CREATE DATABASE IF NOT EXISTS lab04
CHARACTER SET utf8
COLLATE utf8_general_ci;
USE lab04;

八、整数类型：TINYINT、SMALLINT、INT、BIGINT

8.1 整数类型的区别

整数类型主要区别在于占用空间和可表示的数值范围。

这里的“有符号”表示既能够保存负数，也能够保存正数。

比如：

CREATE TABLE tt1 (
    num TINYINT
);

此时 num 的取值范围是：

-128 ~ 127

8.2 整数越界实验

插入合法值：

INSERT INTO tt1 VALUES (1);

然后：

INSERT INTO tt1 VALUES (128);

在严格 SQL 模式下，MySQL 会报错：

ERROR 1264 (22003):
Out of range value for column 'num' at row 1

这是因为 128 超过了有符号 TINYINT 的最大值 127。

能够理解为：

数据类型决定字段能够保存的数据范围，超过范围的数据可能无法成功插入。

8.3 UNSIGNED：无符号整数

如果字段不需保存负数，能够采用：

UNSIGNED

比如：

CREATE TABLE tt2 (
    num TINYINT UNSIGNED
);

无符号 TINYINT 的范围是：

0 ~ 255

尝试插入：

INSERT INTO tt2 VALUES (-1);

在严格模式下会报错。

而：

INSERT INTO tt2 VALUES (255);

能够成功执行。

8.4 为什么不总是采用 UNSIGNED？

对于相同字节数的整数类型，无符号类型能够表示更大的非负数。

但是，采用 UNSIGNED 也意味着无法存储负数，同时可能在计算和类型转换时带来额外限制。

课件提出一个建议：

如果 INT 的范围不足，不一定要优先改成 INT UNSIGNED，也能够考虑 BIGINT。

实际项目需根据业务范围确定类型。

实际处理时，比如，用户年龄不需负数，但数据库主键、计数器和涉及复杂运算的字段需结合业务约束综合设计。

九、BIT：位类型

9.1 什么是 BIT？

BIT 用来保存位字段。

基本语法：

BIT(M)

其中 M 表示位数，取值范围为 1～64。

比如：

CREATE TABLE tt4 (
    id INT,
    a BIT(8)
);

这里的 BIT(8) 表示一个 8 位的字段。

8 位能够表示：

00000000 ~ 11111111

对应十进制：

0 ~ 255

9.2 BIT 查询为什么可能显示乱码？

执行：

INSERT INTO tt4 VALUES (10, 10);

然后：

SELECT * FROM tt4;

你可能发现 a 字段没有像普通整数一样显示 10。

原因是 BIT 类型在客户端查询结果中可能以二进制内容的形式得到，某些字节不是可打印字符。

比如：

INSERT INTO tt4 VALUES (65, 65);

十进制 65 对应的字节是：

01000001

在这个场景下，在 ASCII 编码中对应字符 A。

所以有些客户端可能显示 A。

但是应注意：BIT 的本质是位字段，并不是字符类型。 显示结果与客户端如何处理二进制数据有关。

为了便于查看数值，能够执行：

SELECT id, a + 0 AS bit_value
FROM tt4;

这里：

• a + 0：让 BIT 字段参与数值运算。
• AS bit_value：为结果列设置别名。

这样就能够更直观地查看数值。

9.3 BIT(1) 实验

新建：

CREATE TABLE tt5 (
    gender BIT(1)
);

插入：

INSERT INTO tt5 VALUES (0);
INSERT INTO tt5 VALUES (1);

如果再插入：

INSERT INTO tt5 VALUES (2);

会因为超出一位所能表示的范围而失败，具体错误信息与 SQL 模式有关。

理解这一步时，需留意，虽然 BIT(1) 能够保存 0 和 1，但它同时不只用来性别字段，也能够用来表示某种二进制状态。

十、浮点数 FLOAT 与定点数 DECIMAL

这一部分很重要，涉及数据库中小数的精度问题。

10.1 FLOAT 类型

FLOAT 是单精度浮点数，通常占用 4 字节。

MySQL 5.7 兼容的声明形式：

FLOAT(M,D)

其中：

• M：总显示位数。
• D：小数位数。

比如：

CREATE TABLE tt6 (
    id INT,
    salary FLOAT(4,2)
);

按照课件中的例子，FLOAT(4,2) 能够理解为保留两位整数和两位小数。

插入：

INSERT INTO tt6 VALUES (100, -99.99);
INSERT INTO tt6 VALUES (101, -99.991);

查询：

SELECT * FROM tt6;

结果可能为：

+------+--------+
| id   | salary |
+------+--------+
| 100  | -99.99 |
| 101  | -99.99 |
+------+--------+

实际处理时，需留意，FLOAT 是近似数值类型，底层采用浮点数表示，不能把 FLOAT(M,D) 理解成一定能够精确保存 M 位十进制数字。

10.2 FLOAT 的精度问题

为什么 FLOAT 不能精确表示所有小数？

因为计算机通常采用二进制浮点格式保存 FLOAT。

类似十进制无法用有限位数精确表示：

1 3 = 0.333333 … \frac{1}{3}=0.333333\ldots 31=0.333333…

二进制浮点数也无法精确表示某些十进制小数。

因此，在数据库中：

23.12345612

经过 FLOAT 存储后，可能出现一定的精度误差。

单精度 FLOAT 通常只有约 6～7 位有效十进制数字的精度。

10.3 DECIMAL：定点数

DECIMAL 用来保存精确十进制数值。

基本语法：

DECIMAL(M,D)

其中：

• M：总有效十进制位数。
• D：小数位数。

比如：

DECIMAL(5,2)

表示总共最多 5 位十进制数字，其中 2 位在小数点后面。

所以有符号 DECIMAL(5,2) 的范围为：

-999.99 ~ 999.99

10.4 FLOAT 与 DECIMAL 精度对比

按照课件新建表：

CREATE TABLE tt8 (
    id INT,
    salary FLOAT(10,8),
    salary2 DECIMAL(10,8)
);

插入：

INSERT INTO tt8 VALUES
(100, 23.12345612, 23.12345612);

查询：

SELECT * FROM tt8;

可能看到类似结果：

+------+-------------+-------------+
| id   | salary      | salary2     |
+------+-------------+-------------+
| 100  | 23.12345695 | 23.12345612 |
+------+-------------+-------------+

其中：

• salary 采用 FLOAT，可能产生精度误差。
• salary2 采用 DECIMAL，能够精确表示该范围内的小数。

因此：

理解这一步时，若需保存金额等要求精确十进制表示的数据，应优先采用 DECIMAL。

比如：

CREATE TABLE product (
    id INT,
    price DECIMAL(10,2)
);

这里的价格字段最多允许 10 位有效数字，其中小数部分占 2 位。

10.5 FLOAT、DOUBLE、DECIMAL 的区别

在这个场景下，MySQL 5.7 中，DECIMAL 的最大精度为 65 位，小数位数最多为 30 位。

十一、字符串类型：CHAR 与 VARCHAR

11.1 CHAR：定长字符串

基本语法：

CHAR(L)

其中 L 表示最大字符数，MySQL 5.7 中最大为 255。

比如：

CREATE TABLE tt9 (
    id INT,
    name CHAR(2)
);

插入：

INSERT INTO tt9 VALUES (100, 'ab');
INSERT INTO tt9 VALUES (101, '中国');

查询：

SELECT * FROM tt9;

结果：

+------+--------+
| id   | name   |
+------+--------+
| 100  | ab     |
| 101  | 中国   |
+------+--------+

这里需留意：

CHAR(2) 表示最多两个字符，而不是两个字节。

因此两个汉字也能够保存。

如果新建：

CREATE TABLE tt10 (
    name CHAR(256)
);

就会超过 CHAR 在 MySQL 5.7 中允许的最大长度。

11.2 VARCHAR：变长字符串

基本语法：

VARCHAR(L)

其中 L 表示允许存储的最大字符数。

比如：

CREATE TABLE tt10 (
    id INT,
    name VARCHAR(6)
);

插入：

INSERT INTO tt10 VALUES (100, 'hello');
INSERT INTO tt10 VALUES (101, '我爱你，中国');

虽然第二条记录包含中文，但“我爱你，中国”总共是 6 个字符。

所以，在采用 utf8 字符集时，能够存入 VARCHAR(6) 字段。

11.3 VARCHAR 的最大长度是多少？

这里容易误解。

VARCHAR(L) 中的 L 是字符数，但是 MySQL 对行大小和字段大小还存在字节级别的限制。

在这个场景下，MySQL 行大小通常有 65535 字节的限制，此外还需计算 NULL 标记、字段长度信息等开销。

所以 VARCHAR 的最大字符数不仅与声明有关，还受到以下因素影响：

• 采用的字符集；
• 每个字符可能占用的最大字节数；
• 表中其他字段的大小；
• 行格式和存储引擎。

落到代码里，比如，MySQL 5.7 的 utf8 最多采用三个字节表示一个字符。

在这个场景下，真实开发场景下不建议采用接近 65535 字节限制的 VARCHAR 作为普通文本字段。

若需保存大文本，应该考虑 TEXT 类型。

11.4 CHAR 与 VARCHAR 的区别

比如：

CHAR(4)

能够保存：

abcd

也能够保存：

A

而 VARCHAR 会根据实际数据长度存储，同时附带长度信息。

在这个场景下，需说明，CHAR 与 VARCHAR 的实际存储行为还会受到字符集、字符末尾空格以及存储引擎的影响，所以不能轻松地认定 CHAR 在所有情况下都更快。

11.5 应该如何选择？

通常能够参考：

CHAR：

适合长度相对固定的字符数据。

VARCHAR：

适合长度变化较大的字符数据。

比如：

CREATE TABLE student (
    id INT,
    name VARCHAR(30),
    school_code CHAR(6)
);

这里：

• name：姓名长度不固定。
• school_code：如果业务规定编码始终为固定长度，能够采用 CHAR。

十二、日期和时间类型

MySQL 5.7 中常用的日期时间类型包括：

• DATE
• DATETIME
• TIMESTAMP

12.1 DATE

DATE 用来存储日期。

格式：

YYYY-MM-DD

比如：

2026-11-09

在 MySQL 5.7 里，DATE 通常占用 3 字节。

12.2 DATETIME

DATETIME 用来存储日期和时间。

格式：

YYYY-MM-DD HH:MM:SS

比如：

2026-11-09 10:30:00

落到代码里，MySQL 5.7 中，不带小数秒精度的 DATETIME 通常占用 5 字节。

它适合保存某个明确的日期和时间值。

12.3 TIMESTAMP

TIMESTAMP 也用来表示日期和时间。

它与 DATETIME 的一个重要区别是：

从实现思路看，TIMESTAMP 在存储与读取时涉及会话时区转换，而 DATETIME 不进行同样的时区转换。

落到代码里，MySQL 5.7 中，不带小数秒精度的 TIMESTAMP 通常占用 4 字节。

实际处理时，TIMESTAMP 的兼容范围也比 DATETIME 窄，通常适用来接近 Unix 时间戳范围内的时间值。

12.4 DATE、DATETIME 与 TIMESTAMP 对比

12.5 实验：自动更新时间

理解这一步时，我们借助 TIMESTAMP 展示了插入和更新数据时自动记录时间的行为。

实际处理时，为便于让实验在不同的 MySQL 5.7 设置下更加稳定，能够显式指定自动时间属性：

CREATE TABLE birthday (
    id INT,
    t1 DATE,
    t2 DATETIME,
    t3 TIMESTAMP
       DEFAULT CURRENT_TIMESTAMP
       ON UPDATE CURRENT_TIMESTAMP
);

这里新增两个部分：

DEFAULT CURRENT_TIMESTAMP

表示插入记录时，如果没有指定该字段的值，就默认采用当前时间。

ON UPDATE CURRENT_TIMESTAMP

表示记录发生更新时，自动更新为当前时间。

插入数据：

INSERT INTO birthday(id, t1, t2)
VALUES (1, '1997-07-01', '2008-08-08 12:01:01');

查询：

SELECT * FROM birthday;

t3 将显示插入时的时间。

再执行：

UPDATE birthday
SET t1 = '2000-01-01'
WHERE id = 1;

再次查询：

SELECT * FROM birthday;

能够观察 t3 的变化。

在这个场景下，注意，自动设置和自动更新 TIMESTAMP 的行为与字段定义及 MySQL 设置有关，不应该认为所有 TIMESTAMP 字段都会无条件自动更新。

十三、ENUM：枚举类型

13.1 什么是 ENUM？

ENUM 是枚举类型。

它的特点是：

从预先定义的多个选项中选择一个值。

比如：

ENUM('男', '女')

表示字段只能保存定义中的某一个选项。

能够理解为单选。

13.2 新建示例

CREATE TABLE vote_gender (
    id INT,
    gender ENUM('男', '女')
);

插入：

INSERT INTO vote_gender VALUES (1, '男');
INSERT INTO vote_gender VALUES (2, '女');

在正常情况下，插入的字符串应当属于枚举定义的选项。

若插入非法值，严格 SQL 模式下通常会报错。

13.3 ENUM 的内部编号

课件指出，ENUM 内部采用数字编号表示不同枚举值。

比如：

ENUM('男', '女')

对应：

男 → 1
女 → 2

因此也能够进行数字编号相关的操作。

在这个场景下，不过，真实开发场景下不建议依赖数字编号插入枚举值，因为可读性较差，而且修改枚举定义可能造成维护问题。

十四、SET：集合类型

14.1 什么是 SET？

SET 与 ENUM 不同。

ENUM 是单选，而 SET 能够从多个预定义选项中选择任意多个。

比如：

SET('登山', '游泳', '篮球', '武术')

能够保存：

登山

也能够保存：

登山,武术

14.2 新建调查表

按照课件中的案例：

CREATE TABLE votes (
    username VARCHAR(30),
    hobby SET('登山','游泳','篮球','武术'),
    gender ENUM('男','女')
);

这里：

• username：用户名。
• hobby：用户爱好，允许多选。
• gender：枚举字段，单选。

插入数据：

INSERT INTO votes VALUES
('雷锋', '登山,武术', '男');

INSERT INTO votes VALUES
('Juse', '登山,武术', '女');

INSERT INTO votes VALUES
('LiLei', '登山', '男');

INSERT INTO votes VALUES
('HanMeiMei', '游泳', '女');

查询：

SELECT * FROM votes;

能够查看所有记录。

14.3 SET 的内部实现思路

课件将 SET 与二进制位联系起来。

比如：

登山 → 0001
游泳 → 0010
篮球 → 0100
武术 → 1000

如果同时选择登山和武术：

0001
1000
----
1001

因此，能够借助不同的二进制位来表示多个选项的组合。

SET 最多允许定义 64 个成员。

这与 Linux 权限等场景中采用比特位表示多个状态的思想有相似之处。

14.4 查询喜欢登山的人

如果执行：

SELECT *
FROM votes
WHERE hobby = '登山';

只能匹配集合值恰好是 '登山' 的记录。

对于：

登山,武术

这种同时具有多个爱好的记录，不能轻松采用相等比较来完成成员匹配。

所以课件引入了：

FIND_IN_SET()

14.5 FIND_IN_SET 函数

基本语法：

FIND_IN_SET(sub, str_list)

其中：

• sub：需查找的字符串。
• str_list：以逗号分隔的字符串列表。

比如：

SELECT FIND_IN_SET('a', 'a,b,c');

结果为：

1

而：

SELECT FIND_IN_SET('d', 'a,b,c');

结果为：

0

这里的下标从 1 开始。

如果没有找到，对应结果为 0。

因此，查询所有喜欢登山的用户：

SELECT *
FROM votes
WHERE FIND_IN_SET('登山', hobby) > 0;

执行后，能够匹配：

登山
登山,武术

等包含登山选项的记录。

十五、常用问题总结

15.1 为什么执行 INSERT 会出现 Out of range？

比如：

CREATE TABLE tt1 (
    num TINYINT
);

INSERT INTO tt1 VALUES (128);

在严格模式下会出现：

ERROR 1264 (22003)

这是因为有符号 TINYINT 的最大值为 127。

解决方法是根据实际需求选择适合的数据类型，而不是随意关闭数据检查。

15.2 为什么 BIT 查询结果看起来像乱码？

BIT 保存的是位字段。

在这个场景下，某些 MySQL 客户端会按二进制字节显示查询结果，所以可能出现不可打印字符。

能够采用：

SELECT a + 0 FROM tt4;

查看数值形式。

15.3 为什么 FLOAT 与插入的值不完全一致？

FLOAT 属于近似数值类型。

比如：

23.12345612

在浮点格式中可能无法精确表示。

若需准确保存十进制金额等数据，应该优先考虑：

DECIMAL(M,D)

15.4 为什么 CHAR(2) 能保存两个汉字？

因为 CHAR 中的长度通常按照字符数量计算，而不是直接按字节数计算。

比如：

CHAR(2)

能够保存：

中国

前提是当前字符集能够表示这些字符。

15.5 为什么 ALTER TABLE 后字段注释消失了？

比如：

ALTER TABLE users
MODIFY name VARCHAR(60);

该语句重新定义了字段类型，但是没有写原来的 COMMENT。

修改列定义时，如果需保留字段的其他属性，应在新的定义中明确指定。

15.6 为什么新增字段之后，原有记录没有丢失？

比如：

ALTER TABLE users
ADD assets VARCHAR(100);

这条语句主要修改表结构。

原来的记录仍然存在。

在这个场景下，新增字段允许 NULL 且没有其他默认值时，原有记录通常会在新列显示 NULL。

15.7 为什么 TIMESTAMP 不一定自动更新？

实际处理时，因为 TIMESTAMP 的默认值和自动更新行为受到字段定义和 MySQL 设置影响。

若想要明确实现自动更新，能够采用：

TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP

15.8 为什么 SET 采用等号不能查出所有符合条件的数据？

比如：

WHERE hobby = '登山'

匹配的是整个集合值。

如果字段内容包含多个成员，比如：

登山,武术

就不能轻松地把它当成只包含 '登山' 的集合。

能够采用：

WHERE FIND_IN_SET('登山', hobby) > 0

完成成员查询。

十六、常用 SQL 命令速查

16.1 表操作

16.2 常用数据类型

十七、综合实战：设计一张学生信息表

学习完表操作和数据类型之后，我们尝试综合运用前面的知识。

需求如下所示：

设计一张学生表，保存：

• 学号；
• 姓名；
• 年龄；
• 成绩；
• 出生日期；
• 兴趣爱好；
• 记录新建时间。

新建数据库：

CREATE DATABASE IF NOT EXISTS school_demo
CHARACTER SET utf8
COLLATE utf8_general_ci;

选择数据库：

USE school_demo;

新建学生表：

CREATE TABLE students (
    id INT COMMENT '学号',
    name VARCHAR(30) COMMENT '姓名',
    age TINYINT UNSIGNED COMMENT '年龄',
    score DECIMAL(5,2) COMMENT '成绩',
    birthday DATE COMMENT '出生日期',
    hobby SET('篮球','足球','游泳','编程')
        COMMENT '兴趣爱好',
    created_at TIMESTAMP
        DEFAULT CURRENT_TIMESTAMP
        COMMENT '创建时间'
)
ENGINE=InnoDB
CHARACTER SET=utf8;

这里综合采用了：

• INT：保存编号；
• VARCHAR：保存姓名；
• TINYINT UNSIGNED：保存非负年龄；
• DECIMAL：保存精确成绩；
• DATE：保存日期；
• SET：保存多个兴趣爱好；
• TIMESTAMP：保存新建时间；
• COMMENT：字段说明；
• ENGINE：设置 InnoDB 存储引擎。

结合项目来看，需留意，这里尚未添加主键、非空约束和唯一约束等设计。这些内容将在下一章《MySQL表的约束》中学习。

插入数据：

INSERT INTO students
(id, name, age, score, birthday, hobby)
VALUES
(1001, '张三', 18, 95.50, '2008-03-15', '篮球,编程'),
(1002, '李四', 19, 89.00, '2007-06-20', '足球'),
(1003, '王五', 20, 92.50, '2006-01-10', '游泳,编程');

查询：

SELECT * FROM students;

然后查看表结构：

DESC students;

查看建表 SQL：

SHOW CREATE TABLE students;

如果想查询所有喜欢编程的学生：

SELECT id, name, hobby
FROM students
WHERE FIND_IN_SET('编程', hobby) > 0;

理解这一步时，以上内容完成后，我们就已经能够独立设计和采用一张包含多种数据类型的 MySQL 表。

十八、总结

主要掌握了以下知识。

第一，MySQL 表操作。

学习了如何采用 CREATE TABLE 新建表、借助 DESC 查看表结构，以及采用 ALTER TABLE 完成字段添加、修改、删除和表重命名。

其中需重点区分：

• 修改表结构：ALTER TABLE
• 修改表数据：UPDATE
• 删除表：DROP TABLE

第二，MySQL 数据类型。

了解了不同类型的作用及适用范围。

其中重点是：

• INT 与 TINYINT 的范围区别；
• SIGNED 与 UNSIGNED 的区别；
• FLOAT 与 DECIMAL 的精度区别；
• CHAR 与 VARCHAR 的区别；
• DATE、DATETIME、TIMESTAMP 的区别；
• ENUM 和 SET 的单选、多选特性。

第三，数据库表设计的基本思想。

在这个场景下，设计表时，不应该随意为所有字段采用同一种类型，而应根据真实业务需求选择合适的数据类型。

比如：

id INT
name VARCHAR(30)
price DECIMAL(10,2)
created_at TIMESTAMP

在这个场景下，合理的数据类型设计，有助于保证数据正确性，也会影响存储空间、查询性能和后续维护。

在这个场景下，到这里，我们已经完成了 MySQL 数据库基础、库操作、表操作及数据类型的学习。

理解这一步时，总的来说，mysql数据库表操作适合结合实际项目边做边理解。先抓住核心思路，再逐步补上细节和边界处理，最后效果会更稳定，也更容易复用。