MySQL カラム名にハマる

バグ踏みの達人です。
PHP から MySQL のデータを更新しようとして珍しいバグを踏んだのでメモ

何故かエラーになる

あるテーブルに次のようなコードでデータを入れようとしたらエラーになりました。

<?php
/*
 * id: INT AUTO_INCREMENT NOT NULL PRIMARY KEY
 * token: VARCHAR(255)
 * exp: timestamp NOT NULL
 * revoke: TINYINT(1) NOT NULL DEFAULT 0
 */
function create_token($token, $exp) {
  $pdo = db_connect();
  $sql = "INSERT INTO {$table} (id, token, exp, revoke) VALUES (null, :token, :exp, 0)";
  $stmt = $pdo->prepare($sql);
  $pdo->beginTransaction();
  $stmt->bindValue(':token', $token, PDO::PARAM_STR);
  $stmt->bindValue(':exp', $exp, PDO::PARAM_INT);
  $stmt->execute();
  $pdo->commit();
}

👇

Uncaught Exception: INSERT TOKEN: SQLSTATE[42000]: Syntax error or access violation: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
near 'revoke) VALUES (2, 'token...'

You have an error in your SQL syntax SQL 文にシンタックスエラーがあるとのこと。
ただどう見ても SQL 文に間違いは無さそう…

カラム名 `revoke` が原因

revoke は MySQL の予約語になっていて、この SQL が渡された時に revoke を実行しようとすると解釈してしまうために不正な SQL になっていたっぽい。

13.7.1.6 REVOKE 構文 MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.7.1.6 REVOKE 構文

これ。

予約語をカラム名にしている場合は ` バッククオートでカラム名を囲めばエラーにならない

<?php
$sql = "INSERT INTO {$table}
  (id, token, exp, `revoke`) 
  VALUES
  (null, :token, :exp, 0)";

こんな感じで `revoke` とすればエラーにならずに SQL が正しく実行されました。 (作っていたものは、まだ初期段階だったのでカラム名を変更しました。)

所感

エラーがシンタックスエラーとししか出なかったので、順番にカラムを消したり値を変えたりで試して原因を特定するのが大変でした。
SQL はカラム名の指定に ` (バッククオート) なしでも動作するけど、mysqlAdmin で SQL 作る時にデフォルトで ` が付けられるのはこういう理由があったからなのか〜と納得したのでした。
DBのカラム名ローマ字読みな日本語にするの案外このバグを踏むこと無さそうなので良いのかもしれない…

[参考]