MySQL 中真正修改表，要靠这几个 DDL 命令

MySQL 中真正修改表，要靠这几个 DDL 命令

很多刚学数据库的人，最容易把两件事混在一起：

看起来都叫“修改”，但本质完全不是一回事。

比如：

如果你连这两类操作都没分清，后面写 SQL 很容易把自己绕进去。

今天这篇文章，我们就从实战角度，把 MySQL 中修改表结构、删除列、修改列定义、删除整张表这一套 DDL 操作讲清楚。

不空讲概念，直接配代码、运行结果和典型坑点。

一、先搞清楚：你改的是“数据”，还是“表”

数据库里常见的 SQL 操作，可以粗分为两类：

1）DML：操作数据

这类语句主要针对表里的记录，比如：

例子：

UPDATE t_student SET s_age = 20 WHERE s_no = 1;

这叫改数据，不叫改表。

2）DDL：操作数据库对象

这类语句针对的是数据库对象本身，比如：

常见命令有：

例子：

ALTER TABLE t_student ADD score DOUBLE(5,2);

这叫修改表结构，属于 DDL。

二、修改表结构sql删除表中数据，核心就是 ALTER TABLE

在 MySQL 里，改表结构最常用的命令就是：

ALTER TABLE 表名 ...

后面根据你的需求，可以继续接：

所以你可以把 ALTER TABLE 理解成一句总开关：

我要开始动这张表本身了。

三、给表新增一列：ADD 才是正确打开方式

假设现在有一张学生表 ，结构如下：

CREATE TABLE t_student (
    s_no INT(6),
    s_name VARCHAR(20),
    s_sex CHAR(1),
    s_age INT,
    enter_date DATE,
    class_name VARCHAR(30),
    email VARCHAR(50)
);

先查一下表中的数据：

SELECT * FROM t_student;

运行结果示例：

+------+--------+-------+-------+------------+------------+------------------+
| s_no | s_name | s_sex | s_age | enter_date | class_name | email            |
+------+--------+-------+-------+------------+------------+------------------+
|    1 | 张三   | 男    |    18 | 2022-05-08 | 软件一班   | 123@126.com      |
|    2 | 李四   | 女    |    19 | 2022-05-08 | 软件二班   | li4@126.com      |
+------+--------+-------+-------+------------+------------+------------------+

现在我们想增加一列“成绩”。

1）新增一列的基本写法

ALTER TABLE t_student
ADD score DOUBLE(5,2);

运行结果：

Query OK, 0 rows affected

再次查询表结构或直接查看数据：

SELECT * FROM t_student;

运行结果示例：

+------+--------+-------+-------+------------+------------+------------------+-------+
| s_no | s_name | s_sex | s_age | enter_date | class_name | email            | score |
+------+--------+-------+-------+------------+------------+------------------+-------+
|    1 | 张三   | 男    |    18 | 2022-05-08 | 软件一班   | 123@126.com      | NULL  |
|    2 | 李四   | 女    |    19 | 2022-05-08 | 软件二班   | li4@126.com      | NULL  |
+------+--------+-------+-------+------------+------------+------------------+-------+

你会发现：

这是正常现象，因为以前压根没有这一列。

2）(5,2) 到底什么意思？

比如：

score DOUBLE(5,2)

含义是：

例如这些值是合法的：

如果你插入更长的小数，MySQL 会按字段定义做处理。

四、加完新列后，数据会怎么存？看一个精度案例就明白了

现在 score 列已经有了，我们给某个学生写入成绩。

UPDATE t_student
SET score = 123.5678
WHERE s_no = 1;

运行结果：

Query OK, 1 row affected

查询：

SELECT s_no, s_name, score FROM t_student WHERE s_no = 1;

运行结果：

+------+--------+--------+
| s_no | s_name | score  |
+------+--------+--------+
|    1 | 张三   | 123.57 |
+------+--------+--------+

为什么是 123.57？

因为前面定义的是：

DOUBLE(5,2)

小数只保留 2 位，所以会发生四舍五入。

这对开发和安全有什么提醒？

如果你在数据库里存的是：

字段精度没设计好，就可能导致数据在落库时被自动截断或四舍五入，后面做统计、告警阈值判断时会出偏差。

五、新增列不一定只能加在最后，位置也能控制

很多人以为：

ADD 新列默认只能加到最后。

其实不是。

MySQL 支持你指定列的位置。

常见两种方式：

1）把新列放到第一列

先删除 score 列，再重新添加到最前面。

删除列：

ALTER TABLE t_student
DROP COLUMN score;

运行结果：

Query OK, 0 rows affected

添加到第一列：

