MySQL - ALTER TABLEのソースを表示

== 概要 ==
<code>ALTER TABLE</code> 文は、既存のテーブル構造を変更するためのDDL (Data Definition Language) 文である。<br>
<br>
この文を使用することで、カラムの追加や削除、データ型の変更、制約の追加や削除、テーブル名の変更、ストレージエンジンの変更等、様々なテーブル構造の変更が可能である。<br>
<br>
MySQL 8.0以降では、<code>INSTANT DDL</code>という強力な機能が導入され、多くの操作がメタデータの変更のみで完了し、秒以下で実行できるようになった。<br>
従来はテーブル全体の再構築 (<code>COPY</code>) が必要だった操作でも、<code>INPLACE</code>方式や<code>INSTANT</code>方式で実行できるようになり、大規模テーブルに対するスキーマ変更が大幅に高速化された。<br>
<br>
<code>ALTER TABLE</code> 文は、<code>INSTANT</code>、<code>INPLACE</code>、<code>COPY</code>の3つのアルゴリズムを使用する。<br>
<code>INSTANT</code>方式は最も高速でメタデータのみを変更し、<code>INPLACE</code>方式はテーブルを部分的に再構築して<code>DML</code>を一部許可し、<br>
<code>COPY</code>方式は完全なテーブル再構築を行い<code>DML</code>を完全にブロックする。<br>
<br>
操作の種類によって、どのアルゴリズムが使用されるかが異なり、パフォーマンスへの影響も大きく変わる。<br>
大規模なテーブルに対してスキーマ変更を行う場合は、実行前にアルゴリズムとロック方式を理解しておくことが重要である。<br>
<br><br>

== 基本構文 ==
==== ALTER TABLEの基本構文 ====
<code>ALTER TABLE</code> 文は、テーブル名の後に変更操作を指定する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 基本構文
 ALTER TABLE table_name
    operation1,
    operation2,
    ...;
 
 # 単一の操作
 ALTER TABLE employees ADD COLUMN email VARCHAR(255);
 
 # 複数の操作を1文で実行 (カンマ区切り)
 ALTER TABLE employees
    ADD COLUMN email VARCHAR(255),
    ADD COLUMN phone VARCHAR(20),
    MODIFY COLUMN salary DECIMAL(10, 2);
 </syntaxhighlight>
<br>
複数の操作を1文にまとめることで、テーブルの再構築が1回で済み、パフォーマンスが向上する場合がある。<br>
<br>
==== ALGORITHM と LOCK句 ====
MySQL 8.0以降では、<code>ALGORITHM</code> 句 と <code>LOCK</code> 句を使用して、実行方法を明示的に指定できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # ALGORITHMを指定
 ALTER TABLE employees
    ADD COLUMN email VARCHAR(255),
    ALGORITHM=INSTANT;
 
 # LOCKを指定
 ALTER TABLE employees
    ADD COLUMN email VARCHAR(255),
    LOCK=NONE;
 
 # 両方を指定
 ALTER TABLE employees
    ADD COLUMN email VARCHAR(255),
    ALGORITHM=INSTANT,
    LOCK=NONE;
 </syntaxhighlight>
<br>
<center>
{| class="wikitable"
|+ ALGORITHM句の指定値
! 指定値 !! 説明 !! 実行時間の目安
|-
| INSTANT || メタデータのみ変更、ディスク変更最小 || 秒以下
|-
| INPLACE || テーブルを部分的に再構築、DML部分許可 || 分〜数時間
|-
| COPY || テーブル全体を完全に再構築、DMLブロック || 数時間〜数日
|-
| DEFAULT || MySQLが自動選択 (INSTANT -> INPLACE -> COPYの順に試行) || 操作により異なる
|}
</center>
<br>
<center>
{| class="wikitable"
|+ LOCK句の指定値
! 指定値 !! 説明
|-
| NONE || ロックなし、SELECT / INSERT / UPDATE / DELETE 全て許可
|-
| SHARED || 共有ロック、SELECT許可、DML (INSERT / UPDATE / DELETE) はブロック
|-
| EXCLUSIVE || 排他ロック、全ての操作をブロック
|-
| DEFAULT || MySQLが自動選択 (可能な限り緩いロックを使用)
|}
</center>
<br>
<u><code>ALGORITHM</code> を指定しない場合、MySQLは自動的に最適なアルゴリズムを選択する。</u><br>
<u>指定したアルゴリズムで操作が実行できない場合は、エラーが発生する。</u><br>
<br>
 <syntaxhighlight lang="mysql">
 # ALGORITHM=INSTANTを要求するが、INSTANTで実行できない操作の場合はエラー
 ALTER TABLE employees
    DROP COLUMN email,
    ALGORITHM=INSTANT;
 # MySQL 8.0.29未満ではエラー: ALGORITHM=INSTANT is not supported.
 </syntaxhighlight>
<br><br>

== カラム操作 ==
==== ADD COLUMN : カラム追加 ====
<code>ADD COLUMN</code> は、テーブルに新しいカラムを追加する。<br>
<br>
MySQL 8.0.12以降では、デフォルトで <code>ALGORITHM=INSTANT</code> が使用され、秒以下で実行される。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 基本的なカラム追加 (テーブル末尾に追加)
 ALTER TABLE employees
    ADD COLUMN email VARCHAR(255);
 
 # デフォルト値を指定
 ALTER TABLE employees
    ADD COLUMN status VARCHAR(20) DEFAULT 'active';
 
 # NOT NULL制約を指定
 ALTER TABLE employees
    ADD COLUMN department_id INT NOT NULL;
 
 # 複数のカラムを同時に追加
 ALTER TABLE employees
    ADD COLUMN email VARCHAR(255),
    ADD COLUMN phone VARCHAR(20),
    ADD COLUMN address TEXT;
 </syntaxhighlight>
