<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>Chapter 9. Language Structure</title><meta name="generator" content="DocBook XSL Stylesheets V1.69.1"><link rel="start" href="index.html" title="MySQL 5.0 Reference Manual"><link rel="up" href="index.html" title="MySQL 5.0 Reference Manual"><link rel="prev" href="client-side-scripts.html" title="Chapter 8. Client and Utility Programs"><link rel="next" href="charset.html" title="Chapter 10. Character Set Support"></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 9. Language Structure</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="client-side-scripts.html">Prev</a> </td><th width="60%" align="center"> </th><td width="20%" align="right"> <a accesskey="n" href="charset.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 9. 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">9.1. Literal Values</a></span></dt><dd><dl><dt><span class="section"><a href="language-structure.html#string-syntax">9.1.1. Strings</a></span></dt><dt><span class="section"><a href="language-structure.html#number-syntax">9.1.2. Numbers</a></span></dt><dt><span class="section"><a href="language-structure.html#hexadecimal-values">9.1.3. Hexadecimal Values</a></span></dt><dt><span class="section"><a href="language-structure.html#boolean-values">9.1.4. Boolean Values</a></span></dt><dt><span class="section"><a href="language-structure.html#bit-field-values">9.1.5. Bit-Field Values</a></span></dt><dt><span class="section"><a href="language-structure.html#null-values">9.1.6. <code class="literal">NULL</code> Values</a></span></dt></dl></dd><dt><span class="section"><a href="language-structure.html#legal-names">9.2. Database, Table, Index, Column, and Alias Names</a></span></dt><dd><dl><dt><span class="section"><a href="language-structure.html#identifier-qualifiers">9.2.1. Identifier Qualifiers</a></span></dt><dt><span class="section"><a href="language-structure.html#name-case-sensitivity">9.2.2. Identifier Case Sensitivity</a></span></dt></dl></dd><dt><span class="section"><a href="language-structure.html#variables">9.3. User Variables</a></span></dt><dt><span class="section"><a href="language-structure.html#system-variables">9.4. System Variables</a></span></dt><dd><dl><dt><span class="section"><a href="language-structure.html#structured-system-variables">9.4.1. Structured System Variables</a></span></dt></dl></dd><dt><span class="section"><a href="language-structure.html#comments">9.5. Comment Syntax</a></span></dt><dt><span class="section"><a href="language-structure.html#reserved-words">9.6. Treatment of Reserved Words in MySQL</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 table and column names
</p></li><li><p>
User and system variables
</p></li><li><p>
Comments
</p></li><li><p>
Reserved words
</p></li></ul></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="literals"></a>9.1. Literal Values</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="language-structure.html#string-syntax">9.1.1. Strings</a></span></dt><dt><span class="section"><a href="language-structure.html#number-syntax">9.1.2. Numbers</a></span></dt><dt><span class="section"><a href="language-structure.html#hexadecimal-values">9.1.3. Hexadecimal Values</a></span></dt><dt><span class="section"><a href="language-structure.html#boolean-values">9.1.4. Boolean Values</a></span></dt><dt><span class="section"><a href="language-structure.html#bit-field-values">9.1.5. Bit-Field Values</a></span></dt><dt><span class="section"><a href="language-structure.html#null-values">9.1.6. <code class="literal">NULL</code> Values</a></span></dt></dl></div><a class="indexterm" name="id2898747"></a><a class="indexterm" name="id2898757"></a><a class="indexterm" name="id2898767"></a><a class="indexterm" name="id2898774"></a><a class="indexterm" name="id2898781"></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>9.1.1. Strings</h3></div></div></div><p>
A string is a sequence of characters, surrounded by either
single quote (‘<code class="literal">'</code>’) or double
quote (‘<code class="literal">"</code>’) characters. Examples:
</p><pre class="programlisting">'a string'
"another string"
</pre><p>
If the server SQL mode has <code class="literal">ANSI_QUOTES</code>
enabled, string literals can be quoted only with single quotes.
A string quoted with double quotes is interpreted as an
identifier.
</p><p>
String literals may have an optional character set introducer
and <code class="literal">COLLATE</code> clause:
</p><a class="indexterm" name="id2898853"></a><a class="indexterm" name="id2898864"></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>
For more information about these forms of string syntax, see
<a href="charset.html#charset-literal" title="10.3.7. Character String Literal Character Set and Collation">Section 10.3.7, “Character String Literal Character Set and Collation”</a>.
</p><p>
Within a string, certain sequences have special meaning. Each of
these sequences begins with a backslash
(‘<code class="literal">\</code>’), 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="id2898955"></a>
<a class="indexterm" name="id2898964"></a></td><td>An ASCII 0 (<code class="literal">NUL</code>) character.</td></tr><tr><td><code class="literal">\'</code>
<a class="indexterm" name="id2898987"></a>
<a class="indexterm" name="id2898996"></a></td><td>A single quote (‘<code class="literal">'</code>’) character.</td></tr><tr><td><code class="literal">\"</code>
<a class="indexterm" name="id2899020"></a>
<a class="indexterm" name="id2899030"></a></td><td>A double quote (‘<code class="literal">"</code>’) character.</td></tr><tr><td><code class="literal">\b</code>
<a class="indexterm" name="id2899054"></a>
<a class="indexterm" name="id2899063"></a></td><td>A backspace character.</td></tr><tr><td><code class="literal">\n</code>
<a class="indexterm" name="id2899083"></a>
<a class="indexterm" name="id2899092"></a>
<a class="indexterm" name="id2899101"></a>
<a class="indexterm" name="id2899110"></a></td><td>A newline (linefeed) character.</td></tr><tr><td><code class="literal">\r</code>
<a class="indexterm" name="id2899131"></a>
<a class="indexterm" name="id2899140"></a>
<a class="indexterm" name="id2899149"></a></td><td>A carriage return character.</td></tr><tr><td><code class="literal">\t</code>
<a class="indexterm" name="id2899169"></a>
<a class="indexterm" name="id2899178"></a></td><td>A tab character.</td></tr><tr><td><code class="literal">\Z</code>
<a class="indexterm" name="id2899198"></a>
<a class="indexterm" name="id2899207"></a></td><td>ASCII 26 (Control-Z). This character can be encoded as
‘<code class="literal">\Z</code>’ to allow you to work
around the problem that ASCII 26 stands for END-OF-FILE
on Windows. (ASCII 26 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>.)</td></tr><tr><td><code class="literal">\\</code>
<a class="indexterm" name="id2899247"></a>
<a class="indexterm" name="id2899256"></a></td><td>A backslash (‘<code class="literal">\</code>’) character.</td></tr><tr><td><code class="literal">\%</code>
<a class="indexterm" name="id2899280"></a>
<a class="indexterm" name="id2899289"></a></td><td>A ‘<code class="literal">%</code>’ character. See note following
table.</td></tr><tr><td><code class="literal">\_</code>
<a class="indexterm" name="id2899313"></a>
<a class="indexterm" name="id2899322"></a></td><td>A ‘<code class="literal">_</code>’ character. See note following
table.</td></tr></tbody></table></div><p>
These sequences are case sensitive. For example,
‘<code class="literal">\b</code>’ is interpreted as a
backspace, but ‘<code class="literal">\B</code>’ is
interpreted as ‘<code class="literal">B</code>’.
</p><p>
The ‘<code class="literal">\%</code>’ and
‘<code class="literal">\_</code>’ sequences are used to search
for literal instances of ‘<code class="literal">%</code>’ and
‘<code class="literal">_</code>’ in pattern-matching contexts
where they would otherwise be interpreted as wildcard
characters. See <a href="functions.html#string-comparison-functions" title="12.3.1. String Comparison Functions">Section 12.3.1, “String Comparison Functions”</a>.
Note that if you use ‘<code class="literal">\%</code>’ or
‘<code class="literal">\_</code>’ in other contexts, they
return the strings ‘<code class="literal">\%</code>’ and
‘<code class="literal">\_</code>’ and not
‘<code class="literal">%</code>’ and
‘<code class="literal">_</code>’.
</p><p>
In all other escape sequences, backslash is ignored. That is,
the escaped character is interpreted as if it was not escaped.
</p><a class="indexterm" name="id2899416"></a><p>
There are several ways to include quotes within a string:
</p><div class="itemizedlist"><ul type="disc"><li><p>
A ‘<code class="literal">'</code>’ inside a string quoted
with ‘<code class="literal">'</code>’ may be written as
‘<code class="literal">''</code>’.
</p></li><li><p>
A ‘<code class="literal">"</code>’ inside a string quoted
with ‘<code class="literal">"</code>’ may be written as
‘<code class="literal">""</code>’.
</p></li><li><p>
You can precede the quote character with an escape character
(‘<code class="literal">\</code>’).
</p></li><li><p>
A ‘<code class="literal">'</code>’ inside a string quoted
with ‘<code class="literal">"</code>’ needs no special
treatment and need not be doubled or escaped. In the same
way, ‘<code class="literal">"</code>’ inside a string
quoted with ‘<code class="literal">'</code>’ needs no
special treatment.
</p></li></ul></div><p>
The following <code class="literal">SELECT</code> 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="id2899553"></a><p>
If you want to insert binary data into a string column (such as
a <code class="literal">BLOB</code>), 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 (ASCII 0). Represent this character by
‘<code class="literal">\0</code>’ (a backslash
followed by an ASCII ‘<code class="literal">0</code>’
character).</td></tr><tr><td><code class="literal">\</code></td><td>Backslash (ASCII 92). Represent this character by
‘<code class="literal">\\</code>’.</td></tr><tr><td><code class="literal">'</code></td><td>Single quote (ASCII 39). Represent this character by
‘<code class="literal">\'</code>’.</td></tr><tr><td><code class="literal">"</code></td><td>Double quote (ASCII 34). Represent this character by
‘<code class="literal">\"</code>’.</td></tr></tbody></table></div><a class="indexterm" name="id2899663"></a><a class="indexterm" name="id2899669"></a><a class="indexterm" name="id2899681"></a><a class="indexterm" name="id2899690"></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. For example, in a C program, you can use the
<code class="literal">mysql_real_escape_string()</code> C API function
to escape characters. See
<a href="apis.html#mysql-real-escape-string" title="22.2.3.52. mysql_real_escape_string()">Section 22.2.3.52, “<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="apis.html#perl" title="22.4. MySQL Perl API">Section 22.4, “MySQL Perl API”</a>.
</p></li><li><p>
As an alternative to explicitly escaping special characters,
many MySQL APIs provide a placeholder capability that allows
you to insert special markers into a query string, and then
bind data values to them when you issue the query. 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>9.1.2. Numbers</h3></div></div></div><a class="indexterm" name="id2899765"></a><a class="indexterm" name="id2899771"></a><a class="indexterm" name="id2899782"></a><a class="indexterm" name="id2899788"></a><a class="indexterm" name="id2899795"></a><a class="indexterm" name="id2899802"></a><a class="indexterm" name="id2899811"></a><p>
Integers are represented as a sequence of digits. Floats use
‘<code class="literal">.</code>’ as a decimal separator.
Either type of number may be preceded by
‘<code class="literal">-</code>’ to indicate a negative value.
</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>9.1.3. Hexadecimal Values</h3></div></div></div><a class="indexterm" name="id2899867"></a><p>
MySQL supports hexadecimal values. In numeric contexts, these
act like integers (64-bit precision). In string contexts, these
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 0xa+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
<code class="literal">CAST(... AS UNSIGNED)</code>:
</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">0x</code> syntax is based on ODBC. Hexadecimal
strings are often used by ODBC to supply values for
<code class="literal">BLOB</code> columns. The
<code class="literal">x'hexstring'</code> syntax is based on standard SQL.
</p><p>
You can convert a string or a number to a string in hexadecimal
format with the <code class="literal">HEX()</code> 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>9.1.4. Boolean Values</h3></div></div></div><p>
The constant <code class="literal">TRUE</code> evaluates to
<code class="literal">1</code> and the constant <code class="literal">FALSE</code>
evaluates to <code class="literal">0</code>. 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>9.1.5. Bit-Field Values</h3></div></div></div><p>
Beginning with MySQL 5.0.3, bit-field values can be written
using <code class="literal">b'<em class="replaceable"><code>value</code></em>'</code>
notation. <em class="replaceable"><code>value</code></em> is a binary value
written using zeroes and ones.
</p><p>
Bit-field notation is convenient for specifying values to be
assigned to <code class="literal">BIT</code> 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>
+------+----------+----------+----------+
| b+0 | BIN(b+0) | OCT(b+0) | HEX(b+0) |
+------+----------+----------+----------+
| 255 | 11111111 | 377 | FF |
| 10 | 1010 | 12 | A |
+------+----------+----------+----------+
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="null-values"></a>9.1.6. <code class="literal">NULL</code> Values</h3></div></div></div><a class="indexterm" name="id2900090"></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.
</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. See
<a href="problems.html#problems-with-null" title="A.5.3. Problems with NULL Values">Section A.5.3, “Problems with <code class="literal">NULL</code> Values”</a>.
</p><p>
For text file import or export operations performed with
<code class="literal">LOAD DATA INFILE</code> or <code class="literal">SELECT ... INTO
OUTFILE</code>, <code class="literal">NULL</code> is represented by the
sequence <code class="literal">\N</code>. See <a href="sql-syntax.html#load-data" title="13.2.5. LOAD DATA INFILE Syntax">Section 13.2.5, “<code class="literal">LOAD DATA INFILE</code> Syntax”</a>.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="legal-names"></a>9.2. Database, Table, Index, Column, and Alias Names</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="language-structure.html#identifier-qualifiers">9.2.1. Identifier Qualifiers</a></span></dt><dt><span class="section"><a href="language-structure.html#name-case-sensitivity">9.2.2. Identifier Case Sensitivity</a></span></dt></dl></div><a class="indexterm" name="id2900169"></a><a class="indexterm" name="id2900176"></a><a class="indexterm" name="id2900182"></a><a class="indexterm" name="id2900189"></a><a class="indexterm" name="id2900199"></a><a class="indexterm" name="id2900209"></a><a class="indexterm" name="id2900220"></a><a class="indexterm" name="id2900230"></a><p>
Database, table, index, column, and alias names are identifiers.
This section describes the allowable syntax for identifiers in
MySQL.
</p><p>
The following table describes the maximum length and allowable
characters for each type of identifier.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Identifier</strong></span></td><td><span class="bold"><strong>Maximum Length (bytes)</strong></span></td><td><span class="bold"><strong>Allowed Characters</strong></span></td></tr><tr><td>Database</td><td>64</td><td>Any character that is allowed in a directory name, except
‘<code class="literal">/</code>’,
‘<code class="literal">\</code>’, or
‘<code class="literal">.</code>’</td></tr><tr><td>Table</td><td>64</td><td>Any character that is allowed in a filename, except
‘<code class="literal">/</code>’,
‘<code class="literal">\</code>’, or
‘<code class="literal">.</code>’</td></tr><tr><td>Column</td><td>64</td><td>All characters</td></tr><tr><td>Index</td><td>64</td><td>All characters</td></tr><tr><td>Alias</td><td>255</td><td>All characters</td></tr></tbody></table></div><p>
In addition to the restrictions noted in the table, no identifier
can contain ASCII 0 or a byte with a value of 255. Database,
table, and column names should not end with space characters. The
use of identifier quote characters in identifiers is permitted,
although it is best to avoid doing so if possible.
</p><p>
Identifiers are stored using Unicode (UTF8). This applies to
identifiers in table definitions that 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 string columns in the grant tables (and in any other
tables) in MySQL 5.0 equate to numbers of characters;
this means that (unlike the case for some earlier versions of
MySQL) you can use multi-byte characters without reducing the
number of characters allowed for values stored in these columns.
</p><a class="indexterm" name="id2900421"></a><a class="indexterm" name="id2900434"></a><a class="indexterm" name="id2900443"></a><a class="indexterm" name="id2900452"></a><p>
An identifier may be quoted or unquoted. If an identifier is a
reserved word or contains special characters, you
<span class="emphasis"><em>must</em></span> quote it whenever you refer to it. For a
list of reserved words, see <a href="language-structure.html#reserved-words" title="9.6. Treatment of Reserved Words in MySQL">Section 9.6, “Treatment of Reserved Words in MySQL”</a>.
Special characters are those outside the set of alphanumeric
characters from the current character set,
‘<code class="literal">_</code>’, and
‘<code class="literal">$</code>’.
</p><p>
The identifier quote character is the backtick
(‘<code class="literal">`</code>’):
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM `select` WHERE `select`.id > 100;</code></strong>
</pre><p>
If the server SQL mode includes the <code class="literal">ANSI_QUOTES</code>
mode option, it is also allowable to quote identifiers with 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>
See <a href="database-administration.html#server-sql-mode" title="5.3.2. The Server SQL Mode">Section 5.3.2, “The Server SQL Mode”</a>.
</p><p>
Identifier quote characters can be included within an identifier
<span class="emphasis"><em>if you quote the identifier</em></span>. 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>
It is recommended that you do not use names of the pattern
<code class="literal"><em class="replaceable"><code>X</code></em>e<em class="replaceable"><code>X</code></em></code>,
such as <code class="literal">1e</code> or <code class="literal">2e2</code>, because
an expression like <code class="literal">1e+1</code> is ambiguous. It might
be interpreted as the expression <code class="literal">1e + 1</code> or as
the number <code class="literal">1e+1</code>, depending on context.
</p><p>
Be careful when using <code class="literal">MD5</code> to produce table
names, as it can produce illegal table names such as the ones
listed above.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="identifier-qualifiers"></a>9.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
should be separated by period
(‘<code class="literal">.</code>’) 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 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 query 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, <code class="literal">`my-table`.`my-column`</code> is legal,
whereas <code class="literal">`my-table.my-column`</code> is not.
</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
<code class="literal">SELECT</code> 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.col_name</code> and
<code class="literal">db2.t.col_name</code>.
</p><p>
A word that follows a period in a qualified name must be an
identifier, so it is not necessary to quote it, even if it is a
reserved word.
</p><a class="indexterm" name="id2900836"></a><a class="indexterm" name="id2900842"></a><p>
The syntax <em class="replaceable"><code>.tbl_name</code></em> means the table
<em class="replaceable"><code>tbl_name</code></em> in the current database.
This syntax is accepted for ODBC compatibility because some ODBC
programs prefix table names with a
‘<code class="literal">.</code>’ character.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="name-case-sensitivity"></a>9.2.2. Identifier Case Sensitivity</h3></div></div></div><a class="indexterm" name="id2900885"></a><a class="indexterm" name="id2900895"></a><a class="indexterm" name="id2900905"></a><a class="indexterm" name="id2900916"></a><a class="indexterm" name="id2900926"></a><a class="indexterm" name="id2900936"></a><a class="indexterm" name="id2900947"></a><a class="indexterm" name="id2900957"></a><p>
In MySQL, databases correspond to directories within the data
directory. Each tables within a database corresponds to at least
one file within the database directory (and possibly more,
depending on the storage engine). Consequently, the case
sensitivity of the underlying operating system determines the
case sensitivity of database and table names. This means
database and table names are case sensitive in most varieties of
Unix, and not case sensitive in Windows. One notable exception
is Mac OS X, which is Unix-based but uses a default filesystem
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.8.4. MySQL Extensions to Standard SQL">Section 1.8.4, “MySQL Extensions to Standard SQL”</a>.
</p><p>
<span class="bold"><strong>Note</strong></span>: Although database and
table names are not case sensitive on some platforms, you should
not refer to a given database or table using different cases
within the same query. The following query 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><p>
Column, index, stored routine, and trigger names are not case
sensitive on any platform, nor are column aliases.
</p><p>
By default, table aliases are case sensitive on Unix, but not so
on Windows or Mac OS X. The following query 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 query is permitted on Windows. To avoid being
caught out by such differences, 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 defined by the
<code class="literal">lower_case_table_names</code> system variable, which
you can set when starting <span><strong class="command">mysqld</strong></span>.
<code class="literal">lower_case_table_names</code> can take one of the
following values:
</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 <code class="literal">CREATE TABLE</code> or
<code class="literal">CREATE DATABASE</code> statement. Name
comparisons are case sensitive. This is the default on
Unix systems. Note that if you force this to 0 with
<code class="option">--lower-case-table-names=0</code> on a
case-insensitive filesystem and access
<code class="literal">MyISAM</code> tablenames using different
lettercases, this may lead to index corruption.</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. This value
is the default on Windows and Mac OS X systems.</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 <code class="literal">CREATE TABLE</code> or
<code class="literal">CREATE DATABASE</code> statement, but MySQL
converts them to lowercase on lookup. Name comparisons
are not case sensitive.
<span class="bold"><strong>Note</strong></span>: This works
<span class="emphasis"><em>only</em></span> on filesystems 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>
On both Windows and Mac OS X, the default value of
<code class="literal">lower_case_table_names</code> is 1.
</p><p>
If you are using MySQL on only one platform, you don't normally
have to change the <code class="literal">lower_case_table_names</code>
variable. However, you may encounter difficulties if you want to
transfer tables between platforms that differ in filesystem 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 stemming
from database or table name lettercase, 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 <code class="literal">SHOW TABLES</code> or <code class="literal">SHOW
DATABASES</code>, you don't 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
queries always refer to your database and table names with
the correct lettercase on Windows. If you transfer your
queries 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, you should set
<code class="literal">lower_case_table_names</code> to 1 on all
platforms to force names to be converted to lowercase.
</p></li></ul></div><p>
Note that before setting
<code class="literal">lower_case_table_names</code> to 1 on Unix, you must
first convert your old database and table names to lowercase
before restarting <span><strong class="command">mysqld</strong></span>.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="variables"></a>9.3. User Variables</h2></div></div></div><a class="indexterm" name="id2901356"></a><a class="indexterm" name="id2901367"></a><a class="indexterm" name="id2901373"></a><p>
You can store a value in a user variable and then refer to it
later; this allows you to pass values from one statement to
another. <span class="emphasis"><em>User variables are
connection-specific</em></span>. That is, a 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,
‘<code class="literal">.</code>’,
‘<code class="literal">_</code>’, and
‘<code class="literal">$</code>’. The default character set is
cp1252 (Latin1). This may be changed with the
<code class="option">--default-character-set</code> option to
<span><strong class="command">mysqld</strong></span>. See <a href="database-administration.html#character-sets" title="5.10.1. The Character Set Used for Data and Sorting">Section 5.10.1, “The Character Set Used for Data and Sorting”</a>.
User variable names are not case sensitive.
</p><p>
One way to set a user variable is by issuing a
<code class="literal">SET</code> 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 <code class="literal">SET</code>, either <code class="literal">=</code> or
<code class="literal">:=</code> can be used as the assignment operator. The
<em class="replaceable"><code>expr</code></em> assigned to each variable can
evaluate to an integer, real, string, or <code class="literal">NULL</code>
value.
</p><p>
You can also assign a value to a user variable in statements other
than <code class="literal">SET</code>. 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-<code class="literal">SET</code> statements:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @t1=0, @t2=0, @t3=0;</code></strong>
mysql> <strong class="userinput"><code>SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;</code></strong>
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1 | @t2 | @t3 |
+----------------------+------+------+------+
| 5 | 5 | 1 | 4 |
+----------------------+------+------+------+
</pre><p>
User variables may be used 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
<code class="literal">SELECT</code> statement, or the <code class="literal">IGNORE number
LINES</code> clause of a <code class="literal">LOAD DATA</code>
statement.
</p><p>
If you refer to a variable that has not been initialized, its
value is <code class="literal">NULL</code>.
</p><p>
If a user variable is assigned a string value, it also has the
same character set and collation as the string. The coercibility
of user variables is implicit as of MySQL 5.0.3. (This is the same
coercibility as for table column values.)
</p><p>
<span class="bold"><strong>Note</strong></span>: In a
<code class="literal">SELECT</code> statement, each 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
<code class="literal">SELECT</code> list. For example, the following
statement does <span class="emphasis"><em>not</em></span> work as expected:
</p><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 <code class="literal">SELECT</code> list that uses
<code class="literal">@aa</code>. This does not work as expected:
<code class="literal">@aa</code> does not contain the value of the current
row, but the value of <code class="literal">id</code> from the previous
selected row.
</p><p>
The general rule is to never 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 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 <code class="literal">SELECT</code> 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 <code class="literal">SELECT</code>
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>
An unassigned variable has a value of <code class="literal">NULL</code> with
a type of string.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="system-variables"></a>9.4. System Variables</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="language-structure.html#structured-system-variables">9.4.1. Structured System Variables</a></span></dt></dl></div><a class="indexterm" name="id2901741"></a><a class="indexterm" name="id2901751"></a><a class="indexterm" name="id2901758"></a><p>
MySQL provides access to many system and connection variables.
Many variables can be changed dynamically while the server is
running. This often allows you to modify server operation without
having to stop and restart it.
</p><p>
The <span><strong class="command">mysqld</strong></span> server maintains two kinds of
variables. Global variables affect the overall operation of the
server. Session variables affect its operation for individual
client connections.
</p><p>
When the server starts, it initializes all global variables to
their default values. These defaults may be changed by options
specified in option files or on the command line. After the server
starts, those global variables that are dynamic can be changed by
connecting to the server and issuing a <code class="literal">SET GLOBAL
<em class="replaceable"><code>var_name</code></em></code> statement. To change
a global variable, you must have the <code class="literal">SUPER</code>
privilege.
</p><p>
The server also maintains a set of session variables for each
client that connects. The client's session variables are
initialized at connect time using the current values of the
corresponding global variables. For those session variables that
are dynamic, the client can change them by issuing a <code class="literal">SET
SESSION <em class="replaceable"><code>var_name</code></em></code> statement.
Setting a session variable requires no special privilege, but a
client can change only its own session variables, not those of any
other client.
</p><p>
A change to a global variable is visible to any client that
accesses that global variable. However, it affects the
corresponding session variable that is initialized from the global
variable only for clients that connect after the change. It does
not affect the session variable for any client that is currently
connected (not even that of the client that issues the
<code class="literal">SET GLOBAL</code> statement).
</p><p>
Global or session variables may be set or retrieved using several
syntax forms. The following examples use
<code class="literal">sort_buffer_size</code> as a sample variable name.
</p><p>
To set the value of a <code class="literal">GLOBAL</code> variable, use one
of the following syntaxes:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET GLOBAL sort_buffer_size=<em class="replaceable"><code>value</code></em>;</code></strong>
mysql> <strong class="userinput"><code>SET @@global.sort_buffer_size=<em class="replaceable"><code>value</code></em>;</code></strong>
</pre><p>
To set the value of a <code class="literal">SESSION</code> variable, use one
of the following syntaxes:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET SESSION sort_buffer_size=<em class="replaceable"><code>value</code></em>;</code></strong>
mysql> <strong class="userinput"><code>SET @@session.sort_buffer_size=<em class="replaceable"><code>value</code></em>;</code></strong>
mysql> <strong class="userinput"><code>SET sort_buffer_size=<em class="replaceable"><code>value</code></em>;</code></strong>
</pre><p>
<code class="literal">LOCAL</code> is a synonym for
<code class="literal">SESSION</code>.
</p><p>
If you don't specify <code class="literal">GLOBAL</code>,
<code class="literal">SESSION</code>, or <code class="literal">LOCAL</code> when
setting a variable, <code class="literal">SESSION</code> is the default. See
<a href="sql-syntax.html#set-option" title="13.5.3. SET Syntax">Section 13.5.3, “<code class="literal">SET</code> Syntax”</a>.
</p><p>
To retrieve the value of a <code class="literal">GLOBAL</code> variable, use
one of the following statements:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT @@global.sort_buffer_size;</code></strong>
mysql> <strong class="userinput"><code>SHOW GLOBAL VARIABLES like 'sort_buffer_size';</code></strong>
</pre><p>
To retrieve the value of a <code class="literal">SESSION</code> variable,
use one of the following statements:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT @@sort_buffer_size;</code></strong>
mysql> <strong class="userinput"><code>SELECT @@session.sort_buffer_size;</code></strong>
mysql> <strong class="userinput"><code>SHOW SESSION VARIABLES like 'sort_buffer_size';</code></strong>
</pre><p>
Here, too, <code class="literal">LOCAL</code> is a synonym for
<code class="literal">SESSION</code>.
</p><p>
When you retrieve a variable with <code class="literal">SELECT
@@<em class="replaceable"><code>var_name</code></em></code> (that is, you do
not specify <code class="literal">global.</code>,
<code class="literal">session.</code>, or <code class="literal">local.</code>, MySQL
returns the <code class="literal">SESSION</code> value if it exists and the
<code class="literal">GLOBAL</code> value otherwise.
</p><p>
For <code class="literal">SHOW VARIABLES</code>, if you do not specify
<code class="literal">GLOBAL</code>, <code class="literal">SESSION</code>, or
<code class="literal">LOCAL</code>, MySQL returns the
<code class="literal">SESSION</code> values.
</p><p>
The reason for requiring the <code class="literal">GLOBAL</code> keyword
when setting <code class="literal">GLOBAL</code>-only variables but not when
retrieving them is to prevent problems in the future. If we remove
a <code class="literal">SESSION</code> variable with the same name as a
<code class="literal">GLOBAL</code> variable, a client with the
<code class="literal">SUPER</code> privilege might accidentally change the
<code class="literal">GLOBAL</code> variable rather than just the
<code class="literal">SESSION</code> variable for its own connection. If we
add a <code class="literal">SESSION</code> variable with the same name as a
<code class="literal">GLOBAL</code> variable, a client that intends to
change the <code class="literal">GLOBAL</code> variable might find only its
own <code class="literal">SESSION</code> variable changed.
</p><p>
Further information about system startup options and system
variables can be found in <a href="database-administration.html#server-options" title="5.3.1. mysqld Command-Line Options">Section 5.3.1, “<span><strong class="command">mysqld</strong></span> Command-Line Options”</a> and
<a href="database-administration.html#server-system-variables" title="5.3.3. Server System Variables">Section 5.3.3, “Server System Variables”</a>. A list of the variables
that can be set at runtime is given in
<a href="database-administration.html#dynamic-system-variables" title="5.3.3.1. Dynamic System Variables">Section 5.3.3.1, “Dynamic System Variables”</a>.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="structured-system-variables"></a>9.4.1. Structured System Variables</h3></div></div></div><p>
A structured variable differs from a regular system variable in
two respects:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Its value is a structure with components that specify server
parameters considered to be closely related.
</p></li><li><p>
There might be several instances of a given type of
structured variable. Each one has a different name and
refers to a different resource maintained by the server.
</p></li></ul></div><p>
MySQL 5.0 supports one structured variable type,
which specifies parameters governing the operation of key
caches. A key cache structured variable has these components:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">key_buffer_size</code>
</p></li><li><p>
<code class="literal">key_cache_block_size</code>
</p></li><li><p>
<code class="literal">key_cache_division_limit</code>
</p></li><li><p>
<code class="literal">key_cache_age_threshold</code>
</p></li></ul></div><p>
This section describes the syntax for referring to structured
variables. Key cache variables are used for syntax examples, but
specific details about how key caches operate are found
elsewhere, in <a href="optimization.html#myisam-key-cache" title="7.4.6. The MyISAM Key Cache">Section 7.4.6, “The <code class="literal">MyISAM</code> Key Cache”</a>.
</p><p>
To refer to a component of a structured variable instance, you
can use a compound name in
<code class="literal">instance_name.component_name</code> format.
Examples:
</p><pre class="programlisting">hot_cache.key_buffer_size
hot_cache.key_cache_block_size
cold_cache.key_cache_block_size
</pre><p>
For each structured system variable, an instance with the name
of <code class="literal">default</code> is always predefined. If you refer
to a component of a structured variable without any instance
name, the <code class="literal">default</code> instance is used. Thus,
<code class="literal">default.key_buffer_size</code> and
<code class="literal">key_buffer_size</code> both refer to the same system
variable.
</p><p>
The naming rules for structured variable instances and
components are as follows:
</p><div class="itemizedlist"><ul type="disc"><li><p>
For a given type of structured variable, each instance must
have a name that is unique <span class="emphasis"><em>within</em></span>
variables of that type. However, instance names need not be
unique <span class="emphasis"><em>across</em></span> structured variable
types. For example, each structured variable has an instance
named <code class="literal">default</code>, so
<code class="literal">default</code> is not unique across variable
types.
</p></li><li><p>
The names of the components of each structured variable type
must be unique across all system variable names. If this
were not true (that is, if two different types of structured
variables could share component member names), it would not
be clear which default structured variable to use for
references to member names that are not qualified by an
instance name.
</p></li><li><p>
If a structured variable instance name is not legal as an
unquoted identifier, refer to it as a quoted identifier
using backticks. For example, <code class="literal">hot-cache</code>
is not legal, but <code class="literal">`hot-cache`</code> is.
</p></li><li><p>
<code class="literal">global</code>, <code class="literal">session</code>, and
<code class="literal">local</code> are not legal instance names. This
avoids a conflict with notation such as
<code class="literal">@@global.<em class="replaceable"><code>var_name</code></em></code>
for referring to non-structured system variables.
</p></li></ul></div><p>
Currently, the first two rules have no possibility of being
violated because the only structured variable type is the one
for key caches. These rules assume greater significance if some
other type of structured variable is created in the future.
</p><p>
With one exception, you can refer to structured variable
components using compound names in any context where simple
variable names can occur. For example, you can assign a value to
a structured variable using a command-line option:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysqld --hot_cache.key_buffer_size=64K</code></strong>
</pre><p>
In an option file, use this:
</p><pre class="programlisting">[mysqld]
hot_cache.key_buffer_size=64K
</pre><p>
If you start the server with this option, it creates a key cache
named <code class="literal">hot_cache</code> with a size of 64KB in
addition to the default key cache that has a default size of
8MB.
</p><p>
Suppose that you start the server as follows:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysqld --key_buffer_size=256K \</code></strong>
<strong class="userinput"><code>--extra_cache.key_buffer_size=128K \</code></strong>
<strong class="userinput"><code>--extra_cache.key_cache_block_size=2048</code></strong>
</pre><p>
In this case, the server sets the size of the default key cache
to 256KB. (You could also have written
<code class="option">--default.key_buffer_size=256K</code>.) In addition,
the server creates a second key cache named
<code class="literal">extra_cache</code> that has a size of 128KB, with
the size of block buffers for caching table index blocks set to
2048 bytes.
</p><p>
The following example starts the server with three different key
caches having sizes in a 3:1:1 ratio:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysqld --key_buffer_size=6M \</code></strong>
<strong class="userinput"><code>--hot_cache.key_buffer_size=2M \</code></strong>
<strong class="userinput"><code>--cold_cache.key_buffer_size=2M</code></strong>
</pre><p>
Structured variable values may be set and retrieved at runtime
as well. For example, to set a key cache named
<code class="literal">hot_cache</code> to a size of 10MB, use either of
these statements:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET GLOBAL hot_cache.key_buffer_size = 10*1024*1024;</code></strong>
mysql> <strong class="userinput"><code>SET @@global.hot_cache.key_buffer_size = 10*1024*1024;</code></strong>
</pre><p>
To retrieve the cache size, do this:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT @@global.hot_cache.key_buffer_size;</code></strong>
</pre><p>
However, the following statement does not work. The variable is
not interpreted as a compound name, but as a simple string for a
<code class="literal">LIKE</code> pattern-matching operation:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW GLOBAL VARIABLES LIKE 'hot_cache.key_buffer_size';</code></strong>
</pre><p>
This is the exception to being able to use structured variable
names anywhere a simple variable name may occur.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="comments"></a>9.5. Comment Syntax</h2></div></div></div><a class="indexterm" name="id2902548"></a><a class="indexterm" name="id2902557"></a><p>
The MySQL server supports three comment styles:
</p><div class="itemizedlist"><ul type="disc"><li><p>
From a ‘<code class="literal">#</code>’ character to the end
of the line.
</p></li><li><p>
From a ‘<code class="literal">-- </code>’ sequence to the
end of the line. Note that the ‘<code class="literal">--
</code>’ (double-dash) comment style requires the
second dash to be followed by at least one whitespace
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.8.5.7. '--' as the Start of a Comment">Section 1.8.5.7, “'<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. The closing sequence need not
be on the same line, so this syntax allows a comment to extend
over multiple lines.
</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>
/*
this is a
multiple-line comment
*/
1;
</pre><p>
The comment syntax just described applies to how the
<span><strong class="command">mysqld</strong></span> server parses SQL statements. The
<span><strong class="command">mysql</strong></span> client program also performs some parsing
of statements before sending them to the server. (For example, it
does this to determine statement boundaries within a
multiple-statement input line.)
</p><p>
In MySQL 5.0, the only limitation on the way that
<span><strong class="command">mysql</strong></span> parses <code class="literal">/* ... */</code>
comments is that an exclamation point used with this style of
comment delimiter marks portions of SQL statements for conditional
execution. This applies both when you run <span><strong class="command">mysql</strong></span>
interactively and when you put commands in a file and use
<span><strong class="command">mysql</strong></span> in batch mode to process the file with
<span><strong class="command">mysql <
<em class="replaceable"><code>file_name</code></em></strong></span>. For more
information and examples, see
<a href="introduction.html#extensions-to-ansi" title="1.8.4. MySQL Extensions to Standard SQL">Section 1.8.4, “MySQL Extensions to Standard SQL”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="reserved-words"></a>9.6. Treatment of Reserved Words in MySQL</h2></div></div></div><a class="indexterm" name="id2902725"></a><a class="indexterm" name="id2902732"></a><p>
A common problem stems from trying to use an identifier such as a
table or column name that is the name of a built-in MySQL data
type or function, such as <code class="literal">TIMESTAMP</code> or
<code class="literal">GROUP</code>. You are permitted to do this (for
example, <code class="literal">ABS</code> is acceptable as a column name).
However, by default, no whitespace is allowed in function
invocations between the function name and the following
‘<code class="literal">(</code>’ character. This requirement
allows a function call to be distinguished from a reference to a
column name.
</p><p>
A side effect of this behavior is that omitting a space in some
contexts causes an identifier to be interpreted as a function
name. For example, this statement is legal:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE abs (val INT);</code></strong>
</pre><p>
But omitting the space after <code class="literal">abs</code> causes a
syntax error because the statement then appears to invoke the
<code class="literal">ABS()</code> function:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE abs(val INT);</code></strong>
</pre><p>
If the server SQL mode includes the
<code class="literal">IGNORE_SPACE</code> mode value, the server allows
function invocations to have whitespace between a function name
and the following ‘<code class="literal">(</code>’ character.
This causes function names to be treated as reserved words. As a
result, identifiers that are the same as function names must be
quoted as described in <a href="language-structure.html#legal-names" title="9.2. Database, Table, Index, Column, and Alias Names">Section 9.2, “Database, Table, Index, Column, and Alias Names”</a>. The server
SQL mode is controlled as described in
<a href="database-administration.html#server-sql-mode" title="5.3.2. The Server SQL Mode">Section 5.3.2, “The Server SQL Mode”</a>.
</p><p>
A word that follows a period in a qualified name must be an
identifier, so it is not necessary to quote it, even if it is a
reserved word.
</p><p>
The words in the following table are explicitly reserved in MySQL.
Most of them are forbidden by standard SQL as column and/or table
names (for example, <code class="literal">GROUP</code>). A few are reserved
because MySQL needs them and (currently) uses a
<span><strong class="command">yacc</strong></span> parser. A reserved word can be used as an
identifier if you quote it.
</p><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><tbody><tr><td>ADD</td><td>ALL</td><td>ALTER</td></tr><tr><td>ANALYZE</td><td>AND</td><td>AS</td></tr><tr><td>ASC</td><td>ASENSITIVE</td><td>BEFORE</td></tr><tr><td>BETWEEN</td><td>BIGINT</td><td>BINARY</td></tr><tr><td>BLOB</td><td>BOTH</td><td>BY</td></tr><tr><td>CALL</td><td>CASCADE</td><td>CASE</td></tr><tr><td>CHANGE</td><td>CHAR</td><td>CHARACTER</td></tr><tr><td>CHECK</td><td>COLLATE</td><td>COLUMN</td></tr><tr><td>CONDITION</td><td>CONNECTION</td><td>CONSTRAINT</td></tr><tr><td>CONTINUE</td><td>CONVERT</td><td>CREATE</td></tr><tr><td>CROSS</td><td>CURRENT_DATE</td><td>CURRENT_TIME</td></tr><tr><td>CURRENT_TIMESTAMP</td><td>CURRENT_USER</td><td>CURSOR</td></tr><tr><td>DATABASE</td><td>DATABASES</td><td>DAY_HOUR</td></tr><tr><td>DAY_MICROSECOND</td><td>DAY_MINUTE</td><td>DAY_SECOND</td></tr><tr><td>DEC</td><td>DECIMAL</td><td>DECLARE</td></tr><tr><td>DEFAULT</td><td>DELAYED</td><td>DELETE</td></tr><tr><td>DESC</td><td>DESCRIBE</td><td>DETERMINISTIC</td></tr><tr><td>DISTINCT</td><td>DISTINCTROW</td><td>DIV</td></tr><tr><td>DOUBLE</td><td>DROP</td><td>DUAL</td></tr><tr><td>EACH</td><td>ELSE</td><td>ELSEIF</td></tr><tr><td>ENCLOSED</td><td>ESCAPED</td><td>EXISTS</td></tr><tr><td>EXIT</td><td>EXPLAIN</td><td>FALSE</td></tr><tr><td>FETCH</td><td>FLOAT</td><td>FLOAT4</td></tr><tr><td>FLOAT8</td><td>FOR</td><td>FORCE</td></tr><tr><td>FOREIGN</td><td>FROM</td><td>FULLTEXT</td></tr><tr><td>GOTO</td><td>GRANT</td><td>GROUP</td></tr><tr><td>HAVING</td><td>HIGH_PRIORITY</td><td>HOUR_MICROSECOND</td></tr><tr><td>HOUR_MINUTE</td><td>HOUR_SECOND</td><td>IF</td></tr><tr><td>IGNORE</td><td>IN</td><td>INDEX</td></tr><tr><td>INFILE</td><td>INNER</td><td>INOUT</td></tr><tr><td>INSENSITIVE</td><td>INSERT</td><td>INT</td></tr><tr><td>INT1</td><td>INT2</td><td>INT3</td></tr><tr><td>INT4</td><td>INT8</td><td>INTEGER</td></tr><tr><td>INTERVAL</td><td>INTO</td><td>IS</td></tr><tr><td>ITERATE</td><td>JOIN</td><td>KEY</td></tr><tr><td>KEYS</td><td>KILL</td><td>LABEL</td></tr><tr><td>LEADING</td><td>LEAVE</td><td>LEFT</td></tr><tr><td>LIKE</td><td>LIMIT</td><td>LINES</td></tr><tr><td>LOAD</td><td>LOCALTIME</td><td>LOCALTIMESTAMP</td></tr><tr><td>LOCK</td><td>LONG</td><td>LONGBLOB</td></tr><tr><td>LONGTEXT</td><td>LOOP</td><td>LOW_PRIORITY</td></tr><tr><td>MATCH</td><td>MEDIUMBLOB</td><td>MEDIUMINT</td></tr><tr><td>MEDIUMTEXT</td><td>MIDDLEINT</td><td>MINUTE_MICROSECOND</td></tr><tr><td>MINUTE_SECOND</td><td>MOD</td><td>MODIFIES</td></tr><tr><td>NATURAL</td><td>NOT</td><td>NO_WRITE_TO_BINLOG</td></tr><tr><td>NULL</td><td>NUMERIC</td><td>ON</td></tr><tr><td>OPTIMIZE</td><td>OPTION</td><td>OPTIONALLY</td></tr><tr><td>OR</td><td>ORDER</td><td>OUT</td></tr><tr><td>OUTER</td><td>OUTFILE</td><td>PRECISION</td></tr><tr><td>PRIMARY</td><td>PROCEDURE</td><td>PURGE</td></tr><tr><td>RAID0</td><td>READ</td><td>READS</td></tr><tr><td>REAL</td><td>REFERENCES</td><td>REGEXP</td></tr><tr><td>RELEASE</td><td>RENAME</td><td>REPEAT</td></tr><tr><td>REPLACE</td><td>REQUIRE</td><td>RESTRICT</td></tr><tr><td>RETURN</td><td>REVOKE</td><td>RIGHT</td></tr><tr><td>RLIKE</td><td>SCHEMA</td><td>SCHEMAS</td></tr><tr><td>SECOND_MICROSECOND</td><td>SELECT</td><td>SENSITIVE</td></tr><tr><td>SEPARATOR</td><td>SET</td><td>SHOW</td></tr><tr><td>SMALLINT</td><td>SONAME</td><td>SPATIAL</td></tr><tr><td>SPECIFIC</td><td>SQL</td><td>SQLEXCEPTION</td></tr><tr><td>SQLSTATE</td><td>SQLWARNING</td><td>SQL_BIG_RESULT</td></tr><tr><td>SQL_CALC_FOUND_ROWS</td><td>SQL_SMALL_RESULT</td><td>SSL</td></tr><tr><td>STARTING</td><td>STRAIGHT_JOIN</td><td>TABLE</td></tr><tr><td>TERMINATED</td><td>THEN</td><td>TINYBLOB</td></tr><tr><td>TINYINT</td><td>TINYTEXT</td><td>TO</td></tr><tr><td>TRAILING</td><td>TRIGGER</td><td>TRUE</td></tr><tr><td>UNDO</td><td>UNION</td><td>UNIQUE</td></tr><tr><td>UNLOCK</td><td>UNSIGNED</td><td>UPDATE</td></tr><tr><td>USAGE</td><td>USE</td><td>USING</td></tr><tr><td>UTC_DATE</td><td>UTC_TIME</td><td>UTC_TIMESTAMP</td></tr><tr><td>VALUES</td><td>VARBINARY</td><td>VARCHAR</td></tr><tr><td>VARCHARACTER</td><td>VARYING</td><td>WHEN</td></tr><tr><td>WHERE</td><td>WHILE</td><td>WITH</td></tr><tr><td>WRITE</td><td>X509</td><td>XOR</td></tr><tr><td>YEAR_MONTH</td><td>ZEROFILL</td><td> </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>
<code class="literal">BIT</code>
</p></li><li><p>
<code class="literal">DATE</code>
</p></li><li><p>
<code class="literal">ENUM</code>
</p></li><li><p>
<code class="literal">NO</code>
</p></li><li><p>
<code class="literal">TEXT</code>
</p></li><li><p>
<code class="literal">TIME</code>
</p></li><li><p>
<code class="literal">TIMESTAMP</code>
</p></li></ul></div></div></div><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="client-side-scripts.html">Prev</a> </td><td width="20%" align="center"> </td><td width="40%" align="right"> <a accesskey="n" href="charset.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 8. Client and Utility Programs </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> Chapter 10. Character Set Support</td></tr></table></div></body></html>
distrib
>
Mandriva
>
2008.1
>
x86_64
>
media
>
main-release
>
by-pkgid
>
ebb1914cf182a88528b4547490db1dd8
>
files
>
375
