SQLの組み込み関数 — CONCATにNULL1つで全部消える

SQLの組み込み関数。文字と数値のカードを整える小さな処理装置、道具箱。

日付と時刻の関数は、本線の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通りあること

この記事でやること

  1. 練習用のテーブルを3つ作る(d_fn_ で始まる表)。前後に空白、全角半角の混在、NULL、大文字小文字のばらつきを、わざと入れる
  2. 文字列関数と数値関数を、その汚いデータの上で動かして、きれいにする
  3. CONCAT と CONCAT_WS を並べて打ち、NULL の扱いの違いを目で見る
  4. COALESCE / NULLIF / CASE で NULL とゼロ除算を潰す
  5. 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)で打ちます。

bash
$ sudo mysql

まずバージョンを見ておきます。この記事は 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;

email にだけ 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つ、読み取ることがあります。

  1. id = 1:CONCAT_WS は NULL の引数を、無かったことにして飛ばします。 区切り文字も一緒に消えるので、佐藤 / 太郎 / のようにはなりません。CONCAT のほうは、同じ行が丸ごと NULL です
  2. id = 4:空文字は飛ばしません。 だから両方とも末尾に / が残ります。CONCAT_WS が無視するのは NULL だけです
  3. 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つあります。

  1. サトウ タロウ(半角カナ)と スズキ ハナコ(全角カナ)が、同じ19バイトです。「半角だから1バイト」ではありません。 UTF-8 では、半角カナも1文字3バイトです
  2. イトウ サブロウ は8文字。 見えている全角スペースが、ちゃんと1文字として数えられています
  3. ワタナベ シロウ は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 |
+----+----------+-----+-------------+

読み方はこうです。

  1. NULLIF(qty, 0) で、qty が 0 なら NULL にする
  2. amount / NULL は NULL(0で割ったのではなく、NULL で割った)
  3. 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 — 使える索引の候補すら無いと判断されている

なぜこうなるのか。 索引に入っているのは email の値そのもの(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 が email から計算して、実際に保存します。 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つあります。

  1. 索引が効くかどうかが、変換の向きで決まる。 明示的に書けば、どちらが変換されるのかが目で見える
  2. 黙った変換は、エラーではなく警告になる。 'abc' + 5 が 5 になって、そのまま合計に混ざる
  3. データベースを移すと、変換の規則が変わる。 PostgreSQL は text 型の列と数値の足し算('10'::text + 5 など)をエラーにします(型の付いていない '10' + 5 は 15 になります)。MySQL だけで通っていた式は、移すと落ちます

⑧ どこでやるか — 判断表

冒頭の問いに戻ります。 判断の基準は「どこで一度だけやるか」でした。

どこでやるか向いていること具体例
保存するとき(1回)何度も同じ条件で検索するもの。 元の値を残さなくていいものメールアドレスの小文字化、電話番号のハイフン除去、前後の空白落とし
SQLの中(読むたび)集計・並べ替え・絞り込みの条件に使うもの。 データベースから出す量を減らせるものCASE での区分け、COALESCE での欠損埋め、GROUP BY の前の正規化
アプリの中(表示のとき)見せ方だけの話。 言語・地域・画面によって変わるもの「1,234円」のカンマと単位、「3分前」のような相対表示、翻訳、丸めた表示

判断に迷ったら、この順で自問しています。

  1. その整形の結果で、検索・並べ替え・集計をするか。 するなら、SQLの中か、保存時。 アプリではできません(全件持ってこないと並べ替えられない)
  2. 元の値を捨てていいか。 捨てていいなら保存時。残すなら生成列か、読むたび
  3. 見せ方だけの話か。 なら、アプリ。 通貨記号を 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_ で始まる表だけです。 全部消します。

bash
$ sudo mysql
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 方言。迷ったら COALESCE
  • NULLIF(a, b) は「a と b が同じなら NULL」。 分子 / NULLIF(分母, 0) がゼロ除算を避ける定石で、他のデータベースに移しても壊れない
  • CASE には単純CASE と検索CASE の2つがある。 単純CASE は = で比べているので、WHEN NULL は絶対に当たらない。NULL を判定するなら検索CASE で IS NULL
  • ELSE を書かないと、どれにも当たらない行が黙って 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 を書く

参考リンク


次に読む

この記事は 【深掘り】SQL の 8 / 17 本目です。

記事
← 前の記事GROUP BYと集計関数 — 付けたら行が減った
→ 次の記事相関サブクエリとNOT IN — 行の数だけ実行される

上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。

全体の順路は はじめに にあります。

roadmap.sh のどこをやっているか

Backend›SQL — Functions
  • 文字列・数値・NULL を扱う関数
  • WHERE に関数を書くと索引が効かない

この記事は、上の項目を実際に手を動かして確かめる回です。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次