<br>
カラムの位置を指定することもできる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # テーブルの先頭にカラムを追加
 ALTER TABLE employees
    ADD COLUMN employee_code VARCHAR(10) FIRST;
 
 # 特定のカラムの後にカラムを追加
 ALTER TABLE employees
    ADD COLUMN middle_name VARCHAR(50) AFTER first_name;
 
 # 複数のカラムを異なる位置に追加
 ALTER TABLE employees
    ADD COLUMN employee_code VARCHAR(10) FIRST,
    ADD COLUMN email VARCHAR(255) AFTER last_name;
 </syntaxhighlight>
<br>
MySQL 8.0.12以降では、<code>FIRST</code> や <code>AFTER</code> を指定しても、<code>ALGORITHM=INSTANT</code> で実行される。<br>
ただし、テーブルには以下に示す制限がある。<br>
<br>
* 最大1022カラムまで
*: この制限を超えると、INSTANT追加できなくなる。
*: <br>
* 最大64行バージョンまで
*: INSTANTでカラムを追加するたびに行バージョンが増加する。
*: 64回を超えるとテーブル再構築が必要
<br>
 <syntaxhighlight lang="mysql">
 # 行バージョンの確認
 SELECT TABLE_NAME, TOTAL_ROW_VERSIONS
    FROM information_schema.INNODB_TABLES
    WHERE NAME = 'database_name/table_name';
 </syntaxhighlight>
<br>
==== DROP COLUMN : カラム削除 ====
<code>DROP COLUMN</code> は、テーブルからカラムを削除する。<br>
<br>
MySQL 8.0.29以降では、<code>ALGORITHM=INSTANT</code> で実行される。<br>
MySQL 8.0.28以前では、<code>ALGORITHM=INPLACE</code> が使用される。<br>
<br>
 <syntaxhighlight lang="mysql">
 # カラムを削除
 ALTER TABLE employees
    DROP COLUMN email;
 
 # 複数のカラムを削除
 ALTER TABLE employees
    DROP COLUMN email,
    DROP COLUMN phone;
 
 # COLUMNキーワードを省略 (可能だが非推奨)
 ALTER TABLE employees
    DROP email;
 </syntaxhighlight>
<br>
カラム削除には以下の制限がある。<br>
<br>
* テーブルの最後の1列は削除できない
*: 少なくとも1つのカラムが必要。
*: <br>
* 外部キー制約で参照されているカラムは削除できない
*: 先に外部キー制約を削除する必要がある。
*: <br>
* インデックスに含まれるカラムは削除できない
*: 先にインデックスを削除する必要がある。
<br>
 <syntaxhighlight lang="mysql">
 # エラー例: 最後の1列を削除
 ALTER TABLE single_column_table
    DROP COLUMN only_column;
 # ERROR 1090 (42000): You can't delete all columns with ALTER TABLE
 
 # 外部キー制約とインデックスを削除してからカラムを削除
 ALTER TABLE orders
    DROP FOREIGN KEY fk_user_id,
    DROP INDEX idx_user_id,
    DROP COLUMN user_id;
 </syntaxhighlight>
<br>
==== MODIFY COLUMN : データ型と制約の変更 ====
<code>MODIFY COLUMN</code> は、カラムのデータ型や制約を変更する。<br>
<br>
カラム名は変更しない。<br>
カラム名を変更する場合は、<code>RENAME COLUMN</code> または <code>CHANGE COLUMN</code> を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # データ型を変更
 ALTER TABLE employees
    MODIFY COLUMN salary DECIMAL(12, 2);
 
 # NOT NULL制約を追加
 ALTER TABLE employees
    MODIFY COLUMN email VARCHAR(255) NOT NULL;
 
 # NOT NULL制約を削除 (NULLを許可)
 ALTER TABLE employees
    MODIFY COLUMN email VARCHAR(255);
 
 # デフォルト値を変更
 ALTER TABLE employees
    MODIFY COLUMN status VARCHAR(20) DEFAULT 'inactive';
 
 # 複数のカラムを変更
 ALTER TABLE employees
    MODIFY COLUMN salary DECIMAL(12, 2),
    MODIFY COLUMN email VARCHAR(255) NOT NULL;
 </syntaxhighlight>
<br>
データ型の変更は、互換性のある型への変更であれば <code>ALGORITHM=INSTANT</code> で実行される場合がある。<br>
非互換な変更 (例: VARCHAR(50)型からINT型への変更) は、テーブルの再構築が必要となり、<code>ALGORITHM=COPY</code> が使用される。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 互換性のある変更 (INSTANT可能)
 ALTER TABLE employees
    MODIFY COLUMN name VARCHAR(255);  # VARCHAR(100) から VARCHAR(255)
 
 # 非互換な変更 (COPY必要)
 ALTER TABLE employees
    MODIFY COLUMN department_id VARCHAR(10);  # INT から VARCHAR
 </syntaxhighlight>
<br>
<code>MODIFY COLUMN</code> では、変更しない属性も含めて完全な定義を記述する必要がある。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 元の定義: email VARCHAR(255) NOT NULL DEFAULT ''
 
 # デフォルト値のみを変更したい場合でも、完全な定義を記述
 ALTER TABLE employees
    MODIFY COLUMN email VARCHAR(255) NOT NULL DEFAULT 'unknown@example.com';
 
 # NOT NULLを省略すると、NULLを許可する定義になる
 ALTER TABLE employees
    MODIFY COLUMN email VARCHAR(255) DEFAULT 'unknown@example.com';
 # この場合、NOT NULL制約が削除される
 </syntaxhighlight>
