MySQL DDLの書き方｜CREATE・ALTER・DROP TABLEと制約を解説

MySQLのDDL（Data Definition Language）は、database・table・column・index・constraintなど、dataを入れる器の構造を定義するSQLです。

CREATE TABLE products (...);
ALTER TABLE products ADD COLUMN description TEXT;
DROP TABLE products;

CREATEは作成、ALTERは変更、DROPは削除に使います。行dataを追加するINSERT、更新するUPDATE、削除するDELETEはDDLではなくDMLです。

この記事ではMySQL 8.4を基準に、databaseとtableの作成、data type、PRIMARY KEY・FOREIGN KEY・CHECK、ALTER TABLE、定義確認、暗黙COMMIT、online DDL、本番で安全に変更する手順まで解説します。

DDLとDMLの違い

最初にSQLの役割を分けます。

分類	対象	主なstatement
DDL	schema・table・column・constraint	CREATE、ALTER、DROP、TRUNCATE
DML	table内のrow data	SELECT、INSERT、UPDATE、DELETE
DCL	account・privilege	GRANT、REVOKE

SQL分類の基本はMySQLのSQL statementとquery、row操作はMySQL DMLの書き方へ分離しています。

作業前にMySQL versionと接続先を確認する

DDLはtable全体へ影響します。最初に接続先とversionを確認します。

SELECT VERSION();
SELECT DATABASE();
SELECT CURRENT_USER();

productionで直接試さず、localまたはstagingで同じMySQL major version・storage engine・data量に近い条件を用意します。破壊的変更の前にはschemaとdataのbackup、復元手順も確認してください。

CREATE DATABASEでdatabaseを作成する

database名、character set、collationを指定する例です。

CREATE DATABASE IF NOT EXISTS shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

utf8mb4は4byte UTF-8を扱えます。MySQL 8.4のserver defaultもutf8mb4ですが、migrationで明示すると環境差を減らせます。collationは比較・sort・UNIQUE判定へ影響するため、case・accent・kanaを区別する要件に合わせて選びます。

IF NOT EXISTSは既存databaseがある場合のerrorを避けるだけで、既存character setやcollationが期待どおりかは確認しません。

使用するdatabaseを選択する

USE shop;
SELECT DATABASE();

databaseを選ばずCREATE TABLE productsを実行すると、No database selectedになる場合があります。shop.productsのように完全修飾名を書けば、default databaseへ依存しません。

CREATE TABLEの基本構文

商品tableを作る実用的な例です。

CREATE TABLE shop.products (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  sku VARCHAR(64) NOT NULL,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) UNSIGNED NOT NULL,
  stock INT UNSIGNED NOT NULL DEFAULT 0,
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL
    DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_products_sku (sku),
  CONSTRAINT chk_products_price CHECK (price >= 0),
  CONSTRAINT chk_products_stock CHECK (stock >= 0)
) ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci
  COMMENT = '商品';

columnは「名前、data type、NULL可否、default、追加属性」の順に読むと理解しやすくなります。table末尾へPRIMARY KEY、UNIQUE、CHECK、FOREIGN KEYなどのtable constraintを書きます。

IF NOT EXISTSも使えますが、同名tableの定義が一致する保証はありません。migrationでは適用済みversionを管理し、SHOW CREATE TABLEで実定義を確認します。

よく使うdata type

dataの意味と範囲に合わせ、必要以上に大きな型を選ばないようにします。

type	用途	注意点
INT / BIGINT	整数、ID、件数	負数不要ならUNSIGNEDを検討
DECIMAL(p, s)	金額など正確な小数	FLOATよりdecimal計算向き
VARCHAR(n)	長さに上限がある文字列	nは文字数
TEXT	説明文・長文	default・indexに型別制約あり
DATE / DATETIME	日付・日時	timezone変換要件を決める
TIMESTAMP	日時	session timezone変換と範囲を確認
JSON	可変構造data	主要検索項目を何でもJSONへ入れない
BINARY / BLOB	binary data	画像本体はobject storageも比較

INT(8)は8桁制限ではない

