拓展阅读
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 08 datetime timestamp 以及如何自动更新,如何实现范围查询
MySQL 09 MySQL-09-SP mysql 存储过程
需求
表在上线以后,我们需要对表进行 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
语句有助于提高性能,减少锁定时间,节省磁盘空间,并降低数据不一致的风险。
这在处理大表或高并发环境下尤其重要,有助于保持数据库的高效和稳定运行。
chat
为什么 mysql 需要有 CHANGE COLUMN,而不是统一的 MODIFY COLUMN?
MySQL 提供了 MODIFY COLUMN
和 CHANGE COLUMN
两个不同的语法,以便为用户提供灵活性来处理不同的修改需求。虽然这两者在某些情况下可以互换使用,但它们之间有一些关键区别,这使得保留两者有其实际意义。
主要区别
- 修改字段名称:
CHANGE COLUMN
可以同时修改字段名称和字段类型。MODIFY COLUMN
只能修改字段类型,不能修改字段名称。
- 语法要求:
CHANGE COLUMN
语法:ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name column_definition;
CHANGE COLUMN
需要指定旧的字段名称和新的字段名称,即使字段名称没有变化,新的字段名称也必须写出来。MODIFY COLUMN
语法:ALTER TABLE table_name MODIFY COLUMN column_name column_definition;
MODIFY COLUMN
只需要指定字段名称和新的字段定义,不需要旧的字段名称。
使用场景
- 修改字段类型而不修改名称:
- 使用
MODIFY COLUMN
更简洁,因为只需指定字段名称和新类型。
ALTER TABLE my_table MODIFY COLUMN name VARCHAR(256);
- 使用
- 修改字段名称和类型:
- 使用
CHANGE COLUMN
,因为它允许同时修改字段名称和类型。
ALTER TABLE my_table CHANGE COLUMN old_name new_name VARCHAR(256);
- 使用
示例对比
假设有一个表 employees
,包含字段 employee_name
,我们需要将其长度改为 256 个字符。
-
使用
MODIFY COLUMN
仅修改字段类型:ALTER TABLE employees MODIFY COLUMN employee_name VARCHAR(256);
-
使用
CHANGE COLUMN
修改字段名称和类型(即使名称不变,也必须指定):ALTER TABLE employees CHANGE COLUMN employee_name employee_name VARCHAR(256);
结论
MySQL 保留 MODIFY COLUMN
和 CHANGE COLUMN
两个语法,是为了提供更大的灵活性,满足不同的修改需求。
这样,用户在只需要修改字段类型时可以使用更简洁的 MODIFY COLUMN
,而在需要修改字段名称时可以使用 CHANGE COLUMN
。
这种设计提高了 SQL 语句的可读性和易用性。
参考资料
https://www.jb51.net/database/3110399bu.htm
https://blog.csdn.net/weixin_44147535/article/details/135438169
更多学习
更多实时资讯,前沿技术,生活趣事。尽在【老马啸西风】
交流社群:[交流群信息](https://mp.weixin.qq.com/s/rkSvXxiiLGjl3S-ZOZCr0Q)