mysql alter添加列

當(dāng)表中的數(shù)據(jù)量不是很大的時候可以直接使用alter命令添加新列，5.6及其之前的版本會將表鎖住。

alter的執(zhí)行過程如下

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

2、創(chuàng)建了一個臨時表，添加想要的字段

3、將原表中的數(shù)據(jù)復(fù)制到臨時表中

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

在5.6之后的版本mysql數(shù)據(jù)庫做了很多優(yōu)化，像onlineDDL在執(zhí)行alter時可以指定algorithm和lock字段，用于選擇ddl修改表結(jié)構(gòu)時的算法和是否對原表加鎖，algorithm為inplace表示添加字段時不再創(chuàng)建臨時表，直接在原表上添加字段，避免重建表帶來的IO和cpu消耗；lock = none表示進行ddl時不再鎖表，允許進行表數(shù)據(jù)的修改及訪問

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

當(dāng)然如果它的數(shù)據(jù)量特別巨大，且訪問很頻繁的話，添加列的速度還是很慢，可以考慮選取一個訪問量沒那么大的時間段，先在從庫添加這一列，成功之后，進行主從切換，然后在原master節(jié)點和其余從節(jié)點表中添加該字段；

還可以使用pt_ocs工具，它會創(chuàng)建一個臨時表（當(dāng)然臨時表結(jié)構(gòu)是按照需要修改完全后的），然后將原表的數(shù)據(jù)copy到臨時表，并且會有一個觸發(fā)器，用于記錄并同步在copy過程中原表的數(shù)據(jù)變化，在數(shù)據(jù)同步完之后會短暫的鎖表執(zhí)行觸發(fā)器，將臨時表rename并刪除原表。

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

臨時表從主庫拉取數(shù)據(jù)的速度由chunk-size參數(shù)控制，默認(rèn)每次1000條 每秒復(fù)制30次 寫QPS 3W（若從庫臨時表根據(jù)binlog同步數(shù)據(jù)寫入速度沒跟上，當(dāng)執(zhí)行rename命令，完成表替換后，有可能會產(chǎn)生主從延遲 ）

mysql基礎(chǔ)之a(chǎn)lter字段解讀

1、先創(chuàng)建一張表testalter_tbl

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

2、刪除，添加或修改表字段

• 刪除字段i

alter table testalter_tbl drop i

注意：如果數(shù)據(jù)表中只剩余一個字段則無法使用DROP來刪除字段。

• 添加字段i

使用 ADD 子句來向數(shù)據(jù)表中添加列，如下實例在表 testalter_tbl 中添加 i 字段，并定義數(shù)據(jù)類型:

ALTER TABLE testalter_tbl ADD i INT;

注意：執(zhí)行以上命令后，i 字段會自動添加到數(shù)據(jù)表字段的末尾。

也可以指定添加字段的位置，通過使用使用MySQL提供的關(guān)鍵字 FIRST (設(shè)定位第一列)， AFTER 字段名（設(shè)定位于某個字段之后）。

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 關(guān)鍵字可用于 ADD 與 MODIFY 子句，所以如果你想重置數(shù)據(jù)表字段的位置就需要先使用 DROP 刪除字段然后使用 ADD 來添加字段并設(shè)置位置。

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 值和默認(rèn)值的影響

當(dāng)你修改字段時，你可以指定是否包含值或者是否設(shè)置默認(rèn)值。

ALTER TABLE testalter_tbl?
? ? ?MODIFY j BIGINT NOT NULL DEFAULT 100;

注意：如果你不設(shè)置默認(rèn)值，MySQL會自動設(shè)置該字段默認(rèn)為 NULL。

• 修改默認(rèn)值

ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

• 刪除字段的默認(rèn)值

ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

• 修改表名

ALTER TABLE testalter_tbl RENAME TO alter_tbl;

總結(jié)

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論