テーブルを作ってデータを入れる — CREATE TABLE・AUTO_INCREMENT・INSERT

MySQLとは何かでサーバーに接続できるようになりました。 ここからは、実際にテーブルを作ってデータを出し入れします。

学習者

CREATE TABLE って標準SQLですよね。MySQLで特別に覚えることってあるんですか？

あります。しかも、自分が書いていないものが勝手に足されるという形で現れます。まずそれを見てみましょう。

書いたものと、できたもの

次のテーブルを作ります。

CREATE TABLE users (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  email      VARCHAR(255) NOT NULL UNIQUE,
  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
);

作ったあと、MySQLが実際にどう解釈したかを SHOW CREATE TABLE で確認できます。

SHOW CREATE TABLE users\G

Create Table: CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(255) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `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 `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

書いた通りになっていない箇所が3つあります。

書けるけれど、そういう型があるわけではない——という点だけ覚えておけば実用上は困りません。WHERE is_active = TRUE も WHERE is_active = 1 も同じように動きます。

先生

SHOW CREATE TABLE は「自分の書いたDDLをMySQLがどう受け取ったか」を答え合わせできるコマンド。作った直後に一度は打つ癖をつけるといいよ。

末尾の ENGINE と CHARSET は、指定しなかったので既定値が補われたものです。それぞれInnoDBと文字コードの章で扱います。

AUTO_INCREMENT

AUTO_INCREMENT は、値を省略したときにMySQLが連番を振ってくれる指定です。主キーとセットで使うのが定番です。

INSERT INTO users (name, email) VALUES ('田中', 'tanaka@example.com');

id を書いていませんが、自動で 1 が入ります。

ここで実際の挙動を見てください。3件入れたあと、UNIQUE に違反する行を1件失敗させ、そのあともう1件入れます。

INSERT INTO t (name) VALUES ('a'), ('b'), ('c');
INSERT IGNORE INTO t (name) VALUES ('a');   -- UNIQUE違反で失敗
INSERT INTO t (name) VALUES ('d');
SELECT * FROM t;

+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  5 | d    |
+----+------+

4番が飛んでいます。 失敗したINSERTが採番だけ済ませてしまったためです。

採番したIDを受け取る

アプリケーションからINSERTしたあと、「いま挿入された行のID」が欲しくなります。

構文： LAST_INSERT_ID()

戻り値： 直前のINSERTで採番された AUTO_INCREMENT の値（同じ接続の中でのみ有効）

ここに引っかかりやすい仕様があります。複数行をまとめてINSERTした場合、返るのは最初の行のIDです。

INSERT INTO t (name) VALUES ('a'), ('b'), ('c');
SELECT LAST_INSERT_ID();

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+

3件入れて最後は 3 なのに、返るのは 1 です。名前から想像する挙動と逆なので注意してください。

この「接続ごと」という性質は、コネクションプールを使うときに効いてきます。INSERTと LAST_INSERT_ID() が別々の接続で実行されると、まったく無関係な値が返ります。

created_at と updated_at

MySQLには、更新日時を自動で書き換える指定があります。

updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

created_at には前者だけ、updated_at には両方を付ける、というのが定番の組み合わせです。アプリケーション側で日時をセットする必要がなくなります。

テーブルを確認する

作ったテーブルを調べるコマンドは3つ覚えれば十分です。

DESCRIBE は普段の確認用、SHOW CREATE TABLE は「本当のところどうなっているか」を見たいときに使います。DESC users; と省略しても同じです。

データを入れる

INSERTには2つの書き方があります。

-- 1行ずつ
INSERT INTO users (name, email) VALUES ('田中', 'tanaka@example.com');
 
-- まとめて
INSERT INTO users (name, email) VALUES
  ('鈴木', 'suzuki@example.com'),
  ('佐藤', 'sato@example.com'),
  ('高橋', 'takahashi@example.com');

件数が多いときは、まとめて書くほうが圧倒的に速くなります。1行ずつのINSERTは、その都度クライアントとサーバーの往復が発生するためです。

