Skip to content

MySQL チートシート

人気のあるオープンソースのリレーショナルデータベース管理システム。

01

はじめに

MySQL への接続

MySQL の標準 CLI が mysql クライアントです。パスワードがシェルの履歴に見えるのではなく安全にプロンプトされるよう、必ず -p を(後ろにスペースを空けず)使いましょう。-h がホストを、-P(大文字)がポートを設定します。接続状態の表示は \s です。スクリプトで 1 回限りのクエリを実行するには -e を。

mysql
# 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 がすべてのテーブルとデータを即座に取り除きます。

mysql
# 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 のような幅広の行では、はるかに読みやすいです。

mysql
# 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 で、モニタリングに有用です。

mysql
# 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 です。

mysql
-- 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 を。

mysql
# /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]>\_
02

DDL 操作

テーブルの作成

CREATE TABLE が列、型、制約、テーブルオプションを定義します。デフォルトで推奨されるエンジンが InnoDB です(トランザクション、行レベルのロック、外部キーに対応します)。AUTO_INCREMENT のオーバーフローを避けるためには BIGINT UNSIGNED、金銭に正確なのは DECIMAL(p,s)、固定リストでの検証が ENUM、挿入時の自動充填が TIMESTAMP DEFAULT CURRENT_TIMESTAMP です。

mysql
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 です。

mysql
# 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 を無効化しましょう。

mysql
# 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 を。

mysql
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 です。管理しやすいよう制約に明示的な名前を付けましょう。外部キーには両方の列のインデックスが必要です。

mysql
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 にも書かれません。

mysql
# 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;
03

データ型

数値型

正確さが効く金銭の値には DECIMAL を使いましょう — FLOAT/DOUBLE は近似的で丸め誤差が蓄積します。正の範囲を 2 倍にしつつ負を禁じるのが UNSIGNED です。表示幅(INT(11) など)は MySQL 8.0 で非推奨になり無視されます — 保存範囲を制限したことは一度もありません。AUTO_INCREMENT のオーバーフローを避けるため推奨されるのが、BIGINT UNSIGNED です。代理キーの便利なショートカットが SERIAL です。

mysql
# 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 ファミリーで、デフォルト値を持てず、インデックスには接頭辞の長さが要ります。

mysql
# 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'; です。

mysql
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 range

ENUM と SET 型

固定リストの文字列値の 1 つをコンパクトに(1〜2 バイト)保存し、厳密モードで未知の値を拒否するのが ENUM です。最大 64 メンバーの任意の組み合わせをビットマスクとして保存するのが SET です。どちらも、スキーマ変更が高コストになり(メンバーの追加・並べ替えに ALTER TABLE が要る)、クエリも扱いにくいため、多くのチームが、ルックアップテーブルか CHECK 付きの VARCHAR を推奨します。SET の列のクエリには FIND_IN_SET が役立ちます。

mysql
# 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() を使いましょう。少数のキーの頻繁なクエリには、それらを生成列へ抽出しインデックスすれば、最高のパフォーマンスが得られます。

mysql
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() を加えています。

mysql
# 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;
04

DML(INSERT / UPDATE / DELETE)

INSERT

パース、ネットワークのラウンドトリップ、インデックスの更新を償却するため、複数行の INSERT が繰り返しの単一行の挿入よりはるかに速いです。テーブル間でデータをコピーするのが INSERT ... SELECT です。MySQL 固有で 1 行に便利なのが SET 構文です。ステートメントがスキーマ変更に耐えるよう、ターゲットの列は必ず明示的に列挙しましょう。冪等な挿入には、INSERT IGNORE か ON DUPLICATE KEY UPDATE を。

mysql
# 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 です。

mysql
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 を拒否します — うっかりの大規模変更への防御です。

mysql
# 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 句を使いましょう。

mysql
# 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 を推奨しましょう。

mysql
# 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 listed

LOAD DATA INFILE

CSV の一括ロードの最も速い方法が LOAD DATA INFILE です — SQL のパースをバイパスするため、INSERT ステートメントの 20 倍速いです。サーバーが安全なディレクトリ(secure_file_priv)からファイルを読み、LOCAL はクライアントがファイルを送りますが、セキュリティのためデフォルトで無効です。ロード中の値の変換には、@var で列を取り込み SET しましょう。対称的なエクスポートが SELECT ... INTO OUTFILE です。

mysql
# 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';
05

SELECT クエリ

基本の SELECT

本番では SELECT * を避けましょう — 不必要なデータを転送し、列が追加・削除されると壊れ、カバリングインデックスの利用をオプティマイザーに許しません。必要な列を名指ししましょう。OFFSET 付きの LIMIT がページネーションを実装しますが、大きなオフセットでは遅いです(スキップされた行を依然スキャンします)。はるかに良くスケールするのが、キーセットのページネーション(WHERE id > last_id LIMIT n)です。

mysql
# 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 を評価するため、混在した条件は括弧で包みましょう。最高のパフォーマンスのためには、最も選択的な条件を先に置き、インデックス付きの列が演算子の左に来るようにしましょう。

mysql
# 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 result

ORDER BY と LIMIT

結果のソートが ORDER BY で、複数のキーは左から右へ適用されます。列挙リストによるカスタム順序を可能にするのが FIELD() です。大きな結果セットでは、インデックスが対応しない限り、ORDER BY ... LIMIT が依然セット全体をスキャン・ソートし得ます — ORDER BY の列にインデックスを加えましょう。MySQL 8.0+ でのグループごとのトップ N の正統な解決策が、(ROW_NUMBER OVER PARTITION BY の)窓関数のパターンです。

mysql
# 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 は高コストになり得ます — 一時テーブルを構築するため、インデックスを推奨しましょう。

mysql
# 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 インデックスを。

mysql
# 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() です。

mysql
# 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;
06

JOIN

INNER JOIN

両方のテーブルに一致があるときだけ行を返すのが INNER JOIN です — どちらの側の一致しない行も落とされます。INNER JOIN の省略形が JOIN です。列名が同一で結果に 1 回現れるとき、ON a.col = b.col の簡潔な代替が USING (col) です。正規化されたテーブルを関連付けるための、デフォルトで最もよくある join のタイプが INNER JOIN です。

mysql
# 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 は決して一致しません。

mysql
# 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 のパターンです。

mysql
# 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 でインデントされたツリー出力を構築できます。

mysql
# 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
# 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 branch

NATURAL JOIN と USING

NATURAL JOIN は脆いです — 共有されたすべての列名で結合するため、列の追加が静かに結果を変え得ます。本番では避けましょう。より安全で、結合列を出力で 1 つのコピーへ折りたたむのが USING (col) です。オプティマイザーが使うテーブルの順序を強制するのが STRAIGHT_JOIN で、悪い計画の手動の上書きです。本物の問題が修正されたら取り除きましょう。明快さのため、明示的な ON を推奨します。

mysql
# 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;
07

集計とグループ化

集計関数

多くの行を 1 行へ折りたたむのが集計関数です:COUNT、SUM、AVG、MIN、MAX。行を数えるのが COUNT(*)、col の非 null の値を数えるのが COUNT(col)、一意の値を数えるのが COUNT(DISTINCT col) です。AVG が NULL を(ゼロではなく)無視するため結果が歪み得ます — NULL をゼロとして扱いたいなら COALESCE(col,0) を使いましょう。GROUP BY なしの集計が、空のテーブルでも単一の行を返します。

mysql
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() でグループ化します。

mysql
# 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 BY

HAVING

