MySQL-17-mysql alter 语句如何实现？如何合并为一个

2024-07-07 17:19 由老马啸西风发表于 #后端开发

拓展阅读

MySQL 00 View

MySQL 01 Ruler mysql 日常开发规范

MySQL 02 truncate table 与 delete 清空表的区别和坑

MySQL 03 Expression 1 of ORDER BY clause is not in SELECT list,references column

MySQL 04 EMOJI 表情与 UTF8MB4 的故事

MySQL 05 MySQL入门教程（MySQL tutorial book）

MySQL 06 mysql 如何实现类似 oracle 的 merge into

MySQL 07 timeout 超时异常

MySQL 08 datetime timestamp 以及如何自动更新，如何实现范围查询

MySQL 09 MySQL-09-SP mysql 存储过程

MySQL 09 MySQL-group by 分组

需求

表在上线以后，我们需要对表进行 alter 字段处理

实现方式

mysql 如何通过 alter 添加一个字段？如何修改一个字段？

实际测试

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.31-log |
+------------+

创建一张测试表

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

添加字段

alter table students add column create_time datetime(6) comment '创建时间';

测试效果

mysql> alter table students add column create_time datetime(6) comment '创建时间';
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> desc students;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| name        | varchar(100) | YES  |     | NULL    |                |
| age         | int(11)      | YES  |     | NULL    |                |
| create_time | datetime(6)  | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

修改字段

ALTER TABLE students MODIFY COLUMN name VARCHAR(256);

实际测试效果：

mysql> ALTER TABLE students MODIFY COLUMN name VARCHAR(256);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc students;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| name        | varchar(256) | YES  |     | NULL    |                |
| age         | int(11)      | YES  |     | NULL    |                |
| create_time | datetime(6)  | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

修改字段名称+属性

如果我们希望修改字段名称，需要使用 CHANGE COLUMN

ALTER TABLE students CHANGE COLUMN create_time created_time datetime(3) COMMENT '创建时间';

实际测试效果如下：

mysql> ALTER TABLE students CHANGE COLUMN create_time created_time datetime(3) COMMENT '创建时间';
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc students;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| name         | varchar(256) | YES  |     | NULL    |                |
| age          | int(11)      | YES  |     | NULL    |                |
| created_time | datetime(3)  | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

场景2：如何把同一个表的多个 alter 语句合并为 1 个？

在 MySQL 中，你可以将多个 ALTER TABLE 语句合并为一个，以提高效率和减少表锁定时间。这是通过在一个 ALTER TABLE 语句中指定多个修改来实现的。每个修改用逗号分隔。

假设你有以下多个 ALTER TABLE 语句：

ALTER TABLE my_table ADD COLUMN new_column1 VARCHAR(100);
ALTER TABLE my_table MODIFY COLUMN existing_column VARCHAR(256);
ALTER TABLE my_table ADD COLUMN new_column2 INT;
ALTER TABLE my_table DROP COLUMN old_column;

你可以将它们合并为一个 ALTER TABLE 语句，如下所示：

ALTER TABLE my_table
ADD COLUMN new_column1 VARCHAR(100),
MODIFY COLUMN existing_column VARCHAR(256),
ADD COLUMN new_column2 INT,
DROP COLUMN old_column;

示例

假设你有一个名为 students 的表，你想执行以下修改：

添加一个名为 email 的字段，类型为 VARCHAR(255)。
修改 name 字段的长度为 VARCHAR(256)。
添加一个名为 birthdate 的字段，类型为 DATE。
删除一个名为 old_field 的字段。

将这些操作合并为一个 ALTER TABLE 语句：

ALTER TABLE students
ADD COLUMN email VARCHAR(255),
MODIFY COLUMN name VARCHAR(256),
ADD COLUMN birthdate DATE,
DROP COLUMN old_field;

注意事项

顺序问题：在某些情况下，操作的顺序可能很重要。例如，如果你要修改一个字段的名称然后更改其类型，确保先重命名再修改类型。
兼容性问题：确保所有操作在一个 ALTER TABLE 语句中执行时不会互相冲突。例如，删除一个字段然后再添加同名字段在同一个语句中可能会出问题。
备份数据：在对表结构进行重大更改之前，最好备份数据，以防出现意外情况。

合并 ALTER TABLE 语句不仅可以提高执行效率，还可以减少数据库表的锁定时间，尤其是在处理大表时，这一点尤为重要。

为什么需要合并为一个?

将多个 ALTER TABLE 语句合并为一个有以下几个主要原因和好处：

1. 减少表锁定时间

每个 ALTER TABLE 操作都会对表进行锁定，阻止其他操作在同一时间修改表结构。多个 ALTER TABLE 语句将导致多次锁定表，增加锁定时间。而将多个修改合并为一个 ALTER TABLE 语句只会锁定表一次，从而减少表的锁定时间，提高并发操作的性能。

2. 提高执行效率

每个 ALTER TABLE 操作都需要对表进行扫描和重新构建索引，这会增加执行时间。将多个操作合并为一个语句可以减少表扫描和索引重建的次数，从而提高执行效率。

3. 减少日志和备份空间

每个 ALTER TABLE 操作都会生成一条日志记录。如果有大量的 ALTER TABLE 操作，这些日志记录会占用大量的磁盘空间。合并操作可以减少日志记录的数量，节省磁盘空间。

4. 降低发生错误的概率

当你分多次执行 ALTER TABLE 语句时，任何一次操作的失败都可能导致数据不一致。将所有操作合并在一起，可以保证所有修改要么全部成功，要么全部失败，减少了数据不一致的风险。

示例对比

多次 `ALTER TABLE` 语句：

ALTER TABLE students ADD COLUMN email VARCHAR(255);
ALTER TABLE students MODIFY COLUMN name VARCHAR(256);
ALTER TABLE students ADD COLUMN birthdate DATE;
ALTER TABLE students DROP COLUMN old_field;

这些语句会导致表被锁定四次，每次操作都会产生相应的日志记录，并且每次操作都会重新扫描表和重建索引。

合并后的 `ALTER TABLE` 语句：

ALTER TABLE students
ADD COLUMN email VARCHAR(255),
MODIFY COLUMN name VARCHAR(256),
ADD COLUMN birthdate DATE,
DROP COLUMN old_field;

这个语句只会锁定表一次，减少表扫描和索引重建的次数，以及相应的日志记录。

结论

合并 ALTER TABLE 语句有助于提高性能，减少锁定时间，节省磁盘空间，并降低数据不一致的风险。

这在处理大表或高并发环境下尤其重要，有助于保持数据库的高效和稳定运行。