Help
If you need to construct SQL statements that refer to identifiers containing special characters, quote the identifiers so that they can be inserted safely into
statement strings. The topic called “How to handle special characters and NULL values in MySQL” discusses how to handle
special characters in data values by using placeholders or quoting
methods. Special characters also can be present in identifiers such as
database, table, and column names. For example, the table name
some table contains a space, which
is not allowed by default:
mysql>CREATE TABLE some table (i INT);
ERROR 1064 (42000): You have an error in your SQL syntax near 'table (i INT)'Special characters are handled differently in identifiers than in data values. To make an identifier safe for insertion into an SQL statement, quote it by enclosing it within backticks:
mysql>CREATE TABLE `some table` (i INT);
Query OK, 0 rows affected (0.04 sec)If a quoting character appears within the identifier itself,
double it when quoting the identifier. For example, quote abc`def as `abc``def`.
In MySQL, backticks
are always allowed for identifier quoting. If
the
ANSI_QUOTES SQL mode
is enabled, the double-quote character also is legal for quoting
identifiers. Thus, both of the following statements are equivalent
with the ANSI_QUOTES SQL mode
enabled:
CREATE TABLE `some table` (i INT); CREATE TABLE "some table" (i INT);
If it’s necessary to know which identifier quoting characters
are allowable, issue a
SELECT
@@sql_mode statement to retrieve the SQL
mode and check whether its value includes ANSI_QUOTES.
Be aware that although strings in MySQL normally can be quoted
using either single-quote or double-quote characters ('abc', "abc"), that is not true when ANSI_QUOTES is enabled. In that case, MySQL
interprets 'abc' as a string and
"abc" as an identifier, so you must
use only single quotes for strings.
Within a program, you can use an identifier-quoting routine if
your API provides one, or write one yourself if it does not. Perl DBI
has a
quote_identifier( ) method that returns
a properly quoted identifier. For an API that has no such method, you
can quote an identifier by enclosing it within backticks and doubling
any backticks that occur within the identifier. Here’s a Ruby routine
that does so:
def quote_identifier(ident) return "`" + ident.gsub(/`/, "``") + "`" end
If you’re willing to assume that an identifier has no internal backticks, you can simply enclose it within backticks.
Portability note: If you write your own identifier-quoting routines, remember that other DBMSs may require different quoting conventions.
In some contexts, identifiers might be used as data values, and
should be handled as such. If you select information from INFORMATION_SCHEMA, the metadata database,
it’s common to indicate which rows to return by specifying database
object names in the WHERE clause.
For example, this statement retrieves the column names for the
profile table in the cookbook database:
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'cookbook' AND TABLE_NAME = 'item';
The database and table names are used here as data values, not as identifiers. Were you to construct this statement within a program, you would parameterize them using placeholders, not identifier quoting. For example, you might do this in Ruby:
names = dbh.select_all(
"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?",
db_name, tbl_name)
A handy resource when you need quick solutions or techniques, this Cookbook addresses specific questions in using MySQL. You'll find dozens of short, focused pieces of code and hundreds of worked-out examples that are perfect for programmers of all levels who don't have the time (or expertise) to solve MySQL problems from scratch. The new edition covers MySQL 5.0 and the older but still widespread MySQL 4.1.
