0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@yml024(Yumul Onishi)

【DB設計・DB運用】MySQL SQL小技・注意点メモ

0
Last updated at Posted at 2026-09-05

MySQL SQL小技・注意点メモ

MySQLを日常的に書く中で知っておくと役立つ書き方、逆に避けるべき書き方、そして過去に存在していたが廃止された機能をまとめたメモ書きである。標準SQLとの違いを意識しながら整理している。

コメントの書き方

MySQLでは1行コメントに -- と # の両方が使える。違いは以下の通りである。

記法 標準SQL 空白の要否
-- 標準 直後に空白が必要
# MySQL独自 不要

PostgreSQLやOracleへの移行可能性があるなら -- を使う判断になる。MySQL専用であれば # でも問題ない。

# はPHPやシェルスクリプトと同じ記法であるため、そちらに慣れているエンジニアには馴染みやすい。

基本文法のクセ

文字列はシングルクォートで囲むのが基本になる。ダブルクォートは製品によって識別子として扱われるため、文字列に使うと事故につながる。

SELECT * FROM users WHERE name = 'Tanaka';

文字列中にシングルクォートを含めたい場合は2つ重ねてエスケープする。

SELECT 'It''s a pen';

識別子（テーブル名・カラム名）を予約語や特殊な名前と衝突させたくない場合は、MySQLではバッククォートで囲む。

SELECT `order` FROM `table`;

演算子は = と <> が標準になる。!= もMySQLでは使えるが標準SQLでは非推奨の扱いである。

SELECT * FROM users WHERE age <> 20;

範囲検索や部分一致は以下のように書く。

SELECT * FROM users WHERE age BETWEEN 20 AND 30;
SELECT * FROM users WHERE name LIKE '%田中%';

LIKE のワイルドカードは % が0文字以上、_ が1文字にマッチする仕様になっている。

NULLの比較は = ではなく IS NULL / IS NOT NULL を使用する。= NULL は常に不定（UNKNOWN）となり意図した結果にならない。

SELECT * FROM users WHERE deleted_at IS NULL;

条件分岐は CASE WHEN を使う。

SELECT
  CASE WHEN score >= 80 THEN 'A'
       WHEN score >= 60 THEN 'B'
       ELSE 'C'
  END AS grade
FROM exams;

トランザクションは BEGIN / COMMIT / ROLLBACK で制御する。

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

その他の慣習は以下の通りである。

  • 予約語（SELECT, FROM, WHEREなど）は大文字、テーブル名・カラム名は小文字で書くのが一般的になっている
  • テーブル名は単数形か複数形かを社内で統一しておく必要がある
  • 命名はスネークケース（user_name）が主流であり、キャメルケースは環境によってクォートが必要になる場合がある
  • 文末には ; をつける
  • サブクエリに別名をつける際は AS を明示することが多い
  • 複数行のコメントは /* ... */ で囲む
  • AND と OR が混在する条件式は必ず括弧で優先順位を明示する
  • NULLの代替値には COALESCE() または IFNULL() を使用する

IFNULL() はMySQL独自、COALESCE() は標準SQL準拠の関数である。移植性を意識するならCOALESCE()を選ぶ判断になる。

MySQL独自のあまり知られていない書き方

ユーザー変数を使うと、SELECT文の中で連番を振ることができる。

SET @rank = 0;
SELECT @rank := @rank + 1 AS rank, name FROM users;

UPDATE文で値を計算しながら変数に退避することもできる。

UPDATE users SET point = @p := point + 100;

INSERT文は複数行をまとめて1文で書ける。

INSERT INTO t (a, b) VALUES (1, 2), (3, 4), (5, 6);

INSERT IGNORE はエラーになる行だけをスキップして処理を続行する。

INSERT IGNORE INTO users (id, name) VALUES (1, 'Sato');

REPLACE INTO は主キーが重複した場合に既存行を削除してから挿入し直す構文になっている。全カラムが上書きされる点に注意が必要である。

REPLACE INTO users (id, name) VALUES (1, 'Sato');

UPDATEやDELETEでもJOINを使用できる。

UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.col = t2.col;
DELETE t1 FROM t1 JOIN t2 ON t1.id = t2.id WHERE t2.flag = 1;

JSON型はネイティブでサポートされており、JSON_EXTRACT() や矢印演算子で値を取得できる。

SELECT JSON_EXTRACT(data, '$.name') FROM logs;
SELECT data->>'$.name' FROM logs;

GROUP_CONCAT() はGROUP BYの結果をカンマ区切り文字列に集約する関数である。

SELECT dept, GROUP_CONCAT(name SEPARATOR ', ') FROM employees GROUP BY dept;

FIND_IN_SET() はカンマ区切り文字列の中に値が含まれるかを判定する関数である。正規化が崩れたテーブルを扱う際に使うことになるケースが多い。