<br>
==== CHANGE COLUMN : カラム名とデータ型の変更 ====
<code>CHANGE COLUMN</code> は、カラム名とデータ型の両方を変更できる。<br>
<br>
ただし、カラム名のみを変更する場合は、<code>RENAME COLUMN</code> を使用することが推奨される。<br>
<br>
 <syntaxhighlight lang="mysql">
 # カラム名とデータ型を変更
 ALTER TABLE employees
    CHANGE COLUMN old_name new_name VARCHAR(100);
 
 # カラム名のみを変更 (データ型は同じ)
 ALTER TABLE employees
    CHANGE COLUMN email email_address VARCHAR(255);
 
 # データ型も同時に変更
 ALTER TABLE employees
    CHANGE COLUMN salary monthly_salary DECIMAL(12, 2);
 </syntaxhighlight>
<br>
<u><code>CHANGE COLUMN</code> は、旧形式のコマンドである。</u><br>
<u>MySQL 8.0以降では、カラム名の変更には <code>RENAME COLUMN</code> を使用することが推奨される。</u><br>
<br>
 <syntaxhighlight lang="mysql">
 # 新形式 : RENAME COLUMN (推奨)
 ALTER TABLE employees
    RENAME COLUMN email TO email_address;
 
 # 旧形式 : CHANGE COLUMN (非推奨)
 ALTER TABLE employees
    CHANGE COLUMN email email_address VARCHAR(255);
 </syntaxhighlight>
<br>
==== RENAME COLUMN : カラム名の変更 (MySQL 8.0+) ====
<code>RENAME COLUMN</code> は、MySQL 8.0以降で使用可能なカラム名変更の専用構文である。<br>
<br>
<code>ALGORITHM=INSTANT</code> で実行され、データ型や制約は変更されない。<br>
<br>
 <syntaxhighlight lang="mysql">
 # カラム名を変更
 ALTER TABLE employees
    RENAME COLUMN email TO email_address;
 
 # 複数のカラム名を変更
 ALTER TABLE employees
    RENAME COLUMN first_name TO given_name,
    RENAME COLUMN last_name TO family_name;
 </syntaxhighlight>
<br>
<code>RENAME COLUMN</code> は、<code>CHANGE COLUMN</code> より簡潔で、意図が明確である。<br>
また、データ型を再指定する必要がないため、エラーが発生しにくい。<br>
<br>
==== ALTER COLUMN SET/DROP DEFAULT : デフォルト値の変更 ====
<code>ALTER COLUMN SET DEFAULT</code> は、カラムのデフォルト値を設定する。<br>
<code>ALTER COLUMN DROP DEFAULT</code> は、カラムのデフォルト値を削除する。<br>
<br>
どちらも <code>ALGORITHM=INSTANT</code> で実行される。<br>
<br>
 <syntaxhighlight lang="mysql">
 # デフォルト値を設定
 ALTER TABLE employees
    ALTER COLUMN status SET DEFAULT 'active';
 
 # デフォルト値を削除
 ALTER TABLE employees
    ALTER COLUMN status DROP DEFAULT;
 
 # 複数のカラムのデフォルト値を変更
 ALTER TABLE employees
    ALTER COLUMN status SET DEFAULT 'active',
    ALTER COLUMN salary SET DEFAULT 0;
 </syntaxhighlight>
<br>
<code>ALTER COLUMN</code> は、デフォルト値の変更のみを行う。<br>
データ型やNOT NULL制約を変更する場合は、<code>MODIFY COLUMN</code> を使用する。<br>
<br><br>

== テーブル操作 ==
==== RENAME TABLE / RENAME TO : テーブル名の変更 ====
<code>RENAME TO</code> は、テーブル名を変更する。<br>
<br>
<code>ALGORITHM=INSTANT</code> で実行され、メタデータのみが変更される。<br>
<br>
 <syntaxhighlight lang="mysql">
 # テーブル名を変更
 ALTER TABLE old_table_name
    RENAME TO new_table_name;
 
 # RENAME TOの代わりにRENAME ASも使用可能
 ALTER TABLE old_table_name
    RENAME AS new_table_name;
 </syntaxhighlight>
<br>
<code>RENAME TABLE</code> 文を使用することもできる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # RENAME TABLE文 (推奨)
 RENAME TABLE old_table_name TO new_table_name;
 
 # 複数のテーブルを同時に変更
 RENAME TABLE
    old_table1 TO new_table1,
    old_table2 TO new_table2,
    old_table3 TO new_table3;
 </syntaxhighlight>
<br>
<code>RENAME TABLE</code> 文の方が簡潔で複数のテーブルを同時に変更できるため、推奨される。<br>
<br>
==== ENGINE : ストレージエンジンの変更 ====
<code>ENGINE</code> 句は、テーブルのストレージエンジンを変更する。<br>
<br>
この操作は、<code>ALGORITHM=COPY</code> で実行され、テーブル全体が再構築される。<br>
大規模なテーブルでは、非常に時間が掛かる可能性がある。<br>
<br>
 <syntaxhighlight lang="mysql">
 # ストレージエンジンをInnoDBに変更
 ALTER TABLE employees
    ENGINE=InnoDB;
 
 # MyISAMからInnoDBへの変換 (一般的な用途)
 ALTER TABLE old_myisam_table
    ENGINE=InnoDB;
 </syntaxhighlight>
<br>
同じストレージエンジンを指定した場合でも、テーブルの再構築が実行される。<br>
これを利用して、テーブルの最適化やディスク領域の再利用が可能である。<br>
<br>
 <syntaxhighlight lang="mysql">
 # テーブルの再構築による最適化
 ALTER TABLE employees
    ENGINE=InnoDB;
 # 既にInnoDBでも、再構築される
 </syntaxhighlight>
