Author: paul
Date: 2006-01-25 20:09:37 +0100 (Wed, 25 Jan 2006)
New Revision: 1031
Log:
r2509@kite-hub: paul | 2006-01-25 12:39:46 -0600
Three new sections to serve as explicit locations for stuff
formerly buried in larger discussions:
- INSERT ... ON DUPLICATE KEY UPDATE
- Data Type Default Values (in data types chapter, formerly buried
in CREATE TABLE section)
- Concurrent Inserts (right now this is minimal, but it will serve
as a location dedicated to information on this topic; right now,
concurrent insert information is splattered all over the manual)
Next commit will retarget those <xref> elements that need to be changed.
Modified:
trunk/
trunk/refman-4.1/data-types.xml
trunk/refman-4.1/optimization.xml
trunk/refman-4.1/sql-syntax.xml
trunk/refman-5.0/data-types.xml
trunk/refman-5.0/optimization.xml
trunk/refman-5.0/sql-syntax.xml
trunk/refman-5.1/data-types.xml
trunk/refman-5.1/optimization.xml
trunk/refman-5.1/sql-syntax.xml
trunk/refman-common/titles.en.ent
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6663
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2497
+ b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6663
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2509
Modified: trunk/refman-4.1/data-types.xml
===================================================================
--- trunk/refman-4.1/data-types.xml 2006-01-25 04:15:33 UTC (rev 1030)
+++ trunk/refman-4.1/data-types.xml 2006-01-25 19:09:37 UTC (rev 1031)
@@ -1845,6 +1845,116 @@
</section>
+ <section id="data-type-defaults">
+
+ <title>&title-data-type-defaults;</title>
+
+ <indexterm>
+ <primary>default values</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>default values</primary>
+ <secondary>implicit</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>implicit default values</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>default values</primary>
+ <secondary>explicit</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>explicit default values</primary>
+ </indexterm>
+
+ <indexterm type="function">
+ <primary>DEFAULT value clause</primary>
+ </indexterm>
+
+ <para>
+ The <literal>DEFAULT <replaceable>value</replaceable></literal>
+ clause in a data type specification indicates a default value
+ for a column. With one exception, the default value must be a
+ constant; it cannot be a function or an expression. This means,
+ for example, that you cannot set the default for a date column
+ to be the value of a function such as <literal>NOW()</literal>
+ or <literal>CURRENT_DATE</literal>. The exception is that you
+ can specify <literal>CURRENT_TIMESTAMP</literal> as the default
+ for a <literal>TIMESTAMP</literal> column as of MySQL 4.1.2. See
+ <xref linkend="timestamp-4-1"/>.
+ </para>
+
+ <para>
+ If a column definition includes no explicit
+ <literal>DEFAULT</literal> value, MySQL determines the default
+ value as follows:
+ </para>
+
+ <para>
+ If the column can take <literal>NULL</literal> as a value, the
+ column is defined with an explicit <literal>DEFAULT
+ NULL</literal> clause.
+ </para>
+
+ <para>
+ If the column cannot take <literal>NULL</literal> as the value,
+ MySQL defines the column with an explicit
+ <literal>DEFAULT</literal> clause, using the implicit default
+ value for the column data type. Implicit defaults are defined as
+ follows:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ For numeric types other than those declared with the
+ <literal>AUTO_INCREMENT</literal> attribute, the default is
+ <literal>0</literal>. For an
+ <literal>AUTO_INCREMENT</literal> column, the default value
+ is the next value in the sequence.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ For date and time types other than
+ <literal>TIMESTAMP</literal>, the default is the appropriate
+ <quote>zero</quote> value for the type. For the first
+ <literal>TIMESTAMP</literal> column in a table, the default
+ value is the current date and time. See
+ <xref linkend="date-and-time-types"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ For string types other than <literal>ENUM</literal>, the
+ default value is the empty string. For
+ <literal>ENUM</literal>, the default is the first
+ enumeration value.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ <literal>BLOB</literal> and <literal>TEXT</literal> columns
+ cannot be assigned a default value.
+ </para>
+
+ <para>
+ For a given table, you can use the <literal>SHOW CREATE
+ TABLE</literal> statement to see which columns have an explicit
+ <literal>DEFAULT</literal> clause.
+ </para>
+
+ </section>
+
</section>
<section id="numeric-types">
Modified: trunk/refman-4.1/optimization.xml
===================================================================
--- trunk/refman-4.1/optimization.xml 2006-01-25 04:15:33 UTC (rev 1030)
+++ trunk/refman-4.1/optimization.xml 2006-01-25 19:09:37 UTC (rev 1031)
@@ -3719,19 +3719,11 @@
<listitem>
<para>
- <indexterm>
- <primary>concurrent inserts</primary>
- </indexterm>
-
- <indexterm>
- <primary>inserts</primary>
- <secondary>concurrent</secondary>
- </indexterm>
-
For a <literal>MyISAM</literal> table, you can use
concurrent inserts to add rows at the same time that
<literal>SELECT</literal> statements are running if there
- are no deleted rows in middle of the table.
+ are no deleted rows in middle of the table. See
+ <xref linkend="concurrent-inserts"/>.
</para>
</listitem>
@@ -4988,6 +4980,28 @@
</section>
+ <section id="concurrent-inserts">
+
+ <title>&title-concurrent-inserts;</title>
+
+ <indexterm>
+ <primary>concurrent inserts</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>inserts</primary>
+ <secondary>concurrent</secondary>
+ </indexterm>
+
+ <para>
+ For a <literal>MyISAM</literal> table, you can use concurrent
+ inserts to add rows at the same time that
+ <literal>SELECT</literal> statements are running if there are no
+ deleted rows in middle of the table.
+ </para>
+
+ </section>
+
</section>
<section id="optimizing-database-structure">
Modified: trunk/refman-4.1/sql-syntax.xml
===================================================================
--- trunk/refman-4.1/sql-syntax.xml 2006-01-25 04:15:33 UTC (rev 1030)
+++ trunk/refman-4.1/sql-syntax.xml 2006-01-25 19:09:37 UTC (rev 1031)
@@ -1547,15 +1547,6 @@
<primary>default values</primary>
</indexterm>
- <indexterm>
- <primary>default values</primary>
- <secondary>implicit</secondary>
- </indexterm>
-
- <indexterm>
- <primary>implicit default values</primary>
- </indexterm>
-
<indexterm type="function">
<primary>DEFAULT value clause</primary>
</indexterm>
@@ -1575,67 +1566,14 @@
<para>
If a column definition includes no explicit
<literal>DEFAULT</literal> value, MySQL determines the
- default value as follows:
+ default value as described in
+ <xref linkend="data-type-defaults"/>.
</para>
<para>
- If the column can take <literal>NULL</literal> as a value,
- the column is defined with an explicit <literal>DEFAULT
- NULL</literal> clause.
- </para>
-
- <para>
- If the column cannot take <literal>NULL</literal> as the
- value, MySQL defines the column with an explicit
- <literal>DEFAULT</literal> clause, using the implicit
- default value for the column data type. Implicit defaults
- are defined as follows:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- For numeric types other than those declared with the
- <literal>AUTO_INCREMENT</literal> attribute, the default
- is <literal>0</literal>. For an
- <literal>AUTO_INCREMENT</literal> column, the default
- value is the next value in the sequence.
- </para>
- </listitem>
-
- <listitem>
- <para>
- For date and time types other than
- <literal>TIMESTAMP</literal>, the default is the
- appropriate <quote>zero</quote> value for the type. For
- the first <literal>TIMESTAMP</literal> column in a
- table, the default value is the current date and time.
- See <xref linkend="date-and-time-types"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- For string types other than <literal>ENUM</literal>, the
- default value is the empty string. For
- <literal>ENUM</literal>, the default is the first
- enumeration value.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
<literal>BLOB</literal> and <literal>TEXT</literal> columns
cannot be assigned a default value.
</para>
-
- <para>
- For a given table, you can use the <literal>SHOW CREATE
- TABLE</literal> statement to see which columns have an
- explicit <literal>DEFAULT</literal> clause.
- </para>
</listitem>
<listitem>
@@ -4098,94 +4036,20 @@
</para>
</listitem>
+ <listitem>
+ <para>
+ If you specify <literal>ON DUPLICATE KEY UPDATE</literal>,
+ and a row is inserted that would cause a duplicate value in
+ a <literal>UNIQUE</literal> index or <literal>PRIMARY
+ KEY</literal>, an <literal>UPDATE</literal> of the old row
+ is performed. See <xref linkend="insert-on-duplicate"/>.
+ <literal>ON DUPLICATE KEY UPDATE</literal> was added in
+ MySQL 4.1.0.
+ </para>
+ </listitem>
+
</itemizedlist>
- <remark role="todo">
- make ON DUPLICATE stuff a separate subsection?
- </remark>
-
- <para>
- If you specify <literal>ON DUPLICATE KEY UPDATE</literal> (added
- in MySQL 4.1.0), and a row is inserted that would cause a
- duplicate value in a <literal>UNIQUE</literal> index or
- <literal>PRIMARY KEY</literal>, an <literal>UPDATE</literal> of
- the old row is performed. For example, if column
- <literal>a</literal> is declared as <literal>UNIQUE</literal>
- and contains the value <literal>1</literal>, the following two
- statements have identical effect:
- </para>
-
-<programlisting>
-mysql> <userinput>INSERT INTO table (a,b,c) VALUES (1,2,3)</userinput>
- -> <userinput>ON DUPLICATE KEY UPDATE c=c+1;</userinput>
-
-mysql> <userinput>UPDATE table SET c=c+1 WHERE a=1;</userinput>
-</programlisting>
-
- <para>
- The rows-affected value is 1 if the row is inserted as a new row
- and 2 if an existing row is updated.
- </para>
-
- <para>
- <emphasis role="bold">Note</emphasis>: If column
- <literal>b</literal> is also unique, the
- <literal>INSERT</literal> would be equivalent to this
- <literal>UPDATE</literal> statement instead:
- </para>
-
-<programlisting>
-mysql> <userinput>UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;</userinput>
-</programlisting>
-
- <para>
- If <literal>a=1 OR b=2</literal> matches several rows, only
- <emphasis>one</emphasis> row is updated. In general, you should
- try to avoid using an <literal>ON DUPLICATE KEY</literal> clause
- on tables with multiple unique keys.
- </para>
-
- <para>
- As of MySQL 4.1.1, you can use the
- <literal>VALUES(<replaceable>col_name</replaceable>)</literal>
- function in the <literal>UPDATE</literal> clause to refer to
- column values from the <literal>INSERT</literal> portion of the
- <literal>INSERT ... UPDATE</literal> statement. In other words,
- <literal>VALUES(<replaceable>col_name</replaceable>)</literal>
- in the <literal>UPDATE</literal> clause refers to the value of
- <replaceable>col_name</replaceable> that would be inserted, had
- no duplicate-key conflict occurred. This function is especially
- useful in multiple-row inserts. The <literal>VALUES()</literal>
- function is meaningful only in <literal>INSERT ...
- UPDATE</literal> statements and returns <literal>NULL</literal>
- otherwise.
- </para>
-
- <para>
- Example:
- </para>
-
-<programlisting>
-mysql> <userinput>INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)</userinput>
- -> <userinput>ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);</userinput>
-</programlisting>
-
- <para>
- That statement is identical to the following two statements:
- </para>
-
-<programlisting>
-mysql> <userinput>INSERT INTO table (a,b,c) VALUES (1,2,3)</userinput>
- -> <userinput>ON DUPLICATE KEY UPDATE c=3;</userinput>
-mysql> <userinput>INSERT INTO table (a,b,c) VALUES (4,5,6)</userinput>
- -> <userinput>ON DUPLICATE KEY UPDATE c=9;</userinput>
-</programlisting>
-
- <para>
- When you use <literal>ON DUPLICATE KEY UPDATE</literal>, the
- <literal>DELAYED</literal> option is ignored.
- </para>
-
<indexterm type="function">
<primary>LAST_INSERT_ID()</primary>
</indexterm>
@@ -4749,6 +4613,95 @@
</section>
+ <section id="insert-on-duplicate">
+
+ <title>&title-insert-on-duplicate;</title>
+
+ <para>
+ If you specify <literal>ON DUPLICATE KEY UPDATE</literal>
+ (added in MySQL 4.1.0), and a row is inserted that would cause
+ a duplicate value in a <literal>UNIQUE</literal> index or
+ <literal>PRIMARY KEY</literal>, an <literal>UPDATE</literal>
+ of the old row is performed. For example, if column
+ <literal>a</literal> is declared as <literal>UNIQUE</literal>
+ and contains the value <literal>1</literal>, the following two
+ statements have identical effect:
+ </para>
+
+<programlisting>
+mysql> <userinput>INSERT INTO table (a,b,c) VALUES (1,2,3)</userinput>
+ -> <userinput>ON DUPLICATE KEY UPDATE c=c+1;</userinput>
+
+mysql> <userinput>UPDATE table SET c=c+1 WHERE a=1;</userinput>
+</programlisting>
+
+ <para>
+ The rows-affected value is 1 if the row is inserted as a new
+ record and 2 if an existing record is updated.
+ </para>
+
+ <para>
+ <emphasis role="bold">Note</emphasis>: If column
+ <literal>b</literal> is also unique, the
+ <literal>INSERT</literal> would be equivalent to this
+ <literal>UPDATE</literal> statement instead:
+ </para>
+
+<programlisting>
+mysql> <userinput>UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;</userinput>
+</programlisting>
+
+ <para>
+ If <literal>a=1 OR b=2</literal> matches several rows, only
+ <emphasis>one</emphasis> row is updated. In general, you
+ should try to avoid using an <literal>ON DUPLICATE
+ KEY</literal> clause on tables with multiple unique keys.
+ </para>
+
+ <para>
+ As of MySQL 4.1.1, you can use the
+ <literal>VALUES(<replaceable>col_name</replaceable>)</literal>
+ function in the <literal>UPDATE</literal> clause to refer to
+ column values from the <literal>INSERT</literal> portion of
+ the <literal>INSERT ... UPDATE</literal> statement. In other
+ words,
+ <literal>VALUES(<replaceable>col_name</replaceable>)</literal>
+ in the <literal>UPDATE</literal> clause refers to the value of
+ <replaceable>col_name</replaceable> that would be inserted,
+ had no duplicate-key conflict occurred. This function is
+ especially useful in multiple-row inserts. The
+ <literal>VALUES()</literal> function is meaningful only in
+ <literal>INSERT ... UPDATE</literal> statements and returns
+ <literal>NULL</literal> otherwise.
+ </para>
+
+ <para>
+ Example:
+ </para>
+
+<programlisting>
+mysql> <userinput>INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)</userinput>
+ -> <userinput>ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);</userinput>
+</programlisting>
+
+ <para>
+ That statement is identical to the following two statements:
+ </para>
+
+<programlisting>
+mysql> <userinput>INSERT INTO table (a,b,c) VALUES (1,2,3)</userinput>
+ -> <userinput>ON DUPLICATE KEY UPDATE c=3;</userinput>
+mysql> <userinput>INSERT INTO table (a,b,c) VALUES (4,5,6)</userinput>
+ -> <userinput>ON DUPLICATE KEY UPDATE c=9;</userinput>
+</programlisting>
+
+ <para>
+ When you use <literal>ON DUPLICATE KEY UPDATE</literal>, the
+ <literal>DELAYED</literal> option is ignored.
+ </para>
+
+ </section>
+
</section>
<section id="load-data">
Modified: trunk/refman-5.0/data-types.xml
===================================================================
--- trunk/refman-5.0/data-types.xml 2006-01-25 04:15:33 UTC (rev 1030)
+++ trunk/refman-5.0/data-types.xml 2006-01-25 19:09:37 UTC (rev 1031)
@@ -1855,6 +1855,185 @@
</section>
+ <section id="data-type-defaults">
+
+ <title>&title-data-type-defaults;</title>
+
+ <indexterm>
+ <primary>default values</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>default values</primary>
+ <secondary>implicit</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>implicit default values</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>default values</primary>
+ <secondary>explicit</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>explicit default values</primary>
+ </indexterm>
+
+ <indexterm type="function">
+ <primary>DEFAULT value clause</primary>
+ </indexterm>
+
+ <para>
+ The <literal>DEFAULT <replaceable>value</replaceable></literal>
+ clause in a data type specification indicates a default value
+ for a column. With one exception, the default value must be a
+ constant; it cannot be a function or an expression. This means,
+ for example, that you cannot set the default for a date column
+ to be the value of a function such as <literal>NOW()</literal>
+ or <literal>CURRENT_DATE</literal>. The exception is that you
+ can specify <literal>CURRENT_TIMESTAMP</literal> as the default
+ for a <literal>TIMESTAMP</literal> column. See
+ <xref linkend="timestamp-4-1"/>.
+ </para>
+
+ <para>
+ Prior to MySQL 5.0.2, if a column definition includes no
+ explicit <literal>DEFAULT</literal> value, MySQL determines the
+ default value as follows:
+ </para>
+
+ <para>
+ If the column can take <literal>NULL</literal> as a value, the
+ column is defined with an explicit <literal>DEFAULT
+ NULL</literal> clause.
+ </para>
+
+ <para>
+ If the column cannot take <literal>NULL</literal> as the value,
+ MySQL defines the column with an explicit
+ <literal>DEFAULT</literal> clause, using the implicit default
+ value for the column data type. Implicit defaults are defined as
+ follows:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ For numeric types other than those declared with the
+ <literal>AUTO_INCREMENT</literal> attribute, the default is
+ <literal>0</literal>. For an
+ <literal>AUTO_INCREMENT</literal> column, the default value
+ is the next value in the sequence.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ For date and time types other than
+ <literal>TIMESTAMP</literal>, the default is the appropriate
+ <quote>zero</quote> value for the type. For the first
+ <literal>TIMESTAMP</literal> column in a table, the default
+ value is the current date and time. See
+ <xref linkend="date-and-time-types"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ For string types other than <literal>ENUM</literal>, the
+ default value is the empty string. For
+ <literal>ENUM</literal>, the default is the first
+ enumeration value.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ <literal>BLOB</literal> and <literal>TEXT</literal> columns
+ cannot be assigned a default value.
+ </para>
+
+ <para>
+ As of MySQL 5.0.2, if a column definition includes no explicit
+ <literal>DEFAULT</literal> value, MySQL determines the default
+ value as follows:
+ </para>
+
+ <para>
+ If the column can take <literal>NULL</literal> as a value, the
+ column is defined with an explicit <literal>DEFAULT
+ NULL</literal> clause. This is the same as before 5.0.2.
+ </para>
+
+ <para>
+ If the column cannot take <literal>NULL</literal> as the value,
+ MySQL defines the column with no explicit
+ <literal>DEFAULT</literal> clause. For data entry, if an
+ <literal>INSERT</literal> or <literal>REPLACE</literal>
+ statement includes no value for the column, MySQL handles the
+ column according to the SQL mode in effect at the time:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ If strict mode is not enabled, MySQL sets the column to the
+ implicit default value for the column data type.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If strict mode is enabled, an error occurs for transactional
+ tables and the statement is rolled back. For
+ non-transactional tables, an error occurs, but if this
+ happens for the second or subsequent row of a multiple-row
+ statement, the preceding rows will have been inserted.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ Suppose that a table <literal>t</literal> is defined as follows:
+ </para>
+
+<programlisting>
+CREATE TABLE t (i INT NOT NULL);
+</programlisting>
+
+ <para>
+ In this case, <literal>i</literal> has no explicit default, so
+ in strict mode each of the following statements produce an error
+ and no row is inserted. When not using strict mode, only the
+ third statement produces an error; the implicit default is
+ inserted for the first two statements, but the third fails
+ because <literal>DEFAULT(i)</literal> cannot produce a value:
+ </para>
+
+<programlisting>
+INSERT INTO t VALUES();
+INSERT INTO t VALUES(DEFAULT);
+INSERT INTO t VALUES(DEFAULT(i));
+</programlisting>
+
+ <para>
+ See <xref linkend="server-sql-mode"/>.
+ </para>
+
+ <para>
+ For a given table, you can use the <literal>SHOW CREATE
+ TABLE</literal> statement to see which columns have an explicit
+ <literal>DEFAULT</literal> clause.
+ </para>
+
+ </section>
+
</section>
<section id="numeric-types">
Modified: trunk/refman-5.0/optimization.xml
===================================================================
--- trunk/refman-5.0/optimization.xml 2006-01-25 04:15:33 UTC (rev 1030)
+++ trunk/refman-5.0/optimization.xml 2006-01-25 19:09:37 UTC (rev 1031)
@@ -5177,19 +5177,11 @@
<listitem>
<para>
- <indexterm>
- <primary>concurrent inserts</primary>
- </indexterm>
-
- <indexterm>
- <primary>inserts</primary>
- <secondary>concurrent</secondary>
- </indexterm>
-
For a <literal>MyISAM</literal> table, you can use
concurrent inserts to add rows at the same time that
<literal>SELECT</literal> statements are running if there
- are no deleted rows in middle of the table.
+ are no deleted rows in middle of the table. See
+ <xref linkend="concurrent-inserts"/>.
</para>
</listitem>
@@ -6443,6 +6435,28 @@
</section>
+ <section id="concurrent-inserts">
+
+ <title>&title-concurrent-inserts;</title>
+
+ <indexterm>
+ <primary>concurrent inserts</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>inserts</primary>
+ <secondary>concurrent</secondary>
+ </indexterm>
+
+ <para>
+ For a <literal>MyISAM</literal> table, you can use concurrent
+ inserts to add rows at the same time that
+ <literal>SELECT</literal> statements are running if there are no
+ deleted rows in middle of the table.
+ </para>
+
+ </section>
+
</section>
<section id="optimizing-database-structure">
Modified: trunk/refman-5.0/sql-syntax.xml
===================================================================
--- trunk/refman-5.0/sql-syntax.xml 2006-01-25 04:15:33 UTC (rev 1030)
+++ trunk/refman-5.0/sql-syntax.xml 2006-01-25 19:09:37 UTC (rev 1031)
@@ -1591,15 +1591,6 @@
<primary>default values</primary>
</indexterm>
- <indexterm>
- <primary>default values</primary>
- <secondary>implicit</secondary>
- </indexterm>
-
- <indexterm>
- <primary>implicit default values</primary>
- </indexterm>
-
<indexterm type="function">
<primary>DEFAULT value clause</primary>
</indexterm>
@@ -1617,141 +1608,15 @@
</para>
<para>
- Prior to MySQL 5.0.2, if a column definition includes no
- explicit <literal>DEFAULT</literal> value, MySQL determines
- the default value as follows:
+ If a column definition includes no explicit
+ <literal>DEFAULT</literal> value, MySQL determines default
+ value as described in <xref linkend="data-type-defaults"/>.
</para>
<para>
- If the column can take <literal>NULL</literal> as a value,
- the column is defined with an explicit <literal>DEFAULT
- NULL</literal> clause.
- </para>
-
- <para>
- If the column cannot take <literal>NULL</literal> as the
- value, MySQL defines the column with an explicit
- <literal>DEFAULT</literal> clause, using the implicit
- default value for the column data type. Implicit defaults
- are defined as follows:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- For numeric types other than those declared with the
- <literal>AUTO_INCREMENT</literal> attribute, the default
- is <literal>0</literal>. For an
- <literal>AUTO_INCREMENT</literal> column, the default
- value is the next value in the sequence.
- </para>
- </listitem>
-
- <listitem>
- <para>
- For date and time types other than
- <literal>TIMESTAMP</literal>, the default is the
- appropriate <quote>zero</quote> value for the type. For
- the first <literal>TIMESTAMP</literal> column in a
- table, the default value is the current date and time.
- See <xref linkend="date-and-time-types"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- For string types other than <literal>ENUM</literal>, the
- default value is the empty string. For
- <literal>ENUM</literal>, the default is the first
- enumeration value.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
<literal>BLOB</literal> and <literal>TEXT</literal> columns
cannot be assigned a default value.
</para>
-
- <para>
- As of MySQL 5.0.2, if a column definition includes no
- explicit <literal>DEFAULT</literal> value, MySQL determines
- the default value as follows:
- </para>
-
- <para>
- If the column can take <literal>NULL</literal> as a value,
- the column is defined with an explicit <literal>DEFAULT
- NULL</literal> clause. This is the same as before 5.0.2.
- </para>
-
- <para>
- If the column cannot take <literal>NULL</literal> as the
- value, MySQL defines the column with no explicit
- <literal>DEFAULT</literal> clause. For data entry, if an
- <literal>INSERT</literal> or <literal>REPLACE</literal>
- statement includes no value for the column, MySQL handles
- the column according to the SQL mode in effect at the time:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- If strict mode is not enabled, MySQL sets the column to
- the implicit default value for the column data type.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If strict mode is enabled, an error occurs for
- transactional tables and the statement is rolled back.
- For non-transactional tables, an error occurs, but if
- this happens for the second or subsequent row of a
- multiple-row statement, the preceding rows will have
- been inserted.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- Suppose that a table <literal>t</literal> is defined as
- follows:
- </para>
-
-<programlisting>
-CREATE TABLE t (i INT NOT NULL);
-</programlisting>
-
- <para>
- In this case, <literal>i</literal> has no explicit default,
- so in strict mode each of the following statements produce
- an error and no row is inserted. When not using strict mode,
- only the third statement produces an error; the implicit
- default is inserted for the first two statements, but the
- third fails because <literal>DEFAULT(i)</literal> cannot
- produce a value:
- </para>
-
-<programlisting>
-INSERT INTO t VALUES();
-INSERT INTO t VALUES(DEFAULT);
-INSERT INTO t VALUES(DEFAULT(i));
-</programlisting>
-
- <para>
- See <xref linkend="server-sql-mode"/>.
- </para>
-
- <para>
- For a given table, you can use the <literal>SHOW CREATE
- TABLE</literal> statement to see which columns have an
- explicit <literal>DEFAULT</literal> clause.
- </para>
</listitem>
<listitem>
@@ -4131,93 +3996,18 @@
</para>
</listitem>
+ <listitem>
+ <para>
+ If you specify <literal>ON DUPLICATE KEY UPDATE</literal>,
+ and a row is inserted that would cause a duplicate value in
+ a <literal>UNIQUE</literal> index or <literal>PRIMARY
+ KEY</literal>, an <literal>UPDATE</literal> of the old row
+ is performed. See <xref linkend="insert-on-duplicate"/>.
+ </para>
+ </listitem>
+
</itemizedlist>
- <remark role="todo">
- make ON DUPLICATE stuff a separate subsection?
- </remark>
-
- <para>
- If you specify <literal>ON DUPLICATE KEY UPDATE</literal>, and a
- row is inserted that would cause a duplicate value in a
- <literal>UNIQUE</literal> index or <literal>PRIMARY
- KEY</literal>, an <literal>UPDATE</literal> of the old row is
- performed. For example, if column <literal>a</literal> is
- declared as <literal>UNIQUE</literal> and contains the value
- <literal>1</literal>, the following two statements have
- identical effect:
- </para>
-
-<programlisting>
-mysql> <userinput>INSERT INTO table (a,b,c) VALUES (1,2,3)</userinput>
- -> <userinput>ON DUPLICATE KEY UPDATE c=c+1;</userinput>
-
-mysql> <userinput>UPDATE table SET c=c+1 WHERE a=1;</userinput>
-</programlisting>
-
- <para>
- The rows-affected value is 1 if the row is inserted as a new row
- and 2 if an existing row is updated.
- </para>
-
- <para>
- <emphasis role="bold">Note</emphasis>: If column
- <literal>b</literal> is also unique, the
- <literal>INSERT</literal> would be equivalent to this
- <literal>UPDATE</literal> statement instead:
- </para>
-
-<programlisting>
-mysql> <userinput>UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;</userinput>
-</programlisting>
-
- <para>
- If <literal>a=1 OR b=2</literal> matches several rows, only
- <emphasis>one</emphasis> row is updated. In general, you should
- try to avoid using an <literal>ON DUPLICATE KEY</literal> clause
- on tables with multiple unique keys.
- </para>
-
- <para>
- You can use the <literal>VALUES(col_name)</literal> function in
- the <literal>UPDATE</literal> clause to refer to column values
- from the <literal>INSERT</literal> portion of the
- <literal>INSERT ... UPDATE</literal> statement. In other words,
- <literal>VALUES(<replaceable>col_name</replaceable>)</literal>
- in the <literal>UPDATE</literal> clause refers to the value of
- <replaceable>col_name</replaceable> that would be inserted, had
- no duplicate-key conflict occurred. This function is especially
- useful in multiple-row inserts. The <literal>VALUES()</literal>
- function is meaningful only in <literal>INSERT ...
- UPDATE</literal> statements and returns <literal>NULL</literal>
- otherwise.
- </para>
-
- <para>
- Example:
- </para>
-
-<programlisting>
-mysql> <userinput>INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)</userinput>
- -> <userinput>ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);</userinput>
-</programlisting>
-
- <para>
- That statement is identical to the following two statements:
- </para>
-
-<programlisting>
-mysql> <userinput>INSERT INTO table (a,b,c) VALUES (1,2,3)</userinput>
- -> <userinput>ON DUPLICATE KEY UPDATE c=3;</userinput>
-mysql> <userinput>INSERT INTO table (a,b,c) VALUES (4,5,6)</userinput>
- -> <userinput>ON DUPLICATE KEY UPDATE c=9;</userinput>
-</programlisting>
-
- <para>
- When you use <literal>ON DUPLICATE KEY UPDATE</literal>, the
- <literal>DELAYED</literal> option is ignored.
- </para>
-
<indexterm type="function">
<primary>LAST_INSERT_ID()</primary>
</indexterm>
@@ -4768,6 +4558,94 @@
</section>
+ <section id="insert-on-duplicate">
+
+ <title>&title-insert-on-duplicate;</title>
+
+ <para>
+ If you specify <literal>ON DUPLICATE KEY UPDATE</literal>, and
+ a row is inserted that would cause a duplicate value in a
+ <literal>UNIQUE</literal> index or <literal>PRIMARY
+ KEY</literal>, an <literal>UPDATE</literal> of the old row is
+ performed. For example, if column <literal>a</literal> is
+ declared as <literal>UNIQUE</literal> and contains the value
+ <literal>1</literal>, the following two statements have
+ identical effect:
+ </para>
+
+<programlisting>
+mysql> <userinput>INSERT INTO table (a,b,c) VALUES (1,2,3)</userinput>
+ -> <userinput>ON DUPLICATE KEY UPDATE c=c+1;</userinput>
+
+mysql> <userinput>UPDATE table SET c=c+1 WHERE a=1;</userinput>
+</programlisting>
+
+ <para>
+ The rows-affected value is 1 if the row is inserted as a new
+ record and 2 if an existing record is updated.
+ </para>
+
+ <para>
+ <emphasis role="bold">Note</emphasis>: If column
+ <literal>b</literal> is also unique, the
+ <literal>INSERT</literal> would be equivalent to this
+ <literal>UPDATE</literal> statement instead:
+ </para>
+
+<programlisting>
+mysql> <userinput>UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;</userinput>
+</programlisting>
+
+ <para>
+ If <literal>a=1 OR b=2</literal> matches several rows, only
+ <emphasis>one</emphasis> row is updated. In general, you
+ should try to avoid using an <literal>ON DUPLICATE
+ KEY</literal> clause on tables with multiple unique keys.
+ </para>
+
+ <para>
+ You can use the <literal>VALUES(col_name)</literal> function
+ in the <literal>UPDATE</literal> clause to refer to column
+ values from the <literal>INSERT</literal> portion of the
+ <literal>INSERT ... UPDATE</literal> statement. In other
+ words,
+ <literal>VALUES(<replaceable>col_name</replaceable>)</literal>
+ in the <literal>UPDATE</literal> clause refers to the value of
+ <replaceable>col_name</replaceable> that would be inserted,
+ had no duplicate-key conflict occurred. This function is
+ especially useful in multiple-row inserts. The
+ <literal>VALUES()</literal> function is meaningful only in
+ <literal>INSERT ... UPDATE</literal> statements and returns
+ <literal>NULL</literal> otherwise.
+ </para>
+
+ <para>
+ Example:
+ </para>
+
+<programlisting>
+mysql> <userinput>INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)</userinput>
+ -> <userinput>ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);</userinput>
+</programlisting>
+
+ <para>
+ That statement is identical to the following two statements:
+ </para>
+
+<programlisting>
+mysql> <userinput>INSERT INTO table (a,b,c) VALUES (1,2,3)</userinput>
+ -> <userinput>ON DUPLICATE KEY UPDATE c=3;</userinput>
+mysql> <userinput>INSERT INTO table (a,b,c) VALUES (4,5,6)</userinput>
+ -> <userinput>ON DUPLICATE KEY UPDATE c=9;</userinput>
+</programlisting>
+
+ <para>
+ When you use <literal>ON DUPLICATE KEY UPDATE</literal>, the
+ <literal>DELAYED</literal> option is ignored.
+ </para>
+
+ </section>
+
</section>
<section id="load-data">
Modified: trunk/refman-5.1/data-types.xml
===================================================================
--- trunk/refman-5.1/data-types.xml 2006-01-25 04:15:33 UTC (rev 1030)
+++ trunk/refman-5.1/data-types.xml 2006-01-25 19:09:37 UTC (rev 1031)
@@ -1775,6 +1775,132 @@
</section>
+ <section id="data-type-defaults">
+
+ <title>&title-data-type-defaults;</title>
+
+ <indexterm>
+ <primary>default values</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>default values</primary>
+ <secondary>implicit</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>implicit default values</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>default values</primary>
+ <secondary>explicit</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>explicit default values</primary>
+ </indexterm>
+
+ <indexterm type="function">
+ <primary>DEFAULT value clause</primary>
+ </indexterm>
+
+ <para>
+ The <literal>DEFAULT <replaceable>value</replaceable></literal>
+ clause in a data type specification indicates a default value
+ for a column. With one exception, the default value must be a
+ constant; it cannot be a function or an expression. This means,
+ for example, that you cannot set the default for a date column
+ to be the value of a function such as <literal>NOW()</literal>
+ or <literal>CURRENT_DATE</literal>. The exception is that you
+ can specify <literal>CURRENT_TIMESTAMP</literal> as the default
+ for a <literal>TIMESTAMP</literal> column. See
+ <xref linkend="timestamp-4-1"/>.
+ </para>
+
+ <para>
+ <literal>BLOB</literal> and <literal>TEXT</literal> columns
+ cannot be assigned a default value.
+ </para>
+
+ <para>
+ If a column definition includes no explicit
+ <literal>DEFAULT</literal> value, MySQL determines the default
+ value as follows:
+ </para>
+
+ <para>
+ If the column can take <literal>NULL</literal> as a value, the
+ column is defined with an explicit <literal>DEFAULT
+ NULL</literal> clause. (This is the same as in earlier versions
+ of MySQL.)
+ </para>
+
+ <para>
+ If the column cannot take <literal>NULL</literal> as the value,
+ MySQL defines the column with no explicit
+ <literal>DEFAULT</literal> clause. For data entry, if an
+ <literal>INSERT</literal> or <literal>REPLACE</literal>
+ statement includes no value for the column, MySQL handles the
+ column according to the SQL mode in effect at the time:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ If strict mode is not enabled, MySQL sets the column to the
+ implicit default value for the column data type.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If strict mode is enabled, an error occurs for transactional
+ tables and the statement is rolled back. For
+ non-transactional tables, an error occurs, but if this
+ happens for the second or subsequent row of a multiple-row
+ statement, the preceding rows will have been inserted.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ Suppose that a table <literal>t</literal> is defined as follows:
+ </para>
+
+<programlisting>
+CREATE TABLE t (i INT NOT NULL);
+</programlisting>
+
+ <para>
+ In this case, <literal>i</literal> has no explicit default, so
+ in strict mode each of the following statements produce an error
+ and no row is inserted. When not using strict mode, only the
+ third statement produces an error; the implicit default is
+ inserted for the first two statements, but the third fails
+ because <literal>DEFAULT(i)</literal> cannot produce a value:
+ </para>
+
+<programlisting>
+INSERT INTO t VALUES();
+INSERT INTO t VALUES(DEFAULT);
+INSERT INTO t VALUES(DEFAULT(i));
+</programlisting>
+
+ <para>
+ See <xref linkend="server-sql-mode"/>.
+ </para>
+
+ <para>
+ For a given table, you can use the <literal>SHOW CREATE
+ TABLE</literal> statement to see which columns have an explicit
+ <literal>DEFAULT</literal> clause.
+ </para>
+
+ </section>
+
</section>
<section id="numeric-types">
Modified: trunk/refman-5.1/optimization.xml
===================================================================
--- trunk/refman-5.1/optimization.xml 2006-01-25 04:15:33 UTC (rev 1030)
+++ trunk/refman-5.1/optimization.xml 2006-01-25 19:09:37 UTC (rev 1031)
@@ -5186,19 +5186,11 @@
<listitem>
<para>
- <indexterm>
- <primary>concurrent inserts</primary>
- </indexterm>
-
- <indexterm>
- <primary>inserts</primary>
- <secondary>concurrent</secondary>
- </indexterm>
-
For a <literal>MyISAM</literal> table, you can use
concurrent inserts to add rows at the same time that
<literal>SELECT</literal> statements are running if there
- are no deleted rows in middle of the table.
+ are no deleted rows in middle of the table. See
+ <xref linkend="concurrent-inserts"/>.
</para>
</listitem>
@@ -6451,6 +6443,28 @@
</section>
+ <section id="concurrent-inserts">
+
+ <title>&title-concurrent-inserts;</title>
+
+ <indexterm>
+ <primary>concurrent inserts</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>inserts</primary>
+ <secondary>concurrent</secondary>
+ </indexterm>
+
+ <para>
+ For a <literal>MyISAM</literal> table, you can use concurrent
+ inserts to add rows at the same time that
+ <literal>SELECT</literal> statements are running if there are no
+ deleted rows in middle of the table.
+ </para>
+
+ </section>
+
</section>
<section id="optimizing-database-structure">
Modified: trunk/refman-5.1/sql-syntax.xml
===================================================================
--- trunk/refman-5.1/sql-syntax.xml 2006-01-25 04:15:33 UTC (rev 1030)
+++ trunk/refman-5.1/sql-syntax.xml 2006-01-25 19:09:37 UTC (rev 1031)
@@ -1847,15 +1847,6 @@
<primary>default values</primary>
</indexterm>
- <indexterm>
- <primary>default values</primary>
- <secondary>implicit</secondary>
- </indexterm>
-
- <indexterm>
- <primary>implicit default values</primary>
- </indexterm>
-
<indexterm type="function">
<primary>DEFAULT value clause</primary>
</indexterm>
@@ -1873,88 +1864,16 @@
</para>
<para>
- <literal>BLOB</literal> and <literal>TEXT</literal> columns
- cannot be assigned a default value.
- </para>
-
- <para>
If a column definition includes no explicit
<literal>DEFAULT</literal> value, MySQL determines the
- default value as follows:
+ default value as described in
+ <xref linkend="data-type-defaults"/>.
</para>
<para>
- If the column can take <literal>NULL</literal> as a value,
- the column is defined with an explicit <literal>DEFAULT
- NULL</literal> clause. (This is the same as in earlier
- versions of MySQL.)
+ <literal>BLOB</literal> and <literal>TEXT</literal> columns
+ cannot be assigned a default value.
</para>
-
- <para>
- If the column cannot take <literal>NULL</literal> as the
- value, MySQL defines the column with no explicit
- <literal>DEFAULT</literal> clause. For data entry, if an
- <literal>INSERT</literal> or <literal>REPLACE</literal>
- statement includes no value for the column, MySQL handles
- the column according to the SQL mode in effect at the time:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- If strict mode is not enabled, MySQL sets the column to
- the implicit default value for the column data type.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If strict mode is enabled, an error occurs for
- transactional tables and the statement is rolled back.
- For non-transactional tables, an error occurs, but if
- this happens for the second or subsequent row of a
- multiple-row statement, the preceding rows will have
- been inserted.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- Suppose that a table <literal>t</literal> is defined as
- follows:
- </para>
-
-<programlisting>
-CREATE TABLE t (i INT NOT NULL);
-</programlisting>
-
- <para>
- In this case, <literal>i</literal> has no explicit default,
- so in strict mode each of the following statements produce
- an error and no row is inserted. When not using strict mode,
- only the third statement produces an error; the implicit
- default is inserted for the first two statements, but the
- third fails because <literal>DEFAULT(i)</literal> cannot
- produce a value:
- </para>
-
-<programlisting>
-INSERT INTO t VALUES();
-INSERT INTO t VALUES(DEFAULT);
-INSERT INTO t VALUES(DEFAULT(i));
-</programlisting>
-
- <para>
- See <xref linkend="server-sql-mode"/>.
- </para>
-
- <para>
- For a given table, you can use the <literal>SHOW CREATE
- TABLE</literal> statement to see which columns have an
- explicit <literal>DEFAULT</literal> clause.
- </para>
</listitem>
<listitem>
@@ -4675,93 +4594,18 @@
</para>
</listitem>
+ <listitem>
+ <para>
+ If you specify <literal>ON DUPLICATE KEY UPDATE</literal>,
+ and a row is inserted that would cause a duplicate value in
+ a <literal>UNIQUE</literal> index or <literal>PRIMARY
+ KEY</literal>, an <literal>UPDATE</literal> of the old row
+ is performed. See <xref linkend="insert-on-duplicate"/>.
+ </para>
+ </listitem>
+
</itemizedlist>
- <remark role="todo">
- make ON DUPLICATE stuff a separate subsection?
- </remark>
-
- <para>
- If you specify <literal>ON DUPLICATE KEY UPDATE</literal>, and a
- row is inserted that would cause a duplicate value in a
- <literal>UNIQUE</literal> index or <literal>PRIMARY
- KEY</literal>, an <literal>UPDATE</literal> of the old row is
- performed. For example, if column <literal>a</literal> is
- declared as <literal>UNIQUE</literal> and contains the value
- <literal>1</literal>, the following two statements have
- identical effect:
- </para>
-
-<programlisting>
-mysql> <userinput>INSERT INTO table (a,b,c) VALUES (1,2,3)</userinput>
- -> <userinput>ON DUPLICATE KEY UPDATE c=c+1;</userinput>
-
-mysql> <userinput>UPDATE table SET c=c+1 WHERE a=1;</userinput>
-</programlisting>
-
- <para>
- The rows-affected value is 1 if the row is inserted as a new row
- and 2 if an existing row is updated.
- </para>
-
- <para>
- <emphasis role="bold">Note</emphasis>: If column
- <literal>b</literal> is also unique, the
- <literal>INSERT</literal> would be equivalent to this
- <literal>UPDATE</literal> statement instead:
- </para>
-
-<programlisting>
-mysql> <userinput>UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;</userinput>
-</programlisting>
-
- <para>
- If <literal>a=1 OR b=2</literal> matches several rows, only
- <emphasis>one</emphasis> row is updated. In general, you should
- try to avoid using an <literal>ON DUPLICATE KEY</literal> clause
- on tables with multiple unique keys.
- </para>
-
- <para>
- You can use the <literal>VALUES(col_name)</literal> function in
- the <literal>UPDATE</literal> clause to refer to column values
- from the <literal>INSERT</literal> portion of the
- <literal>INSERT ... UPDATE</literal> statement. In other words,
- <literal>VALUES(<replaceable>col_name</replaceable>)</literal>
- in the <literal>UPDATE</literal> clause refers to the value of
- <replaceable>col_name</replaceable> that would be inserted, had
- no duplicate-key conflict occurred. This function is especially
- useful in multiple-row inserts. The <literal>VALUES()</literal>
- function is meaningful only in <literal>INSERT ...
- UPDATE</literal> statements and returns <literal>NULL</literal>
- otherwise.
- </para>
-
- <para>
- Example:
- </para>
-
-<programlisting>
-mysql> <userinput>INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)</userinput>
- -> <userinput>ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);</userinput>
-</programlisting>
-
- <para>
- That statement is identical to the following two statements:
- </para>
-
-<programlisting>
-mysql> <userinput>INSERT INTO table (a,b,c) VALUES (1,2,3)</userinput>
- -> <userinput>ON DUPLICATE KEY UPDATE c=3;</userinput>
-mysql> <userinput>INSERT INTO table (a,b,c) VALUES (4,5,6)</userinput>
- -> <userinput>ON DUPLICATE KEY UPDATE c=9;</userinput>
-</programlisting>
-
- <para>
- When you use <literal>ON DUPLICATE KEY UPDATE</literal>, the
- <literal>DELAYED</literal> option is ignored.
- </para>
-
<indexterm type="function">
<primary>LAST_INSERT_ID()</primary>
</indexterm>
@@ -5312,6 +5156,94 @@
</section>
+ <section id="insert-on-duplicate">
+
+ <title>&title-insert-on-duplicate;</title>
+
+ <para>
+ If you specify <literal>ON DUPLICATE KEY UPDATE</literal>, and
+ a row is inserted that would cause a duplicate value in a
+ <literal>UNIQUE</literal> index or <literal>PRIMARY
+ KEY</literal>, an <literal>UPDATE</literal> of the old row is
+ performed. For example, if column <literal>a</literal> is
+ declared as <literal>UNIQUE</literal> and contains the value
+ <literal>1</literal>, the following two statements have
+ identical effect:
+ </para>
+
+<programlisting>
+mysql> <userinput>INSERT INTO table (a,b,c) VALUES (1,2,3)</userinput>
+ -> <userinput>ON DUPLICATE KEY UPDATE c=c+1;</userinput>
+
+mysql> <userinput>UPDATE table SET c=c+1 WHERE a=1;</userinput>
+</programlisting>
+
+ <para>
+ The rows-affected value is 1 if the row is inserted as a new
+ record and 2 if an existing record is updated.
+ </para>
+
+ <para>
+ <emphasis role="bold">Note</emphasis>: If column
+ <literal>b</literal> is also unique, the
+ <literal>INSERT</literal> would be equivalent to this
+ <literal>UPDATE</literal> statement instead:
+ </para>
+
+<programlisting>
+mysql> <userinput>UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;</userinput>
+</programlisting>
+
+ <para>
+ If <literal>a=1 OR b=2</literal> matches several rows, only
+ <emphasis>one</emphasis> row is updated. In general, you
+ should try to avoid using an <literal>ON DUPLICATE
+ KEY</literal> clause on tables with multiple unique keys.
+ </para>
+
+ <para>
+ You can use the <literal>VALUES(col_name)</literal> function
+ in the <literal>UPDATE</literal> clause to refer to column
+ values from the <literal>INSERT</literal> portion of the
+ <literal>INSERT ... UPDATE</literal> statement. In other
+ words,
+ <literal>VALUES(<replaceable>col_name</replaceable>)</literal>
+ in the <literal>UPDATE</literal> clause refers to the value of
+ <replaceable>col_name</replaceable> that would be inserted,
+ had no duplicate-key conflict occurred. This function is
+ especially useful in multiple-row inserts. The
+ <literal>VALUES()</literal> function is meaningful only in
+ <literal>INSERT ... UPDATE</literal> statements and returns
+ <literal>NULL</literal> otherwise.
+ </para>
+
+ <para>
+ Example:
+ </para>
+
+<programlisting>
+mysql> <userinput>INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)</userinput>
+ -> <userinput>ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);</userinput>
+</programlisting>
+
+ <para>
+ That statement is identical to the following two statements:
+ </para>
+
+<programlisting>
+mysql> <userinput>INSERT INTO table (a,b,c) VALUES (1,2,3)</userinput>
+ -> <userinput>ON DUPLICATE KEY UPDATE c=3;</userinput>
+mysql> <userinput>INSERT INTO table (a,b,c) VALUES (4,5,6)</userinput>
+ -> <userinput>ON DUPLICATE KEY UPDATE c=9;</userinput>
+</programlisting>
+
+ <para>
+ When you use <literal>ON DUPLICATE KEY UPDATE</literal>, the
+ <literal>DELAYED</literal> option is ignored.
+ </para>
+
+ </section>
+
</section>
<section id="load-data">
Modified: trunk/refman-common/titles.en.ent
===================================================================
--- trunk/refman-common/titles.en.ent 2006-01-25 04:15:33 UTC (rev 1030)
+++ trunk/refman-common/titles.en.ent 2006-01-25 19:09:37 UTC (rev 1031)
@@ -71,7 +71,7 @@
<!ENTITY title-bdb-start "<literal>BDB</literal> Startup Options">
<!ENTITY title-bdb-storage-engine "The <literal>BDB</literal> (<literal>BerkeleyDB</literal>) Storage Engine">
<!ENTITY title-bdb-todo "Things We Need to Fix for <literal>BDB</literal>">
-<!ENTITY title-begin-end "<literal>BEGIN … END</literal> Compound Statement Syntax">
+<!ENTITY title-begin-end "<literal>BEGIN ... END</literal> Compound Statement Syntax">
<!ENTITY title-beos "BeOS Notes">
<!ENTITY title-binary-log "The Binary Log">
<!ENTITY title-binary-notes-linux "Linux Binary Distribution Notes">
@@ -237,6 +237,7 @@
<!ENTITY title-compile-and-link-options "How Compiling and Linking Affects the Speed of MySQL">
<!ENTITY title-compiling-for-debugging "Compiling MySQL for Debugging">
<!ENTITY title-compressed-format "Compressed Table Characteristics">
+<!ENTITY title-concurrent-inserts "Concurrent Inserts">
<!ENTITY title-conditions-and-handlers "Conditions and Handlers">
<!ENTITY title-configure-options "Typical <command>configure</command> Options">
<!ENTITY title-connecting "Connecting to the MySQL Server">
@@ -288,6 +289,7 @@
<!ENTITY title-data-size "Make Your Data as Small as Possible">
<!ENTITY title-data-source-name "What is a Data Source Name?">
<!ENTITY title-data-type-overview "Data Type Overview">
+<!ENTITY title-data-type-defaults "Data Type Default Values">
<!ENTITY title-data-types "Data Types">
<!ENTITY title-database-administration "Database Administration">
<!ENTITY title-database-administration-statements "Database Administration Statements">
@@ -484,7 +486,7 @@
<!ENTITY title-innodb-init "Creating the <literal>InnoDB</literal> Tablespace">
<!ENTITY title-innodb-insert-buffering "Insert Buffering">
<!ENTITY title-innodb-lock-modes "<literal>InnoDB</literal> Lock Modes">
-<!ENTITY title-innodb-locking-reads "<literal>SELECT … FOR UPDATE</literal> and <literal>SELECT … LOCK IN SHARE MODE</literal> Locking Reads">
+<!ENTITY title-innodb-locking-reads "<literal>SELECT ... FOR UPDATE</literal> and <literal>SELECT ... LOCK IN SHARE MODE</literal> Locking Reads">
<!ENTITY title-innodb-locks-set "Locks Set by Different SQL Statements in <literal>InnoDB</literal>">
<!ENTITY title-innodb-monitor "<literal>SHOW ENGINE INNODB STATUS</literal> and the <literal>InnoDB</literal> Monitors">
<!ENTITY title-innodb-multi-versioning "Implementation of Multi-Versioning">
@@ -551,7 +553,8 @@
<!ENTITY title-innodb-tuning "<literal>InnoDB</literal> Performance Tuning Tips">
<!ENTITY title-insert "<literal>INSERT</literal> Syntax">
<!ENTITY title-insert-delayed "<literal>INSERT DELAYED</literal> Syntax">
-<!ENTITY title-insert-select "<literal>INSERT … SELECT</literal> Syntax">
+<!ENTITY title-insert-on-duplicate "<literal>INSERT ... ON DUPLICATE KEY UPDATE</literal> Syntax">
+<!ENTITY title-insert-select "<literal>INSERT ... SELECT</literal> Syntax">
<!ENTITY title-insert-speed "Speed of <literal>INSERT</literal> Statements">
<!ENTITY title-install-plugin "<literal>INSTALL PLUGIN</literal> Syntax">
<!ENTITY title-installation-issues "Installation-Related Issues">
@@ -698,13 +701,13 @@
<!ENTITY title-myisam-table-close "Problems from Tables Not Being Closed Properly">
<!ENTITY title-myisam-table-formats "<literal>MyISAM</literal> Table Storage Formats">
<!ENTITY title-myisam-table-problems "<literal>MyISAM</literal> Table Problems">
-<!ENTITY title-myisamchk-check-options "Check Options for <command>myisamchk</command>">
+<!ENTITY title-myisamchk-check-options "<command>myisamchk</command> Check Options">
<!ENTITY title-myisamchk-for-manpage "<literal>MyISAM</literal> table-maintenance utility">
<!ENTITY title-myisamchk-for-manual "<literal>MyISAM</literal> Table-Maintenance Utility">
-<!ENTITY title-myisamchk-general-options "General Options for <command>myisamchk</command>">
+<!ENTITY title-myisamchk-general-options "<command>myisamchk</command> General Options">
<!ENTITY title-myisamchk-memory "<command>myisamchk</command> Memory Usage">
-<!ENTITY title-myisamchk-other-options "Other Options for <command>myisamchk</command>">
-<!ENTITY title-myisamchk-repair-options "Repair Options for <command>myisamchk</command>">
+<!ENTITY title-myisamchk-other-options "Other <literal>myisamchk</literal> Options">
+<!ENTITY title-myisamchk-repair-options "<command>myisamchk</command> Repair Options">
<!ENTITY title-myisamlog-for-manpage "display <literal>MyISAM</literal> log file contents">
<!ENTITY title-myisamlog-for-manual "Display <literal>MyISAM</literal> Log File Contents">
<!ENTITY title-myisampack-for-manpage "generate compressed, read-only <literal>MyISAM</literal> tables">
@@ -1480,7 +1483,7 @@
<!ENTITY title-security-against-attack "Making MySQL Secure Against Attackers">
<!ENTITY title-security-guidelines "General Security Guidelines">
<!ENTITY title-select "<literal>SELECT</literal> Syntax">
-<!ENTITY title-select-into-statement "<literal>SELECT … INTO</literal> Statement">
+<!ENTITY title-select-into-statement "<literal>SELECT ... INTO</literal> Statement">
<!ENTITY title-select-speed "Speed of <literal>SELECT</literal> Queries">
<!ENTITY title-selecting-all "Selecting All Data">
<!ENTITY title-selecting-columns "Selecting Particular Columns">
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r1031 - in trunk: . refman-4.1 refman-5.0 refman-5.1 refman-common | paul | 25 Jan |
