MySQL 数据库表操作：数据类型与约束详解

在深入 MySQL 表操作之前，我们需要先选定具体的数据库。如果不确定当前选中的是哪个库，可以直接查询。

mysql> use learn1;
Database changed 

mysql> select database();

一、表的操作

1. 创建表

创建表是定义数据结构的基础。语法结构如下：

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

其中 field 表示列名，datatype 表示列类型。若未指定字符集或校验规则，则默认继承所在数据库的设置。

2. 查看表

查看表结构通常使用 DESC 命令，它能快速展示字段信息。

desc 表名;

3. 修改表

实际开发中，经常需要调整表结构，比如修改字段名、类型、大小，或者增减字段。这里不存在'查找'操作，只有增删改。

ALTER TABLE tablename ADD (column datatype [DEFAULT expr][, column datatype]...);
ALTER TABLE tablename MODIFY (column datatype [DEFAULT expr][, column datatype]...);
ALTER TABLE tablename DROP (column);

4. 删除表

DROP TABLE t1;

说明： 像 CREATE, DROP, SHOW TABLES 这类操作属于 DDL（数据定义语言），它们操作的是表的结构。而 SELECT 操作的是表的内容，属于 DML。目前我们学到的库和表的基本操作主要归类为 DDL。

5. 案例实战

① 创建表

CREATE TABLE IF NOT EXISTS user1 (
    id INT,
    name VARCHAR(20) COMMENT '用户名',
    password VARCHAR(20) COMMENT '用户密码',
    birthday DATE COMMENT '用户生日'
) CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE MyISAM;

CREATE TABLE IF NOT EXISTS user2 (
    id INT,
    name VARCHAR(20) COMMENT '用户名',
    password VARCHAR(20) COMMENT '用户密码',
    birthday DATE COMMENT '用户生日'
) CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE=InnoDB;

注意 IF NOT EXISTS 表示表不存在时才创建。两种存储引擎的文件结构不同：

• MyISAM：生成三个文件，users.frm（表结构）、users.MYD（表数据）、users.MYI（表索引）。
• InnoDB：生成两个文件，Users.frm（表结构）、Users.ibd（表数据 & 表索引）。

② 插入、删除、查看表数据

执行相应的 INSERT, DELETE, SELECT 语句即可管理数据。

③ 查看表信息

MySQL 会记录用户的操作痕迹，包括建表、删表等行为。查看创建表语句时，有时会发现设置编码的字段不见了，这是因为服务器内部进行了语法优化。

④ 修改表信息

Ⅰ、修改表名

mysql> SHOW TABLES;
+------------------+
| Tables_in_learn1 |
+------------------+
| u1               |
| user1            |
+------------------+

mysql> ALTER TABLE user1 RENAME TO user;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
+------------------+
| Tables_in_learn1 |
+------------------+
| u1               |
| user             |
+------------------+

注意 TO 关键字可以省略。

Ⅱ、新增一列

ALTER TABLE tablename ADD column_name datatype AFTER existing_column;

AFTER 表示你想指定新增到那一列的后面。插入新字段后，对原来表中的数据没有影响，之前的数据仍然保留。

Ⅲ、修改列属性

ALTER TABLE tablename MODIFY column_name new_datatype;

我们发现 name 字段大小确实已经改变，且不影响原有数据。但需要注意的是，如果使用 MODIFY 修改字段属性，它会覆盖该字段原有的所有属性（如 COMMENT）。如果未来想改某一列，建议把这一列曾经的所有属性全部复制下来，再重新提交。

Ⅳ、删除列名

ALTER TABLE user DROP password;

注意：删除之后，删除字段及其对应的列数据都没了。

Ⅴ、修改列名

将 name 列修改为 xingming：

ALTER TABLE user CHANGE name xingming VARCHAR(60) DEFAULT NULL;

CHANGE 后面跟着旧列名，再跟新列名和属性。列名称要改不仅仅需要提供新列名称，这个列的相关属性也需要完整定义，因为修改是覆盖实现的。

二、数据类型

1. 数据类型分类

MySQL 中每种数据类型都有其特定用途，类似于 C/C++ 等语言中的类型系统。常见的类型包括数值、字符串、日期时间等。

2. 数值类型

数值类型分为位类型、布尔类型、整数类型、浮点数类型等。以下主要以整型为例。

整数类型包括 TINYINT, SMALLINT 等，它们根据名称不同，所占字节数也不同。默认情况下是有符号类型的，取值范围与 C/C++ 对应整数范围相同。如果加上 UNSIGNED，则表示无符号类型。

2.1 TINYINT 类型

以 TINYINT 为例，其他整数类型使用方法类似。

CREATE TABLE IF NOT EXISTS t1(num TINYINT);

