Author: paul
Date: 2006-01-23 22:26:35 +0100 (Mon, 23 Jan 2006)
New Revision: 1004
Log:
r2447@kite-hub: paul | 2006-01-23 14:18:33 -0600
Move section.
Modified:
trunk/
trunk/refman-4.1/database-administration.xml
trunk/refman-5.0/database-administration.xml
trunk/refman-5.1/database-administration.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6574
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2446
+ b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6574
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2447
Modified: trunk/refman-4.1/database-administration.xml
===================================================================
--- trunk/refman-4.1/database-administration.xml 2006-01-23 21:26:18 UTC (rev 1003)
+++ trunk/refman-4.1/database-administration.xml 2006-01-23 21:26:35 UTC (rev 1004)
@@ -1977,483 +1977,6 @@
</refsection>
- <refsection id="server-sql-mode">
-
- <title>&title-server-sql-mode;</title>
-
- <para>
- The MySQL server can operate in different SQL modes, and (as
- of MySQL 4.1) can apply these modes differentially for
- different clients. This enables each application to tailor the
- server's operating mode to its own requirements.
- </para>
-
- <para>
- Modes define what SQL syntax MySQL should support and what
- kind of data validation checks it should perform. This makes
- it easier to use MySQL in different environments and to use
- MySQL together with other database servers.
- </para>
-
- <para>
- You can set the default SQL mode by starting
- <command>mysqld</command> with the
- <option>--sql-mode="<replaceable>modes</replaceable>"</option>
- option. The value also can be empty
- (<option>--sql-mode=""</option>) if you want to reset it.
- </para>
-
- <para>
- Beginning with MySQL 4.1, you can also change the SQL mode
- after startup time by setting the <literal>sql_mode</literal>
- variable with a <literal>SET [SESSION|GLOBAL]
- sql_mode='<replaceable>modes</replaceable>'</literal>
- statement. Setting the <literal>GLOBAL</literal> variable
- requires the <literal>SUPER</literal> privilege and affects
- the operation of all clients that connect from that time on.
- Setting the <literal>SESSION</literal> variable affects only
- the current client. Any client can change its session
- <literal>sql_mode</literal> value.
- </para>
-
- <para>
- <replaceable>modes</replaceable> is a list of different modes
- separated by comma (‘<literal>,</literal>’)
- characters. You can retrieve the current mode by issuing a
- <literal>SELECT @@sql_mode</literal> statement. The default
- value is empty (no modes set).
- </para>
-
- <indexterm>
- <primary>ANSI SQL mode</primary>
- </indexterm>
-
- <para>
- The most important <literal>sql_mode</literal> value is
- <literal>ANSI</literal>, which changes syntax and behavior to
- be more conformant to standard SQL. This mode is available
- beginning in MySQL 4.1.1
- </para>
-
- <para>
- The following list describes all the supported modes:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <indexterm>
- <primary>ANSI_QUOTES SQL mode</primary>
- </indexterm>
-
- <literal>ANSI_QUOTES</literal>
- </para>
-
- <para>
- Treat ‘<literal>"</literal>’ as an identifier
- quote character (like the
- ‘<literal>`</literal>’ quote character) and
- not as a string quote character. You can still use
- ‘<literal>`</literal>’ to quote identifiers in
- ANSI mode. With <literal>ANSI_QUOTES</literal> enabled,
- you cannot use double quotes to quote a literal string,
- because it is interpreted as an identifier. (New in MySQL
- 4.0.0)
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>IGNORE_SPACE SQL mode</primary>
- </indexterm>
-
- <literal>IGNORE_SPACE</literal>
- </para>
-
- <para>
- Allow spaces between a function name and the
- ‘<literal>(</literal>’ character. This forces
- all function names to be treated as reserved words. As a
- result, if you want to access any database, table, or
- column name that is a reserved word, you must quote it.
- For example, because there is a <literal>USER()</literal>
- function, the name of the <literal>user</literal> table in
- the <literal>mysql</literal> database and the
- <literal>User</literal> column in that table become
- reserved, so you must quote them:
- </para>
-
-<programlisting>
-SELECT "User" FROM mysql."user";
-</programlisting>
-
- <para>
- (New in MySQL 4.0.0)
- </para>
-
- <para>
- See <xref linkend="myisam-start"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_AUTO_VALUE_ON_ZERO SQL mode</primary>
- </indexterm>
-
- <literal>NO_AUTO_VALUE_ON_ZERO</literal>
- </para>
-
- <para>
- <literal>NO_AUTO_VALUE_ON_ZERO</literal> affects handling
- of <literal>AUTO_INCREMENT</literal> columns. Normally,
- you generate the next sequence number for the column by
- inserting either <literal>NULL</literal> or
- <literal>0</literal> into it.
- <literal>NO_AUTO_VALUE_ON_ZERO</literal> suppresses this
- behavior for <literal>0</literal> so that only
- <literal>NULL</literal> generates the next sequence
- number. (New in MySQL 4.1.1)
- </para>
-
- <para>
- This mode can be useful if <literal>0</literal> has been
- stored in a table's <literal>AUTO_INCREMENT</literal>
- column. (Storing <literal>0</literal> is not a recommended
- practice, by the way.) For example, if you dump the table
- with <command>mysqldump</command> and then reload it,
- MySQL normally generates new sequence numbers when it
- encounters the <literal>0</literal> values, resulting in a
- table with contents different from the one that was
- dumped. Enabling <literal>NO_AUTO_VALUE_ON_ZERO</literal>
- before reloading the dump file solves this problem. As of
- MySQL 4.1.1, <command>mysqldump</command> automatically
- includes a statement in the dump output that enables
- <literal>NO_AUTO_VALUE_ON_ZERO</literal>, to avoid this
- problem..
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_DIR_IN_CREATE SQL mode</primary>
- </indexterm>
-
- <literal>NO_DIR_IN_CREATE</literal>
- </para>
-
- <para>
- When creating a table, ignore all <literal>INDEX
- DIRECTORY</literal> and <literal>DATA DIRECTORY</literal>
- directives. This option is useful on slave replication
- servers. (New in MySQL 4.0.15)
- </para>
- </listitem>
-
-<!--
- <listitem>
- <remark role="todo">
- [js] Does not appear to be available in 4.1?
- </remark>
-
- <para>
- <literal>NO_ENGINE_SUBSTITUTION</literal>
- </para>
-
- <para>
- Prevents automatic substitution of storage engine when the
- requested storage engine is disabled or not compiled in.
- </para>
- </listitem>
--->
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_FIELD_OPTIONS SQL mode</primary>
- </indexterm>
-
- <literal>NO_FIELD_OPTIONS</literal>
- </para>
-
- <para>
- do not print MySQL-specific column options in the output
- of <literal>SHOW CREATE TABLE</literal>. This mode is used
- by <command>mysqldump</command> in portability mode. (New
- in MySQL 4.1.1)
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_KEY_OPTIONS SQL mode</primary>
- </indexterm>
-
- <literal>NO_KEY_OPTIONS</literal>
- </para>
-
- <para>
- do not print MySQL-specific index options in the output of
- <literal>SHOW CREATE TABLE</literal>. This mode is used by
- <command>mysqldump</command> in portability mode. (New in
- MySQL 4.1.1)
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_TABLE_OPTIONS SQL mode</primary>
- </indexterm>
-
- <literal>NO_TABLE_OPTIONS</literal>
- </para>
-
- <para>
- do not print MySQL-specific table options (such as
- <literal>ENGINE</literal>) in the output of <literal>SHOW
- CREATE TABLE</literal>. This mode is used by
- <command>mysqldump</command> in portability mode. (New in
- MySQL 4.1.1)
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_UNSIGNED_SUBTRACTION SQL mode</primary>
- </indexterm>
-
- <literal>NO_UNSIGNED_SUBTRACTION</literal>
- </para>
-
- <para>
- In subtraction operations, do not mark the result as
- <literal>UNSIGNED</literal> if one of the operands is
- unsigned. Note that this makes <literal>UNSIGNED
- BIGINT</literal> not 100% usable in all contexts. See
- <xref linkend="cast-functions"/>. (New in MySQL 4.0.2)
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>ONLY_FULL_GROUP_BY SQL mode</primary>
- </indexterm>
-
- <literal>ONLY_FULL_GROUP_BY</literal>
- </para>
-
- <para>
- Do not allow queries that in the <literal>GROUP
- BY</literal> part refer to a not selected column. (New in
- MySQL 4.0.0)
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>PIPES_AS_CONCAT SQL mode</primary>
- </indexterm>
-
- <literal>PIPES_AS_CONCAT</literal>
- </para>
-
- <para>
- Treat <literal>||</literal> as a string concatenation
- operator (same as <literal>CONCAT()</literal>) rather than
- as a synonym for <literal>OR</literal>. (New in MySQL
- 4.0.0)
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>REAL_AS_FLOAT SQL mode</primary>
- </indexterm>
-
- <literal>REAL_AS_FLOAT</literal>
- </para>
-
- <para>
- Treat <literal>REAL</literal> as a synonym for
- <literal>FLOAT</literal> rather than as a synonym for
- <literal>DOUBLE</literal>. (New in MySQL 4.0.0)
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- The following special modes are provided as shorthand for
- combinations of mode values from the preceding list. All are
- available as of MySQL 4.1.1.
- </para>
-
- <para>
- The descriptions include all mode values that are available in
- the most recent version of MySQL. For older versions, a
- combination mode does not include individual mode values that
- are not available except in newer versions.
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <indexterm>
- <primary>ANSI SQL mode</primary>
- </indexterm>
-
- <literal>ANSI</literal>
- </para>
-
- <para>
- Equivalent to <literal>REAL_AS_FLOAT</literal>,
- <literal>PIPES_AS_CONCAT</literal>,
- <literal>ANSI_QUOTES</literal>,
- <literal>IGNORE_SPACE</literal>. Before MySQL 4.1.11,
- <literal>ANSI</literal> also includes
- <literal>ONLY_FULL_GROUP_BY</literal>. See
- <xref linkend="ansi-mode"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>DB2 SQL mode</primary>
- </indexterm>
-
- <literal>DB2</literal>
- </para>
-
- <para>
- Equivalent to <literal>PIPES_AS_CONCAT</literal>,
- <literal>ANSI_QUOTES</literal>,
- <literal>IGNORE_SPACE</literal>,
- <literal>NO_KEY_OPTIONS</literal>,
- <literal>NO_TABLE_OPTIONS</literal>,
- <literal>NO_FIELD_OPTIONS</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>MAXDB SQL mode</primary>
- </indexterm>
-
- <literal>MAXDB</literal>
- </para>
-
- <para>
- Equivalent to <literal>PIPES_AS_CONCAT</literal>,
- <literal>ANSI_QUOTES</literal>,
- <literal>IGNORE_SPACE</literal>,
- <literal>NO_KEY_OPTIONS</literal>,
- <literal>NO_TABLE_OPTIONS</literal>,
- <literal>NO_FIELD_OPTIONS</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>MSSQL SQL mode</primary>
- </indexterm>
-
- <literal>MSSQL</literal>
- </para>
-
- <para>
- Equivalent to <literal>PIPES_AS_CONCAT</literal>,
- <literal>ANSI_QUOTES</literal>,
- <literal>IGNORE_SPACE</literal>,
- <literal>NO_KEY_OPTIONS</literal>,
- <literal>NO_TABLE_OPTIONS</literal>,
- <literal>NO_FIELD_OPTIONS</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>MYSQL323 SQL mode</primary>
- </indexterm>
-
- <literal>MYSQL323</literal>
- </para>
-
- <para>
- Equivalent to <literal>NO_FIELD_OPTIONS</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>MYSQL40 SQL mode</primary>
- </indexterm>
-
- <literal>MYSQL40</literal>
- </para>
-
- <para>
- Equivalent to <literal>NO_FIELD_OPTIONS</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>ORACLE SQL mode</primary>
- </indexterm>
-
- <literal>ORACLE</literal>
- </para>
-
- <para>
- Equivalent to <literal>PIPES_AS_CONCAT</literal>,
- <literal>ANSI_QUOTES</literal>,
- <literal>IGNORE_SPACE</literal>,
- <literal>NO_KEY_OPTIONS</literal>,
- <literal>NO_TABLE_OPTIONS</literal>,
- <literal>NO_FIELD_OPTIONS</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>POSTGRESQL SQL mode</primary>
- </indexterm>
-
- <literal>POSTGRESQL</literal>
- </para>
-
- <para>
- Equivalent to <literal>PIPES_AS_CONCAT</literal>,
- <literal>ANSI_QUOTES</literal>,
- <literal>IGNORE_SPACE</literal>,
- <literal>NO_KEY_OPTIONS</literal>,
- <literal>NO_TABLE_OPTIONS</literal>,
- <literal>NO_FIELD_OPTIONS</literal>.
- </para>
- </listitem>
-
- </itemizedlist>
-
- </refsection>
-
<refsection id="server-system-variables">
<title>&title-server-system-variables;</title>
@@ -7597,6 +7120,483 @@
</refsection>
+ <refsection id="server-sql-mode">
+
+ <title>&title-server-sql-mode;</title>
+
+ <para>
+ The MySQL server can operate in different SQL modes, and (as
+ of MySQL 4.1) can apply these modes differentially for
+ different clients. This enables each application to tailor the
+ server's operating mode to its own requirements.
+ </para>
+
+ <para>
+ Modes define what SQL syntax MySQL should support and what
+ kind of data validation checks it should perform. This makes
+ it easier to use MySQL in different environments and to use
+ MySQL together with other database servers.
+ </para>
+
+ <para>
+ You can set the default SQL mode by starting
+ <command>mysqld</command> with the
+ <option>--sql-mode="<replaceable>modes</replaceable>"</option>
+ option. The value also can be empty
+ (<option>--sql-mode=""</option>) if you want to reset it.
+ </para>
+
+ <para>
+ Beginning with MySQL 4.1, you can also change the SQL mode
+ after startup time by setting the <literal>sql_mode</literal>
+ variable with a <literal>SET [SESSION|GLOBAL]
+ sql_mode='<replaceable>modes</replaceable>'</literal>
+ statement. Setting the <literal>GLOBAL</literal> variable
+ requires the <literal>SUPER</literal> privilege and affects
+ the operation of all clients that connect from that time on.
+ Setting the <literal>SESSION</literal> variable affects only
+ the current client. Any client can change its session
+ <literal>sql_mode</literal> value.
+ </para>
+
+ <para>
+ <replaceable>modes</replaceable> is a list of different modes
+ separated by comma (‘<literal>,</literal>’)
+ characters. You can retrieve the current mode by issuing a
+ <literal>SELECT @@sql_mode</literal> statement. The default
+ value is empty (no modes set).
+ </para>
+
+ <indexterm>
+ <primary>ANSI SQL mode</primary>
+ </indexterm>
+
+ <para>
+ The most important <literal>sql_mode</literal> value is
+ <literal>ANSI</literal>, which changes syntax and behavior to
+ be more conformant to standard SQL. This mode is available
+ beginning in MySQL 4.1.1
+ </para>
+
+ <para>
+ The following list describes all the supported modes:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>ANSI_QUOTES SQL mode</primary>
+ </indexterm>
+
+ <literal>ANSI_QUOTES</literal>
+ </para>
+
+ <para>
+ Treat ‘<literal>"</literal>’ as an identifier
+ quote character (like the
+ ‘<literal>`</literal>’ quote character) and
+ not as a string quote character. You can still use
+ ‘<literal>`</literal>’ to quote identifiers in
+ ANSI mode. With <literal>ANSI_QUOTES</literal> enabled,
+ you cannot use double quotes to quote a literal string,
+ because it is interpreted as an identifier. (New in MySQL
+ 4.0.0)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>IGNORE_SPACE SQL mode</primary>
+ </indexterm>
+
+ <literal>IGNORE_SPACE</literal>
+ </para>
+
+ <para>
+ Allow spaces between a function name and the
+ ‘<literal>(</literal>’ character. This forces
+ all function names to be treated as reserved words. As a
+ result, if you want to access any database, table, or
+ column name that is a reserved word, you must quote it.
+ For example, because there is a <literal>USER()</literal>
+ function, the name of the <literal>user</literal> table in
+ the <literal>mysql</literal> database and the
+ <literal>User</literal> column in that table become
+ reserved, so you must quote them:
+ </para>
+
+<programlisting>
+SELECT "User" FROM mysql."user";
+</programlisting>
+
+ <para>
+ (New in MySQL 4.0.0)
+ </para>
+
+ <para>
+ See <xref linkend="myisam-start"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_AUTO_VALUE_ON_ZERO SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_AUTO_VALUE_ON_ZERO</literal>
+ </para>
+
+ <para>
+ <literal>NO_AUTO_VALUE_ON_ZERO</literal> affects handling
+ of <literal>AUTO_INCREMENT</literal> columns. Normally,
+ you generate the next sequence number for the column by
+ inserting either <literal>NULL</literal> or
+ <literal>0</literal> into it.
+ <literal>NO_AUTO_VALUE_ON_ZERO</literal> suppresses this
+ behavior for <literal>0</literal> so that only
+ <literal>NULL</literal> generates the next sequence
+ number. (New in MySQL 4.1.1)
+ </para>
+
+ <para>
+ This mode can be useful if <literal>0</literal> has been
+ stored in a table's <literal>AUTO_INCREMENT</literal>
+ column. (Storing <literal>0</literal> is not a recommended
+ practice, by the way.) For example, if you dump the table
+ with <command>mysqldump</command> and then reload it,
+ MySQL normally generates new sequence numbers when it
+ encounters the <literal>0</literal> values, resulting in a
+ table with contents different from the one that was
+ dumped. Enabling <literal>NO_AUTO_VALUE_ON_ZERO</literal>
+ before reloading the dump file solves this problem. As of
+ MySQL 4.1.1, <command>mysqldump</command> automatically
+ includes a statement in the dump output that enables
+ <literal>NO_AUTO_VALUE_ON_ZERO</literal>, to avoid this
+ problem..
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_DIR_IN_CREATE SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_DIR_IN_CREATE</literal>
+ </para>
+
+ <para>
+ When creating a table, ignore all <literal>INDEX
+ DIRECTORY</literal> and <literal>DATA DIRECTORY</literal>
+ directives. This option is useful on slave replication
+ servers. (New in MySQL 4.0.15)
+ </para>
+ </listitem>
+
+<!--
+ <listitem>
+ <remark role="todo">
+ [js] Does not appear to be available in 4.1?
+ </remark>
+
+ <para>
+ <literal>NO_ENGINE_SUBSTITUTION</literal>
+ </para>
+
+ <para>
+ Prevents automatic substitution of storage engine when the
+ requested storage engine is disabled or not compiled in.
+ </para>
+ </listitem>
+-->
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_FIELD_OPTIONS SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_FIELD_OPTIONS</literal>
+ </para>
+
+ <para>
+ do not print MySQL-specific column options in the output
+ of <literal>SHOW CREATE TABLE</literal>. This mode is used
+ by <command>mysqldump</command> in portability mode. (New
+ in MySQL 4.1.1)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_KEY_OPTIONS SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_KEY_OPTIONS</literal>
+ </para>
+
+ <para>
+ do not print MySQL-specific index options in the output of
+ <literal>SHOW CREATE TABLE</literal>. This mode is used by
+ <command>mysqldump</command> in portability mode. (New in
+ MySQL 4.1.1)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_TABLE_OPTIONS SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_TABLE_OPTIONS</literal>
+ </para>
+
+ <para>
+ do not print MySQL-specific table options (such as
+ <literal>ENGINE</literal>) in the output of <literal>SHOW
+ CREATE TABLE</literal>. This mode is used by
+ <command>mysqldump</command> in portability mode. (New in
+ MySQL 4.1.1)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_UNSIGNED_SUBTRACTION SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_UNSIGNED_SUBTRACTION</literal>
+ </para>
+
+ <para>
+ In subtraction operations, do not mark the result as
+ <literal>UNSIGNED</literal> if one of the operands is
+ unsigned. Note that this makes <literal>UNSIGNED
+ BIGINT</literal> not 100% usable in all contexts. See
+ <xref linkend="cast-functions"/>. (New in MySQL 4.0.2)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>ONLY_FULL_GROUP_BY SQL mode</primary>
+ </indexterm>
+
+ <literal>ONLY_FULL_GROUP_BY</literal>
+ </para>
+
+ <para>
+ Do not allow queries that in the <literal>GROUP
+ BY</literal> part refer to a not selected column. (New in
+ MySQL 4.0.0)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>PIPES_AS_CONCAT SQL mode</primary>
+ </indexterm>
+
+ <literal>PIPES_AS_CONCAT</literal>
+ </para>
+
+ <para>
+ Treat <literal>||</literal> as a string concatenation
+ operator (same as <literal>CONCAT()</literal>) rather than
+ as a synonym for <literal>OR</literal>. (New in MySQL
+ 4.0.0)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>REAL_AS_FLOAT SQL mode</primary>
+ </indexterm>
+
+ <literal>REAL_AS_FLOAT</literal>
+ </para>
+
+ <para>
+ Treat <literal>REAL</literal> as a synonym for
+ <literal>FLOAT</literal> rather than as a synonym for
+ <literal>DOUBLE</literal>. (New in MySQL 4.0.0)
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ The following special modes are provided as shorthand for
+ combinations of mode values from the preceding list. All are
+ available as of MySQL 4.1.1.
+ </para>
+
+ <para>
+ The descriptions include all mode values that are available in
+ the most recent version of MySQL. For older versions, a
+ combination mode does not include individual mode values that
+ are not available except in newer versions.
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>ANSI SQL mode</primary>
+ </indexterm>
+
+ <literal>ANSI</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>REAL_AS_FLOAT</literal>,
+ <literal>PIPES_AS_CONCAT</literal>,
+ <literal>ANSI_QUOTES</literal>,
+ <literal>IGNORE_SPACE</literal>. Before MySQL 4.1.11,
+ <literal>ANSI</literal> also includes
+ <literal>ONLY_FULL_GROUP_BY</literal>. See
+ <xref linkend="ansi-mode"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>DB2 SQL mode</primary>
+ </indexterm>
+
+ <literal>DB2</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+ <literal>ANSI_QUOTES</literal>,
+ <literal>IGNORE_SPACE</literal>,
+ <literal>NO_KEY_OPTIONS</literal>,
+ <literal>NO_TABLE_OPTIONS</literal>,
+ <literal>NO_FIELD_OPTIONS</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>MAXDB SQL mode</primary>
+ </indexterm>
+
+ <literal>MAXDB</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+ <literal>ANSI_QUOTES</literal>,
+ <literal>IGNORE_SPACE</literal>,
+ <literal>NO_KEY_OPTIONS</literal>,
+ <literal>NO_TABLE_OPTIONS</literal>,
+ <literal>NO_FIELD_OPTIONS</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>MSSQL SQL mode</primary>
+ </indexterm>
+
+ <literal>MSSQL</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+ <literal>ANSI_QUOTES</literal>,
+ <literal>IGNORE_SPACE</literal>,
+ <literal>NO_KEY_OPTIONS</literal>,
+ <literal>NO_TABLE_OPTIONS</literal>,
+ <literal>NO_FIELD_OPTIONS</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>MYSQL323 SQL mode</primary>
+ </indexterm>
+
+ <literal>MYSQL323</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>NO_FIELD_OPTIONS</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>MYSQL40 SQL mode</primary>
+ </indexterm>
+
+ <literal>MYSQL40</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>NO_FIELD_OPTIONS</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>ORACLE SQL mode</primary>
+ </indexterm>
+
+ <literal>ORACLE</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+ <literal>ANSI_QUOTES</literal>,
+ <literal>IGNORE_SPACE</literal>,
+ <literal>NO_KEY_OPTIONS</literal>,
+ <literal>NO_TABLE_OPTIONS</literal>,
+ <literal>NO_FIELD_OPTIONS</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>POSTGRESQL SQL mode</primary>
+ </indexterm>
+
+ <literal>POSTGRESQL</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+ <literal>ANSI_QUOTES</literal>,
+ <literal>IGNORE_SPACE</literal>,
+ <literal>NO_KEY_OPTIONS</literal>,
+ <literal>NO_TABLE_OPTIONS</literal>,
+ <literal>NO_FIELD_OPTIONS</literal>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </refsection>
+
<refsection id="server-shutdown">
<title>&title-server-shutdown;</title>
Modified: trunk/refman-5.0/database-administration.xml
===================================================================
--- trunk/refman-5.0/database-administration.xml 2006-01-23 21:26:18 UTC (rev 1003)
+++ trunk/refman-5.0/database-administration.xml 2006-01-23 21:26:35 UTC (rev 1004)
@@ -1941,832 +1941,6 @@
</refsection>
- <refsection id="server-sql-mode">
-
- <title>&title-server-sql-mode;</title>
-
- <para>
- The MySQL server can operate in different SQL modes, and can
- apply these modes differently for different clients. This
- enables each application to tailor the server's operating mode
- to its own requirements.
- </para>
-
- <para>
- Modes define what SQL syntax MySQL should support and what
- kind of data validation checks it should perform. This makes
- it easier to use MySQL in different environments and to use
- MySQL together with other database servers.
- </para>
-
- <para>
- You can set the default SQL mode by starting
- <command>mysqld</command> with the
- <option>--sql-mode="<replaceable>modes</replaceable>"</option>
- option. The value also can be empty
- (<option>--sql-mode=""</option>) if you want to reset it.
- </para>
-
- <para>
- You can also change the SQL mode after startup time by setting
- the <literal>sql_mode</literal> variable using a <literal>SET
- [SESSION|GLOBAL]
- sql_mode='<replaceable>modes</replaceable>'</literal>
- statement. Setting the <literal>GLOBAL</literal> variable
- requires the <literal>SUPER</literal> privilege and affects
- the operation of all clients that connect from that time on.
- Setting the <literal>SESSION</literal> variable affects only
- the current client. Any client can change its own session
- <literal>sql_mode</literal> value at any time.
- </para>
-
- <para>
- <replaceable>modes</replaceable> is a list of different modes
- separated by comma (‘<literal>,</literal>’)
- characters. You can retrieve the current mode by issuing a
- <literal>SELECT @@sql_mode</literal> statement. The default
- value is empty (no modes set).
- </para>
-
- <para>
- The most important <literal>sql_mode</literal> values are
- probably these:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <indexterm>
- <primary>ANSI SQL mode</primary>
- </indexterm>
-
- <literal>ANSI</literal>
- </para>
-
- <para>
- Change syntax and behavior to be more conformant to
- standard SQL.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>STRICT_TRANS_TABLES SQL mode</primary>
- </indexterm>
-
- <literal>STRICT_TRANS_TABLES</literal>
- </para>
-
- <para>
- If a value could not be inserted as given into a
- transactional table, abort the statement. For a
- non-transactional table, abort the statement if the value
- occurs in a single-row statement or the first row of a
- multiple-row statement. More detail is given later in this
- section. (Implemented in MySQL 5.0.2)
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>TRADITIONAL SQL mode</primary>
- </indexterm>
-
- <literal>TRADITIONAL</literal>
- </para>
-
- <para>
- Make MySQL behave like a <quote>traditional</quote> SQL
- database system. A simple description of this mode is
- <quote>give an error instead of a warning</quote> when
- inserting an incorrect value into a column.
- <emphasis role="bold">Note</emphasis>: The
- <literal>INSERT</literal>/<literal>UPDATE</literal> aborts
- as soon as the error is noticed. This may not be what you
- want if you are using a non-transactional storage engine,
- because data changes made prior to the error are not be
- rolled back, resulting in a <quote>partially done</quote>
- update. (Added in MySQL 5.0.2)
- </para>
- </listitem>
-
- </itemizedlist>
-
- <indexterm>
- <primary>STRICT SQL mode</primary>
- </indexterm>
-
- <para>
- When this manual refers to <quote>strict mode,</quote> it
- means a mode where at least one of
- <literal>STRICT_TRANS_TABLES</literal> or
- <literal>STRICT_ALL_TABLES</literal> is enabled.
- </para>
-
- <para>
- The following list describes all supported modes:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <indexterm>
- <primary>ALLOW_INVALID_DATES SQL mode</primary>
- </indexterm>
-
- <literal>ALLOW_INVALID_DATES</literal>
- </para>
-
- <para>
- Don't do full checking of dates. Check only that the month
- is in the range from 1 to 12 and the day is in the range
- from 1 to 31. This is very convenient for Web applications
- where you obtain year, month, and day in three different
- fields and you want to store exactly what the user
- inserted (without date validation). This mode applies to
- <literal>DATE</literal> and <literal>DATETIME</literal>
- columns. It does not apply <literal>TIMESTAMP</literal>
- columns, which always require a valid date.
- </para>
-
- <para>
- This mode is implemented in MySQL 5.0.2. Before 5.0.2,
- this was the default MySQL date-handling mode. As of
- 5.0.2, the server requires that month and day values be
- legal, and not merely in the range 1 to 12 and 1 to 31,
- respectively. With strict mode disabled, invalid dates
- such as <literal>'2004-04-31'</literal> are converted to
- <literal>'0000-00-00'</literal> and a warning is
- generated. With strict mode enabled, invalid dates
- generate an error. To allow such dates, enable
- <literal>ALLOW_INVALID_DATES</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>ANSI_QUOTES SQL mode</primary>
- </indexterm>
-
- <literal>ANSI_QUOTES</literal>
- </para>
-
- <para>
- Treat ‘<literal>"</literal>’ as an identifier
- quote character (like the
- ‘<literal>`</literal>’ quote character) and
- not as a string quote character. You can still use
- ‘<literal>`</literal>’ to quote identifiers in
- ANSI mode. With <literal>ANSI_QUOTES</literal> enabled,
- you cannot use double quotes to quote a literal string,
- because it is interpreted as an identifier.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>ERROR_FOR_DIVISION_BY_ZERO SQL mode</primary>
- </indexterm>
-
- <literal>ERROR_FOR_DIVISION_BY_ZERO</literal>
- </para>
-
- <para>
- Produce an error in strict mode (otherwise a warning) when
- we encounter a division by zero (or
- <literal>MOD(X,0)</literal>) during an
- <literal>INSERT</literal> or <literal>UPDATE</literal>. If
- this mode is not given, MySQL instead returns
- <literal>NULL</literal> for divisions by zero. If used in
- <literal>INSERT IGNORE</literal> or <literal>UPDATE
- IGNORE</literal>, MySQL generates a warning for divisions
- by zero, but the result of the operation is
- <literal>NULL</literal>. (Implemented in MySQL 5.0.2)
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>HIGH_NOT_PRECEDENCE SQL mode</primary>
- </indexterm>
-
- <literal>HIGH_NOT_PRECEDENCE</literal>
- </para>
-
- <para>
- From MySQL 5.0.2 on, the precedence of the
- <literal>NOT</literal> operator is such that expressions
- such as <literal>NOT a BETWEEN b AND c</literal> are
- parsed as <literal>NOT (a BETWEEN b AND c)</literal>.
- Before MySQL 5.0.2, the expression is parsed as
- <literal>(NOT a) BETWEEN b AND c</literal>. The old
- higher-precedence behavior can be obtained by enabling the
- <literal>HIGH_NOT_PRECEDENCE</literal> SQL mode. (Added in
- MySQL 5.0.2)
- </para>
-
-<programlisting>
-mysql> <userinput>SET sql_mode = '';</userinput>
-mysql> <userinput>SELECT NOT 1 BETWEEN -5 AND 5;</userinput>
- -> 0
-mysql> <userinput>SET sql_mode = 'broken_not';</userinput>
-mysql> <userinput>SELECT NOT 1 BETWEEN -5 AND 5;</userinput>
- -> 1
-</programlisting>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>IGNORE_SPACE SQL mode</primary>
- </indexterm>
-
- <literal>IGNORE_SPACE</literal>
- </para>
-
- <para>
- Allow spaces between a function name and the
- ‘<literal>(</literal>’ character. This forces
- all function names to be treated as reserved words. As a
- result, if you want to access any database, table, or
- column name that is a reserved word, you must quote it.
- For example, because there is a <literal>USER()</literal>
- function, the name of the <literal>user</literal> table in
- the <literal>mysql</literal> database and the
- <literal>User</literal> column in that table become
- reserved, so you must quote them:
- </para>
-
-<programlisting>
-SELECT "User" FROM mysql."user";
-</programlisting>
-
- <para>
- The <literal>IGNORE_SPACE</literal> SQL mode applies to
- built-in functions, not to stored routines. it is always
- allowable to have spaces after a routine name, regardless
- of whether <literal>IGNORE_SPACE</literal> is enabled.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_AUTO_CREATE_USER SQL mode</primary>
- </indexterm>
-
- <literal>NO_AUTO_CREATE_USER</literal>
- </para>
-
- <para>
- Prevent <literal>GRANT</literal> from automatically
- creating new users if it would otherwise do so, unless a
- non-empty password also is specified. (Added in MySQL
- 5.0.2)
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_AUTO_VALUE_ON_ZERO SQL mode</primary>
- </indexterm>
-
- <literal>NO_AUTO_VALUE_ON_ZERO</literal>
- </para>
-
- <para>
- <literal>NO_AUTO_VALUE_ON_ZERO</literal> affects handling
- of <literal>AUTO_INCREMENT</literal> columns. Normally,
- you generate the next sequence number for the column by
- inserting either <literal>NULL</literal> or
- <literal>0</literal> into it.
- <literal>NO_AUTO_VALUE_ON_ZERO</literal> suppresses this
- behavior for <literal>0</literal> so that only
- <literal>NULL</literal> generates the next sequence
- number.
- </para>
-
- <para>
- This mode can be useful if <literal>0</literal> has been
- stored in a table's <literal>AUTO_INCREMENT</literal>
- column. (Storing <literal>0</literal> is not a recommended
- practice, by the way.) For example, if you dump the table
- with <command>mysqldump</command> and then reload it,
- MySQL normally generates new sequence numbers when it
- encounters the <literal>0</literal> values, resulting in a
- table with contents different from the one that was
- dumped. Enabling <literal>NO_AUTO_VALUE_ON_ZERO</literal>
- before reloading the dump file solves this problem.
- <command>mysqldump</command> now automatically includes in
- its output a statement that enables
- <literal>NO_AUTO_VALUE_ON_ZERO</literal>, to avoid this
- problem.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_BACKSLASH_ESCAPES SQL mode</primary>
- </indexterm>
-
- <literal>NO_BACKSLASH_ESCAPES</literal>
- </para>
-
- <para>
- Disable the use of the backslash character
- (‘<literal>\</literal>’) as an escape
- character within strings. With this mode enabled,
- backslash becomes any ordinary character like any other.
- (Implemented in MySQL 5.0.1)
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_DIR_IN_CREATE SQL mode</primary>
- </indexterm>
-
- <literal>NO_DIR_IN_CREATE</literal>
- </para>
-
- <para>
- When creating a table, ignore all <literal>INDEX
- DIRECTORY</literal> and <literal>DATA DIRECTORY</literal>
- directives. This option is useful on slave replication
- servers.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>NO_ENGINE_SUBSTITUTION</literal>
- </para>
-
- <para>
- Prevents automatic substitution of storage engine when the
- requested storage engine is disabled or not compiled in.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_FIELD_OPTIONS SQL mode</primary>
- </indexterm>
-
- <literal>NO_FIELD_OPTIONS</literal>
- </para>
-
- <para>
- Don't print MySQL-specific column options in the output of
- <literal>SHOW CREATE TABLE</literal>. This mode is used by
- <command>mysqldump</command> in portability mode.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_KEY_OPTIONS SQL mode</primary>
- </indexterm>
-
- <literal>NO_KEY_OPTIONS</literal>
- </para>
-
- <para>
- Don't print MySQL-specific index options in the output of
- <literal>SHOW CREATE TABLE</literal>. This mode is used by
- <command>mysqldump</command> in portability mode.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_TABLE_OPTIONS SQL mode</primary>
- </indexterm>
-
- <literal>NO_TABLE_OPTIONS</literal>
- </para>
-
- <para>
- Don't print MySQL-specific table options (such as
- <literal>ENGINE</literal>) in the output of <literal>SHOW
- CREATE TABLE</literal>. This mode is used by
- <command>mysqldump</command> in portability mode.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_UNSIGNED_SUBTRACTION SQL mode</primary>
- </indexterm>
-
- <literal>NO_UNSIGNED_SUBTRACTION</literal>
- </para>
-
- <para>
- In subtraction operations, don't mark the result as
- <literal>UNSIGNED</literal> if one of the operands is
- unsigned. Note that this makes <literal>UNSIGNED
- BIGINT</literal> not 100% usable in all contexts. See
- <xref linkend="cast-functions"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_ZERO_DATE SQL mode</primary>
- </indexterm>
-
- <literal>NO_ZERO_DATE</literal>
- </para>
-
- <para>
- In strict mode, don't allow
- <literal>'0000-00-00'</literal> as a valid date. You can
- still insert zero dates with the <literal>IGNORE</literal>
- option. When not in strict mode, the date is accepted but
- a warning is generated. (Added in MySQL 5.0.2)
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_ZERO_IN_DATE SQL mode</primary>
- </indexterm>
-
- <literal>NO_ZERO_IN_DATE</literal>
- </para>
-
- <para>
- In strict mode, don't accept dates where the month or day
- part is 0. If used with the <literal>IGNORE</literal>
- option, we insert a <literal>'0000-00-00'</literal> date
- for any such date. When not in strict mode, the date is
- accepted but a warning is generated. (Added in MySQL
- 5.0.2)
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>ONLY_FULL_GROUP_BY SQL mode</primary>
- </indexterm>
-
- <literal>ONLY_FULL_GROUP_BY</literal>
- </para>
-
- <para>
- Do not allow queries that in the <literal>GROUP
- BY</literal> part refer to a column that is not selected.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>PIPES_AS_CONCAT SQL mode</primary>
- </indexterm>
-
- <literal>PIPES_AS_CONCAT</literal>
- </para>
-
- <para>
- Treat <literal>||</literal> as a string concatenation
- operator (same as <literal>CONCAT()</literal>) rather than
- as a synonym for <literal>OR</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>REAL_AS_FLOAT SQL mode</primary>
- </indexterm>
-
- <literal>REAL_AS_FLOAT</literal>
- </para>
-
- <para>
- Treat <literal>REAL</literal> as a synonym for
- <literal>FLOAT</literal> rather than as a synonym for
- <literal>DOUBLE</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>STRICT_ALL_TABLES SQL mode</primary>
- </indexterm>
-
- <literal>STRICT_ALL_TABLES</literal>
- </para>
-
- <para>
- Enable strict mode for all storage engines. Invalid data
- values are rejected. Additional detail follows. (Added in
- MySQL 5.0.2)
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>STRICT_TRANS_TABLES SQL mode</primary>
- </indexterm>
-
- <literal>STRICT_TRANS_TABLES</literal>
- </para>
-
- <para>
- Enable strict mode for transactional storage engines, and
- when possible for non-transactional storage engines.
- Additional details follow. (Implemented in MySQL 5.0.2)
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- Strict mode controls how MySQL handles input values that are
- invalid or missing. A value can be invalid for several
- reasons. For example, it might have the wrong data type for
- the column, or it might be out of range. A value is missing
- when a new row to be inserted does not contain a value for a
- column that has no explicit <literal>DEFAULT</literal> clause
- in its definition.
- </para>
-
- <para>
- For transactional tables, an error occurs for invalid or
- missing values in a statement when either of the
- <literal>STRICT_ALL_TABLES</literal> or
- <literal>STRICT_TRANS_TABLES</literal> modes are enabled. The
- statement is aborted and rolled back.
- </para>
-
- <para>
- For non-transactional tables, the behavior is the same for
- either mode, if the bad value occurs in the first row to be
- inserted or updated. The statement is aborted and the table
- remains unchanged. If the statement inserts or modifies
- multiple rows and the bad value occurs in the second or later
- row, the result depends on which strict option is enabled:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- For <literal>STRICT_ALL_TABLES</literal>, MySQL returns an
- error and ignores the rest of the rows. However, in this
- case, the earlier rows still have been inserted or
- updated. This means that you might get a partial update,
- which might not be what you want. To avoid this, it's best
- to use single-row statements because these can be aborted
- without changing the table.
- </para>
- </listitem>
-
- <listitem>
- <para>
- For <literal>STRICT_TRANS_TABLES</literal>, MySQL converts
- an invalid value to the closest valid value for the column
- and insert the adjusted value. If a value is missing,
- MySQL inserts the implicit default value for the column
- data type. In either case, MySQL generates a warning
- rather than an error and continues processing the
- statement. Implicit defaults are described in
- <xref linkend="create-table"/>.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- Strict mode disallows invalid date values such as
- <literal>'2004-04-31'</literal>. It does not disallow dates
- with zero parts such as <literal>2004-04-00'</literal> or
- <quote>zero</quote> dates. To disallow these as well, enable
- the <literal>NO_ZERO_IN_DATE</literal> and
- <literal>NO_ZERO_DATE</literal> SQL modes in addition to
- strict mode.
- </para>
-
- <para>
- If you are not using strict mode (that is, neither
- <literal>STRICT_TRANS_TABLES</literal> nor
- <literal>STRICT_ALL_TABLES</literal> is enabled), MySQL
- inserts adjusted values for invalid or missing values and
- produces warnings. In strict mode, you can produce this
- behavior by using <literal>INSERT IGNORE</literal> or
- <literal>UPDATE IGNORE</literal>. See
- <xref linkend="show-warnings"/>.
- </para>
-
- <para>
- The following special modes are provided as shorthand for
- combinations of mode values from the preceding list. All are
- available in MySQL ¤t-series; beginning with version
- 5.0.0, except for <literal>TRADITIONAL</literal>, which was
- implemented in MySQL 5.0.2.
- </para>
-
- <para>
- The descriptions include all mode values that are available in
- the most recent version of MySQL. For older versions, a
- combination mode does not include individual mode values that
- are not available except in newer versions.
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <indexterm>
- <primary>ANSI SQL mode</primary>
- </indexterm>
-
- <literal>ANSI</literal>
- </para>
-
- <para>
- Equivalent to <literal>REAL_AS_FLOAT</literal>,
- <literal>PIPES_AS_CONCAT</literal>,
- <literal>ANSI_QUOTES</literal>,
- <literal>IGNORE_SPACE</literal>. Before MySQL 5.0.3,
- <literal>ANSI</literal> also includes
- <literal>ONLY_FULL_GROUP_BY</literal>. See
- <xref linkend="ansi-mode"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>DB2 SQL mode</primary>
- </indexterm>
-
- <literal>DB2</literal>
- </para>
-
- <para>
- Equivalent to <literal>PIPES_AS_CONCAT</literal>,
- <literal>ANSI_QUOTES</literal>,
- <literal>IGNORE_SPACE</literal>,
- <literal>NO_KEY_OPTIONS</literal>,
- <literal>NO_TABLE_OPTIONS</literal>,
- <literal>NO_FIELD_OPTIONS</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>MAXDB SQL mode</primary>
- </indexterm>
-
- <literal>MAXDB</literal>
- </para>
-
- <para>
- Equivalent to <literal>PIPES_AS_CONCAT</literal>,
- <literal>ANSI_QUOTES</literal>,
- <literal>IGNORE_SPACE</literal>,
- <literal>NO_KEY_OPTIONS</literal>,
- <literal>NO_TABLE_OPTIONS</literal>,
- <literal>NO_FIELD_OPTIONS</literal>,
- <literal>NO_AUTO_CREATE_USER</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>MSSQL SQL mode</primary>
- </indexterm>
-
- <literal>MSSQL</literal>
- </para>
-
- <para>
- Equivalent to <literal>PIPES_AS_CONCAT</literal>,
- <literal>ANSI_QUOTES</literal>,
- <literal>IGNORE_SPACE</literal>,
- <literal>NO_KEY_OPTIONS</literal>,
- <literal>NO_TABLE_OPTIONS</literal>,
- <literal>NO_FIELD_OPTIONS</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>MYSQL323 SQL mode</primary>
- </indexterm>
-
- <literal>MYSQL323</literal>
- </para>
-
- <para>
- Equivalent to <literal>NO_FIELD_OPTIONS</literal>,
- <literal>HIGH_NOT_PRECEDENCE</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>MYSQL40 SQL mode</primary>
- </indexterm>
-
- <literal>MYSQL40</literal>
- </para>
-
- <para>
- Equivalent to <literal>NO_FIELD_OPTIONS</literal>,
- <literal>HIGH_NOT_PRECEDENCE</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>ORACLE SQL mode</primary>
- </indexterm>
-
- <literal>ORACLE</literal>
- </para>
-
- <para>
- Equivalent to <literal>PIPES_AS_CONCAT</literal>,
- <literal>ANSI_QUOTES</literal>,
- <literal>IGNORE_SPACE</literal>,
- <literal>NO_KEY_OPTIONS</literal>,
- <literal>NO_TABLE_OPTIONS</literal>,
- <literal>NO_FIELD_OPTIONS</literal>,
- <literal>NO_AUTO_CREATE_USER</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>POSTGRESQL SQL mode</primary>
- </indexterm>
-
- <literal>POSTGRESQL</literal>
- </para>
-
- <para>
- Equivalent to <literal>PIPES_AS_CONCAT</literal>,
- <literal>ANSI_QUOTES</literal>,
- <literal>IGNORE_SPACE</literal>,
- <literal>NO_KEY_OPTIONS</literal>,
- <literal>NO_TABLE_OPTIONS</literal>,
- <literal>NO_FIELD_OPTIONS</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>TRADITIONAL SQL mode</primary>
- </indexterm>
-
- <literal>TRADITIONAL</literal>
- </para>
-
- <para>
- Equivalent to <literal>STRICT_TRANS_TABLES</literal>,
- <literal>STRICT_ALL_TABLES</literal>,
- <literal>NO_ZERO_IN_DATE</literal>,
- <literal>NO_ZERO_DATE</literal>,
- <literal>ERROR_FOR_DIVISION_BY_ZERO</literal>,
- <literal>NO_AUTO_CREATE_USER</literal>.
- </para>
- </listitem>
-
- </itemizedlist>
-
- </refsection>
-
<refsection id="server-system-variables">
<title>&title-server-system-variables;</title>
@@ -8809,6 +7983,832 @@
</refsection>
+ <refsection id="server-sql-mode">
+
+ <title>&title-server-sql-mode;</title>
+
+ <para>
+ The MySQL server can operate in different SQL modes, and can
+ apply these modes differently for different clients. This
+ enables each application to tailor the server's operating mode
+ to its own requirements.
+ </para>
+
+ <para>
+ Modes define what SQL syntax MySQL should support and what
+ kind of data validation checks it should perform. This makes
+ it easier to use MySQL in different environments and to use
+ MySQL together with other database servers.
+ </para>
+
+ <para>
+ You can set the default SQL mode by starting
+ <command>mysqld</command> with the
+ <option>--sql-mode="<replaceable>modes</replaceable>"</option>
+ option. The value also can be empty
+ (<option>--sql-mode=""</option>) if you want to reset it.
+ </para>
+
+ <para>
+ You can also change the SQL mode after startup time by setting
+ the <literal>sql_mode</literal> variable using a <literal>SET
+ [SESSION|GLOBAL]
+ sql_mode='<replaceable>modes</replaceable>'</literal>
+ statement. Setting the <literal>GLOBAL</literal> variable
+ requires the <literal>SUPER</literal> privilege and affects
+ the operation of all clients that connect from that time on.
+ Setting the <literal>SESSION</literal> variable affects only
+ the current client. Any client can change its own session
+ <literal>sql_mode</literal> value at any time.
+ </para>
+
+ <para>
+ <replaceable>modes</replaceable> is a list of different modes
+ separated by comma (‘<literal>,</literal>’)
+ characters. You can retrieve the current mode by issuing a
+ <literal>SELECT @@sql_mode</literal> statement. The default
+ value is empty (no modes set).
+ </para>
+
+ <para>
+ The most important <literal>sql_mode</literal> values are
+ probably these:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>ANSI SQL mode</primary>
+ </indexterm>
+
+ <literal>ANSI</literal>
+ </para>
+
+ <para>
+ Change syntax and behavior to be more conformant to
+ standard SQL.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>STRICT_TRANS_TABLES SQL mode</primary>
+ </indexterm>
+
+ <literal>STRICT_TRANS_TABLES</literal>
+ </para>
+
+ <para>
+ If a value could not be inserted as given into a
+ transactional table, abort the statement. For a
+ non-transactional table, abort the statement if the value
+ occurs in a single-row statement or the first row of a
+ multiple-row statement. More detail is given later in this
+ section. (Implemented in MySQL 5.0.2)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>TRADITIONAL SQL mode</primary>
+ </indexterm>
+
+ <literal>TRADITIONAL</literal>
+ </para>
+
+ <para>
+ Make MySQL behave like a <quote>traditional</quote> SQL
+ database system. A simple description of this mode is
+ <quote>give an error instead of a warning</quote> when
+ inserting an incorrect value into a column.
+ <emphasis role="bold">Note</emphasis>: The
+ <literal>INSERT</literal>/<literal>UPDATE</literal> aborts
+ as soon as the error is noticed. This may not be what you
+ want if you are using a non-transactional storage engine,
+ because data changes made prior to the error are not be
+ rolled back, resulting in a <quote>partially done</quote>
+ update. (Added in MySQL 5.0.2)
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <indexterm>
+ <primary>STRICT SQL mode</primary>
+ </indexterm>
+
+ <para>
+ When this manual refers to <quote>strict mode,</quote> it
+ means a mode where at least one of
+ <literal>STRICT_TRANS_TABLES</literal> or
+ <literal>STRICT_ALL_TABLES</literal> is enabled.
+ </para>
+
+ <para>
+ The following list describes all supported modes:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>ALLOW_INVALID_DATES SQL mode</primary>
+ </indexterm>
+
+ <literal>ALLOW_INVALID_DATES</literal>
+ </para>
+
+ <para>
+ Don't do full checking of dates. Check only that the month
+ is in the range from 1 to 12 and the day is in the range
+ from 1 to 31. This is very convenient for Web applications
+ where you obtain year, month, and day in three different
+ fields and you want to store exactly what the user
+ inserted (without date validation). This mode applies to
+ <literal>DATE</literal> and <literal>DATETIME</literal>
+ columns. It does not apply <literal>TIMESTAMP</literal>
+ columns, which always require a valid date.
+ </para>
+
+ <para>
+ This mode is implemented in MySQL 5.0.2. Before 5.0.2,
+ this was the default MySQL date-handling mode. As of
+ 5.0.2, the server requires that month and day values be
+ legal, and not merely in the range 1 to 12 and 1 to 31,
+ respectively. With strict mode disabled, invalid dates
+ such as <literal>'2004-04-31'</literal> are converted to
+ <literal>'0000-00-00'</literal> and a warning is
+ generated. With strict mode enabled, invalid dates
+ generate an error. To allow such dates, enable
+ <literal>ALLOW_INVALID_DATES</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>ANSI_QUOTES SQL mode</primary>
+ </indexterm>
+
+ <literal>ANSI_QUOTES</literal>
+ </para>
+
+ <para>
+ Treat ‘<literal>"</literal>’ as an identifier
+ quote character (like the
+ ‘<literal>`</literal>’ quote character) and
+ not as a string quote character. You can still use
+ ‘<literal>`</literal>’ to quote identifiers in
+ ANSI mode. With <literal>ANSI_QUOTES</literal> enabled,
+ you cannot use double quotes to quote a literal string,
+ because it is interpreted as an identifier.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>ERROR_FOR_DIVISION_BY_ZERO SQL mode</primary>
+ </indexterm>
+
+ <literal>ERROR_FOR_DIVISION_BY_ZERO</literal>
+ </para>
+
+ <para>
+ Produce an error in strict mode (otherwise a warning) when
+ we encounter a division by zero (or
+ <literal>MOD(X,0)</literal>) during an
+ <literal>INSERT</literal> or <literal>UPDATE</literal>. If
+ this mode is not given, MySQL instead returns
+ <literal>NULL</literal> for divisions by zero. If used in
+ <literal>INSERT IGNORE</literal> or <literal>UPDATE
+ IGNORE</literal>, MySQL generates a warning for divisions
+ by zero, but the result of the operation is
+ <literal>NULL</literal>. (Implemented in MySQL 5.0.2)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>HIGH_NOT_PRECEDENCE SQL mode</primary>
+ </indexterm>
+
+ <literal>HIGH_NOT_PRECEDENCE</literal>
+ </para>
+
+ <para>
+ From MySQL 5.0.2 on, the precedence of the
+ <literal>NOT</literal> operator is such that expressions
+ such as <literal>NOT a BETWEEN b AND c</literal> are
+ parsed as <literal>NOT (a BETWEEN b AND c)</literal>.
+ Before MySQL 5.0.2, the expression is parsed as
+ <literal>(NOT a) BETWEEN b AND c</literal>. The old
+ higher-precedence behavior can be obtained by enabling the
+ <literal>HIGH_NOT_PRECEDENCE</literal> SQL mode. (Added in
+ MySQL 5.0.2)
+ </para>
+
+<programlisting>
+mysql> <userinput>SET sql_mode = '';</userinput>
+mysql> <userinput>SELECT NOT 1 BETWEEN -5 AND 5;</userinput>
+ -> 0
+mysql> <userinput>SET sql_mode = 'broken_not';</userinput>
+mysql> <userinput>SELECT NOT 1 BETWEEN -5 AND 5;</userinput>
+ -> 1
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>IGNORE_SPACE SQL mode</primary>
+ </indexterm>
+
+ <literal>IGNORE_SPACE</literal>
+ </para>
+
+ <para>
+ Allow spaces between a function name and the
+ ‘<literal>(</literal>’ character. This forces
+ all function names to be treated as reserved words. As a
+ result, if you want to access any database, table, or
+ column name that is a reserved word, you must quote it.
+ For example, because there is a <literal>USER()</literal>
+ function, the name of the <literal>user</literal> table in
+ the <literal>mysql</literal> database and the
+ <literal>User</literal> column in that table become
+ reserved, so you must quote them:
+ </para>
+
+<programlisting>
+SELECT "User" FROM mysql."user";
+</programlisting>
+
+ <para>
+ The <literal>IGNORE_SPACE</literal> SQL mode applies to
+ built-in functions, not to stored routines. it is always
+ allowable to have spaces after a routine name, regardless
+ of whether <literal>IGNORE_SPACE</literal> is enabled.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_AUTO_CREATE_USER SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_AUTO_CREATE_USER</literal>
+ </para>
+
+ <para>
+ Prevent <literal>GRANT</literal> from automatically
+ creating new users if it would otherwise do so, unless a
+ non-empty password also is specified. (Added in MySQL
+ 5.0.2)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_AUTO_VALUE_ON_ZERO SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_AUTO_VALUE_ON_ZERO</literal>
+ </para>
+
+ <para>
+ <literal>NO_AUTO_VALUE_ON_ZERO</literal> affects handling
+ of <literal>AUTO_INCREMENT</literal> columns. Normally,
+ you generate the next sequence number for the column by
+ inserting either <literal>NULL</literal> or
+ <literal>0</literal> into it.
+ <literal>NO_AUTO_VALUE_ON_ZERO</literal> suppresses this
+ behavior for <literal>0</literal> so that only
+ <literal>NULL</literal> generates the next sequence
+ number.
+ </para>
+
+ <para>
+ This mode can be useful if <literal>0</literal> has been
+ stored in a table's <literal>AUTO_INCREMENT</literal>
+ column. (Storing <literal>0</literal> is not a recommended
+ practice, by the way.) For example, if you dump the table
+ with <command>mysqldump</command> and then reload it,
+ MySQL normally generates new sequence numbers when it
+ encounters the <literal>0</literal> values, resulting in a
+ table with contents different from the one that was
+ dumped. Enabling <literal>NO_AUTO_VALUE_ON_ZERO</literal>
+ before reloading the dump file solves this problem.
+ <command>mysqldump</command> now automatically includes in
+ its output a statement that enables
+ <literal>NO_AUTO_VALUE_ON_ZERO</literal>, to avoid this
+ problem.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_BACKSLASH_ESCAPES SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_BACKSLASH_ESCAPES</literal>
+ </para>
+
+ <para>
+ Disable the use of the backslash character
+ (‘<literal>\</literal>’) as an escape
+ character within strings. With this mode enabled,
+ backslash becomes any ordinary character like any other.
+ (Implemented in MySQL 5.0.1)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_DIR_IN_CREATE SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_DIR_IN_CREATE</literal>
+ </para>
+
+ <para>
+ When creating a table, ignore all <literal>INDEX
+ DIRECTORY</literal> and <literal>DATA DIRECTORY</literal>
+ directives. This option is useful on slave replication
+ servers.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>NO_ENGINE_SUBSTITUTION</literal>
+ </para>
+
+ <para>
+ Prevents automatic substitution of storage engine when the
+ requested storage engine is disabled or not compiled in.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_FIELD_OPTIONS SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_FIELD_OPTIONS</literal>
+ </para>
+
+ <para>
+ Don't print MySQL-specific column options in the output of
+ <literal>SHOW CREATE TABLE</literal>. This mode is used by
+ <command>mysqldump</command> in portability mode.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_KEY_OPTIONS SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_KEY_OPTIONS</literal>
+ </para>
+
+ <para>
+ Don't print MySQL-specific index options in the output of
+ <literal>SHOW CREATE TABLE</literal>. This mode is used by
+ <command>mysqldump</command> in portability mode.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_TABLE_OPTIONS SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_TABLE_OPTIONS</literal>
+ </para>
+
+ <para>
+ Don't print MySQL-specific table options (such as
+ <literal>ENGINE</literal>) in the output of <literal>SHOW
+ CREATE TABLE</literal>. This mode is used by
+ <command>mysqldump</command> in portability mode.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_UNSIGNED_SUBTRACTION SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_UNSIGNED_SUBTRACTION</literal>
+ </para>
+
+ <para>
+ In subtraction operations, don't mark the result as
+ <literal>UNSIGNED</literal> if one of the operands is
+ unsigned. Note that this makes <literal>UNSIGNED
+ BIGINT</literal> not 100% usable in all contexts. See
+ <xref linkend="cast-functions"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_ZERO_DATE SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_ZERO_DATE</literal>
+ </para>
+
+ <para>
+ In strict mode, don't allow
+ <literal>'0000-00-00'</literal> as a valid date. You can
+ still insert zero dates with the <literal>IGNORE</literal>
+ option. When not in strict mode, the date is accepted but
+ a warning is generated. (Added in MySQL 5.0.2)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_ZERO_IN_DATE SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_ZERO_IN_DATE</literal>
+ </para>
+
+ <para>
+ In strict mode, don't accept dates where the month or day
+ part is 0. If used with the <literal>IGNORE</literal>
+ option, we insert a <literal>'0000-00-00'</literal> date
+ for any such date. When not in strict mode, the date is
+ accepted but a warning is generated. (Added in MySQL
+ 5.0.2)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>ONLY_FULL_GROUP_BY SQL mode</primary>
+ </indexterm>
+
+ <literal>ONLY_FULL_GROUP_BY</literal>
+ </para>
+
+ <para>
+ Do not allow queries that in the <literal>GROUP
+ BY</literal> part refer to a column that is not selected.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>PIPES_AS_CONCAT SQL mode</primary>
+ </indexterm>
+
+ <literal>PIPES_AS_CONCAT</literal>
+ </para>
+
+ <para>
+ Treat <literal>||</literal> as a string concatenation
+ operator (same as <literal>CONCAT()</literal>) rather than
+ as a synonym for <literal>OR</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>REAL_AS_FLOAT SQL mode</primary>
+ </indexterm>
+
+ <literal>REAL_AS_FLOAT</literal>
+ </para>
+
+ <para>
+ Treat <literal>REAL</literal> as a synonym for
+ <literal>FLOAT</literal> rather than as a synonym for
+ <literal>DOUBLE</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>STRICT_ALL_TABLES SQL mode</primary>
+ </indexterm>
+
+ <literal>STRICT_ALL_TABLES</literal>
+ </para>
+
+ <para>
+ Enable strict mode for all storage engines. Invalid data
+ values are rejected. Additional detail follows. (Added in
+ MySQL 5.0.2)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>STRICT_TRANS_TABLES SQL mode</primary>
+ </indexterm>
+
+ <literal>STRICT_TRANS_TABLES</literal>
+ </para>
+
+ <para>
+ Enable strict mode for transactional storage engines, and
+ when possible for non-transactional storage engines.
+ Additional details follow. (Implemented in MySQL 5.0.2)
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ Strict mode controls how MySQL handles input values that are
+ invalid or missing. A value can be invalid for several
+ reasons. For example, it might have the wrong data type for
+ the column, or it might be out of range. A value is missing
+ when a new row to be inserted does not contain a value for a
+ column that has no explicit <literal>DEFAULT</literal> clause
+ in its definition.
+ </para>
+
+ <para>
+ For transactional tables, an error occurs for invalid or
+ missing values in a statement when either of the
+ <literal>STRICT_ALL_TABLES</literal> or
+ <literal>STRICT_TRANS_TABLES</literal> modes are enabled. The
+ statement is aborted and rolled back.
+ </para>
+
+ <para>
+ For non-transactional tables, the behavior is the same for
+ either mode, if the bad value occurs in the first row to be
+ inserted or updated. The statement is aborted and the table
+ remains unchanged. If the statement inserts or modifies
+ multiple rows and the bad value occurs in the second or later
+ row, the result depends on which strict option is enabled:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ For <literal>STRICT_ALL_TABLES</literal>, MySQL returns an
+ error and ignores the rest of the rows. However, in this
+ case, the earlier rows still have been inserted or
+ updated. This means that you might get a partial update,
+ which might not be what you want. To avoid this, it's best
+ to use single-row statements because these can be aborted
+ without changing the table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ For <literal>STRICT_TRANS_TABLES</literal>, MySQL converts
+ an invalid value to the closest valid value for the column
+ and insert the adjusted value. If a value is missing,
+ MySQL inserts the implicit default value for the column
+ data type. In either case, MySQL generates a warning
+ rather than an error and continues processing the
+ statement. Implicit defaults are described in
+ <xref linkend="create-table"/>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ Strict mode disallows invalid date values such as
+ <literal>'2004-04-31'</literal>. It does not disallow dates
+ with zero parts such as <literal>2004-04-00'</literal> or
+ <quote>zero</quote> dates. To disallow these as well, enable
+ the <literal>NO_ZERO_IN_DATE</literal> and
+ <literal>NO_ZERO_DATE</literal> SQL modes in addition to
+ strict mode.
+ </para>
+
+ <para>
+ If you are not using strict mode (that is, neither
+ <literal>STRICT_TRANS_TABLES</literal> nor
+ <literal>STRICT_ALL_TABLES</literal> is enabled), MySQL
+ inserts adjusted values for invalid or missing values and
+ produces warnings. In strict mode, you can produce this
+ behavior by using <literal>INSERT IGNORE</literal> or
+ <literal>UPDATE IGNORE</literal>. See
+ <xref linkend="show-warnings"/>.
+ </para>
+
+ <para>
+ The following special modes are provided as shorthand for
+ combinations of mode values from the preceding list. All are
+ available in MySQL ¤t-series; beginning with version
+ 5.0.0, except for <literal>TRADITIONAL</literal>, which was
+ implemented in MySQL 5.0.2.
+ </para>
+
+ <para>
+ The descriptions include all mode values that are available in
+ the most recent version of MySQL. For older versions, a
+ combination mode does not include individual mode values that
+ are not available except in newer versions.
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>ANSI SQL mode</primary>
+ </indexterm>
+
+ <literal>ANSI</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>REAL_AS_FLOAT</literal>,
+ <literal>PIPES_AS_CONCAT</literal>,
+ <literal>ANSI_QUOTES</literal>,
+ <literal>IGNORE_SPACE</literal>. Before MySQL 5.0.3,
+ <literal>ANSI</literal> also includes
+ <literal>ONLY_FULL_GROUP_BY</literal>. See
+ <xref linkend="ansi-mode"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>DB2 SQL mode</primary>
+ </indexterm>
+
+ <literal>DB2</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+ <literal>ANSI_QUOTES</literal>,
+ <literal>IGNORE_SPACE</literal>,
+ <literal>NO_KEY_OPTIONS</literal>,
+ <literal>NO_TABLE_OPTIONS</literal>,
+ <literal>NO_FIELD_OPTIONS</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>MAXDB SQL mode</primary>
+ </indexterm>
+
+ <literal>MAXDB</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+ <literal>ANSI_QUOTES</literal>,
+ <literal>IGNORE_SPACE</literal>,
+ <literal>NO_KEY_OPTIONS</literal>,
+ <literal>NO_TABLE_OPTIONS</literal>,
+ <literal>NO_FIELD_OPTIONS</literal>,
+ <literal>NO_AUTO_CREATE_USER</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>MSSQL SQL mode</primary>
+ </indexterm>
+
+ <literal>MSSQL</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+ <literal>ANSI_QUOTES</literal>,
+ <literal>IGNORE_SPACE</literal>,
+ <literal>NO_KEY_OPTIONS</literal>,
+ <literal>NO_TABLE_OPTIONS</literal>,
+ <literal>NO_FIELD_OPTIONS</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>MYSQL323 SQL mode</primary>
+ </indexterm>
+
+ <literal>MYSQL323</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>NO_FIELD_OPTIONS</literal>,
+ <literal>HIGH_NOT_PRECEDENCE</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>MYSQL40 SQL mode</primary>
+ </indexterm>
+
+ <literal>MYSQL40</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>NO_FIELD_OPTIONS</literal>,
+ <literal>HIGH_NOT_PRECEDENCE</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>ORACLE SQL mode</primary>
+ </indexterm>
+
+ <literal>ORACLE</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+ <literal>ANSI_QUOTES</literal>,
+ <literal>IGNORE_SPACE</literal>,
+ <literal>NO_KEY_OPTIONS</literal>,
+ <literal>NO_TABLE_OPTIONS</literal>,
+ <literal>NO_FIELD_OPTIONS</literal>,
+ <literal>NO_AUTO_CREATE_USER</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>POSTGRESQL SQL mode</primary>
+ </indexterm>
+
+ <literal>POSTGRESQL</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+ <literal>ANSI_QUOTES</literal>,
+ <literal>IGNORE_SPACE</literal>,
+ <literal>NO_KEY_OPTIONS</literal>,
+ <literal>NO_TABLE_OPTIONS</literal>,
+ <literal>NO_FIELD_OPTIONS</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>TRADITIONAL SQL mode</primary>
+ </indexterm>
+
+ <literal>TRADITIONAL</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>STRICT_TRANS_TABLES</literal>,
+ <literal>STRICT_ALL_TABLES</literal>,
+ <literal>NO_ZERO_IN_DATE</literal>,
+ <literal>NO_ZERO_DATE</literal>,
+ <literal>ERROR_FOR_DIVISION_BY_ZERO</literal>,
+ <literal>NO_AUTO_CREATE_USER</literal>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </refsection>
+
<refsection id="server-shutdown">
<title>&title-server-shutdown;</title>
Modified: trunk/refman-5.1/database-administration.xml
===================================================================
--- trunk/refman-5.1/database-administration.xml 2006-01-23 21:26:18 UTC (rev 1003)
+++ trunk/refman-5.1/database-administration.xml 2006-01-23 21:26:35 UTC (rev 1004)
@@ -1933,820 +1933,6 @@
</refsection>
- <refsection id="server-sql-mode">
-
- <title>&title-server-sql-mode;</title>
-
- <para>
- The MySQL server can operate in different SQL modes, and can
- apply these modes differently for different clients. This
- enables each application to tailor the server's operating mode
- to its own requirements.
- </para>
-
- <para>
- Modes define what SQL syntax MySQL should support and what
- kind of data validation checks it should perform. This makes
- it easier to use MySQL in different environments and to use
- MySQL together with other database servers.
- </para>
-
- <para>
- You can set the default SQL mode by starting
- <command>mysqld</command> with the
- <option>--sql-mode="<replaceable>modes</replaceable>"</option>
- option. The value also can be empty
- (<option>--sql-mode=""</option>) if you want to reset it.
- </para>
-
- <para>
- You can also change the SQL mode after startup time by setting
- the <literal>sql_mode</literal> variable using a <literal>SET
- [SESSION|GLOBAL]
- sql_mode='<replaceable>modes</replaceable>'</literal>
- statement. Setting the <literal>GLOBAL</literal> variable
- requires the <literal>SUPER</literal> privilege and affects
- the operation of all clients that connect from that time on.
- Setting the <literal>SESSION</literal> variable affects only
- the current client. Any client can change its own session
- <literal>sql_mode</literal> value at any time.
- </para>
-
- <para>
- <replaceable>modes</replaceable> is a list of different modes
- separated by comma (‘<literal>,</literal>’)
- characters. You can retrieve the current mode by issuing a
- <literal>SELECT @@sql_mode</literal> statement. The default
- value is empty (no modes set).
- </para>
-
- <para>
- The most important <literal>sql_mode</literal> values are
- probably these:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <indexterm>
- <primary>ANSI SQL mode</primary>
- </indexterm>
-
- <literal>ANSI</literal>
- </para>
-
- <para>
- Change syntax and behavior to be more conformant to
- standard SQL.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>STRICT_TRANS_TABLES SQL mode</primary>
- </indexterm>
-
- <literal>STRICT_TRANS_TABLES</literal>
- </para>
-
- <para>
- If a value could not be inserted as given into a
- transactional table, abort the statement. For a
- non-transactional table, abort the statement if the value
- occurs in a single-row statement or the first row of a
- multiple-row statement. More detail is given later in this
- section.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>TRADITIONAL SQL mode</primary>
- </indexterm>
-
- <literal>TRADITIONAL</literal>
- </para>
-
- <para>
- Make MySQL behave like a <quote>traditional</quote> SQL
- database system. A simple description of this mode is
- <quote>give an error instead of a warning</quote> when
- inserting an incorrect value into a column.
- <emphasis role="bold">Note</emphasis>: The
- <literal>INSERT</literal>/<literal>UPDATE</literal> aborts
- as soon as the error is noticed. This may not be what you
- want if you are using a non-transactional storage engine,
- because data changes made prior to the error are not be
- rolled back, resulting in a <quote>partially done</quote>
- update.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <indexterm>
- <primary>STRICT SQL mode</primary>
- </indexterm>
-
- <para>
- When this manual refers to <quote>strict mode,</quote> it
- means a mode where at least one of
- <literal>STRICT_TRANS_TABLES</literal> or
- <literal>STRICT_ALL_TABLES</literal> is enabled.
- </para>
-
- <para>
- The following list describes all supported modes:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <indexterm>
- <primary>ALLOW_INVALID_DATES SQL mode</primary>
- </indexterm>
-
- <literal>ALLOW_INVALID_DATES</literal>
- </para>
-
- <para>
- Don't do full checking of dates. Check only that the month
- is in the range from 1 to 12 and the day is in the range
- from 1 to 31. This is very convenient for Web applications
- where you obtain year, month, and day in three different
- fields and you want to store exactly what the user
- inserted (without date validation). This mode applies to
- <literal>DATE</literal> and <literal>DATETIME</literal>
- columns. It does not apply <literal>TIMESTAMP</literal>
- columns, which always require a valid date.
- </para>
-
- <para>
- The server requires that month and day values be legal,
- and not merely in the range 1 to 12 and 1 to 31,
- respectively. With strict mode disabled, invalid dates
- such as <literal>'2004-04-31'</literal> are converted to
- <literal>'0000-00-00'</literal> and a warning is
- generated. With strict mode enabled, invalid dates
- generate an error. To allow such dates, enable
- <literal>ALLOW_INVALID_DATES</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>ANSI_QUOTES SQL mode</primary>
- </indexterm>
-
- <literal>ANSI_QUOTES</literal>
- </para>
-
- <para>
- Treat ‘<literal>"</literal>’ as an identifier
- quote character (like the
- ‘<literal>`</literal>’ quote character) and
- not as a string quote character. You can still use
- ‘<literal>`</literal>’ to quote identifiers in
- ANSI mode. With <literal>ANSI_QUOTES</literal> enabled,
- you cannot use double quotes to quote a literal string,
- because it is interpreted as an identifier.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>ERROR_FOR_DIVISION_BY_ZERO SQL mode</primary>
- </indexterm>
-
- <literal>ERROR_FOR_DIVISION_BY_ZERO</literal>
- </para>
-
- <para>
- Produce an error in strict mode (otherwise a warning) when
- we encounter a division by zero (or
- <literal>MOD(X,0)</literal>) during an
- <literal>INSERT</literal> or <literal>UPDATE</literal>. If
- this mode is not given, MySQL instead returns
- <literal>NULL</literal> for divisions by zero. If used in
- <literal>INSERT IGNORE</literal> or <literal>UPDATE
- IGNORE</literal>, MySQL generates a warning for divisions
- by zero, but the result of the operation is
- <literal>NULL</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>HIGH_NOT_PRECEDENCE SQL mode</primary>
- </indexterm>
-
- <literal>HIGH_NOT_PRECEDENCE</literal>
- </para>
-
- <para>
- The precedence of the <literal>NOT</literal> operator is
- such that expressions such as <literal>NOT a BETWEEN b AND
- c</literal> are parsed as <literal>NOT (a BETWEEN b AND
- c)</literal>. In some older versions of MySQL, the
- expression was parsed as <literal>(NOT a) BETWEEN b AND
- c</literal>. The old higher-precedence behavior can be
- obtained by enabling the
- <literal>HIGH_NOT_PRECEDENCE</literal> SQL mode.
- </para>
-
-<programlisting>
-mysql> <userinput>SET sql_mode = '';</userinput>
-mysql> <userinput>SELECT NOT 1 BETWEEN -5 AND 5;</userinput>
- -> 0
-mysql> <userinput>SET sql_mode = 'broken_not';</userinput>
-mysql> <userinput>SELECT NOT 1 BETWEEN -5 AND 5;</userinput>
- -> 1
-</programlisting>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>IGNORE_SPACE SQL mode</primary>
- </indexterm>
-
- <literal>IGNORE_SPACE</literal>
- </para>
-
- <para>
- Allow spaces between a function name and the
- ‘<literal>(</literal>’ character. This forces
- all function names to be treated as reserved words. As a
- result, if you want to access any database, table, or
- column name that is a reserved word, you must quote it.
- For example, because there is a <literal>USER()</literal>
- function, the name of the <literal>user</literal> table in
- the <literal>mysql</literal> database and the
- <literal>User</literal> column in that table become
- reserved, so you must quote them:
- </para>
-
-<programlisting>
-SELECT "User" FROM mysql."user";
-</programlisting>
-
- <para>
- The <literal>IGNORE_SPACE</literal> SQL mode applies to
- built-in functions, not to stored routines. it is always
- allowable to have spaces after a routine name, regardless
- of whether <literal>IGNORE_SPACE</literal> is enabled.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_AUTO_CREATE_USER SQL mode</primary>
- </indexterm>
-
- <literal>NO_AUTO_CREATE_USER</literal>
- </para>
-
- <para>
- Prevent <literal>GRANT</literal> from automatically
- creating new users if it would otherwise do so, unless a
- non-empty password also is specified.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_AUTO_VALUE_ON_ZERO SQL mode</primary>
- </indexterm>
-
- <literal>NO_AUTO_VALUE_ON_ZERO</literal>
- </para>
-
- <para>
- <literal>NO_AUTO_VALUE_ON_ZERO</literal> affects handling
- of <literal>AUTO_INCREMENT</literal> columns. Normally,
- you generate the next sequence number for the column by
- inserting either <literal>NULL</literal> or
- <literal>0</literal> into it.
- <literal>NO_AUTO_VALUE_ON_ZERO</literal> suppresses this
- behavior for <literal>0</literal> so that only
- <literal>NULL</literal> generates the next sequence
- number.
- </para>
-
- <para>
- This mode can be useful if <literal>0</literal> has been
- stored in a table's <literal>AUTO_INCREMENT</literal>
- column. (Storing <literal>0</literal> is not a recommended
- practice, by the way.) For example, if you dump the table
- with <command>mysqldump</command> and then reload it,
- MySQL normally generates new sequence numbers when it
- encounters the <literal>0</literal> values, resulting in a
- table with contents different from the one that was
- dumped. Enabling <literal>NO_AUTO_VALUE_ON_ZERO</literal>
- before reloading the dump file solves this problem.
- <command>mysqldump</command> now automatically includes in
- its output a statement that enables
- <literal>NO_AUTO_VALUE_ON_ZERO</literal>, to avoid this
- problem.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_BACKSLASH_ESCAPES SQL mode</primary>
- </indexterm>
-
- <literal>NO_BACKSLASH_ESCAPES</literal>
- </para>
-
- <para>
- Disable the use of the backslash character
- (‘<literal>\</literal>’) as an escape
- character within strings. With this mode enabled,
- backslash becomes any ordinary character like any other.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_DIR_IN_CREATE SQL mode</primary>
- </indexterm>
-
- <literal>NO_DIR_IN_CREATE</literal>
- </para>
-
- <para>
- When creating a table, ignore all <literal>INDEX
- DIRECTORY</literal> and <literal>DATA DIRECTORY</literal>
- directives. This option is useful on slave replication
- servers.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>NO_ENGINE_SUBSTITUTION</literal>
- </para>
-
- <para>
- Prevents automatic substitution of storage engine when the
- requested storage engine is disabled or not compiled in.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_FIELD_OPTIONS SQL mode</primary>
- </indexterm>
-
- <literal>NO_FIELD_OPTIONS</literal>
- </para>
-
- <para>
- Don't print MySQL-specific column options in the output of
- <literal>SHOW CREATE TABLE</literal>. This mode is used by
- <command>mysqldump</command> in portability mode.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_KEY_OPTIONS SQL mode</primary>
- </indexterm>
-
- <literal>NO_KEY_OPTIONS</literal>
- </para>
-
- <para>
- Don't print MySQL-specific index options in the output of
- <literal>SHOW CREATE TABLE</literal>. This mode is used by
- <command>mysqldump</command> in portability mode.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_TABLE_OPTIONS SQL mode</primary>
- </indexterm>
-
- <literal>NO_TABLE_OPTIONS</literal>
- </para>
-
- <para>
- Don't print MySQL-specific table options (such as
- <literal>ENGINE</literal>) in the output of <literal>SHOW
- CREATE TABLE</literal>. This mode is used by
- <command>mysqldump</command> in portability mode.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_UNSIGNED_SUBTRACTION SQL mode</primary>
- </indexterm>
-
- <literal>NO_UNSIGNED_SUBTRACTION</literal>
- </para>
-
- <para>
- In subtraction operations, don't mark the result as
- <literal>UNSIGNED</literal> if one of the operands is
- unsigned. Note that this makes <literal>UNSIGNED
- BIGINT</literal> not 100% usable in all contexts. See
- <xref linkend="cast-functions"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_ZERO_DATE SQL mode</primary>
- </indexterm>
-
- <literal>NO_ZERO_DATE</literal>
- </para>
-
- <para>
- In strict mode, don't allow
- <literal>'0000-00-00'</literal> as a valid date. You can
- still insert zero dates with the <literal>IGNORE</literal>
- option. When not in strict mode, the date is accepted but
- a warning is generated.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>NO_ZERO_IN_DATE SQL mode</primary>
- </indexterm>
-
- <literal>NO_ZERO_IN_DATE</literal>
- </para>
-
- <para>
- In strict mode, don't accept dates where the month or day
- part is 0. If used with the <literal>IGNORE</literal>
- option, we insert a <literal>'0000-00-00'</literal> date
- for any such date. When not in strict mode, the date is
- accepted but a warning is generated.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>ONLY_FULL_GROUP_BY SQL mode</primary>
- </indexterm>
-
- <literal>ONLY_FULL_GROUP_BY</literal>
- </para>
-
- <para>
- Do not allow queries that in the <literal>GROUP
- BY</literal> part refer to a column that is not selected.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>PIPES_AS_CONCAT SQL mode</primary>
- </indexterm>
-
- <literal>PIPES_AS_CONCAT</literal>
- </para>
-
- <para>
- Treat <literal>||</literal> as a string concatenation
- operator (same as <literal>CONCAT()</literal>) rather than
- as a synonym for <literal>OR</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>REAL_AS_FLOAT SQL mode</primary>
- </indexterm>
-
- <literal>REAL_AS_FLOAT</literal>
- </para>
-
- <para>
- Treat <literal>REAL</literal> as a synonym for
- <literal>FLOAT</literal> rather than as a synonym for
- <literal>DOUBLE</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>STRICT_ALL_TABLES SQL mode</primary>
- </indexterm>
-
- <literal>STRICT_ALL_TABLES</literal>
- </para>
-
- <para>
- Enable strict mode for all storage engines. Invalid data
- values are rejected. Additional detail follows.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>STRICT_TRANS_TABLES SQL mode</primary>
- </indexterm>
-
- <literal>STRICT_TRANS_TABLES</literal>
- </para>
-
- <para>
- Enable strict mode for transactional storage engines, and
- when possible for non-transactional storage engines.
- Additional details follow.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- Strict mode controls how MySQL handles input values that are
- invalid or missing. A value can be invalid for several
- reasons. For example, it might have the wrong data type for
- the column, or it might be out of range. A value is missing
- when a new row to be inserted does not contain a value for a
- column that has no explicit <literal>DEFAULT</literal> clause
- in its definition.
- </para>
-
- <para>
- For transactional tables, an error occurs for invalid or
- missing values in a statement when either of the
- <literal>STRICT_ALL_TABLES</literal> or
- <literal>STRICT_TRANS_TABLES</literal> modes are enabled. The
- statement is aborted and rolled back.
- </para>
-
- <para>
- For non-transactional tables, the behavior is the same for
- either mode, if the bad value occurs in the first row to be
- inserted or updated. The statement is aborted and the table
- remains unchanged. If the statement inserts or modifies
- multiple rows and the bad value occurs in the second or later
- row, the result depends on which strict option is enabled:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- For <literal>STRICT_ALL_TABLES</literal>, MySQL returns an
- error and ignores the rest of the rows. However, in this
- case, the earlier rows still have been inserted or
- updated. This means that you might get a partial update,
- which might not be what you want. To avoid this, it's best
- to use single-row statements because these can be aborted
- without changing the table.
- </para>
- </listitem>
-
- <listitem>
- <para>
- For <literal>STRICT_TRANS_TABLES</literal>, MySQL converts
- an invalid value to the closest valid value for the column
- and insert the adjusted value. If a value is missing,
- MySQL inserts the implicit default value for the column
- data type. In either case, MySQL generates a warning
- rather than an error and continues processing the
- statement. Implicit defaults are described in
- <xref linkend="create-table"/>.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- Strict mode disallows invalid date values such as
- <literal>'2004-04-31'</literal>. It does not disallow dates
- with zero parts such as <literal>2004-04-00'</literal> or
- <quote>zero</quote> dates. To disallow these as well, enable
- the <literal>NO_ZERO_IN_DATE</literal> and
- <literal>NO_ZERO_DATE</literal> SQL modes in addition to
- strict mode.
- </para>
-
- <para>
- If you are not using strict mode (that is, neither
- <literal>STRICT_TRANS_TABLES</literal> nor
- <literal>STRICT_ALL_TABLES</literal> is enabled), MySQL
- inserts adjusted values for invalid or missing values and
- produces warnings. In strict mode, you can produce this
- behavior by using <literal>INSERT IGNORE</literal> or
- <literal>UPDATE IGNORE</literal>. See
- <xref linkend="show-warnings"/>.
- </para>
-
- <para>
- The following special modes are provided as shorthand for
- combinations of mode values from the preceding list.
- </para>
-
- <para>
- The descriptions include all mode values that are available in
- the most recent version of MySQL. For older versions, a
- combination mode does not include individual mode values that
- are not available except in newer versions.
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <indexterm>
- <primary>ANSI SQL mode</primary>
- </indexterm>
-
- <literal>ANSI</literal>
- </para>
-
- <para>
- Equivalent to <literal>REAL_AS_FLOAT</literal>,
- <literal>PIPES_AS_CONCAT</literal>,
- <literal>ANSI_QUOTES</literal>,
- <literal>IGNORE_SPACE</literal>. See
- <xref linkend="ansi-mode"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>DB2 SQL mode</primary>
- </indexterm>
-
- <literal>DB2</literal>
- </para>
-
- <para>
- Equivalent to <literal>PIPES_AS_CONCAT</literal>,
- <literal>ANSI_QUOTES</literal>,
- <literal>IGNORE_SPACE</literal>,
- <literal>NO_KEY_OPTIONS</literal>,
- <literal>NO_TABLE_OPTIONS</literal>,
- <literal>NO_FIELD_OPTIONS</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>MAXDB SQL mode</primary>
- </indexterm>
-
- <literal>MAXDB</literal>
- </para>
-
- <para>
- Equivalent to <literal>PIPES_AS_CONCAT</literal>,
- <literal>ANSI_QUOTES</literal>,
- <literal>IGNORE_SPACE</literal>,
- <literal>NO_KEY_OPTIONS</literal>,
- <literal>NO_TABLE_OPTIONS</literal>,
- <literal>NO_FIELD_OPTIONS</literal>,
- <literal>NO_AUTO_CREATE_USER</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>MSSQL SQL mode</primary>
- </indexterm>
-
- <literal>MSSQL</literal>
- </para>
-
- <para>
- Equivalent to <literal>PIPES_AS_CONCAT</literal>,
- <literal>ANSI_QUOTES</literal>,
- <literal>IGNORE_SPACE</literal>,
- <literal>NO_KEY_OPTIONS</literal>,
- <literal>NO_TABLE_OPTIONS</literal>,
- <literal>NO_FIELD_OPTIONS</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>MYSQL323 SQL mode</primary>
- </indexterm>
-
- <literal>MYSQL323</literal>
- </para>
-
- <para>
- Equivalent to <literal>NO_FIELD_OPTIONS</literal>,
- <literal>HIGH_NOT_PRECEDENCE</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>MYSQL40 SQL mode</primary>
- </indexterm>
-
- <literal>MYSQL40</literal>
- </para>
-
- <para>
- Equivalent to <literal>NO_FIELD_OPTIONS</literal>,
- <literal>HIGH_NOT_PRECEDENCE</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>ORACLE SQL mode</primary>
- </indexterm>
-
- <literal>ORACLE</literal>
- </para>
-
- <para>
- Equivalent to <literal>PIPES_AS_CONCAT</literal>,
- <literal>ANSI_QUOTES</literal>,
- <literal>IGNORE_SPACE</literal>,
- <literal>NO_KEY_OPTIONS</literal>,
- <literal>NO_TABLE_OPTIONS</literal>,
- <literal>NO_FIELD_OPTIONS</literal>,
- <literal>NO_AUTO_CREATE_USER</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>POSTGRESQL SQL mode</primary>
- </indexterm>
-
- <literal>POSTGRESQL</literal>
- </para>
-
- <para>
- Equivalent to <literal>PIPES_AS_CONCAT</literal>,
- <literal>ANSI_QUOTES</literal>,
- <literal>IGNORE_SPACE</literal>,
- <literal>NO_KEY_OPTIONS</literal>,
- <literal>NO_TABLE_OPTIONS</literal>,
- <literal>NO_FIELD_OPTIONS</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>TRADITIONAL SQL mode</primary>
- </indexterm>
-
- <literal>TRADITIONAL</literal>
- </para>
-
- <para>
- Equivalent to <literal>STRICT_TRANS_TABLES</literal>,
- <literal>STRICT_ALL_TABLES</literal>,
- <literal>NO_ZERO_IN_DATE</literal>,
- <literal>NO_ZERO_DATE</literal>,
- <literal>ERROR_FOR_DIVISION_BY_ZERO</literal>,
- <literal>NO_AUTO_CREATE_USER</literal>.
- </para>
- </listitem>
-
- </itemizedlist>
-
- </refsection>
-
<refsection id="server-system-variables">
<title>&title-server-system-variables;</title>
@@ -8854,6 +8040,820 @@
</refsection>
+ <refsection id="server-sql-mode">
+
+ <title>&title-server-sql-mode;</title>
+
+ <para>
+ The MySQL server can operate in different SQL modes, and can
+ apply these modes differently for different clients. This
+ enables each application to tailor the server's operating mode
+ to its own requirements.
+ </para>
+
+ <para>
+ Modes define what SQL syntax MySQL should support and what
+ kind of data validation checks it should perform. This makes
+ it easier to use MySQL in different environments and to use
+ MySQL together with other database servers.
+ </para>
+
+ <para>
+ You can set the default SQL mode by starting
+ <command>mysqld</command> with the
+ <option>--sql-mode="<replaceable>modes</replaceable>"</option>
+ option. The value also can be empty
+ (<option>--sql-mode=""</option>) if you want to reset it.
+ </para>
+
+ <para>
+ You can also change the SQL mode after startup time by setting
+ the <literal>sql_mode</literal> variable using a <literal>SET
+ [SESSION|GLOBAL]
+ sql_mode='<replaceable>modes</replaceable>'</literal>
+ statement. Setting the <literal>GLOBAL</literal> variable
+ requires the <literal>SUPER</literal> privilege and affects
+ the operation of all clients that connect from that time on.
+ Setting the <literal>SESSION</literal> variable affects only
+ the current client. Any client can change its own session
+ <literal>sql_mode</literal> value at any time.
+ </para>
+
+ <para>
+ <replaceable>modes</replaceable> is a list of different modes
+ separated by comma (‘<literal>,</literal>’)
+ characters. You can retrieve the current mode by issuing a
+ <literal>SELECT @@sql_mode</literal> statement. The default
+ value is empty (no modes set).
+ </para>
+
+ <para>
+ The most important <literal>sql_mode</literal> values are
+ probably these:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>ANSI SQL mode</primary>
+ </indexterm>
+
+ <literal>ANSI</literal>
+ </para>
+
+ <para>
+ Change syntax and behavior to be more conformant to
+ standard SQL.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>STRICT_TRANS_TABLES SQL mode</primary>
+ </indexterm>
+
+ <literal>STRICT_TRANS_TABLES</literal>
+ </para>
+
+ <para>
+ If a value could not be inserted as given into a
+ transactional table, abort the statement. For a
+ non-transactional table, abort the statement if the value
+ occurs in a single-row statement or the first row of a
+ multiple-row statement. More detail is given later in this
+ section.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>TRADITIONAL SQL mode</primary>
+ </indexterm>
+
+ <literal>TRADITIONAL</literal>
+ </para>
+
+ <para>
+ Make MySQL behave like a <quote>traditional</quote> SQL
+ database system. A simple description of this mode is
+ <quote>give an error instead of a warning</quote> when
+ inserting an incorrect value into a column.
+ <emphasis role="bold">Note</emphasis>: The
+ <literal>INSERT</literal>/<literal>UPDATE</literal> aborts
+ as soon as the error is noticed. This may not be what you
+ want if you are using a non-transactional storage engine,
+ because data changes made prior to the error are not be
+ rolled back, resulting in a <quote>partially done</quote>
+ update.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <indexterm>
+ <primary>STRICT SQL mode</primary>
+ </indexterm>
+
+ <para>
+ When this manual refers to <quote>strict mode,</quote> it
+ means a mode where at least one of
+ <literal>STRICT_TRANS_TABLES</literal> or
+ <literal>STRICT_ALL_TABLES</literal> is enabled.
+ </para>
+
+ <para>
+ The following list describes all supported modes:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>ALLOW_INVALID_DATES SQL mode</primary>
+ </indexterm>
+
+ <literal>ALLOW_INVALID_DATES</literal>
+ </para>
+
+ <para>
+ Don't do full checking of dates. Check only that the month
+ is in the range from 1 to 12 and the day is in the range
+ from 1 to 31. This is very convenient for Web applications
+ where you obtain year, month, and day in three different
+ fields and you want to store exactly what the user
+ inserted (without date validation). This mode applies to
+ <literal>DATE</literal> and <literal>DATETIME</literal>
+ columns. It does not apply <literal>TIMESTAMP</literal>
+ columns, which always require a valid date.
+ </para>
+
+ <para>
+ The server requires that month and day values be legal,
+ and not merely in the range 1 to 12 and 1 to 31,
+ respectively. With strict mode disabled, invalid dates
+ such as <literal>'2004-04-31'</literal> are converted to
+ <literal>'0000-00-00'</literal> and a warning is
+ generated. With strict mode enabled, invalid dates
+ generate an error. To allow such dates, enable
+ <literal>ALLOW_INVALID_DATES</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>ANSI_QUOTES SQL mode</primary>
+ </indexterm>
+
+ <literal>ANSI_QUOTES</literal>
+ </para>
+
+ <para>
+ Treat ‘<literal>"</literal>’ as an identifier
+ quote character (like the
+ ‘<literal>`</literal>’ quote character) and
+ not as a string quote character. You can still use
+ ‘<literal>`</literal>’ to quote identifiers in
+ ANSI mode. With <literal>ANSI_QUOTES</literal> enabled,
+ you cannot use double quotes to quote a literal string,
+ because it is interpreted as an identifier.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>ERROR_FOR_DIVISION_BY_ZERO SQL mode</primary>
+ </indexterm>
+
+ <literal>ERROR_FOR_DIVISION_BY_ZERO</literal>
+ </para>
+
+ <para>
+ Produce an error in strict mode (otherwise a warning) when
+ we encounter a division by zero (or
+ <literal>MOD(X,0)</literal>) during an
+ <literal>INSERT</literal> or <literal>UPDATE</literal>. If
+ this mode is not given, MySQL instead returns
+ <literal>NULL</literal> for divisions by zero. If used in
+ <literal>INSERT IGNORE</literal> or <literal>UPDATE
+ IGNORE</literal>, MySQL generates a warning for divisions
+ by zero, but the result of the operation is
+ <literal>NULL</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>HIGH_NOT_PRECEDENCE SQL mode</primary>
+ </indexterm>
+
+ <literal>HIGH_NOT_PRECEDENCE</literal>
+ </para>
+
+ <para>
+ The precedence of the <literal>NOT</literal> operator is
+ such that expressions such as <literal>NOT a BETWEEN b AND
+ c</literal> are parsed as <literal>NOT (a BETWEEN b AND
+ c)</literal>. In some older versions of MySQL, the
+ expression was parsed as <literal>(NOT a) BETWEEN b AND
+ c</literal>. The old higher-precedence behavior can be
+ obtained by enabling the
+ <literal>HIGH_NOT_PRECEDENCE</literal> SQL mode.
+ </para>
+
+<programlisting>
+mysql> <userinput>SET sql_mode = '';</userinput>
+mysql> <userinput>SELECT NOT 1 BETWEEN -5 AND 5;</userinput>
+ -> 0
+mysql> <userinput>SET sql_mode = 'broken_not';</userinput>
+mysql> <userinput>SELECT NOT 1 BETWEEN -5 AND 5;</userinput>
+ -> 1
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>IGNORE_SPACE SQL mode</primary>
+ </indexterm>
+
+ <literal>IGNORE_SPACE</literal>
+ </para>
+
+ <para>
+ Allow spaces between a function name and the
+ ‘<literal>(</literal>’ character. This forces
+ all function names to be treated as reserved words. As a
+ result, if you want to access any database, table, or
+ column name that is a reserved word, you must quote it.
+ For example, because there is a <literal>USER()</literal>
+ function, the name of the <literal>user</literal> table in
+ the <literal>mysql</literal> database and the
+ <literal>User</literal> column in that table become
+ reserved, so you must quote them:
+ </para>
+
+<programlisting>
+SELECT "User" FROM mysql."user";
+</programlisting>
+
+ <para>
+ The <literal>IGNORE_SPACE</literal> SQL mode applies to
+ built-in functions, not to stored routines. it is always
+ allowable to have spaces after a routine name, regardless
+ of whether <literal>IGNORE_SPACE</literal> is enabled.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_AUTO_CREATE_USER SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_AUTO_CREATE_USER</literal>
+ </para>
+
+ <para>
+ Prevent <literal>GRANT</literal> from automatically
+ creating new users if it would otherwise do so, unless a
+ non-empty password also is specified.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_AUTO_VALUE_ON_ZERO SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_AUTO_VALUE_ON_ZERO</literal>
+ </para>
+
+ <para>
+ <literal>NO_AUTO_VALUE_ON_ZERO</literal> affects handling
+ of <literal>AUTO_INCREMENT</literal> columns. Normally,
+ you generate the next sequence number for the column by
+ inserting either <literal>NULL</literal> or
+ <literal>0</literal> into it.
+ <literal>NO_AUTO_VALUE_ON_ZERO</literal> suppresses this
+ behavior for <literal>0</literal> so that only
+ <literal>NULL</literal> generates the next sequence
+ number.
+ </para>
+
+ <para>
+ This mode can be useful if <literal>0</literal> has been
+ stored in a table's <literal>AUTO_INCREMENT</literal>
+ column. (Storing <literal>0</literal> is not a recommended
+ practice, by the way.) For example, if you dump the table
+ with <command>mysqldump</command> and then reload it,
+ MySQL normally generates new sequence numbers when it
+ encounters the <literal>0</literal> values, resulting in a
+ table with contents different from the one that was
+ dumped. Enabling <literal>NO_AUTO_VALUE_ON_ZERO</literal>
+ before reloading the dump file solves this problem.
+ <command>mysqldump</command> now automatically includes in
+ its output a statement that enables
+ <literal>NO_AUTO_VALUE_ON_ZERO</literal>, to avoid this
+ problem.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_BACKSLASH_ESCAPES SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_BACKSLASH_ESCAPES</literal>
+ </para>
+
+ <para>
+ Disable the use of the backslash character
+ (‘<literal>\</literal>’) as an escape
+ character within strings. With this mode enabled,
+ backslash becomes any ordinary character like any other.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_DIR_IN_CREATE SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_DIR_IN_CREATE</literal>
+ </para>
+
+ <para>
+ When creating a table, ignore all <literal>INDEX
+ DIRECTORY</literal> and <literal>DATA DIRECTORY</literal>
+ directives. This option is useful on slave replication
+ servers.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>NO_ENGINE_SUBSTITUTION</literal>
+ </para>
+
+ <para>
+ Prevents automatic substitution of storage engine when the
+ requested storage engine is disabled or not compiled in.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_FIELD_OPTIONS SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_FIELD_OPTIONS</literal>
+ </para>
+
+ <para>
+ Don't print MySQL-specific column options in the output of
+ <literal>SHOW CREATE TABLE</literal>. This mode is used by
+ <command>mysqldump</command> in portability mode.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_KEY_OPTIONS SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_KEY_OPTIONS</literal>
+ </para>
+
+ <para>
+ Don't print MySQL-specific index options in the output of
+ <literal>SHOW CREATE TABLE</literal>. This mode is used by
+ <command>mysqldump</command> in portability mode.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_TABLE_OPTIONS SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_TABLE_OPTIONS</literal>
+ </para>
+
+ <para>
+ Don't print MySQL-specific table options (such as
+ <literal>ENGINE</literal>) in the output of <literal>SHOW
+ CREATE TABLE</literal>. This mode is used by
+ <command>mysqldump</command> in portability mode.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_UNSIGNED_SUBTRACTION SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_UNSIGNED_SUBTRACTION</literal>
+ </para>
+
+ <para>
+ In subtraction operations, don't mark the result as
+ <literal>UNSIGNED</literal> if one of the operands is
+ unsigned. Note that this makes <literal>UNSIGNED
+ BIGINT</literal> not 100% usable in all contexts. See
+ <xref linkend="cast-functions"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_ZERO_DATE SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_ZERO_DATE</literal>
+ </para>
+
+ <para>
+ In strict mode, don't allow
+ <literal>'0000-00-00'</literal> as a valid date. You can
+ still insert zero dates with the <literal>IGNORE</literal>
+ option. When not in strict mode, the date is accepted but
+ a warning is generated.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>NO_ZERO_IN_DATE SQL mode</primary>
+ </indexterm>
+
+ <literal>NO_ZERO_IN_DATE</literal>
+ </para>
+
+ <para>
+ In strict mode, don't accept dates where the month or day
+ part is 0. If used with the <literal>IGNORE</literal>
+ option, we insert a <literal>'0000-00-00'</literal> date
+ for any such date. When not in strict mode, the date is
+ accepted but a warning is generated.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>ONLY_FULL_GROUP_BY SQL mode</primary>
+ </indexterm>
+
+ <literal>ONLY_FULL_GROUP_BY</literal>
+ </para>
+
+ <para>
+ Do not allow queries that in the <literal>GROUP
+ BY</literal> part refer to a column that is not selected.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>PIPES_AS_CONCAT SQL mode</primary>
+ </indexterm>
+
+ <literal>PIPES_AS_CONCAT</literal>
+ </para>
+
+ <para>
+ Treat <literal>||</literal> as a string concatenation
+ operator (same as <literal>CONCAT()</literal>) rather than
+ as a synonym for <literal>OR</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>REAL_AS_FLOAT SQL mode</primary>
+ </indexterm>
+
+ <literal>REAL_AS_FLOAT</literal>
+ </para>
+
+ <para>
+ Treat <literal>REAL</literal> as a synonym for
+ <literal>FLOAT</literal> rather than as a synonym for
+ <literal>DOUBLE</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>STRICT_ALL_TABLES SQL mode</primary>
+ </indexterm>
+
+ <literal>STRICT_ALL_TABLES</literal>
+ </para>
+
+ <para>
+ Enable strict mode for all storage engines. Invalid data
+ values are rejected. Additional detail follows.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>STRICT_TRANS_TABLES SQL mode</primary>
+ </indexterm>
+
+ <literal>STRICT_TRANS_TABLES</literal>
+ </para>
+
+ <para>
+ Enable strict mode for transactional storage engines, and
+ when possible for non-transactional storage engines.
+ Additional details follow.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ Strict mode controls how MySQL handles input values that are
+ invalid or missing. A value can be invalid for several
+ reasons. For example, it might have the wrong data type for
+ the column, or it might be out of range. A value is missing
+ when a new row to be inserted does not contain a value for a
+ column that has no explicit <literal>DEFAULT</literal> clause
+ in its definition.
+ </para>
+
+ <para>
+ For transactional tables, an error occurs for invalid or
+ missing values in a statement when either of the
+ <literal>STRICT_ALL_TABLES</literal> or
+ <literal>STRICT_TRANS_TABLES</literal> modes are enabled. The
+ statement is aborted and rolled back.
+ </para>
+
+ <para>
+ For non-transactional tables, the behavior is the same for
+ either mode, if the bad value occurs in the first row to be
+ inserted or updated. The statement is aborted and the table
+ remains unchanged. If the statement inserts or modifies
+ multiple rows and the bad value occurs in the second or later
+ row, the result depends on which strict option is enabled:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ For <literal>STRICT_ALL_TABLES</literal>, MySQL returns an
+ error and ignores the rest of the rows. However, in this
+ case, the earlier rows still have been inserted or
+ updated. This means that you might get a partial update,
+ which might not be what you want. To avoid this, it's best
+ to use single-row statements because these can be aborted
+ without changing the table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ For <literal>STRICT_TRANS_TABLES</literal>, MySQL converts
+ an invalid value to the closest valid value for the column
+ and insert the adjusted value. If a value is missing,
+ MySQL inserts the implicit default value for the column
+ data type. In either case, MySQL generates a warning
+ rather than an error and continues processing the
+ statement. Implicit defaults are described in
+ <xref linkend="create-table"/>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ Strict mode disallows invalid date values such as
+ <literal>'2004-04-31'</literal>. It does not disallow dates
+ with zero parts such as <literal>2004-04-00'</literal> or
+ <quote>zero</quote> dates. To disallow these as well, enable
+ the <literal>NO_ZERO_IN_DATE</literal> and
+ <literal>NO_ZERO_DATE</literal> SQL modes in addition to
+ strict mode.
+ </para>
+
+ <para>
+ If you are not using strict mode (that is, neither
+ <literal>STRICT_TRANS_TABLES</literal> nor
+ <literal>STRICT_ALL_TABLES</literal> is enabled), MySQL
+ inserts adjusted values for invalid or missing values and
+ produces warnings. In strict mode, you can produce this
+ behavior by using <literal>INSERT IGNORE</literal> or
+ <literal>UPDATE IGNORE</literal>. See
+ <xref linkend="show-warnings"/>.
+ </para>
+
+ <para>
+ The following special modes are provided as shorthand for
+ combinations of mode values from the preceding list.
+ </para>
+
+ <para>
+ The descriptions include all mode values that are available in
+ the most recent version of MySQL. For older versions, a
+ combination mode does not include individual mode values that
+ are not available except in newer versions.
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>ANSI SQL mode</primary>
+ </indexterm>
+
+ <literal>ANSI</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>REAL_AS_FLOAT</literal>,
+ <literal>PIPES_AS_CONCAT</literal>,
+ <literal>ANSI_QUOTES</literal>,
+ <literal>IGNORE_SPACE</literal>. See
+ <xref linkend="ansi-mode"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>DB2 SQL mode</primary>
+ </indexterm>
+
+ <literal>DB2</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+ <literal>ANSI_QUOTES</literal>,
+ <literal>IGNORE_SPACE</literal>,
+ <literal>NO_KEY_OPTIONS</literal>,
+ <literal>NO_TABLE_OPTIONS</literal>,
+ <literal>NO_FIELD_OPTIONS</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>MAXDB SQL mode</primary>
+ </indexterm>
+
+ <literal>MAXDB</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+ <literal>ANSI_QUOTES</literal>,
+ <literal>IGNORE_SPACE</literal>,
+ <literal>NO_KEY_OPTIONS</literal>,
+ <literal>NO_TABLE_OPTIONS</literal>,
+ <literal>NO_FIELD_OPTIONS</literal>,
+ <literal>NO_AUTO_CREATE_USER</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>MSSQL SQL mode</primary>
+ </indexterm>
+
+ <literal>MSSQL</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+ <literal>ANSI_QUOTES</literal>,
+ <literal>IGNORE_SPACE</literal>,
+ <literal>NO_KEY_OPTIONS</literal>,
+ <literal>NO_TABLE_OPTIONS</literal>,
+ <literal>NO_FIELD_OPTIONS</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>MYSQL323 SQL mode</primary>
+ </indexterm>
+
+ <literal>MYSQL323</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>NO_FIELD_OPTIONS</literal>,
+ <literal>HIGH_NOT_PRECEDENCE</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>MYSQL40 SQL mode</primary>
+ </indexterm>
+
+ <literal>MYSQL40</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>NO_FIELD_OPTIONS</literal>,
+ <literal>HIGH_NOT_PRECEDENCE</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>ORACLE SQL mode</primary>
+ </indexterm>
+
+ <literal>ORACLE</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+ <literal>ANSI_QUOTES</literal>,
+ <literal>IGNORE_SPACE</literal>,
+ <literal>NO_KEY_OPTIONS</literal>,
+ <literal>NO_TABLE_OPTIONS</literal>,
+ <literal>NO_FIELD_OPTIONS</literal>,
+ <literal>NO_AUTO_CREATE_USER</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>POSTGRESQL SQL mode</primary>
+ </indexterm>
+
+ <literal>POSTGRESQL</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+ <literal>ANSI_QUOTES</literal>,
+ <literal>IGNORE_SPACE</literal>,
+ <literal>NO_KEY_OPTIONS</literal>,
+ <literal>NO_TABLE_OPTIONS</literal>,
+ <literal>NO_FIELD_OPTIONS</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>TRADITIONAL SQL mode</primary>
+ </indexterm>
+
+ <literal>TRADITIONAL</literal>
+ </para>
+
+ <para>
+ Equivalent to <literal>STRICT_TRANS_TABLES</literal>,
+ <literal>STRICT_ALL_TABLES</literal>,
+ <literal>NO_ZERO_IN_DATE</literal>,
+ <literal>NO_ZERO_DATE</literal>,
+ <literal>ERROR_FOR_DIVISION_BY_ZERO</literal>,
+ <literal>NO_AUTO_CREATE_USER</literal>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </refsection>
+
<refsection id="server-shutdown">
<title>&title-server-shutdown;</title>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r1004 - in trunk: . refman-4.1 refman-5.0 refman-5.1 | paul | 23 Jan |
