Database Reference
In-Depth Information
CREATE
TABLE
`
some
table
`
(
i
INT
);
CREATE
TABLE
"some table"
(
i
INT
);
If it's necessary to know which identifier quoting characters are permitted, execute a
SELECT
@@sql_mode
statement to retrieve the SQL mode and check whether its value
includes
ANSI_QUOTES
.
If a quoting character appears within the identifier itself, double it when quoting the
identifier. For example, quote
abc`def
as
`abc``def`
.
Be aware that although string data values in MySQL normally can be quoted using either
single-quote or double-quote characters (
'abc'
,
"abc"
), that is not true when
AN
SI_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 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 PHP routine that does so:
function
quote_identifier
(
$ident
)
{
return
(
'`'
.
str_replace
(
'`'
,
'``'
,
$ident
)
.
'`'
);
}
Portability note: If you write your own identifier-quoting routines, remember that other
DBMSs may require different quoting conventions.
In contexts where identifiers are used as data values, handle them as such. If you select
information from the
INFORMATION_SCHEMA
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
cook
book
database:
SELECT
COLUMN_NAME
FROM
INFORMATION_SCHEMA
.
COLUMNS
WHERE
TABLE_SCHEMA
=
'cookbook'
AND
TABLE_NAME
=
'profile'
;
The database and table names are used here as data values, not as identifiers. Were you
to construct this statement within a program, parameterize them using placeholders,
not identifier quoting. For example, in Ruby, do this:
names
=
dbh
.
select_all
(
"SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?"
,
db_name
,
tbl_name
)