<br>
ただし、<code>OPTIMIZE TABLE</code> 文を使用する方が意図が明確である。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 推奨: OPTIMIZE TABLE
 OPTIMIZE TABLE employees;
 </syntaxhighlight>
<br>
==== CONVERT TO CHARACTER SET : 文字セットの変換 ====
<code>CONVERT TO CHARACTER SET</code> は、テーブルの文字セットとコレーションを変換する。<br>
<br>
この操作は、全ての文字列カラム (CHAR, VARCHAR, TEXT) のデータを変換し、<code>ALGORITHM=COPY</code> で実行される。<br>
<br>
 <syntaxhighlight lang="mysql">
 # テーブル全体の文字セットをutf8mb4に変換
 ALTER TABLE employees
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 
 # コレーションを省略 (デフォルトのコレーションが使用される)
 ALTER TABLE employees
    CONVERT TO CHARACTER SET utf8mb4;
 </syntaxhighlight>
<br>
この操作は、既存のデータを変換するため、時間がかかる可能性がある。<br>
また、文字セット変換により、一部の文字が失われる可能性もある (例: latin1からutf8への変換)。<br>
<br>
==== DEFAULT CHARACTER SET : デフォルト文字セットの変更 ====
<code>DEFAULT CHARACTER SET</code> は、テーブルのデフォルト文字セットを変更する。<br>
<br>
この操作は、既存のカラムの文字セットは変更せず、新しく追加されるカラムのデフォルト文字セットのみを変更する。<br>
<code>ALGORITHM=INSTANT</code> で実行される。<br>
<br>
 <syntaxhighlight lang="mysql">
 # デフォルト文字セットを変更 (既存カラムは影響なし)
 ALTER TABLE employees
    DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 
 # 既存カラムの文字セットは変わらない
 # 今後追加されるカラムはutf8mb4になる
 </syntaxhighlight>
<br>
既存のカラムも含めて文字セットを変換する場合は、<code>CONVERT TO CHARACTER SET</code> を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 比較: CONVERT TO CHARACTER SET (既存カラムも変換)
 ALTER TABLE employees
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 </syntaxhighlight>
<br>
==== AUTO_INCREMENT : 次のシーケンス値の設定 ====
<code>AUTO_INCREMENT</code> 句は、次に挿入される行の自動インクリメント値を設定する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 次のAUTO_INCREMENT値を1000に設定
 ALTER TABLE employees
    AUTO_INCREMENT = 1000;
 
 # 現在の値より小さい値は無視される
 ALTER TABLE employees
    AUTO_INCREMENT = 1;
 # 現在のAUTO_INCREMENT値が既に100の場合、この操作は無視される
 </syntaxhighlight>
<br>
現在の <code>AUTO_INCREMENT</code> 値を確認するには、<code>SHOW CREATE TABLE</code> を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 現在のAUTO_INCREMENT値を確認
 SHOW CREATE TABLE employees;
 </syntaxhighlight>
<br><br>

== 制約操作 ==
==== PRIMARY KEY : 主キーの追加と削除 ====
<code>ADD PRIMARY KEY</code> は、テーブルに主キーを追加する。<br>
<code>DROP PRIMARY KEY</code> は、テーブルから主キーを削除する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 主キーを追加
 ALTER TABLE employees
    ADD PRIMARY KEY (id);
 
 # 複合主キーを追加
 ALTER TABLE order_items
    ADD PRIMARY KEY (order_id, product_id);
 
 # 主キーを削除
 ALTER TABLE employees
    DROP PRIMARY KEY;
 </syntaxhighlight>
<br>
主キーの追加は、<code>ALGORITHM=INPLACE</code> で実行される。<br>
主キーの削除のみは、<code>ALGORITHM=COPY</code> が必要となる。<br>
<br>
ただし、主キーの削除と追加を同時に行う場合は、<code>ALGORITHM=INPLACE</code> で実行される。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 主キーを削除してから追加 (INPLACE可能)
 ALTER TABLE employees
    DROP PRIMARY KEY,
    ADD PRIMARY KEY (employee_code);
 </syntaxhighlight>
<br>
主キーを追加する場合、カラムは <code>NOT NULL</code> である必要がある。<br>
<br>
 <syntaxhighlight lang="mysql">
 # エラー例: NULLを許可するカラムに主キーを追加
 ALTER TABLE employees
    ADD PRIMARY KEY (email);
 # ERROR 1171 (42000): All parts of a PRIMARY KEY must be NOT NULL
 
 # 先にNOT NULL制約を追加
 ALTER TABLE employees
    MODIFY COLUMN email VARCHAR(255) NOT NULL,
    ADD PRIMARY KEY (email);
 </syntaxhighlight>
<br>
==== UNIQUE / INDEX : 一意制約とインデックスの操作 ====
<code>ADD UNIQUE</code> は、一意制約 (ユニークインデックス) を追加する。<br>
<code>ADD INDEX</code> は、通常のインデックスを追加する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 一意制約を追加
 ALTER TABLE employees
    ADD UNIQUE (email);
 
 # 名前を指定して一意制約を追加
 ALTER TABLE employees
    ADD UNIQUE INDEX idx_email (email);
 
 # 複合一意制約を追加
 ALTER TABLE employees
    ADD UNIQUE (department_id, employee_code);
 
 # 通常のインデックスを追加
 ALTER TABLE employees
    ADD INDEX idx_department (department_id);
 
 # 複合インデックスを追加
 ALTER TABLE employees
    ADD INDEX idx_dept_salary (department_id, salary);
 </syntaxhighlight>
<br>
インデックスの削除は、<code>DROP INDEX</code> を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # インデックスを削除
 ALTER TABLE employees
    DROP INDEX idx_email;
 
 # 複数のインデックスを削除
 ALTER TABLE employees
    DROP INDEX idx_email,
    DROP INDEX idx_department;
 </syntaxhighlight>
