MySQL/Language/Specifying names

From Wikibooks, open books for an open world
Jump to navigation Jump to search

In this book, we will quote the MySQL identifiers (tables names, fields, databases, etc.) using backquotes (`).

Backquote is ASCII 96. It can be type on Linux systems by pressing: ALT+'.

Most often, this is optional. However, this allows better error messages from MySQL. For example, this error is not very helpful:

mysql> SELECT user_id, group_id FROM user,group LIMIT 1;
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 'group LIMIT 1' at line 1

But this one is better:

mysql> SELECT user_id, group_id FROM `user`,`group` LIMIT 1;
ERROR 1146 (42S02): Table '' doesn't exist

Ok, it was just a missing s:

mysql>  SELECT user_id, group_id FROM `user`,`groups` LIMIT 1;
| user_id | group_id |
|     100 |        2 |
1 row in set (0.02 sec)

This syntax allows the user to use reserved words and some illegal characters in objects' names. It is even possible to use backquotes by typing it twice:

RENAME TABLE `user` TO ````

However, this is not a portable syntax. The SQL standard recommends the use of a double quote ("). If you want to write portable SQL quote, do not quote the identifiers. But is there something like portable SQL, even remotely?