集計の結果をフィルタするのが HAVING で、グループ化の前に入力行をフィルタするのが WHERE です — そのため HAVING が集計(SUM、COUNT)を参照でき、WHERE はできません。効率のため、行レベルのフィルタは WHERE に置きましょう(グループ化の前に行を減らします)。MySQL では HAVING が別名を受け付けます(HAVING users > 100)。クエリは両方を使えます:WHERE が行を狭め、GROUP BY がバケット化し、HAVING がバケットを狭めます。

mysql
# 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 でエミュレートしましょう。

mysql
# 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 です。

mysql
# 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 ...) のクラシックなユースケースです。

mysql
# 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;
08

サブクエリと CTE

スカラーのサブクエリ

単一の値(1 行、1 列)を返すのがスカラーのサブクエリで、式が有効などこでも使えます — SELECT のリスト、WHERE、HAVING です。行を返さなければ、値は NULL です。オプティマイザーがしばしば、相関スカラーのサブクエリを結合へ変えます(サブクエリのマテリアライゼーション)。明快さのため(そして時にはパフォーマンスのため)、同じサブクエリが繰り返し参照されるなら、CTE か JOIN がより良いです。

mysql
# 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 column

IN / ANY / ALL のサブクエリ

サブクエリが返す任意の値に一致するのが IN で、補集合が NOT IN ですが、これは NULL に毒されます — サブクエリが NULL を返せば、NOT IN が行を全く返さないため、NULL を必ず除外するか NOT EXISTS を使いましょう。比較が返されたすべての値について成り立つとき ALL が真で、少なくとも 1 つで成り立つのが ANY/SOME です。オプティマイザーがこれらをしばしばセミ結合へ書き換えます。

mysql
# 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 をしばしばセミ結合へ変えます。

mysql
# 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)を推奨しましょう。

mysql
# 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+ ではそちらに手を伸ばしましょう。

mysql
# 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 内の以前のものを参照できます。

mysql
# 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);
09

インデックス

インデックスの作成

書き込みのオーバーヘッドとストレージを代償に、ルックアップとソートを速くするデータ構造(InnoDB では B+Tree)がインデックスです。WHERE、JOIN、ORDER BY で使われる列へインデックスを作りましょう。一意性の制約も強制するのが UNIQUE インデックスです。長いテキスト列の接頭辞インデックスがスペースを節約しますが、カバリングスキャンや ORDER BY には使えません。InnoDB ではすべてのプライマリキーがクラスタ化され — セカンダリインデックスが PK の値を行ポインタとして保存します。

mysql
# 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」を示す)。過剰なインデックスは避けましょう — すべてのインデックスが書き込みを遅くします。

mysql
# 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 のような専用エンジンを検討しましょう。

mysql
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 の慣例に一致するよう、座標は常に(経度, 緯度)として表現しましょう。半径のクエリが、空間インデックスで桁違いに速くなります。

mysql
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 を実行しましょう。

mysql
# 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 構文より推奨されるのが、オプティマイザーのヒント(/*+ ... */)で、タグ付けされたステートメントにだけ影響します。ヒントは控えめに使いましょう — 悪いデータ分布や欠けた統計のための絆創膏です。

mysql
# 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]';
10

ビュー

ビューの作成

仮想テーブルのように振る舞う保存されたクエリがビューです — 複雑なクエリを単純化し、スキーマ変更を抽象化し、行/列の可視性を強制します。1 ステップで定義を更新するのが CREATE OR REPLACE です。ビューはデータを保存しない(マテリアライズされない限り、基礎のクエリを毎回再実行します)ため、書き込みのコストを加えませんが読み取りの高速化もしません。可能な限り、ビューがクエリへマージされます。

mysql
# 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、サブクエリのあるビューは一般に更新可能ではありません。

mysql
# 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 を使いましょう。

mysql
# 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 で — 監査に有用です。ビューは基底テーブルに依存するため、基底テーブルの削除が、再作成されるまでビューを無効にします。

mysql
# 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 です。

mysql
# 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';
11

ストアドプロシージャ

プロシージャの作成と呼び出し

名前付きで事前コンパイルされた SQL のブロックがストアドプロシージャです。プロシージャがセミコロンを含め得るため、DELIMITER を変え、CREATE 全体が 1 つのステートメントとして実行され、その後リセットします。実行が CALL です。プロシージャは値を直接返しませんが、結果セットを返し OUT パラメータを修正できます。サーバー側で動くため、複数ステップのロジックのネットワークラウンドトリップを削りますが、アプリのコードよりバージョン管理とテストが難しいです。

mysql
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\G

IN / OUT / INOUT のパラメータ

パラメータは、IN(読み取り専用の入力)、OUT(セッション変数経由で呼び出し元へ書き戻す)、INOUT(両方)です。スカラーのクエリ結果を変数へ割り当てるのが SELECT ... INTO です。例が、SELECT FOR UPDATE で行をロックし失われた更新を防ぎつつ、トランザクションで借方/貸方を包みます。ステータスコードの返却には OUT パラメータを使い、プロシージャ内で SELECT すれば、結果セットは直接返されます。

mysql
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 です。

mysql
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 を推奨しましょう。ロジックが本質的に逐次的なときだけカーソルを使います。

mysql
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)です。

mysql
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)と印付けましょう — 関数のバイナリロギングとレプリケーションに必要です。プロシージャには決定論フラグが不要です。ルーチンの監査と棚卸しに、これらのビューを使いましょう。

mysql
# 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';
12

関数

文字列関数

バイトを数えるのが LENGTH で、文字を数えるのが CHAR_LENGTH です(マルチバイトの utf8mb4 で重要)。1 起点なのが SUBSTRING で、負の開始が末尾から数えます。いずれかの引数が NULL なら CONCAT が NULL を返すため、NULL をスキップするには CONCAT_WS を使いましょう。区切りで分割し最初の N パートを保つのが SUBSTRING_INDEX です(負の N は右から保ちます)。BOTH/LEADING/TRAILING のバリアントを持ち、スペースだけではなく任意の文字をトリミングできるのが TRIM です。

mysql
# 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 です。

mysql
# 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 に注意)。

mysql
# 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 の組み合わせが最も安全です。

mysql
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 方言で機能します。

mysql
# 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 を使いましょう。

mysql
# 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;
13

トリガー

BEFORE / AFTER トリガー

INSERT/UPDATE/DELETE で自動的に発火するのがトリガーで、(NEW の値を修正し検証し中止できる)BEFORE か(ロギングのような副作用のための)AFTER です。影響を受けた行ごとに本文が 1 回動くのが FOR EACH ROW の意味で、行トリガーが MySQL がサポートする唯一の種類です。デフォルト化/正規化には BEFORE トリガーが良く、カスケードする非クリティカルな作業には AFTER トリガーです。トリガーが、発火するステートメントと同じトランザクションで動きます。

mysql
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 でも発火することを覚えましょう。大きな操作では遅くなり得ます。

mysql
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 を。

mysql
# 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
# 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 です。トリガーはテーブルに結びつくため、テーブルの削除がトリガーを落とします。定義者に注意しましょう:プロシージャと同様、トリガーがデフォルトで定義者の権限で動きます。

mysql
# 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
14

トランザクションとロック

トランザクションの制御

MySQL はデフォルトで自動コミットします — 各ステートメントが自分のトランザクションです。COMMIT(保存)か ROLLBACK(取り消し)で終わる明示的なブロックを始めるのが、START TRANSACTION/BEGIN です。トランザクション的なエンジン(InnoDB)だけがトランザクションに対応し、MyISAM はしません。決定的に重要なのは、DDL のステートメント(CREATE/ALTER/DROP/TRUNCATE)が現在のトランザクションを暗黙にコミットし、ロールバックできないことです。したがって、原子性を期待して DDL とトランザクション的な DML を混ぜないようにしましょう。

mysql
# 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 back

Savepoint