ALTER TABLE t_student
ADD score DOUBLE(5,2) FIRST;

查询：

SELECT * FROM t_student;

运行结果示例：

+-------+------+--------+-------+-------+------------+------------+------------------+
| score | s_no | s_name | s_sex | s_age | enter_date | class_name | email            |
+-------+------+--------+-------+-------+------------+------------+------------------+
|  NULL |    1 | 张三   | 男    |    18 | 2022-05-08 | 软件一班   | 123@126.com      |
|  NULL |    2 | 李四   | 女    |    19 | 2022-05-08 | 软件二班   | li4@126.com      |
+-------+------+--------+-------+-------+------------+------------+------------------+

可以看到，score 已经到了第一列。

2）把新列放到指定字段后面

假设你想把 score 放到 s_sex 和 s_age 之间，也就是放在 s_sex 后面：

先删掉：

ALTER TABLE t_student
DROP COLUMN score;

再重新加：

ALTER TABLE t_student
ADD score DOUBLE(5,2) AFTER s_sex;

查询结果示例：

+------+--------+-------+-------+-------+------------+------------+------------------+
| s_no | s_name | s_sex | score | s_age | enter_date | class_name | email            |
+------+--------+-------+-------+-------+------------+------------+------------------+
|    1 | 张三   | 男    | NULL  |    18 | 2022-05-08 | 软件一班   | 123@126.com      |
|    2 | 李四   | 女    | NULL  |    19 | 2022-05-08 | 软件二班   | li4@126.com      |
+------+--------+-------+-------+-------+------------+------------+------------------+

六、删除一列：DROP 不是清空，是直接移除

删除列的语法如下：

ALTER TABLE 表名
DROP COLUMN 列名;

例如删除成绩列：

ALTER TABLE t_student
DROP COLUMN score;

运行结果：

Query OK, 0 rows affected

再次查询：

SELECT * FROM t_student;

你会发现 score 列彻底没了。

这里要特别注意

删除列和把列值改成 NULL 是两回事：

这属于结构级删除sql删除表中数据，操作前最好确认：

否则你删完一列，业务代码可能直接报错。

七、修改列定义： 和 ，很多人总分不清

这是 MySQL 改表结构里最容易搞混的一组命令：

两者都能“改列”，但能力不一样。

1）：只改列定义，不改列名

例如，我们现在有：

score DOUBLE(5,2)

想把它改成：

FLOAT(4,1)

可以这么写：

ALTER TABLE t_student
MODIFY score FLOAT(4,1);

运行结果：

Query OK, 2 rows affected

如果原来某条记录中：

123.57

再查时可能会变成：

123.6

因为现在字段定义成了：

FLOAT(4,1)

只保留 1 位小数。

示例：修改类型后的数据变化

先写入一条数据：

UPDATE t_student
SET score = 123.57
WHERE s_no = 1;

然后修改列类型：

ALTER TABLE t_student
MODIFY score FLOAT(4,1);

查询结果：

SELECT s_no, score FROM t_student WHERE s_no = 1;

运行结果：

+------+-------+
| s_no | score |
+------+-------+
|    1 | 123.6 |
+------+-------+

说明 MySQL 会按新类型尝试转换已有数据。

2）：既能改列名，也能改列定义

如果你不仅想改类型，还想顺手把列名改掉，比如把 score 改成 ，可以用：

ALTER TABLE t_student
CHANGE score score1 DOUBLE(5,1);

这个语句的结构要注意：

CHANGE 旧列名 新列名 新数据类型

也就是说， 必须把老名字和新名字都写出来。

八、为什么 会报 out of range？因为你改小了，旧数据装不下

看一个典型报错。

假设当前 score 的值是：

123.6

你现在执行：

ALTER TABLE t_student
CHANGE score score1 DOUBLE(3,1);

很可能直接报错：

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

原因是什么？

因为：

DOUBLE(3,1)

表示总位数只有 3 位MySQL 中真正修改表，要靠这几个 DDL 命令，其中小数占 1 位。

那你真正能表示的数很有限，比如：

但你当前表里已经有：

123.6

它根本放不进去sql删除表中数据，所以报：

Out of range

正确做法

改成能容纳当前数据的类型，例如：

ALTER TABLE t_student
CHANGE score score1 DOUBLE(5,1);

运行结果：

Query OK, 2 rows affected

查询：

SELECT s_no, score1 FROM t_student;

运行结果：

+------+--------+
| s_no | score1 |
+------+--------+
|    1 |  123.6 |
|    2 |   NULL |
+------+--------+

这样就成功了。

九、删表和删数据，不是一个量级的操作

这是很多初学者最容易搞错的地方之一。

