拓展閱讀

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: 0mysql>
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: 0mysql> 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: 0mysql> 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 的表，你想執行以下修改：

1. 添加一個名為 email 的字段，類型為 VARCHAR(255)。
2. 修改 name 字段的長度為 VARCHAR(256)。
3. 添加一個名為 birthdate 的字段，類型為 DATE。
4. 刪除一個名為 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;

注意事項

1. 順序問題：在某些情況下，操作的順序可能很重要。例如，如果你要修改一個字段的名稱然后更改其類型，確保先重命名再修改類型。
2. 兼容性問題：確保所有操作在一個 ALTER TABLE 語句中執行時不會互相沖突。例如，刪除一個字段然后再添加同名字段在同一個語句中可能會出問題。
3. 備份數據：在對表結構進行重大更改之前，最好備份數據，以防出現意外情況。

合并 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 語句有助于提高性能，減少鎖定時間，節省磁盤空間，并降低數據不一致的風險。

這在處理大表或高并發環境下尤其重要，有助于保持數據庫的高效和穩定運行。