トランザクション全体を中断せず部分的にロールバックできる、トランザクション内の名前付きの点を印付けるのが SAVEPOINT です。セーブポイント後の作業を取り消すのが ROLLBACK TO SAVEPOINT で、マーカーを破棄するのが RELEASE SAVEPOINT です(作業は残ります)。省略可能なステップを試み、優雅に回復させられます。トランザクションの外には見えず、COMMIT/ROLLBACK で消えます。ネストしたセーブポイントもサポートされています。

mysql
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 を除くどのレベルでも、読み手が書き手をブロックしません。

mysql
# 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 を推奨しましょう。

mysql
# 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 transaction

SELECT ... FOR UPDATE / SHARE

排他的な行ロックを取り、他のトランザクションが同じ行を更新・ロックしようとするとブロックするのが SELECT ... FOR UPDATE です — 送金のような read-modify-write のシーケンスには不可欠です。共有ロックを取るのが FOR SHARE です(他は読めるが書けない)。ロックの解放は COMMIT/ROLLBACK でです。待つ代わりにエラーを出すのが NOWAIT と、ロックされた行をスキップするのが SKIP LOCKED を、8.0 が加えています — 互いをブロックすべきでない job-queue の worker に完璧です。

mysql
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 の有効化です。デッドロックの予防は、トランザクションをまたいで一貫した順序で行をロックし、トランザクションを短く保ち、ロックする列をインデックスしましょう(さもないとロックが大きな範囲へエスカレートします)。デッドロックし得るトランザクションの周りには、必ずリトライのロジックを書きましょう。

mysql
# 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
15

ユーザー管理

ユーザーの作成と認証

MySQL のアカウントは常に 'user'@'host' です — ホストがどこから接続できるかを制御し、任意のホストのワイルドカードが '%' です。一部の古いクライアントが対応しない、より安全な caching_sha2_password が MySQL 8.0 のデフォルトです — それらのためには mysql_native_password へフォールバックしましょう。'%' より狭くスコープされたホスト(10.0.0.%)を推奨しましょう。8.0 で削除された、古い GRANT ... IDENTIFIED BY 構文より CREATE USER が推奨です。

mysql
# create a user that can connect from anywhere
CREATE USER 'app'@'%' IDENTIFIED BY 'StrongPass!2024';

# restrict to localhost only
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'localpass';

# MySQL 8.0 default plugin is caching_sha2_password
CREATE USER 'dev'@'10.0.%.%' IDENTIFIED WITH caching_sha2_password
  BY 'devpass';

# legacy mysql_native_password (needed for some older clients)
CREATE USER 'legacy'@'%' IDENTIFIED WITH mysql_native_password
  BY 'legacypass';

# rename or change host
RENAME USER 'app'@'%' TO 'appuser'@'10.0.0.%';

# drop a user
DROP USER IF EXISTS 'legacy'@'%';

GRANT 権限

4 つのスコープで権限を割り当てるのが GRANT です:グローバル(*.*)、データベース(db.*)、テーブル(db.table)、列/ルーチン。GRANT OPTION を除くすべてが ALL PRIVILEGES です。必要な最小限の権限を使いましょう — レポートのユーザーは SELECT しか受け取るべきではありません。ユーザーが自分の権限を他へ与えられるのが WITH GRANT OPTION です(危険です)。GRANT がメモリ内テーブルを更新するため FLUSH PRIVILEGES がめったに不要で、mysql のテーブルを手動編集した後にだけ使いましょう。

mysql
# grant all on a specific database
GRANT ALL PRIVILEGES ON mydb.* TO 'app'@'%';

# grant read-only on one table
GRANT SELECT ON mydb.users TO 'reporting'@'%';

# grant specific privileges
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'editor'@'localhost';

# grant the ability to grant to others (WITH GRANT OPTION)
GRANT SELECT ON mydb.* TO 'lead'@'%' WITH GRANT OPTION;

# grant at server level (use sparingly)
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'10.0.0.5';

# reload privilege tables into memory (usually automatic)
FLUSH PRIVILEGES;

REVOKE 権限

以前に付与された権限を取り除くのが REVOKE で、スコープは正確に一致させなければなりません。GRANT OPTION を含むすべてを剥ぎ取るには、REVOKE ALL PRIVILEGES, GRANT OPTION を使いましょう。取り消しはユーザーを落としません — アカウントが依然存在し接続できますが、再度付与されるまで権限がありません。ユーザーの実効的な権限の検査は SHOW GRANTS です。アクセスを変える前に ALWAYS で見直しましょう。ユーザーの完全な除去は DROP USER です。

mysql
# remove specific privileges
REVOKE INSERT, UPDATE ON mydb.* FROM 'editor'@'localhost';

# remove all privileges (but keep the user)
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app'@'%';

# revoke at a specific scope
REVOKE SELECT ON mydb.users FROM 'reporting'@'%';

# see what a user currently has
SHOW GRANTS FOR 'app'@'%';

# show grants for the current user
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER();

ロール(MySQL 8.0+)

ロール(8.0+)により、権限をグループ化し多くのユーザーへ一度に割り当てられ、アクセス管理が単純化します。ロールを付与されたユーザーは、ロールがアクティブ化されるまで権限を得ず — ログイン時にデフォルトでアクティブにするのが SET DEFAULT ROLE です。アクティブなロールの表示が CURRENT_ROLE() です。ロールを一度変えれば、すべてのメンバーユーザーが変化を継承します。各ユーザーへ個別に同じ権限を付与するより、はるかにきれいです。

mysql
# a role is a named bundle of privileges
CREATE ROLE 'app_read', 'app_write';

GRANT SELECT ON mydb.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON mydb.* TO 'app_write';

# grant a role to users
GRANT 'app_read' TO 'alice'@'%', 'bob'@'%';
GRANT 'app_write' TO 'carol'@'%';

# a user must activate a role to use it (default: none)
SET DEFAULT ROLE 'app_read' TO 'alice'@'%';
SET DEFAULT ROLE ALL TO 'alice'@'%';

# see active roles
SELECT CURRENT_ROLE();

パスワードの管理

パスワードを管理するモダンな方法が ALTER USER です(SET PASSWORD は非推奨です)。次回ログインでのリセットを強制するのが PASSWORD EXPIRE で — パスワードのローテーションポリシーに有用です。MEDIUM(大小混在、数字、特殊文字)と STRONG(辞書の単語もなし)で、長さ/複雑性を強制するのが、validate_password コンポーネントです。アカウントを落とさず一時的に無効化するのが、ACCOUNT LOCK/UNLOCK で — 調査中のアクセス停止に便利です。

mysql
# change your own password
ALTER USER USER() IDENTIFIED BY 'NewPass!2024';

# change another user's password
ALTER USER 'app'@'%' IDENTIFIED BY 'NewAppPass!2024';

# require a password to be expired (user must reset on next login)
ALTER USER 'app'@'%' PASSWORD EXPIRE;

# enforce password policy (8.0+ validate_password component)
INSTALL COMPONENT 'file://component_validate_password';
SET GLOBAL validate_password.policy = 'MEDIUM';  -- 0/LOW,1/MEDIUM,2/STRONG
SET GLOBAL validate_password.length = 12;

# lock an account temporarily
ALTER USER 'app'@'%' ACCOUNT LOCK;
ALTER USER 'app'@'%' ACCOUNT UNLOCK;

Show Grants とユーザー

すべてのアカウントを保持するシステムテーブルが mysql.user です — account_locked や password_expired のようなフラグと共に、ユーザーの棚卸しのためにクエリしましょう。1 ユーザーの権限のより親切でデコードされたビューが SHOW GRANTS です。権限チェックに使われる身元が CURRENT_USER() です(接続文字列の USER() とは異なり得ます)— ロールやプロキシを通して接続するときに重要です。休眠や過剰権限のアカウントのため、これらを定期的に監査しましょう。