<br>
MySQL 8.0以降では、<code>RENAME INDEX</code> でインデックス名を変更できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # インデックス名を変更 (MySQL 8.0+)
 ALTER TABLE employees
    RENAME INDEX old_index_name TO new_index_name;
 </syntaxhighlight>
<br>
また、不可視インデックス (Invisible Index) の設定も可能である。<br>
<br>
 <syntaxhighlight lang="mysql">
 # インデックスを不可視にする (MySQL 8.0+)
 ALTER TABLE employees
    ALTER INDEX idx_email INVISIBLE;
 
 # インデックスを可視にする
 ALTER TABLE employees
    ALTER INDEX idx_email VISIBLE;
 </syntaxhighlight>
<br>
不可視インデックスは、オプティマイザーによって使用されなくなるが、インデックス自体は維持される。<br>
インデックスの削除前に、パフォーマンスへの影響を確認するために使用できる。<br>
<br>
==== FOREIGN KEY : 外部キー制約の操作 ====
<code>ADD FOREIGN KEY</code> は、外部キー制約を追加する。<br>
<code>DROP FOREIGN KEY</code> は、外部キー制約を削除する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 外部キー制約を追加
 ALTER TABLE orders
    ADD FOREIGN KEY (user_id) REFERENCES users(id);
 
 # 名前を指定して外部キー制約を追加
 ALTER TABLE orders
    ADD CONSTRAINT fk_user_id
       FOREIGN KEY (user_id) REFERENCES users(id);
 
 # ON DELETE / ON UPDATE句を指定
 ALTER TABLE orders
    ADD CONSTRAINT fk_user_id
       FOREIGN KEY (user_id) REFERENCES users(id)
       ON DELETE CASCADE
       ON UPDATE CASCADE;
 
 # 複合外部キー制約を追加
 ALTER TABLE order_items
    ADD CONSTRAINT fk_order
       FOREIGN KEY (order_id, product_id) REFERENCES orders(id, product_id);
 </syntaxhighlight>
<br>
外部キー制約の削除は、制約名を指定する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 外部キー制約を削除
 ALTER TABLE orders
    DROP FOREIGN KEY fk_user_id;
 </syntaxhighlight>
<br>
外部キー制約名を確認するには、<code>SHOW CREATE TABLE</code> を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 外部キー制約名を確認
 SHOW CREATE TABLE orders;
 
 # または、INFORMATION_SCHEMAから確認
 SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME
    FROM information_schema.KEY_COLUMN_USAGE
    WHERE TABLE_SCHEMA = 'database_name'
       AND TABLE_NAME = 'orders'
       AND REFERENCED_TABLE_NAME IS NOT NULL;
 </syntaxhighlight>
<br>
外部キー制約の参照動作 (ON DELETE / ON UPDATE) には、以下のオプションがある。<br>
<br>
* <code>CASCADE</code>
*: 親レコードの削除 / 更新時、子レコードも削除/更新する。
*: <br>
* <code>SET NULL</code>
*: 親レコードの削除 / 更新時、子レコードの外部キーを <code>NULL</code> に設定する。
*: <br>
* <code>RESTRICT</code>
*: 親レコードの削除 / 更新を禁止する。(デフォルト)
*: <br>
* <code>NO ACTION</code>
*: <code>RESTRICT</code> と同じ。(SQL標準)
* <code>SET DEFAULT</code>
*: MySQL 8.0ではサポートされていない。
<br>
詳細は、[[MySQL - 外部キー]]のページを参照すること。<br>
<br>
==== CHECK制約 (MySQL 8.0.16+) ====
<code>ADD CHECK</code> は、チェック制約を追加する。<br>
<code>DROP CHECK</code> は、チェック制約を削除する。<br>
<br>
チェック制約は、MySQL 8.0.16以降で使用可能である。<br>
<br>
 <syntaxhighlight lang="mysql">
 # チェック制約を追加
 ALTER TABLE employees
    ADD CHECK (salary >= 0);
 
 # 名前を指定してチェック制約を追加
 ALTER TABLE employees
    ADD CONSTRAINT chk_salary CHECK (salary >= 0);
 
 # 複雑な条件のチェック制約
 ALTER TABLE employees
    ADD CONSTRAINT chk_email CHECK (email LIKE '%@%');
 
 # 複数カラムを参照するチェック制約
 ALTER TABLE employees
    ADD CONSTRAINT chk_dates CHECK (hire_date <= termination_date);
 </syntaxhighlight>
<br>
チェック制約の削除は、制約名を指定する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # チェック制約を削除
 ALTER TABLE employees
    DROP CHECK chk_salary;
 </syntaxhighlight>
<br>
チェック制約は、<code>ENFORCED</code> または <code>NOT ENFORCED</code> を指定できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # チェック制約を無効化
 ALTER TABLE employees
    ALTER CHECK chk_salary NOT ENFORCED;
 
 # チェック制約を有効化
 ALTER TABLE employees
    ALTER CHECK chk_salary ENFORCED;
 </syntaxhighlight>
<br>
チェック制約名を確認するには、<code>INFORMATION_SCHEMA</code> を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # チェック制約を確認
 SELECT CONSTRAINT_NAME, CHECK_CLAUSE
    FROM information_schema.CHECK_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = 'database_name'
       AND TABLE_NAME = 'employees';
 </syntaxhighlight>
<br><br>