查看表结构时，TINYINT 后面有一个数字 4，这代表显示宽度。在 MySQL Server 8.0.17 之后，在不使用 ZEROFILL 修饰符时已弃用显示宽度，所以 TINYINT 后没有 4 也是正常的。

• 插入数据时，TINYINT 默认有符号，取值范围 -128~127。超出范围的数据无法插入。
• 可以通过 UNSIGNED 指定为无符号类型，范围变为 0~255。

数据越界测试：

mysql> INSERT INTO t1 VALUES(-129);
ERROR 1264(22003): Out of range value for column 'num' at row 1

mysql> INSERT INTO t1 VALUES(-128);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t2 VALUES(128); -- t2 is unsigned tinyint
Query OK, 1 row affected (0.00 sec)

虽然无符号类型范围扩大，但建议在设计时将 INT 提升为 BIGINT 而不是依赖 UNSIGNED，以防数据增长导致溢出。

约束机制： 如果我们向 MySQL 特定类型中插入不合法数据，MySQL 通常会直接拦截，保证数据的完整性。这意味着一旦成功插入，数据一定是合法的，不会发生截断或隐式类型转化。

2.2 BIT 类型

语法： BIT [(M)]，M 表示比特位数，范围 1 到 64。默认为 1。

CREATE TABLE t3(id INT, online BIT(1));

使用一个比特位表示用户是否在线，只能插入 0 或 1。查询时通常按 ASCII 码值显示，不可见内容可用 HEX 函数以十六进制形式查看。

2.3 浮点数类型

2.3.1 FLOAT

语法： FLOAT [(M, D)] [UNSIGNED]，M 指定显示长度，D 指定小数位数，占用 4 个字节。

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

FLOAT(4,2) 范围约为 -99.99 ~ 99.99。插入数据时精度不够补 0，超过则四舍五入。浮点数存储可能存在精度损失。

2.3.2 DECIMAL

语法： DECIMAL(M, D) [UNSIGNED]，定点数，M 指定总长度，D 指定小数位数。

DECIMAL 能有效避免精度损失。FLOAT 默认精度约 7 位，而 DECIMAL 可精确到 65 位整数和 30 位小数。对于高精度场景（如金额），应优先使用 DECIMAL。

【案例】对比精度：

CREATE TABLE t6 (id INT, salary FLOAT(10,8), salary2 DECIMAL(10,8));
INSERT INTO t6 VALUES(100, 23.12345612, 23.12345612);
SELECT * FROM t6;

可以看到 FLOAT 存储的值发生了微小变化，而 DECIMAL 保持了原样。DECIMAL 能完全按照怎么存就怎么取的方式工作。

3. 字符串类型

3.1 CHAR

语法： CHAR(L)，固定长度字符串，L 最大 255。

CREATE TABLE IF NOT EXISTS t7(id INT, name CHAR(2));

插入数据时，如果超过定义长度不允许插入。MySQL 中的字符概念不同于 C/C++，一个汉字就是一个字符。CHAR(2) 表示存放两个字符，可以是字母或汉字。

总结： CHAR 括号内填的是固定长度上限，一旦定义好空间就申请好了。如果用多少是你的事，给多少由 L 决定。如果插入字符超过 L 就不让你插。

3.2 VARCHAR

语法： VARCHAR(L)，可变长度字符串，L 最大 65535 字节。

与 CHAR 的区别在于：VARCHAR 是变长的，实际使用空间根据字符串长度动态分配，而 CHAR 是固定长度。关于 VARCHAR(len)，len 的值与表的编码密切相关。

UTF8 VS UTF-8【细节】

MySQL 的 utf8 不是真正的 UTF-8 编码。在 utf8 编码中，单个字符占 3 个字节，因此 varchar(n) 参数 n 最大值为 21844（65535 / 3 ≈ 21845，减去长度记录字节）。

真正的 UTF-8 支持 4 个字节（如 emoji、繁体字），MySQL 称之为 utf8mb4。MySQL 官网在 2010 年发布了 utf8mb4 来支持真正的 UTF-8。现在 MySQL 8.0 之后，默认字符集已变为 utf8mb4。

建议： 所有使用 utf8 的用户都应该改用 utf8mb4，永远不要再使用旧的 utf8。

总结： VARCHAR 有自己长度上限，范围内用多少给多少。最大字节数为 65535，需预留 1-4 个字节记录实际字符长度。在 UTF8 下最大字符个数是 16383，如果有其他字段，这个值会变小。

3.3 CHAR VS VARCHAR

• 共同点：都能保存字符串，都有上限。
• 区别：CHAR 定长，一次分配固定空间；VARCHAR 变长，根据实际使用分配。

实际存储	char(4)	varchar(4)	char 占用字节	varchar 占用字节
abcd	abcd	abcd	4*3=12	4*3+1=13
A	A	A	4*3=12	1*3+1=4
Abcde	×	×	数据超过长度	数据超过长度