型に合わない値は「エラーになる」

現在のMySQLは、既定で厳格モード（STRICT_TRANS_TABLES）が有効です。

CREATE TABLE t (code VARCHAR(3), qty INT NOT NULL);
INSERT INTO t (code, qty) VALUES ('ABCDE', 1);

ERROR 1406 (22001): Data too long for column 'code' at row 1

VARCHAR(3) に5文字を入れようとして、エラーで拒否されました。

データを更新する・削除する

UPDATE と DELETE は、文法そのものは標準SQLと同じです。

UPDATE users SET is_active = FALSE WHERE id = 3;
DELETE FROM users WHERE id = 3;

危険なのは WHERE を書き忘れたときです。

UPDATE users SET is_active = FALSE;  -- 全行が更新される

学習者

さすがに全行更新は止めてくれますよね？

止まりません。既定では通ります。

SHOW VARIABLES LIKE 'sql_safe_updates';

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_safe_updates | OFF   |
+------------------+-------+

sql_safe_updates を ON にすると、主キーやインデックスを使わない UPDATE / DELETE が拒否されるようになります。本番のデータベースに手作業で繋ぐときは、有効にしておくと事故を1段階防げます。

SET sql_safe_updates = 1;

DELETE と TRUNCATE の違い

全行消す方法は2つあり、結果が違います。

-- どちらも中身は空になる
DELETE FROM t;
TRUNCATE TABLE t;

空にしたあと、もう1行INSERTしてIDを見ると差が出ます。

テスト用のデータを作り直すときは TRUNCATE、業務データを条件付きで消すときは DELETE、という使い分けで困りません。

よくあるハマりどころ

IDの最大値を件数として使う

-- 危ない
SELECT MAX(id) FROM users;

欠番があるため、これは件数になりません。削除された行があればさらにずれます。件数は COUNT(*) を使ってください。

複数行INSERTのあとにLAST_INSERT_ID()を使う

上で見た通り、返るのは最初の行のIDです。まとめてINSERTした全行のIDが欲しい場合は、1行ずつINSERTするか、採番を自分で管理する設計に変える必要があります。

削除フラグとUNIQUEを併用する

is_deleted のような論理削除フラグを使いつつ email に UNIQUE を付けると、同じメールアドレスで再登録できなくなります。削除済みの行が制約を占有し続けるためです。

論理削除を採用するなら、UNIQUE制約をどう扱うかを設計時に決めておく必要があります。

TRUNCATE をトランザクションで囲む

BEGIN;
TRUNCATE TABLE orders;  -- ここで暗黙にコミットされる
ROLLBACK;               -- 戻らない

TRUNCATE はDDLとして扱われるため、暗黙のコミットが発生します。囲んでも意味がありません。

ちゃんと使うためのポイント

• 作った直後に SHOW CREATE TABLE で答え合わせをする
• MySQLに真偽値型はない。BOOLEAN は tinyint(1) の別名
• AUTO_INCREMENT は一意性のみ保証。欠番は正常に発生する
• LAST_INSERT_ID() は複数行INSERTでは最初の行のIDを返す
• updated_at には ON UPDATE CURRENT_TIMESTAMP を付けると自動更新される
• 既定で厳格モードが有効なので、型に合わない値はエラーになる
• UPDATE / DELETE は WHERE 忘れが止められない。先に SELECT で確認する
• TRUNCATE は速いがロールバックできず、AUTO_INCREMENT もリセットされる

作成した列にどの型を割り当てるべきかは、データ型の選び方で掘り下げます。INT と BIGINT、DECIMAL と DOUBLE、DATETIME と TIMESTAMP の違いを、実務での判断基準から整理します。

ここまでは mysql コマンドから手で書き込んできました。アプリケーションから同じ操作をするときは、Node.jsからMySQLに接続するで扱うコネクションプールが必要になります。

参考リンク

• MySQL 8.4 Reference Manual — CREATE TABLE Statement
• MySQL 8.4 Reference Manual — Using AUTO_INCREMENT
• MySQL 8.4 Reference Manual — Server SQL Modes