MySQL/Language/Specifying Names

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 '' 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?

  This article uses material from the Wikipedia page available here. It is released under the Creative Commons Attribution-Share-Alike License 3.0.



Connect with defaultLogic
What We've Done
Led Digital Marketing Efforts of Top 500 e-Retailers.
Worked with Top Brands at Leading Agencies.
Successfully Managed Over $50 million in Digital Ad Spend.
Developed Strategies and Processes that Enabled Brands to Grow During an Economic Downturn.
Taught Advanced Internet Marketing Strategies at the graduate level.

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.

  Contact Us