== MySQL 8.0以降の新機能 ==
==== INSTANT DDL ====
MySQL 8.0以降では、<code>INSTANT DDL</code> という機能により、多くのスキーマ変更がメタデータのみの変更で完了する。<br>
<br>
<code>INSTANT DDL</code> は、テーブルデータを物理的に変更せず、秒以下で完了する。<br>
大規模なテーブルに対するスキーマ変更が劇的に高速化された。<br>
<br>
<center>
{| class="wikitable"
|+ INSTANT DDLに対応する操作 (MySQL 8.0+)
! 操作 !! MySQL 8.0.12+ !! MySQL 8.0.29+
|-
| ADD COLUMN || 対応 || 対応
|-
| DROP COLUMN || 非対応 (INPLACE) || 対応
|-
| RENAME COLUMN || 対応 || 対応
|-
| ALTER COLUMN SET/DROP DEFAULT || 対応 || 対応
|-
| ENUM / SET型への値追加 || 対応 || 対応
|-
| RENAME TABLE || 対応 || 対応
|-
| DEFAULT CHARACTER SET || 対応 || 対応
|}
</center>
<br>
<code>INSTANT DDL</code> の実行例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # カラム追加 (INSTANT)
 ALTER TABLE employees
    ADD COLUMN email VARCHAR(255),
    ALGORITHM=INSTANT;
 # 実行時間: 約0.1秒 (65万行のテーブル)
 
 # カラム削除 (MySQL 8.0.29+でINSTANT)
 ALTER TABLE employees
    DROP COLUMN email,
    ALGORITHM=INSTANT;
 # 実行時間: 約0.1秒 (65万行のテーブル)
 
 # デフォルト値変更 (INSTANT)
 ALTER TABLE employees
    ALTER COLUMN status SET DEFAULT 'active',
    ALGORITHM=INSTANT;
 # 実行時間: 秒以下
 </syntaxhighlight>
<br>
<code>INSTANT DDL</code> の制限事項を以下に示す。<br>
<br>
* 最大1022カラムまで
*: InnoDBの最大カラム数制限。
*: <br>
* 最大64行バージョンまで
*: INSTANTでカラムを追加するたびに行バージョンが増加する。
*: 64回を超えるとテーブル再構築が必要。
*: <br>
* 一部のデータ型変更はINSTANT非対応
*: 非互換なデータ型変更はCOPYが必要。
<br>
行バージョンを確認して、64回の制限に近づいている場合は、テーブルを再構築する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 行バージョンの確認
 SELECT TABLE_NAME, TOTAL_ROW_VERSIONS
    FROM information_schema.INNODB_TABLES
    WHERE NAME = 'database_name/employees';
 
 # テーブル再構築により行バージョンをリセット
 ALTER TABLE employees
    ENGINE=InnoDB,
    ALGORITHM=COPY;
 </syntaxhighlight>
<br>
==== 不可視カラム (Invisible Column) ====
MySQL 8.0.23以降では、不可視カラム (Invisible Column) を作成できる。<br>
<br>
不可視カラムは、<code>SELECT *</code> から除外されるが、明示的に指定すれば取得できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 不可視カラムを追加
 ALTER TABLE employees
    ADD COLUMN internal_notes TEXT INVISIBLE;
 
 # 既存カラムを不可視にする
 ALTER TABLE employees
    MODIFY COLUMN internal_id INT INVISIBLE;
 
 # 不可視カラムを可視にする
 ALTER TABLE employees
    MODIFY COLUMN internal_notes TEXT VISIBLE;
 </syntaxhighlight>
<br>
不可視カラムの動作確認を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # SELECT * では不可視カラムは表示されない
 SELECT * FROM employees;
 # internal_notesカラムは表示されない
 
 # 明示的に指定すれば取得できる
 SELECT id, name, internal_notes FROM employees;
 # internal_notesカラムが表示される
 </syntaxhighlight>
<br>
不可視カラムは、以下のような用途で使用される。<br>
<br>
* 内部的なメタデータの保存
*: アプリケーションから隠したい情報を保存。
* 段階的なスキーマ変更
*: 新しいカラムを不可視で追加し、テスト後に可視にする。
* 後方互換性の維持
*: SELECT * を使用する既存コードに影響を与えずにカラムを追加。
<br>
==== その他のMySQL 8.0新機能 ====
MySQL 8.0では、<code>ALTER TABLE</code> に関連する多くの機能が追加された。<br>
<br>
* <code>RENAME INDEX</code> (8.0+)
*: インデックス名を変更。
*: <br>
* <code>ALTER INDEX INVISIBLE/VISIBLE</code> (8.0+)
*: インデックスを不可視/可視に変更。
*: <br>
* <code>CHECK</code>制約 (8.0.16+)
*: チェック制約の追加と管理。
*: <br>
* <code>INSTANT DDL</code>の拡張 (8.0.12+, 8.0.29+)
*: より多くの操作が<code>INSTANT</code>に対応。
*: <br>
* 式インデックス (8.0.13+)
*: 関数や式に対するインデックスの作成。
<br>
 <syntaxhighlight lang="mysql">
 # 式インデックスの作成 (MySQL 8.0.13+)
 ALTER TABLE employees
    ADD INDEX idx_upper_name ((UPPER(name)));
 
 # 関数インデックスの作成
 ALTER TABLE orders
    ADD INDEX idx_year ((YEAR(ordered_at)));
 </syntaxhighlight>
<br><br>