旧来のINT(8)にある数字は整数の保存範囲や桁数を制限するものではありません。MySQL 8系では整数表示幅へ依存せず、範囲はTINYINT・SMALLINT・INT・BIGINTとSIGNED / UNSIGNEDで決めます。

郵便番号や電話番号のように計算しない値は、先頭0や記号を保持できる文字列として設計します。

NULL・DEFAULT・COMMENTを設定する

name VARCHAR(255) NOT NULL,
stock INT UNSIGNED NOT NULL DEFAULT 0,
description TEXT NULL COMMENT '商品説明'

• NOT NULL: 値が必須
• NULL: 値が不明・未設定である状態を許可
• DEFAULT: columnを省略したINSERTで使用する値
• COMMENT: schemaに残す説明

空文字、0、false、NULLは意味が異なります。application都合で何でもdefault 0にせず、domain上の意味を決めます。

PRIMARY KEYとAUTO_INCREMENT

PRIMARY KEYは各rowのidentityを表し、1tableにつき1つです。複数columnを組み合わせた複合主キーも作れます。

CREATE TABLE order_items (
  order_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  quantity INT UNSIGNED NOT NULL,
  PRIMARY KEY (order_id, product_id)
) ENGINE = InnoDB;

AUTO_INCREMENTはtableにつき1columnで、indexが必要です。一般的にはPRIMARY KEYと組み合わせます。

id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

開始候補を変更する構文は次のとおりです。

ALTER TABLE products AUTO_INCREMENT = 1000;

既存最大値以下へ設定しても、次の値が単純に小さく戻るとは限りません。欠番を埋める用途や、削除後にIDを再利用する設計には使わないでください。

UNIQUEとINDEXを作成する

UNIQUEは重複を防ぐconstraintを持つindexです。通常INDEXは検索・sort・join conditionを助けますが、重複自体は許可します。

UNIQUE KEY uq_products_sku (sku),
KEY idx_products_status_created (status, created_at)

複合indexはcolumn順が重要です。queryのWHERE・JOIN・ORDER BYとdata分布を確認し、使われないindexを増やしすぎないようにします。indexはreadを速くできる一方、INSERT・UPDATE・DELETEとstorageにcostがかかります。

CHECK制約で値の範囲を守る

MySQL 8.4ではCHECK constraintがenforceされます。

CONSTRAINT chk_products_price CHECK (price >= 0),
CONSTRAINT chk_products_status
  CHECK (status IN ('draft', 'active', 'archived'))

application側のvalidationも必要ですが、それだけではbatch、管理tool、別serviceから不正値が入る可能性があります。DB constraintはどの書き込み経路にも共通する最後の整合性ruleです。

旧MySQLではCHECK構文をparseしてもenforceしなかったversionがあるため、upgrade中の環境は実versionと既存constraintを確認してください。

FOREIGN KEYで参照整合性を保つ

外部キーはJOINを行うための機能ではなく、子tableの参照値が親tableに存在することを保証するconstraintです。JOIN自体は外部キーがなくても実行できます。

CREATE TABLE categories (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
) ENGINE = InnoDB;

ALTER TABLE products
  ADD COLUMN category_id BIGINT UNSIGNED NULL,
  ADD CONSTRAINT fk_products_category
    FOREIGN KEY (category_id)
    REFERENCES categories (id)
    ON UPDATE RESTRICT
    ON DELETE SET NULL;

• 子と親のcolumn type・size・符号を互換にする
• 参照先はPRIMARY KEYまたは適切なUNIQUE keyを使う
• 対応storage engineとindex要件を確認する
• SET NULLなら子columnでNULLを許可する
• CASCADEが大量rowへ連鎖する範囲を確認する

主なactionはRESTRICT / NO ACTION、CASCADE、SET NULLです。削除したcategoryの商品まで削除すべきか、削除を拒否すべきか、参照だけNULLへするかをbusiness ruleから選びます。

table定義を確認する

作成後はSQLが成功したことだけでなく、serverが保存した実定義を確認します。

