拓展閱讀
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
的表,你想執行以下修改:
- 添加一個名為
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
語句有助于提高性能,減少鎖定時間,節省磁盤空間,并降低數據不一致的風險。
這在處理大表或高并發環境下尤其重要,有助于保持數據庫的高效和穩定運行。