== パフォーマンスとアルゴリズム ==
==== INSTANT / INPLACE / COPYの違い ====
<code>ALTER TABLE</code> 文は、3つのアルゴリズムを使用する。<br>
<br>
<center>
{| class="wikitable"
|+ ALTER TABLEのアルゴリズム比較
! アルゴリズム !! 実行時間 !! ディスク使用量 !! ロック !! DML許可
|-
| INSTANT || 秒以下 || 最小 (メタデータのみ) || なし || 完全許可
|-
| INPLACE || 分〜数時間 || 中程度 (一時ファイル) || 短時間のみ || 部分的に許可
|-
| COPY || 最長 (数時間〜数日) || 大 (テーブル2倍以上) || 完全ロック || 完全ブロック
|}
</center>
<br>
パフォーマンスの実測例 (65万行のテーブル) を以下に示す。<br>
<br>
* INSTANT (カラム追加)
*: 約0.1秒
*: <br>
* INPLACE (インデックス追加)
*: 約30秒〜数分
*: <br>
* COPY (ストレージエンジン変更)
*: 約4.5時間
<br>
 <syntaxhighlight lang="mysql">
 # INSTANTで実行 (最速)
 ALTER TABLE employees
    ADD COLUMN email VARCHAR(255),
    ALGORITHM=INSTANT;
 
 # INPLACEで実行 (中速)
 ALTER TABLE employees
    ADD INDEX idx_email (email),
    ALGORITHM=INPLACE;
 
 # COPYで実行 (最遅)
 ALTER TABLE employees
    ENGINE=InnoDB,
    ALGORITHM=COPY;
 </syntaxhighlight>
<br>
MySQLは、<code>ALGORITHM</code> を指定しない場合、自動的に最適なアルゴリズムを選択する。<br>
優先順位は、<u>INSTANT</u> -> <u>INPLACE</u> -> <u>COPY</u> の順である。<br>
<br>
 <syntaxhighlight lang="mysql">
 # ALGORITHMを指定しない (自動選択)
 ALTER TABLE employees
    ADD COLUMN email VARCHAR(255);
 # INSTANTで実行される
 
 ALTER TABLE employees
    ADD INDEX idx_email (email);
 # INPLACEで実行される
 
 ALTER TABLE employees
    ENGINE=InnoDB;
 # COPYで実行される
 </syntaxhighlight>
<br>
特定のアルゴリズムを強制する場合は、<code>ALGORITHM</code> 句を指定する。<br>
指定したアルゴリズムで実行できない場合は、エラーが発生する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # INSTANT強制 (実行できない場合はエラー)
 ALTER TABLE employees
    DROP COLUMN email,
    ALGORITHM=INSTANT;
 # MySQL 8.0.28以前ではエラー
 
 # INPLACE強制 (実行できない場合はエラー)
 ALTER TABLE employees
    ENGINE=InnoDB,
    ALGORITHM=INPLACE;
 # COPYが必要な操作のためエラー
 </syntaxhighlight>
<br>
==== LOCKの種類と影響 ====
<code>LOCK</code> 句は、<code>ALTER TABLE</code> 実行中のテーブルロックレベルを指定する。<br>
<br>
<center>
{| class="wikitable"
|+ LOCKレベルと操作の許可
! LOCKレベル !! SELECT !! INSERT !! UPDATE !! DELETE
|-
| NONE || 許可 || 許可 || 許可 || 許可
|-
| SHARED || 許可 || ブロック || ブロック || ブロック
|-
| EXCLUSIVE || ブロック || ブロック || ブロック || ブロック
|-
| DEFAULT || MySQLが自動選択 || - || - || -
|}
</center>
<br>
 <syntaxhighlight lang="mysql">
 # LOCK=NONE (最も緩い、DML許可)
 ALTER TABLE employees
    ADD INDEX idx_email (email),
    LOCK=NONE;
 # SELECT/INSERT/UPDATE/DELETE全て許可
 
 # LOCK=SHARED (SELECTのみ許可)
 ALTER TABLE employees
    ADD INDEX idx_email (email),
    LOCK=SHARED;
 # SELECTは許可、DMLはブロック
 
 # LOCK=EXCLUSIVE (完全ロック)
 ALTER TABLE employees
    ENGINE=InnoDB,
    LOCK=EXCLUSIVE;
 # 全ての操作をブロック
 </syntaxhighlight>
<br>
<code>LOCK=NONE</code> を指定した場合、操作が <code>LOCK=NONE</code> で実行できなければエラーになる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # LOCK=NONEを強制
 ALTER TABLE employees
    ENGINE=InnoDB,
    LOCK=NONE;
 # エラー: LOCK=NONE is not supported. Reason: Cannot change storage engine.
 </syntaxhighlight>
<br>
==== 大規模テーブルへの対処 ====
大規模なテーブルに対して <code>ALTER TABLE</code> を実行する場合は、以下の点に注意する。<br>
<br>
* ディスク容量の確保
*: COPY方式では、テーブルサイズの2倍以上のディスク容量が必要。
*: <br>
* 実行時間の見積もり
*: 事前に小規模なテーブルで実行時間を測定。
*: <br>
* ロックとDMLのブロック時間
*: アプリケーションへの影響を考慮。
*: <br>
* メタデータロックの確認
*: 長時間実行中のトランザクションがあると、<code>ALTER TABLE</code> が待機する。
<br>
実行中のロックを確認する方法を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 実行中のプロセスを確認
 SHOW PROCESSLIST;
 
 # InnoDBのトランザクション状態を確認
 SELECT * FROM information_schema.INNODB_TRX;
 
 # メタデータロックの待機を確認 (MySQL 8.0+)
 SELECT * FROM performance_schema.metadata_locks;
 </syntaxhighlight>
<br>
大規模テーブルのスキーマ変更には、以下の戦略が有効である。<br>
<br>
* INSTANT DDLの活用
*: MySQL 8.0以降では、可能な限りINSTANTで実行する。
*: <br>
* オンラインDDLツールの使用
*: pt-online-schema-change (Percona Toolkit) や gh-ost (GitHub) 等のツールを使用。
*: <br>
* レプリケーション環境での段階的実行
*: スレーブから順に実行し、最後にマスターを切り替える。
*: <br>
* ダウンタイムの確保
*: メンテナンス時間を設けて実行。
<br>
 <syntaxhighlight lang="bash">
 # pt-online-schema-changeの使用例
 pt-online-schema-change --alter "ADD COLUMN email VARCHAR(255)" \
    D=database_name,t=employees \
    --execute
 </syntaxhighlight>