SHOW CREATE TABLE shop.products;
SHOW FULL COLUMNS FROM shop.products;
SHOW INDEX FROM shop.products;
SHOW TABLE STATUS FROM shop LIKE 'products';

• SHOW CREATE TABLE: constraint・engine・charsetを含む再現SQL
• SHOW FULL COLUMNS: type・NULL・default・comment・extra
• SHOW INDEX: index名・column順・unique性
• SHOW TABLE STATUS: engine・row情報・collation

GUI表示だけでなくSQLで確認すると、migration reviewや複数環境の比較に使えます。

ALTER TABLEでcolumnを変更する

columnを追加する

ALTER TABLE products
  ADD COLUMN description TEXT NULL AFTER name;

FIRSTやAFTERで表示順を指定できますが、applicationはcolumn順ではなくcolumn名へ依存させます。位置変更のためだけにlarge tableをrebuildしない判断も必要です。

column名を変更する

ALTER TABLE products
  RENAME COLUMN stock TO stock_quantity;

application codeを同時に切り替えると停止時間が生じます。後方互換が必要なら、新column追加、両方へ書き込み、data移行、read切替、旧column削除のexpand-contractを検討します。

type・NULL・defaultを変更する

ALTER TABLE products
  MODIFY COLUMN name VARCHAR(300) NOT NULL;

MODIFY COLUMNでは新しい完全なcolumn定義を書きます。NOT NULLやdefaultを書き忘れて意図せず失わないよう、先にSHOW CREATE TABLEを保存します。型を狭める変更は既存dataが収まるか事前にqueryで確認してください。

columnを削除する

ALTER TABLE products
  DROP COLUMN description;

DROP COLUMNはdataも削除します。先にapplicationから参照を外し、一定期間を置いてからschemaを削除する二段階変更が安全です。

indexとconstraintを追加・削除する

-- index追加
ALTER TABLE products
  ADD INDEX idx_products_status_created (status, created_at);

-- index削除
ALTER TABLE products
  DROP INDEX idx_products_status_created;

-- CHECK追加
ALTER TABLE products
  ADD CONSTRAINT chk_products_stock CHECK (stock_quantity >= 0);

-- CHECK削除
ALTER TABLE products
  DROP CHECK chk_products_stock;

-- FOREIGN KEY削除
ALTER TABLE products
  DROP FOREIGN KEY fk_products_category;

外部キーを削除しても、子側に自動作成されたindexが別に残る場合があります。SHOW CREATE TABLEとSHOW INDEXの両方を確認します。

RENAME TABLE・TRUNCATE・DROPの違い

statement	結果	主な用途
RENAME TABLE	table名を変更	切替・退避
TRUNCATE TABLE	全rowを削除しtableは残す	検証dataの初期化など
DROP TABLE	定義と全dataを削除	table廃止

RENAME TABLE products TO products_archive;
TRUNCATE TABLE work_items;
DROP TABLE IF EXISTS products_archive;

DROP TABLEはtriggerも含めてtableを削除し、通常は暗黙COMMITを起こします。IF EXISTSは名前間違いによるerrorを隠すこともあるため、production migrationでは対象名と件数をreviewします。

database全体の削除はさらに影響が大きくなります。

DROP DATABASE IF EXISTS shop;

database内の全tableとdataを削除します。local test環境以外で安易に実行しないでください。

DDLとtransaction・暗黙COMMIT

MySQLの多くのDDLは、実行前後に暗黙COMMITを起こします。

START TRANSACTION;
UPDATE products SET status = 'archived' WHERE id = 10;
ALTER TABLE products ADD COLUMN note TEXT;
ROLLBACK;

この例で「UPDATEもALTERもROLLBACKされる」と考えてはいけません。DDLがactive transactionを暗黙にcommitするため、DMLとDDLを1つのrollback可能な作業として混ぜないようにします。

MySQL 8のatomic DDLは、対応する1つのDDL statementについてdata dictionary・storage engine・binary logの更新をまとめて成功または失敗させる仕組みです。利用者が複数DDLを自由にROLLBACKできるtransactional DDLという意味ではありません。

