新規作成と、既存表の確認・複製を分ける

CREATE TABLEは指定先に新しい表を作るDDLです。ここではMySQL8.4.13/MariaDB11.8.9の新規隔離検体で試した例を示します。既存のusers等へ試す手順ではありません。cwiki_w24は検体用の新規DBで、同名表が存在しないことを確認しています。既存DBや本番へこの教材をそのまま実行しません。

1. 意図した型と属性で作成する

-- New isolated cwiki_w24 database only. The name must not exist already.
CREATE TABLE cwiki_w24.items (
    id INT UNSIGNED NOT NULL PRIMARY KEY,
    code VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
        NOT NULL DEFAULT 'new' COMMENT 'public label',
    flag BOOLEAN NOT NULL DEFAULT FALSE,
    payload JSON,
    KEY idx_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

idは整数の主キー、codeは20文字/NOT NULL/既定値/コメント/照合順序を持ち、索引を別に定義しています。BOOLEANを使うだけでは値の0/1制限を付けていません。JSON列も、MySQLとMariaDBの格納表現が同じという意味ではありません。実際の要件に必要なCHECK等は別に設計します。

2. 作った定義を読み返す

SHOW CREATE TABLE cwiki_w24.items;
SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT,
       CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'cwiki_w24' AND TABLE_NAME = 'items'
ORDER BY ORDINAL_POSITION;

期待した列順、型/NULL/既定値/コメントと、SHOW CREATE TABLEの制約を照合します。COLUMN_DEFAULTの表示に製品差があるため、表示文字列だけを他製品へ移植しません。検体ではMariaDBのpayloadはlongtext、MySQLはjsonと表示されました。無効JSONは両方で拒否されましたが、全JSON関数/キー重複/索引の同一性を試験したものではありません。

3. IF NOT EXISTSは既存定義を一致させない

-- Existing attributes and rows are not replaced by this different definition.
CREATE TABLE IF NOT EXISTS cwiki_w24.items (unrelated INT);
SHOW WARNINGS;

既存itemsとは違う定義を指定したこの検査は、両製品で1050の警告になり、元の4列と2行は不変でした。成功終了だけで新しい定義を適用できたと思わず、警告と実際の定義を確認します。既存表を更新したい場合の代わりにDROP/CREATEを追加しないでください。

4. LIKEとAS SELECTはバックアップの同義語ではない

-- Two different destinations, not a complete backup procedure.
CREATE TABLE cwiki_w24.items_like LIKE cwiki_w24.items;
CREATE TABLE cwiki_w24.items_selected AS
    SELECT id, code FROM cwiki_w24.items;

検体のLIKE先は0行で、主キーとidx_codeがありました。SELECT先には2行が入り、索引はありませんでした。LIKEでも外部キーは複製されず、SELECT先もすべての制約/属性を丸ごと引き継ぐ完全コピーではありません。データの同時更新、trigger/view、権限等も含む復旧可能性を、この2文だけで保証しません。

5. 外部キーは参照する列の型まで合わせる

-- Matching integer size and signedness on both sides.
CREATE TABLE cwiki_w24.parents (id INT UNSIGNED NOT NULL PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE cwiki_w24.children (
    id INT UNSIGNED NOT NULL PRIMARY KEY,
    parent_id INT UNSIGNED NOT NULL,
    CONSTRAINT fk_w24_parent FOREIGN KEY (parent_id)
        REFERENCES cwiki_w24.parents(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

この新例では親のidと子のparent_idを同じINT UNSIGNEDにそろえています。検体では参照されている親行のDELETEは1451で拒否されました。RESTRICTはCASCADEと違い、子行を自動削除する設計ではありません。実データの保持要件に合わせて参照アクションを選びます。

後の旧users例はidがINT、旧orders例は参照列user_idがBIGINTなので、その2例を続けて実行すると外部キーを作れません。検体でも不一致をMySQL3780/MariaDB1005で拒否しました。説明用の別例と、一つの整合したschemaを区別してください。FOREIGN_KEY_CHECKSを無効化して通す対処はしません。

文字列・日時・真偽値の選び方

CHAR(n)のnは文字数で、n byte固定ではありません。VARCHARも255文字を超えたら必ずTEXTという境界ではなく、文字コード・行サイズ・索引・クエリ・最大長を含めて選びます。金額のDECIMALも桁数/小数点以下/丸め/範囲を業務側で設計します。

TIMESTAMPは接続のタイムゾーンとの変換がありますが、元のタイムゾーン識別子を列値と一緒に保持する型ではありません。時刻範囲やstorageは製品/版/精度で異なります。MySQL8.4.13では2040年の検体値を拒否、MariaDB11.8.9ではUTC条件で保持しました。長期保存だから型を一律変更するのではなく、アプリの時刻仕様と移行条件を確認します。

元の複合主キー・UNSIGNEDの説明も注意を補って残しています。列追加や型変更は定義変更、名前だけの変更はカラム名の変更へ進んでください。

旧例の注意：各構文を続けて実行する要約ではありません。新規作成/既存確認/複製は別で、実表へ試さず定義/利用先を確認します。

この記事の要点

基本構文: CREATE TABLE table_name (col1 type1, col2 type2, ...) 主要型: INT / BIGINT / VARCHAR(n) / TEXT / DATE / DATETIME / DECIMAL(p,s) / BOOLEAN / JSON 制約: PRIMARY KEY / AUTO_INCREMENT / NOT NULL / DEFAULT / UNIQUE / FOREIGN KEY 外部キー: ON DELETE CASCADE / SET NULL / RESTRICT MySQL では基本 ENGINE = InnoDB + CHARSET = utf8mb4 CREATE TABLE IF NOT EXISTS / CREATE TABLE ... LIKE / CREATE TABLE ... AS SELECT

基本構文

旧例の注意：単独のusers例です。後のordersは参照列の整数幅が合わず、このまま組み合わせてFKは作れません。既存表や広い属性変更へ流用しません。

CREATE TABLE users (
    id          INT          AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(255) NOT NULL UNIQUE,
    age         TINYINT UNSIGNED,
    profile     TEXT,
    is_active   BOOLEAN      NOT NULL DEFAULT TRUE,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

主要なカラム型

旧例の注意：符号/charset/小数秒精度/製品版を別に確認します。VARCHARのrow/byte限度と文字数を混同しません。

型	サイズ・範囲	用途
TINYINT	1B / -128〜127	状態フラグ
INT	4B / 約 ±21 億	一般的な ID / 数量
BIGINT	8B / 約 ±9.2×10^18	大規模 ID / Unix epoch ms
DECIMAL(p,s)	固定小数	金額 (DECIMAL(10,2) = 整数 8 桁 + 小数 2 桁)
FLOAT / DOUBLE	浮動小数	科学計算 (金額には使わない)
CHAR(n)	n文字。byte数は文字コード等に依存	長さが揃う文字列 (郵便番号など)
VARCHAR(n)	最大 n 文字 (≤65535B)	名前 / メール / 短文テキスト
TEXT	最大 64KB	長文 (ブログ本文)
MEDIUMTEXT	最大 16MB	記事本文
LONGTEXT	最大 4GB	ログ / 大量テキスト
DATE	3B	誕生日
DATETIME	MySQL5.6.4以降は5B+小数秒分。製品/版/formatを確認	イベント時刻 (TZ 持たない)
TIMESTAMP	版/小数秒精度と時刻範囲を確認。MariaDB11.5以降は上限が異なる	時刻変換あり。元のTZ識別子を保持する型ではない
BOOLEAN (= TINYINT(1))	1B	真偽値用途のalias。0/1以外を禁止する制約ではない
JSON	製品別	MySQLのJSONとMariaDBのLONGTEXT系aliasを区別
ENUM('a','b')	1〜2B	固定列挙

主要な制約

旧例の注意：旧usersのINTとこのuser_id BIGINTは不整合で、そのまま作成できません。CHECKのMySQL対応も8.0.16以降等の条件を確認し、CASCADEを一般的な安全策にしません。

CREATE TABLE orders (
    -- 主キー (自動採番)
    id BIGINT AUTO_INCREMENT PRIMARY KEY,

    -- NOT NULL
    user_id BIGINT NOT NULL,

    -- DEFAULT
    status VARCHAR(20) NOT NULL DEFAULT 'pending',

    -- CHECK (MySQL 8.0+ / PostgreSQL)
    amount DECIMAL(10,2) NOT NULL CHECK (amount >= 0),

    -- UNIQUE
    order_no VARCHAR(50) NOT NULL UNIQUE,

    -- 複数列 UNIQUE
    UNIQUE KEY uq_user_order (user_id, order_no),

    -- インデックス
    KEY idx_user_created (user_id, created_at),

    -- 外部キー
    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB CHARSET = utf8mb4;

FOREIGN KEY の参照アクション

旧例の注意：InnoDB等の対象engineを確認します。CASCADEは子行にも作用するため、データ保持要件から選びます。

アクション	親が削除/更新されたら子は
CASCADE	連動して削除/更新
SET NULL	外部キー列に NULL を入れる (NOT NULL では使えない)
SET DEFAULT	DEFAULT 値にする (InnoDB は非対応)
RESTRICT (= NO ACTION)	子があるなら親の操作を拒否 (デフォルト)

テーブル作成の便利構文

旧例の注意：IF NOT EXISTS内の省略はSQL断片です。LIKEはデータ/FK等を丸ごと複製せず、AS SELECTも完全backupではありません。TEMPORARYは作成する接続と寿命を確認します。

-- 既にあれば作らない
CREATE TABLE IF NOT EXISTS users ( ... );

-- 既存テーブルと同じ構造を作る (データは入らない)
CREATE TABLE users_backup LIKE users;

-- 既存データから新テーブルを作る (制約は引き継がない)
CREATE TABLE users_old AS
SELECT * FROM users WHERE created_at < '2020-01-01';

-- 一時テーブル (接続切れたら自動削除)
CREATE TEMPORARY TABLE tmp_sum AS
SELECT user_id, SUM(amount) AS total
FROM orders GROUP BY user_id;

ENGINE と CHARSET の意味

旧例の注意：engine/charset/collationは対象版と比較要件から選びます。名称だけで他製品互換や日本語の最適解とは言えません。

項目	推奨	理由
ENGINE	InnoDB	トランザクション / 外部キー / 行ロック対応。MySQL 5.5+ で既定
ENGINE	MyISAM (旧)	テーブルロック・トランザクション無し。新規利用非推奨
CHARSET	utf8mb4	4 バイト UTF-8 = 絵文字対応。utf8 は 3 バイトで絵文字非対応
COLLATE	utf8mb4_unicode_ci or utf8mb4_0900_ai_ci	比較規則/版/一意性/要件で選ぶ。日本語の有無だけで一律推奨しない

命名のコツ

• テーブル名は複数形・スネークケース (users / order_items)
• 外部キー名は fk_子テーブル_親テーブル (fk_orders_user)
• UNIQUE は uq_テーブル_カラム
• インデックスは idx_テーブル_カラム
• 主キーの型/幅/自然キーや複合キーは件数見積り/参照/索引等から設計し、BIGINT固定を必須にしない

変更系コマンド

旧例の注意：削除/型変更/DROPを含む旧比較例で、既存usersへ順次実行しません。属性省略やFK依存、DDLの取消不可も確認します。

-- カラム追加
ALTER TABLE users ADD COLUMN nickname VARCHAR(50);

-- カラム削除
ALTER TABLE users DROP COLUMN nickname;

-- カラム名・型変更 (MySQL)
ALTER TABLE users CHANGE name full_name VARCHAR(200) NOT NULL;
-- 型のみ変更
ALTER TABLE users MODIFY email VARCHAR(320);

-- インデックス追加
CREATE INDEX idx_email ON users (email);

-- テーブル削除
DROP TABLE users;
DROP TABLE IF EXISTS users;

FAQ

Q: VARCHAR と TEXT どちらを使えば？
A: 255文字を固定境界にせず、charset/行サイズ/索引/クエリ/最大長から選びます。型名だけでソートが速いとは保証しません。

Q: DATETIME と TIMESTAMP の違い
A: TIMESTAMPの接続TZ変換と、元のTZ識別子の保持は別です。範囲/小数秒精度/版を確認し、MySQLの2038上限をMariaDB全版へ一般化しません。DATETIMEとの選定もアプリの時刻仕様に合わせます。

Q: 主キーを UUID にしたい
A: CHAR(36) (文字列) または BINARY(16) (バイナリ)。MySQL 8 なら関数 UUID_TO_BIN() / BIN_TO_UUID() あり。インデックス効率は連番より落ちる点に注意。

元のMariaDB構文と複合主キーの説明

MariaDBのテーブル作成のSQLです。

元例の注意：日本語/角括弧/省略は置換箇所です。実行用SQLではありません。

【構文】　※ []は任意項目

CREATE TABLE テーブル名(
    カラム名 データ型 [制約など]
    ,カラム名 データ型 [制約など]
    ,・・・
    [,制約など]
);

 

元例の注意：TEST_TABLEは説明名です。既存の同名表へ実行せず、後の2つの複合主キー比較例も同じ表へ順次作る手順ではありません。

【例】

CREATE TABLE TEST_TABLE(
    id VARCHAR(9),
    subId VARCHAR(9),
    name VARCHAR(255) NOT NULL,
    deleteFlag INT,
    PRIMARY KEY(id, subId)
);

 

【制約の種類】

    PRIMARY KEY：主キーを指定する。
    UNIQUE：対象列を一意にする。
    NOT NULL：NULLを許容しない。
    DEFAULT ‘値’：INSERT時に値が指定されなかった場合に入る値を定義する。
    AUTO_INCREMENT：自動採番を行う列。increment/offset等の条件があり、連続した+1や欠番なしを保証しない。
    
【注意点】

    制約はテーブル作成の際、列毎に定義することができるが、主キー制約など複数カラムに対して制約をかける場合も多々あるので、基本的に複数カラムが制約の対象のなりうるものは列定義が全て終わった後に制約の定義をするようにした方が良い。

    例１（カラム毎に定義 = 複数カラムに定義できない）：
        CREATE TABLE TEST_TABLE(
    id VARCHAR(9) PRIMARY KEY,
    subId VARCHAR(9)
);

    例２（最後にまとめて定義 = 複数カラムに定義できる）：
        CREATE TABLE TEST_TABLE(
    id VARCHAR(9),
    subId VARCHAR(9),
    PRIMARY KEY(id, subId)
);

【UNSIGNED】

整数型のUNSIGNEDは負数を含まない範囲にする指定です。同じ幅の符号付き整数より正の上限が広がりますが、全数値型に同じ倍増規則があるわけではありません。外部キーの参照列との型/符号も照合します。

確認範囲と関連ページ

2026年10月26日確認。新12SQLを新規tmpfs MySQL8.4.13/MariaDB11.8.9-ubu2404で実行し各15/計30試験に成功。既存表/複製、列の属性/値、strictな縮小とNULL拒否、名前変更とview/索引、DEFAULT、metadata lock待ち、型/日時の製品差と外部キーを検査しました。本番schema/データ/設定/利用側の移行はしていません。既存のvendor/依存を変更せず、Laravelのmigrate/外部オンライン変更ツール、他DB製品の旧全SQL、全型/照合順序/依存/巨大負荷・無停止や完全復旧を実行保証するものではありません。新図は900/360px静止画で確認し、Work設定でブラウザ無効のため実ブラウザ/実端末は未検証です。

テーブル関連の親ページから子ページを選択できます。テーブル作成、カラム定義変更、カラム名変更、追加/削除、外部キー、製品配下のSQL、MySQL/MariaDBも参照します。

一次資料：MySQL ALTERの属性と依存、MariaDB ALTER、MySQL LIKE、MySQL SELECTでの作成、Online DDLとmetadata lock、操作別のOnline DDL、MySQLの格納容量、MariaDB TIMESTAMPの版別範囲、MariaDB JSON、SQL Serverの列名変更、PostgreSQL ALTER、Laravel13の列変更、Laravel10.x公式文書の旧DB条件、pt-online-schema-changeの動作とdry-run、gh-ostの要件/制約。