MySQL Order by boolean value tinyint

Question

I am using mysql Ver 14.14 Distrib 5.5.31, for debian-linux-gnu (x86_64).

This query fails with a You have an error in your SQL syntax[...]near 'read ASC' message :

SELECT 'messages'.* FROM 'messages' WHERE 'messages'.'user_id' = 2 ORDER BY read ASC;

where the read column is a TINYINT(1) value generated by the Rails ActiveRecord interface to store boolean values.

The same action works when switching to postgresql, but i currently have no access to the pg generated queries. Is there something wrong with the actual query? (maybe i cannot order by a tinyint) or should I file a bug report?

Answer 1

Read is reserve keyword in mysql http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-5.html

you have to add 'read' ASC in your query

Answer 2

Issue is "read" is a keyword in mysql. It is better if you can avoid using reserved words for column identifiers

You can use it with backticks,

ORDER BY `read' ASC

Answer 3

In addition to @naveen's answer, you'll need to change your single quotation marks into backticks:

SELECT `messages`.* FROM `messages` WHERE `messages`.`user_id` = 2 ORDER BY read ASC;

Better yet, do not use MySQL reserved words as column names. To change the name, use ALTER:

ALTER TABLE messages CHANGE read seen TINYINT