In this study guide, 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 'savannah.group' doesn't exist
Ok, it was just a missing
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?
Manage research, learning and skills at NCR Works. Create an account using LinkedIn to manage and organize your omni-channel knowledge. NCR Works is like a shopping cart for information -- helping you to save, discuss and share.