online DDLとmetadata lock

InnoDBのALTER TABLEはoperationによってALGORITHMが異なります。

algorithm	概要	影響
INSTANT	主にmetadataだけを変更	最も軽いが対応operationに制限
INPLACE	temporary table copyを避ける	rebuildや一時lockが必要な場合あり
COPY	新tableへdataをcopy	時間・disk・lock影響が大きい

MySQL 8.4では対応するcolumn追加などでINSTANTがdefaultになりますが、すべてのALTERが瞬時に終わるわけではありません。明示して非対応なら失敗させる例です。

ALTER TABLE products
  ADD COLUMN note VARCHAR(255) NULL,
  ALGORITHM = INSTANT;

LOCK = NONEを指定できるoperationもありますが、DDL開始・終了時などにmetadata lockが必要です。長いtransactionがtableを参照しているとALTERが待ち、その待機が後続queryへ波及する場合があります。onlineという名前を「無停止保証」と解釈しないでください。

lockとtransactionの詳細はMySQLのlockとdeadlockへ分離しています。

本番で安全にschemaを変更する手順

1. 目的・対象table・expected schemaをmigration fileへ書く
2. 現在のSHOW CREATE TABLEとMySQL versionを保存する
3. backupを作成し、復元できることを確認する
4. stagingで実data量に近い条件と実行時間を測る
5. applicationとの後方互換性を確認する
6. ALGORITHM・lock・disk使用量・replication lagを見積もる
7. trafficが低い時間帯と中止条件を決める
8. 実行中のprocess・metadata lock・error logを監視する
9. 実行後にschema、row count、主要query、applicationを確認する

column削除やrenameは、application deployと同時に一気に行うより、追加して両対応、data移行、read切替、旧定義削除というexpand-contractが安全です。

tableの分割やcolumn設計自体はdatabase正規化とMySQLの実践的なtable構成で先に検討してください。

DDLでよくあるerror

No database selected

USE database_nameを実行するか、database_name.table_nameで完全修飾します。

Table already exists

同名tableがあります。単にIF NOT EXISTSで隠す前に、SHOW CREATE TABLEで期待した定義か確認します。

You have an error in your SQL syntax

comma、parentheses、quote、reserved word、statement末尾、MySQL versionを確認します。複数statementを一度に実行せず、最小単位へ分けると場所を特定しやすくなります。

Cannot add foreign key constraint

親子columnのtype・UNSIGNED、index、storage engine、既存orphan row、constraint名を確認します。SHOW CREATE TABLEで実定義を比較してください。

Data truncated・Data too long

既存dataが変更後のtype・length・CHECK・NOT NULLに収まりません。ALTER前に対象rowをSELECTして修正方針を決めます。

ALTER TABLEが終わらない

table size、algorithm、copy・rebuild、metadata lock待ち、長いtransactionを確認します。焦ってsessionをkillする前に、現在phaseとrollback costを確認してください。

DDL実装チェックリスト

• DDLとDMLを分けた
• MySQL version・接続先・default databaseを確認した
• character setとcollationを決めた
• INTの表示幅を桁数制限として使っていない
• PRIMARY KEY・UNIQUE・CHECK・FOREIGN KEYの役割を分けた
• SHOW CREATE TABLEで実定義を確認した
• DDLの暗黙COMMITを考慮した
• ALTERのalgorithm・lock・table sizeを確認した
• 破壊的変更前にbackupと復元手順を確認した
• applicationとの後方互換性とdeploy順を確認した
• staging検証・監視・中止条件を用意した

まとめ

MySQLのDDLは、CREATEでschema objectを作り、ALTERで変更し、DROPで削除します。INSERT・UPDATE・DELETEはrow dataを扱うDMLなので分けて考えます。

構文だけでなく、data type、key・constraint、character set、暗黙COMMIT、online DDL、metadata lockを理解することが重要です。特にproductionのALTER・DROPは、backup、migration管理、後方互換deploy、実行時間とlock監視まで含めて1つの変更として設計してください。