<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>Chapter 8. Language Structure</title><link rel="stylesheet" href="mysql-html.css" type="text/css"><meta name="generator" content="DocBook XSL Stylesheets V1.69.1"><link rel="start" href="index.html" title="MySQL 5.1 Reference Manual"><link rel="up" href="index.html" title="MySQL 5.1 Reference Manual"><link rel="prev" href="optimization.html" title="Chapter 7. Optimization"><link rel="next" href="internationalization-localization.html" title="Chapter 9. Internationalization and Localization"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">Chapter 8. Language Structure</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="optimization.html">Prev</a> </td><th width="60%" align="center"> </th><td width="20%" align="right"> <a accesskey="n" href="internationalization-localization.html">Next</a></td></tr></table><hr></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="language-structure"></a>Chapter 8. Language Structure</h2></div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="language-structure.html#literals">8.1. Literal Values</a></span></dt><dd><dl><dt><span class="section"><a href="language-structure.html#string-syntax">8.1.1. Strings</a></span></dt><dt><span class="section"><a href="language-structure.html#number-syntax">8.1.2. Numbers</a></span></dt><dt><span class="section"><a href="language-structure.html#hexadecimal-values">8.1.3. Hexadecimal Values</a></span></dt><dt><span class="section"><a href="language-structure.html#boolean-values">8.1.4. Boolean Values</a></span></dt><dt><span class="section"><a href="language-structure.html#bit-field-values">8.1.5. Bit-Field Values</a></span></dt><dt><span class="section"><a href="language-structure.html#null-values">8.1.6. <code class="literal">NULL</code> Values</a></span></dt></dl></dd><dt><span class="section"><a href="language-structure.html#identifiers">8.2. Schema Object Names</a></span></dt><dd><dl><dt><span class="section"><a href="language-structure.html#identifier-qualifiers">8.2.1. Identifier Qualifiers</a></span></dt><dt><span class="section"><a href="language-structure.html#identifier-case-sensitivity">8.2.2. Identifier Case Sensitivity</a></span></dt><dt><span class="section"><a href="language-structure.html#identifier-mapping">8.2.3. Mapping of Identifiers to File Names</a></span></dt><dt><span class="section"><a href="language-structure.html#function-resolution">8.2.4. Function Name Parsing and Resolution</a></span></dt></dl></dd><dt><span class="section"><a href="language-structure.html#reserved-words">8.3. Reserved Words</a></span></dt><dt><span class="section"><a href="language-structure.html#user-variables">8.4. User-Defined Variables</a></span></dt><dt><span class="section"><a href="language-structure.html#comments">8.5. Comment Syntax</a></span></dt></dl></div><p>
This chapter discusses the rules for writing the following elements
of SQL statements when using MySQL:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Literal values such as strings and numbers
</p></li><li><p>
Identifiers such as database, table, and column names
</p></li><li><p>
Reserved words
</p></li><li><p>
User-defined and system variables
</p></li><li><p>
Comments
</p></li></ul></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="literals"></a>8.1. Literal Values</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="language-structure.html#string-syntax">8.1.1. Strings</a></span></dt><dt><span class="section"><a href="language-structure.html#number-syntax">8.1.2. Numbers</a></span></dt><dt><span class="section"><a href="language-structure.html#hexadecimal-values">8.1.3. Hexadecimal Values</a></span></dt><dt><span class="section"><a href="language-structure.html#boolean-values">8.1.4. Boolean Values</a></span></dt><dt><span class="section"><a href="language-structure.html#bit-field-values">8.1.5. Bit-Field Values</a></span></dt><dt><span class="section"><a href="language-structure.html#null-values">8.1.6. <code class="literal">NULL</code> Values</a></span></dt></dl></div><a class="indexterm" name="id4300970"></a><p>
This section describes how to write literal values in MySQL. These
include strings, numbers, hexadecimal values, boolean values, and
<code class="literal">NULL</code>. The section also covers the various
nuances and “<span class="quote">gotchas</span>” that you may run into when
dealing with these basic types in MySQL.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="string-syntax"></a>8.1.1. Strings</h3></div></div></div><a class="indexterm" name="id4301002"></a><a class="indexterm" name="id4301015"></a><a class="indexterm" name="id4301027"></a><a class="indexterm" name="id4301039"></a><p>
A string is a sequence of bytes or characters, enclosed within
either single quote (“<span class="quote"><code class="literal">'</code></span>”) or
double quote (“<span class="quote"><code class="literal">"</code></span>”) characters.
Examples:
</p><pre class="programlisting">'a string'
"another string"
</pre><a class="indexterm" name="id4301076"></a><a class="indexterm" name="id4301085"></a><p>
Quoted strings placed next to each other are concatenated to a
single string. The following lines are equivalent:
</p><pre class="programlisting">'a string'
'a' ' ' 'string'
</pre><p>
If the <a href="server-administration.html#sqlmode_ansi_quotes"><code class="literal">ANSI_QUOTES</code></a> SQL mode is
enabled, string literals can be quoted only within single quotes
because a string quoted within double quotes is interpreted as
an identifier.
</p><p>
A <em class="firstterm">binary string</em> is a string of bytes that
has no character set or collation. A <em class="firstterm">nonbinary
string</em> is a string of characters that has a
character set and collation. For both types of strings,
comparisons are based on the numeric values of the string unit.
For binary strings, the unit is the byte. For nonbinary strings
the unit is the character and some character sets allow
multi-byte characters. Character value ordering is a function of
the string collation.
</p><p>
String literals may have an optional character set introducer
and <code class="literal">COLLATE</code> clause:
</p><a class="indexterm" name="id4301144"></a><a class="indexterm" name="id4301157"></a><pre class="programlisting">[_<em class="replaceable"><code>charset_name</code></em>]'<em class="replaceable"><code>string</code></em>' [COLLATE <em class="replaceable"><code>collation_name</code></em>]
</pre><p>
Examples:
</p><pre class="programlisting">SELECT _latin1'<em class="replaceable"><code>string</code></em>';
SELECT _latin1'<em class="replaceable"><code>string</code></em>' COLLATE latin1_danish_ci;
</pre><p>
You can use
<code class="literal">N'<em class="replaceable"><code>literal</code></em>'</code> (or
<code class="literal">n'<em class="replaceable"><code>literal</code></em>'</code>) to
create a string in the national character set. These statements
are equivalent:
</p><pre class="programlisting">SELECT N'some text';
SELECT n'some text';
SELECT _utf8'some text';
</pre><p>
For more information about these forms of string syntax, see
<a href="internationalization-localization.html#charset-literal" title="9.1.3.5. Character String Literal Character Set and Collation">Section 9.1.3.5, “Character String Literal Character Set and Collation”</a>, and
<a href="internationalization-localization.html#charset-national" title="9.1.3.6. National Character Set">Section 9.1.3.6, “National Character Set”</a>.
</p><p>
Within a string, certain sequences have special meaning unless
the <a href="server-administration.html#sqlmode_no_backslash_escapes"><code class="literal">NO_BACKSLASH_ESCAPES</code></a> SQL
mode is enabled. Each of these sequences begins with a backslash
(“<span class="quote"><code class="literal">\</code></span>”), known as the
<span class="emphasis"><em>escape character</em></span>. MySQL recognizes the
following escape sequences.
</p><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><tbody><tr><td><code class="literal">\0</code>
<a class="indexterm" name="id4301290"></a>
<a class="indexterm" name="id4301298"></a></td><td>An ASCII NUL (<code class="literal">0x00</code>) character.</td></tr><tr><td><code class="literal">\'</code>
<a class="indexterm" name="id4301326"></a>
<a class="indexterm" name="id4301335"></a></td><td>A single quote (“<span class="quote"><code class="literal">'</code></span>”) character.</td></tr><tr><td><code class="literal">\"</code>
<a class="indexterm" name="id4301364"></a>
<a class="indexterm" name="id4301373"></a></td><td>A double quote (“<span class="quote"><code class="literal">"</code></span>”) character.</td></tr><tr><td><code class="literal">\b</code>
<a class="indexterm" name="id4301402"></a>
<a class="indexterm" name="id4301411"></a></td><td>A backspace character.</td></tr><tr><td><code class="literal">\n</code>
<a class="indexterm" name="id4301433"></a>
<a class="indexterm" name="id4301442"></a>
<a class="indexterm" name="id4301451"></a>
<a class="indexterm" name="id4301460"></a></td><td>A newline (linefeed) character.</td></tr><tr><td><code class="literal">\r</code>
<a class="indexterm" name="id4301482"></a>
<a class="indexterm" name="id4301491"></a>
<a class="indexterm" name="id4301500"></a></td><td>A carriage return character.</td></tr><tr><td><code class="literal">\t</code>
<a class="indexterm" name="id4301522"></a>
<a class="indexterm" name="id4301531"></a></td><td>A tab character.</td></tr><tr><td><code class="literal">\Z</code>
<a class="indexterm" name="id4301553"></a>
<a class="indexterm" name="id4301562"></a></td><td>ASCII 26 (Control-Z). See note following the table.</td></tr><tr><td><code class="literal">\\</code>
<a class="indexterm" name="id4301585"></a>
<a class="indexterm" name="id4301594"></a></td><td>A backslash (“<span class="quote"><code class="literal">\</code></span>”) character.</td></tr><tr><td><code class="literal">\%</code>
<a class="indexterm" name="id4301623"></a>
<a class="indexterm" name="id4301632"></a></td><td>A “<span class="quote"><code class="literal">%</code></span>” character. See note following the
table.</td></tr><tr><td><code class="literal">\_</code>
<a class="indexterm" name="id4301661"></a>
<a class="indexterm" name="id4301670"></a></td><td>A “<span class="quote"><code class="literal">_</code></span>” character. See note following the
table.</td></tr></tbody></table></div><p>
For all other escape sequences, backslash is ignored. That is,
the escaped character is interpreted as if it was not escaped.
For example, “<span class="quote"><code class="literal">\x</code></span>” is just
“<span class="quote"><code class="literal">x</code></span>”.
</p><p>
These sequences are case sensitive. For example,
“<span class="quote"><code class="literal">\b</code></span>” is interpreted as a
backspace, but “<span class="quote"><code class="literal">\B</code></span>” is
interpreted as “<span class="quote"><code class="literal">B</code></span>”.
</p><p>
The ASCII 26 character can be encoded as
“<span class="quote"><code class="literal">\Z</code></span>” to enable you to work
around the problem that ASCII 26 stands for END-OF-FILE on
Windows. ASCII 26 within a file causes problems if you try to
use <code class="literal">mysql <em class="replaceable"><code>db_name</code></em> <
<em class="replaceable"><code>file_name</code></em></code>.
</p><p>
Escape processing is done according to the character set
indicated by the
<a href="server-administration.html#sysvar_character_set_connection"><code class="literal">character_set_connection</code></a> system
variable. This is true even for strings that are preceded by an
introducer that indicates a different character set, as
discussed in <a href="internationalization-localization.html#charset-literal" title="9.1.3.5. Character String Literal Character Set and Collation">Section 9.1.3.5, “Character String Literal Character Set and Collation”</a>.
</p><p>
The “<span class="quote"><code class="literal">\%</code></span>” and
“<span class="quote"><code class="literal">\_</code></span>” sequences are used to
search for literal instances of
“<span class="quote"><code class="literal">%</code></span>” and
“<span class="quote"><code class="literal">_</code></span>” in pattern-matching contexts
where they would otherwise be interpreted as wildcard
characters. See the description of the
<a href="functions.html#operator_like"><code class="literal">LIKE</code></a> operator in
<a href="functions.html#string-comparison-functions" title="11.4.1. String Comparison Functions">Section 11.4.1, “String Comparison Functions”</a>. If you use
“<span class="quote"><code class="literal">\%</code></span>” or
“<span class="quote"><code class="literal">\_</code></span>” outside of pattern-matching
contexts, they evaluate to the strings
“<span class="quote"><code class="literal">\%</code></span>” and
“<span class="quote"><code class="literal">\_</code></span>”, not to
“<span class="quote"><code class="literal">%</code></span>” and
“<span class="quote"><code class="literal">_</code></span>”.
</p><a class="indexterm" name="id4301864"></a><p>
There are several ways to include quote characters within a
string:
</p><div class="itemizedlist"><ul type="disc"><li><p>
A “<span class="quote"><code class="literal">'</code></span>” inside a string quoted
with “<span class="quote"><code class="literal">'</code></span>” may be written as
“<span class="quote"><code class="literal">''</code></span>”.
</p></li><li><p>
A “<span class="quote"><code class="literal">"</code></span>” inside a string quoted
with “<span class="quote"><code class="literal">"</code></span>” may be written as
“<span class="quote"><code class="literal">""</code></span>”.
</p></li><li><p>
Precede the quote character by an escape character
(“<span class="quote"><code class="literal">\</code></span>”).
</p></li><li><p>
A “<span class="quote"><code class="literal">'</code></span>” inside a string quoted
with “<span class="quote"><code class="literal">"</code></span>” needs no special
treatment and need not be doubled or escaped. In the same
way, “<span class="quote"><code class="literal">"</code></span>” inside a string
quoted with “<span class="quote"><code class="literal">'</code></span>” needs no
special treatment.
</p></li></ul></div><p>
The following <a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a> statements
demonstrate how quoting and escaping work:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';</code></strong>
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+
mysql> <strong class="userinput"><code>SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";</code></strong>
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+
mysql> <strong class="userinput"><code>SELECT 'This\nIs\nFour\nLines';</code></strong>
+--------------------+
| This
Is
Four
Lines |
+--------------------+
mysql> <strong class="userinput"><code>SELECT 'disappearing\ backslash';</code></strong>
+------------------------+
| disappearing backslash |
+------------------------+
</pre><a class="indexterm" name="id4302036"></a><p>
If you want to insert binary data into a string column (such as
a <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> column), the following
characters must be represented by escape sequences.
</p><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><tbody><tr><td><code class="literal">NUL</code></td><td><code class="literal">NUL</code> byte (<code class="literal">0x00</code>). Represent this
character by “<span class="quote"><code class="literal">\0</code></span>” (a
backslash followed by an ASCII
“<span class="quote"><code class="literal">0</code></span>” character).</td></tr><tr><td><code class="literal">\</code></td><td>Backslash (ASCII 92). Represent this character by
“<span class="quote"><code class="literal">\\</code></span>”.</td></tr><tr><td><code class="literal">'</code></td><td>Single quote (ASCII 39). Represent this character by
“<span class="quote"><code class="literal">\'</code></span>”.</td></tr><tr><td><code class="literal">"</code></td><td>Double quote (ASCII 34). Represent this character by
“<span class="quote"><code class="literal">\"</code></span>”.</td></tr></tbody></table></div><a class="indexterm" name="id4302175"></a><a class="indexterm" name="id4302184"></a><a class="indexterm" name="id4302196"></a><a class="indexterm" name="id4302205"></a><p>
When writing application programs, any string that might contain
any of these special characters must be properly escaped before
the string is used as a data value in an SQL statement that is
sent to the MySQL server. You can do this in two ways:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Process the string with a function that escapes the special
characters. In a C program, you can use the
<a href="connectors-apis.html#mysql-real-escape-string" title="21.9.3.53. mysql_real_escape_string()"><code class="literal">mysql_real_escape_string()</code></a> C
API function to escape characters. See
<a href="connectors-apis.html#mysql-real-escape-string" title="21.9.3.53. mysql_real_escape_string()">Section 21.9.3.53, “<code class="literal">mysql_real_escape_string()</code>”</a>. The Perl DBI
interface provides a <code class="literal">quote</code> method to
convert special characters to the proper escape sequences.
See <a href="connectors-apis.html#apis-perl" title="21.11. MySQL Perl API">Section 21.11, “MySQL Perl API”</a>. Other language interfaces
may provide a similar capability.
</p></li><li><p>
As an alternative to explicitly escaping special characters,
many MySQL APIs provide a placeholder capability that
enables you to insert special markers into a statement
string, and then bind data values to them when you issue the
statement. In this case, the API takes care of escaping
special characters in the values for you.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="number-syntax"></a>8.1.2. Numbers</h3></div></div></div><a class="indexterm" name="id4302275"></a><a class="indexterm" name="id4302284"></a><a class="indexterm" name="id4302296"></a><a class="indexterm" name="id4302305"></a><a class="indexterm" name="id4302314"></a><a class="indexterm" name="id4302323"></a><a class="indexterm" name="id4302332"></a><p>
Integers are represented as a sequence of digits. Floats use
“<span class="quote"><code class="literal">.</code></span>” as a decimal separator.
Either type of number may be preceded by
“<span class="quote"><code class="literal">-</code></span>” or
“<span class="quote"><code class="literal">+</code></span>” to indicate a negative or
positive value, respectively
</p><p>
Examples of valid integers:
</p><pre class="programlisting">1221
0
-32
</pre><p>
Examples of valid floating-point numbers:
</p><pre class="programlisting">294.42
-32032.6809e+10
148.00
</pre><p>
An integer may be used in a floating-point context; it is
interpreted as the equivalent floating-point number.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="hexadecimal-values"></a>8.1.3. Hexadecimal Values</h3></div></div></div><a class="indexterm" name="id4302398"></a><p>
MySQL supports hexadecimal values, written using
<code class="literal">X'<em class="replaceable"><code>val</code></em>'</code>,
<code class="literal">x'<em class="replaceable"><code>val</code></em>'</code>, or
<code class="literal">0x<em class="replaceable"><code>val</code></em></code> format,
where <em class="replaceable"><code>val</code></em> contains hexadecimal digits
(<code class="literal">0..9</code>, <code class="literal">A..F</code>). Lettercase
of the digits does not matter. For values written using
<code class="literal">X'<em class="replaceable"><code>val</code></em>'</code> or
<code class="literal">x'<em class="replaceable"><code>val</code></em>'</code> format,
<em class="replaceable"><code>val</code></em> must contain an even number of
digits. For values written using
<code class="literal">0x<em class="replaceable"><code>val</code></em> syntax</code>,
values that contain an odd number of digits are treated as
having an extra leading <code class="literal">0</code>. For example,
<code class="literal">0x0a</code> and <code class="literal">0xaaa</code> are
interpreted as <code class="literal">0x0a</code> and
<code class="literal">0x0aaa</code>.
</p><p>
In numeric contexts, hexadecimal values act like integers
(64-bit precision). In string contexts, they act like binary
strings, where each pair of hex digits is converted to a
character:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT X'4D7953514C';</code></strong>
-> 'MySQL'
mysql> <strong class="userinput"><code>SELECT 0x0a+0;</code></strong>
-> 10
mysql> <strong class="userinput"><code>SELECT 0x5061756c;</code></strong>
-> 'Paul'
</pre><p>
The default type of a hexadecimal value is a string. If you want
to ensure that the value is treated as a number, you can use
<a href="functions.html#function_cast"><code class="literal">CAST(... AS UNSIGNED)</code></a>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT 0x41, CAST(0x41 AS UNSIGNED);</code></strong>
-> 'A', 65
</pre><p>
The <code class="literal">X'<em class="replaceable"><code>hexstring</code></em>'</code>
syntax is based on standard SQL. The <code class="literal">0x</code>
syntax is based on ODBC. Hexadecimal strings are often used by
ODBC to supply values for <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a>
columns.
</p><p>
You can convert a string or a number to a string in hexadecimal
format with the <a href="functions.html#function_hex"><code class="literal">HEX()</code></a> function:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT HEX('cat');</code></strong>
-> '636174'
mysql> <strong class="userinput"><code>SELECT 0x636174;</code></strong>
-> 'cat'
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="boolean-values"></a>8.1.4. Boolean Values</h3></div></div></div><a class="indexterm" name="id4302632"></a><a class="indexterm" name="id4302641"></a><p>
The constants <code class="literal">TRUE</code> and
<code class="literal">FALSE</code> evaluate to <code class="literal">1</code> and
<code class="literal">0</code>, respectively. The constant names can be
written in any lettercase.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT TRUE, true, FALSE, false;</code></strong>
-> 1, 1, 0, 0
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="bit-field-values"></a>8.1.5. Bit-Field Values</h3></div></div></div><p>
Bit-field values can be written using
<code class="literal">b'<em class="replaceable"><code>value</code></em>'</code> or
<code class="literal">0b<em class="replaceable"><code>value</code></em></code> notation.
<em class="replaceable"><code>value</code></em> is a binary value written using
zeros and ones.
</p><p>
Bit-field notation is convenient for specifying values to be
assigned to <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BIT</code></a> columns:
</p><pre class="programlisting">
mysql> <strong class="userinput"><code>CREATE TABLE t (b BIT(8));</code></strong>
mysql> <strong class="userinput"><code>INSERT INTO t SET b = b'11111111';</code></strong>
mysql> <strong class="userinput"><code>INSERT INTO t SET b = b'1010';</code></strong>
mysql> <strong class="userinput"><code>INSERT INTO t SET b = b'0101';</code></strong>
</pre><p>
Bit values are returned as binary values. To display them in
printable form, add 0 or use a conversion function such as
<a href="functions.html#function_bin"><code class="literal">BIN()</code></a>. High-order 0 bits are not
displayed in the converted value.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT b+0, BIN(b+0), OCT(b+0), HEX(b+0) FROM t;</code></strong>
+------+----------+----------+----------+
| b+0 | BIN(b+0) | OCT(b+0) | HEX(b+0) |
+------+----------+----------+----------+
| 255 | 11111111 | 377 | FF |
| 10 | 1010 | 12 | A |
| 5 | 101 | 5 | 5 |
+------+----------+----------+----------+
</pre><p>
Bit values assigned to user variables are treated as binary
strings. To assign a bit value as a number to a user variable,
use <a href="functions.html#function_cast"><code class="literal">CAST()</code></a> or
<code class="literal">+0</code>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @v1 = 0b1000001;</code></strong>
mysql> <strong class="userinput"><code>SET @v2 = CAST(0b1000001 AS UNSIGNED), @v3 = 0b1000001+0;</code></strong>
mysql> <strong class="userinput"><code>SELECT @v1, @v2, @v3;</code></strong>
+------+------+------+
| @v1 | @v2 | @v3 |
+------+------+------+
| A | 65 | 65 |
+------+------+------+
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="null-values"></a>8.1.6. <code class="literal">NULL</code> Values</h3></div></div></div><a class="indexterm" name="id4302868"></a><p>
The <code class="literal">NULL</code> value means “<span class="quote">no data.</span>”
<code class="literal">NULL</code> can be written in any lettercase. A
synonym is <code class="literal">\N</code> (case sensitive).
</p><p>
For text file import or export operations performed with
<a href="sql-syntax.html#load-data" title="12.2.6. LOAD DATA INFILE
Syntax"><code class="literal">LOAD DATA
INFILE</code></a> or
<a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT ... INTO
OUTFILE</code></a>, <code class="literal">NULL</code> is represented by the
<code class="literal">\N</code> sequence. See <a href="sql-syntax.html#load-data" title="12.2.6. LOAD DATA INFILE
Syntax">Section 12.2.6, “<code class="literal">LOAD DATA INFILE</code>
Syntax”</a>.
</p><p>
Be aware that the <code class="literal">NULL</code> value is different
from values such as <code class="literal">0</code> for numeric types or
the empty string for string types. For more information, see
<a href="error-handling.html#problems-with-null" title="B.1.5.3. Problems with NULL Values">Section B.1.5.3, “Problems with <code class="literal">NULL</code> Values”</a>.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="identifiers"></a>8.2. Schema Object Names</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="language-structure.html#identifier-qualifiers">8.2.1. Identifier Qualifiers</a></span></dt><dt><span class="section"><a href="language-structure.html#identifier-case-sensitivity">8.2.2. Identifier Case Sensitivity</a></span></dt><dt><span class="section"><a href="language-structure.html#identifier-mapping">8.2.3. Mapping of Identifiers to File Names</a></span></dt><dt><span class="section"><a href="language-structure.html#function-resolution">8.2.4. Function Name Parsing and Resolution</a></span></dt></dl></div><a class="indexterm" name="id4302968"></a><a class="indexterm" name="id4302977"></a><a class="indexterm" name="id4302986"></a><a class="indexterm" name="id4302995"></a><a class="indexterm" name="id4303007"></a><a class="indexterm" name="id4303020"></a><a class="indexterm" name="id4303032"></a><a class="indexterm" name="id4303045"></a><p>
Certain objects within MySQL, including database, table, index,
column, alias, view, stored procedure, partition, tablespace, and
other object names are known as identifiers. This section
describes the allowable syntax for identifiers in MySQL.
<a href="language-structure.html#identifier-case-sensitivity" title="8.2.2. Identifier Case Sensitivity">Section 8.2.2, “Identifier Case Sensitivity”</a>, describes which
types of identifiers are case sensitive and under what conditions.
</p><a class="indexterm" name="id4303068"></a><a class="indexterm" name="id4303080"></a><a class="indexterm" name="id4303089"></a><a class="indexterm" name="id4303098"></a><p>
An identifier may be quoted or unquoted. If an identifier contains
special characters or is a reserved word, you
<span class="emphasis"><em>must</em></span> quote it whenever you refer to it. The
set of alphanumeric characters from the current character set,
“<span class="quote"><code class="literal">_</code></span>”, and
“<span class="quote"><code class="literal">$</code></span>” are not special. Reserved
words are listed at <a href="language-structure.html#reserved-words" title="8.3. Reserved Words">Section 8.3, “Reserved Words”</a>. (Exception:
A reserved word that follows a period in a qualified name must be
an identifier, so it need not be quoted.)
</p><p>
The identifier quote character is the backtick
(“<span class="quote"><code class="literal">`</code></span>”):
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM `select` WHERE `select`.id > 100;</code></strong>
</pre><p>
If the <a href="server-administration.html#sqlmode_ansi_quotes"><code class="literal">ANSI_QUOTES</code></a> SQL mode is
enabled, it is also allowable to quote identifiers within double
quotes:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE "test" (col INT);</code></strong>
ERROR 1064: You have an error in your SQL syntax...
mysql> <strong class="userinput"><code>SET sql_mode='ANSI_QUOTES';</code></strong>
mysql> <strong class="userinput"><code>CREATE TABLE "test" (col INT);</code></strong>
Query OK, 0 rows affected (0.00 sec)
</pre><p>
The <a href="server-administration.html#sqlmode_ansi_quotes"><code class="literal">ANSI_QUOTES</code></a> mode causes the
server to interpret double-quoted strings as identifiers.
Consequently, when this mode is enabled, string literals must be
enclosed within single quotes. They cannot be enclosed within
double quotes. The server SQL mode is controlled as described in
<a href="server-administration.html#server-sql-mode" title="5.1.8. Server SQL Modes">Section 5.1.8, “Server SQL Modes”</a>.
</p><p>
Identifier quote characters can be included within an identifier
if you quote the identifier. If the character to be included
within the identifier is the same as that used to quote the
identifier itself, then you need to double the character. The
following statement creates a table named <code class="literal">a`b</code>
that contains a column named <code class="literal">c"d</code>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE `a``b` (`c"d` INT);</code></strong>
</pre><p>
Aliases may be quoted either as identifiers or as strings:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT 1 AS `one`, 2 AS 'two';</code></strong>
+-----+-----+
| one | two |
+-----+-----+
| 1 | 2 |
+-----+-----+
</pre><p>
Identifiers may begin with a digit but unless quoted may not
consist solely of digits.
</p><p>
It is recommended that you do not use names that begin with
<code class="literal"><em class="replaceable"><code>M</code></em>e</code> or
<code class="literal"><em class="replaceable"><code>M</code></em>e<em class="replaceable"><code>N</code></em></code>,
where <em class="replaceable"><code>M</code></em> and
<em class="replaceable"><code>N</code></em> are integers. For example, avoid
using <code class="literal">1e</code> as an identifier, because an
expression such as <code class="literal">1e+3</code> is ambiguous. Depending
on context, it might be interpreted as the expression <code class="literal">1e
+ 3</code> or as the number <code class="literal">1e+3</code>.
</p><p>
Be careful when using <a href="functions.html#function_md5"><code class="literal">MD5()</code></a> to
produce table names because it can produce names in illegal or
ambiguous formats such as those just described.
</p><p>
A user variable cannot be used directly in an SQL statement as an
identifier or as part of an identifier. See
<a href="language-structure.html#user-variables" title="8.4. User-Defined Variables">Section 8.4, “User-Defined Variables”</a>, for more information and
examples of workarounds.
</p><p>
There are some restrictions on the characters that may appear in
identifiers:
</p><div class="itemizedlist"><ul type="disc"><li><p>
No identifier can contain ASCII NUL (<code class="literal">0x00</code>).
</p></li><li><p>
Database, table, and column names should not end with space
characters.
</p></li><li><p>
Before MySQL 5.1.6, database and table names cannot contain
“<span class="quote"><code class="literal">/</code></span>”,
“<span class="quote"><code class="literal">\</code></span>”,
“<span class="quote"><code class="literal">.</code></span>”, or characters that are
not allowed in file names.
</p></li></ul></div><a class="indexterm" name="id4303392"></a><p>
As of MySQL 5.1.6, special characters in database and table names
are encoded in the corresponding file system names as described in
<a href="language-structure.html#identifier-mapping" title="8.2.3. Mapping of Identifiers to File Names">Section 8.2.3, “Mapping of Identifiers to File Names”</a>. If you have databases or
tables from an older version of MySQL that contain special
characters and for which the underlying directory names or file
names have not been updated to use the new encoding, the server
displays their names with a prefix of
<code class="literal">#mysql50#</code>. For information about referring to
such names or converting them to the newer encoding, see that
section.
</p><p>
The following table describes the maximum length for each type of
identifier.
</p><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Identifier</strong></span></td><td><span class="bold"><strong>Maximum Length (characters)</strong></span></td></tr><tr><td>Database</td><td>64</td></tr><tr><td>Table</td><td>64</td></tr><tr><td>Column</td><td>64</td></tr><tr><td>Index</td><td>64</td></tr><tr><td>Constraint</td><td>64</td></tr><tr><td>Stored Function or Procedure</td><td>64</td></tr><tr><td>Trigger</td><td>64</td></tr><tr><td>View</td><td>64</td></tr><tr><td>Event</td><td>64</td></tr><tr><td>Tablespace</td><td>64</td></tr><tr><td>Log File Group</td><td>64</td></tr><tr><td>Alias</td><td>256 (see exception following table)</td></tr></tbody></table></div><p>
As of MySQL 5.1.23, aliases for column names in
<a href="sql-syntax.html#create-view" title="12.1.20. CREATE VIEW Syntax"><code class="literal">CREATE VIEW</code></a> statements are checked
against the maximum column length of 64 characters (not the
maximum alias length of 256 characters).
</p><p>
Identifiers are stored using Unicode (UTF-8). This applies to
identifiers in table definitions that are stored in
<code class="filename">.frm</code> files and to identifiers stored in the
grant tables in the <code class="literal">mysql</code> database. The sizes
of the identifier string columns in the grant tables are measured
in characters. You can use multi-byte characters without reducing
the number of characters allowed for values stored in these
columns, something not true prior to MySQL 4.1. The allowable
Unicode characters are those in the Basic Multilingual Plane
(BMP). Supplementary characters are not allowed.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="identifier-qualifiers"></a>8.2.1. Identifier Qualifiers</h3></div></div></div><p>
MySQL allows names that consist of a single identifier or
multiple identifiers. The components of a multiple-part name
must be separated by period
(“<span class="quote"><code class="literal">.</code></span>”) characters. The initial
parts of a multiple-part name act as qualifiers that affect the
context within which the final identifier is interpreted.
</p><p>
In MySQL, you can refer to a table column using any of the
following forms.
</p><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Column Reference</strong></span></td><td><span class="bold"><strong>Meaning</strong></span></td></tr><tr><td><em class="replaceable"><code>col_name</code></em></td><td>The column <em class="replaceable"><code>col_name</code></em> from whichever table used
in the statement contains a column of that name.</td></tr><tr><td><em class="replaceable"><code>tbl_name.col_name</code></em></td><td>The column <em class="replaceable"><code>col_name</code></em> from table
<em class="replaceable"><code>tbl_name</code></em> of the default
database.</td></tr><tr><td><em class="replaceable"><code>db_name.tbl_name.col_name</code></em></td><td>The column <em class="replaceable"><code>col_name</code></em> from table
<em class="replaceable"><code>tbl_name</code></em> of the database
<em class="replaceable"><code>db_name</code></em>.</td></tr></tbody></table></div><p>
If any components of a multiple-part name require quoting, quote
them individually rather than quoting the name as a whole. For
example, write <code class="literal">`my-table`.`my-column`</code>, not
<code class="literal">`my-table.my-column`</code>.
</p><p>
A reserved word that follows a period in a qualified name must
be an identifier, so in that context it need not be quoted.
</p><p>
You need not specify a <em class="replaceable"><code>tbl_name</code></em> or
<em class="replaceable"><code>db_name.tbl_name</code></em> prefix for a column
reference in a statement unless the reference would be
ambiguous. Suppose that tables <code class="literal">t1</code> and
<code class="literal">t2</code> each contain a column
<code class="literal">c</code>, and you retrieve <code class="literal">c</code> in a
<a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a> statement that uses both
<code class="literal">t1</code> and <code class="literal">t2</code>. In this case,
<code class="literal">c</code> is ambiguous because it is not unique among
the tables used in the statement. You must qualify it with a
table name as <code class="literal">t1.c</code> or <code class="literal">t2.c</code>
to indicate which table you mean. Similarly, to retrieve from a
table <code class="literal">t</code> in database <code class="literal">db1</code>
and from a table <code class="literal">t</code> in database
<code class="literal">db2</code> in the same statement, you must refer to
columns in those tables as
<code class="literal">db1.t.<em class="replaceable"><code>col_name</code></em></code> and
<code class="literal">db2.t.<em class="replaceable"><code>col_name</code></em></code>.
</p><a class="indexterm" name="id4303865"></a><a class="indexterm" name="id4303874"></a><p>
The syntax
<code class="literal"><em class="replaceable"><code>.tbl_name</code></em></code> means
the table <em class="replaceable"><code>tbl_name</code></em> in the default
database. This syntax is accepted for ODBC compatibility because
some ODBC programs prefix table names with a
“<span class="quote"><code class="literal">.</code></span>” character.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="identifier-case-sensitivity"></a>8.2.2. Identifier Case Sensitivity</h3></div></div></div><a class="indexterm" name="id4303918"></a><a class="indexterm" name="id4303931"></a><a class="indexterm" name="id4303943"></a><a class="indexterm" name="id4303956"></a><a class="indexterm" name="id4303968"></a><a class="indexterm" name="id4303980"></a><a class="indexterm" name="id4303993"></a><a class="indexterm" name="id4304005"></a><p>
In MySQL, databases correspond to directories within the data
directory. Each table within a database corresponds to at least
one file within the database directory (and possibly more,
depending on the storage engine). Triggers also correspond to
files. Consequently, the case sensitivity of the underlying
operating system plays a part in the case sensitivity of
database and table names. This means database, table, and
trigger names are not case sensitive in Windows, but are case
sensitive in most varieties of Unix. One notable exception is
Mac OS X, which is Unix-based but uses a default file system
type (HFS+) that is not case sensitive. However, Mac OS X also
supports UFS volumes, which are case sensitive just as on any
Unix. See <a href="introduction.html#extensions-to-ansi" title="1.7.4. MySQL Extensions to Standard SQL">Section 1.7.4, “MySQL Extensions to Standard SQL”</a>. The
<a href="server-administration.html#sysvar_lower_case_table_names"><code class="literal">lower_case_table_names</code></a> system
variable also affects how the server handles identifier case
sensitivity, as described later in this section.
</p><p class="mnmas"><b>MySQL Enterprise</b>
<a href="server-administration.html#sysvar_lower_case_table_names"><code class="literal">lower_case_table_names</code></a> is
just one of the system variables monitored by the MySQL
Enterprise Monitor. For information about subscribing to this
service, see
<a href="http://www.mysql.com/products/enterprise/advisors.html" target="_top">http://www.mysql.com/products/enterprise/advisors.html</a>.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
Although database, table, and trigger names are not case
sensitive on some platforms, you should not refer to one of
these using different cases within the same statement. The
following statement would not work because it refers to a
table both as <code class="literal">my_table</code> and as
<code class="literal">MY_TABLE</code>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM my_table WHERE MY_TABLE.col=1;</code></strong>
</pre></div><p>
Column, index, stored routine, and event names are not case
sensitive on any platform, nor are column aliases.
</p><p>
However, names of triggers and logfile groups are case
sensitive. This differs from standard SQL.
</p><p>
By default, table aliases are case sensitive on Unix, but not so
on Windows or Mac OS X. The following statement would not work
on Unix, because it refers to the alias both as
<code class="literal">a</code> and as <code class="literal">A</code>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT <em class="replaceable"><code>col_name</code></em> FROM <em class="replaceable"><code>tbl_name</code></em> AS a</code></strong>
-> <strong class="userinput"><code>WHERE a.<em class="replaceable"><code>col_name</code></em> = 1 OR A.<em class="replaceable"><code>col_name</code></em> = 2;</code></strong>
</pre><p>
However, this same statement is permitted on Windows. To avoid
problems caused by such differences, it is best to adopt a
consistent convention, such as always creating and referring to
databases and tables using lowercase names. This convention is
recommended for maximum portability and ease of use.
</p><p>
How table and database names are stored on disk and used in
MySQL is affected by the
<a href="server-administration.html#sysvar_lower_case_table_names"><code class="literal">lower_case_table_names</code></a> system
variable, which you can set when starting
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a>.
<a href="server-administration.html#sysvar_lower_case_table_names"><code class="literal">lower_case_table_names</code></a> can take
the values shown in the following table. This variable does
<span class="emphasis"><em>not</em></span> affect case sensitivity of trigger
identifiers. On Unix, the default value of
<a href="server-administration.html#sysvar_lower_case_table_names"><code class="literal">lower_case_table_names</code></a> is 0. On
Windows the default value is 1. On Mac OS X, the default value
is 2.
</p><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Value</strong></span></td><td><span class="bold"><strong>Meaning</strong></span></td></tr><tr><td><code class="literal">0</code></td><td>Table and database names are stored on disk using the lettercase
specified in the <a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE
TABLE</code></a> or <a href="sql-syntax.html#create-database" title="12.1.10. CREATE DATABASE Syntax"><code class="literal">CREATE
DATABASE</code></a> statement. Name comparisons are case
sensitive. Note that if you force this variable to 0
with
<a href="server-administration.html#sysvar_lower_case_table_names"><code class="option">--lower-case-table-names=0</code></a>
on a case-insensitive file system and access
<code class="literal">MyISAM</code> tablenames using different
lettercases, index corruption may result.</td></tr><tr><td><code class="literal">1</code></td><td>Table names are stored in lowercase on disk and name comparisons are not
case sensitive. MySQL converts all table names to
lowercase on storage and lookup. This behavior also
applies to database names and table aliases.</td></tr><tr><td><code class="literal">2</code></td><td>Table and database names are stored on disk using the lettercase
specified in the <a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE
TABLE</code></a> or <a href="sql-syntax.html#create-database" title="12.1.10. CREATE DATABASE Syntax"><code class="literal">CREATE
DATABASE</code></a> statement, but MySQL converts them to
lowercase on lookup. Name comparisons are not case
sensitive. This works <span class="emphasis"><em>only</em></span> on file
systems that are not case sensitive!
<code class="literal">InnoDB</code> table names are stored in
lowercase, as for
<code class="literal">lower_case_table_names=1</code>.</td></tr></tbody></table></div><p>
If you are using MySQL on only one platform, you do not normally
have to change the
<a href="server-administration.html#sysvar_lower_case_table_names"><code class="literal">lower_case_table_names</code></a> variable
from its default value. However, you may encounter difficulties
if you want to transfer tables between platforms that differ in
file system case sensitivity. For example, on Unix, you can have
two different tables named <code class="literal">my_table</code> and
<code class="literal">MY_TABLE</code>, but on Windows these two names are
considered identical. To avoid data transfer problems arising
from lettercase of database or table names, you have two
options:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Use <code class="literal">lower_case_table_names=1</code> on all
systems. The main disadvantage with this is that when you
use <a href="sql-syntax.html#show-tables" title="12.5.5.39. SHOW TABLES Syntax"><code class="literal">SHOW TABLES</code></a> or
<a href="sql-syntax.html#show-databases" title="12.5.5.15. SHOW DATABASES Syntax"><code class="literal">SHOW DATABASES</code></a>, you do not
see the names in their original lettercase.
</p></li><li><p>
Use <code class="literal">lower_case_table_names=0</code> on Unix and
<code class="literal">lower_case_table_names=2</code> on Windows. This
preserves the lettercase of database and table names. The
disadvantage of this is that you must ensure that your
statements always refer to your database and table names
with the correct lettercase on Windows. If you transfer your
statements to Unix, where lettercase is significant, they do
not work if the lettercase is incorrect.
</p><p>
<span class="bold"><strong>Exception</strong></span>: If you are using
<code class="literal">InnoDB</code> tables and you are trying to avoid
these data transfer problems, you should set
<a href="server-administration.html#sysvar_lower_case_table_names"><code class="literal">lower_case_table_names</code></a> to 1
on all platforms to force names to be converted to
lowercase.
</p></li></ul></div><p>
If you plan to set the
<a href="server-administration.html#sysvar_lower_case_table_names"><code class="literal">lower_case_table_names</code></a> system
variable to 1 on Unix, you must first convert your old database
and table names to lowercase before stopping
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> and restarting it with the new
variable setting.
</p><p>
Object names may be considered duplicates if their uppercase
forms are equal according to a binary collation. That is true
for names of cursors, conditions, functions, procedures,
savepoints, stored routine parameters, stored program local
variables, and plugins. It is not true for names of columns,
constraints, databases, partitions, statements prepared with
<a href="sql-syntax.html#prepare" title="12.7.1. PREPARE Syntax"><code class="literal">PREPARE</code></a>, tables, triggers, users,
and user-defined variables.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="identifier-mapping"></a>8.2.3. Mapping of Identifiers to File Names</h3></div></div></div><p>
There is a correspondence between database and table identifiers
and names in the file system. For the basic structure, MySQL
represents each database as a directory in the data directory,
and each table by one or more files in the appropriate database
directory. For the table format files
(<code class="filename">.FRM</code>), the data is always stored in this
structure and location.
</p><p>
For the data and index files, the exact representation on disk
is storage engine specific. These files may be stored in the
same location as the <code class="filename">FRM</code> files, or the
information may be stored separate file.
<code class="literal">InnoDB</code> data is stored in the InnoDB data
files. If you are using tablespaces with
<code class="literal">InnoDB</code>, then the specific tablespace files
you create are used instead.
</p><p>
Before MySQL 5.1.6, there are some limitations on the characters
that can be used in identifiers for database objects that
correspond to file system objects. For example, path name
separator characters are disallowed, and
“<span class="quote"><code class="literal">.</code></span>” is disallowed because it
begins the extension for table files.
</p><p>
As of MySQL 5.1.6, any character is legal in database or table
identifiers except ASCII NUL (<code class="literal">0x00</code>). MySQL
encodes any characters that are problematic in the corresponding
file system objects when it creates database directories or
table files:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Basic Latin letters (<code class="literal">a..zA..Z</code>) and digits
(<code class="literal">0..9</code>) are encoded as is. Consequently,
their case sensitivity directly depends on file system
features.
</p></li><li><p>
All other national letters from alphabets that have
uppercase/lowercase mapping are encoded as follows:
</p><pre class="programlisting">Code range Pattern Number Used Unused Blocks
-----------------------------------------------------------------------------
00C0..017F [@][0..4][g..z] 5*20= 100 97 3 Latin1 Supplement + Ext A
0370..03FF [@][5..9][g..z] 5*20= 100 88 12 Greek + Coptic
0400..052F [@][g..z][0..6] 20*7= 140 140 137 Cyrillic
0530..058F [@][g..z][7..8] 20*2= 40 38 2 Armenian
2160..217F [@][g..z][9] 20*1= 20 16 4 Number Forms
0180..02AF [@][g..z][a..k] 28*11=220 203 17 Latin Ext B + IPA
1E00..0EFF [@][g..z][l..r] 20*7= 140 136 4 Latin Additional Extended
1F00..1FFF [@][g..z][s..z] 20*8= 160 144 16 Greek Extended
.... .... [@][a..f][g..z] 6*20= 120 0 120 RESERVED
24B6..24E9 [@][@][a..z] 26 26 0 Enclosed Alphanumerics
FF21..FF5A [@][a..z][@] 26 26 0 Full Width forms
</pre><p>
One of the bytes in the sequence encodes lettercase. For
example: <code class="literal">LATIN CAPITAL LETTER A WITH
GRAVE</code> is encoded as <code class="literal">@0G</code>,
whereas <code class="literal">LATIN SMALL LETTER A WITH GRAVE</code>
is encoded as <code class="literal">@0g</code>. Here the third byte
(<code class="literal">G</code> or <code class="literal">g</code>) indicates
lettercase. (On a case-insensitive file system, both letters
will be treated as the same.)
</p><p>
For some blocks, such as Cyrillic, the second byte
determines lettercase. For other blocks, such as Latin1
Supplement, the third byte determines lettercase. If two
bytes in the sequence are letters (as in Greek Extended),
the leftmost letter character stands for lettercase. All
other letter bytes must be in lowercase.
</p></li><li><p>
All nonletter characters, as well as letters from alphabets
that do not have uppercase/lowercase mapping (such as
Hebrew) are encoded using hexadecimal representation using
lowercase letters for hex digits <code class="literal">a..f</code>:
</p><pre class="programlisting">0x003F -> @003f
0xFFFF -> @ffff
</pre><p>
The hexadecimal values correspond to character values in the
<code class="literal">ucs2</code> double-byte character set.
</p></li></ul></div><p>
On Windows, some names such as <code class="literal">nul</code>,
<code class="literal">prn</code>, and <code class="literal">aux</code> cannot be
used as file names because they are reserved as device names. As
of MySQL 5.1.10, these are allowable names in MySQL. They are
encoded by appending <code class="literal">@@@</code> to the name when the
server creates the corresponding file or directory. This occurs
on all platforms for portability of the corresponding database
object between platforms.
</p><a class="indexterm" name="id4304704"></a><p>
If you have databases or tables from a version of MySQL older
than 5.1.6 that contain special characters and for which the
underlying directory names or file names have not been updated
to use the new encoding, the server displays their names with a
prefix of <code class="literal">#mysql50#</code> in the output from
<code class="literal">INFORMATION_SCHEMA</code> tables or
<a href="sql-syntax.html#show" title="12.5.5. SHOW Syntax"><code class="literal">SHOW</code></a> statements. For example, if
you have a table named <code class="literal">a@b</code> and its name
encoding has not been updated, <a href="sql-syntax.html#show-tables" title="12.5.5.39. SHOW TABLES Syntax"><code class="literal">SHOW
TABLES</code></a> displays it like this:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW TABLES;</code></strong>
+----------------+
| Tables_in_test |
+----------------+
| #mysql50#a@b |
+----------------+
</pre><p>
To refer to such a name for which the encoding has not been
updated, you must supply the <code class="literal">#mysql50#</code>
prefix:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW COLUMNS FROM `a@b`;</code></strong>
ERROR 1146 (42S02): Table 'test.a@b' doesn't exist
mysql> <strong class="userinput"><code>SHOW COLUMNS FROM `#mysql50#a@b`;</code></strong>
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
</pre><p>
To update old names to eliminate the need to use the special
prefix to refer to them, re-encode them with
<a href="programs.html#mysqlcheck" title="4.5.3. mysqlcheck — A Table Maintenance Program"><span><strong class="command">mysqlcheck</strong></span></a>. The following command updates all
names to the new encoding:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysqlcheck --check-upgrade --fix-db-names --fix-table-names --all-databases</code></strong>
</pre><p>
To check only specific databases or tables, omit
<a href="programs.html#option_mysqlcheck_all-databases"><code class="option">--all-databases</code></a> and provide
the appropriate database or table arguments. For information
about <a href="programs.html#mysqlcheck" title="4.5.3. mysqlcheck — A Table Maintenance Program"><span><strong class="command">mysqlcheck</strong></span></a> invocation syntax, see
<a href="programs.html#mysqlcheck" title="4.5.3. mysqlcheck — A Table Maintenance Program">Section 4.5.3, “<span><strong class="command">mysqlcheck</strong></span> — A Table Maintenance Program”</a>.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
The <code class="literal">#mysql50#</code> prefix is intended only to be
used internally by the server. You should not create databases
or tables with names that use this prefix.
</p><p>
Also, <a href="programs.html#mysqlcheck" title="4.5.3. mysqlcheck — A Table Maintenance Program"><span><strong class="command">mysqlcheck</strong></span></a> cannot fix names that
contain literal instances of the <code class="literal">@</code>
character that is used for encoding special characters. If you
have databases or tables that contain this character, use
<a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program"><span><strong class="command">mysqldump</strong></span></a> to dump them before upgrading to
MySQL 5.1.6 or later, and then reload the dump file after
upgrading.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="function-resolution"></a>8.2.4. Function Name Parsing and Resolution</h3></div></div></div><a class="indexterm" name="id4304892"></a><a class="indexterm" name="id4304904"></a><p>
MySQL 5.1 supports built-in (native) functions,
user-defined functions (UDFs), and stored functions. This
section describes how the server recognizes whether the name of
a built-in function is used as a function call or as an
identifier, and how the server determines which function to use
in cases when functions of different types exist with a given
name.
</p><p>
<span class="bold"><strong>Built-In Function Name Parsing</strong></span>
</p><p>
The parser uses default rules for parsing names of built-in
functions. These rules can be changed by enabling the
<a href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a> SQL mode.
</p><p>
When the parser encounters a word that is the name of a built-in
function, it must determine whether the name signifies a
function call or is instead a nonexpression reference to an
identifier such as a table or column name. For example, in the
following statements, the first reference to
<code class="literal">count</code> is a function call, whereas the second
reference is a table name:
</p><pre class="programlisting">SELECT COUNT(*) FROM mytable;
CREATE TABLE count (i INT);
</pre><p>
The parser should recognize the name of a built-in function as
indicating a function call only when parsing what is expected to
be an expression. That is, in nonexpression context, function
names are permitted as identifiers.
</p><p>
However, some built-in functions have special parsing or
implementation considerations, so the parser uses the following
rules by default to distinguish whether their names are being
used as function calls or as identifiers in nonexpression
context:
</p><div class="itemizedlist"><ul type="disc"><li><p>
To use the name as a function call in an expression, there
must be no whitespace between the name and the following
“<span class="quote"><code class="literal">(</code></span>” parenthesis character.
</p></li><li><p>
Conversely, to use the function name as an identifier, it
must not be followed immediately by a parenthesis.
</p></li></ul></div><p>
The requirement that function calls be written with no
whitespace between the name and the parenthesis applies only to
the built-in functions that have special considerations.
<code class="literal">COUNT</code> is one such name. The exact list of
function names for which following whitespace determines their
interpretation are those listed in the
<code class="literal">sql_functions[]</code> array of the
<code class="filename">sql/lex.h</code> source file. Before MySQL 5.1,
these are rather numerous (about 200), so you may find it
easiest to treat the no-whitespace requirement as applying to
all function calls. In MySQL 5.1, parser improvements reduce to
about 30 the number of affected function names.
</p><p>
For functions not listed in the
<code class="literal">sql_functions[]</code>) array, whitespace does not
matter. They are interpreted as function calls only when used in
expression context and may be used freely as identifiers
otherwise. <code class="literal">ASCII</code> is one such name. However,
for these nonaffected function names, interpretation may vary in
expression context:
<code class="literal"><em class="replaceable"><code>func_name</code></em> ()</code> is
interpreted as a built-in function if there is one with the
given name; if not,
<code class="literal"><em class="replaceable"><code>func_name</code></em> ()</code> is
interpreted as a user-defined function or stored function if one
exists with that name.
</p><p>
The <a href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a> SQL mode can
be used to modify how the parser treats function names that are
whitespace-sensitive:
</p><div class="itemizedlist"><ul type="disc"><li><p>
With <a href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a>
disabled, the parser interprets the name as a function call
when there is no whitespace between the name and the
following parenthesis. This occurs even when the function
name is used in nonexpression context:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE count(i INT);</code></strong>
ERROR 1064 (42000): You have an error in your SQL syntax ...
near 'count(i INT)'
</pre><p>
To eliminate the error and cause the name to be treated as
an identifier, either use whitespace following the name or
write it as a quoted identifier (or both):
</p><pre class="programlisting">CREATE TABLE count (i INT);
CREATE TABLE `count`(i INT);
CREATE TABLE `count` (i INT);
</pre></li><li><p>
With <a href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a> enabled,
the parser loosens the requirement that there be no
whitespace between the function name and the following
parenthesis. This provides more flexibility in writing
function calls. For example, either of the following
function calls are legal:
</p><pre class="programlisting">SELECT COUNT(*) FROM mytable;
SELECT COUNT (*) FROM mytable;
</pre><p>
However, enabling
<a href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a> also has the
side effect that the parser treats the affected function
names as reserved words (see
<a href="language-structure.html#reserved-words" title="8.3. Reserved Words">Section 8.3, “Reserved Words”</a>). This means that a space
following the name no longer signifies its use as an
identifier. The name can be used in function calls with or
without following whitespace, but causes a syntax error in
nonexpression context unless it is quoted. For example, with
<a href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a> enabled, both
of the following statements fail with a syntax error because
the parser interprets <code class="literal">count</code> as a reserved
word:
</p><pre class="programlisting">CREATE TABLE count(i INT);
CREATE TABLE count (i INT);
</pre><p>
To use the function name in nonexpression context, write it
as a quoted identifier:
</p><pre class="programlisting">CREATE TABLE `count`(i INT);
CREATE TABLE `count` (i INT);
</pre></li></ul></div><p>
To enable the <a href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a> SQL
mode, use this statement:
</p><pre class="programlisting">SET sql_mode = 'IGNORE_SPACE';
</pre><p>
<a href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a> is also enabled
by certain other composite modes such as
<a href="server-administration.html#sqlmode_ansi"><code class="literal">ANSI</code></a> that include it in their
value:
</p><pre class="programlisting">SET sql_mode = 'ANSI';
</pre><p>
Check <a href="server-administration.html#server-sql-mode" title="5.1.8. Server SQL Modes">Section 5.1.8, “Server SQL Modes”</a>, to see which composite
modes enable <a href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a>.
</p><p>
To minimize the dependency of SQL code on the
<a href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a> setting, use
these guidelines:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Avoid creating UDFs or stored functions that have the same
name as a built-in function.
</p></li><li><p>
Avoid using function names in nonexpression context. For
example, these statements use <code class="literal">count</code> (one
of the affected function names affected by
<a href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a>), so they
fail with or without whitespace following the name if
<a href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a> is enabled:
</p><pre class="programlisting">CREATE TABLE count(i INT);
CREATE TABLE count (i INT);
</pre><p>
If you must use a function name in nonexpression context,
write it as a quoted identifier:
</p><pre class="programlisting">CREATE TABLE `count`(i INT);
CREATE TABLE `count` (i INT);
</pre></li></ul></div><p>
The number of function names affected by
<a href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a> was reduced
significantly in MySQL 5.1.13, from about 200 to about 30. As of
MySQL 5.1.13, only the following functions are still affected by
the <a href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a> setting.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col><col></colgroup><tbody><tr><td><code class="literal">ADDDATE</code></td><td><code class="literal">BIT_AND</code></td><td><code class="literal">BIT_OR</code></td><td><code class="literal">BIT_XOR</code></td></tr><tr><td><code class="literal">CAST</code></td><td><code class="literal">COUNT</code></td><td><code class="literal">CURDATE</code></td><td><code class="literal">CURTIME</code></td></tr><tr><td><code class="literal">DATE_ADD</code></td><td><code class="literal">DATE_SUB</code></td><td><code class="literal">EXTRACT</code></td><td><code class="literal">GROUP_CONCAT</code></td></tr><tr><td><code class="literal">MAX</code></td><td><code class="literal">MID</code></td><td><code class="literal">MIN</code></td><td><code class="literal">NOW</code></td></tr><tr><td><code class="literal">POSITION</code></td><td><code class="literal">SESSION_USER</code></td><td><code class="literal">STD</code></td><td><code class="literal">STDDEV</code></td></tr><tr><td><code class="literal">STDDEV_POP</code></td><td><code class="literal">STDDEV_SAMP</code></td><td><code class="literal">SUBDATE</code></td><td><code class="literal">SUBSTR</code></td></tr><tr><td><code class="literal">SUBSTRING</code></td><td><code class="literal">SUM</code></td><td><code class="literal">SYSDATE</code></td><td><code class="literal">SYSTEM_USER</code></td></tr><tr><td><code class="literal">TRIM</code></td><td><code class="literal">VARIANCE</code></td><td><code class="literal">VAR_POP</code></td><td><code class="literal">VAR_SAMP</code></td></tr></tbody></table></div><p>
For earlier versions of MySQL, check the contents of the
<code class="literal">sql_functions[]</code> array in the
<code class="filename">sql/lex.h</code> source file to see which
functions are affected by
<a href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a>.
</p><p>
<span class="bold"><strong>Incompatibility warning</strong></span>: The
change in MySQL 5.1.13 that reduces the number of function names
affected by <a href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a>
improves the consistency of parser operation. However, it also
introduces the possibility of incompatibility for old SQL code
that relies on the following conditions:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<a href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a> is disabled.
</p></li><li><p>
The presence or absence of whitespace following a function
name is used to distinguish between a built-in function and
stored function that have the same name, such as
<a href="functions.html#function_pi"><code class="literal">PI()</code></a> versus <code class="literal">PI
()</code>.
</p></li></ul></div><p>
For functions that are no longer affected by
<a href="server-administration.html#sqlmode_ignore_space"><code class="literal">IGNORE_SPACE</code></a> as of MySQL
5.1.13, that strategy no longer works. Either of the following
approaches can be used if you have code that is subject to the
preceding incompatibility:
</p><div class="itemizedlist"><ul type="disc"><li><p>
If a stored function has a name that conflicts with a
built-in function, refer to the stored function with a
schema name qualifier, regardless of whether whitespace is
present. For example, write
<code class="literal"><em class="replaceable"><code>schema_name</code></em>.PI()</code>
or <code class="literal"><em class="replaceable"><code>schema_name</code></em>.PI
()</code>.
</p></li><li><p>
Alternatively, rename the stored function to use a
nonconflicting name and change invocations of the function
to use the new name.
</p></li></ul></div><p>
<span class="bold"><strong>Function Name Resolution</strong></span>
</p><p>
The following rules describe how the server resolves references
to function names for function creation and invocation:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Built-in functions and user-defined functions
</p><p>
As of MySQL 5.1.14, an error occurs if you try to create a
UDF with the same name as a built-in function. Before
5.1.14, a UDF can be created with the same name as a
built-in function but the UDF cannot be invoked because the
parser resolves invocations of the function to refer to the
built-in function. For example, if you create a UDF named
<code class="literal">ABS</code>, references to
<a href="functions.html#function_abs"><code class="literal">ABS()</code></a> invoke the built-in
function.
</p></li><li><p>
Built-in functions and stored functions
</p><p>
It is possible to create a stored function with the same
name as a built-in function, but to invoke the stored
function it is necessary to qualify it with a schema name.
For example, if you create a stored function named
<code class="literal">PI</code> in the <code class="literal">test</code> schema,
you invoke it as <code class="literal">test.PI()</code> because the
server resolves <a href="functions.html#function_pi"><code class="literal">PI()</code></a> as a
reference to the built-in function. As of 5.1.14, the server
creates a warning if the stored function name collides with
a built-in function name. The warning can be displayed with
<a href="sql-syntax.html#show-warnings" title="12.5.5.42. SHOW WARNINGS Syntax"><code class="literal">SHOW WARNINGS</code></a>.
</p></li><li><p>
User-defined functions and stored functions
</p><p>
User-defined functions and stored functions share the same
namespace, so you cannot create a UDF and a stored function
with the same name.
</p></li></ul></div><p>
The preceding function name resolution rules have implications
for upgrading to versions of MySQL that implement new built-in
functions:
</p><div class="itemizedlist"><ul type="disc"><li><p>
If you have already created a user-defined function with a
given name and upgrade MySQL to a version that implements a
new built-in function with the same name, the UDF becomes
inaccessible. To correct this, use <a href="sql-syntax.html#drop-function" title="12.1.23. DROP FUNCTION Syntax"><code class="literal">DROP
FUNCTION</code></a> to drop the UDF, and then use
<a href="sql-syntax.html#create-function" title="12.1.12. CREATE FUNCTION Syntax"><code class="literal">CREATE FUNCTION</code></a> to re-create
the UDF with a different nonconflicting name.
</p></li><li><p>
If a new version of MySQL implements a built-in function
with the same name as an existing stored function, you have
two choices: Rename the stored function to use a
nonconflicting name, or change calls to the function so that
they use a schema qualifier (that is, use
<code class="literal"><em class="replaceable"><code>schema_name</code></em>.<em class="replaceable"><code>func_name</code></em>()</code>
syntax).
</p></li></ul></div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="reserved-words"></a>8.3. Reserved Words</h2></div></div></div><a class="indexterm" name="id4305897"></a><a class="indexterm" name="id4305906"></a><p>
Certain words such as <a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a>,
<a href="sql-syntax.html#delete" title="12.2.2. DELETE Syntax"><code class="literal">DELETE</code></a>, or
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BIGINT</code></a> are reserved and require
special treatment for use as identifiers such as table and column
names. This may also be true for the names of built-in functions.
</p><p>
Reserved words are permitted as identifiers if you quote them as
described in <a href="language-structure.html#identifiers" title="8.2. Schema Object Names">Section 8.2, “Schema Object Names”</a>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE interval (begin INT, end INT);</code></strong>
ERROR 1064 (42000): You have an error in your SQL syntax ...
near 'interval (begin INT, end INT)'
mysql> <strong class="userinput"><code>CREATE TABLE `interval` (begin INT, end INT);</code></strong>
Query OK, 0 rows affected (0.01 sec)
</pre><p>
Exception: A word that follows a period in a qualified name must
be an identifier, so it need not be quoted even if it is reserved:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE mydb.interval (begin INT, end INT);</code></strong>
Query OK, 0 rows affected (0.01 sec)
</pre><p>
Names of built-in functions are permitted as identifiers but may
require care to be used as such. For example,
<code class="literal">COUNT</code> is acceptable as a column name. However,
by default, no whitespace is allowed in function invocations
between the function name and the following
“<span class="quote"><code class="literal">(</code></span>” character. This requirement
enables the parser to distinguish whether the name is used in a
function call or in nonfunction context. For further detail on
recognition of function names, see
<a href="language-structure.html#function-resolution" title="8.2.4. Function Name Parsing and Resolution">Section 8.2.4, “Function Name Parsing and Resolution”</a>.
</p><p>
The words in the following table are explicitly reserved in MySQL
5.1. In addition, <code class="literal">_FILENAME</code> is
reserved. At some point, you might upgrade to a higher version, so
it is a good idea to have a look at future reserved words, too.
You can find these in the manuals that cover higher versions of
MySQL. Most of the words in the table are forbidden by standard
SQL as column or table names (for example,
<code class="literal">GROUP</code>). A few are reserved because MySQL needs
them and uses a <span><strong class="command">yacc</strong></span> parser. A reserved word
can be used as an identifier if you quote it.
</p><p>
For a more detailed list of reserved words, including differences
between versions, see
<a href="http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-1.html" target="_top">Reserved Words in MySQL 5.1</a>.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><code class="literal">ACCESSIBLE</code></td><td><code class="literal">ADD</code></td><td><code class="literal">ALL</code></td></tr><tr><td><code class="literal">ALTER</code></td><td><code class="literal">ANALYZE</code></td><td><code class="literal">AND</code></td></tr><tr><td><code class="literal">AS</code></td><td><code class="literal">ASC</code></td><td><code class="literal">ASENSITIVE</code></td></tr><tr><td><code class="literal">BEFORE</code></td><td><code class="literal">BETWEEN</code></td><td><code class="literal">BIGINT</code></td></tr><tr><td><code class="literal">BINARY</code></td><td><code class="literal">BLOB</code></td><td><code class="literal">BOTH</code></td></tr><tr><td><code class="literal">BY</code></td><td><code class="literal">CALL</code></td><td><code class="literal">CASCADE</code></td></tr><tr><td><code class="literal">CASE</code></td><td><code class="literal">CHANGE</code></td><td><code class="literal">CHAR</code></td></tr><tr><td><code class="literal">CHARACTER</code></td><td><code class="literal">CHECK</code></td><td><code class="literal">COLLATE</code></td></tr><tr><td><code class="literal">COLUMN</code></td><td><code class="literal">CONDITION</code></td><td><code class="literal">CONSTRAINT</code></td></tr><tr><td><code class="literal">CONTINUE</code></td><td><code class="literal">CONVERT</code></td><td><code class="literal">CREATE</code></td></tr><tr><td><code class="literal">CROSS</code></td><td><code class="literal">CURRENT_DATE</code></td><td><code class="literal">CURRENT_TIME</code></td></tr><tr><td><code class="literal">CURRENT_TIMESTAMP</code></td><td><code class="literal">CURRENT_USER</code></td><td><code class="literal">CURSOR</code></td></tr><tr><td><code class="literal">DATABASE</code></td><td><code class="literal">DATABASES</code></td><td><code class="literal">DAY_HOUR</code></td></tr><tr><td><code class="literal">DAY_MICROSECOND</code></td><td><code class="literal">DAY_MINUTE</code></td><td><code class="literal">DAY_SECOND</code></td></tr><tr><td><code class="literal">DEC</code></td><td><code class="literal">DECIMAL</code></td><td><code class="literal">DECLARE</code></td></tr><tr><td><code class="literal">DEFAULT</code></td><td><code class="literal">DELAYED</code></td><td><code class="literal">DELETE</code></td></tr><tr><td><code class="literal">DESC</code></td><td><code class="literal">DESCRIBE</code></td><td><code class="literal">DETERMINISTIC</code></td></tr><tr><td><code class="literal">DISTINCT</code></td><td><code class="literal">DISTINCTROW</code></td><td><code class="literal">DIV</code></td></tr><tr><td><code class="literal">DOUBLE</code></td><td><code class="literal">DROP</code></td><td><code class="literal">DUAL</code></td></tr><tr><td><code class="literal">EACH</code></td><td><code class="literal">ELSE</code></td><td><code class="literal">ELSEIF</code></td></tr><tr><td><code class="literal">ENCLOSED</code></td><td><code class="literal">ESCAPED</code></td><td><code class="literal">EXISTS</code></td></tr><tr><td><code class="literal">EXIT</code></td><td><code class="literal">EXPLAIN</code></td><td><code class="literal">FALSE</code></td></tr><tr><td><code class="literal">FETCH</code></td><td><code class="literal">FLOAT</code></td><td><code class="literal">FLOAT4</code></td></tr><tr><td><code class="literal">FLOAT8</code></td><td><code class="literal">FOR</code></td><td><code class="literal">FORCE</code></td></tr><tr><td><code class="literal">FOREIGN</code></td><td><code class="literal">FROM</code></td><td><code class="literal">FULLTEXT</code></td></tr><tr><td><code class="literal">GRANT</code></td><td><code class="literal">GROUP</code></td><td><code class="literal">HAVING</code></td></tr><tr><td><code class="literal">HIGH_PRIORITY</code></td><td><code class="literal">HOUR_MICROSECOND</code></td><td><code class="literal">HOUR_MINUTE</code></td></tr><tr><td><code class="literal">HOUR_SECOND</code></td><td><code class="literal">IF</code></td><td><code class="literal">IGNORE</code></td></tr><tr><td><code class="literal">IN</code></td><td><code class="literal">INDEX</code></td><td><code class="literal">INFILE</code></td></tr><tr><td><code class="literal">INNER</code></td><td><code class="literal">INOUT</code></td><td><code class="literal">INSENSITIVE</code></td></tr><tr><td><code class="literal">INSERT</code></td><td><code class="literal">INT</code></td><td><code class="literal">INT1</code></td></tr><tr><td><code class="literal">INT2</code></td><td><code class="literal">INT3</code></td><td><code class="literal">INT4</code></td></tr><tr><td><code class="literal">INT8</code></td><td><code class="literal">INTEGER</code></td><td><code class="literal">INTERVAL</code></td></tr><tr><td><code class="literal">INTO</code></td><td><code class="literal">IS</code></td><td><code class="literal">ITERATE</code></td></tr><tr><td><code class="literal">JOIN</code></td><td><code class="literal">KEY</code></td><td><code class="literal">KEYS</code></td></tr><tr><td><code class="literal">KILL</code></td><td><code class="literal">LEADING</code></td><td><code class="literal">LEAVE</code></td></tr><tr><td><code class="literal">LEFT</code></td><td><code class="literal">LIKE</code></td><td><code class="literal">LIMIT</code></td></tr><tr><td><code class="literal">LINEAR</code></td><td><code class="literal">LINES</code></td><td><code class="literal">LOAD</code></td></tr><tr><td><code class="literal">LOCALTIME</code></td><td><code class="literal">LOCALTIMESTAMP</code></td><td><code class="literal">LOCK</code></td></tr><tr><td><code class="literal">LONG</code></td><td><code class="literal">LONGBLOB</code></td><td><code class="literal">LONGTEXT</code></td></tr><tr><td><code class="literal">LOOP</code></td><td><code class="literal">LOW_PRIORITY</code></td><td><code class="literal">MASTER_SSL_VERIFY_SERVER_CERT</code></td></tr><tr><td><code class="literal">MATCH</code></td><td><code class="literal">MEDIUMBLOB</code></td><td><code class="literal">MEDIUMINT</code></td></tr><tr><td><code class="literal">MEDIUMTEXT</code></td><td><code class="literal">MIDDLEINT</code></td><td><code class="literal">MINUTE_MICROSECOND</code></td></tr><tr><td><code class="literal">MINUTE_SECOND</code></td><td><code class="literal">MOD</code></td><td><code class="literal">MODIFIES</code></td></tr><tr><td><code class="literal">NATURAL</code></td><td><code class="literal">NOT</code></td><td><code class="literal">NO_WRITE_TO_BINLOG</code></td></tr><tr><td><code class="literal">NULL</code></td><td><code class="literal">NUMERIC</code></td><td><code class="literal">ON</code></td></tr><tr><td><code class="literal">OPTIMIZE</code></td><td><code class="literal">OPTION</code></td><td><code class="literal">OPTIONALLY</code></td></tr><tr><td><code class="literal">OR</code></td><td><code class="literal">ORDER</code></td><td><code class="literal">OUT</code></td></tr><tr><td><code class="literal">OUTER</code></td><td><code class="literal">OUTFILE</code></td><td><code class="literal">PRECISION</code></td></tr><tr><td><code class="literal">PRIMARY</code></td><td><code class="literal">PROCEDURE</code></td><td><code class="literal">PURGE</code></td></tr><tr><td><code class="literal">RANGE</code></td><td><code class="literal">READ</code></td><td><code class="literal">READS</code></td></tr><tr><td><code class="literal">READ_WRITE</code></td><td><code class="literal">REAL</code></td><td><code class="literal">REFERENCES</code></td></tr><tr><td><code class="literal">REGEXP</code></td><td><code class="literal">RELEASE</code></td><td><code class="literal">RENAME</code></td></tr><tr><td><code class="literal">REPEAT</code></td><td><code class="literal">REPLACE</code></td><td><code class="literal">REQUIRE</code></td></tr><tr><td><code class="literal">RESTRICT</code></td><td><code class="literal">RETURN</code></td><td><code class="literal">REVOKE</code></td></tr><tr><td><code class="literal">RIGHT</code></td><td><code class="literal">RLIKE</code></td><td><code class="literal">SCHEMA</code></td></tr><tr><td><code class="literal">SCHEMAS</code></td><td><code class="literal">SECOND_MICROSECOND</code></td><td><code class="literal">SELECT</code></td></tr><tr><td><code class="literal">SENSITIVE</code></td><td><code class="literal">SEPARATOR</code></td><td><code class="literal">SET</code></td></tr><tr><td><code class="literal">SHOW</code></td><td><code class="literal">SMALLINT</code></td><td><code class="literal">SPATIAL</code></td></tr><tr><td><code class="literal">SPECIFIC</code></td><td><code class="literal">SQL</code></td><td><code class="literal">SQLEXCEPTION</code></td></tr><tr><td><code class="literal">SQLSTATE</code></td><td><code class="literal">SQLWARNING</code></td><td><code class="literal">SQL_BIG_RESULT</code></td></tr><tr><td><code class="literal">SQL_CALC_FOUND_ROWS</code></td><td><code class="literal">SQL_SMALL_RESULT</code></td><td><code class="literal">SSL</code></td></tr><tr><td><code class="literal">STARTING</code></td><td><code class="literal">STRAIGHT_JOIN</code></td><td><code class="literal">TABLE</code></td></tr><tr><td><code class="literal">TERMINATED</code></td><td><code class="literal">THEN</code></td><td><code class="literal">TINYBLOB</code></td></tr><tr><td><code class="literal">TINYINT</code></td><td><code class="literal">TINYTEXT</code></td><td><code class="literal">TO</code></td></tr><tr><td><code class="literal">TRAILING</code></td><td><code class="literal">TRIGGER</code></td><td><code class="literal">TRUE</code></td></tr><tr><td><code class="literal">UNDO</code></td><td><code class="literal">UNION</code></td><td><code class="literal">UNIQUE</code></td></tr><tr><td><code class="literal">UNLOCK</code></td><td><code class="literal">UNSIGNED</code></td><td><code class="literal">UPDATE</code></td></tr><tr><td><code class="literal">USAGE</code></td><td><code class="literal">USE</code></td><td><code class="literal">USING</code></td></tr><tr><td><code class="literal">UTC_DATE</code></td><td><code class="literal">UTC_TIME</code></td><td><code class="literal">UTC_TIMESTAMP</code></td></tr><tr><td><code class="literal">VALUES</code></td><td><code class="literal">VARBINARY</code></td><td><code class="literal">VARCHAR</code></td></tr><tr><td><code class="literal">VARCHARACTER</code></td><td><code class="literal">VARYING</code></td><td><code class="literal">WHEN</code></td></tr><tr><td><code class="literal">WHERE</code></td><td><code class="literal">WHILE</code></td><td><code class="literal">WITH</code></td></tr><tr><td><code class="literal">WRITE</code></td><td><code class="literal">XOR</code></td><td><code class="literal">YEAR_MONTH</code></td></tr><tr><td><code class="literal">ZEROFILL</code></td><td class="auto-generated"> </td><td class="auto-generated"> </td></tr></tbody></table></div><p>The following are new reserved words in MySQL 5.1:</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><code class="literal">ACCESSIBLE</code></td><td><code class="literal">LINEAR</code></td><td><code class="literal">MASTER_SSL_VERIFY_SERVER_CERT</code></td></tr><tr><td><code class="literal">RANGE</code></td><td><code class="literal">READ_ONLY</code></td><td><code class="literal">READ_WRITE</code></td></tr></tbody></table></div><p>
MySQL allows some keywords to be used as unquoted identifiers
because many people previously used them. Examples are those in
the following list:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">ACTION</code>
</p></li><li><p>
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BIT</code></a>
</p></li><li><p>
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a>
</p></li><li><p>
<a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a>
</p></li><li><p>
<code class="literal">NO</code>
</p></li><li><p>
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a>
</p></li><li><p>
<a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a>
</p></li><li><p>
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a>
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="user-variables"></a>8.4. User-Defined Variables</h2></div></div></div><a class="indexterm" name="id4307673"></a><a class="indexterm" name="id4307685"></a><a class="indexterm" name="id4307694"></a><a class="indexterm" name="id4307706"></a><a class="indexterm" name="id4307715"></a><a class="indexterm" name="id4307724"></a><p>
You can store a value in a user-defined variable and then refer to
it later. This enables you to pass values from one statement to
another. <span class="emphasis"><em>User-defined variables are
connection-specific</em></span>. That is, a user variable defined
by one client cannot be seen or used by other clients. All
variables for a given client connection are automatically freed
when that client exits.
</p><p>
User variables are written as
<code class="literal">@<em class="replaceable"><code>var_name</code></em></code>, where the
variable name <em class="replaceable"><code>var_name</code></em> may consist of
alphanumeric characters from the current character set,
“<span class="quote"><code class="literal">.</code></span>”,
“<span class="quote"><code class="literal">_</code></span>”, and
“<span class="quote"><code class="literal">$</code></span>”. The default character set is
<code class="literal">latin1</code> (cp1252 West European). This may be
changed with the
<a href="server-administration.html#option_mysqld_character-set-server"><code class="option">--character-set-server</code></a> option to
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a>. See
<a href="internationalization-localization.html#charset-configuration" title="9.2. The Character Set Used for Data and Sorting">Section 9.2, “The Character Set Used for Data and Sorting”</a>. A user variable name can
contain other characters if you quote it as a string or identifier
(for example, <code class="literal">@'my-var'</code>,
<code class="literal">@"my-var"</code>, or <code class="literal">@`my-var`</code>).
</p><p>
User variable names not case sensitive in MySQL 5.0 and up.
</p><p>
One way to set a user-defined variable is by issuing a
<a href="sql-syntax.html#set-option" title="12.5.4. SET Syntax"><code class="literal">SET</code></a>
statement:
</p><pre class="programlisting">SET @<em class="replaceable"><code>var_name</code></em> = <em class="replaceable"><code>expr</code></em> [, @<em class="replaceable"><code>var_name</code></em> = <em class="replaceable"><code>expr</code></em>] ...
</pre><p>
For <a href="sql-syntax.html#set-option" title="12.5.4. SET Syntax"><code class="literal">SET</code></a>,
either <code class="literal">=</code> or <code class="literal">:=</code> can be used
as the assignment operator.
</p><p>
You can also assign a value to a user variable in statements other
than <a href="sql-syntax.html#set-option" title="12.5.4. SET Syntax"><code class="literal">SET</code></a>. In
this case, the assignment operator must be <code class="literal">:=</code>
and not <code class="literal">=</code> because <code class="literal">=</code> is
treated as a comparison operator in
non-<a href="sql-syntax.html#set-option" title="12.5.4. SET Syntax"><code class="literal">SET</code></a>
statements:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @t1=1, @t2=2, @t3:=4;</code></strong>
mysql> <strong class="userinput"><code>SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;</code></strong>
+------+------+------+--------------------+
| @t1 | @t2 | @t3 | @t4 := @t1+@t2+@t3 |
+------+------+------+--------------------+
| 1 | 2 | 4 | 7 |
+------+------+------+--------------------+
</pre><p>
User variables can be assigned a value from a limited set of data
types: integer, decimal, floating-point, binary or nonbinary
string, or <code class="literal">NULL</code> value. Assignment of decimal
and real values does not preserve the precision or scale of the
value. A value of a type other than one of the allowable types is
converted to an allowable type. For example, a value having a
temporal or spatial data type is converted to a binary string.
</p><p>
If a user variable is assigned a nonbinary (character) string
value, it has the same character set and collation as the string.
The coercibility of user variables is implicit. (This is the same
coercibility as for table column values.)
</p><p>
Bit values assigned to user variables are treated as binary
strings. To assign a bit value as a number to a user variable, use
<a href="functions.html#function_cast"><code class="literal">CAST()</code></a> or <code class="literal">+0</code>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @v1 = b'1000001';</code></strong>
mysql> <strong class="userinput"><code>SET @v2 = CAST(b'1000001' AS UNSIGNED), @v3 = b'1000001'+0;</code></strong>
mysql> <strong class="userinput"><code>SELECT @v1, @v2, @v3;</code></strong>
+------+------+------+
| @v1 | @v2 | @v3 |
+------+------+------+
| A | 65 | 65 |
+------+------+------+
</pre><p>
If the value of a user variable is selected in a result set, it is
returned to the client as a string.
</p><p>
User variables may be used in contexts where expressions are
allowed. This does not currently include contexts that explicitly
require a literal value, such as in the <code class="literal">LIMIT</code>
clause of a <a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a> statement, or
the <code class="literal">IGNORE <em class="replaceable"><code>N</code></em> LINES</code>
clause of a <a href="sql-syntax.html#load-data" title="12.2.6. LOAD DATA INFILE
Syntax"><code class="literal">LOAD DATA</code></a> statement.
</p><p>
If you refer to a variable that has not been initialized, it has a
value of <code class="literal">NULL</code> and a type of string.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
In a <a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a> statement, each
select expression is evaluated only when sent to the client.
This means that in a <code class="literal">HAVING</code>, <code class="literal">GROUP
BY</code>, or <code class="literal">ORDER BY</code> clause, you cannot
refer to an expression that involves variables that are set in
the select expression list. For example, the following statement
does <span class="emphasis"><em>not</em></span> work as expected:
</p></div><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT (@aa:=id) AS a, (@aa+3) AS b FROM <em class="replaceable"><code>tbl_name</code></em> HAVING b=5;</code></strong>
</pre><p>
The reference to <code class="literal">b</code> in the
<code class="literal">HAVING</code> clause refers to an alias for an
expression in the select list that uses <code class="literal">@aa</code>.
This does not work as expected: <code class="literal">@aa</code> contains
the value of <code class="literal">id</code> from the previous selected row,
not from the current row.
</p><p>
The order of evaluation for user variables is undefined and may
change based on the elements contained within a given query. In
<code class="literal">SELECT @a, @a:=@a+1, ...</code>, you might think that
MySQL will evaluate <code class="literal">@a</code> first and then do an
assignment second, but changing the query (for example, by adding
a <code class="literal">GROUP BY</code>, <code class="literal">HAVING</code>, or
<code class="literal">ORDER BY</code> clause) may change the order of
evaluation.
</p><p>
The general rule is never to assign a value to a user variable in
one part of a statement <span class="emphasis"><em>and</em></span> use the same
variable in some other part of the same statement. You might get
the results you expect, but this is not guaranteed.
</p><p>
Another issue with setting a variable and using it in the same
statement is that the default result type of a variable is based
on the type of the variable at the start of the statement. The
following example illustrates this:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @a='test';</code></strong>
mysql> <strong class="userinput"><code>SELECT @a,(@a:=20) FROM <em class="replaceable"><code>tbl_name</code></em>;</code></strong>
</pre><p>
For this <a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a> statement, MySQL
reports to the client that column one is a string and converts all
accesses of <code class="literal">@a</code> to strings, even though @a is
set to a number for the second row. After the
<a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a> statement executes,
<code class="literal">@a</code> is regarded as a number for the next
statement.
</p><p>
To avoid problems with this behavior, either do not set and use
the same variable within a single statement, or else set the
variable to <code class="literal">0</code>, <code class="literal">0.0</code>, or
<code class="literal">''</code> to define its type before you use it.
</p><p>
User variables are intended to provide data values. They cannot be
used directly in an SQL statement as an identifier or as part of
an identifier, such as in contexts where a table or database name
is expected, or as a reserved word such as
<a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a>. This is true even if the
variable is quoted, as shown in the following example:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT c1 FROM t;</code></strong>
+----+
| c1 |
+----+
| 0 |
+----+
| 1 |
+----+
2 rows in set (0.00 sec)
mysql> <strong class="userinput"><code>SET @col = "c1";</code></strong>
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>SELECT @col FROM t;</code></strong>
+------+
| @col |
+------+
| c1 |
+------+
1 row in set (0.00 sec)
mysql> <strong class="userinput"><code>SELECT `@col` FROM t;</code></strong>
<span class="errortext">ERROR 1054 (42S22): Unknown column '@col' in 'field list'</span>
mysql> SET @col = "`c1`";
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>SELECT @col FROM t;</code></strong>
+------+
| @col |
+------+
| `c1` |
+------+
1 row in set (0.00 sec)
</pre><p>
An exception to this principle that user variables cannot be used
to provide identifiers is that if you are constructing a string
for use as a prepared statement to be executed later. In this
case, user variables can be used to provide any part of the
statement. The following example illustrates how this can be done:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @c = "c1";</code></strong>
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>SET @s = CONCAT("SELECT ", @c, " FROM t");</code></strong>
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>PREPARE stmt FROM @s;</code></strong>
Query OK, 0 rows affected (0.04 sec)
Statement prepared
mysql> <strong class="userinput"><code>EXECUTE stmt;</code></strong>
+----+
| c1 |
+----+
| 0 |
+----+
| 1 |
+----+
2 rows in set (0.00 sec)
mysql> <strong class="userinput"><code>DEALLOCATE PREPARE stmt;</code></strong>
Query OK, 0 rows affected (0.00 sec)
</pre><p>
See <a href="sql-syntax.html#sql-syntax-prepared-statements" title="12.7. SQL Syntax for Prepared Statements">Section 12.7, “SQL Syntax for Prepared Statements”</a>, for more
information.
</p><p>
A similar technique can be used in application programs to
construct SQL statements using program variables, as shown here
using PHP 5:
</p><pre class="programlisting"><?php
$mysqli = new mysqli("localhost", "user", "pass", "test");
if( mysqli_connect_errno() )
die("Connection failed: %s\n", mysqli_connect_error());
$col = "c1";
$query = "SELECT $col FROM t";
$result = $mysqli->query($query);
while($row = $result->fetch_assoc())
{
echo "<p>" . $row["$col"] . "</p>\n";
}
$result->close();
$mysqli->close();
?>
</pre><p>
Assembling an SQL statement in this fashion is sometimes known as
“<span class="quote">Dynamic SQL</span>”.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="comments"></a>8.5. Comment Syntax</h2></div></div></div><a class="indexterm" name="id4308403"></a><a class="indexterm" name="id4308412"></a><p>
MySQL Server supports three comment styles:
</p><div class="itemizedlist"><ul type="disc"><li><p>
From a “<span class="quote"><code class="literal">#</code></span>” character to the
end of the line.
</p></li><li><p>
From a “<span class="quote"><code class="literal">-- </code></span>” sequence to
the end of the line. In MySQL, the
“<span class="quote"><code class="literal">-- </code></span>” (double-dash)
comment style requires the second dash to be followed by at
least one whitespace or control character (such as a space,
tab, newline, and so on). This syntax differs slightly from
standard SQL comment syntax, as discussed in
<a href="introduction.html#ansi-diff-comments" title="1.7.5.6. '--' as the Start of a Comment">Section 1.7.5.6, “'<code class="literal">--</code>' as the Start of a Comment”</a>.
</p></li><li><p>
From a <code class="literal">/*</code> sequence to the following
<code class="literal">*/</code> sequence, as in the C programming
language. This syntax allows a comment to extend over multiple
lines because the beginning and closing sequences need not be
on the same line.
</p></li></ul></div><p>
The following example demonstrates all three comment styles:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT 1+1; # This comment continues to the end of line</code></strong>
mysql> <strong class="userinput"><code>SELECT 1+1; -- This comment continues to the end of line</code></strong>
mysql> <strong class="userinput"><code>SELECT 1 /* this is an in-line comment */ + 1;</code></strong>
mysql> <strong class="userinput"><code>SELECT 1+</code></strong>
<strong class="userinput"><code>/*</code></strong>
<strong class="userinput"><code>this is a</code></strong>
<strong class="userinput"><code>multiple-line comment</code></strong>
<strong class="userinput"><code>*/</code></strong>
<strong class="userinput"><code>1;</code></strong>
</pre><p>
Nested comments are not supported.
</p><p>
MySQL Server supports some variants of C-style comments. These
enable you to write code that includes MySQL extensions, but is
still portable, by using comments of the following form:
</p><pre class="programlisting">/*! <em class="replaceable"><code>MySQL-specific code</code></em> */
</pre><p>
In this case, MySQL Server parses and executes the code within the
comment as it would any other SQL statement, but other SQL servers
will ignore the extensions. For example, MySQL Server recognizes
the <code class="literal">STRAIGHT_JOIN</code> keyword in the following
statement, but other servers will not:
</p><pre class="programlisting">SELECT /*! STRAIGHT_JOIN */ col1 FROM table1,table2 WHERE ...
</pre><p>
If you add a version number after the
“<span class="quote"><code class="literal">!</code></span>” character, the syntax within
the comment is executed only if the MySQL version is greater than
or equal to the specified version number. The
<code class="literal">TEMPORARY</code> keyword in the following comment is
executed only by servers from MySQL 3.23.02 or higher:
</p><pre class="programlisting">CREATE /*!32302 TEMPORARY */ TABLE t (a INT);
</pre><p>
The comment syntax just described applies to how the
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> server parses SQL statements. The
<a href="programs.html#mysql" title="4.5.1. mysql — The MySQL Command-Line Tool"><span><strong class="command">mysql</strong></span></a> client program also performs some parsing
of statements before sending them to the server. (It does this to
determine statement boundaries within a multiple-statement input
line.)
</p><p>
The use of short-form <a href="programs.html#mysql" title="4.5.1. mysql — The MySQL Command-Line Tool"><span><strong class="command">mysql</strong></span></a> commands such as
<code class="literal">\C</code> within multi-line <code class="literal">/* ...
*/</code> comments is not supported.
</p></div></div><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="optimization.html">Prev</a> </td><td width="20%" align="center"> </td><td width="40%" align="right"> <a accesskey="n" href="internationalization-localization.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 7. Optimization </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> Chapter 9. Internationalization and Localization</td></tr></table></div></body></html>
