列の別名

どうも列の別名にアンダースコアを使うと、挙動が変わるみたい。

  • アンスコなし
mysql> SELECT date_format(datetime, '%Y%c') as yearmonth
    -> FROM journals
    -> WHERE (date_format(datetime, '%c') = '8')
    -> GROUP BY yearmonth  ORDER BY yearmonth
    -> ;
+-----------+
| yearmonth |
+-----------+
| 20078     |
+-----------+
1 row in set (0.01 sec)
  • アンスコあり
mysql> SELECT date_format(datetime, '%Y%c') as year_month
    -> FROM journals
    -> WHERE (date_format(datetime, '%c') = '8')
    -> GROUP BY year_month  ORDER 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
FROM journals
WHERE (date_format(datetime, '%c') = '8')
GROUP BY yea' at line 1
mysql>
  • アンスコありだけどシングルクォート囲み
mysql> SELECT date_format(datetime, '%Y%c') as 'year_month'
    -> FROM journals
    -> WHERE (date_format(datetime, '%c') = '8')
    -> GROUP BY 'year_month'  ORDER BY 'year_month'
    -> ;
+------------+
| year_month |
+------------+
| 20078      |
+------------+
1 row in set (0.00 sec)

なんでだろお。