mysql alter新增列的實現方式

2023-01-20 14:02:15

mysql alter新增列

當表中的資料量不是很大的時候可以直接使用alter命令新增新列，5.6及其之前的版本會將表鎖住。

alter的執行過程如下

1、將原表鎖住，不能進行更新、插入、刪除等修改操作

2、建立了一個臨時表，新增想要的欄位

3、將原表中的資料複製到臨時表中

4、將臨時表改名，並刪除原表

在5.6之後的版本mysql資料庫做了很多優化，像onlineDDL在執行alter時可以指定algorithm和lock欄位，用於選擇ddl修改表結構時的演演算法和是否對原表加鎖，algorithm為inplace表示新增欄位時不再建立臨時表，直接在原表上新增欄位，避免重建錶帶來的IO和cpu消耗；lock = none表示進行ddl時不再鎖表，允許進行表資料的修改及存取

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

當然如果它的資料量特別巨大，且存取很頻繁的話，新增列的速度還是很慢，可以考慮選取一個存取量沒那麼大的時間段，先在從庫新增這一列，成功之後，進行主從切換，然後在原master節點和其餘從節點表中新增該欄位；

還可以使用pt_ocs工具，它會建立一個臨時表（當然臨時表結構是按照需要修改完全後的），然後將原表的資料copy到臨時表，並且會有一個觸發器，用於記錄並同步在copy過程中原表的資料變化，在資料同步完之後會短暫的鎖表執行觸發器，將臨時表rename並刪除原表。

MT用的是線上DDL工具gh-ost

臨時表從主庫拉取資料的速度由chunk-size引數控制，預設每次1000條每秒複製30次寫QPS 3W（若從庫臨時表根據binlog同步資料寫入速度沒跟上，當執行rename命令，完成表替換後，有可能會產生主從延遲）

mysql基礎之alter欄位解讀

1、先建立一張表testalter_tbl

    create table testalter_tbl
    (
     i INT,
     c CHAR(1)
    );

2、刪除，新增或修改表欄位

刪除欄位i

alter table testalter_tbl drop i

注意：如果資料表中只剩餘一個欄位則無法使用DROP來刪除欄位。

新增欄位i

使用 ADD 子句來向資料表中新增列，如下範例在表 testalter_tbl 中新增 i 欄位，並定義資料型別:

ALTER TABLE testalter_tbl ADD i INT;

注意：執行以上命令後，i 欄位會自動新增到資料表欄位的末尾。

也可以指定新增欄位的位置，通過使用使用MySQL提供的關鍵字 FIRST (設定位第一列)， AFTER 欄位名（設定位於某個欄位之後）。

ALTER TABLE testalter_tbl DROP i;#刪除i這一列
ALTER TABLE testalter_tbl ADD i INT FIRST;#把i這一列新增到第一列
ALTER TABLE testalter_tbl DROP i;#刪除i這一列
ALTER TABLE testalter_tbl ADD i INT AFTER c;#把i這一列新增到c之後

注意：FIRST 和 AFTER 關鍵字可用於 ADD 與 MODIFY 子句，所以如果你想重置資料表欄位的位置就需要先使用 DROP 刪除欄位然後使用 ADD 來新增欄位並設定位置。

3、修改欄位型別及名稱

如果需要修改欄位型別及名稱, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句。

把欄位 c 的型別從 CHAR(1) 改為 CHAR(10)

ALTER TABLE testalter_tbl MODIFY c CHAR(10);

使用 CHANGE 子句修改

ALTER TABLE testalter_tbl CHANGE i j BIGINT;#可以通過change改變欄位名和欄位型別

4、 ALTER TABLE 對 Null 值和預設值的影響

當你修改欄位時，你可以指定是否包含值或者是否設定預設值。

ALTER TABLE testalter_tbl 
     MODIFY j BIGINT NOT NULL DEFAULT 100;

注意：如果你不設定預設值，MySQL會自動設定該欄位預設為 NULL。

修改預設值

ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

刪除欄位的預設值

ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

修改表名

ALTER TABLE testalter_tbl RENAME TO alter_tbl;

總結

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。

mysql alter新增列的實現方式

目錄