mysql
# list all users (MySQL 8.0+)
SELECT user, host, account_locked, password_expired
FROM mysql.user;

# grants for a specific user
SHOW GRANTS FOR 'app'@'%';

# grants for the current session
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER();

# who am I?
SELECT CURRENT_USER(), USER();

# active roles for the current session
SELECT CURRENT_ROLE();

# list global privileges
SELECT user, host, Super_priv, Process_priv, Reload_priv
FROM mysql.user;
16

バックアップとリストア

mysqldump

クラシックな論理バックアップのツールが mysqldump です — データベースを再作成する SQL ステートメントを出力します。単一の REPEATABLE READ トランザクションを使い、ロックなしで一貫した InnoDB スナップショットを与えるのが、--single-transaction です。ストアドプログラムを(さもなければ省略される)含めるのが、--routines/--triggers/--events です。大きなデータベースには、mysqldump よりはるかに速い、Percona XtraBackup か MySQL Enterprise Backup(物理バックアップ)を推奨しましょう。

mysql
# dump a single database to a file
mysqldump -u root -p mydb > mydb.sql

# dump all databases
mysqldump -u root -p --all-databases > alldb.sql

# dump with routines, triggers and events
mysqldump -u root -p --routines --triggers --events mydb > mydb_full.sql

# dump schema only (no data) or data only
mysqldump -u root -p --no-data mydb > schema.sql
mysqldump -u root -p --no-create-info mydb > data.sql

# consistent dump of InnoDB in one transaction
mysqldump -u root -p --single-transaction mydb > mydb.sql

# compress on the fly
mysqldump -u root -p mydb | gzip > mydb.sql.gz

ダンプからのリストア

リストアするには、ダンプファイルを mysql クライアントへパイプするか、内側で SOURCE を使いましょう。ターゲットのデータベースは既に存在しなければなりません(CREATE DATABASE してから USE しましょう)。より速いリストアのため、ロード中は外部キーと一意のチェックと自動コミットを無効化し、その後再有効化しましょう — ダンプ自体が、通常これらのガードを既に含みます。gunzip による圧縮ダンプのリストアが、ディスクへの展開を避けます。ステージングサーバーでリストアを必ずテストしましょう。

mysql
# restore a database dump (database must exist)
mysql -u root -p mydb < mydb.sql

# restore all databases (creates them)
mysql -u root -p < alldb.sql

# restore a compressed dump
gunzip < mydb.sql.gz | mysql -u root -p mydb

# restore from inside the mysql client
SOURCE /path/to/mydb.sql;

# speed up restores: disable checks and autocommit during load
SET foreign_key_checks = 0;
SET unique_checks = 0;
SET autocommit = 0;
SOURCE /path/to/mydb.sql;
COMMIT;
SET foreign_key_checks = 1;
SET unique_checks = 1;

CSV へのエクスポート

サーバー側のファイルを書くのが SELECT ... INTO OUTFILE で、secure_file_priv のディレクトリに限定され(secure_file_priv が NULL なら完全に無効)、FILE 権限が要求されます — 既存のファイルを上書きできません。そのような制限なしに、ローカルでタブ区切りのファイルを作るのが、mysql クライアントの --batch --raw で、cron ジョブに理想的です。CSV 互換のための区切り、引用、エスケープを制御するのが、FIELDS/LINES 句です。

mysql
# server-side export to a file (needs FILE privilege + secure_file_priv)
SELECT id, username, email, created_at
FROM users
INTO OUTFILE '/var/lib/mysql-files/users.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n';

# client-side export with the mysql client (no FILE privilege needed)
mysql -u root -p -e "SELECT id, username, email FROM users" \
  --batch --raw mydb > users.tsv

# include column headers with a UNION
SELECT 'id','username','email' UNION
SELECT id, username, email FROM users
INTO OUTFILE '/var/lib/mysql-files/users.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

CSV からのインポート

CSV をロードする最も速い方法が LOAD DATA INFILE です — INSERT ステートメントよりはるかに速いです。サーバー側の形式が、secure_file_priv の下のパスから読み、LOCAL がクライアントのファイルを読みます(クライアントとサーバーの両方で local_infile を有効化)。ヘッダーのスキップが IGNORE 1 LINES です。値を変換するには(日付文字列のパースなど)、@var で列を取り込み SET しましょう。非常に大きなロードには、キー/外部キーを無効化し単一のトランザクションを使いましょう。

mysql
# server-side import (needs FILE privilege)
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
(id, username, email, @created_at)
SET created_at = STR_TO_DATE(@created_at, '%Y-%m-%d %H:%i:%s');

# client-side import (no FILE privilege, sends file from client)
LOAD DATA LOCAL INFILE 'C:/data/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES (id, username, email);

# enable LOCAL on the client:  --local-infile=1
# and on the server:  SET GLOBAL local_infile = 1;

バイナリログ(mysqlbinlog)

レプリケーションとポイントインタイムの回復のために、データを変えるすべてのステートメントを記録するのが、バイナリログです。現在のファイルと位置(レプリケーションの座標)を与えるのが、SHOW MASTER STATUS です。検査やリプレイのために binlog ファイルを SQL(または行イベント)へ戻すのが、mysqlbinlog で — 正確な瞬間へ回復するには、start/stop の時刻か位置と組み合わせましょう。ディスクの取り戻しが PURGE BINARY LOGS で、これを自動化するのが、expire_logs_days/binlog_expire_logs_seconds に任せることです。

mysql
# enable the binary log (my.cnf)
# [mysqld]
# log_bin = mysql-bin
# binlog_format = ROW
# server_id = 1

# show current binlog files and position
SHOW BINARY LOGS;
SHOW MASTER STATUS;

# view events in a binlog file
mysqlbinlog mysql-bin.000123

# filter by time / position
mysqlbinlog --start-datetime="2024-06-01 00:00:00" \
            --stop-datetime="2024-06-01 12:00:00" \
            mysql-bin.000123 > replay.sql

# replay events to recover
mysql -u root -p < replay.sql

# purge old binlogs
PURGE BINARY LOGS BEFORE '2024-06-01 00:00:00';

Clone プラグインとレプリケーション

論理のダンプよりはるかに速く InnoDB のデータディレクトリを物理的にコピーするのが、clone プラグイン(8.0+)で、レプリカのプロビジョニングやスナップショットに理想的です。レプリケーションのためには、ソースへレプリカを向かせましょう(CHANGE REPLICATION SOURCE TO、8.0+。旧 CHANGE MASTER TO)と START REPLICA。レプリケーションの位置決めを堅牢にするのが、GTID(SOURCE_AUTO_POSITION=1)です。遅延とエラーを明らかにするのが SHOW REPLICA STATUS です。レプリカの健康のため、Seconds_Behind_Source を監視しましょう。

mysql
# install the clone plugin (8.0+) for fast physical copy
INSTALL PLUGIN clone SONAME 'mysql_clone.so';

# clone a local data directory to another path
CLONE LOCAL DATA DIRECTORY = '/var/lib/mysql-clone';

# clone from a remote donor (good for provisioning replicas)
CLONE INSTANCE FROM 'donor'@'10.0.0.2':3306
  IDENTIFIED BY 'donorpass';

# set up replication from a donor
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='10.0.0.2', SOURCE_PORT=3306,
  SOURCE_USER='repl', SOURCE_PASSWORD='replpass',
  SOURCE_AUTO_POSITION=1;
START REPLICA;   -- 8.0+ (was START SLAVE)
SHOW REPLICA STATUS\G
17

パフォーマンスの最適化

EXPLAIN

