Author: paul
Date: 2006-01-27 17:18:29 +0100 (Fri, 27 Jan 2006)
New Revision: 1063
Log:
r6748@frost: paul | 2006-01-27 10:18:13 -0600
General revisions.
Modified:
trunk/
trunk/refman-4.1/functions.xml
trunk/refman-4.1/sql-syntax.xml
trunk/refman-5.0/functions.xml
trunk/refman-5.0/sql-syntax.xml
trunk/refman-5.1/functions.xml
trunk/refman-5.1/sql-syntax.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6744
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2564
+ b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6748
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2564
Modified: trunk/refman-4.1/functions.xml
===================================================================
--- trunk/refman-4.1/functions.xml 2006-01-27 15:41:40 UTC (rev 1062)
+++ trunk/refman-4.1/functions.xml 2006-01-27 16:18:29 UTC (rev 1063)
@@ -2514,8 +2514,12 @@
</listitem>
<listitem>
- <remark role="help-topic" condition="INSERT"/>
+ <remark role="help-topic" condition="INSERT function"/>
+ <remark role="help-keywords">
+ INSERT
+ </remark>
+
<remark role="help-syntax-begin"/>
<para>
Modified: trunk/refman-4.1/sql-syntax.xml
===================================================================
--- trunk/refman-4.1/sql-syntax.xml 2006-01-27 15:41:40 UTC (rev 1062)
+++ trunk/refman-4.1/sql-syntax.xml 2006-01-27 16:18:29 UTC (rev 1063)
@@ -3812,11 +3812,11 @@
<primary>ON DUPLICATE KEY</primary>
</indexterm>
- <indexterm type="function">
+ <indexterm>
<primary>INSERT</primary>
</indexterm>
- <remark role="help-topic" condition="INSERT INTO"/>
+ <remark role="help-topic" condition="INSERT"/>
<remark role="help-keywords">
INSERT INTO LOW_PRIORITY HIGH_PRIORITY IGNORE DUPLICATE KEY
@@ -3874,6 +3874,16 @@
<remark role="help-description-end"/>
<para>
+ You can use <literal>REPLACE</literal> instead of
+ <literal>INSERT</literal> to overwrite old rows.
+ <literal>REPLACE</literal> is the counterpart to <literal>INSERT
+ IGNORE</literal> in the treatment of new rows that contain
+ unique key values that duplicate old rows: The new rows are used
+ to replace the old rows rather than being discarded. See
+ <xref linkend="replace"/>.
+ </para>
+
+ <para>
<replaceable>tbl_name</replaceable> is the table into which rows
should be inserted. The columns for which the statement provides
values can be specified as follows:
@@ -3883,23 +3893,33 @@
<listitem>
<para>
- The column name list or the <literal>SET</literal> clause
- indicates the columns explicitly.
+ You can provide a comma-separated list of column names
+ following the table name. In this case, a value for each
+ named column must be provided by the
+ <literal>VALUES</literal> list or the
+ <literal>SELECT</literal> statement.
</para>
</listitem>
<listitem>
<para>
- If you do not specify the column list for <literal>INSERT
- ... VALUES</literal> or <literal>INSERT ...
+ If you do not specify a list of column names for
+ <literal>INSERT ... VALUES</literal> or <literal>INSERT ...
SELECT</literal>, values for every column in the table must
- be provided in the <literal>VALUES</literal> list or by the
- <literal>SELECT</literal>. If you do not know the order of
- the columns in the table, use <literal>DESCRIBE
+ be provided by the <literal>VALUES</literal> list or the
+ <literal>SELECT</literal> statement. If you do not know the
+ order of the columns in the table, use <literal>DESCRIBE
<replaceable>tbl_name</replaceable></literal> to find out.
</para>
</listitem>
+ <listitem>
+ <para>
+ The <literal>SET</literal> clause indicates the column names
+ explicitly.
+ </para>
+ </listitem>
+
</itemizedlist>
<para>
@@ -3941,8 +3961,8 @@
<para>
As of MySQL 4.1.0, you can use
<literal>DEFAULT(<replaceable>col_name</replaceable>)</literal>
- as a more general form that can be used in expressions for
- producing a column's default value.
+ as a more general form that can be used in expressions to
+ produce a given column's default value.
</para>
</listitem>
@@ -3954,7 +3974,7 @@
</para>
<programlisting>
-mysql> <userinput>INSERT INTO <replaceable>tbl_name</replaceable> () VALUES();</userinput>
+INSERT INTO <replaceable>tbl_name</replaceable> () VALUES();
</programlisting>
</listitem>
@@ -3991,7 +4011,7 @@
</para>
<programlisting>
-mysql> <userinput>INSERT INTO <replaceable>tbl_name</replaceable> (<replaceable>col1</replaceable>,<replaceable>col2</replaceable>) VALUES(15,<replaceable>col1</replaceable>*2);</userinput>
+INSERT INTO <replaceable>tbl_name</replaceable> (col1,col2) VALUES(15,col1*2);
</programlisting>
<para>
@@ -4001,7 +4021,7 @@
</para>
<programlisting>
-mysql> <userinput>INSERT INTO <replaceable>tbl_name</replaceable> (<replaceable>col1</replaceable>,<replaceable>col2</replaceable>) VALUES(<replaceable>col2</replaceable>*2,15);</userinput>
+INSERT INTO <replaceable>tbl_name</replaceable> (col1,col2) VALUES(col2*2,15);
</programlisting>
<para>
@@ -4017,6 +4037,48 @@
</itemizedlist>
<para>
+ <literal>INSERT</literal> statements that use
+ <literal>VALUES</literal> syntax can insert multiple rows. To do
+ this, include multiple lists of column values, each enclosed
+ within parentheses and separated by commas. Example:
+ </para>
+
+<programlisting>
+INSERT INTO <replaceable>tbl_name</replaceable> (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
+</programlisting>
+
+ <para>
+ The values list for each row must be enclosed within
+ parentheses. The following statement is illegal because the
+ number of values in the list does not match the number of column
+ names:
+ </para>
+
+<programlisting>
+INSERT INTO <replaceable>tbl_name</replaceable> (a,b,c) VALUES(1,2,3,4,5,6,7,8,9);
+</programlisting>
+
+ <para>
+ The rows-affected value for an <literal>INSERT</literal> can be
+ obtained using the <literal>mysql_affected_rows()</literal> C
+ API function. See <xref linkend="mysql-affected-rows"/>.
+ </para>
+
+ <para>
+ If <literal>INSERT</literal> inserts a row into a table that has
+ an <literal>AUTO_INCREMENT</literal> column, you can find the
+ value used for that column by using the SQL
+ <literal>LAST_INSERT_ID()</literal> function. From within the C
+ API, use the <literal>mysql_insert_id()</literal> function.
+ However, you should note that the two functions do not always
+ behave identically. The behavior of <literal>INSERT</literal>
+ statements with respect to <literal>AUTO_INCREMENT</literal>
+ columns is discussed further in
+ <xref linkend="information-functions"/>, and
+ <xref linkend="mysql-insert-id"/>.
+ </para>
+
+ <para>
The <literal>INSERT</literal> statement supports the following
modifiers:
</para>
@@ -4028,14 +4090,21 @@
If you use the <literal>DELAYED</literal> keyword, the
server puts the row or rows to be inserted into a buffer,
and the client issuing the <literal>INSERT DELAYED</literal>
- statement can then continue. If the table is in use, the
- server holds the rows. When the table is free, the server
- begins inserting rows, checking periodically to see whether
- there are any new read requests for the table. If there are,
- the delayed row queue is suspended until the table becomes
- free again. See <xref linkend="insert-delayed"/>.
- <literal>DELAYED</literal> was added in MySQL 3.22.5.
+ statement can then continue immediately. If the table is in
+ use, the server holds the rows. When the table is free, the
+ server begins inserting rows, checking periodically to see
+ whether there are any new read requests for the table. If
+ there are, the delayed row queue is suspended until the
+ table becomes free again. See
+ <xref linkend="insert-delayed"/>. <literal>DELAYED</literal>
+ was added in MySQL 3.22.5.
</para>
+
+ <para>
+ <literal>DELAYED</literal> is ignored with <literal>INSERT
+ ... SELECT</literal> or <literal>INSERT ... ON DUPLICATE KEY
+ UPDATE</literal>.
+ </para>
</listitem>
<listitem>
@@ -4050,8 +4119,7 @@
LOW_PRIORITY</literal> statement to wait for a very long
time (or even forever) in a read-heavy environment. (This is
in contrast to <literal>INSERT DELAYED</literal>, which lets
- the client continue at once. See
- <xref linkend="insert-delayed"/>.) Note that
+ the client continue at once. Note that
<literal>LOW_PRIORITY</literal> should normally not be used
with <literal>MyISAM</literal> tables because doing so
disables concurrent inserts. See
@@ -4073,23 +4141,14 @@
<listitem>
<para>
- The rows-affected value for an <literal>INSERT</literal> can
- be obtained using the
- <literal>mysql_affected_rows()</literal> C API function. See
- <xref linkend="mysql-affected-rows"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If you use the <literal>IGNORE</literal> keyword in an
- <literal>INSERT</literal> statement, errors that occur while
- executing the statement are treated as warnings instead. For
- example, without <literal>IGNORE</literal>, a row that
- duplicates an existing <literal>UNIQUE</literal> index or
- <literal>PRIMARY KEY</literal> value in the table causes a
- duplicate-key error and the statement is aborted. With
- <literal>IGNORE</literal>, the row is still not inserted,
+ If you use the <literal>IGNORE</literal> keyword, errors
+ that occur while executing the <literal>INSERT</literal>
+ statement are treated as warnings instead. For example,
+ without <literal>IGNORE</literal>, a row that duplicates an
+ existing <literal>UNIQUE</literal> index or <literal>PRIMARY
+ KEY</literal> value in the table causes a duplicate-key
+ error and the statement is aborted. With
+ <literal>IGNORE</literal>, the row still is not inserted,
but no error is issued. Data conversions that would trigger
errors abort the statement if <literal>IGNORE</literal> is
not specified. With <literal>IGNORE</literal>, invalid
@@ -4124,19 +4183,6 @@
</indexterm>
<para>
- You can find the value used for an
- <literal>AUTO_INCREMENT</literal> column by using the SQL
- <literal>LAST_INSERT_ID()</literal> function. From within the C
- API, use the <literal>mysql_insert_id()</literal> function.
- However, you should note that the two functions do not always
- behave identically. The behavior of <literal>INSERT</literal>
- statements with respect to <literal>AUTO_INCREMENT</literal>
- columns is discussed further in
- <xref linkend="information-functions"/>, and
- <xref linkend="mysql-insert-id"/>.
- </para>
-
- <para>
If you use an <literal>INSERT ... VALUES</literal> statement
with multiple value lists or <literal>INSERT ...
SELECT</literal>, the statement returns an information string in
@@ -4162,6 +4208,10 @@
<itemizedlist>
<listitem>
+ <remark role="todo">
+ [pd] Differs in strict mode?
+ </remark>
+
<para>
Inserting <literal>NULL</literal> into a column that has
been declared <literal>NOT NULL</literal>. For multiple-row
@@ -4230,18 +4280,14 @@
function. See <xref linkend="mysql-info"/>.
</para>
- <indexterm type="function">
- <primary>REPLACE ... SELECT</primary>
- </indexterm>
-
- <indexterm type="function">
- <primary>INSERT ... SELECT</primary>
- </indexterm>
-
<section id="insert-select">
<title>&title-insert-select;</title>
+ <indexterm>
+ <primary>INSERT ... SELECT</primary>
+ </indexterm>
+
<remark role="help-topic" condition="INSERT SELECT"/>
<remark role="help-syntax"/>
@@ -4346,16 +4392,6 @@
must qualify non-unique column names in the values part.
</para>
- <para>
- You can use <literal>REPLACE</literal> instead of
- <literal>INSERT</literal> to overwrite old rows.
- <literal>REPLACE</literal> is the counterpart to
- <literal>INSERT IGNORE</literal> in the treatment of new rows
- that contain unique key values that duplicate old rows: The
- new rows are used to replace the old rows rather than being
- discarded.
- </para>
-
</section>
<section id="insert-delayed">
@@ -4694,10 +4730,10 @@
</para>
<programlisting>
-mysql> <userinput>INSERT INTO table (a,b,c) VALUES (1,2,3)</userinput>
- -> <userinput>ON DUPLICATE KEY UPDATE c=c+1;</userinput>
+INSERT INTO table (a,b,c) VALUES (1,2,3)
+ ON DUPLICATE KEY UPDATE c=c+1;
-mysql> <userinput>UPDATE table SET c=c+1 WHERE a=1;</userinput>
+UPDATE table SET c=c+1 WHERE a=1;
</programlisting>
<para>
@@ -4706,21 +4742,20 @@
</para>
<para>
- <emphasis role="bold">Note</emphasis>: If column
- <literal>b</literal> is also unique, the
- <literal>INSERT</literal> would be equivalent to this
+ If column <literal>b</literal> is also unique, the
+ <literal>INSERT</literal> is 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>
+UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
</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.
+ KEY</literal> clause on tables with multiple unique indexes.
</para>
<para>
@@ -4737,16 +4772,12 @@
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.
+ <literal>NULL</literal> otherwise. Example:
</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>
+INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
+ ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
</programlisting>
<para>
@@ -4754,15 +4785,15 @@
</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>
+INSERT INTO table (a,b,c) VALUES (1,2,3)
+ ON DUPLICATE KEY UPDATE c=3;
+INSERT INTO table (a,b,c) VALUES (4,5,6)
+ ON DUPLICATE KEY UPDATE c=9;
</programlisting>
<para>
- When you use <literal>ON DUPLICATE KEY UPDATE</literal>, the
- <literal>DELAYED</literal> option is ignored.
+ The <literal>DELAYED</literal> option is ignored when you use
+ <literal>ON DUPLICATE KEY UPDATE</literal>.
</para>
</section>
@@ -14466,7 +14497,7 @@
</para>
<para>
- The connection identifier
+ The connection identifier.
</para>
</listitem>
@@ -14476,7 +14507,7 @@
</para>
<para>
- The MySQL user who is issuing the statement. If this is
+ The MySQL user who issued the statement. If this is
<literal>system user</literal>, it refers to a non-client
thread spawned by the server to handle tasks internally.
This could be the I/O or SQL thread used on replication
@@ -14493,7 +14524,8 @@
<para>
The hostname of the client issuing the statement (except
- for <literal>system user</literal> where there is no host)
+ for <literal>system user</literal> where there is no
+ host).
</para>
<para>
@@ -14512,7 +14544,7 @@
<para>
The default database, if one is selected, otherwise
- <literal>NULL</literal>
+ <literal>NULL</literal>.
</para>
</listitem>
@@ -14524,7 +14556,7 @@
<para>
The value of that column corresponds to the
<literal>COM_<replaceable>xxx</replaceable></literal>
- commands of the client/server protocol; see
+ commands of the client/server protocol. See
<xref linkend="server-status-variables"/>
</para>
@@ -14558,7 +14590,7 @@
<para>
The time in seconds between the start of the statement or
- command and now
+ command and now.
</para>
</listitem>
@@ -14568,7 +14600,7 @@
</para>
<para>
- An action, event, or state which can be one of the
+ An action, event, or state, which can be one of the
following: <literal>After create</literal>,
<literal>Analyzing</literal>, <literal>Changing
master</literal>, <literal>Checking master
Modified: trunk/refman-5.0/functions.xml
===================================================================
--- trunk/refman-5.0/functions.xml 2006-01-27 15:41:40 UTC (rev 1062)
+++ trunk/refman-5.0/functions.xml 2006-01-27 16:18:29 UTC (rev 1063)
@@ -2607,8 +2607,12 @@
</listitem>
<listitem>
- <remark role="help-topic" condition="INSERT"/>
+ <remark role="help-topic" condition="INSERT function"/>
+ <remark role="help-keywords">
+ INSERT
+ </remark>
+
<remark role="help-syntax-begin"/>
<para>
Modified: trunk/refman-5.0/sql-syntax.xml
===================================================================
--- trunk/refman-5.0/sql-syntax.xml 2006-01-27 15:41:40 UTC (rev 1062)
+++ trunk/refman-5.0/sql-syntax.xml 2006-01-27 16:18:29 UTC (rev 1063)
@@ -3737,11 +3737,11 @@
<primary>ON DUPLICATE KEY</primary>
</indexterm>
- <indexterm type="function">
+ <indexterm>
<primary>INSERT</primary>
</indexterm>
- <remark role="help-topic" condition="INSERT INTO"/>
+ <remark role="help-topic" condition="INSERT"/>
<remark role="help-keywords">
INSERT INTO LOW_PRIORITY HIGH_PRIORITY IGNORE DUPLICATE KEY
@@ -3796,6 +3796,16 @@
<remark role="help-description-end"/>
<para>
+ You can use <literal>REPLACE</literal> instead of
+ <literal>INSERT</literal> to overwrite old rows.
+ <literal>REPLACE</literal> is the counterpart to <literal>INSERT
+ IGNORE</literal> in the treatment of new rows that contain
+ unique key values that duplicate old rows: The new rows are used
+ to replace the old rows rather than being discarded. See
+ <xref linkend="replace"/>.
+ </para>
+
+ <para>
<replaceable>tbl_name</replaceable> is the table into which rows
should be inserted. The columns for which the statement provides
values can be specified as follows:
@@ -3805,23 +3815,33 @@
<listitem>
<para>
- The column name list or the <literal>SET</literal> clause
- indicates the columns explicitly.
+ You can provide a comma-separated list of column names
+ following the table name. In this case, a value for each
+ named column must be provided by the
+ <literal>VALUES</literal> list or the
+ <literal>SELECT</literal> statement.
</para>
</listitem>
<listitem>
<para>
- If you do not specify the column list for <literal>INSERT
- ... VALUES</literal> or <literal>INSERT ...
+ If you do not specify a list of column names for
+ <literal>INSERT ... VALUES</literal> or <literal>INSERT ...
SELECT</literal>, values for every column in the table must
- be provided in the <literal>VALUES</literal> list or by the
- <literal>SELECT</literal>. If you do not know the order of
- the columns in the table, use <literal>DESCRIBE
+ be provided by the <literal>VALUES</literal> list or the
+ <literal>SELECT</literal> statement. If you do not know the
+ order of the columns in the table, use <literal>DESCRIBE
<replaceable>tbl_name</replaceable></literal> to find out.
</para>
</listitem>
+ <listitem>
+ <para>
+ The <literal>SET</literal> clause indicates the column names
+ explicitly.
+ </para>
+ </listitem>
+
</itemizedlist>
<para>
@@ -3836,13 +3856,14 @@
<primary>default values</primary>
</indexterm>
- If you are not running in strict mode, any column not
+ If you are not running in strict SQL mode, any column not
explicitly given a value is set to its default (explicit or
implicit) value. For example, if you specify a column list
that doesn't name all the columns in the table, unnamed
columns are set to their default values. Default value
- assignment is described in <xref linkend="create-table"/>.
- See also <xref linkend="constraint-invalid-data"/>.
+ assignment is described in
+ <xref linkend="data-type defaults"/>. See also
+ <xref linkend="constraint-invalid-data"/>.
</para>
<para>
@@ -3869,8 +3890,8 @@
<para>
You can also use
<literal>DEFAULT(<replaceable>col_name</replaceable>)</literal>
- as a more general form that can be used in expressions for
- producing a column's default value.
+ as a more general form that can be used in expressions to
+ produce a given column's default value.
</para>
</listitem>
@@ -3882,14 +3903,14 @@
</para>
<programlisting>
-mysql> <userinput>INSERT INTO <replaceable>tbl_name</replaceable> () VALUES();</userinput>
+INSERT INTO <replaceable>tbl_name</replaceable> () VALUES();
</programlisting>
<para>
- In strict mode, you will get an error if a column doesn't
- have a default value. Otherwise, MySQL will use the implicit
- default value for any column without an explicitly defined
- default.
+ In strict mode, an error occurs if any column doesn't have a
+ default value. Otherwise, MySQL uses the implicit default
+ value for any column that does not have an explicitly
+ defined default.
</para>
</listitem>
@@ -3926,7 +3947,7 @@
</para>
<programlisting>
-mysql> <userinput>INSERT INTO <replaceable>tbl_name</replaceable> (<replaceable>col1</replaceable>,<replaceable>col2</replaceable>) VALUES(15,<replaceable>col1</replaceable>*2);</userinput>
+INSERT INTO <replaceable>tbl_name</replaceable> (col1,col2) VALUES(15,col1*2);
</programlisting>
<para>
@@ -3936,7 +3957,7 @@
</para>
<programlisting>
-mysql> <userinput>INSERT INTO <replaceable>tbl_name</replaceable> (<replaceable>col1</replaceable>,<replaceable>col2</replaceable>) VALUES(<replaceable>col2</replaceable>*2,15);</userinput>
+INSERT INTO <replaceable>tbl_name</replaceable> (col1,col2) VALUES(col2*2,15);
</programlisting>
<para>
@@ -3952,6 +3973,48 @@
</itemizedlist>
<para>
+ <literal>INSERT</literal> statements that use
+ <literal>VALUES</literal> syntax can insert multiple rows. To do
+ this, include multiple lists of column values, each enclosed
+ within parentheses and separated by commas. Example:
+ </para>
+
+<programlisting>
+INSERT INTO <replaceable>tbl_name</replaceable> (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
+</programlisting>
+
+ <para>
+ The values list for each row must be enclosed within
+ parentheses. The following statement is illegal because the
+ number of values in the list does not match the number of column
+ names:
+ </para>
+
+<programlisting>
+INSERT INTO <replaceable>tbl_name</replaceable> (a,b,c) VALUES(1,2,3,4,5,6,7,8,9);
+</programlisting>
+
+ <para>
+ The rows-affected value for an <literal>INSERT</literal> can be
+ obtained using the <literal>mysql_affected_rows()</literal> C
+ API function. See <xref linkend="mysql-affected-rows"/>.
+ </para>
+
+ <para>
+ If <literal>INSERT</literal> inserts a row into a table that has
+ an <literal>AUTO_INCREMENT</literal> column, you can find the
+ value used for that column by using the SQL
+ <literal>LAST_INSERT_ID()</literal> function. From within the C
+ API, use the <literal>mysql_insert_id()</literal> function.
+ However, you should note that the two functions do not always
+ behave identically. The behavior of <literal>INSERT</literal>
+ statements with respect to <literal>AUTO_INCREMENT</literal>
+ columns is discussed further in
+ <xref linkend="information-functions"/>, and
+ <xref linkend="mysql-insert-id"/>.
+ </para>
+
+ <para>
The <literal>INSERT</literal> statement supports the following
modifiers:
</para>
@@ -3963,13 +4026,20 @@
If you use the <literal>DELAYED</literal> keyword, the
server puts the row or rows to be inserted into a buffer,
and the client issuing the <literal>INSERT DELAYED</literal>
- statement can then continue. If the table is in use, the
- server holds the rows. When the table is free, the server
- begins inserting rows, checking periodically to see whether
- there are any new read requests for the table. If there are,
- the delayed row queue is suspended until the table becomes
- free again. See <xref linkend="insert-delayed"/>.
+ statement can then continue immediately. If the table is in
+ use, the server holds the rows. When the table is free, the
+ server begins inserting rows, checking periodically to see
+ whether there are any new read requests for the table. If
+ there are, the delayed row queue is suspended until the
+ table becomes free again. See
+ <xref linkend="insert-delayed"/>.
</para>
+
+ <para>
+ <literal>DELAYED</literal> is ignored with <literal>INSERT
+ ... SELECT</literal> or <literal>INSERT ... ON DUPLICATE KEY
+ UPDATE</literal>.
+ </para>
</listitem>
<listitem>
@@ -3984,8 +4054,7 @@
LOW_PRIORITY</literal> statement to wait for a very long
time (or even forever) in a read-heavy environment. (This is
in contrast to <literal>INSERT DELAYED</literal>, which lets
- the client continue at once. See
- <xref linkend="insert-delayed"/>.) Note that
+ the client continue at once. Note that
<literal>LOW_PRIORITY</literal> should normally not be used
with <literal>MyISAM</literal> tables because doing so
disables concurrent inserts. See
@@ -4005,23 +4074,14 @@
<listitem>
<para>
- The rows-affected value for an <literal>INSERT</literal> can
- be obtained using the
- <literal>mysql_affected_rows()</literal> C API function. See
- <xref linkend="mysql-affected-rows"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If you use the <literal>IGNORE</literal> keyword in an
- <literal>INSERT</literal> statement, errors that occur while
- executing the statement are treated as warnings instead. For
- example, without <literal>IGNORE</literal>, a row that
- duplicates an existing <literal>UNIQUE</literal> index or
- <literal>PRIMARY KEY</literal> value in the table causes a
- duplicate-key error and the statement is aborted. With
- <literal>IGNORE</literal>, the row is still not inserted,
+ If you use the <literal>IGNORE</literal> keyword, errors
+ that occur while executing the <literal>INSERT</literal>
+ statement are treated as warnings instead. For example,
+ without <literal>IGNORE</literal>, a row that duplicates an
+ existing <literal>UNIQUE</literal> index or <literal>PRIMARY
+ KEY</literal> value in the table causes a duplicate-key
+ error and the statement is aborted. With
+ <literal>IGNORE</literal>, the row still is not inserted,
but no error is issued. Data conversions that would trigger
errors abort the statement if <literal>IGNORE</literal> is
not specified. With <literal>IGNORE</literal>, invalid
@@ -4054,19 +4114,6 @@
</indexterm>
<para>
- You can find the value used for an
- <literal>AUTO_INCREMENT</literal> column by using the SQL
- <literal>LAST_INSERT_ID()</literal> function. From within the C
- API, use the <literal>mysql_insert_id()</literal> function.
- However, you should note that the two functions do not always
- behave identically. The behavior of <literal>INSERT</literal>
- statements with respect to <literal>AUTO_INCREMENT</literal>
- columns is discussed further in
- <xref linkend="information-functions"/>, and
- <xref linkend="mysql-insert-id"/>.
- </para>
-
- <para>
If you use an <literal>INSERT ... VALUES</literal> statement
with multiple value lists or <literal>INSERT ...
SELECT</literal>, the statement returns an information string in
@@ -4092,6 +4139,10 @@
<itemizedlist>
<listitem>
+ <remark role="todo">
+ [pd] Differs in strict mode?
+ </remark>
+
<para>
Inserting <literal>NULL</literal> into a column that has
been declared <literal>NOT NULL</literal>. For multiple-row
@@ -4160,18 +4211,14 @@
function. See <xref linkend="mysql-info"/>.
</para>
- <indexterm type="function">
- <primary>REPLACE ... SELECT</primary>
- </indexterm>
-
- <indexterm type="function">
- <primary>INSERT ... SELECT</primary>
- </indexterm>
-
<section id="insert-select">
<title>&title-insert-select;</title>
+ <indexterm>
+ <primary>INSERT ... SELECT</primary>
+ </indexterm>
+
<remark role="help-topic" condition="INSERT SELECT"/>
<remark role="help-syntax"/>
@@ -4265,16 +4312,6 @@
must qualify non-unique column names in the values part.
</para>
- <para>
- You can use <literal>REPLACE</literal> instead of
- <literal>INSERT</literal> to overwrite old rows.
- <literal>REPLACE</literal> is the counterpart to
- <literal>INSERT IGNORE</literal> in the treatment of new rows
- that contain unique key values that duplicate old rows: The
- new rows are used to replace the old rows rather than being
- discarded.
- </para>
-
</section>
<section id="insert-delayed">
@@ -4611,10 +4648,10 @@
</para>
<programlisting>
-mysql> <userinput>INSERT INTO table (a,b,c) VALUES (1,2,3)</userinput>
- -> <userinput>ON DUPLICATE KEY UPDATE c=c+1;</userinput>
+INSERT INTO table (a,b,c) VALUES (1,2,3)
+ ON DUPLICATE KEY UPDATE c=c+1;
-mysql> <userinput>UPDATE table SET c=c+1 WHERE a=1;</userinput>
+UPDATE table SET c=c+1 WHERE a=1;
</programlisting>
<para>
@@ -4623,28 +4660,28 @@
</para>
<para>
- <emphasis role="bold">Note</emphasis>: If column
- <literal>b</literal> is also unique, the
- <literal>INSERT</literal> would be equivalent to this
+ If column <literal>b</literal> is also unique, the
+ <literal>INSERT</literal> is 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>
+UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
</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.
+ KEY</literal> clause on tables with multiple unique indexes.
</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
+ 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
@@ -4653,16 +4690,12 @@
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.
+ <literal>NULL</literal> otherwise. Example:
</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>
+INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
+ ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
</programlisting>
<para>
@@ -4670,15 +4703,15 @@
</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>
+INSERT INTO table (a,b,c) VALUES (1,2,3)
+ ON DUPLICATE KEY UPDATE c=3;
+INSERT INTO table (a,b,c) VALUES (4,5,6)
+ ON DUPLICATE KEY UPDATE c=9;
</programlisting>
<para>
- When you use <literal>ON DUPLICATE KEY UPDATE</literal>, the
- <literal>DELAYED</literal> option is ignored.
+ The <literal>DELAYED</literal> option is ignored when you use
+ <literal>ON DUPLICATE KEY UPDATE</literal>.
</para>
</section>
@@ -15667,7 +15700,7 @@
</para>
<para>
- The connection identifier
+ The connection identifier.
</para>
</listitem>
@@ -15677,7 +15710,7 @@
</para>
<para>
- The MySQL user who is issuing the statement. If this is
+ The MySQL user who issued the statement. If this is
<literal>system user</literal>, it refers to a non-client
thread spawned by the server to handle tasks internally.
This could be the I/O or SQL thread used on replication
@@ -15694,7 +15727,8 @@
<para>
The hostname of the client issuing the statement (except
- for <literal>system user</literal> where there is no host)
+ for <literal>system user</literal> where there is no
+ host).
</para>
<para>
@@ -15713,7 +15747,7 @@
<para>
The default database, if one is selected, otherwise
- <literal>NULL</literal>
+ <literal>NULL</literal>.
</para>
</listitem>
@@ -15725,7 +15759,7 @@
<para>
The value of that column corresponds to the
<literal>COM_<replaceable>xxx</replaceable></literal>
- commands of the client/server protocol; see
+ commands of the client/server protocol. See
<xref linkend="server-status-variables"/>
</para>
@@ -15759,7 +15793,7 @@
<para>
The time in seconds between the start of the statement or
- command and now
+ command and now.
</para>
</listitem>
@@ -15769,7 +15803,7 @@
</para>
<para>
- An action, event, or state which can be one of the
+ An action, event, or state, which can be one of the
following: <literal>After create</literal>,
<literal>Analyzing</literal>, <literal>Changing
master</literal>, <literal>Checking master
Modified: trunk/refman-5.1/functions.xml
===================================================================
--- trunk/refman-5.1/functions.xml 2006-01-27 15:41:40 UTC (rev 1062)
+++ trunk/refman-5.1/functions.xml 2006-01-27 16:18:29 UTC (rev 1063)
@@ -2579,8 +2579,12 @@
</listitem>
<listitem>
- <remark role="help-topic" condition="INSERT"/>
+ <remark role="help-topic" condition="INSERT function"/>
+ <remark role="help-keywords">
+ INSERT
+ </remark>
+
<remark role="help-syntax-begin"/>
<para>
Modified: trunk/refman-5.1/sql-syntax.xml
===================================================================
--- trunk/refman-5.1/sql-syntax.xml 2006-01-27 15:41:40 UTC (rev 1062)
+++ trunk/refman-5.1/sql-syntax.xml 2006-01-27 16:18:29 UTC (rev 1063)
@@ -4345,11 +4345,11 @@
<primary>ON DUPLICATE KEY</primary>
</indexterm>
- <indexterm type="function">
+ <indexterm>
<primary>INSERT</primary>
</indexterm>
- <remark role="help-topic" condition="INSERT INTO"/>
+ <remark role="help-topic" condition="INSERT"/>
<remark role="help-keywords">
INSERT INTO LOW_PRIORITY HIGH_PRIORITY IGNORE DUPLICATE KEY
@@ -4404,6 +4404,16 @@
<remark role="help-description-end"/>
<para>
+ You can use <literal>REPLACE</literal> instead of
+ <literal>INSERT</literal> to overwrite old rows.
+ <literal>REPLACE</literal> is the counterpart to <literal>INSERT
+ IGNORE</literal> in the treatment of new rows that contain
+ unique key values that duplicate old rows: The new rows are used
+ to replace the old rows rather than being discarded. See
+ <xref linkend="replace"/>.
+ </para>
+
+ <para>
<replaceable>tbl_name</replaceable> is the table into which rows
should be inserted. The columns for which the statement provides
values can be specified as follows:
@@ -4413,23 +4423,33 @@
<listitem>
<para>
- The column name list or the <literal>SET</literal> clause
- indicates the columns explicitly.
+ You can provide a comma-separated list of column names
+ following the table name. In this case, a value for each
+ named column must be provided by the
+ <literal>VALUES</literal> list or the
+ <literal>SELECT</literal> statement.
</para>
</listitem>
<listitem>
<para>
- If you do not specify the column list for <literal>INSERT
- ... VALUES</literal> or <literal>INSERT ...
+ If you do not specify a list of column names for
+ <literal>INSERT ... VALUES</literal> or <literal>INSERT ...
SELECT</literal>, values for every column in the table must
- be provided in the <literal>VALUES</literal> list or by the
- <literal>SELECT</literal>. If you do not know the order of
- the columns in the table, use <literal>DESCRIBE
+ be provided by the <literal>VALUES</literal> list or the
+ <literal>SELECT</literal> statement. If you do not know the
+ order of the columns in the table, use <literal>DESCRIBE
<replaceable>tbl_name</replaceable></literal> to find out.
</para>
</listitem>
+ <listitem>
+ <para>
+ The <literal>SET</literal> clause indicates the column names
+ explicitly.
+ </para>
+ </listitem>
+
</itemizedlist>
<para>
@@ -4444,13 +4464,14 @@
<primary>default values</primary>
</indexterm>
- If you are not running in strict mode, any column not
+ If you are not running in strict SQL mode, any column not
explicitly given a value is set to its default (explicit or
implicit) value. For example, if you specify a column list
that doesn't name all the columns in the table, unnamed
columns are set to their default values. Default value
- assignment is described in <xref linkend="create-table"/>.
- See also <xref linkend="constraint-invalid-data"/>.
+ assignment is described in
+ <xref linkend="data-type defaults"/>. See also
+ <xref linkend="constraint-invalid-data"/>.
</para>
<para>
@@ -4477,8 +4498,8 @@
<para>
You can also use
<literal>DEFAULT(<replaceable>col_name</replaceable>)</literal>
- as a more general form that can be used in expressions for
- producing a column's default value.
+ as a more general form that can be used in expressions to
+ produce a given column's default value.
</para>
</listitem>
@@ -4490,14 +4511,14 @@
</para>
<programlisting>
-mysql> <userinput>INSERT INTO <replaceable>tbl_name</replaceable> () VALUES();</userinput>
+INSERT INTO <replaceable>tbl_name</replaceable> () VALUES();
</programlisting>
<para>
- In strict mode, you will get an error if a column doesn't
- have a default value. Otherwise, MySQL will use the implicit
- default value for any column without an explicitly defined
- default.
+ In strict mode, an error occurs if any column doesn't have a
+ default value. Otherwise, MySQL uses the implicit default
+ value for any column that does not have an explicitly
+ defined default.
</para>
</listitem>
@@ -4534,7 +4555,7 @@
</para>
<programlisting>
-mysql> <userinput>INSERT INTO <replaceable>tbl_name</replaceable> (<replaceable>col1</replaceable>,<replaceable>col2</replaceable>) VALUES(15,<replaceable>col1</replaceable>*2);</userinput>
+INSERT INTO <replaceable>tbl_name</replaceable> (col1,col2) VALUES(15,col1*2);
</programlisting>
<para>
@@ -4544,7 +4565,7 @@
</para>
<programlisting>
-mysql> <userinput>INSERT INTO <replaceable>tbl_name</replaceable> (<replaceable>col1</replaceable>,<replaceable>col2</replaceable>) VALUES(<replaceable>col2</replaceable>*2,15);</userinput>
+INSERT INTO <replaceable>tbl_name</replaceable> (col1,col2) VALUES(col2*2,15);
</programlisting>
<para>
@@ -4560,6 +4581,48 @@
</itemizedlist>
<para>
+ <literal>INSERT</literal> statements that use
+ <literal>VALUES</literal> syntax can insert multiple rows. To do
+ this, include multiple lists of column values, each enclosed
+ within parentheses and separated by commas. Example:
+ </para>
+
+<programlisting>
+INSERT INTO <replaceable>tbl_name</replaceable> (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
+</programlisting>
+
+ <para>
+ The values list for each row must be enclosed within
+ parentheses. The following statement is illegal because the
+ number of values in the list does not match the number of column
+ names:
+ </para>
+
+<programlisting>
+INSERT INTO <replaceable>tbl_name</replaceable> (a,b,c) VALUES(1,2,3,4,5,6,7,8,9);
+</programlisting>
+
+ <para>
+ The rows-affected value for an <literal>INSERT</literal> can be
+ obtained using the <literal>mysql_affected_rows()</literal> C
+ API function. See <xref linkend="mysql-affected-rows"/>.
+ </para>
+
+ <para>
+ If <literal>INSERT</literal> inserts a row into a table that has
+ an <literal>AUTO_INCREMENT</literal> column, you can find the
+ value used for that column by using the SQL
+ <literal>LAST_INSERT_ID()</literal> function. From within the C
+ API, use the <literal>mysql_insert_id()</literal> function.
+ However, you should note that the two functions do not always
+ behave identically. The behavior of <literal>INSERT</literal>
+ statements with respect to <literal>AUTO_INCREMENT</literal>
+ columns is discussed further in
+ <xref linkend="information-functions"/>, and
+ <xref linkend="mysql-insert-id"/>.
+ </para>
+
+ <para>
The <literal>INSERT</literal> statement supports the following
modifiers:
</para>
@@ -4571,13 +4634,20 @@
If you use the <literal>DELAYED</literal> keyword, the
server puts the row or rows to be inserted into a buffer,
and the client issuing the <literal>INSERT DELAYED</literal>
- statement can then continue. If the table is in use, the
- server holds the rows. When the table is free, the server
- begins inserting rows, checking periodically to see whether
- there are any new read requests for the table. If there are,
- the delayed row queue is suspended until the table becomes
- free again. See <xref linkend="insert-delayed"/>.
+ statement can then continue immediately. If the table is in
+ use, the server holds the rows. When the table is free, the
+ server begins inserting rows, checking periodically to see
+ whether there are any new read requests for the table. If
+ there are, the delayed row queue is suspended until the
+ table becomes free again. See
+ <xref linkend="insert-delayed"/>.
</para>
+
+ <para>
+ <literal>DELAYED</literal> is ignored with <literal>INSERT
+ ... SELECT</literal> or <literal>INSERT ... ON DUPLICATE KEY
+ UPDATE</literal>.
+ </para>
</listitem>
<listitem>
@@ -4592,8 +4662,7 @@
LOW_PRIORITY</literal> statement to wait for a very long
time (or even forever) in a read-heavy environment. (This is
in contrast to <literal>INSERT DELAYED</literal>, which lets
- the client continue at once. See
- <xref linkend="insert-delayed"/>.) Note that
+ the client continue at once. Note that
<literal>LOW_PRIORITY</literal> should normally not be used
with <literal>MyISAM</literal> tables because doing so
disables concurrent inserts. See
@@ -4613,23 +4682,14 @@
<listitem>
<para>
- The rows-affected value for an <literal>INSERT</literal> can
- be obtained using the
- <literal>mysql_affected_rows()</literal> C API function. See
- <xref linkend="mysql-affected-rows"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If you use the <literal>IGNORE</literal> keyword in an
- <literal>INSERT</literal> statement, errors that occur while
- executing the statement are treated as warnings instead. For
- example, without <literal>IGNORE</literal>, a row that
- duplicates an existing <literal>UNIQUE</literal> index or
- <literal>PRIMARY KEY</literal> value in the table causes a
- duplicate-key error and the statement is aborted. With
- <literal>IGNORE</literal>, the row is still not inserted,
+ If you use the <literal>IGNORE</literal> keyword, errors
+ that occur while executing the <literal>INSERT</literal>
+ statement are treated as warnings instead. For example,
+ without <literal>IGNORE</literal>, a row that duplicates an
+ existing <literal>UNIQUE</literal> index or <literal>PRIMARY
+ KEY</literal> value in the table causes a duplicate-key
+ error and the statement is aborted. With
+ <literal>IGNORE</literal>, the row still is not inserted,
but no error is issued. Data conversions that would trigger
errors abort the statement if <literal>IGNORE</literal> is
not specified. With <literal>IGNORE</literal>, invalid
@@ -4662,19 +4722,6 @@
</indexterm>
<para>
- You can find the value used for an
- <literal>AUTO_INCREMENT</literal> column by using the SQL
- <literal>LAST_INSERT_ID()</literal> function. From within the C
- API, use the <literal>mysql_insert_id()</literal> function.
- However, you should note that the two functions do not always
- behave identically. The behavior of <literal>INSERT</literal>
- statements with respect to <literal>AUTO_INCREMENT</literal>
- columns is discussed further in
- <xref linkend="information-functions"/>, and
- <xref linkend="mysql-insert-id"/>.
- </para>
-
- <para>
If you use an <literal>INSERT ... VALUES</literal> statement
with multiple value lists or <literal>INSERT ...
SELECT</literal>, the statement returns an information string in
@@ -4700,6 +4747,10 @@
<itemizedlist>
<listitem>
+ <remark role="todo">
+ [pd] Differs in strict mode?
+ </remark>
+
<para>
Inserting <literal>NULL</literal> into a column that has
been declared <literal>NOT NULL</literal>. For multiple-row
@@ -4768,18 +4819,14 @@
function. See <xref linkend="mysql-info"/>.
</para>
- <indexterm type="function">
- <primary>REPLACE ... SELECT</primary>
- </indexterm>
-
- <indexterm type="function">
- <primary>INSERT ... SELECT</primary>
- </indexterm>
-
<section id="insert-select">
<title>&title-insert-select;</title>
+ <indexterm>
+ <primary>INSERT ... SELECT</primary>
+ </indexterm>
+
<remark role="help-topic" condition="INSERT SELECT"/>
<remark role="help-syntax"/>
@@ -4873,16 +4920,6 @@
must qualify non-unique column names in the values part.
</para>
- <para>
- You can use <literal>REPLACE</literal> instead of
- <literal>INSERT</literal> to overwrite old rows.
- <literal>REPLACE</literal> is the counterpart to
- <literal>INSERT IGNORE</literal> in the treatment of new rows
- that contain unique key values that duplicate old rows: The
- new rows are used to replace the old rows rather than being
- discarded.
- </para>
-
</section>
<section id="insert-delayed">
@@ -5219,10 +5256,10 @@
</para>
<programlisting>
-mysql> <userinput>INSERT INTO table (a,b,c) VALUES (1,2,3)</userinput>
- -> <userinput>ON DUPLICATE KEY UPDATE c=c+1;</userinput>
+INSERT INTO table (a,b,c) VALUES (1,2,3)
+ ON DUPLICATE KEY UPDATE c=c+1;
-mysql> <userinput>UPDATE table SET c=c+1 WHERE a=1;</userinput>
+UPDATE table SET c=c+1 WHERE a=1;
</programlisting>
<para>
@@ -5231,28 +5268,28 @@
</para>
<para>
- <emphasis role="bold">Note</emphasis>: If column
- <literal>b</literal> is also unique, the
- <literal>INSERT</literal> would be equivalent to this
+ If column <literal>b</literal> is also unique, the
+ <literal>INSERT</literal> is 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>
+UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
</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.
+ KEY</literal> clause on tables with multiple unique indexes.
</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
+ 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
@@ -5261,16 +5298,12 @@
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.
+ <literal>NULL</literal> otherwise. Example:
</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>
+INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
+ ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
</programlisting>
<para>
@@ -5278,15 +5311,15 @@
</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>
+INSERT INTO table (a,b,c) VALUES (1,2,3)
+ ON DUPLICATE KEY UPDATE c=3;
+INSERT INTO table (a,b,c) VALUES (4,5,6)
+ ON DUPLICATE KEY UPDATE c=9;
</programlisting>
<para>
- When you use <literal>ON DUPLICATE KEY UPDATE</literal>, the
- <literal>DELAYED</literal> option is ignored.
+ The <literal>DELAYED</literal> option is ignored when you use
+ <literal>ON DUPLICATE KEY UPDATE</literal>.
</para>
</section>
@@ -16204,7 +16237,7 @@
</para>
<para>
- The connection identifier
+ The connection identifier.
</para>
</listitem>
@@ -16214,7 +16247,7 @@
</para>
<para>
- The MySQL user who is issuing the statement. If this is
+ The MySQL user who issued the statement. If this is
<literal>system user</literal>, it refers to a non-client
thread spawned by the server to handle tasks internally.
This could be the I/O or SQL thread used on replication
@@ -16231,7 +16264,8 @@
<para>
The hostname of the client issuing the statement (except
- for <literal>system user</literal> where there is no host)
+ for <literal>system user</literal> where there is no
+ host).
</para>
<para>
@@ -16250,7 +16284,7 @@
<para>
The default database, if one is selected, otherwise
- <literal>NULL</literal>
+ <literal>NULL</literal>.
</para>
</listitem>
@@ -16262,7 +16296,7 @@
<para>
The value of that column corresponds to the
<literal>COM_<replaceable>xxx</replaceable></literal>
- commands of the client/server protocol; see
+ commands of the client/server protocol. See
<xref linkend="server-status-variables"/>
</para>
@@ -16296,7 +16330,7 @@
<para>
The time in seconds between the start of the statement or
- command and now
+ command and now.
</para>
</listitem>
@@ -16306,7 +16340,7 @@
</para>
<para>
- An action, event, or state which can be one of the
+ An action, event, or state, which can be one of the
following: <literal>After create</literal>,
<literal>Analyzing</literal>, <literal>Changing
master</literal>, <literal>Checking master
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r1063 - in trunk: . refman-4.1 refman-5.0 refman-5.1 | paul | 27 Jan |