<br>
==== 複合操作の最適化 ====
複数の操作を1つの <code>ALTER TABLE</code> 文にまとめることで、パフォーマンスが向上する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 非推奨 : 複数のALTER TABLE文を実行
 ALTER TABLE employees ADD COLUMN email VARCHAR(255);
 ALTER TABLE employees ADD COLUMN phone VARCHAR(20);
 ALTER TABLE employees ADD INDEX idx_email (email);
 # テーブル再構築が3回発生する可能性
 
 # 推奨 : 1つのALTER TABLE文にまとめる
 ALTER TABLE employees
    ADD COLUMN email VARCHAR(255),
    ADD COLUMN phone VARCHAR(20),
    ADD INDEX idx_email (email);
 # テーブル再構築が1回で済む
 </syntaxhighlight>
<br>
ただし、<code>INSTANT</code> 操作 と <code>COPY</code> 操作を混在させると、全体が <code>COPY</code> になる場合がある。<br>
<br>
 <syntaxhighlight lang="mysql">
 # INSTANT操作のみ (高速)
 ALTER TABLE employees
    ADD COLUMN email VARCHAR(255),
    ADD COLUMN phone VARCHAR(20);
 # 両方INSTANT、全体がINSTANT
 
 # INSTANT操作とCOPY操作を混在 (低速)
 ALTER TABLE employees
    ADD COLUMN email VARCHAR(255),  # INSTANT
    ENGINE=InnoDB;                  # COPY
 # 全体がCOPYになる
 </syntaxhighlight>
<br>
実行計画を確認するには、<code>EXPLAIN</code> は使用できないが、実行前にテストテーブルで試すことが推奨される。<br>
<br><br>

== 実行例と使用パターン ==
==== 基本的な使用例 ====
実際の使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 例1 : 新しいカラムを追加してインデックスを作成
 ALTER TABLE users
    ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ADD INDEX idx_created (created_at);
 
 # 例2 : カラムのデータ型を変更
 ALTER TABLE products
    MODIFY COLUMN price DECIMAL(12, 2) NOT NULL;
 
 # 例3 : 外部キー制約を追加
 ALTER TABLE orders
    ADD CONSTRAINT fk_user_id
       FOREIGN KEY (user_id) REFERENCES users(id)
       ON DELETE CASCADE;

 # 例4 : 主キーを変更
 ALTER TABLE employees
    DROP PRIMARY KEY,
    ADD PRIMARY KEY (employee_code);
 
 # 例5 : 文字セットを変換
 ALTER TABLE articles
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 </syntaxhighlight>
<br>
==== スキーマ変更のベストプラクティス ====
スキーマ変更を安全に実行するためのベストプラクティスを以下に示す。<br>
<br>
* 事前にバックアップを取得する
*: スキーマ変更前に必ずバックアップを取得。
*: <br>
* テスト環境で事前に実行する
*: 本番環境と同じデータ量で実行時間を測定。
*: <br>
* 実行時間を見積もる
*: COPYが必要な場合は、十分な時間を確保。
*: <br>
* ディスク容量を確認する
*: COPY方式では、テーブルサイズの2倍以上の空き容量が必要。
*: <br>
* メタデータロックに注意する
*: 長時間実行中のトランザクションがあると、<code>ALTER TABLE</code> が待機する。
*: <br>
* アプリケーションへの影響を考慮する
*: ロックによるダウンタイムや、レスポンス遅延の可能性。
<br>
 <syntaxhighlight lang="mysql">
 # 実行前のチェックリスト
 
 # 1. ディスク容量の確認
 SHOW TABLE STATUS LIKE 'employees';
 
 # 2. 実行中のトランザクションの確認
 SELECT * FROM information_schema.INNODB_TRX;
 
 # 3. テストテーブルで実行時間を測定
 CREATE TABLE test_employees LIKE employees;
 INSERT INTO test_employees SELECT * FROM employees LIMIT 10000;
 ALTER TABLE test_employees ADD COLUMN email VARCHAR(255);
 
 # 4. 本番実行
 ALTER TABLE employees
    ADD COLUMN email VARCHAR(255),
    ALGORITHM=INSTANT,
    LOCK=NONE;
 </syntaxhighlight>
<br><br>

== 関連ページ ==
* [[MySQL - テーブル]]
* [[MySQL - データ型]]
* [[MySQL - 外部キー]]
* [[MySQL - インデックス]]
* [[MySQL - UPDATE]]
* [[MySQL - DELETE]]
<br><br>


{{#seo:
|title={{PAGENAME}} : Exploring Electronics and SUSE Linux | MochiuWiki
|keywords=MochiuWiki,Mochiu,Wiki,Mochiu Wiki,Electric Circuit,Electric,pcb,Mathematics,AVR,TI,STMicro,AVR,ATmega,MSP430,STM,Arduino,Xilinx,FPGA,Verilog,HDL,PinePhone,Pine Phone,Raspberry,Raspberry Pi,C,C++,C#,Qt,Qml,MFC,Shell,Bash,Zsh,Fish,SUSE,SLE,Suse Enterprise,Suse Linux,openSUSE,open SUSE,Leap,Linux,uCLnux,電気回路,電子回路,基板,プリント基板
|description={{PAGENAME}} - 電子回路とSUSE Linuxに関する情報 | This page is {{PAGENAME}} in our wiki about electronic circuits and SUSE Linux
|image=/resources/assets/MochiuLogo_Single_Blue.png
}}

__FORCETOC__
[[カテゴリ:MySQL]]