Most tools or languages you work with have words which are “reserved”, meaning you cannot use them in a variable declaration or schema definition etc as they have special treatment in the underlying system. If you try to do this you’ll usually get an immediate error.
Today I needed to change an existing SQL query from a “SELECT *” to explicitly defining each column because I wanted to add some date formating at the SQL level.
The query (after modifications) was as follows:
SELECT id , date_format(dt_start, "%d/%m/%Y") as dt_start , date_format(dt_end, "%d/%m/%Y") as dt_end , repeat FROM smsschedule
Now remember that the code used to be SELECT * and was working fine. But when I changed it as above it failed and threw an error:
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…
The reason is that “repeat” is a MySQL reserved word. The interesting part (and real reason for this post) is that it didn’t fail when the query was sent as “*”, only when I explicitly set the invalid column in the SELECT list.
This was sent from ColdFusion but I also checked directly against the database (MySQL query browser) and had the same result.
Might be best to check the reserved list, most are obvious but a few could catch you out if you’re not aware of them.
Also interesting to note is that the table in question contained 2 column names which I thought would have failed. Those being “hour” and “minute”.
Not only did they not fail, but they aren’t in the reserved words list for MySQL. It would appear that function names (at least some of them) are ok to be used as column names. Still…I wouldn’t really recommend using these column names in your tables 
11 Comments
Most SQL languages allow you to put square brackets [] around columns and other objects that conflict with reserved words.
@Chris, thanks Chris. I forgot to mention that I tried adding square brackets as well as a column alias but neither worked.
I had to rename the column.
Tick marks should bypass the reserved word filter. e.g. `repeat`
@Ben, tick marks (back tick) or quotes? Quotes won’t work as you’ll just return the string that was in quotes. So if I do:
SELECT ‘repeat’
FROM mytable
I will get ‘repeat’ back. Same with double quotes.
I assume you mean back ticks (`) which do work, but not really very friendly!
Still, a good solution if you aren’t able to rename your database column. Thanks for that.
I wonder if it’s considered a “bug” if MySQL actually lets you name a column with a reserved word in the first place.
I meant back tick marks. Sorry for the confusion.
in all databases you can name tables and columns with names being keywords. That is why there are characters to escape them. In mysql these are back quotes, in mssql [] and afair “” for names with spaces
@radek, whilst technically this is true I’d argue the logic of ever knowingly using reserved words. It just seems like heading down a path of more trouble particularly in multi-developer environments.
It might not seem like trouble, after all what is a pair of square brackets/back ticks etc? But each and every developer who works on the project has to know and understand that you need to escape the table (or column) name. I’d bet that this would cause unnecessary headache for some like it did me today!
I think the idea behind it is backwards / cross system compat. If you migrate your app from other DBMS / legacy programming languge you may not change your code.
should be ” you may not want to change your code “
I’ve just had a similar problem. A site which has been working fine for years. I was doing some updates and started getting errors in my MySql query. Navicat showed the field name in blue and that’s what triggered me to this word being a reserved word. My site does vacation rental properties and as part of that, if you’ve got children you might order a ‘cot’. That’s on one of the forms as a field. Mysql seems to be confusing ‘cot’ with ‘cotangent’. Been working for years though before now. I’ll try the backticks and see how that goes. Thanks for the heads-up
I often use back-ticks with MySQL, these are MySQL native for calling db names and columns.
So, I use column names like `group` or `key` with no problem.
The back-tick have more power than that
Back-ticks can be used to retrive aliases with spaces in names
Your post is old, but you may try:
SELECT id , date_format(dt_start, “%d/%m/%Y”) as `Date Start` , date_format(dt_end, “%d/%m/%Y”) as `Date End` , `repeat` as `rpt` FROM smsschedule