MySQLのALTER TABLE ― 列の追加、MODIFYとCHANGEの違い、終わらないときのメタデータロック

※この記事にはアフィリエイト広告（プロモーション）が含まれます。

MySQL で表の列を足したり変えたりするのは ALTER TABLE です。列の変更には MODIFY と CHANGE の2つがあります。この記事の出力は MySQL 8.4.11（Ubuntu 26.04 の標準パッケージ）で採りました。

まず見る（結論）

ALTER TABLE emp ADD COLUMN dept varchar(10) AFTER name;          -- 列の追加（位置指定）
ALTER TABLE emp MODIFY name varchar(50) NOT NULL DEFAULT '' COMMENT '氏名';  -- 型の変更
ALTER TABLE emp RENAME COLUMN dept TO dept_code;                 -- 列名だけ変える
ALTER TABLE emp CHANGE dept_code dept varchar(20);               -- 名前と型を同時に
ALTER TABLE emp DROP COLUMN c2;                                  -- 列の削除

• MODIFY で書かなかった NOT NULL・DEFAULT・COMMENT は消える。SHOW CREATE TABLE で今の定義を写してから書く
• 名前だけ変えるなら RENAME COLUMN
• 終わらないときは SHOW PROCESSLIST の Waiting for table metadata lock を探す

列を追加する

ALTER TABLE emp ADD COLUMN dept varchar(10);
ALTER TABLE emp ADD COLUMN memo text AFTER name;      -- name の後ろ
ALTER TABLE emp ADD COLUMN no int FIRST;              -- 先頭
ALTER TABLE emp ADD COLUMN c1 int, ADD COLUMN c2 int; -- まとめて

行がある表に NOT NULL の列を既定値なしで足しても、エラーにはなりませんでした。既存の行には型ごとの暗黙の既定値（int なら 0）が入ります。

ALTER TABLE emp ADD COLUMN status int NOT NULL;
SELECT id, name, status FROM emp;
+----+--------+--------+
| id | name   | status |
+----+--------+--------+
|  1 | 佐藤   |      0 |
|  2 | 鈴木   |      0 |
|  3 | 高橋   |      0 |
+----+--------+--------+

ただし列定義に既定値は付かないので、その後 status を省略した INSERT は ERROR 1364 (HY000): Field 'status' doesn't have a default value になりました。既定値を決めて DEFAULT 0 まで書いておきます。

同じ名前の列があると ERROR 1060 (42S21): Duplicate column name 'dept' です。PostgreSQL にある ADD COLUMN IF NOT EXISTS は、MySQL 8.4 では ERROR 1064（構文エラー）でした。

MODIFY で定義が消える

name varchar(20) NOT NULL DEFAULT '' COMMENT '氏名' の列を、長さだけ変えるつもりで次のように書きました。

ALTER TABLE emp MODIFY name varchar(50);

SHOW CREATE TABLE emp\G
  `name` varchar(50) DEFAULT NULL,

NOT NULL・DEFAULT ''・COMMENT '氏名' がすべて消え、NULL を許す列になりました。エラーも警告も出ません。MODIFY は書いた内容で列を作り直すので、残したい属性は全部書きます。

ALTER TABLE emp MODIFY name varchar(50) NOT NULL DEFAULT '' COMMENT '氏名';

CHANGE も同じで、旧名・新名に続けて定義を全部書きます。ALTER TABLE emp CHANGE dept varchar(20) のように新しい名前を省くと ERROR 1064 です。名前を変えないなら CHANGE dept dept varchar(20) と同じ名前を2回書きます。

PostgreSQL の書き方（ALTER COLUMN code TYPE bigint）は MySQL では ERROR 1064 です。

型の変更が失敗する

ALTER TABLE emp MODIFY code int;
ERROR 1366 (HY000): Incorrect integer value: 'A30' for column 'code' at row 3

ALTER TABLE emp MODIFY name varchar(1);
ERROR 1265 (01000): Data truncated for column 'name' at row 1

数値にならない値や、新しい長さに入らない値が1行でもあると失敗し、表は変わりません。at row の番号を手がかりに値を直してから実行します。sql_mode が既定（STRICT_TRANS_TABLES を含む）の場合の結果です。

ALGORITHM と LOCK

MySQL 8.4 の InnoDB は、変更の種類によって表をコピーせずに済ませます。ALGORITHM を指定すると、その方法で実行できないときにエラーで止まるので、表のコピーが走るかを事前に確かめられます。

ALTER TABLE emp MODIFY code bigint, ALGORITHM=INSTANT;
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Need to rebuild the table to change column type. Try ALGORITHM=COPY/INPLACE.

ALTER TABLE emp MODIFY code bigint, ALGORITHM=COPY, LOCK=NONE;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.

varchar の延長は、utf8mb4 で 255 バイトを超えるかどうかで分かれました（varchar(30) は 120 バイト、varchar(300) は 1,200 バイト）。長さを記録する領域が1バイトから2バイトに変わるため、表の作り直しが要ります。COPY の間は表への書き込みが止まります。

ALTER TABLE が終わらない（メタデータロック待ち）

別の接続が表を読んだままトランザクションを開いていると、ALTER TABLE はその終了を待ちます。待っている間は、後から来た SELECT も止まります。

SELECT ID, USER, COMMAND, TIME, STATE, LEFT(INFO, 50) AS INFO
  FROM information_schema.PROCESSLIST ORDER BY ID;
+----+------+---------+------+---------------------------------+---------------------------------------+
| ID | USER | COMMAND | TIME | STATE                           | INFO                                  |
+----+------+---------+------+---------------------------------+---------------------------------------+
| 47 | root | Query   |    4 | User sleep                      | SELECT SLEEP(10)                      |
| 48 | root | Query   |    2 | Waiting for table metadata lock | ALTER TABLE fsv.emp ADD COLUMN c4 int |
| 49 | root | Query   |    1 | Waiting for table metadata lock | SELECT count(*) FROM fsv.emp          |
+----+------+---------+------+---------------------------------+---------------------------------------+

ID 47 は BEGIN; SELECT count(*) FROM fsv.emp; の後、トランザクションを閉じていない接続です。PROCESSLIST の INFO には今の文（SLEEP）しか出ないので、この接続が表のロックを持っていることは分かりません。相手は sys.schema_table_lock_waits で引きます。

SELECT * FROM sys.schema_table_lock_waits\G
（1行目から抜粋）
               waiting_pid: 48
             waiting_query: ALTER TABLE fsv.emp ADD COLUMN c4 int
              blocking_pid: 47
        blocking_lock_type: SHARED_READ
sql_kill_blocking_connection: KILL 47

blocking_pid が待たせている相手で、そのまま実行できる KILL 47 も出力されます。同じビューには、待っている ALTER TABLE（48）が後ろの SELECT（49）を待たせている行も並ぶので、blocking_lock_type が SHARED_READ の行を見ます。

lock_wait_timeout の既定は 31536000 秒（1年）です。待ち続けないように、セッションで短くしてから実行します。

SET SESSION lock_wait_timeout = 4;
ALTER TABLE emp ADD COLUMN c4 int;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

関連: MySQLでテーブル定義を確認する ／ MySQLの「Lock wait timeout exceeded」 ／ PostgreSQLのALTER TABLE