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?

score 3 · Accepted Answer · answered Sep 25 '13 at 09:55

3

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

answered Sep 25 '13 at 09:55

naveen goyal

4,571
2
16
26

score 2 · Answer 2 · edited Aug 17 '22 at 15:49

2

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

edited Aug 17 '22 at 15:49

yoozer8

7,361
7
58
93

answered Sep 25 '13 at 09:58

unutbu

842,883
184
1,785
1,677

1

The actual code uses backticks, it was a typo. Thanks for the 'seen' suggestion i was actually stuck trying to find a good renaming! – Spyros Mandekis Sep 25 '13 at 10:13

score 1 · Answer 3 · answered Sep 25 '13 at 09:57

1

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

answered Sep 25 '13 at 09:57

Chamal

1,439
10
15

MySQL Order by boolean value tinyint

3 Answers3