1）删除数据

比如：

DELETE FROM t_student;

这会删除表中的所有记录，

但表结构还在。

2）删除表

如果你执行：

DROP TABLE t_student;

那就不是删数据了，而是把整张表对象直接删除：

示例：删除整张表

DROP TABLE t_student;

运行结果：

Query OK, 0 rows affected

然后你再查：

SELECT * FROM t_student;

运行结果：

ERROR 1146 (42S02): Table 'mytestdb.t_student' doesn't exist

这个错误码 1146 的核心意思就是：

表不存在了。

十、MySQL 语法记不住怎么办？别死背，学会查文档才是正路

很多人一看到：

就开始慌。

其实数据库学习到后面，你会发现一个真相：

没人靠死记硬背吃饭，真正重要的是知道“去哪里查”和“怎么判断该用哪个命令”。

尤其 ALTER TABLE 的语法扩展很多MySQL 中真正修改表，要靠这几个 DDL 命令，除了本文讲的这些，还包括：

这些内容不可能全靠脑子硬背。

正确学习方式

记住这几点就够了：

1）先掌握核心骨架

比如你得知道：

2）遇到细节再查文档

比如：

这些都可以查官方文档或可靠资料。

3）先理解，不要硬背

理解“为什么要这样改”，比背 10 条语法更重要。

十一、从网络安全角度看，为什么表结构修改也很重要？

很多人觉得改表结构只是开发人员的事。

其实在安全工作里，这类能力也很常用。

1）审计日志表扩展字段

比如你原来只有：

后面为了安全分析，想加上：

这时候就需要 ALTER TABLE ADD。

2）调整字段精度，避免误判

例如安全评分字段如果精度太低，可能导致：

这时候就要改字段类型。

3）清理历史无用字段

随着业务变化，一些旧字段不再使用，留着反而造成：

这时候要用 DROP 。

4）误删表比误删数据更严重

很多测试环境混着正式数据，

一条 DROP TABLE 下去，损失可比 大得多。

十二、完整实战案例：从加列到改列再到删表

下面给你一组完整 SQL，适合直接练习。

-- 1. 创建测试表
CREATE TABLE t_student (
    s_no INT(6),
    s_name VARCHAR(20),
    s_sex CHAR(1),
    s_age INT,
    enter_date DATE,
    class_name VARCHAR(30),
    email VARCHAR(50)
);
-- 2. 插入测试数据
INSERT INTO t_student VALUES
(1, '张三', '男', 18, '2022-05-08', '软件一班', '123@126.com'),
(2, '李四', '女', 19, '2022-05-09', '软件二班', 'li4@126.com');
-- 3. 增加成绩列
ALTER TABLE t_student
ADD score DOUBLE(5,2) AFTER s_sex;
-- 4. 更新成绩
UPDATE t_student
SET score = 123.5678
WHERE s_no = 1;
-- 5. 查看结果
SELECT * FROM t_student;
-- 6. 修改列类型
ALTER TABLE t_student
MODIFY score FLOAT(4,1);
-- 7. 查看结果
SELECT s_no, score FROM t_student;
-- 8. 修改列名和类型
ALTER TABLE t_student
CHANGE score score1 DOUBLE(5,1);
-- 9. 查看结果
SELECT s_no, score1 FROM t_student;
-- 10. 删除列
ALTER TABLE t_student
DROP COLUMN score1;
-- 11. 删除整张表
DROP TABLE t_student;

部分运行结果示例更新成绩后：

+------+--------+-------+--------+-------+------------+------------+------------------+
| s_no | s_name | s_sex | score  | s_age | enter_date | class_name | email            |
+------+--------+-------+--------+-------+------------+------------+------------------+
|    1 | 张三   | 男    | 123.57 |    18 | 2022-05-08 | 软件一班   | 123@126.com      |
|    2 | 李四   | 女    | NULL   |    19 | 2022-05-09 | 软件二班   | li4@126.com      |
+------+--------+-------+--------+-------+------------+------------+------------------+

修改为 FLOAT(4,1) 后：

+------+-------+
| s_no | score |
+------+-------+
|    1 | 123.6 |
|    2 | NULL  |
+------+-------+

删除表后再查询：

ERROR 1146 (42S02): Table 'mytestdb.t_student' doesn't exist

十三、总结：搞懂这 6 个命令，表结构操作就算入门了

如果你只想先抓重点，那记住下面这 6 条就够了：

改表结构，核心命令是 ALTER TABLE。新增列用 ADD。删除列用 DROP 。只改列定义，用 。改列名同时改定义，用 。删除整张表，用 DROP TABLE，这一步最危险。