はじめに
MySQL への接続
MySQL の標準 CLI が mysql クライアントです。パスワードがシェルの履歴に見えるのではなく安全にプロンプトされるよう、必ず -p を(後ろにスペースを空けず)使いましょう。-h がホストを、-P(大文字)がポートを設定します。接続状態の表示は \s です。スクリプトで 1 回限りのクエリを実行するには -e を。
# connect to local server (prompt for password)
mysql -u root -p
# connect to a remote host on a custom port
mysql -h 192.168.1.100 -P 3307 -u admin -p
# connect directly to a specific database
mysql -u root -p mydb
# non-interactive: run a query and exit
mysql -u root -p -e "SELECT VERSION();"
# show server status inside the client
\s
SELECT VERSION(), CURRENT_USER, DATABASE();データベースの管理
絵文字を含む完全な Unicode をサポートするには utf8mb4(utf8 ではない)を使いましょう — MySQL の utf8 は、すべての文字を保存できない 3 バイトの部分集合です。正しいソートのための推奨照合順序が utf8mb4_unicode_ci です。スクリプトでのエラー防止が IF EXISTS/IF NOT EXISTS です。DROP DATABASE がすべてのテーブルとデータを即座に取り除きます。
# list all databases
SHOW DATABASES;
# create a database with charset and collation
CREATE DATABASE mydb
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
# switch to a database
USE mydb;
# show the current database
SELECT DATABASE();
# drop a database (irreversible)
DROP DATABASE IF EXISTS mydb;
# alter a database's charset
ALTER DATABASE mydb CHARACTER SET utf8mb4;オブジェクトの表示と記述
SHOW コマンドが MySQL 固有のインスペクションツールです。素早い列の概観が DESCRIBE で、再利用できる完全な DDL を与えるのが SHOW CREATE TABLE です。縦の出力のためには ; の代わりに \G を追記しましょう — SHOW CREATE TABLE のような幅広の行では、はるかに読みやすいです。
# list tables in the current database
SHOW TABLES;
# show table structure (columns, types, keys)
DESCRIBE users;
# equivalent
SHOW COLUMNS FROM users;
# show create statement for a table
SHOW CREATE TABLE users\G
# list indexes on a table
SHOW INDEX FROM users;
# show stored procedures / functions
SHOW PROCEDURE STATUS WHERE Db = 'mydb';
SHOW FUNCTION STATUS WHERE Db = 'mydb';
# list triggers
SHOW TRIGGERS\Gサーバーのステータスと変数
MySQL の変数には、SESSION(現在の接続)と GLOBAL のスコープがあります。SET GLOBAL が実行中のサーバーを変えますが再起動でリセットされるため、設定は my.cnf で永続化しましょう。値の読み取りは @@var です。ランタイムのカウンタ(接続、稼働時間、スループット)の公開が SHOW STATUS で、モニタリングに有用です。
# view a system variable
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'version%';
# view session vs global variable
SELECT @@session.sql_mode, @@global.sql_mode;
# set a variable for the current session
SET sql_mode = 'STRICT_TRANS_TABLES';
SET @@session.foreign_key_checks = 0;
# set globally (needs SUPER/privilege, persists until restart)
SET GLOBAL max_connections = 200;
# show server status counters
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Uptime';コメントとステートメントの基本
MySQL は 3 つのコメントスタイルをサポートします:--(末尾スペースが必要)、/* */ ブロック、行末までの # です。/*! ... */ が特別な実行可能コメントで — 内側のコードが MySQL 上でのみ実行され、他の SQL エンジンは無視します 。ポータブルなスキーマファイルに有用です。結果の縦表示での実行が \G です。
-- single-line comment (note the space after --)
SELECT 1; -- inline comment
/* multi-line
comment block */
SELECT 2;
# hash-style comment (MySQL-specific, to end of line)
SELECT 3;
# MySQL executable comments: run only on MySQL
SELECT 1 /*!50100 , 2 */; /* the part runs on MySQL >= 5.1 */
# statements end with semicolon; \G ends and prints vertically
SELECT * FROM users\G設定ファイル(my.cnf)
my.cnf(Linux)/ my.ini(Windows)が、セクションで整理された永続的なサーバーとクライアントの設定を保持します:サーバーは [mysqld]、クライアントは [client]/[mysql] です。InnoDB の最も重要なチューニングのノブが innodb_buffer_pool_size です(通常、RAM の 50〜75%)。編集後はサーバーを再起動しましょう。検証には mysql --help か SHOW VARIABLES を。
# /etc/my.cnf or ~/.my.cnf (Linux/macOS)
# C:\ProgramData\MySQL\MySQL Server 8.0\my.ini (Windows)
[mysqld]
port = 3306
datadir = /var/lib/mysql
max_connections = 200
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
innodb_buffer_pool_size = 2G
slow_query_log = 1
long_query_time = 2
[client]
default-character-set = utf8mb4
[mysql]
prompt = \u@\h [\d]>\_DDL 操作
テーブルの作成
CREATE TABLE が列、型、制約、テーブルオプションを定義します。デフォルトで推奨されるエンジンが InnoDB です(トランザクション、行レベルのロック、外部キーに対応します)。AUTO_INCREMENT のオーバーフローを避けるためには BIGINT UNSIGNED、金銭に正確なのは DECIMAL(p,s)、固定リストでの検証が ENUM、挿入時の自動充填が TIMESTAMP DEFAULT CURRENT_TIMESTAMP です。
CREATE TABLE users (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL,
birth_date DATE NULL,
status ENUM('active','inactive','banned') NOT NULL DEFAULT 'active',
balance DECIMAL(10,2) NOT NULL DEFAULT 0.00,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;テーブルの変更
ALTER TABLE がデータを再作成せずにスキーマを進化させます。特定の位置に置くのが、AFTER 付きの ADD COLUMN です(MySQL では列の順序は見た目だけです)。type/default の変更が MODIFY で、CHANGE よりきれいなのが RENAME COLUMN(8.0+)です。大きな ALTER 操作がテーブルを再構築しロックし得るため、大きなテーブルには pt-online-schema-change かオンライン DDL を使いましょう。アトミックなのが RENAME TABLE です。
# add a column
ALTER TABLE users
ADD COLUMN phone VARCHAR(20) NULL AFTER email;
# add multiple columns
ALTER TABLE users
ADD COLUMN first_name VARCHAR(50) NULL,
ADD COLUMN last_name VARCHAR(50) NULL;
# modify a column type
ALTER TABLE users
MODIFY COLUMN phone VARCHAR(30) NOT NULL;
# rename a column (MySQL 8+ preserves data)
ALTER TABLE users
RENAME COLUMN phone TO phone_number;
# rename a table
RENAME TABLE users TO members;
ALTER TABLE users RENAME TO members;テーブルの DROP と TRUNCATE
テーブル全体の除去が DROP TABLE で、中身だけを空にし構造を保ち AUTO_INCREMENT を開始値へリセットするのが TRUNCATE です。TRUNCATE が DELETE より速いのは、行ごとの削除とロギングをスキップするからですが、DDL であるため(ロールバックできず、トリガーも発火しません)。他から参照されるテーブルを truncate するときは foreign_key_checks を無効化しましょう。
# drop a table (removes structure and data)
DROP TABLE IF EXISTS old_logs;
# drop multiple tables
DROP TABLE IF EXISTS temp1, temp2;
# truncate: empty the table, keep structure, reset AUTO_INCREMENT
TRUNCATE TABLE session_data;
# truncate cannot be rolled back in some engines
# (InnoDB: TRUNCATE is DDL, implicitly commits)
SET foreign_key_checks = 0;
TRUNCATE TABLE child_table;
SET foreign_key_checks = 1;AUTO_INCREMENT
AUTO_INCREMENT がプライマリキーのための連続番号を生成します — 各テーブルに 1 つだけです。現在のセッションで最も最近の INSERT が生成した id を返すのが LAST_INSERT_ID() で(接続ごとで、同時使用に安全)、削除、ロールバック、明示的な値の指定付きの挿入の後に、隙間が現れ得ます。リセットには、ALTER TABLE ... AUTO_INCREMENT = n を。
CREATE TABLE orders (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
amount DECIMAL(10,2)
);
# set the next auto-increment value
ALTER TABLE orders AUTO_INCREMENT = 1000;
# insert without specifying id
INSERT INTO orders (amount) VALUES (99.50);
# get the last inserted id
SELECT LAST_INSERT_ID();
# show current AUTO_INCREMENT value
SHOW TABLE STATUS LIKE 'orders'\Gテーブルの制約
制約がデータベースレベルでデータの整合性を強制します。InnoDB が対応するのは、プライマリキー、UNIQUE、NOT NULL、CHECK(8.0.16 以降で強制)、外部キーです。親が削除されたとき子の行を取り除くのが FOREIGN KEY ... ON DELETE CASCADE で、それらを null 化するのが SET NULL、削除をブロックするのが RESTRICT/NO ACTION です。管理しやすいよう制約に明示的な名前を付けましょう。外部キーには両方の列のインデックスが必要です。
CREATE TABLE orders (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NOT NULL,
total DECIMAL(10,2) NOT NULL,
status VARCHAR(20) NOT NULL,
# unique constraint
CONSTRAINT uq_order_no UNIQUE (user_id, status),
# check constraint (MySQL 8.0.16+ enforced)
CONSTRAINT chk_total CHECK (total >= 0),
# foreign key with cascading actions
CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE RESTRICT
) ENGINE=InnoDB;
# add a constraint later
ALTER TABLE orders
ADD CONSTRAINT chk_status CHECK (status IN ('paid','shipped','cancelled'));一時テーブル
TEMPORARY のテーブルが現在のセッションでだけ存在し、閉じると自動的に削除されます。同名の本物のテーブルを覆い隠せるため、安全なリファクタリングやデータのステージングに有用です。2 つのセッションが、衝突なく同じ名前の一時テーブルを作れます。他の接続には見えず、デフォルトでは binlog にも書かれません。
# session-scoped, auto-dropped on disconnect
CREATE TEMPORARY TABLE tmp_active
SELECT id, username FROM users WHERE status = 'active';
# identical name shadowing a real table
CREATE TEMPORARY TABLE users
(id INT, name VARCHAR(50));
# only this session sees the temp table
SELECT * FROM users;
# explicit cleanup
DROP TEMPORARY TABLE IF EXISTS tmp_active;データ型
数値型
正確さが効く金銭の値には DECIMAL を使いましょう — FLOAT/DOUBLE は近似的で丸め誤差が蓄積します。正の範囲を 2 倍にしつつ負を禁じるのが UNSIGNED です。表示幅(INT(11) など)は MySQL 8.0 で非推奨になり無視されます — 保存範囲を制限したことは一度もありません。AUTO_INCREMENT のオーバーフローを避けるため推奨されるのが、BIGINT UNSIGNED です。代理キーの便利なショートカットが SERIAL です。
# integers (with optional UNSIGNED)
TINYINT -- 1 byte, -128..127 or 0..255 (UNSIGNED)
SMALLINT -- 2 bytes
MEDIUMINT -- 3 bytes
INT -- 4 bytes
BIGINT -- 8 bytes (use for large AUTO_INCREMENT)
# fixed-point (exact, for money)
DECIMAL(10,2) -- 10 digits total, 2 after decimal
NUMERIC(8,4)
# floating-point (approximate)
FLOAT -- 4 bytes
DOUBLE -- 8 bytes
# other
BIT(8) -- bit-field
SERIAL -- alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE文字列とテキスト型
短い固定長のコードを除き、(スペースで埋める)CHAR より VARCHAR がほとんど常に推奨です。MySQL の utf8mb4 では各文字が最大 4 バイトを取るため、VARCHAR(255) の列が最大 1020 バイトと長さを必要とします — これはインデックスのキー上限(InnoDB で 3072 バイト)で効きます。オフページで大きなデータを保存するのが TEXT/BLOB ファミリーで、デフォルト値を持てず、インデックスには接頭辞の長さが要ります。
# fixed vs variable length
CHAR(10) -- fixed 10 chars, padded with spaces
VARCHAR(255) -- variable, up to 255 chars (stores actual length)
# large text
TINYTEXT -- up to 255 bytes
TEXT -- up to 64 KB
MEDIUMTEXT -- up to 16 MB
LONGTEXT -- up to 4 GB
# binary counterparts
BINARY(16), VARBINARY(255), BLOB, MEDIUMBLOB, LONGBLOB
# national character set
NATIONAL VARCHAR(100) -- same as VARCHAR with utf8mb4
# VARCHAR with charset counts characters, not bytes
VARCHAR(100) CHARACTER SET utf8mb4日付と時刻型
タイムゾーンを意識しないリテラルの日付/時刻を保存するのが DATETIME で、UTC を保存し表示時にセッションの time_zone へ変換するのが TIMESTAMP です — これがクロスタイムゾーンのアプリには向きますが、32 ビットビルドでは 2038 年の上限があります。マイクロ秒の精度には DATETIME(6)/TIMESTAMP(6) を使いましょう。MySQL 5.6.5 以降、CURRENT_TIMESTAMP が両方のデフォルトとして機能します。接続ごとのセッション time_zone の設定は、SET time_zone = '+08:00'; です。
DATE -- 'YYYY-MM-DD', range 1000-01-01 .. 9999-12-31
TIME -- 'HH:MM:SS', can be negative for elapsed time
DATETIME -- 'YYYY-MM-DD HH:MM:SS', 8 bytes, no timezone
TIMESTAMP -- stored as UTC, converted to session timezone, 4 bytes
YEAR -- YEAR(4) e.g. 2024
# DATETIME with fractional seconds and auto-default
CREATE TABLE events (
ts DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3)
ON UPDATE CURRENT_TIMESTAMP(3)
);
# TIMESTAMP range: 1970-01-01 .. 2038-01-19 (32-bit limit)
# MySQL 8.0.28+ extends TIMESTAMP to 24 bits of rangeENUM と SET 型
固定リストの文字列値の 1 つをコンパク トに(1〜2 バイト)保存し、厳密モードで未知の値を拒否するのが ENUM です。最大 64 メンバーの任意の組み合わせをビットマスクとして保存するのが SET です。どちらも、スキーマ変更が高コストになり(メンバーの追加・並べ替えに ALTER TABLE が要る)、クエリも扱いにくいため、多くのチームが、ルックアップテーブルか CHECK 付きの VARCHAR を推奨します。SET の列のクエリには FIND_IN_SET が役立ちます。
# ENUM: exactly one value from a list
CREATE TABLE shirts (
size ENUM('S','M','L','XL') NOT NULL DEFAULT 'M',
color ENUM('red','green','blue') NULL
);
# stored as integer index (1-based), space-efficient
INSERT INTO shirts (size) VALUES ('L'), ('XL');
# SET: zero or more values from a list (bit flags)
CREATE TABLE posts (
tags SET('news','tech','sports','fun') NOT NULL DEFAULT ''
);
INSERT INTO posts (tags) VALUES ('tech,fun');
# query SET members with FIND_IN_SET
SELECT * FROM posts WHERE FIND_IN_SET('tech', tags);JSON 型
JSON 型(MySQL 5.7.8+)が、書き込み時に検証され、速いメンバーアクセスをサポートするバイナリ形式で JSON を保存します。TEXT の列に JSON を保存するより好ましい理由は、中をインデックスしクエリできるからです。値の構築には、JSON_OBJECT()、JSON_ARRAY()、JSON_MERGE_PATCH() を使いましょう。少数のキーの頻繁なクエリには、それらを生成列へ抽出しインデックスすれば、最高のパフォーマンスが得られます。
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
attrs JSON NOT NULL
);
# insert JSON literals
INSERT INTO products (id, name, attrs) VALUES
(1, 'Widget', '{"color":"red","size":42,"tags":["new","sale"]}'),
(2, 'Gadget', '{"color":"blue","in_stock":true}');
# JSON values are validated on insert and stored binary
SELECT attrs FROM products WHERE id = 1;
# pretty-print JSON
SELECT JSON_PRETTY(attrs) FROM products WHERE id = 1;BLOB とバイナリ型
BLOB が任意のバイナリデータ(画像、ファイル)を保存しますが、クエリしにくくデフォルトも持てません。大きなファイルには、DB にはパスを保存し、ファイルはディスク/オブジェクトストレージへ置く方を推奨しましょう。インデックスの効率のため、CHAR(36)(36 バイト)ではなく BINARY(16)(16 バイト)で UUID を保存しましょう — MySQL 8.0 が、インデックスに優しい省略可能な swap 付きの UUID_TO_BIN()/BIN_TO_UUID() を加えています。
# binary large objects
TINYBLOB -- up to 255 bytes
BLOB -- up to 64 KB
MEDIUMBLOB -- up to 16 MB
LONGBLOB -- up to 4 GB
# fixed-size binary (e.g. hashes, UUIDs)
BINARY(16) -- fixed 16 bytes, right-padded with \0
VARBINARY(255)
# store a UUID as binary for compactness
CREATE TABLE sessions (
id BINARY(16) PRIMARY KEY,
data VARBINARY(1000)
);
INSERT INTO sessions (id, data) VALUES (UUID_TO_BIN(UUID()), '...');
# retrieve as text
SELECT BIN_TO_UUID(id) FROM sessions;DML(INSERT / UPDATE / DELETE)
INSERT
パース、ネットワークのラウンドトリップ、インデックスの更新を償却するため、複数行の INSERT が繰り返しの単一行の挿入よりはるかに速いです。テーブル間でデータをコピーするのが INSERT ... SELECT です。MySQL 固有で 1 行に便利なのが SET 構文です。ステートメントがスキーマ変更に耐えるよう、ターゲットの列は必ず明示的に列挙しましょう。冪等な挿入には、INSERT IGNORE か ON DUPLICATE KEY UPDATE を。
# single row
INSERT INTO users (username, email)
VALUES ('alice', '[email protected]');
# multiple rows in one statement
INSERT INTO users (username, email) VALUES
('bob', '[email protected]'),
('carol', '[email protected]'),
('dave', '[email protected]');
# insert from a SELECT
INSERT INTO archive_users (username, email)
SELECT username, email FROM users WHERE status = 'inactive';
# insert with column-order-free syntax
INSERT INTO users SET username='eve', email='[email protected]';INSERT ... ON DUPLICATE KEY UPDATE(Upsert)
upsert を実装するのが ON DUPLICATE KEY UPDATE です:UNIQUE/PRIMARY KEY の競合が起きたとき、MySQL がエラーを出す代わりに既存の行を更新します。挿入されたはずの値を参照するのが VALUES(col) で、MySQL 8.0.19+ がこれを非推奨とし、行の別名(AS new)を推奨しています。アトミックであるため、別々の SELECT-then-INSERT/UPDATE より効率的です。競合を静かにスキップするのが INSERT IGNORE です。
CREATE TABLE counters (
name VARCHAR(50) PRIMARY KEY,
hits INT NOT NULL DEFAULT 0
);
# upsert: insert or update on key conflict
INSERT INTO counters (name, hits)
VALUES ('home', 1)
ON DUPLICATE KEY UPDATE hits = hits + 1;
# reference the proposed values with VALUES()
INSERT INTO counters (name, hits) VALUES ('about', 5)
ON DUPLICATE KEY UPDATE hits = VALUES(hits) + 1;
# MySQL 8.0.20+: use an alias for the row being inserted
INSERT INTO counters (name, hits) VALUES ('home', 1) AS new
ON DUPLICATE KEY UPDATE hits = counters.hits + new.hits;UPDATE
WHERE 句は必ず含めましょう — なければ UPDATE がすべての行を変更します。JOIN 付きのマルチテーブル UPDATE が MySQL でサポートされ、計算値の非正規化に有用です。巨大なテーブルでの長いロックを避ける安全なバッチ更新を可能にするのが、ORDER BY + LIMIT です。safe-updates モードでは、クライアントが WHERE 句にキーのない UPDATE/DELETE を拒否します — うっかりの大規模変更への防御です。
# basic update with WHERE (always include one!)
UPDATE users
SET status = 'banned', email = NULL
WHERE id = 42;
# update with expression
UPDATE products
SET price = price * 1.10
WHERE category = 'electronics';
# multi-table update with JOIN
UPDATE users u
JOIN orders o ON o.user_id = u.id
SET u.total_spent = u.total_spent + o.amount
WHERE o.paid = 1;
# limit and order (useful for batched updates)
UPDATE logs SET archived = 1
WHERE archived = 0
ORDER BY created_at ASC
LIMIT 1000;DELETE
DELETE が行を一度に 1 行ずつ取り除き、トランザクション的(ロールバック可能)でトリガーを発火させます。1 つのステートメントで複数のテーブルから取り除けるのが、マルチテーブル DELETE です。テーブル全体を空にするには、TRUNCATE がはるかに速く AUTO_INCREMENT をリセットしますが、トランザクション的ではありません。インデックスのリーフ更新をスキップするのが QUICK 修飾子(MyISAM)で — InnoDB ではめったに不要です。必ず WHERE 句を使いましょう。
# delete specific rows
DELETE FROM users WHERE status = 'banned' AND last_login < '2020-01-01';
# delete with LIMIT (batched)
DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 5000;
# multi-table delete with JOIN
DELETE u, o
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.id = 99;
# delete all rows (keeps table & AUTO_INCREMENT, unlike TRUNCATE)
DELETE FROM temp_data;
# quick delete that doesn't return row count
DELETE QUICK FROM logs WHERE year = 2020;REPLACE
MySQL 固有の REPLACE は:同 じ PK/UNIQUE キーを持つ行が存在すれば削除され新しい行が挿入され、そうでなければ単に挿入します。upsert より単純ですが副作用があります — UPDATE ではなく DELETE と INSERT のトリガーを発火させ、AUTO_INCREMENT をリセットし、提供されなかった列をデフォルトへ落とします。ほとんどのユースケースには、INSERT ... ON DUPLICATE KEY UPDATE を推奨しましょう。
# REPLACE deletes then inserts on key conflict
REPLACE INTO users (id, username, email)
VALUES (1, 'alice', '[email protected]');
# REPLACE ... SET form
REPLACE INTO users SET id = 1, username = 'alice', email = '[email protected]';
# REPLACE from SELECT
REPLACE INTO daily_stats (day, visits)
SELECT CURDATE(), COUNT(*) FROM visits_log;
# caveat: REPLACE fires DELETE + INSERT triggers,
# not UPDATE, and resets columns not listedLOAD DATA INFILE
CSV の一括ロードの最も速い方法が LOAD DATA INFILE です — SQL のパースをバイパスするため、INSERT ステートメントの 20 倍速いです。サーバーが安全なディレクトリ(secure_file_priv)からファイルを読み、LOCAL はクライアントがファイルを送りますが、セキュリティのためデフォルトで無効です。ロード中の値の変換には、@var で列を取り込み SET しましょう。対称的なエクスポートが SELECT ... INTO OUTFILE です。
# fast bulk import from a CSV file
LOAD DATA INFILE '/var/lib/mysql-files/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(username, email, @created)
SET created_at = STR_TO_DATE(@created, '%Y-%m-%d %H:%i:%s');
# LOCAL reads from the client machine (needs --local-infile)
LOAD DATA LOCAL INFILE 'C:/data/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES (username, email);
# export counterpart
SELECT * FROM users INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';SELECT クエリ
基本の SELECT
本番では SELECT * を避けましょう — 不必要なデータを転送し、列が追加・削除されると壊れ、カバリングインデックスの利用をオプティマイザーに許しません。必要な列を名指ししましょう。OFFSET 付きの LIMIT がページネーションを実装しますが、大きなオフセットでは遅いです(スキップされた行を依然スキャンします)。はるかに良くスケールするのが、キーセットのページネーション(WHERE id > last_id LIMIT n)です。
# select all columns
SELECT * FROM users;
# select specific columns (preferred)
SELECT id, username, email FROM users;
# column aliases
SELECT username AS name, email AS contact FROM users;
# constants and expressions
SELECT 1 + 1 AS sum, NOW() AS now, 'hello' AS greeting;
# limit rows and skip (pagination)
SELECT id, username FROM users
ORDER BY id DESC
LIMIT 10 OFFSET 20;
# MySQL shorthand: LIMIT offset, count
SELECT id, username FROM users LIMIT 20, 10;WHERE と演算子
NULL との比較は NULL を返すため(false として扱われます)、必ず IS NULL / IS NOT NULL を使いましょう。IN/NOT IN のリストの中の NULL が驚くほど空の結果を生むため、代わりに NOT EXISTS か相関サブクエリを使いましょう。範囲に含むのが BETWEEN です。MySQL が OR の前に AND を評価するため、混在した条件は括弧で包みましょう。最高のパフォーマンスのためには、最も選択的な条件を先に置き、インデックス付きの列が演 算子の左に来るようにしましょう。
# comparison operators
SELECT * FROM products WHERE price < 100;
SELECT * FROM products WHERE price BETWEEN 50 AND 150;
SELECT * FROM users WHERE id IN (1, 5, 9);
# logical operators
SELECT * FROM users
WHERE status = 'active' AND (age >= 18 OR verified = 1);
# NULL checks (never use = with NULL)
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;
# BETWEEN is inclusive on both ends
SELECT * FROM orders WHERE created_at
BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59';
# NOT IN with NULL traps: NULL poisons the resultORDER BY と LIMIT
結果のソートが ORDER BY で、複数のキーは左から右へ適用されます。列挙リストによるカスタム順序を可能にするのが FIELD() です。大きな結果セットでは、インデックスが対応しない限り、ORDER BY ... LIMIT が依然セット全体をスキャン・ソートし得ます — ORDER BY の列にインデックスを加えましょう。MySQL 8.0+ でのグループごとのトップ N の正統な解決策が、(ROW_NUMBER OVER PARTITION BY の)窓関数のパターンです。
# ascending (default) and descending
SELECT * FROM users ORDER BY created_at;
SELECT * FROM users ORDER BY created_at DESC, username ASC;
# order by expression
SELECT product, price * stock AS value
FROM inventory
ORDER BY value DESC;
# order by FIELD() for custom ordering
SELECT * FROM tasks
ORDER BY FIELD(priority, 'high', 'medium', 'low');
# stable top-N per group via window function (8.0+)
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id DESC) rn
FROM orders
) t WHERE rn <= 3;DISTINCT とグループ化値
同一の行の折りたたみが DISTINCT で、複数の列ではその組み合わせの重複を除きます。一意の非 null の値を数えるのが COUNT(DISTINCT col) です。DISTINCT は本質的に、選択されたすべての列上の GROUP BY です。代表行(最も新しい行など)を保ちつつ重複を除くには、ROW_NUMBER() OVER (PARTITION BY ...) を使いましょう。DISTINCT は高コストになり得ます — 一時テーブルを構築するため、インデックスを推奨しましょう。
# unique values of one column
SELECT DISTINCT country FROM users;
# distinct over multiple columns
SELECT DISTINCT city, country FROM users;
# COUNT distinct values
SELECT COUNT(DISTINCT country) AS countries FROM users;
# GROUP BY returns one row per group; DISTINCT is a special case
SELECT country, COUNT(*) AS users
FROM users
GROUP BY country;
# deduplicate while keeping the latest row (8.0+)
WITH latest AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id DESC) rn
FROM users
)
SELECT id, email FROM latest WHERE rn = 1;LIKE と REGEXP のパターンマッチング
LIKE は単純ですが、先頭のワイルドカード(LIKE '%x')がインデックスを無効化しフルスキャンを強制します。大文字小文字の敏感さを決めるのが照合順序で、アクセント/大文字小文字を区別しないのが utf8mb4_0900_ai_ci です。完全な正規表現の力を加えるのが REGEXP/RLIKE ですが、インデックスを決して使いません。より豊かなパターン処理を加えるのが、MySQL 8.0 の REGEXP_LIKE、REGEXP_REPLACE、REGEXP_INSTR、REGEXP_SUBSTR です。全文検索には、MATCH ... AGAINST 付きの FULLTEXT インデックスを。
# LIKE wildcards: % (any chars) and _ (one char)
SELECT * FROM users WHERE username LIKE 'a%'; -- starts with a
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE username LIKE '_an'; -- 3 chars, ends 'an'
# escape a wildcard literally
SELECT * FROM users WHERE path LIKE '50\%' ESCAPE '\\';
# case-insensitive LIKE depends on column collation
# *_ci collations are case-insensitive
# REGEXP / RLIKE for regular expressions
SELECT * FROM users WHERE email REGEXP '^[a-z]+@';
SELECT * FROM products WHERE name RLIKE 'iPhone|Galaxy';
# capture groups with REGEXP_REPLACE (8.0+)
SELECT REGEXP_REPLACE(phone, '([0-9]{3})([0-9]{4})', '$1-$2')
FROM contacts;CASE 式
SQL の条件式が CASE です。探索形式が、WHEN 条件を上から下へ評価し、最初の一致の THEN 値を返し、さもなければ ELSE(または NULL)です。CASE は SELECT、WHERE、ORDER BY、集計で使え — SUM(CASE ...) のパターンがクラシックなピボットです。CASE が集計を短絡させないため、すべての行が数えられます。単純なケースのための MySQL のより短いショートカットが、IF() と IFNULL() です。
# searched CASE (like if/else)
SELECT username,
CASE
WHEN age < 18 THEN 'minor'
WHEN age < 65 THEN 'adult'
ELSE 'senior'
END AS age_group
FROM users;
# simple CASE (compare to a value)
SELECT order_id,
CASE status
WHEN 0 THEN 'pending'
WHEN 1 THEN 'paid'
WHEN 2 THEN 'shipped'
ELSE 'unknown'
END AS status_label
FROM orders;
# CASE in aggregate (pivot)
SELECT
SUM(CASE WHEN status='paid' THEN 1 ELSE 0 END) AS paid_count,
SUM(CASE WHEN status='shipped' THEN 1 ELSE 0 END) AS shipped_count
FROM orders;JOIN
INNER JOIN
両方のテーブルに一致があるときだけ行を返すのが INNER JOIN です — どちらの側の一致しない行も落とされます。INNER JOIN の省略形が JOIN です。列名が同一で結果に 1 回現れるとき、ON a.col = b.col の簡潔な代替が USING (col) です。正規化されたテーブルを関連付けるための、デフォルトで最もよくある join のタイプが INNER JOIN です。
# only matching rows from both tables
SELECT u.username, o.id AS order_id, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id
WHERE o.paid = 1;
# join three tables
SELECT u.username, o.id, oi.product_id, oi.qty
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id;
# USING clause when join columns share a name
SELECT u.username, o.id
FROM users u
JOIN orders o USING (user_id);LEFT と RIGHT JOIN
すべての左テーブルの行を保ち、一致がなければ右側を NULL で満たすのが LEFT JOIN です。一致する右の行のない左の行を見つける WHERE o.id IS NULL のトリック(アンチ結合)が、NOT IN よりしばしばより明快で速いです — NULL があるときには特に。対称なのが RIGHT JOIN で、読みやすさのため、ほとんどの人はテーブルを並べ替え LEFT JOIN を使います。結合キーの中の NULL は決して一致しません。
# LEFT JOIN: all left rows, with NULLs where no match
SELECT u.username, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;
# find users with NO orders (anti-join pattern)
SELECT u.username
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;
# RIGHT JOIN is the mirror (rarely used; swap tables instead)
SELECT u.username, o.id
FROM orders o
RIGHT JOIN users u ON o.user_id = u.id;CROSS JOIN
デカルト積を返すのが CROSS JOIN です — 2 つのテーブルの行のすべての組み合わせです。組み合わせの生成や次元マトリックスの播種に有用ですが、N x M の行を作るため、大きなテーブルでは高コストです。テーブル間のカンマ(FROM a, b)が暗黙の CROSS JOIN です。MySQL 8.0+ でのシーケンス/次元テーブル生成の標準的な方法が、再帰 CTE のパターンです。
# Cartesian product: every row of A paired with every row of B
SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;
# same effect with a comma join
SELECT s.size, c.color FROM sizes s, colors c;
# generate a series of dates (no built-in GENERATE_SERIES)
WITH RECURSIVE nums(n) AS (
SELECT 1 UNION ALL SELECT n+1 FROM nums WHERE n < 7
)
SELECT DATE_ADD('2024-01-01', INTERVAL n-1 DAY) AS day
FROM nums;
# build a matrix of all category x region combinations
SELECT cat.name, reg.name
FROM categories cat CROSS JOIN regions reg;Self Join
同じテーブルを 2 回、異なる別名で参照し、内部の行を関連付けるのが self join です — manager/employee や隣接リストの階層のクラシックです。重複した対称ペアを避ける条件が a.id < b.id です。深い階層には、再帰 CTE(MySQL 8.0+)を使いましょう — 固定の深さの上限なしでツリーをたどり、REPEAT/CONCAT でインデントされたツリー出力を構築できます。
# employees and their managers (same table twice)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
# find pairs of users in the same city
SELECT a.username AS user_a, b.username AS user_b, a.city
FROM users a
JOIN users b ON a.city = b.city AND a.id < b.id;
# hierarchical tree with recursive CTE (8.0+)
WITH RECURSIVE org AS (
SELECT id, name, manager_id, 1 AS depth
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, o.depth + 1
FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT CONCAT(REPEAT('-- ', depth-1), name) AS tree
FROM org;FULL OUTER JOIN の回避策
MySQL は FULL OUTER JOIN を実装していません。標準のエミュレーションが、RIGHT JOIN の UNION された LEFT JOIN です — UNION が重複を除くため、各一致する行が 1 回現れ、どちらかの側の一致しない行が、反対側に NULL と共に現れます。大きな結果では、UNION の暗黙の DISTINCT が高コストであるため、重複除去の戦略付きの UNION ALL を推奨しましょう。一致しない行だけが必要なら、代わりに 2 つのアンチ結合を使いましょう。
# MySQL has no FULL OUTER JOIN — emulate with LEFT + UNION + RIGHT
SELECT u.username, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
UNION
SELECT u.username, o.id AS order_id
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id;
# the UNION removes duplicates; use UNION ALL to keep them
# for an anti-union (mismatches only) add WHERE o.id IS NULL
# or u.id IS NULL in each branchNATURAL JOIN と USING
NATURAL JOIN は脆いです — 共有されたすべての列名で結合するため、列の追加が静かに結果を変え得ます。本番では避けましょう。より安全で、結合列を出力で 1 つのコピーへ折りたたむのが USING (col) です。オプティマイザーが使うテーブルの順序を強制するのが STRAIGHT_JOIN で、悪い計画の手動の上書きです。本物の問題が修正されたら取り除きましょう。明快さのため、明示的な ON を推奨します。
# NATURAL JOIN matches all same-named columns automatically
SELECT * FROM users NATURAL JOIN profiles;
# USING matches specific shared columns, dedups them in output
SELECT u.username, user_id
FROM users u
JOIN orders o USING (user_id);
# STRAIGHT_JOIN forces the optimizer to join left-to-right
SELECT STRAIGHT_JOIN u.username, o.id
FROM users u
JOIN orders o ON o.user_id = u.id;集計とグループ化
集計関数
多くの行を 1 行へ折りたたむのが集計関数です:COUNT、SUM、AVG、MIN、MAX。行を数えるのが COUNT(*)、col の非 null の値を数えるのが COUNT(col)、一意の値を数えるのが COUNT(DISTINCT col) です。AVG が NULL を(ゼロではなく)無視するため結果が歪み得ます — NULL をゼロとして扱いたいなら COALESCE(col,0) を使いましょう。GROUP BY なしの集計が、空のテーブルでも単一の行を返します。
SELECT
COUNT(*) AS row_count,
COUNT(email) AS emails, -- ignores NULLs
COUNT(DISTINCT country) AS countries,
MIN(created_at) AS first_user,
MAX(created_at) AS last_user,
AVG(age) AS avg_age,
SUM(balance) AS total_balance
FROM users;
# aggregate over a filtered set
SELECT SUM(amount) AS paid_total
FROM orders
WHERE status = 'paid';GROUP BY
グループ化された列の値を共有する行を 1 つの出力行へ折りたたむのが GROUP BY で、集計はグループごとに計算されます。MySQL 8.0 がデフォルトで ONLY_FULL_GROUP_BY を有効化し、GROUP BY にない列を選ぶクエリを拒否します(以前のバージョンは任意の行を返していました — よくあるバグです)。式や別名でグループ化しましょう。時系列のレポートには、タイムスタンプの YEAR()/MONTH()/DATE() でグループ化します。
# one row per country
SELECT country, COUNT(*) AS users, AVG(age) AS avg_age
FROM users
GROUP BY country;
# group by multiple columns
SELECT country, city, COUNT(*) AS users
FROM users
GROUP BY country, city
ORDER BY country, users DESC;
# group by expression
SELECT YEAR(created_at) AS yr, MONTH(created_at) AS m, COUNT(*)
FROM users
GROUP BY yr, m;
# MySQL 8.0 enforces ONLY_FULL_GROUP_BY:
# every non-aggregated SELECT column must be in GROUP BYHAVING
集計の結果をフィルタするのが HAVING で、グループ 化の前に入力行をフィルタするのが WHERE です — そのため HAVING が集計(SUM、COUNT)を参照でき、WHERE はできません。効率のため、行レベルのフィルタは WHERE に置きましょう(グループ化の前に行を減らします)。MySQL では HAVING が別名を受け付けます(HAVING users > 100)。クエリは両方を使えます:WHERE が行を狭め、GROUP BY がバケット化し、HAVING がバケットを狭めます。
# HAVING filters groups (after aggregation); WHERE filters rows (before)
SELECT country, COUNT(*) AS users
FROM users
GROUP BY country
HAVING COUNT(*) > 100
ORDER BY users DESC;
# filter on an aggregate
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 1000;
# combine WHERE and HAVING
SELECT country, COUNT(*) AS users, AVG(age) AS avg_age
FROM users
WHERE status = 'active'
GROUP BY country
HAVING avg_age >= 30;GROUP BY ROLLUP
各グループ化レベルのための追加の上位集計行と総計を加えるのが WITH ROLLUP で、グループ化された列が NULL になります。小計/合計の行で GROUPING(col) が 1 を返すため、NULL を「ALL」としてラベル付けできます。GROUP BY の列上で ROLLUP が左から右へ機能します。MySQL には WITH CUBE がなく — すべての次元の組み合わせが必要なら、複数の ROLLUP クエリの UNION ALL でエミュレートしましょう。
# ROLLUP adds subtotals and a grand total row
SELECT country, city, COUNT(*) AS users
FROM users
GROUP BY country, city WITH ROLLUP;
# output includes:
# country | city | users
# 'US' | 'NYC' | 120
# 'US' | NULL | 200 <- US subtotal
# NULL | NULL | 950 <- grand total
# identify super-aggregate rows with GROUPING()
SELECT
IF(GROUPING(country)=1,'ALL',country) AS country,
IF(GROUPING(city)=1,'ALL',city) AS city,
COUNT(*) AS users
FROM users
GROUP BY country, city WITH ROLLUP;GROUP_CONCAT
MySQL の文字列集計関数が GROUP_CONCAT です — グループの値を単一の文字列へ連結します。重複の除去には DISTINCT を、ソートには ORDER BY を、区切りの変更(デフォルトは ',')には SEPARATOR を使いましょう。結果は group_concat_max_len(デフォルト 1024 バイト)に上限が設けられます。より長いリストには引き上げましょう。他のデータベースはこれを LISTAGG/STRING_AGG と呼びます — MySQL の等価物が GROUP_CONCAT です。
# concatenate grouped values into one string
SELECT country,
GROUP_CONCAT(username) AS all_names
FROM users
GROUP BY country;
# order within the list and deduplicate
SELECT user_id,
GROUP_CONCAT(DISTINCT tag ORDER BY tag SEPARATOR ',') AS tags
FROM post_tags
GROUP BY user_id;
# the default separator is ',', max length is 1024 by default
# raise the limit:
# SET SESSION group_concat_max_len = 1000000;
SELECT department,
GROUP_CONCAT(name ORDER BY salary DESC SEPARATOR ' | ') AS ranking
FROM employees
GROUP BY department;WITH ROLLUP 対窓集計
小計行を加えることで結果を縮めるのが ROLLUP であるのに対し、窓集計(OVER)が同じ行数を保ち、集計を余分な列として追記します — 詳細と合計の両方を一緒に見られます。サマリーレポートには ROLLUP を、集計と並んだ行ごとのコンテキストが必要なときには窓関数を使いましょう。累計が、SUM OVER (ORDER BY ...) のクラシックなユースケースです。
# ROLLUP gives fewer rows (subtotals mixed into the result)
SELECT country, city, COUNT(*) AS users
FROM users
GROUP BY country, city WITH ROLLUP;
# a window aggregate keeps every detail row AND adds the total
SELECT
country, city,
COUNT(*) AS users_in_city,
COUNT(*) OVER (PARTITION BY country) AS users_in_country,
COUNT(*) OVER () AS users_total
FROM users;
# running total over time
SELECT created_at, amount,
SUM(amount) OVER (ORDER BY created_at) AS running_total
FROM daily_sales;サブクエリと CTE
スカラーのサブクエリ
単一の値(1 行、1 列)を返すのがスカラーのサブクエリで、式が有効などこでも使えます — SELECT のリスト、WHERE、HAVING です。行を返さなければ、値は NULL です。オプティマイザーがしばしば、相関スカラーのサブクエリを結合へ変えます(サブクエリの マテリアライゼーション)。明快さのため(そして時にはパフォーマンスのため)、同じサブクエリが繰り返し参照されるなら、CTE か JOIN がより良いです。
# returns a single value
SELECT username, balance,
(SELECT AVG(balance) FROM users) AS avg_balance
FROM users
WHERE balance > (SELECT AVG(balance) FROM users);
# use in SELECT list, WHERE, HAVING, etc.
SELECT product, price,
price - (SELECT MIN(price) FROM products) AS above_min
FROM products;
# a scalar subquery must return exactly one row, one columnIN / ANY / ALL のサブクエリ
サブクエリが返す任意の値に一致するのが IN で、補集合が NOT IN ですが、これは NULL に毒されます — サブクエリが NULL を返せば、NOT IN が行を全く返さないため、NULL を必ず除外するか NOT EXISTS を使いましょう。比較が返されたすべての値について成り立つとき ALL が真で、少なくとも 1 つで成り立つのが ANY/SOME です。オプティマイザーがこれらをしばしばセミ結合へ書き換えます。
# IN: value matches any row of the subquery
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE status='active');
# NOT IN with NULLs is dangerous — prefer NOT EXISTS
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);
# ANY / SOME / ALL with comparisons
SELECT * FROM products
WHERE price > ALL (SELECT price FROM products WHERE category='toys');
SELECT * FROM products
WHERE price > ANY (SELECT price FROM products WHERE category='toys');EXISTS と NOT EXISTS
データを返さず行の存在をテストするのが EXISTS です — 最初の一致でスキャンを止めるため、存在チェックには効率的です。サブクエリは相関しています(外側のクエリを参照します)。一致のない行を見つける NULL セーフな方法が NOT EXISTS で、NOT IN より一般に推奨です。EXISTS 内の SELECT 1 が慣例で、列のリストは無関係です。オプティマイザーが EXISTS をしばしばセミ結合へ変えます。
# EXISTS: true if the subquery returns any rows
SELECT u.username
FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
# NOT EXISTS: the standard anti-semi-join (NULL-safe)
SELECT u.username
FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
# correlated: the subquery references the outer row (u.id)
# EXISTS stops at the first matching row — efficient for presence checks派生テーブル
FROM 句 の中のサブクエリが派生テーブルで — 選択や結合できる中間結果をマテリアライズします。必ず別名を付けましょう。MySQL 8.0 が多くの派生テーブルを(derived_merge で)外側のクエリへマージしより良い計画にできますが、複雑な集計や LIMIT が通常マテリアライゼーションを強制します。読みやすさと、クエリをまたぐ再利用のためには、インラインの派生テーブルより名前付き CTE(WITH)を推奨しましょう。
# a subquery in the FROM clause is a derived table
SELECT t.country, t.users
FROM (
SELECT country, COUNT(*) AS users
FROM users
GROUP BY country
) t
WHERE t.users > 50;
# join a derived table
SELECT u.username, agg.order_count
FROM users u
JOIN (
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
) agg ON agg.user_id = u.id;
# derived tables must be aliased相関サブクエリ
外側のクエリの列を参照するのが相関サブクエリで、論理的に外側の行ごとに再評価されるため — 大きなセットでは遅くなり得ます。オプティマイザーが結合へ変換したり、キャッシュ(サブクエリのキャッシュ)で緩和したりし得ます。ランク/トップ N の問題(n 番目に高い給与など)には、DENSE_RANK() OVER (ORDER BY salary DESC) のような窓関数がより明快で速いです — MySQL 8.0+ ではそちらに手を伸ばしましょう。
# the subquery references the outer row (u.id) and runs per row
SELECT u.username,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;
# correlated subquery in WHERE (nth-highest salary classic)
SELECT e1.name, e1.salary
FROM employees e1
WHERE 2 = (
SELECT COUNT(DISTINCT e2.salary)
FROM employees e2
WHERE e2.salary > e1.salary
);
# prefer a window function for rank-style problems in 8.0+共通テーブル式(CTE)
1 つのステートメント内での読みやすさと再利用のためにサブクエリに名前を付けるのが、CTE(WITH 句)です。MySQL 8.0+ が対応します。自己参照 CTE を可能にするのが RECURSIVE で — ツリーの走査とシーケンス生成には不可欠です(再帰には UNION ALL、重複除去には UNION)。再帰 CTE には、基底ケース、再帰の腕、終了のガードが必要です。複数の CTE はカ ンマ区切りで、同じ WITH 内の以前のものを参照できます。
# a named CTE (WITH) — readable, can be referenced multiple times
WITH active_users AS (
SELECT id, username FROM users WHERE status = 'active'
)
SELECT au.username, COUNT(o.id) AS orders
FROM active_users au
LEFT JOIN orders o ON o.user_id = au.id
GROUP BY au.username;
# RECURSIVE CTE: walk a hierarchy or generate a series
WITH RECURSIVE nums(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;
# multiple CTEs, comma-separated
WITH a AS (SELECT ...), b AS (SELECT ...)
SELECT * FROM a JOIN b USING (id);インデックス
インデックスの作成
書き込みのオーバーヘッドとストレージを代償に、ルックアップとソートを速くするデータ構造(InnoDB では B+Tree)がインデックスです。WHERE、JOIN、ORDER BY で使われる列へインデックスを作りましょう。一意性の制約も強制するのが UNIQUE インデックスです。長いテキスト列の接頭辞インデックスがスペースを節約しますが、カバリングスキャンや ORDER BY には使えません。InnoDB ではすべてのプライマリキーがクラスタ化され — セカンダリインデックスが PK の値を行ポインタとして保存します。
# single-column index
CREATE INDEX idx_email ON users(email);
# unique index (enforces uniqueness, allows fast lookup)
CREATE UNIQUE INDEX uq_username ON users(username);
# create with a table
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255),
UNIQUE KEY uq_email (email)
);
# full-table prefix index (limited length)
CREATE INDEX idx_name ON users(last_name(20));複合インデックス
複合インデックスは最も左の列が最初の順序であるため、列のリストの最も左の接頭辞でフィルタするクエリにしか役立ちません — 列の順序が決定的に重要です。最も選択的な等価列を先に置き、その後に範囲です。クエリが必要とするすべての列を含むのがカバリングインデックスで、テーブルの ルックアップを完全にスキップするインデックスのみのスキャンを可能にします(EXPLAIN が「Using index」を示す)。過剰なインデックスは避けましょう — すべてのインデックスが書き込みを遅くします。
# multi-column index (column order matters!)
CREATE INDEX idx_last_first ON users(last_name, first_name);
# supports these lookups (leftmost prefix rule):
# WHERE last_name = 'X' -> uses index
# WHERE last_name = 'X' AND first_name = 'Y' -> uses index
# WHERE first_name = 'Y' -> CANNOT use index
# covering index: all needed columns are in the index
CREATE INDEX idx_cover ON orders(user_id, status, amount);
SELECT user_id, SUM(amount) FROM orders
WHERE user_id = 5 AND status = 'paid' GROUP BY user_id;
# ^ index-only scan, no table lookup needed全文インデックス
自然言語と boolean のテキスト検索を可能にするのが FULLTEXT インデックスで、LIKE '%word%' よりはるかに優れています。関連度スコアを返すのが MATCH ... AGAINST です。boolean モードが演算子をサポートします:+require、-exclude、*wildcard、>increase、~decrease。InnoDB(5.6+)か MyISAM と、最小の単語長(ft_min_word_len / innodb_ft_min_token_size、デフォルト 3)が必要です。規模の大きい本気の検索には、Elasticsearch のような専用エンジンを検討しましょう。
CREATE TABLE articles (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT KEY ft_title_body (title, body)
) ENGINE=InnoDB;
# natural-language search
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST ('database performance');
# boolean mode: +require -exclude ~relax
SELECT id, title
FROM articles
WHERE MATCH(title, body)
AGAINST ('+MySQL -Oracle >index' IN BOOLEAN MODE);
# query expansion (second pass adds related terms)
SELECT id FROM articles
WHERE MATCH(title, body) AGAINST ('database' WITH QUERY EXPANSION);空間インデックス
GEOMETRY/POINT/POLYGON の列上の地理クエリを加速するのが、空間(R-tree)インデックスです。MySQL 8.0 が SRID 4326(WGS 84 の GPS 座標)で標準化し、現実世界のメートル距離のための ST_Distance_Sphere を加えています。列は NOT NULL でなければなりません。フィルタリングには ST_Within/ST_Contains/ST_Distance を使いましょう。X/Y の慣例に一致するよう、座標は常に(経度, 緯度)として表現しましょう。半径のクエリが、空間インデックスで桁違いに速くなります。
CREATE TABLE places (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
location POINT NOT NULL SRID 4326,
SPATIAL KEY sp_location (location)
) ENGINE=InnoDB;
# insert a point (longitude, latitude)
INSERT INTO places (name, location)
VALUES ('HQ', ST_PointFromText('POINT(116.40 39.90)', 4326));
# find points within a bounding distance
SELECT name, ST_Distance_Sphere(location,
ST_PointFromText('POINT(116.40 39.90)', 4326)) AS meters
FROM places
WHERE ST_Within(location,
ST_Buffer(ST_PointFromText('POINT(116.40 39.90)', 4326), 0.01));インデックスの管理
テーブルのインデックスを、列の順序とカーディナリティ(一意値の推定)と共に一覧するのが SHOW INDEX です。インデックスの削除が DROP INDEX で、改名(8.0+)が drop+rebuild を避けます。断片化したスペースを取り戻しインデックスの統計を更新するためテーブルを再構築するのが OPTIMIZE TABLE で — ロックが重いため、低トラフィックの間にスケジュールしましょう。古い統計がオプティマイザーを誤導し得るため、大きなデータのロード後に ANALYZE TABLE を実行しましょう。
# show indexes on a table
SHOW INDEX FROM users;
# drop an index
DROP INDEX idx_email ON users;
ALTER TABLE users DROP INDEX uq_username;
# rename an index (8.0+)
ALTER TABLE users RENAME INDEX idx_email TO idx_user_email;
# rebuild all indexes on a table
ALTER TABLE users ENGINE=InnoDB;
OPTIMIZE TABLE users;
# check index cardinality
SHOW INDEX FROM users;
SELECT index_name, cardinality
FROM information_schema.statistics
WHERE table_schema='mydb' AND table_name='users';不可視インデックスとヒント
不可視インデックス(8.0+)により、インデックスの除去を安全にテストできます — オプティマイザーが無視する一方、書き込みが最新を保つため、クエリが後退したら瞬時に戻せます。確信したら削除しましょう。古い USE/FORCE/IGNORE INDEX 構文より推奨さ れるのが、オプティマイザーのヒント(/*+ ... */)で、タグ付けされたステートメントにだけ影響します。ヒントは控えめに使いましょう — 悪いデータ分布や欠けた統計のための絆創膏です。
# make an index invisible (8.0+): optimizer ignores it, still maintained
ALTER TABLE users ALTER INDEX idx_email SET INVISIBLE;
# ... observe query plans, then drop or restore:
ALTER TABLE users ALTER INDEX idx_email SET VISIBLE;
# optimizer hints (8.0+) to force / avoid indexes
SELECT /*+ INDEX(u idx_email) */ * FROM users u WHERE email LIKE 'a%';
SELECT /*+ NO_INDEX(u idx_email) */ * FROM users u;
SELECT /*+ FORCE INDEX(u idx_email) */ * FROM users u;
# index merge / IGNORE_INDEX legacy hint
SELECT * FROM users USE INDEX (idx_email) WHERE email = '[email protected]';
SELECT * FROM users IGNORE INDEX (idx_email) WHERE email = '[email protected]';ビュー
ビューの作成
仮想テーブルのように振る舞う保存されたクエリがビューです — 複雑なクエリを単純化し、スキーマ変更を抽象化し、行/列の可視性を強制します。1 ステップで定義を更新するのが CREATE OR REPLACE です。ビューはデータを保存しない(マテリアライズされない限り、基礎のクエリを毎回再実行します)ため、書き込みのコストを加えませんが読み取りの高速化もしません。可能な限り、ビューがクエリへマージされます。
# a view is a stored SELECT
CREATE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE status = 'active';
# query it like a table
SELECT * FROM active_users WHERE username LIKE 'a%';
# create or replace, with explicit columns
CREATE OR REPLACE VIEW user_summary (username, order_count, total) AS
SELECT u.username, COUNT(o.id), COALESCE(SUM(o.amount), 0)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.username;
# list and inspect views
SHOW FULL TABLES WHERE Table_type = 'VIEW';
SHOW CREATE VIEW active_users\G更新可能なビュー
集計や distinct のない単一の基底テーブル上のビューが更新可能で — INSERT/UPDATE/DELETE が基底テーブルへ伝播します。デフォルトでは、行をビューの WHERE 句の外へ動かす UPDATE が成功します(行がビューから単に消えます)。行が決してビューのスコープを去れないよう、そのような変更をブロックするのが WITH CHECK OPTION です。JOIN、GROUP BY、DISTINCT、サブクエリのあるビューは一般に更新可能ではありません。
# a view over a single base table is updatable
CREATE VIEW active_users AS
SELECT id, username, email, status
FROM users
WHERE status = 'active';
# INSERT through the view inserts into the base table
INSERT INTO active_users (id, username, email, status)
VALUES (10, 'newbie', '[email protected]', 'active');
# UPDATE through the view only touches visible rows
UPDATE active_users SET email = '[email protected]' WHERE id = 10;
# WITH CHECK OPTION prevents inserts/updates that leave the view
CREATE OR REPLACE VIEW active_users AS
SELECT id, username, email, status FROM users WHERE status='active'
WITH CHECK OPTION;ビューの CHECK OPTION
ビューを通して変更された行がそのビューから見えるように強制するのが CHECK OPTION です。ビュー自身の WHERE だけを確認するのが LOCAL で、(デフォルトの)CASCADED が、参照する基礎のビューの WHERE 句も強制します。チェーン全体を一貫させるためビューを積み重ねるときは CASCADED を、基礎のビューのフィルタを意図的に緩めたいときだけ LOCAL を使いましょう。
# LOCAL: only the defining view's WHERE is enforced
CREATE VIEW v_paid AS
SELECT * FROM orders WHERE status='paid' WITH LOCAL CHECK OPTION;
# CASCADED (default): all underlying views' checks are enforced too
CREATE VIEW v_paid_large AS
SELECT * FROM v_paid WHERE amount > 100
WITH CASCADED CHECK OPTION;
# an insert that violates v_paid's filter:
INSERT INTO v_paid_large (id, status, amount) VALUES (1, 'unpaid', 200);
# CASCADED -> rejected (status would leave v_paid)
# LOCAL -> accepted (only v_paid_large's filter checked)ビューの管理
削除せずに既存のビューを再定義するのが ALTER VIEW です(付与を保存します)。ビューがないときのエラーを避けるのが DROP VIEW IF EXISTS です。RENAME TABLE がビューにも機能しますが一般的ではありません。各ビューの定義、更新可能か、セキュリティタイプを公開するのが information_schema.views で — 監査に有用です。ビューは基底テーブルに依存するため、基底テーブルの削除が、再作成されるまでビューを無効にします。
# alter a view's definition (CREATE OR REPLACE is simpler)
ALTER VIEW active_users AS
SELECT id, username FROM users WHERE status='active';
# drop a view
DROP VIEW IF EXISTS active_users;
# drop multiple views
DROP VIEW IF EXISTS v1, v2, v3;
# rename a view (no RENAME VIEW; recreate or use a table rename trick)
RENAME TABLE old_view TO new_view;
# view metadata
SELECT table_name, view_definition, is_updatable
FROM information_schema.views
WHERE table_schema = 'mydb';ビューのセキュリティと決定性
定義者の権限でビューを実行するのが SQL SECURITY DEFINER です — 基底テーブルへのアクセスのないユーザーへ、フィルタされたデータを公開するのに有用です(行レベルセキュリティのビューなど)。代わりに呼び出し元の権限を確認するのが INVOKER です。ビューをクエリへ折りたたむのが ALGORITHM MERGE で(より速く、更新可能なビューを許します)、先にマテリアライズするのが TEMPTABLE です(一部の構成に必要ですが、更新可能性を失い、遅くなり得ます)。MySQL に選ばせるのが UNDEFINED です。
# DEFINER (default): runs with the view creator's privileges
CREATE VIEW secret_emails SQL SECURITY DEFINER AS
SELECT email FROM users;
# INVOKER: runs with the querying user's privileges
CREATE VIEW my_emails SQL SECURITY INVOKER AS
SELECT email FROM users WHERE id = CURRENT_USER_ID();
# ALGORITHM: MERGE (preferred) vs TEMPTABLE vs UNDEFINED
CREATE ALGORITHM=MERGE VIEW v_active AS
SELECT * FROM users WHERE status='active';ストアドプロシージャ
プロシージャの作成と呼び出し
名前付きで事前コンパイルされた SQL のブロックがストアドプロシージャです。プロシージャがセミコロンを含め得るため、DELIMITER を変え、CREATE 全体が 1 つのステートメントとして実行され、その後リセットします。実行が CALL です。プロシージャは値を直接返しませんが、結果セットを返し OUT パラメータを修正できます。サーバー側で動くため、複数ステップのロジックのネットワークラウンドトリップを削りますが、アプリのコードよりバージョン管理とテストが難しいです。
DELIMITER //
CREATE PROCEDURE get_user_by_id(IN p_id INT)
BEGIN
SELECT id, username, email FROM users WHERE id = p_id;
END //
DELIMITER ;
# call it
CALL get_user_by_id(42);
# drop it
DROP PROCEDURE IF EXISTS get_user_by_id;
# show the definition
SHOW CREATE PROCEDURE get_user_by_id\GIN / OUT / INOUT のパラメータ
パラメータは、IN(読み取り専用の入力)、OUT(セッション変数経由で呼び出し元へ書き戻す)、INOUT(両方)です。スカラーのクエリ結果を変数へ割り当てるのが SELECT ... INTO です。例が、SELECT FOR UPDATE で行をロックし失われた更新を防ぎつつ、トランザクションで借方/貸方を包みます。ステータスコードの返却には OUT パラメータを使い、プロシージャ内で SELECT すれば、結果セットは直接返されます。
DELIMITER //
CREATE PROCEDURE transfer(
IN p_from INT,
IN p_to INT,
IN p_amount DECIMAL(10,2),
OUT p_result VARCHAR(50)
)
BEGIN
DECLARE v_bal DECIMAL(10,2);
START TRANSACTION;
SELECT balance INTO v_bal FROM accounts WHERE id = p_from FOR UPDATE;
IF v_bal >= p_amount THEN
UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
COMMIT;
SET p_result = 'OK';
ELSE
ROLLBACK;
SET p_result = 'INSUFFICIENT_FUNDS';
END IF;
END //
DELIMITER ;
# call with a session variable for the OUT value
CALL transfer(1, 2, 50.00, @res);
SELECT @res;変数と制御フロー
ストアドプログラムが、ローカル変数(DECLARE、BEGIN...END にスコープ)、ユーザー変数(@var、セッションスコープ)、システム変数をサポートします。制御フローは、IF/ELSEIF/ELSE、CASE、3 つのループを含みます:LOOP(LEAVE/ITERATE 付き)、WHILE(条件が最初)、REPEAT(条件が最後)です。ネストするときはループに名前を付け、LEAVE が正しいものを狙うようにしましょう。値の割り当てが SET で、クエリのスカラーを変数へコピーするのが SELECT ... INTO です。
DELIMITER //
CREATE PROCEDURE classify(IN p_age INT, OUT p_label VARCHAR(20))
BEGIN
DECLARE v_count INT DEFAULT 0;
# IF / ELSEIF / ELSE
IF p_age < 13 THEN
SET p_label = 'child';
ELSEIF p_age < 20 THEN
SET p_label = 'teen';
ELSE
SET p_label = 'adult';
END IF;
# simple loop with a counter
simple_loop: LOOP
SET v_count = v_count + 1;
IF v_count >= 5 THEN LEAVE simple_loop; END IF;
END LOOP;
# WHILE / REPEAT alternatives
# WHILE cond DO ... END WHILE;
# REPEAT ... UNTIL cond END REPEAT;
# CASE statement
CASE p_label
WHEN 'child' THEN SET p_label = CONCAT(p_label, '!');
ELSE SET p_label = CONCAT(p_label, '.');
END CASE;
END //
DELIMITER ;カーソル
ストアドプロシージャが結果セット上で行ごとに反復できるようにするのが、カーソルです。カーソルを先に宣言し、枯渇を検出する CONTINUE HANDLER FOR NOT FOUND を(フラグを設定しループを LEAVE)、FETCH が変数へ 1 行を取得します。MySQL ではカーソルは読み取り専用で前方のみです。セットベースの SQL と比べ、行ごとの処理は遅いため、可能な限り単一の UPDATE/INSERT ... SELECT を推奨しましょう。ロジックが本質的に逐次的なときだけカーソルを使います。
DELIMITER //
CREATE PROCEDURE log_all_users()
BEGIN
DECLARE v_id INT;
DECLARE v_name VARCHAR(50);
DECLARE done INT DEFAULT 0;
# declare the cursor BEFORE handlers
DECLARE cur CURSOR FOR
SELECT id, username FROM users WHERE status='active';
# continue handler for end of cursor -> set done=1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
FETCH cur INTO v_id, v_name;
IF done THEN LEAVE read_loop; END IF;
INSERT INTO audit_log(user_id, action) VALUES (v_id, CONCAT('seen:', v_name));
END LOOP;
CLOSE cur;
END //
DELIMITER ;
CALL log_all_users();エラーハンドラ
ストアドプログラム内のエラーを捉えるのがハンドラです:CONTINUE がハンドラの本文を実行し失敗したステートメントの後に再開し、EXIT が本文を実行し BEGIN...END ブロックを去り、UNDO がロールバックします(非推奨)。任意のエラーには SQLEXCEPTION を、警告には SQLWARNING を、または特定の SQLSTATE/errno(重複キーの 1062 など)を捉えましょう。後始末後の現在のエラーの再スローが RESIGNAL です。ハンドラを自己文書化するのが、名前付き条件(CONDITION FOR)です。
DELIMITER //
CREATE PROCEDURE safe_insert(IN p_email VARCHAR(255))
BEGIN
# SQLEXCEPTION catches any error; SQLSTATE/errno catch specific ones
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL; -- re-raise so the caller sees the error
END;
DECLARE CONTINUE HANDLER FOR 1062 -- duplicate key
BEGIN
SELECT 'Duplicate skipped' AS msg;
END;
START TRANSACTION;
INSERT INTO users (username, email) VALUES ('x', p_email);
INSERT INTO stats (email) VALUES (p_email);
COMMIT;
END //
DELIMITER ;
# named conditions make handlers readable
DECLARE dup_key CONDITION FOR 1062;
DECLARE CONTINUE HANDLER FOR dup_key SELECT 'dup';プロシージャのメタデータ
プロシージャの一覧が SHOW PROCEDURE STATUS で、完全な本文を返すのが SHOW CREATE PROCEDURE です。決定性とセキュリティタイプを含むより豊かなメタデータを与えるのが、information_schema.routines です。出力が入力だけに依存するなら、関数を DETERMINISTIC(READS SQL DATA / NO SQL)と印付けましょう — 関数のバイナリロギングとレプリケーションに必要です。プロシージャには決定論フラグが不要です。ルーチンの監査と棚卸しに、これらのビューを使いましょう。
# list procedures in a database
SHOW PROCEDURE STATUS WHERE Db = 'mydb';
# show definition
SHOW CREATE PROCEDURE get_user_by_id\G
# query information_schema for routines
SELECT routine_name, routine_type, created, last_altered,
is_deterministic, security_type
FROM information_schema.routines
WHERE routine_schema = 'mydb';
# list stored functions too
SELECT routine_name, routine_type
FROM information_schema.routines
WHERE routine_schema = 'mydb' AND routine_type='FUNCTION';関数
文字列関数
バイトを数えるのが LENGTH で、文字を数えるのが CHAR_LENGTH です(マルチバイトの utf8mb4 で重要)。1 起点なのが SUBSTRING で、負の開始が末尾から数えます。いずれかの引数が NULL なら CONCAT が NULL を返すため、NULL をスキップするには CONCAT_WS を使いましょう。区切りで分割し最初の N パートを保つのが SUBSTRING_INDEX です(負の N は右から保ちます )。BOTH/LEADING/TRAILING のバリアントを持ち、スペースだけではなく任意の文字をトリミングできるのが TRIM です。
# length & case
SELECT LENGTH('hello'), CHAR_LENGTH('héllo'); -- bytes vs chars
SELECT UPPER('abc'), LOWER('ABC');
# substring & position
SELECT SUBSTRING('MySQL', 3); -- 'SQL'
SELECT SUBSTRING('MySQL', 1, 2); -- 'My'
SELECT INSTR('MySQL', 'SQL'); -- 3
SELECT LOCATE('SQL', 'MySQL', 1); -- 3
# trim & pad
SELECT TRIM(' hi '), LTRIM(' hi'), RTRIM('hi ');
SELECT LPAD('5', 3, '0'), RPAD('5', 3, '-'); -- 005, 5--
# split & replace
SELECT REPLACE('a-b-c', '-', '/');
SELECT SUBSTRING_INDEX('a,b,c', ',', 2); -- 'a,b'
# concatenate with NULL-safe separator
SELECT CONCAT_WS(',', 'a', NULL, 'b'); -- 'a,b'数値関数
ゼロから離れて丸めるのが ROUND で、丸めずに桁を切るのが TRUNCATE です。互換的なのが MOD と % です。[0,1) の float を返すのが RAND() で、ORDER BY RAND() が、結果セット全体をソートするため、行のサンプリングの便利だが高コストな方法です — 大きなテーブルには、ランダムな PK 範囲かリザーバサンプリングを使いましょう。千の区切りを加え与えられた小数桁へ丸め、文字列を返すのが FORMAT です。
# rounding
SELECT ROUND(2.567, 1); -- 2.6
SELECT CEIL(2.1), FLOOR(2.9); -- 3, 2
SELECT TRUNCATE(2.567, 1); -- 2.5
# modular & power
SELECT MOD(17, 5), 17 % 5; -- 2, 2
SELECT POWER(2, 10); -- 1024
SELECT SQRT(16); -- 4
# random
SELECT RAND(); -- 0..1 float
SELECT FLOOR(RAND() * 100); -- 0..99 integer
SELECT * FROM users ORDER BY RAND() LIMIT 5; -- random sample (slow!)
# formatting
SELECT FORMAT(1234567.891, 2); -- '1,234,567.89'
SELECT ABS(-5), SIGN(-5); -- 5, -1日付と時刻関数
現在の日付/時刻を 1 回の呼び出しで返すのが NOW() です(1 つのステートメントをまたいで一貫しています)。日付/時刻のパートを返すのが CURDATE/CURTIME です。正統な日付の算術が、INTERVAL 付きの DATE_ADD/SUB で — 月/年の繰り上がりを正しく扱います。日数を返すのが DATEDIFF(date1 - date2)で、TIME を返すのが TIMEDIFF です。%Y %m %d %H %i %s の指定子を使うのが、DATE_FORMAT と STR_TO_DATE です(分は小文字の i、秒は s に注意)。
# current values
SELECT NOW(), CURDATE(), CURTIME(), UTC_TIMESTAMP();
# parts of a date
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW()), DAYNAME(NOW());
SELECT WEEK(NOW()), WEEKDAY(NOW()), DAYOFWEEK(NOW());
# arithmetic
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
SELECT DATE_SUB('2024-01-31', INTERVAL 1 MONTH); -- 2023-12-31
SELECT DATEDIFF('2024-12-31', '2024-01-01'); -- 365 days
SELECT TIMEDIFF('18:00:00', '09:30:00'); -- 08:30:00
# formatting
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
SELECT STR_TO_DATE('31/12/2024', '%d/%m/%Y');カスタム関数
単一のスカラー値を返し、(CALL するプロシージャと違い)式の中で使えるのが、ストアド関数です。特性を宣言しなければなりません:DETERMINISTIC(同じ入力が常に同じ出力、ステートメントベースのレプリケーションには必須)、と READS SQL DATA / NO SQL / MODIFIES SQL DATA のうち 1 つです。関数が、結果セットを返したりトランザクション制御を使うことはできません。純粋な計算には、NO SQL + DETERMINISTIC の組み合わせが最も安全です。
DELIMITER //
CREATE FUNCTION full_name(p_first VARCHAR(50), p_last VARCHAR(50))
RETURNS VARCHAR(101)
DETERMINISTIC
READS SQL DATA
BEGIN
RETURN CONCAT_WS(' ', p_first, p_last);
END //
DELIMITER ;
# use it like a built-in
SELECT id, full_name(first_name, last_name) FROM users;
# drop it
DROP FUNCTION IF EXISTS full_name;
# functions MUST declare their nature for binary logging:
# DETERMINISTIC - same inputs -> same output (required for replication)
# READS SQL DATA - reads but doesn't modify
# NO SQL - no SQL at all制御フロー関数
コンパクトな三項が IF() で、a が NULL なら b を返す(2 引数のみ)のが IFNULL(a,b)、N 引数へ一般化するのが COALESCE です。a=b のとき NULL を返すのが NULLIF(a,b) で、ゼロ除算の回避に便利です(x / NULLIF(y,0))。1/0 を返すのが ISNULL() であり、値ではないため IFNULL と混同しないようにしましょう。最も柔軟で移植可能な条件式が CASE で、任意の SQL 方言で機能します。
# IF(test, true_val, false_val) - ternary
SELECT IF(age >= 18, 'adult', 'minor') FROM users;
# IFNULL / NULLIF
SELECT IFNULL(email, 'no-email') FROM users; -- coalesce one
SELECT COALESCE(nick, email, username) FROM users; -- coalesce many
SELECT NULLIF(status, 'inactive'); -- NULL if equal, else status
# CASE as a function (see SELECT section for full CASE)
SELECT CASE WHEN x>0 THEN 'pos' WHEN x<0 THEN 'neg' ELSE 'zero' END;
# ISNULL() returns 1/0 (different from IFNULL!)
SELECT ISNULL(email) FROM users;キャストと変換
値の型を変えるのが CAST と CONVERT です — MySQL がさもなければインデックスを無効化する暗黙の変換を行うとき(文字列と数値の列の比較など)に不可欠です。よくあるニーズをカバーするのが、CAST(... AS DATE/DATETIME/SIGNED/UNSIGNED/CHAR/DECIMAL) です。注意:CAST('123abc' AS SIGNED) が、エラーを出すのではなく警告付きで 123 を返します。CAST が厳密な ISO 形式を期待するため、カスタムの日付形式のパースには STR_TO_DATE を使いましょう。
# CAST to a specific type
SELECT CAST('2024-01-15' AS DATE);
SELECT CAST(3.14 AS SIGNED); -- 3
SELECT CAST('123abc' AS SIGNED); -- 123 (warns in strict mode)
SELECT CAST(123 AS CHAR(10));
# CONVERT is the same thing with different syntax
SELECT CONVERT('2024-01-15', DATE);
SELECT CONVERT('hello' USING utf8mb4); -- charset conversion
# common: string -> date for comparison
SELECT * FROM orders
WHERE created_at >= CAST('2024-01-01' AS DATETIME);
# format a number as a zero-padded string
SELECT LPAD(CAST(id AS CHAR), 6, '0') FROM orders;トリガー
BEFORE / AFTER トリガー
INSERT/UPDATE/DELETE で自動的に発火するのがトリガーで、(NEW の値を修正し検証し中止できる)BEFORE か(ロギングのような副作用のための)AFTER です。影響を受けた行ごとに本文が 1 回動くのが FOR EACH ROW の意味で、行トリガーが MySQL がサポートする唯一の種類です。デフォルト化/正規化には BEFORE トリガーが良く、カスケードする非クリティカルな作業には AFTER トリガーです。トリガーが、発火するステートメントと同じトランザクションで動きます。
DELIMITER //
CREATE TRIGGER trg_users_before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
# normalize email to lowercase before saving
SET NEW.email = LOWER(NEW.email);
IF NEW.created_at IS NULL THEN
SET NEW.created_at = NOW();
END IF;
END //
DELIMITER ;
# AFTER INSERT trigger logs the event
CREATE TRIGGER trg_users_after_insert
AFTER INSERT ON users
FOR EACH ROW
INSERT INTO audit_log(table_name, row_id, action, at)
VALUES ('users', NEW.id, 'INSERT', NOW());監査トリガー(UPDATE)
トリガーのよくある使い方が変更の監査です — 履歴テーブルへ OLD 対 NEW を取り込みましょう。NULL を考慮した値の比較が必要です(どちらかの側が NULL なら OLD.email <> NEW.email は NULL)ので、IS NULL の確認で守りましょう。OLD を使うのが AFTER DELETE です(削除には NEW がありません)。行の変化ごとに動くため、監査のロジックは軽量に保ちましょう。トリガーが一括の UPDATE/DELETE でも発火することを覚えましょう。大きな操作では遅くなり得ます。
DELIMITER //
CREATE TRIGGER trg_users_after_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
IF OLD.email <> NEW.email OR (OLD.email IS NULL) <> (NEW.email IS NULL) THEN
INSERT INTO audit_log(table_name, row_id, field, old_val, new_val, at)
VALUES ('users', NEW.id, 'email', OLD.email, NEW.email, NOW());
END IF;
END //
DELIMITER ;
# a DELETE audit trigger
CREATE TRIGGER trg_users_after_delete
AFTER DELETE ON users
FOR EACH ROW
INSERT INTO archive_users(id, username, email, deleted_at)
VALUES (OLD.id, OLD.username, OLD.email, NOW());OLD と NEW の値
入ってくる行の値を保持するのが NEW で、以前のものが OLD です。データを正規化またはデフォルト化するため、BEFORE INSERT/UPDATE で NEW を修正でき、AFTER トリガーと DELETE は読み取り専用で見ます。トリガーやストアドプログラムからカスタムエラーを発生させる標準的な方法が、SIGNAL SQLSTATE '45000' です(45000 は「未処理のユーザー定義例外」を意味します)。変更を中止すべき検証には BEFORE トリガーを、副作用には AFTER を。
# OLD: the row before the change (available on UPDATE/DELETE)
# NEW: the row after the change (available on INSERT/UPDATE)
# BEFORE INSERT: only NEW, you can modify it
# AFTER INSERT: only NEW, read-only
# BEFORE UPDATE: both OLD and NEW, you can modify NEW
# AFTER UPDATE: both OLD and NEW, read-only
# BEFORE DELETE: only OLD, read-only
# AFTER DELETE: only OLD, read-only
CREATE TRIGGER trg_balance_check
BEFORE UPDATE ON accounts
FOR EACH ROW
BEGIN
IF NEW.balance < 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Balance cannot be negative';
END IF;
END;複数イベントのトリガー(FOLLOWS/PRECEDES)
5.7.2 以降、テーブルが同じイベントとタイミングのために複数のトリガーを持ち、FOLLOWS/PRECEDES で順序付けられます。各トリガーが依然、正確に 1 イベント(INSERT、UPDATE か DELETE)を 1 タイミング(BEFORE/AFTER)で扱うため — いくつかのイベントを扱うには複数のトリガーを作ります。トリガーが互いの効果に依存するとき順序が効きます。MySQL には INSTEAD OF トリガーがありません(ビューは直接更新可能です)。
# MySQL allows multiple triggers with the same event/timing on one table (5.7.2+)
# order them with FOLLOWS / PRECEDES
DELIMITER //
CREATE TRIGGER trg_users_after_insert_2
AFTER INSERT ON users
FOR EACH ROW
FOLLOWS trg_users_after_insert
BEGIN
UPDATE user_stats SET total = total + 1 WHERE user_id = NEW.id;
END //
DELIMITER ;
# events: INSERT | UPDATE | DELETE
# timing: BEFORE | AFTER
# a trigger cannot span multiple events; create one per eventトリガーの管理
すべてのトリガーを一覧するのが SHOW TRIGGERS です('Table' という列名が予約されているため、バッククォートの引用に注意しましょう)。完全な本文と定義者を返すのが、SHOW CREATE TRIGGER です。監査のための構造化されたメタデータを与えるのが information_schema.triggers です。スクリプトに安全なのが DROP TRIGGER IF EXISTS です。トリガーはテーブルに結びつくため、テーブルの削除がトリガーを落とします。定義者に注意しましょう:プロシージャと同様、トリガーがデフォルトで定義者の権限で動きます。
# list triggers
SHOW TRIGGERS\G
SHOW TRIGGERS WHERE `Table` = 'users';
# show definition
SHOW CREATE TRIGGER trg_users_before_insert\G
# query metadata
SELECT trigger_name, event_manipulation, action_timing,
event_object_table, created
FROM information_schema.triggers
WHERE trigger_schema = 'mydb';
# drop a trigger
DROP TRIGGER IF EXISTS trg_users_before_insert;
# triggers fire on the table's schema; definer is recordedトランザクションとロック
トランザクションの制御
MySQL はデフォルトで自動コミットします — 各ステートメントが自分のトランザクションです。COMMIT(保存)か ROLLBACK(取り消し)で終わる明示的なブロックを始めるのが、START TRANSACTION/BEGIN です。トランザクション的なエンジン(InnoDB)だけがトランザクションに対応し、MyISAM はしません。決定的に重要なのは、DDL のステートメント(CREATE/ALTER/DROP/TRUNCATE)が現在のトランザクションを暗黙にコミットし、ロールバックできないことです。したがって、原子性を期待して DDL とトランザクション的な DML を混ぜないようにしましょう。
# explicit transaction (autocommit is on by default)
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK;
# alternative syntax
BEGIN;
INSERT INTO orders (user_id, amount) VALUES (1, 50);
UPDATE users SET balance = balance - 50 WHERE id = 1;
COMMIT;
# turn autocommit off for the session
SET autocommit = 0;
-- every statement is now part of a transaction until COMMIT/ROLLBACK
SET autocommit = 1;
# DDL (CREATE/ALTER/DROP) implicitly commits — cannot be rolled backSavepoint
トランザクション全体を中断せず部分的にロールバックできる、トランザクション内の名前付きの点を印付けるのが SAVEPOINT です。セーブポイント後の作業を取り消すのが ROLLBACK TO SAVEPOINT で、マーカーを破棄するのが RELEASE SAVEPOINT です(作業は残ります)。省略可能なステップを試み、優雅に回復させられます。トランザクションの外には見えず、COMMIT/ROLLBACK で消えます。ネストしたセーブポイントもサポートされています。
START TRANSACTION;
INSERT INTO orders (amount) VALUES (10);
SAVEPOINT sp_after_order;
INSERT INTO order_items (order_id) VALUES (LAST_INSERT_ID());
SAVEPOINT sp_after_items;
-- something risky
UPDATE inventory SET stock = stock - 1 WHERE sku='X';
-- undo just the last step, keep the order + items
ROLLBACK TO SAVEPOINT sp_after_items;
-- release a savepoint when no longer needed
RELEASE SAVEPOINT sp_after_order;
COMMIT;分離レベル
一貫性と同時実行をトレードするのが分離レベルです。MySQL のデフォルトの REPEATABLE READ が、ダーティと非リピータブルリードを防ぎ、SQL 標準と違い、next-key ロック経由でほとんどのファントムリードも避けます。より低いロックのため人気だが非リピータブルリードを許すのが、READ COMMITTED です。すべての読み取りをロック読み取りへ変えるのが SERIALIZABLE です。InnoDB は MVCC を使うため、SERIALIZABLE を除くどのレベルでも、読み手が書き手をブロックしません。
# four levels, from least to most strict
# READ UNCOMMITTED - dirty reads possible (rarely used)
# READ COMMITTED - no dirty reads; non-repeatable reads possible
# REPEATABLE READ - default; consistent reads within a transaction
# SERIALIZABLE - locks everything; safest, slowest
# set for the session
SET SESSION transaction_isolation = 'REPEATABLE-READ';
# MySQL 8.0+ name; older: tx_isolation
# set globally (new connections inherit it)
SET GLOBAL transaction_isolation = 'READ-COMMITTED';
# inspect
SELECT @@transaction_isolation;
SELECT @@GLOBAL.transaction_isolation;LOCK TABLES
テーブルレベルのロックを取るのが LOCK TABLES で、ほとんど MyISAM 時代の機能です — InnoDB の行レベルのロックが通常より好ましいです。他のセッションが読めるが書き込みをブロックするのが READ ロックで、他の全員をブロックするのが WRITE ロックです。ロックを保持している間は、ロックされたテーブルにしかアクセスできません。LOCK TABLES がアクティブなトランザクションを暗黙にコミットするため、トランザクションと組み合いません。InnoDB の同時実行制御には、SELECT ... FOR UPDATE を推奨しましょう。
# explicit table locks (outside transactions; MyISAM-style)
LOCK TABLES users WRITE, orders READ;
-- only these tables are accessible; users is write-locked
SELECT * FROM users;
UNLOCK TABLES;
# READ lock: this session can read; others can read but not write
# WRITE lock: only this session can read/write; others blocked entirely
# in InnoDB prefer row-level locks (SELECT FOR UPDATE) over LOCK TABLES
# LOCK TABLES implicitly commits the active transactionSELECT ... FOR UPDATE / SHARE
排他的な行ロックを取り、他のトランザクションが同じ行を更新・ロックしようとするとブロックするのが SELECT ... FOR UPDATE です — 送金のような read-modify-write のシーケンスには不可欠です。共有ロックを取るのが FOR SHARE です(他は読めるが書けない)。ロックの解放は COMMIT/ROLLBACK でです。待つ代わりにエラーを出すのが NOWAIT と、ロックされた行をスキップするのが SKIP LOCKED を、8.0 が加えています — 互いをブロックすべきでない job-queue の worker に完璧です。
START TRANSACTION;
# lock the row so other transactions cannot modify it until commit
SELECT balance INTO @b FROM accounts WHERE id = 1 FOR UPDATE;
IF @b >= 100 THEN
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
END IF;
COMMIT; -- releases the lock
# FOR UPDATE: exclusive lock (write intent)
# FOR SHARE: shared lock (read intent, others can read)
# NOWAIT / SKIP LOCKED reduce waiting (8.0+)
SELECT * FROM jobs WHERE status='pending' LIMIT 1 FOR UPDATE SKIP LOCKED;デッドロックと検出
InnoDB がデッドロックを自動検出し、一方のトランザクション(犠牲者)をロールバックし他が進めるため — エラー 1213 が得られます。最後のデッドロックの表示が SHOW ENGINE INNODB STATUS で、すべてをログに残すのが、innodb_print_all_deadlocks の有効化です。デッドロックの予防は、トランザクションをまたいで一貫した順序で行をロックし、トランザクションを短く保ち、ロックする列をインデックスしましょう(さもないとロックが大きな範囲へエスカレートします)。デッドロックし得るトランザクションの周りには、必ずリトライのロジックを書きましょう。
# deadlock: two transactions each hold a lock the other needs
# Tx1: locks A, wants B
# Tx2: locks B, wants A
# -> InnoDB detects it and rolls back the victim automatically
# see the last deadlock
SHOW ENGINE INNODB STATUS\G
# log all deadlocks to the error log
SET GLOBAL innodb_print_all_deadlocks = 1;
# mitigate by:
# - locking tables/rows in a consistent order across transactions
# - keeping transactions short
# - adding appropriate indexes (avoid gap locks on full scans)
# - using a lower isolation level where safeユーザー管理
ユーザーの作成と認証
MySQL のアカウントは常に 'user'@'host' です — ホストがどこから接続できるかを制御し、任意のホストのワイルドカードが '%' です。一部の古いクライアントが対応しない、より安全な caching_sha2_password が MySQL 8.0 のデフォルトです — それらのためには mysql_native_password へフォールバックしましょう。'%' より狭くスコープされたホスト(10.0.0.%)を推奨しましょう。8.0 で削除された、古い GRANT ... IDENTIFIED BY 構文より CREATE USER が推奨です。