MySQL ビュー作成時のカラム別名（エイリアス）にアンダーバーが上手く使えないことがある

ビューを作る時に、カラムに別名（エイリアス）を付けたいことも多いと思いますが、

MySQLで特定のケースにおいて、別名にアンダーバーなどが上手く使えない場合があります。

(MySQL5.1.6)

 

例えばこんな感じのテーブル、ビューを作ってみます。

mysql> CREATE TABLE tbl_test
    -> (
    ->   id int NOT NULL AUTO_INCREMENT,
    ->   value int NOT NULL,
    ->   insdate timestamp NOT NULL,
    ->   CONSTRAINT pk_tbl_test PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.14 sec)

mysql> CREATE VIEW vw_test AS
    -> SELECT
    ->   DATE_FORMAT(insdate, '%Y%m') AS year_month, SUM(value) AS sum_value
    -> FROM
    ->   tbl_test
    -> GROUP BY
    ->   year_month;
ERROR 1064 (42000): 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 'year_month, SUM(value) AS sum_value
FROM
  tbl_test
GROUP BY
  year_month' at line 3
mysql> 

ビューの作成に失敗しました。

カラム別名にアンダーバーが含まれていて、これがどうも予約語として認識されているようです。

そのため、カラム別名の部分を識別子として認識させるために'`'(バッククォート)で囲んで再実行してみます。

mysql> CREATE VIEW vw_test AS
    -> SELECT
    ->   DATE_FORMAT(insdate, '%Y%m') AS `year_month`, SUM(value) AS sum_value
    -> FROM
    ->   tbl_test
    -> GROUP BY
    ->   `year_month`;
Query OK, 0 rows affected (0.01 sec)

ビューの作成が成功しました。

あれ？でも"sum_value"もカラム別名なのにバッククォート使わずにいけてるじゃないか、

と思うのですが、これは大丈夫なようです。

推測ですが、DATE_FORMATのような、ワイルドカードまたは特殊記号を使う関数を使う場合、

そのカラム中では別名においてもアンダーバーなどが予約語化してしまうようです。

予約語化というか、カラム別名自体が文字列化してしまうのではないかと思います。

 

そのため、以下の文字列中で特別な意味を持つ文字はすべて今回の問題に引っかかるかもしれません。

\0, シングルクォート（'）, ダブルクォート（"）, バックスペース(\b),

改行文字（\n）, キャリッジリターン（\r）, タブ(\t), Control-Z(\z),

バックスラッシュ(\), パーセント（%）, アンダーバー（_）

 

これで解決！と思いきや、

このビューからSELECTする際にも注意が必要になります。

mysql> select * from vw_test where year_month = '201212';
ERROR 1064 (42000): 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 'year_month = '201212'' at line 1

 

ここでもカラム別名にバッククォートをつけてやる必要があるのです。

mysql> select * from vw_test where `year_month` = '201212';
+------------+-----------+
| year_month | sum_value |
+------------+-----------+
| 201212     |      1000 |
+------------+-----------+
1 row in set (0.00 sec)

 

SELECTできました。

 

てな感じで、直感的にSELECTが実行できないため、

バッククォートしないと、カラム別名にアンダーバーを使えない状況になったら、

極力使わない方が良さそうですね。

（常にバッククォートを使うという習慣がある人や環境なら別なのでしょうけど）