クエリ計画を明らかにするのが EXPLAIN です:どのインデックスが使われるか、何行が推定されるか、filesort か一時テーブルが必要か。アクセス方法をランクするのが 'type' の列です — const/eq_ref が最良(単一行情報のルックアップ)、ref/range がインデックスを使い、index がインデックス全体をスキャンし、ALL がテーブル全体をスキャンします(通常悪いです)。カバリングのインデックスのみのスキャンが「Using index」であり、最適化すべき高コストな操作への警告が「Using filesort」/「Using temporary」です。

mysql
# show the execution plan for a query
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

# extended: extra info + warnings
EXPLAIN EXTENDED SELECT ...;
SHOW WARNINGS;

# format as a tree (8.0+) — more readable
EXPLAIN FORMAT=TREE SELECT ...;

# the key columns to read:
#   type        - access method (const > eq_ref > ref > range > index > ALL)
#   key         - the index chosen
#   rows        - estimated rows scanned
#   Extra       - 'Using index' (good) / 'Using filesort' / 'Using temporary'

EXPLAIN ANALYZE(8.0+)

EXPLAIN ANALYZE(8.0.17+)が実際にクエリを実行し、イテレータごとの本物の行数とタイミングを報告します — 時間が本当にどこで使われているか明らかにし、素の EXPLAIN の推定よりはるかに正確です。ステージングで SELECT クエリ上で使いましょう。オプティマイザーが書き直したクエリ(ビューのマージ、定数の折りたたみの後)を見るには、SHOW WARNINGS とペアにしましょう。書き込みクエリには、読み取り専用の等価物上で EXPLAIN するか、performance_schema で検査しましょう。

mysql
# actually runs the query and reports real per-step timing
EXPLAIN ANALYZE
SELECT u.username, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;

# output shows actual rows, loops and per-iterator ms:
# -> Table scan on users  (actual rows=1000, loops=1)
#     -> Covering index lookup on o  (actual rows=2.4, loops=1000)

# unlike EXPLAIN, this executes the statement (read-only queries only)

# also useful: SHOW WARNINGS after EXPLAIN to see the rewritten query
EXPLAIN SELECT ...;
SHOW WARNINGS\G

オプティマイザーのヒント

単一のステートメントのためにプランナーを上書きするのが、オプティマイザーのヒント(/*+ ... */)です。SET GLOBAL より狙いが定まっており、ステートメントの再起動にも耐えます。悪い計画を修正するのに使いましょう、しかし基礎の原因(古い統計、欠けたインデックス、歪んだデータ)も扱うこと — データが変わるとヒントは腐ります。暴走する SELECT からサーバーを守るのが、読み取り専用のクエリに上限を設ける MAX_EXECUTION_TIME です。1 つのステートメントだけのためにセッション変数を調整するのが、SET_VAR です。

mysql
# index hints (8.0+ style, after SELECT)
SELECT /*+ INDEX(u idx_email) */ *
FROM users u
WHERE email = '[email protected]';

SELECT /*+ NO_RANGE_OPTIMIZATION(u) */ *
FROM users u WHERE id BETWEEN 1 AND 1000;

# join order / algorithm hints
SELECT /*+ JOIN_ORDER(a,b,c) */ * FROM a JOIN b ON ... JOIN c ON ...;
SELECT /*+ HASH_JOIN(b) */ * FROM a JOIN b ON a.id=b.aid;
SELECT /*+ NLJ(b) */ * FROM a JOIN b ON a.id=b.aid;

# resource hints
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM big_table;
# aborts the query after 1000 ms (read-only)

# SET_VAR hint changes a variable for one statement
SELECT /*+ SET_VAR(sort_buffer_size=16M) */ ... FROM ...;

スロークエリログ

long_query_time(秒単位、秒未満も可)を超えるクエリを捉えるのが、スロークエリログです。速くてもフルスキャンを捉えるには、log_queries_not_using_indexes を有効化しましょう。似たクエリを集約し時間/数/行でランク付けするのが、mysqldumpslow で、最悪の犯人の優先付けができます。はるかに豊かなレポート(指紋、パーセンタイル)を与えるのが、Percona の pt-query-digest で — スローログ分析の標準的なツールです。ログは定期的にローテーションし掃除しましょう。

mysql
# enable in my.cnf
# [mysqld]
# slow_query_log = 1
# slow_query_log_file = /var/log/mysql/slow.log
# long_query_time = 2          # log queries slower than 2s
# log_queries_not_using_indexes = 1
# log_slow_admin_statements = 1

# enable at runtime (persists until restart)
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;

# analyze the slow log with mysqldumpslow
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
#   -s t sort by total time, -t 10 top 10
#   -s c count, -s r rows, -s l lock time

# pt-query-digest (Percona Toolkit) gives richer analysis
pt-query-digest /var/log/mysql/slow.log

SHOW PROFILE と Performance Schema

クエリをステージへ分解するのが SHOW PROFILE です(テーブルを開く、ソート、データ送信)— 便利ですが非推奨です。モダンな等価物が、8.0 でデフォルトで有効な低オーバーヘッドの計装フレームワーク performance_schema です。サーバーをまたいで最も多くの集計時間を消費するクエリを見つけるには、events_statements_summary_by_digest をクエリし、その後、ステージごとの詳細は events_stages_history_long へ掘り下げましょう。これが、単一の遅いクエリでなく、体系的なボトルネックを見つける方法です。

mysql
# profiling (deprecated but quick) - per-statement stage timings
SET profiling = 1;
SELECT COUNT(*) FROM huge_table;
SHOW PROFILE;
SHOW PROFILE FOR QUERY 1;

# preferred: performance_schema (8.0+, enabled by default)
# top queries by total latency
SELECT digest_text, count_star, sum_timer_wait/1e9 AS total_s
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

# which stage of which query took longest
SELECT event_name, format_time(timer_wait) AS took
FROM performance_schema.events_stages_history_long
ORDER BY timer_wait DESC LIMIT 10;

インデックス最適化のヒント

ほとんどのパフォーマンスの戦いに勝つのが、インデックスの設計です。最も左の接頭辞のルールに従い、先頭のワイルドカードを避け、インデックス付きの列を関数で包まない(または生成列 + インデックスを使う)ようにしましょう。ホットなクエリにはカバリングインデックスを目指します。インデックスが使えるよう、WHERE の条件は sargable(検索引数可能)に保ちましょう:DATE(created_at)=x より created_at >= x AND created_at < y です。オプティマイザーが依存するカーディナリティの統計を更新するのが、ANALYZE TABLE です。

mysql
# 1. index columns used in WHERE / JOIN / ORDER BY
#    with the most selective equality column first
CREATE INDEX idx_user_status ON orders(user_id, status, created_at);

# 2. avoid leading wildcards (defeats the index)
SELECT * FROM users WHERE name LIKE 'A%';   -- OK
SELECT * FROM users WHERE name LIKE '%A';   -- full scan

# 3. don't wrap indexed columns in functions
SELECT * FROM orders WHERE DATE(created_at)='2024-01-01';        -- bad
SELECT * FROM orders WHERE created_at >= '2024-01-01'
  AND created_at < '2024-01-02';                                  -- good

# 4. use a covering index so the query is index-only
EXPLAIN SELECT user_id, status FROM orders WHERE user_id=5;
-- 'Using index' in Extra means no table lookup

# 5. refresh statistics after big loads
ANALYZE TABLE orders;
18

JSON 操作

JSON の列と挿入

