A collection of computer systems and programming tips that you may find useful.
 
Brought to you by Craic Computing LLC, a bioinformatics consulting company.

Wednesday, February 23, 2011

Tables named with reserved words in MySQL

Trying to fix a string encoding issue in a MySQL database I realized that one of my tables was called the same as a MySQL reserved word - specifically I have a table called 'references'.

This was created from a Rails app and I have been using this with no problems for a few months, so you can use reserved words, or at least that one.

Problem is that direct SQL statements in the MySQL client like this don't work:
mysql> describe references;
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 'references' at line 1
The solution is to prefix the table name with the database name like this:
mysql> describe mydb.references;
+------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |


 

No comments:

Archive of Tips