MySQL-17-mysql alter 語句如何實現？如何合併為一個

老马啸西风發表於2024-07-07
MySql

擴充閱讀

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

  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 語句有助於提高效能，減少鎖定時間，節省磁碟空間，並降低資料不一致的風險。

這在處理大表或高併發環境下尤其重要，有助於保持資料庫的高效和穩定執行。

相關文章