バイナリで検証された形式で JSON を保存するのが、JSON 型です。JSON の文字列リテラルで挿入するか、(キー/バリューのペアの)JSON_OBJECT() と JSON_ARRAY() で値を構築しましょう。無効な JSON は挿入時に拒否されます。JSON がパース済みのツリーとして保存されるため、メンバーアクセスは速いですが、文書全体が一緒に読まれます — 頻繁にクエリされるスカラー値には、生成列へ抽出しインデックスすれば、両方の最良を得られます。

mysql
CREATE TABLE products (
  id    INT PRIMARY KEY,
  name  VARCHAR(100),
  attrs JSON
);

# insert JSON literals
INSERT INTO products VALUES
  (1, 'Widget',
   '{"color":"red","size":42,"tags":["new","sale"],"stock":{"ny":5,"la":3}}'),
  (2, 'Gadget', '{"color":"blue","in_stock":true}');

# build JSON with functions
INSERT INTO products VALUES (3, 'Thing',
  JSON_OBJECT('color','green','tags',JSON_ARRAY('new')));

JSON_EXTRACT / -> / ->>

JSON のメンバーを取り出すのが ->(引用符付きの JSON を返す)で、引用符なしのテキストとして返すのが ->> です(比較には一般的な選択)。パスは、ルートに $.、メンバーに $.key、配列のインデックスに $.arr[0]、ネストに $.a.b を使います。キーの存在の確認が JSON_CONTAINS_PATH です。関数形式の -> が JSON_EXTRACT です。メンバーの値で効率的に文書を見つけるには、attrs->>'$.color' 上に生成列 + インデックスを加えましょう。

mysql
# extract a member (returns JSON)
SELECT JSON_EXTRACT(attrs, '$.color') FROM products WHERE id=1;   -- "red"
SELECT attrs->'$.color'   FROM products WHERE id=1;               -- "red"

# extract as text (unquoted)
SELECT attrs->>'$.color'  FROM products WHERE id=1;               -- red

# array element access
SELECT attrs->>'$.tags[0]' FROM products WHERE id=1;              -- new

# nested paths
SELECT attrs->>'$.stock.ny' FROM products WHERE id=1;             -- 5

# filter rows by a JSON value
SELECT id, name FROM products
WHERE attrs->>'$.color' = 'red';

# exists check
SELECT id FROM products
WHERE JSON_CONTAINS_PATH(attrs, 'one', '$.stock');

JSON_SET / INSERT / REPLACE / REMOVE

修正された JSON 文書を返すのがこれらの関数です — 列を更新するには、結果を代入し戻す必要があります。upsert が JSON_SET(追加か更新)で、欠けているものだけを加えるのが INSERT、既存だけを更新するのが REPLACE、削除が REMOVE です。2 つの文書をマージし null に設定されたキーを取り除くのが、JSON_MERGE_PATCH(RFC 7396)です — 部分的なパッチを適用する標準的な方法です。既存の配列/オブジェクトを置き換える代わりに保つのが、JSON_MERGE_PRESERVE です。

mysql
# SET updates or adds a member (returns the modified document)
UPDATE products
SET attrs = JSON_SET(attrs, '$.color', 'green', '$.weight', 1.2)
WHERE id = 1;

# INSERT adds only if the path doesn't exist
UPDATE products
SET attrs = JSON_INSERT(attrs, '$.code', 'W-100')
WHERE id = 1;

# REPLACE updates only if the path exists
UPDATE products
SET attrs = JSON_REPLACE(attrs, '$.size', 99)
WHERE id = 1;

# REMOVE deletes a member
UPDATE products
SET attrs = JSON_REMOVE(attrs, '$.tags[0]')
WHERE id = 1;

# MERGE_PATCH merges two JSON documents (8.0+)
SET attrs = JSON_MERGE_PATCH(attrs, '{"size":50,"color":null}')

JSON_TABLE(8.0+)

JSON からリレーショナルへの橋渡しとなるのが、JSON_TABLE(8.0+)です — 任意のテーブルのように結合、フィルタ、集計できる行へ、JSON 文書を粉砕します。JSON のパスを型付きの列へマッピングするのが、columns 句です。1 パスでネストした配列を展開するのが、NESTED PATH です。これにより、JSON の列が半構造化データに実用的になります:柔軟な JSON を保存し、SQL の分析が必要なときは JSON_TABLE でリレーショナルへ粉砕し、ホットな経路には生成列をインデックスしましょう。

mysql
# turn a JSON array of objects into relational rows
SELECT jt.*
FROM products p,
JSON_TABLE(p.attrs, '$.tags[*]'
  COLUMNS (
    tag VARCHAR(50) PATH '$'  -- each element as a column
  )
) AS jt
WHERE p.id = 1;

# flatten an array of objects
SELECT p.id, jt.color, jt.size
FROM products p,
JSON_TABLE(p.attrs, '$'
  COLUMNS (
    color VARCHAR(20) PATH '$.color',
    size  INT          PATH '$.size'
  )
) AS jt;

# nested arrays: NESTED PATH
JSON_TABLE(j, '$.stock.*'
  COLUMNS (warehouse VARCHAR(5) PATH '$.wh', qty INT PATH '$.qty'))

JSON の集計関数

グループごとに値を JSON 配列へ集めるのが JSON_ARRAYAGG で、キー→値のオブジェクトを構築するのが JSON_OBJECTAGG です(後のキーが前の重複を上書きします)。JSON_OBJECT と組み合わせれば、API のレスポンスのためのオブジェクトの配列を、SQL 内に直接構築できます — アプリでの後処理なしにネストしたペイロードを組み立てるのに便利です。NULL の入力はどちらもスキップされます。結果のサイズに注意しましょう:巨大なグループが巨大な JSON 値を作ります。

mysql
# JSON_ARRAYAGG: collect values into a JSON array
SELECT user_id,
  JSON_ARRAYAGG(email) AS emails
FROM contacts
GROUP BY user_id;
-- ["[email protected]","[email protected]"]

# JSON_OBJECTAGG: key/value pairs into a JSON object
SELECT user_id,
  JSON_OBJECTAGG(type, value) AS prefs
FROM user_prefs
GROUP BY user_id;
-- {"theme":"dark","lang":"en"}

# build a JSON array of objects with JSON_OBJECT + ARRAYAGG
SELECT JSON_ARRAYAGG(
  JSON_OBJECT('id', id, 'name', username)
) AS users
FROM users WHERE status='active';

JSON の検証とスキーマ

整形式の確認が JSON_VALID で(挿入が既にこれを強制します)、読みやすさのための再整形が JSON_PRETTY、バイトの報告が JSON_STORAGE_SIZE です。CHECK 制約で JSON Schema(必須のキー、型)を強制できるのが、JSON_SCHEMA_VALID(8.0.17+)です — 柔軟性を失わずに JSON の列へ構造を与える軽量な方法です。パスが欠け・無効なときの挙動を制御する ON EMPTY / ON ERROR の句を、JSON_TABLE と JSON_EXTRACT が受け付けます。

mysql
# validate a string is JSON
SELECT JSON_VALID('{"a":1}');          -- 1
SELECT JSON_VALID('{bad}');            -- 0

# pretty-print and minify
SELECT JSON_PRETTY(attrs) FROM products WHERE id=1;
SELECT JSON_STORAGE_SIZE(attrs) FROM products WHERE id=1;  -- bytes