如何选择：

• 数据确定长度一样（如身份证、手机号、MD5），使用定长 CHAR。
• 数据长度有变化（如名字、地址），使用变长 VARCHAR，但要保证最长能存进去。

定长 VS 变长：

• 定长磁盘空间浪费但效率高。
• 变长磁盘空间节省但效率低。

4. 日期和时间类型

• DATE：日期 'yyyy-mm-dd'，占 3 字节。
• DATETIME：日期时间 'yyyy-mm-dd HH:ii:ss'，范围 1000 到 9999，占 8 字节，手动设置。
• TIMESTAMP：时间戳，从 1970 年开始，占 4 字节，自动更新。

【案例】

CREATE TABLE t10(t1 DATE, t2 DATETIME, t3 TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
INSERT INTO t10(t1, t2) VALUES('1997-7-1', '2008-8-8 12:1:1');
SELECT * FROM t10;

注意： TIMESTAMP 会自动更新，适用于记录最后修改时间。DATETIME 适合存储固定时间，如入职时间。

5. ENUM 和 SET

• ENUM：枚举，'单选'类型。实际存储的是数字编号（1, 2, 3...），最多 65535 个选项。
• SET：集合，'多选'类型。实际存储的是位图（1, 2, 4, 8...），最多 64 个选项。

不建议添加枚举值或集合值时采用数字方式，不利于阅读。

【案例】调查表 votes

CREATE TABLE votes(
    username VARCHAR(30),
    hobby SET('clime','swim','draw'),
    gender ENUM('男','女')
);

ENUM 提供约束，插入时只能插入枚举限定的常量或对应下标（从 1 开始）。SET 插入数字代表位图，例如插入 3 表示前两个爱好都被选中。

集合查询使用 FIND_IN_SET 函数

对于集合类型字段，可以使用 FIND_IN_SET 进行查询。此函数用于确定子串是否存在于逗号分隔的字符串列表中。

SELECT FIND_IN_SET('b', 'a,b,c'); -- 返回 2
SELECT FIND_IN_SET('ab', 'a,b,c'); -- 返回 0

返回值解释：存在返回下标（从 1 开始），不存在返回 0。非 0 即真，0 即假。

应用实例：查找用户爱好包含 'client' 的记录。

SELECT * FROM votes WHERE FIND_IN_SET('clime', hobby);

三、表的约束

真正约束字段的是数据类型，但为了更好保证数据合法性，还需要额外约束。约束的最终目标是保证数据的完整性和可预期性。

3.1 空属性（NULL/NOT NULL）

NULL 表示列可以为空，NOT NULL 表示列不能为空。

通过 NOT NULL，可以设置某列数据在插入时必须填入具体值，否则会报错。例如注册账号时的必填信息。

【案例】

CREATE TABLE myclass(
    class_name VARCHAR(20) NOT NULL,
    other VARCHAR(20)
);

在此示例中，class_name 设置了 NOT NULL，而 other 默认允许为空。如果插入时未指定 class_name 且无默认值，会报错。

3.2 默认值（DEFAULT）

DEFAULT：当插入数据时，如果未指定该列的值，将使用默认值。

【案例】

CREATE TABLE IF NOT EXISTS student(
    name VARCHAR(20) NOT NULL,
    age TINYINT UNSIGNED DEFAULT 18,
    gender VARCHAR(10) DEFAULT '男'
);

DEFAULT 和 NOT NULL 并不冲突，而是互相补充的。当用户指明这一列要插的时候，受 NULL 和 NOT NULL 约束；当用户忽略这一列的时候，如果设置了默认值使用默认值，如果没有则可能报错。

3.3 列描述（COMMENT）

COMMENT：用于给列添加注释说明，便于程序员和数据库管理员理解字段用途。该属性不会对数据插入产生约束效果，相当于代码里的注释。

CREATE TABLE IF NOT EXISTS t5(
    name VARCHAR(20) NOT NULL COMMENT '用户的用户名',
    age TINYINT UNSIGNED DEFAULT 18 COMMENT '用户的年龄'
);

3.4 ZEROFILL

ZEROFILL：在数字前补零，使显示字符长度符合指定的位数。数据库存储的数值不变，仅用于展示效果。

【案例】

CREATE TABLE IF NOT EXISTS t6(a INT UNSIGNED, b INT UNSIGNED ZEROFILL);
INSERT INTO t6 VALUES(111, 222);
SELECT * FROM t6;

我们会发现，ZEROFILL 会让 222 显示为 000000222。验证一下：值不会改变，只是一种格式化输出。

3.5 主键（PRIMARY KEY）

PRIMARY KEY：用于标识表中的唯一记录，不允许重复或为空。表中最多只能有一个主键列，但可以通过复合主键的方式使用多列联合唯一标识。

创建主键有两种方法：

1. 创建表的时候就把主键设置好。
2. 表建好之后但没有主键，可以追加主键。

【案例 1】创建表时设置主键

CREATE TABLE t7(id INT PRIMARY KEY, name VARCHAR(20));
INSERT INTO t7 VALUES(1, 'amy');
INSERT INTO t7 VALUES(1, 'anna'); -- ERROR: Duplicate entry

主键约束倒逼程序员插入时不要出现冲突，同时也保证了根据主键绝对能取出确定的一条记录。

【案例 2】追加主键

ALTER TABLE t7 DROP PRIMARY KEY;
ALTER TABLE t7 ADD PRIMARY KEY(id);

复合主键

一张表只能有一个主键，但这个主键可以由多列组成。例如让两列合起来充当一个主键：

CREATE TABLE t8(id INT, name VARCHAR(20), PRIMARY KEY(id, name));

这里 id 和 name 都是主键，但它们合起来才是一个主键。只有多个同时和历史数据一样才会出现主键冲突。

3.6 自增长（AUTO_INCREMENT）

AUTO_INCREMENT：字段自动增长，从当前最大值加 1，通常配合主键使用，确保值唯一。

特点：

• 任何字段要做自增长，前提本身是一个索引。
• 自增长字段必须是整数。
• 一张表最多只能有一个自增长。

【案例】

CREATE TABLE t10(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20));
INSERT INTO t10(name) VALUES('a');
INSERT INTO t10(name) VALUES('b');
SELECT * FROM t10;