SELECT * FROM tags WHERE FIND_IN_SET('b', tag_list);

LIMIT はオフセットと件数を同時に指定できる。

SELECT * FROM t LIMIT 10, 5;

LIMIT 10, 5 は「10件飛ばして5件取得する」という意味になる。第一引数がオフセット、第二引数が件数である。

実行計画は EXPLAIN、テーブル定義の確認は SHOW CREATE TABLE で行う。

EXPLAIN SELECT * FROM users WHERE id = 1;
SHOW CREATE TABLE users;

MySQL 8.0以降ではCTE（WITH句）やウィンドウ関数（ROW_NUMBER()など）が使用できるようになっている。5.x系では使えないためバージョン確認が必要になる。

WITH ranked AS (
  SELECT id, name, ROW_NUMBER() OVER (ORDER BY score DESC) AS rk
  FROM exams
)
SELECT * FROM ranked WHERE rk <= 3;

セキュリティ上避けるべき書き方

文字列連結によるクエリ組み立ては絶対に行ってはならない。SQLインジェクションの原因になる。

-- NG例
"SELECT * FROM users WHERE name = '" + input + "'"

プレースホルダを使ったプリペアドステートメントで値をバインドする書き方が正しい対応になる。

SELECT * FROM users WHERE name = ?;

LIKE検索やORDER BY・LIMITの値も同様に、ユーザー入力を直接文字列連結しない。ORDER BYやLIMITはプレースホルダに乗せられないケースがあるため、許可された値のホワイトリストと突き合わせて検証する必要がある。

その他、避けるべき運用は以下の通りである。

  • アプリ用ユーザーに GRANT ALL PRIVILEGES を付与しない
  • root ユーザーでアプリケーションを稼働させない
  • DROP・ALTER・GRANT系の権限はアプリ用ユーザーに持たせず運用アカウントと分離する
  • エラーメッセージをそのままユーザーに表示しない（テーブル構造の漏洩につながる）
  • パスワードは平文で保存せず、bcryptなどでハッシュ化する。MD5()やSHA1()単体はすでに弱いため使用しない
  • LOAD_FILE() や INTO OUTFILE は不要であれば権限自体を無効化する
  • リモート接続を無闇に許可せず、bind-addressを絞り接続元IPを制限する
  • UPDATE / DELETE 実行時に WHERE 句を書き忘れない

SQLインジェクション対策の中でも、文字列連結によるクエリ生成の排除は最優先で徹底すべき項目である。

過去に存在し廃止された機能

MySQL 8.0以降で削除・変更された代表的な機能は以下の通りである。

機能 状態
クエリキャッシュ（query_cache_size等） 8.0で完全に削除されている
SQL_CACHE 構文 8.0で構文エラーになる
SQL_CALC_FOUND_ROWS / FOUND_ROWS() 非推奨。COUNT(*)の別実行に置き換える
GRANTでのユーザー新規作成 削除されCREATE USERが必須になった
PASSWORD()関数 8.0で削除されている
OLD_PASSWORD関連機能 削除されている
GROUP BYのASC/DESC指定 削除されている
GROUP BYの暗黙的な並び順保証 廃止されている（明示的なORDER BYが必要）
\NをNULLの代わりに使う記法 廃止されている
PROCEDURE ANALYSE() 削除されている
FLOAT(M,D)のような桁数指定 削除されている
ZEROFILL・整数の表示幅指定 非推奨（将来削除予定）
tx_isolation / tx_read_only transaction_isolation / transaction_read_onlyに名称変更
ONLY_FULL_GROUP_BY 5.7からデフォルト有効化されている
FEDERATEDストレージエンジン デフォルトでは無効化されている
mysql_upgradeコマンド 8.4で削除。起動時に自動処理される
デフォルト認証方式 mysql_native_passwordからcaching_sha2_passwordに変更されている

古いコードが8.0以降で急にエラーになる場合、上記のいずれかに該当しているケースが多い。特にクエリキャッシュ関連の設定値はmy.cnfに残っていると起動エラーにつながる。

まとめ

論点 結論
コメントの書き方 移植性重視なら--、MySQL専用なら#でよい
基本文法 文字列はシングルクォート、識別子はバッククォート、NULL比較はIS NULLを使う
MySQL独自機能 ユーザー変数・JSON関数・GROUP_CONCAT()・CTEなど実務で使える機能が多い
セキュリティ 文字列連結によるクエリ生成は排除し、プレースホルダと権限分離を徹底する
廃止機能 クエリキャッシュやGRANTでのユーザー作成など、8.0以降の移行時に注意すべき項目が多数ある

あとがき
このメモ書きはMySQLの基本文法から独自機能、セキュリティ上の注意点、過去に廃止された機能までを一通り整理したものである。バージョンアップの際は特に廃止機能の一覧を確認し、既存のスクリプトへの影響を事前に洗い出しておく必要がある。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?