# JSON_SCHEMA_VALID (8.0.17+) enforces a schema with CHECK
CREATE TABLE events (
  id INT PRIMARY KEY,
  data JSON,
  CHECK (JSON_SCHEMA_VALID(
    '{"type":"object","required":["ts","type"],
      "properties":{"ts":{"type":"string"},
                    "type":{"type":"string"}}}', data))
);

# JSON_TABLE with ERROR ON ERROR for strict validation
SELECT * FROM JSON_TABLE(j, '$' COLUMNS(v INT PATH '$.x')
  DEFAULT NULL ON EMPTY ERROR ON ERROR) AS t;
19

窓関数

ROW_NUMBER()

指定された順序で、パーティション内の各行へ一意の連続整数を割り当てるのが、ROW_NUMBER() です — ランク付け、重複除去、グループごとのトップ N の正統な方法です。RANK/DENSE_RANK と違い、同点を決して作りません。キーごとに 1 行を保ちつつ重複を除去する DELETE-via-CTE のパターンは(CTE から直接削除できないため、削除する ID を先に選びます)。窓関数には、MySQL 8.0+ が必要です。

mysql
# unique sequential number per row within a partition
SELECT username, country,
  ROW_NUMBER() OVER (PARTITION BY country ORDER BY created_at) AS rn
FROM users;

# top 3 oldest users per country
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY country ORDER BY birth_date) AS rn
  FROM users
)
SELECT * FROM ranked WHERE rn <= 3;

# deduplicate: keep the latest row per email
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY id DESC) AS rn
  FROM users
)
DELETE FROM users WHERE id IN (
  SELECT id FROM ranked WHERE rn > 1
);

RANK() と DENSE_RANK()

同点に同じランクを与え次のランクをスキップするのが RANK()(1,1,4,5)で、ギャップなしで同点に同じランクを与えるのが DENSE_RANK()(1,1,3,4)、決して同点にならないのが ROW_NUMBER()(1,2,3,4)です。意図により選びましょう:リーダーボード風のランキングは通常 RANK か DENSE_RANK を望みます。グループごとの N 番目に高いものを見つけるクラシックな方法が DENSE_RANK です(rank=2 が 2 番目の別の給与を返します)。3 つすべてが、同じ OVER() 構文を共有します。

mysql
# all three ranking functions on the same data
SELECT name, score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
  RANK()       OVER (ORDER BY score DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY score DESC) AS drnk
FROM players;

# with scores 100,100,90,80 the functions return:
#   rn: 1,2,3,4         (always sequential)
#   rnk:1,2,4,5         (ties share a rank, next rank skips)
#   drnk:1,2,3,4        (ties share a rank, no gap)

# find the 2nd-highest salary per department
WITH ranked AS (
  SELECT dept, salary,
    DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dr
  FROM employees
)
SELECT DISTINCT dept, salary FROM ranked WHERE dr = 2;

LAG() と LEAD()

自己結合なしに、現在の行に対する別の行を覗くのが、LAG() と LEAD() です — LAG が後ろを、LEAD が前を見ます。省略可能なオフセット(デフォルト 1)と、オフセットがパーティションの外へ落ちるときのデフォルト値(デフォルト NULL)を取ります。期間ごとの差分、動く比較、シーケンスの連続/中断の検出に完璧です。「前」が明確に定義されるよう、必ず ORDER BY を指定しましょう。

mysql
# compare each row to the previous/next row
SELECT day, sales,
  LAG(sales)  OVER (ORDER BY day) AS prev_day,
  sales - LAG(sales) OVER (ORDER BY day) AS diff,
  LEAD(sales) OVER (ORDER BY day) AS next_day
FROM daily_sales;

# with an explicit offset and default value
SELECT day, sales,
  LAG(sales, 7, 0) OVER (ORDER BY day) AS same_day_last_week
FROM daily_sales;

# year-over-year comparison
SELECT year, region, revenue,
  LAG(revenue) OVER (PARTITION BY region ORDER BY year) AS prev_year,
  revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY year) AS yoy
FROM annual_revenue;

窓集計(SUM / AVG OVER)

行を折りたたむのではなく、動く窓の行の上で集計を計算するのが、OVER() 付きの集計関数です。フレームなしだと、SUM OVER (ORDER BY x) が累計を与え、ROWS BETWEEN 6 PRECEDING AND CURRENT ROW が後続の 7 行の移動平均です。空の OVER() が、すべての行の上での総計を計算します。グループごとに窓をリセットするのが PARTITION BY です。フレームは、ROWS(物理的)か RANGE(値ベース)の境界を使います。

mysql
# running total over time
SELECT day, sales,
  SUM(sales) OVER (ORDER BY day) AS running_total
FROM daily_sales;

# moving average over a 7-day window
SELECT day, sales,
  AVG(sales) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS ma7
FROM daily_sales;

# each row's share of its group total
SELECT user_id, amount,
  amount / SUM(amount) OVER (PARTITION BY user_id) AS share
FROM orders;

# grand total alongside detail rows
SELECT username, balance,
  SUM(balance) OVER () AS total_balance
FROM users;

FIRST_VALUE / LAST_VALUE / NTH_VALUE

フレーム内の特定の行の値を返すのが、FIRST_VALUE/NTH_VALUE です。よくある落とし穴が LAST_VALUE です:デフォルトのフレームが RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW であるため、「last」が現在の行を意味し、パーティションの最後を意味しません — 本物の最後の値を得るには、フレームを UNBOUNDED FOLLOWING へ広げましょう。N 番目の行が現れるまで NULL を返すのが NTH_VALUE です。曖昧さを取り除くのが明示的なフレームで、常に指定する価値があります。

mysql
# first value in each partition (per ordering)
SELECT day, region, sales,
  FIRST_VALUE(sales) OVER (PARTITION BY region ORDER BY day) AS first_sale
FROM daily_sales;