插入时可以不指定 id，系统自动赋值并增长。如果指定 id 插入，也能进行，但再插入相同值时会触发主键冲突。

自增主键的插入机制：

• 默认行为：若未设置起始值，默认从 1 开始。
• 手动设置起始值：如果手动插入一个新的起始值且大于历史值，自增主键将从新的起始值开始。

设置 AUTO_INCREMENT 的原理： 除了表内设置，还可以在表外设置 AUTO_INCREMENT 的值，代表下一次插入的起始值。

CREATE TABLE t11(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20)) AUTO_INCREMENT=100;

获取上次插入的 AUTO_INCREMENT 值可使用 LAST_INSERT_ID() 函数。单条插入返回最后一次插入的值，批量插入返回第一个值。

3.7 唯一键（UNIQUE KEY）

定义： 一张表中往往有很多字段需要唯一性，数据不能重复，但一张表中只能有一个主键，此时唯一键就可以解决表中有多个字段需要唯一性约束的问题。

区别：

• 主键：标识唯一性，主要用于唯一标识记录，不允许为空。
• 唯一键：更多用于业务逻辑上的唯一性约束，允许字段为空，且多个空值不会影响唯一性。

示例场景： 员工管理系统中，身份证号作为主键，工号设置为唯一键。

CREATE TABLE student(
    id CHAR(10) UNIQUE COMMENT '学号，不能重复，但可以为空',
    name VARCHAR(10)
);
INSERT INTO student(id, name) VALUES('01', 'aaa');
INSERT INTO student(id, name) VALUES('01', 'bbb'); -- ERROR: Duplicate entry
INSERT INTO student(id, name) VALUES(NULL, 'bbb'); -- 允许为空

唯一键和主键不冲突，可以理解为对主键的补充设置。一般而言，建议将主键设计成和当前业务无关的字段，这样业务调整时不会做过大的改动。

3.8 外键（FOREIGN KEY）

外键： 从表和主表的关联关系，产生外键约束。为什么需要外键约束？为了确保表间数据的一致性，例如防止插入一个不存在班级的学生或删除一个还有学生的班级。

定义： 外键约束用于建立主表和从表之间的关联关系，主要定义在从表上，主表必须包含主键或唯一键。当定义外键后，要求外键列数据必须在主表的主键列存在或为 NULL。

在从表中，设置外键约束：

FOREIGN KEY (字段名) REFERENCES 主表 (列)

【案例】

CREATE TABLE stu(
    id INT PRIMARY KEY,
    name VARCHAR(30) NOT NULL COMMENT '学生名',
    class_id INT,
    FOREIGN KEY(class_id) REFERENCES class(id)
);

INSERT INTO class VALUES(10, '1班'), (20, '2班');
INSERT INTO stu VALUES(1, '1 班', 10), (2, '2 班', 20);
INSERT INTO stu VALUES(30, '1 班', 3); -- ERROR: Foreign key constraint fails

如果两张表在业务上有相关性但没有建立约束关系，就可能出现问题。建立外键的本质就是把相关性交给 MySQL 去审核，提前告诉 MySQL 表之间的约束关系。当用户插入不符合业务逻辑的数据时，MySQL 不允许你插入。