日付と時刻の関数は、本線の9時間ずれるのは全部同じ理由 — 日付と時刻の扱いで扱い済みです。 この記事では一覧と、索引に関わる注意だけを置いて、中身はそちらに任せます。
前回: GROUP BYと集計関数 — 付けたら行が減った これは深掘りです。本線の順路ではありません。 本線はプロセスを止めても消えない場所 — MySQLとSQLの基本の回から先へ進んでいます。この記事はその回の続きで、「組み込み関数」だけを1本かけて掘り直すものです。飛ばしても本線は繋がります。
作業場所はVMの中(Ubuntu Server)です。MySQL が動いていて、
appdbというデータベースがある状態から始めます。練習用のテーブルはこの記事の中で全部作ります。既存の表には触りません。
画面に「佐藤 太郎(さとちゃん)」と出したい。それだけでした。
SELECT CONCAT(last_name, ' ', first_name, '(', nickname, ')') AS 表示名 FROM members;
半分の行が、丸ごと空っぽになりました。
名前は入っているのに、表示名の列が NULL です。空文字でもなく、名字だけ出るのでもなく、行ごと NULL。
理由は、CONCAT は引数の中に1つでも NULL があると、結果を全部 NULL にするというルールでした。ニックネームを登録していない人の行で、nickname が NULL だった。それだけで、繋げた文字列がまるごと消えていました。
そして次に思ったのがこれです。
「こんな面倒なら、SQLでは生のまま取ってきて、整形は全部アプリ側でやればよくないか」
この判断が、この記事の本題です。
対象レベル: 中級〜上級 / 区分: 発展
判断の基準は「どこで一度だけやるか」
整形をSQLでやるか、アプリでやるか。好みの問題に見えて、基準は1つしかありません。
その処理は、どこで一度だけやれば済むか。
- メールアドレスを小文字に揃えるのは、保存するときに一度だけやれば済みます。読むたびに
LOWER()を呼ぶのは、同じ仕事を毎回やり直しているということです - 「1,234円」のようにカンマと単位を付けるのは、表示のときだけ必要です。データベースに入れる必要はありません。言語や地域が変わったら変わるものは、アプリ側です
NULLを「未設定」という文字に置き換えるのは、その結果を使う場所が1箇所ならアプリ、集計やソートの条件にも使うならSQLです
「SQLの関数を覚えるか、覚えないか」ではありません。 どこで一度やるかを決めるために、SQLでは何ができるのかを知っている必要がある、という順番です。
この記事は、その「何ができるのか」を、実際にありそうな汚いデータの上で動かします。
この記事でわかること
CONCATは引数に1つでもNULLがあると、結果が全部NULLになること。CONCAT_WSはNULLを飛ばすが、空文字は飛ばさないことLENGTHはバイト数、CHAR_LENGTHは文字数で、日本語では3倍違うこと。TRIMは全角スペースを消さないことROUNDは「四捨五入」で説明しきれないこと。2.5を丸めると、型によって3にも2にもなることNULLIFは「これと同じならNULLにする」関数で、ゼロ除算をエラーではなくNULLに変える定石だということWHEREの左辺に関数を書いた瞬間、その列の索引は使われなくなること。LOWER(email) = 'x'は1万行を全部読むこと。直し方は3通りあること
この記事でやること
- 練習用のテーブルを3つ作る(
d_fn_で始まる表)。前後に空白、全角半角の混在、NULL、大文字小文字のばらつきを、わざと入れる - 文字列関数と数値関数を、その汚いデータの上で動かして、きれいにする
CONCATとCONCAT_WSを並べて打ち、NULLの扱いの違いを目で見るCOALESCE/NULLIF/CASEでNULLとゼロ除算を潰す- 1万行の表を作り、
WHERE LOWER(email) = ...とWHERE email = ...をEXPLAINで比べて、3通りに直す
先に知る言葉
この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。
| 言葉 | 意味 |
|---|---|
| 組み込み関数 | データベースが最初から持っている計算の部品。 ROUND(1.5) のように 名前(引数) の形で呼ぶ |
| 引数(ひきすう) | 関数のカッコの中に渡す値。ROUND(1.5, 0) なら 1.5 と 0 の2つ |
| 照合順序(コレーション) | 文字を比べるときの規則。 「大文字と小文字を同じとみなすか」などがここで決まる |
| 索引(インデックス) | 本の巻末の索引と同じもの。 目的の行に、全部を読まずにたどり着くための仕組み |
EXPLAIN | その SELECT を、MySQL がどうやって実行するつもりかを見せる命令。 実際には実行しない |
| 生成列 | 他の列から計算して自動的に埋まる列。 自分で INSERT しない |
| キャスト | 値の型を、別の型として扱い直すこと。 文字列の '12' を数値の 12 にするなど |
① 準備 — 練習用のデータを作る
触るのは d_fn_ で始まる表だけにする
この記事で作る表には、全部 d_fn_ を付けます。 d_ は深掘り(deep)、fn_ は関数(function)のつもりです。
理由は、後片づけのときに「消していいもの」を目で見分けられるようにするためです。本線の記事で作った users / tasks / sessions には、この記事では一切触りません。
管理者で入る
VMの中(Ubuntu Server)で打ちます。
まずバージョンを見ておきます。この記事は MySQL 8.0 で確かめています。
SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.43 |
+-----------+
数字は違って構いません。 ただし 8. で始まっていない場合、後半の生成列と関数索引のところが動かないことがあります。
USE appdb;
表を3つ作る
1つ目は会員の表です。 わざと汚くします。
CREATE TABLE d_fn_member (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR(50) NOT NULL,
kana VARCHAR(100) DEFAULT NULL,
email VARCHAR(120) COLLATE utf8mb4_bin NOT NULL,
phone VARCHAR(20) DEFAULT NULL,
nickname VARCHAR(50) DEFAULT NULL,
zip VARCHAR(10) DEFAULT NULL,
dept_code INT DEFAULT NULL,
PRIMARY KEY (id),
KEY idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
COLLATE utf8mb4_binを付けています。 これは「大文字と小文字を別の文字として扱う」という指定です。後半の山場②で、大小を無視して検索するために必要になります。 いまは「1列だけ設定を変えてある」とだけ覚えておいてください。
2つ目は売上の表です。
CREATE TABLE d_fn_sale (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
member_id INT UNSIGNED NOT NULL,
amount DECIMAL(10,2) DEFAULT NULL,
qty INT NOT NULL DEFAULT 0,
rate DOUBLE NOT NULL DEFAULT 0,
ordered_at DATETIME NOT NULL,
PRIMARY KEY (id),
KEY idx_ordered_at (ordered_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
3つ目は、索引の実験だけに使う大きい表です。 中身は後で作ります。
CREATE TABLE d_fn_bulk (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
email VARCHAR(120) COLLATE utf8mb4_bin NOT NULL,
PRIMARY KEY (id),
KEY idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
データを入れる
ここが一番大事です。 きれいなデータを入れると、関数を使う理由が出てきません。わざと、次の4つを混ぜます。
- 名前の前後に空白(半角のものと、全角のもの)
- カナが全角と半角で混在
- メールアドレスの大文字小文字がばらばら
NULLと、空文字('')の両方
INSERT INTO d_fn_member
(last_name, first_name, kana, email, phone, nickname, zip, dept_code)
VALUES
('佐藤', '太郎', 'サトウ タロウ', 'Taro.Sato@Example.com', '090-1234-5678', NULL, '1500001', 10),
(' 鈴木', '花子 ', 'スズキ ハナコ', 'hanako@example.com', '09012345679', 'はなちゃん', '150-0002', 10),
('高橋', '一郎', NULL, 'ICHIRO@EXAMPLE.COM', NULL, NULL, '2310001', 20),
('田中', '次郎', 'タナカ ジロウ', 'jiro@Example.com', '03-1234-5678', '', '2310002', 20),
('伊藤', '三郎', 'イトウ サブロウ', 'saburo@example.com', '0312345678', 'さぶ', '0600001', 30),
('渡辺', '四郎', 'ワタナベ シロウ', 'shiro@example.com', NULL, NULL, '060-0002', NULL),
('山本', '五郎', 'ヤマモト ゴロウ', 'goro@Example.COM', '080-0000-0001', 'ごろ', '9200001', 30),
('中村', '六子', 'ナカムラ ムツコ', 'mutsuko@example.com', '080-0000-0002', NULL, '9200002', NULL),
(' 小林', '七海', 'コバヤシ ナナミ', 'nanami@example.com', NULL, NULL, '1000001', 40),
('加藤', ' 八郎', 'カトウ ハチロウ', 'hachiro@Example.com', '070-1111-2222', 'はち', '1000002', 40);
9行目の
' 小林'は、先頭が全角スペースです。 2行目の' 鈴木'は半角スペース。この2つは、後で違う結果になります。 4行目のnicknameはNULLではなく空文字('')です。 これも後で効いてきます。
売上のほうも入れます。
INSERT INTO d_fn_sale (member_id, amount, qty, rate, ordered_at) VALUES
(1, 12345.67, 3, 0.075, '2026-08-01 10:00:00'),
(1, 2500.00, 0, 0.075, '2026-08-01 23:30:00'),
(2, 999.50, 2, 0.100, '2026-08-15 09:15:00'),
(3, NULL, 1, 0.080, '2026-08-20 18:45:00'),
(4, 100.00, 7, 0.000, '2026-09-01 00:05:00'),
(5, -450.25, 2, 0.050, '2026-09-01 12:00:00'),
(7, 2.50, 1, 0.500, '2026-09-02 08:00:00'),
(8, 3.50, 4, 0.500, '2026-09-02 08:30:00');
2行目は
qtyが0、4行目はamountがNULL、6行目はamountが負の数です。全部わざとです。
入ったか確かめる
SELECT COUNT(*) AS members FROM d_fn_member;
SELECT COUNT(*) AS sales FROM d_fn_sale;
+---------+
| members |
+---------+
| 10 |
+---------+
+-------+
| sales |
+-------+
| 8 |
+-------+
10 と 8 が出れば準備完了です。
② 文字列を扱う関数
冒頭の事故を、そのまま再現する
まず、記事の最初に起きたことを目で見ます。
SELECT id,
nickname,
CONCAT(last_name, ' ', first_name, '(', nickname, ')') AS concat_result
FROM d_fn_member
WHERE id IN (1, 2, 4);
+----+--------------+-------------------------------+
| id | nickname | concat_result |
+----+--------------+-------------------------------+
| 1 | NULL | NULL |
| 2 | はなちゃん | 鈴木 花子 (はなちゃん) |
| 4 | | 田中 次郎() |
+----+--------------+-------------------------------+
3行とも、名字も名前も入っています。 それでも id = 1 だけ、結果が丸ごと NULL です。
CONCATは、引数に1つでもNULLがあると、結果全体をNULLにします。 これは仕様であって、バグでも設定でもありません。
id = 4 を見てください。空文字('')は消えません。 () という中身のないカッコがそのまま出ています。NULL と空文字は、別のものです。
id = 2 の 鈴木 花子 (...。「花子」の後ろに余計な空白があります。 データに 鈴木 と 花子 を入れたからです。これは後で TRIM で潰します。
CONCAT_WS — 区切り文字を先に決めて、NULL を飛ばす
CONCAT_WS の WS は With Separator(区切り文字つき)です。第1引数が区切り文字で、そこから後ろが繋ぐ値です。
SELECT id,
CONCAT(last_name, ' ', first_name, ' / ', nickname) AS by_concat,
CONCAT_WS(' / ', last_name, first_name, nickname) AS by_concat_ws
FROM d_fn_member
WHERE id IN (1, 2, 4);
+----+-------------------------------+------------------------------------+
| id | by_concat | by_concat_ws |
+----+-------------------------------+------------------------------------+
| 1 | NULL | 佐藤 / 太郎 |
| 2 | 鈴木 花子 / はなちゃん | 鈴木 / 花子 / はなちゃん |
| 4 | 田中 次郎 / | 田中 / 次郎 / |
+----+-------------------------------+------------------------------------+
3つ、読み取ることがあります。
id = 1:CONCAT_WSはNULLの引数を、無かったことにして飛ばします。 区切り文字も一緒に消えるので、佐藤 / 太郎 /のようにはなりません。CONCATのほうは、同じ行が丸ごとNULLですid = 4:空文字は飛ばしません。 だから両方とも末尾に/が残ります。CONCAT_WSが無視するのはNULLだけですid = 4のCONCATはNULLになっていません。nicknameが空文字であってNULLではないからです。CONCATが全体をNULLにするのは、引数にNULLがあるときだけです
まとめると、こうです。
CONCATにNULLが1つでも入れば、結果は必ずNULL。例外はありません。CONCAT_WSはNULLを飛ばす。ただし空文字は飛ばさない。
空文字も飛ばしたい — NULLIF を先取りする
CONCAT_WS は NULL しか飛ばしません。 では空文字も飛ばしたいときは、空文字を NULL に変えてから渡します。
SELECT id,
CONCAT_WS(' / ', last_name, first_name, NULLIF(nickname, '')) AS fixed
FROM d_fn_member
WHERE id IN (1, 2, 4);
+----+------------------------------------+
| id | fixed |
+----+------------------------------------+
| 1 | 佐藤 / 太郎 |
| 2 | 鈴木 / 花子 / はなちゃん |
| 4 | 田中 / 次郎 |
+----+------------------------------------+
NULLIF(nickname, '') は「nickname が空文字と同じなら NULL にする」という意味です。山場①で正面から扱います。 ここでは「そういう道具がある」とだけ。
TRIM — 前後の空白を落とす。ただし全角は落ちない
id = 2 の 鈴木 と 花子 が、まだ気持ち悪いままです。
SELECT id,
CONCAT('[', last_name, ']') AS raw,
CONCAT('[', TRIM(last_name), ']') AS trimmed
FROM d_fn_member
WHERE id IN (2, 9);
+----+------------+------------+
| id | raw | trimmed |
+----+------------+------------+
| 2 | [ 鈴木] | [鈴木] |
| 9 | [ 小林] | [ 小林] |
+----+------------+------------+
id = 2(半角スペース)は落ちました。id = 9(全角スペース)は落ちていません。
TRIMが何も指定せずに落とすのは、半角スペースだけです。 全角スペース(U+3000)は「ただの文字」なので、対象になりません。
目で見て分かりにくいので、文字数で確かめます。
SELECT id,
CHAR_LENGTH(last_name) AS len_raw,
CHAR_LENGTH(TRIM(last_name)) AS len_trimmed
FROM d_fn_member
WHERE id IN (2, 9);
+----+---------+-------------+
| id | len_raw | len_trimmed |
+----+---------+-------------+
| 2 | 3 | 2 |
| 9 | 3 | 3 |
+----+---------+-------------+
数字ではっきりします。 id = 9 は減っていません。
直し方は2つあります。
SELECT id, CHAR_LENGTH(TRIM(BOTH ' ' FROM last_name)) AS way1,
CHAR_LENGTH(TRIM(REPLACE(last_name, ' ', ' '))) AS way2
FROM d_fn_member
WHERE id IN (2, 9);
+----+------+------+
| id | way1 | way2 |
+----+------+------+
| 2 | 3 | 2 |
| 9 | 2 | 2 |
+----+------+------+
way1(TRIM(BOTH ' ' FROM ...))は、全角スペースだけを落とします。 だからid = 2(半角)が落ちませんway2(全角を半角に置換してからTRIM)は、両方落ちます
実務では way2 を使っています。 「どちらの空白が入っているか分からない」のが現実だからです。
LENGTH と CHAR_LENGTH — バイト数と文字数
ここが、日本語を扱う人が必ず1回踏む場所です。
SELECT kana,
LENGTH(kana) AS bytes,
CHAR_LENGTH(kana) AS chars
FROM d_fn_member
WHERE id IN (1, 2, 5, 6);
+--------------------+-------+-------+
| kana | bytes | chars |
+--------------------+-------+-------+
| サトウ タロウ | 19 | 7 |
| スズキ ハナコ | 19 | 7 |
| イトウ サブロウ | 24 | 8 |
| ワタナベ シロウ | 25 | 9 |
+--------------------+-------+-------+
・
LENGTHはバイト数を返します。 「その文字列がメモリ上で何バイトか」
・CHAR_LENGTHは文字数を返します。 「人が数えたら何文字か」
英数字だけなら、この2つは同じ数字になります。 だから英語圏のサンプルコードでは LENGTH が普通に使われていて、そのまま真似すると日本語で壊れます。
読み取れることが3つあります。
サトウ タロウ(半角カナ)とスズキ ハナコ(全角カナ)が、同じ19バイトです。「半角だから1バイト」ではありません。 UTF-8 では、半角カナも1文字3バイトですイトウ サブロウは8文字。 見えている全角スペースが、ちゃんと1文字として数えられていますワタナベ シロウは9文字。 数えるとワ タ ナ ヘ ゙で5文字です。半角カナの濁点(゙)は、独立した1文字です。「ベ」が2文字に化ける、というのはここから来ます
「なぜ1文字が3バイトなのか」は、文字コードの話です。 本線の文字化けは壊れたのではない — 文字コードとUTF-8で扱っています。この記事では「LENGTH を使うと事故る」という結論だけ持って帰ってください。
入力チェックで「名前は20文字まで」と書くときに使うのは、CHAR_LENGTH です。
SUBSTRING / LEFT / RIGHT — 切り出す
SELECT email,
LEFT(email, 4) AS head4,
RIGHT(email, 4) AS tail4,
SUBSTRING(email, 6, 3) AS mid
FROM d_fn_member
WHERE id = 1;
+-----------------------+-------+-------+------+
| email | head4 | tail4 | mid |
+-----------------------+-------+-------+------+
| Taro.Sato@Example.com | Taro | .com | Sat |
+-----------------------+-------+-------+------+
SUBSTRINGの開始位置は 1 から数えます。0 ではありません。 多くのプログラミング言語は 0 から数えるので、ここは毎回間違えます。
位置を数えて書くのは、現実には使いものになりません。 @ の位置は行ごとに違うからです。区切り文字で切りたいときは SUBSTRING_INDEX を使います。
SELECT email,
SUBSTRING_INDEX(email, '@', 1) AS local_part,
SUBSTRING_INDEX(email, '@', -1) AS domain
FROM d_fn_member
WHERE id IN (1, 3);
+-----------------------+------------+-------------+
| email | local_part | domain |
+-----------------------+------------+-------------+
| Taro.Sato@Example.com | Taro.Sato | Example.com |
| ICHIRO@EXAMPLE.COM | ICHIRO | EXAMPLE.COM |
+-----------------------+------------+-------------+
負の数を渡すと、後ろから数えます。 -1 で「最後の区切りから右」です。
UPPER / LOWER — 大小を揃える
SELECT DISTINCT SUBSTRING_INDEX(email, '@', -1) AS domain_raw,
LOWER(SUBSTRING_INDEX(email, '@', -1)) AS domain_lower
FROM d_fn_member;
+-------------+--------------+
| domain_raw | domain_lower |
+-------------+--------------+
| Example.com | example.com |
| example.com | example.com |
| EXAMPLE.COM | example.com |
| Example.COM | example.com |
+-------------+--------------+
同じドメインが4種類に見えています。 LOWER を通すと1つになります。
「ドメインごとに件数を数える」ような集計では、これをやらないと数字が割れます。 集計そのものはGROUP BYを付けたら行が減った — SQL深掘りで扱っています。
UPPER/LOWERが効くのは、基本的に半角の英字です。 日本語には効きません(効く必要もありません)。全角の英字(abc)がどうなるかは照合順序に依存するので、そこに頼った処理は書かないでください。
REPLACE / LPAD — 置き換えて、桁を揃える
郵便番号がばらばらです。 ハイフン付きと無し、先頭の 0 が落ちているように見えるもの。
SELECT id, zip FROM d_fn_member ORDER BY id;
+----+----------+
| id | zip |
+----+----------+
| 1 | 1500001 |
| 2 | 150-0002 |
| 3 | 2310001 |
| 4 | 2310002 |
| 5 | 0600001 |
| 6 | 060-0002 |
| 7 | 9200001 |
| 8 | 9200002 |
| 9 | 1000001 |
| 10 | 1000002 |
+----+----------+
まずハイフンを消して、7桁に揃えて、最後に表示用の形に組み直します。
SELECT id,
zip,
REPLACE(zip, '-', '') AS step1,
LPAD(REPLACE(zip, '-', ''), 7, '0') AS step2,
CONCAT(
LEFT(LPAD(REPLACE(zip, '-', ''), 7, '0'), 3),
'-',
RIGHT(LPAD(REPLACE(zip, '-', ''), 7, '0'), 4)
) AS formatted
FROM d_fn_member
WHERE id IN (1, 2, 5, 6);
+----+----------+---------+---------+-----------+
| id | zip | step1 | step2 | formatted |
+----+----------+---------+---------+-----------+
| 1 | 1500001 | 1500001 | 1500001 | 150-0001 |
| 2 | 150-0002 | 1500002 | 1500002 | 150-0002 |
| 5 | 0600001 | 0600001 | 0600001 | 060-0001 |
| 6 | 060-0002 | 0600002 | 0600002 | 060-0002 |
+----+----------+---------+---------+-----------+
LPAD(値, 桁数, 埋める文字) は、指定の桁数になるまで左を埋めます。 ここでは効いていませんが、zip を INT で持っていたら 600001 になっていて、LPAD が必要になります。
だから郵便番号を数値型で持ってはいけません。 計算しないものは文字列です。この判断はFLOATで金額を持つと1円ずれる — SQL深掘りの話とつながっています。
電話番号も同じ要領です。
SELECT phone, REPLACE(phone, '-', '') AS digits
FROM d_fn_member
WHERE id IN (1, 2, 3);
+---------------+-------------+
| phone | digits |
+---------------+-------------+
| 090-1234-5678 | 09012345678 |
| 09012345679 | 09012345679 |
| NULL | NULL |
+---------------+-------------+
REPLACE(NULL, ...)はNULLです。 文字列関数は、だいたいNULLを渡すとNULLを返します。 これはCONCATの話と同じ根っこです。
③ 数値を扱う関数
ROUND — 「四捨五入」では説明が足りない
まず、いちばん有名な落とし穴から行きます。
SELECT ROUND(2.5), ROUND(3.5), ROUND(-2.5), ROUND(25E-1), ROUND(35E-1);
+------------+------------+-------------+--------------+--------------+
| ROUND(2.5) | ROUND(3.5) | ROUND(-2.5) | ROUND(25E-1) | ROUND(35E-1) |
+------------+------------+-------------+--------------+--------------+
| 3 | 4 | -3 | 2 | 4 |
+------------+------------+-------------+--------------+--------------+
25E-1 は 2.5 です。 同じ 2.5 なのに、ROUND(2.5) は 3、ROUND(25E-1) は 2 になりました。
理由は型です。
・2.5と書くと、MySQL はDECIMAL(正確な数)として扱います。 このときROUNDは「0から遠いほうへ丸める」。だから 2.5 → 3、-2.5 → -3
・25E-1と書くとDOUBLE(近似の数)です。 このときROUNDはC言語のライブラリに任せます。多くの環境では「偶数側へ丸める」。だから 2.5 → 2、3.5 → 4
「四捨五入」という言葉は、この2つを区別しません。 だから、金額の計算を FLOAT / DOUBLE の列でやってはいけない理由がもう1つ増えます。丸め方すら環境に依存します。
DECIMAL で持っている列なら、素直な結果になります。
SELECT id, amount, ROUND(amount), ROUND(amount, 1), ROUND(amount, -3)
FROM d_fn_sale
WHERE id IN (1, 7, 8);
+----+----------+---------------+------------------+-------------------+
| id | amount | ROUND(amount) | ROUND(amount, 1) | ROUND(amount, -3) |
+----+----------+---------------+------------------+-------------------+
| 1 | 12345.67 | 12346 | 12345.7 | 12000 |
| 7 | 2.50 | 3 | 2.5 | 0 |
| 8 | 3.50 | 4 | 3.5 | 0 |
+----+----------+---------------+------------------+-------------------+
- 第2引数は「小数点以下の桁数」です。
1なら小数第1位まで - 負の数を渡すと、整数側を丸めます。
-3は千の位で丸め
2.50 が 3、3.50 が 4。 DECIMAL なので、両方とも「0から遠いほう」に行きました。四捨五入と同じ結果です。
FLOOR / CEIL / TRUNCATE — 負の数で正体が出る
この3つは、正の数だけ見ていると違いが分かりません。
SELECT 1.5 AS v, FLOOR(1.5) AS f, CEIL(1.5) AS c, TRUNCATE(1.5, 0) AS t
UNION ALL
SELECT -1.5, FLOOR(-1.5), CEIL(-1.5), TRUNCATE(-1.5, 0);
+------+----+----+----+
| v | f | c | t |
+------+----+----+----+
| 1.5 | 1 | 2 | 1 |
| -1.5 | -2 | -1 | -1 |
+------+----+----+----+
・
FLOORは「数直線の左へ」。 常に小さいほうへ。だから-1.5は-2
・CEILは「数直線の右へ」。 常に大きいほうへ。だから-1.5は-1
・TRUNCATEは「小数点以下を捨てる」。 大小は関係なく、0に近づく。だから-1.5は-1
正の数では FLOOR と TRUNCATE が同じ結果になります。 だから正のテストデータしか用意していないと、この差に気づけません。 練習データに負の数を1行入れたのは、これを見るためです。
SELECT id, amount, FLOOR(amount) AS f, TRUNCATE(amount, 0) AS t, ABS(amount) AS a
FROM d_fn_sale
WHERE id = 6;
+----+---------+------+------+--------+
| id | amount | f | t | a |
+----+---------+------+------+--------+
| 6 | -450.25 | -451 | -450 | 450.25 |
+----+---------+------+------+--------+
ABS は絶対値です。符号を落として大きさだけにします。「差が何円か」を出すときに使います。
MOD と % — 同じもの。0で割ると NULL
SELECT MOD(10, 3), 10 % 3, MOD(10, 0), 10 % 0, 10 / 0, 10 DIV 0;
+------------+--------+------------+--------+--------+----------+
| MOD(10, 3) | 10 % 3 | MOD(10, 0) | 10 % 0 | 10 / 0 | 10 DIV 0 |
+------------+--------+------------+--------+--------+----------+
| 1 | 1 | NULL | NULL | NULL | NULL |
+------------+--------+------------+--------+--------+----------+
MOD(a, b)とa % bは同じものです。割った余りを返します。 MySQL では、0で割ってもエラーになりません。NULLが返ります。
これは、良い話ではありません。
「エラーで止まる」なら、その場で気づけます。 NULL が返ると、そのまま次の計算に流れて、最後まで NULL のまま進みます。合計を取れば NULL の行は無視され、気づかないうちに件数が合わないという形で出てきます。
だから、割り算を書くときは「分母が0になる可能性」を毎回考える必要があります。 その道具が、次の山場です。
DIVは整数の割り算です。7 DIV 2は3。小数を捨てます。
④ NULL を扱う関数
ここまでで NULL が何度も出てきました。 文字列関数に渡せば NULL、割り算で0を踏めば NULL。NULL は、放っておくと全部を飲み込みます。
NULL そのものの性質(= NULL が真にならない、NOT IN に混ざると全部消える)は、WHEREで別名が使えない理由 — SQL深掘りで扱いました。ここでは「NULL を別の値に変える道具」を並べます。
IFNULL — 2つだけ
SELECT id, nickname, IFNULL(nickname, '未設定') AS shown
FROM d_fn_member
WHERE id IN (1, 2, 4);
+----+--------------+--------------+
| id | nickname | shown |
+----+--------------+--------------+
| 1 | NULL | 未設定 |
| 2 | はなちゃん | はなちゃん |
| 4 | | |
+----+--------------+--------------+
IFNULL(値, 代わりの値)。 第1引数が NULL なら第2引数を返します。
id = 4 に注目してください。 空文字は NULL ではないので、IFNULL は何もしません。 画面には何も表示されないのに「未設定」にもならない、という中途半端な状態になります。
COALESCE — 何個でも書ける
COALESCE は「引数を左から見ていって、最初に NULL でなかったものを返す」関数です。
SELECT id,
nickname,
first_name,
COALESCE(NULLIF(nickname, ''), first_name, '名無し') AS display_name
FROM d_fn_member
WHERE id IN (1, 2, 4);
+----+--------------+------------+--------------+
| id | nickname | first_name | display_name |
+----+--------------+------------+--------------+
| 1 | NULL | 太郎 | 太郎 |
| 2 | はなちゃん | 花子 | はなちゃん |
| 4 | | 次郎 | 次郎 |
+----+--------------+------------+--------------+
「ニックネームがあればそれ、無ければ名前、それも無ければ『名無し』」という優先順位が、1行で書けています。
IFNULLは引数2つ、COALESCEは何個でも。COALESCEは標準SQLにある名前なので、PostgreSQL でもそのまま動きます。IFNULLは MySQL 方言です。 迷ったらCOALESCEを使ってください。 覚える名前が1つ減ります。
COALESCE は、最初に NULL でないものが見つかった時点で、それより右は評価しません。
NULLIF — 「これと同じなら NULL にする」
COALESCE の逆向きの道具です。
SELECT NULLIF(5, 5), NULLIF(5, 3), NULLIF('', ''), NULLIF(0, 0);
+--------------+--------------+----------------+--------------+
| NULLIF(5, 5) | NULLIF(5, 3) | NULLIF('', '') | NULLIF(0, 0) |
+--------------+--------------+----------------+--------------+
| NULL | 5 | NULL | NULL |
+--------------+--------------+----------------+--------------+
NULLIF(a, b)は、aとbが等しければNULL、違えばaを返します。
「わざわざ NULL を作る関数」は、最初まったく用途が分かりませんでした。 使いどころは、はっきり2つあります。
用途1:空文字を NULL に寄せる。 さっき CONCAT_WS で使ったやつです。
用途2:ゼロ除算を消す。 こちらが定石です。
定石 — NULLIF でゼロ除算を避ける
「1個あたりの単価」を出したいとします。 amount / qty です。
SELECT id, amount, qty, amount / qty AS unit_price
FROM d_fn_sale
WHERE id IN (1, 2, 4);
+----+----------+-----+-------------+
| id | amount | qty | unit_price |
+----+----------+-----+-------------+
| 1 | 12345.67 | 3 | 4115.223333 |
| 2 | 2500.00 | 0 | NULL |
| 4 | NULL | 1 | NULL |
+----+----------+-----+-------------+
id = 2 は qty が 0 なので NULL。 MySQL は落ちませんが、NULL が結果に紛れ込みます。
しかも id = 4 の NULL(amount が未入力)と、見た目で区別が付きません。 原因が違うのに、同じ NULL として並んでいます。
「分母が0のときは 0 円と表示する」と決めたなら、こう書きます。
SELECT id, amount, qty,
COALESCE(amount / NULLIF(qty, 0), 0) AS unit_price
FROM d_fn_sale
WHERE id IN (1, 2, 4);
+----+----------+-----+-------------+
| id | amount | qty | unit_price |
+----+----------+-----+-------------+
| 1 | 12345.67 | 3 | 4115.223333 |
| 2 | 2500.00 | 0 | 0.000000 |
| 4 | NULL | 1 | 0.000000 |
+----+----------+-----+-------------+
読み方はこうです。
NULLIF(qty, 0)で、qtyが 0 ならNULLにするamount / NULLはNULL(0で割ったのではなく、NULLで割った)COALESCE(..., 0)で、そのNULLを 0 に置き換える
なぜ
NULLIFを挟むのか。 MySQL は 0 で割ってもNULLを返すので、MySQL だけを見ているとNULLIFは要らないように見えます。 要る理由は、他のデータベースでは 0 除算がエラーで止まるからです。PostgreSQL はdivision by zeroで落ちます。NULLIFを挟んでおけば、どちらでも同じ結果になります。
「MySQLだけで動けばいい」なら省けます。 ただし、省いた式は、移すときに黙って壊れます。
IF — 3つ書くだけの分岐
SELECT id, amount,
IF(amount IS NULL, '未入力', '入力あり') AS state
FROM d_fn_sale
WHERE id IN (1, 4);
+----+----------+--------------+
| id | amount | state |
+----+----------+--------------+
| 1 | 12345.67 | 入力あり |
| 4 | NULL | 未入力 |
+----+----------+--------------+
IF(条件, 真のとき, 偽のとき)。 分岐が2つだけならこれで足ります。
IFは MySQL 方言です。 標準SQLにはIF式がありません。移植を考えるならCASEを使ってください。
CASE — 書き方が2つある
CASE には、見た目の違う2つの書き方があります。 これが混乱の元です。
書き方1:単純CASE。 CASE の直後に、比べる対象を1つ書きます。
SELECT id, dept_code,
CASE dept_code
WHEN 10 THEN '営業部'
WHEN 20 THEN '開発部'
WHEN 30 THEN '管理部'
ELSE 'その他'
END AS dept_name
FROM d_fn_member
WHERE id IN (1, 3, 6, 9);
+----+-----------+-----------+
| id | dept_code | dept_name |
+----+-----------+-----------+
| 1 | 10 | 営業部 |
| 3 | 20 | 開発部 |
| 6 | NULL | その他 |
| 9 | 40 | その他 |
+----+-----------+-----------+
これは「dept_code が 10 と等しいか」を順に見ていく形です。 等しいかどうかしか判定できません。
書き方2:検索CASE。 CASE の直後に何も書かず、WHEN に条件式を丸ごと書きます。
SELECT id, amount,
CASE
WHEN amount IS NULL THEN '未入力'
WHEN amount < 0 THEN '返金'
WHEN amount < 1000 THEN '少額'
WHEN amount < 10000 THEN '通常'
ELSE '高額'
END AS grade
FROM d_fn_sale
ORDER BY id;
+----+----------+-----------+
| id | amount | grade |
+----+----------+-----------+
| 1 | 12345.67 | 高額 |
| 2 | 2500.00 | 通常 |
| 3 | 999.50 | 少額 |
| 4 | NULL | 未入力 |
| 5 | 100.00 | 少額 |
| 6 | -450.25 | 返金 |
| 7 | 2.50 | 少額 |
| 8 | 3.50 | 少額 |
+----+----------+-----------+
単純CASE は「等しいか」だけ。検索CASE は条件式が何でも書けます。 範囲、
IS NULL、LIKE、複数条件のAND。 迷ったら検索CASEです。
WHEN は上から順に見て、最初に当てはまったところで止まります。 上の例で amount = -450.25 は < 1000 にも当てはまりますが、< 0 が先にあるので「返金」になります。順番に意味があります。
単純CASE で NULL は絶対に当たらない
これが CASE の一番の罠です。
SELECT id, dept_code,
CASE dept_code
WHEN 10 THEN '営業部'
WHEN NULL THEN '未所属'
ELSE 'その他'
END AS wrong
FROM d_fn_member
WHERE id IN (1, 6);
+----+-----------+-----------+
| id | dept_code | wrong |
+----+-----------+-----------+
| 1 | 10 | 営業部 |
| 6 | NULL | その他 |
+----+-----------+-----------+
dept_code は NULL なのに、「未所属」になりません。
単純CASE は、内部で
dept_code = NULLという比較をしています。= NULLは、真にも偽にもなりません(「不明」になります)。 だから、どう転んでも当たりません。
直し方は、検索CASEに書き換えて IS NULL を使うことです。
SELECT id, dept_code,
CASE
WHEN dept_code IS NULL THEN '未所属'
WHEN dept_code = 10 THEN '営業部'
ELSE 'その他'
END AS right_way
FROM d_fn_member
WHERE id IN (1, 6);
+----+-----------+-----------+
| id | dept_code | right_way |
+----+-----------+-----------+
| 1 | 10 | 営業部 |
| 6 | NULL | 未所属 |
+----+-----------+-----------+
NULL の判定を含むなら、単純CASE は使えません。 これは覚えるしかない決まりです。
ELSE を書かないとどうなるか
SELECT id, dept_code,
CASE dept_code WHEN 10 THEN '営業部' END AS no_else
FROM d_fn_member
WHERE id IN (1, 3);
+----+-----------+---------+
| id | dept_code | no_else |
+----+-----------+---------+
| 1 | 10 | 営業部 |
| 3 | 20 | NULL |
+----+-----------+---------+
ELSEを省くと、どれにも当たらなかった行はNULLになります。 エラーにはなりません。 だから「たまにNULLが混ざる列」が静かにできます。
ELSE は、要らないと分かっていても書いてください。 書かない理由がありません。
⑤ 日付と時刻の関数 — 一覧と、1つの注意
日付と時刻の中身は、本線の9時間ずれるのは全部同じ理由 — 日付と時刻の扱いで扱い済みです。 タイムゾーン、DATETIME と TIMESTAMP の違い、サマータイム。この記事では繰り返しません。
ここでは、関数の一覧と、この記事の文脈で必要な注意だけを置きます。
| 関数 | 何をするか |
|---|---|
NOW() | いまの日時(2026-09-19 10:30:00 の形) |
CURDATE() | いまの日付だけ |
DATE(式) | 日時から日付部分だけを取り出す |
DATE_FORMAT(式, 書式) | 表示用の文字列に整える('%Y年%m月%d日' など) |
DATE_ADD(式, INTERVAL n 単位) | 足す(INTERVAL 7 DAY など)。引くのは DATE_SUB |
DATEDIFF(a, b) | 日数の差 |
TIMESTAMPDIFF(単位, a, b) | 指定の単位での差(HOUR / MONTH など) |
EXTRACT(単位 FROM 式) | 年だけ・月だけを取り出す |
日時の列を関数でくるむと、索引が効かなくなる
「2026年9月1日の売上を出す」を、こう書きたくなります。
SELECT COUNT(*) FROM d_fn_sale WHERE DATE(ordered_at) = '2026-09-01';
動きます。結果も正しいです。 ですが、ordered_at に張った索引は使われません。
索引を使いたいなら、列そのものを条件に書きます。
SELECT COUNT(*) FROM d_fn_sale
WHERE ordered_at >= '2026-09-01 00:00:00'
AND ordered_at < '2026-09-02 00:00:00';
「以上」と「未満」で挟むのが定石です。
BETWEEN '2026-09-01' AND '2026-09-02'は書かないでください。BETWEENは両端を含むので、9月2日の 00:00:00 ちょうどの行が入ってしまいます。
なぜ関数でくるむと索引が効かないのか。 それが次の山場です。
⑥ WHERE に関数を書くと索引が効かない
小さい表では確かめられない
いま d_fn_member は10行です。 10行の表では、MySQL は索引を使わずに全部読んだほうが速いと判断します。正しい判断ですが、実験にはなりません。
だから1万行の表を作ります。
1万行を作る
種になる小さい表を作って、それを4回掛け合わせます。
CREATE TABLE d_fn_seed (n INT NOT NULL);
INSERT INTO d_fn_seed (n) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
INSERT INTO d_fn_bulk (email)
SELECT CONCAT(a.n * 1000 + b.n * 100 + c.n * 10 + d.n, '-user@Example.com')
FROM d_fn_seed a, d_fn_seed b, d_fn_seed c, d_fn_seed d;
Query OK, 10000 rows affected
Records: 10000 Duplicates: 0 Warnings: 0
FROM a, b, c, dは、4つの表の全組み合わせを作る書き方です。10 × 10 × 10 × 10 で1万通り。この繋ぎ方の意味はJOINは表を繋いでいない — SQL深掘りで扱っています。 ここでは「1万行を手軽に作る道具」として使っているだけです。
入ったか見ます。
SELECT COUNT(*) FROM d_fn_bulk;
SELECT * FROM d_fn_bulk WHERE id IN (1, 4243);
+----------+
| COUNT(*) |
+----------+
| 10000 |
+----------+
+------+-----------------------+
| id | email |
+------+-----------------------+
| 1 | 0-user@Example.com |
| 4243 | 4242-user@Example.com |
+------+-----------------------+
統計情報を最新にしておきます。 これをやらないと EXPLAIN の行数がでたらめな数字になります。
ANALYZE TABLE d_fn_bulk;
比べる — 関数を使わない場合
EXPLAIN SELECT * FROM d_fn_bulk WHERE email = '4242-user@Example.com';
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | d_fn_bulk | ref | idx_email | idx_email | 482 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+
読むのは3箇所です。
typeがref— 索引をたどっているkeyがidx_email— どの索引を使ったかrowsが1— 1行だけ見れば済むと分かっている
比べる — LOWER() でくるんだ場合
EXPLAIN SELECT * FROM d_fn_bulk WHERE LOWER(email) = '4242-user@example.com';
+----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | d_fn_bulk | ALL | NULL | NULL | NULL | NULL | 9825 | 10.00 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------------+
同じ1行を取るのに、rows が 1 から 9825 になりました。
typeがALL— 全行読むという意味keyがNULL— 索引を1つも使っていないpossible_keysもNULL— 使える索引の候補すら無いと判断されている
なぜこうなるのか。 索引に入っているのは
4242-user@Example.com)です。LOWER(email)の値は、どこにも保存されていません。 MySQL はLOWER()の結果を知るために、1行ずつ読んで計算するしかありません。 それが「全行読む」ということです。
電話帳で言えば、こうです。 「佐藤さん」は五十音順の索引で引けます。「名前をひらがなに直したら『さとう』になる人」は、全員分を1人ずつ直してみないと分かりません。
rows の 9825 は概算です。 統計情報からの見積もりなので、ちょうど10000にはなりません。桁が合っていれば読み取りとしては十分です。
時間でも見ておきます。
SELECT COUNT(*) FROM d_fn_bulk WHERE email = '4242-user@Example.com';
SELECT COUNT(*) FROM d_fn_bulk WHERE LOWER(email) = '4242-user@example.com';
1 row in set (0.00 sec)
1 row in set (0.01 sec)
1万行では、体感できるほどの差は出ません。 ここが厄介なところです。
この差は、行数に比例して開きます。 索引を使うほうは、100万行でも1000万行でもほぼ同じ速さです。全行読むほうは、行数に正比例して遅くなります。 開発中の1万行では気づかず、本番で気づく、という形で出てきます。
そして、これは LOWER だけの話ではありません。
WHERE LOWER(email) = 'x' -- 効かない
WHERE TRIM(name) = '佐藤' -- 効かない
WHERE CONCAT(last_name, first_name) = '佐藤太郎' -- 効かない
WHERE DATE(ordered_at) = '2026-09-01' -- 効かない
WHERE YEAR(ordered_at) = 2026 -- 効かない
WHERE id + 0 = 100 -- 効かない
規則は1つです。
WHEREの「左辺」に列があって、それが何かにくるまれていたら、その列の索引は使われません。 くるむのは関数だけではありません。四則演算も同じです。
逆に、右辺に関数があるのは問題ありません。
WHERE ordered_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) -- 効く
右辺は1回計算されて、ただの値になります。 左辺の列はそのままなので、索引が使えます。
直し方1 — 照合順序で、大小を同じとみなす
そもそも「大文字小文字を無視して探す」ために LOWER を使っていました。 ということは、列の照合順序を「大小を区別しない」ものに変えれば、LOWER は要りません。
ALTER TABLE d_fn_bulk
MODIFY email VARCHAR(120) COLLATE utf8mb4_0900_ai_ci NOT NULL;
utf8mb4_0900_ai_ciのciは case-insensitive(大小を区別しない)という意味です。aiは accent-insensitive。MySQL 8.0 の既定の照合順序がこれなので、実は「わざわざutf8mb4_binにしていたのを、既定に戻した」だけです。
大文字で書いても当たります。
SELECT * FROM d_fn_bulk WHERE email = '4242-USER@EXAMPLE.COM';
+------+-----------------------+
| id | email |
+------+-----------------------+
| 4243 | 4242-user@Example.com |
+------+-----------------------+
EXPLAIN SELECT * FROM d_fn_bulk WHERE email = '4242-USER@EXAMPLE.COM';
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | d_fn_bulk | ref | idx_email | idx_email | 482 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+----------+-------+
rows が 1 に戻りました。 大小を無視して、索引も効いています。
これが一番きれいな直し方です。 「大小を区別しないで比べたい」という要求を、アプリではなく列の性質として書いたからです。 ただし、パスワードのハッシュやトークンのように「大小を区別してほしい」列には、絶対にやってはいけません。
abcとABCが同じ値として扱われます。
直し方2 — 生成列に索引を張る
照合順序で解決できない整形もあります。 例えば「前後の空白を落とした値で探したい」。
そのときは、整形済みの値を持つ列を作って、そこに索引を張ります。
まず元に戻します。
ALTER TABLE d_fn_bulk
MODIFY email VARCHAR(120) COLLATE utf8mb4_bin NOT NULL;
ALTER TABLE d_fn_bulk
ADD COLUMN email_lower VARCHAR(120) COLLATE utf8mb4_bin
AS (LOWER(email)) STORED,
ADD KEY idx_email_lower (email_lower);
AS (式) STOREDが生成列です。INSERTのときに自分で値を入れません。 MySQL がSTOREDの代わりにVIRTUALと書くと、保存せずに読むたびに計算します。 索引を張るならSTOREDのほうが素直です。
SELECT id, email, email_lower FROM d_fn_bulk WHERE id = 4243;
+------+-----------------------+-----------------------+
| id | email | email_lower |
+------+-----------------------+-----------------------+
| 4243 | 4242-user@Example.com | 4242-user@example.com |
+------+-----------------------+-----------------------+
勝手に埋まっています。 ここに索引が張ってあるので、こう書けます。
EXPLAIN SELECT * FROM d_fn_bulk WHERE email_lower = '4242-user@example.com';
+----+-------------+-----------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | d_fn_bulk | ref | idx_email_lower | idx_email_lower | 482 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------+-----------------+-----------------+---------+-------+------+----------+-------+
rows が 1。 索引が効いています。
MySQL 8.0.13 以降なら、列を作らずに「関数そのものに索引を張る」書き方もあります。
ALTER TABLE d_fn_bulk ADD KEY idx_lower_email ((LOWER(email)));
カッコが二重になっているのは間違いではありません。 外側が索引の定義、内側が式です。 これを張ると、
WHERE LOWER(email) = '...'がそのまま索引を使えるようになります。 裏では生成列が自動で作られています。
確かめます。
EXPLAIN SELECT * FROM d_fn_bulk WHERE LOWER(email) = '4242-user@example.com';
+----+-------------+-----------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | d_fn_bulk | ref | idx_lower_email | idx_lower_email | 483 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------+-----------------+-----------------+---------+-------+------+----------+-------+
さっきと同じ SELECT が、ALL から ref に変わりました。 文は1文字も変えていません。索引を足しただけです。
索引は「速くする魔法」ではなく「あらかじめ計算して並べておいたもの」です。関数の結果で探したいなら、関数の結果を先に並べておけばいい、というだけの話です。
直し方3 — 保存するときに整えておく
一番地味で、一番効くのがこれです。
メールアドレスを、INSERT する前にアプリ側で小文字にしてから保存する。 そうすれば、検索するときに LOWER を書く理由がなくなります。生成列も要りません。
冒頭の基準に戻ります。
その処理は、どこで一度だけやれば済むか。
メールアドレスの小文字化は、「保存のとき1回」で済みます。 読むたびにやる必要がありません。なのに WHERE LOWER(email) と書くと、検索のたびに1万回計算しています。
ただし、この方法には条件があります。
- すべての入り口が、必ず整形を通ること。 管理画面から直接
INSERTする人が1人いれば、そこから汚いデータが入ります - 既に入っているデータを、まとめて整形し直す必要があること
UPDATE d_fn_bulk SET email = LOWER(email) WHERE email <> LOWER(email);
WHEREを必ず付けてください。 付け方の話と、付け忘れたときに何が起きるかは、WHERE無しのUPDATEは戻せない — SQL深掘りに書きました。
3つのどれを選ぶか。
| 直し方 | 向いている場面 | 注意 |
|---|---|---|
| 照合順序を変える | 大小・アクセントの同一視だけで済むとき | 区別してほしい列に使うと事故る |
| 生成列 + 索引 / 関数索引 | 元の値も、整形した値も、両方要るとき | 書き込みが少し重くなる。容量も増える |
| 保存時に整形 | 元の値を残す必要がないとき | すべての入り口を通す必要がある。既存データの直しも要る |
⑦ 型変換 — CAST と CONVERT
黙って変換されている
MySQL は、型が合わないと勝手に変換します。
SELECT '10' + 5, '10abc' + 5, 'abc' + 5, 10 = '10', 10 = '10.0';
+----------+-------------+-----------+----------+------------+
| '10' + 5 | '10abc' + 5 | 'abc' + 5 | 10 = '10'| 10 = '10.0'|
+----------+-------------+-----------+----------+------------+
| 15 | 15 | 5 | 1 | 1 |
+----------+-------------+-----------+----------+------------+
'10abc' + 5 が 15。 エラーになりません。先頭から数字として読めるところまで読んで、残りを捨てています。
SHOW WARNINGS;
+---------+------+--------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '10abc' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'abc' |
+---------+------+--------------------------------------------------+
警告は出ています。 ですがアプリからはまず見ません。 結果だけが返ってきます。
文字列の列を数値と比べると、索引が飛ぶ
これが型変換の最大の実害です。
SELECT id, zip FROM d_fn_member WHERE zip = 1500001;
+----+---------+
| id | zip |
+----+---------+
| 1 | 1500001 |
+----+---------+
当たっているように見えます。 ですが、内部で起きているのはこれです。
zipは文字列の列、1500001は数値。 型が違うので、MySQL はzipのほうを数値に変換して比べます。 つまりCAST(zip AS 数値) = 1500001と同じです。左辺が関数でくるまれた状態になっています。 だからzipに索引を張っていても、効きません。
しかも、結果まで変わります。
SELECT '0600001' = 600001, '0600001' = '600001';
+--------------------+----------------------+
| '0600001' = 600001 | '0600001' = '600001' |
+--------------------+----------------------+
| 1 | 0 |
+--------------------+----------------------+
数値として比べると当たり、文字列として比べると当たりません。 先頭の 0 が消えるからです。
文字列の列には、必ず文字列を渡してください。
WHERE zip = '1500001'と、クォートを付ける。 これだけです。
明示的に書く — CAST
SELECT CAST('12' AS UNSIGNED) AS a,
CAST('12ab' AS UNSIGNED) AS b,
CAST(3.7 AS SIGNED) AS c,
CAST('2026-09-19' AS DATE) AS d,
CAST(1500001 AS CHAR) AS e;
+------+------+------+------------+---------+
| a | b | c | d | e |
+------+------+------+------------+---------+
| 12 | 12 | 4 | 2026-09-19 | 1500001 |
+------+------+------+------------+---------+
CAST(3.7 AS SIGNED)が4。 切り捨てではなく丸めです。 「整数にする」=「小数を捨てる」ではありません。捨てたいならTRUNCATE(3.7, 0)かFLOOR(3.7)です。
CONVERT は、書き方が違うだけで同じものです。
SELECT CONVERT('12', UNSIGNED), CAST('12' AS UNSIGNED);
+-------------------------+------------------------+
| CONVERT('12', UNSIGNED) | CAST('12' AS UNSIGNED) |
+-------------------------+------------------------+
| 12 | 12 |
+-------------------------+------------------------+
CONVERT には、CAST に無い使い方が1つあります。文字コードの変換です。
SELECT CONVERT('佐藤' USING utf8mb4);
これは型ではなく文字集合を変える構文です。 中身は文字コードとUTF-8のほうです。
どちらを使うか。 標準SQLにあるのは CAST です。 文字コードを変えるとき以外は CAST を使ってください。
なぜ明示的に書くのか
「黙って変換してくれるなら、書かなくていいのでは」と思いました。 書く理由は3つあります。
- 索引が効くかどうかが、変換の向きで決まる。 明示的に書けば、どちらが変換されるのかが目で見える
- 黙った変換は、エラーではなく警告になる。
'abc' + 5が5になって、そのまま合計に混ざる - データベースを移すと、変換の規則が変わる。 PostgreSQL は
text型の列と数値の足し算('10'::text + 5など)をエラーにします(型の付いていない'10' + 5は 15 になります)。MySQL だけで通っていた式は、移すと落ちます
⑧ どこでやるか — 判断表
冒頭の問いに戻ります。 判断の基準は「どこで一度だけやるか」でした。
| どこでやるか | 向いていること | 具体例 |
|---|---|---|
| 保存するとき(1回) | 何度も同じ条件で検索するもの。 元の値を残さなくていいもの | メールアドレスの小文字化、電話番号のハイフン除去、前後の空白落とし |
| SQLの中(読むたび) | 集計・並べ替え・絞り込みの条件に使うもの。 データベースから出す量を減らせるもの | CASE での区分け、COALESCE での欠損埋め、GROUP BY の前の正規化 |
| アプリの中(表示のとき) | 見せ方だけの話。 言語・地域・画面によって変わるもの | 「1,234円」のカンマと単位、「3分前」のような相対表示、翻訳、丸めた表示 |
判断に迷ったら、この順で自問しています。
- その整形の結果で、検索・並べ替え・集計をするか。 するなら、SQLの中か、保存時。 アプリではできません(全件持ってこないと並べ替えられない)
- 元の値を捨てていいか。 捨てていいなら保存時。残すなら生成列か、読むたび
- 見せ方だけの話か。 なら、アプリ。 通貨記号を
CONCATでくっつけてはいけません。画面が2つになった瞬間に破綻します
やってはいけない組み合わせが1つあります。 「保存時に整形せず、
WHEREで毎回LOWER()を呼ぶ」。一番遅くて、一番間違えやすいです。山場②が丸ごとこれの話でした。
つまずいたところ
1. CONCAT_WS にすれば全部解決すると思った
NULL は飛ばしてくれます。空文字は飛ばしません。 練習データの id = 4 は nickname が '' だったので、田中 / 次郎 / と末尾に区切りが残りました。
NULLIF(nickname, '') を挟むまで、原因が分かりませんでした。 画面上では NULL も '' も「何も見えない」ので、SELECT の結果を見ているだけでは区別が付きません。
CHAR_LENGTH() と IS NULL で、どちらなのかを先に確かめるのが早かったです。
2. LENGTH で文字数制限をかけて、日本語だけ弾かれた
「名前は10文字まで」のつもりで LENGTH(name) <= 10 と書きました。 英語名は通り、日本語は4文字で弾かれました。 3バイト×4文字 = 12バイトだったからです。
CHAR_LENGTH に直して解決。 ですが、そもそも英語のサンプルコードを写したのが原因でした。サンプルの LENGTH は、全部 CHAR_LENGTH を疑うと決めました。
3. TRIM したのに空白が消えず、30分探した
' 小林' の先頭が全角スペースでした。 画面では半角と見分けが付きません。
CHAR_LENGTH を前後で比べて、初めて「減っていない」と分かりました。 目で見て分からないものは、数字にするしかありません。
4. CASE dept_code WHEN NULL THEN ... が当たらない
構文エラーになりません。 ELSE の値が返るだけです。「書き間違えた」ではなく「書けてしまう」のが厄介でした。
単純CASE は内部で = を使っている、と知って納得しました。= NULL が真にならないのはSELECT の深掘りでやっていたのに、CASE と結び付いていませんでした。
5. EXPLAIN で差が出ず、「索引の話は嘘では」と思った
最初は10行の d_fn_member で EXPLAIN していました。 LOWER() を付けても付けなくても type: ALL。差が出ません。
10行なら全部読むほうが速いので、MySQL の判断が正しいだけでした。1万行に増やしたら、はっきり分かれました。
「索引の効果は、行が少ないと確かめられない」。 これを知らずに「うちのアプリは索引が効いている」と思い込むのが、一番危ないところでした。
6. ANALYZE TABLE を忘れて、rows の数字がおかしかった
1万行入れた直後の EXPLAIN で、rows が 1 と出ました。 全行読むはずなのに、です。
統計情報が古かっただけでした。ANALYZE TABLE d_fn_bulk; を打ったら 9825 に変わりました。EXPLAIN の rows は実測ではなく見積もりだという意味が、ここでやっと分かりました。
7. 関数索引を張った後、WHERE の書き方をわずかに変えたら効かなくなった
ADD KEY idx_lower_email ((LOWER(email))) を張ったあと、WHERE LOWER(TRIM(email)) = '...' と書いたら type: ALL に戻りました。
索引に登録されているのは LOWER(email) という式そのものです。LOWER(TRIM(email)) は別の式なので、一致しません。
関数索引は「その式で書いたときだけ」効きます。 融通は利きません。
後片づけ
この記事で作ったのは、appdb の中の d_fn_ で始まる表だけです。 全部消します。
USE appdb;
まず、何があるか目で見ます。
SHOW TABLES LIKE 'd\_fn\_%';
+---------------------------+
| Tables_in_appdb (d_fn_%) |
+---------------------------+
| d_fn_bulk |
| d_fn_member |
| d_fn_sale |
| d_fn_seed |
+---------------------------+
d\_fn\_%の\は「_を1文字として扱う」ための印です。LIKEでは_が「任意の1文字」の意味になるので、これが無いと関係ない表まで引っかかります。
外部キーは張っていないので、順番は問いません。
DROP TABLE d_fn_bulk;
DROP TABLE d_fn_seed;
DROP TABLE d_fn_sale;
DROP TABLE d_fn_member;
消えたことと、本線の表が無事なことを、同時に確かめます。
SHOW TABLES;
+-----------------+
| Tables_in_appdb |
+-----------------+
| sessions |
| tasks |
| users |
+-----------------+
d_fn_ で始まる表が1つも無く、tasks / users / sessions が残っていれば完了です。
EXIT;
この記事では、サーバーの設定は何も変えていません。 生成列も関数索引も、消した表と一緒に消えています。MySQL の再起動は要りません。
まとめ — 理解できたこと
- 整形をSQLでやるかアプリでやるかの基準は「どこで一度だけやるか」。 検索・並べ替え・集計に使うならSQL側か保存時、見せ方だけならアプリ側
CONCATは引数に1つでもNULLがあると、結果が全部NULL。 例外なし。CONCAT_WSはNULLを飛ばすが、空文字は飛ばさない- 空文字も飛ばしたいなら
NULLIF(x, '')を挟む。 画面上はNULLも''も見分けが付かないので、IS NULLとCHAR_LENGTHで先に確かめる LENGTHはバイト数、CHAR_LENGTHは文字数。 UTF-8 では日本語1文字が3バイトで、半角カナも3バイト。半角カナの濁点は独立した1文字TRIMは全角スペースを落とさない。 見た目では分からないので、CHAR_LENGTHを前後で比べて確かめるROUND(2.5)は 3 になるが、ROUND(25E-1)は 2 になる。DECIMALは「0から遠いほう」、DOUBLEは環境次第で「偶数側」。「四捨五入」という言葉では説明しきれないFLOORとTRUNCATEの違いは、負の数でしか出ない。FLOOR(-1.5)は-2、TRUNCATE(-1.5, 0)は-1- MySQL は 0 で割ってもエラーにならず
NULLを返す。 止まらないぶん、気づかないまま最後まで流れる COALESCEは最初の非NULLを返す。何個でも書ける。IFNULLは2つだけの MySQL 方言。迷ったらCOALESCENULLIF(a, b)は「aとbが同じならNULL」。分子 / NULLIF(分母, 0)がゼロ除算を避ける定石で、他のデータベースに移しても壊れないCASEには単純CASE と検索CASE の2つがある。 単純CASE は=で比べているので、WHEN NULLは絶対に当たらない。NULLを判定するなら検索CASE でIS NULLELSEを書かないと、どれにも当たらない行が黙ってNULLになる。エラーは出ないWHEREの左辺で列を関数や演算でくるむと、その列の索引は使われない。LOWER/TRIM/DATE/YEAR/+ 0、全部同じEXPLAINで見るのはtype/key/rowsの3つ。type: ALLかつkey: NULLなら全行読んでいる。rowsは実測ではなく見積もりなので、先にANALYZE TABLEを打つ- 索引の効果は、行が少ないと確かめられない。 10行では MySQL が全行読むほうを選ぶ。1万行にして初めて差が見える
- 直し方は3つ。 照合順序で大小を同一視する / 生成列か関数索引を足す / 保存するときに整形しておく。元の値が要らないなら、保存時が一番効く
- 関数索引は「その式で書いたときだけ」効く。
LOWER(email)の索引はLOWER(TRIM(email))には効かない - 文字列の列に数値を渡すと、列のほうが数値に変換されて索引が飛ぶ。 しかも
'0600001' = 600001が真になる。文字列の列には必ずクォートを付けて渡す CAST(3.7 AS SIGNED)は4。 整数にすることは、小数を捨てることではない- 黙った型変換は警告にしかならず、アプリからは見えない。 だから明示的に
CASTを書く
参考リンク
- roadmap.sh — SQL
- MySQL 8.0 リファレンスマニュアル(日本語)
- MySQL — 文字列関数
- MySQL — 数学関数
- MySQL — 制御フロー関数(IF / CASE / IFNULL / NULLIF)
- MySQL — 比較関数と演算子(COALESCE)
- MySQL — 型変換関数(CAST / CONVERT)
- MySQL — 式評価における型変換
- MySQL — 日付と時刻の関数
- MySQL — CREATE INDEX(関数キーパーツ)
- MySQL — 生成列
次に読む
この記事は 【深掘り】SQL の 8 / 17 本目です。
| 記事 | |
|---|---|
| ← 前の記事 | GROUP BYと集計関数 — 付けたら行が減った |
| → 次の記事 | 相関サブクエリとNOT IN — 行の数だけ実行される |
上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。
全体の順路は はじめに にあります。
roadmap.sh のどこをやっているか
- 文字列・数値・NULL を扱う関数
- WHERE に関数を書くと索引が効かない
この記事は、上の項目を実際に手を動かして確かめる回です。

コメント