# LAST_VALUE needs an explicit frame to include current row!
SELECT day, region, sales,
  LAST_VALUE(sales) OVER (
    PARTITION BY region ORDER BY day
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS latest_so_far
FROM daily_sales;

# the Nth value within a partition
SELECT day, region, sales,
  NTH_VALUE(sales, 3) OVER (
    PARTITION BY region ORDER BY day
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS third
FROM daily_sales;

NTILE と PERCENT_RANK

順序付けられた行を、できるだけ均等に n バケットへ分割するのが NTILE(n) です(割り切れないとき、前のバケットが余分な行を得ます)— レポートでの四分位数/十分位数に便利です。0..1 の分数としての相対ランクが PERCENT_RANK() で、累積分布(現在値以下の行の分数)が CUME_DIST() です。バケット化には NTILE を、パーセンタイル風の分析には PERCENT_RANK/CUME_DIST を使いましょう。

mysql
# divide rows into N equal buckets (1..N)
SELECT name, salary,
  NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;
-- quartile 1 = top 25%, quartile 4 = bottom 25%

# relative rank as a fraction 0..1
SELECT name, salary,
  PERCENT_RANK() OVER (ORDER BY salary) AS pct,
  CUME_DIST()    OVER (ORDER BY salary) AS cum
FROM employees;
-- pct  = (rank-1)/(N-1), 0 = lowest, 1 = highest
-- cum  = (rows up to and including current)/N

# CUME_DIST: fraction of rows with a value <= current
20

日付と時刻の処理

現在の日付/時刻関数

1 つのステートメントを通して同じ値を返すのが NOW()/CURRENT_TIMESTAMP です(一貫しています)。各呼び出しでの本物の時刻を反映するのが SYSDATE() です(避けましょう — 非決定的でレプリケーションを壊します)。UTC で(UTC_TIMESTAMP)タイムスタンプを保存し、表示時にローカル時刻へ変換しましょう。マイクロ秒の精度を与えるのが、NOW(6) です。エポック秒への/からの変換が UNIX_TIMESTAMP で — エポック時刻を使う言語との相互運用に便利です。

mysql
# current timestamp (date + time) in one call
SELECT NOW(), SYSDATE(), CURRENT_TIMESTAMP, LOCALTIME;

# current date / time separately
SELECT CURDATE(), CURRENT_DATE;     -- '2024-07-02'
SELECT CURTIME(), CURRENT_TIME;     -- '14:30:00'

# UTC equivalents (store UTC, display local)
SELECT UTC_TIMESTAMP(), UTC_DATE(), UTC_TIME();

# microsecond precision with (6)
SELECT NOW(6), CURRENT_TIMESTAMP(6);

# UNIX timestamp (seconds since epoch) and back
SELECT UNIX_TIMESTAMP();                 -- 1719912600
SELECT FROM_UNIXTIME(1719912600);        -- '2024-07-02 12:30:00'
SELECT FROM_UNIXTIME(1719912600, '%Y-%m-%d');

DATE_FORMAT と STR_TO_DATE

指定子を使い日付/時刻を文字列へ整形するのが DATE_FORMAT です — 小文字の %i(分)と %s(秒)に注意しましょう。よくある落とし穴です。逆の、文字列を DATE/TIME/DATETIME へパースするのが STR_TO_DATE です。ユーザー入力のパースには、形式設定に依存し脆い、暗黙の文字列から日付への変換に頼るのではなく、必ず STR_TO_DATE を使いましょう。日付は文字列ではなく DATE/DATETIME 型として保存しましょう。

mysql
# format a date/time to a string
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');   -- 2024-07-02 14:30:00
SELECT DATE_FORMAT(NOW(), '%d/%m/%Y');            -- 02/07/2024
SELECT DATE_FORMAT(NOW(), '%W, %M %e %Y');        -- Tuesday, July 2 2024

# parse a string into a date
SELECT STR_TO_DATE('02/07/2024', '%d/%m/%Y');     -- 2024-07-02
SELECT STR_TO_DATE('Jul 2, 2024 2:30 PM', '%b %e, %Y %h:%i %p');

# common specifiers:
#   %Y 4-digit year   %y 2-digit year
#   %m month(01-12)   %c month(1-12)   %M month name   %b abbreviated
#   %d day(01-31)     %e day(1-31)     %j day of year
#   %H hour(00-23)    %h hour(01-12)   %i minutes  %s seconds
#   %W weekday name   %a abbreviated   %p AM/PM

DATE_ADD / DATE_SUB と INTERVAL

正統な日付の算術が、INTERVAL 付きの DATE_ADD/DATE_SUB で、よりきれいなのが + INTERVAL / - INTERVAL の省略形です。月/年の繰り上がりを INTERVAL が正しく扱います(閏年の 1 月 31 日 + 1 か月 = 2 月 29 日)。引数の月の最後の日を返すのが LAST_DAY で — 月末のロジックに便利です。YEAR_MONTH のような複合単位が '1-2'(1 年 2 か月)を取ります。日付に文字列の計算を使ってはいけません — 端のケースで壊れます。

mysql
# add an interval to a date
SELECT DATE_ADD('2024-01-31', INTERVAL 1 MONTH);   -- 2024-02-29
SELECT DATE_ADD('2024-01-31', INTERVAL 1 DAY);     -- 2024-02-01
SELECT '2024-01-31' + INTERVAL 1 MONTH;            -- shorthand

# subtract
SELECT DATE_SUB(NOW(), INTERVAL 7 DAY);
SELECT NOW() - INTERVAL 1 HOUR;

# interval units: MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR
#   SECOND_MICROSECOND MINUTE_MICROSECOND ... YEAR_MONTH (compound)

# add to a datetime (keeps the time component)
SELECT DATE_ADD(NOW(), INTERVAL 30 MINUTE);

# last day of next month
SELECT LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH));

DATEDIFF / TIMESTAMPDIFF

丸 1 日単位を返すのが DATEDIFF です(date1 - date2、時刻を無視します)。選んだ単位での差を返しゼロ方向へ切り詰めるのが、TIMESTAMPDIFF です(unit, from, to)— そのため、誕生日が今年過ぎていなければ TIMESTAMPDIFF(YEAR, ...) が年齢を与えません。正確な年齢のためには、月/日がまだ来ていなければ 1 を引き算しましょう。引数の順序に注意しましょう:DATEDIFF は (a,b) = a-b、TIMESTAMPDIFF は (unit, from, to) = to-from です。

mysql
# DATEDIFF: difference in DAYS (date1 - date2)
SELECT DATEDIFF('2024-12-31', '2024-01-01');   -- 365
SELECT DATEDIFF(NOW(), birth_date) AS days_alive FROM users;

# TIMESTAMPDIFF: difference in a chosen unit (date1, date2) -> date2 - date1
SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age;
SELECT TIMESTAMPDIFF(MONTH, '2024-01-15', '2024-07-02');
SELECT TIMESTAMPDIFF(MICROSECOND, '2024-07-02 09:00:00', NOW());

# units: MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR
# (TIMESTAMPDIFF argument order is (unit, from, to))

# TIME values: TIMEDIFF returns a TIME
SELECT TIMEDIFF('18:00:00', '09:30:00');   -- 08:30:00

EXTRACT と日付のパート

単一の数値コンポーネントを返すのが EXTRACT(unit FROM date) で、よくあるものの省略形が YEAR()/MONTH()/DAY() など、名前を返すのが DAYNAME/MONTHNAME です。月次レポートの構築の標準的な方法が、タイムスタンプの YEAR()/MONTH() によるグループ化です。DAYOFWEEK が 1=日曜..7=土曜であるのに対し、WEEKDAY が 0=月曜..6=日曜であることに注意しましょう — あなたの慣例に一致する方を選びましょう。週の開始日とカウントのためのモードを、WEEK() が持ちます。

mysql
# pull out a single component
SELECT EXTRACT(YEAR FROM created_at)   AS yr,
       EXTRACT(MONTH FROM created_at)  AS mon,
       EXTRACT(DAY FROM created_at)    AS dy
FROM orders;

# dedicated functions for each part
SELECT YEAR(created_at), MONTH(created_at), DAY(created_at),
       HOUR(created_at), MINUTE(created_at), SECOND(created_at),
       DAYOFWEEK(created_at), DAYOFYEAR(created_at),
       WEEK(created_at), QUARTER(created_at);

# names instead of numbers
SELECT DAYNAME(created_at), MONTHNAME(created_at);

# group orders by month for a report
SELECT YEAR(created_at) AS yr, MONTH(created_at) AS mon,
       COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
GROUP BY yr, mon
ORDER BY yr, mon;

タイムゾーンの変換

TIMESTAMP の列がローカル時刻で表示されるよう、セッションの time_zone を設定しましょう。名前付きゾーン(Asia/Shanghai)には、mysql_tzinfo_to_sql 経由でシステムのタイムゾーンテーブルをロードする必要があります。ゾーン間でリテラルの datetime を変換するのが、CONVERT_TZ です。ベストプラクティス:すべての時刻を UTC で保存し(UTC での TIMESTAMP か DATETIME を使い)、表示のために各セッションの time_zone を設定し、必要なとき CONVERT_TZ で変換しましょう。これで、異なる地域のユーザーをまたぐ曖昧さが避けられます。

mysql
# see the session time zone
SELECT @@session.time_zone, @@global.time_zone;

# set the session time zone (UTC offset or named, needs tz data loaded)
SET time_zone = '+08:00';            # Asia/Shanghai offset
SET time_zone = 'Asia/Shanghai';     # named zone (load tz tables first)

# convert a datetime between time zones
SELECT CONVERT_TZ('2024-07-02 09:00:00', '+00:00', '+08:00');
-- 2024-07-02 17:00:00

# named zones require the timezone tables to be loaded:
#   mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

# TIMESTAMP columns auto-convert to the session time zone on display;
# DATETIME columns do not -- they store the literal value

# store all times in UTC and convert on the way out
SELECT CONVERT_TZ(created_at, '+00:00', @@session.time_zone)
FROM orders;

Was this helpful?