Author: paul
Date: 2006-01-27 17:38:21 +0100 (Fri, 27 Jan 2006)
New Revision: 1064
Log:
r6750@frost: paul | 2006-01-27 10:38:02 -0600
General revisions.
Modified:
trunk/
trunk/refman-4.1/optimization.xml
trunk/refman-4.1/sql-syntax.xml
trunk/refman-5.0/optimization.xml
trunk/refman-5.0/sql-syntax.xml
trunk/refman-5.1/optimization.xml
trunk/refman-5.1/sql-syntax.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6748
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2564
+ b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6750
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2564
Modified: trunk/refman-4.1/optimization.xml
===================================================================
--- trunk/refman-4.1/optimization.xml 2006-01-27 16:18:29 UTC (rev 1063)
+++ trunk/refman-4.1/optimization.xml 2006-01-27 16:38:21 UTC (rev 1064)
@@ -5000,6 +5000,13 @@
deleted rows in middle of the table.
</para>
+ <para>
+ To ensure that the update log or binary log can be used to
+ re-create the original tables, MySQL does not allow concurrent
+ inserts for <literal>CREATE TABLE ... SELECT</literal>
+ statements.
+ </para>
+
</section>
</section>
Modified: trunk/refman-4.1/sql-syntax.xml
===================================================================
--- trunk/refman-4.1/sql-syntax.xml 2006-01-27 16:18:29 UTC (rev 1063)
+++ trunk/refman-4.1/sql-syntax.xml 2006-01-27 16:38:21 UTC (rev 1064)
@@ -2585,7 +2585,8 @@
<para>
To ensure that the update log or binary log can be used to
re-create the original tables, MySQL does not allow concurrent
- inserts during <literal>CREATE TABLE ... SELECT</literal>.
+ inserts for <literal>CREATE TABLE ... SELECT</literal>
+ statements.
</para>
<section id="silent-column-changes">
@@ -3939,7 +3940,7 @@
you specify a column list that does not 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"/>, and
+ <xref linkend="data-type-defaults"/>, and
<xref linkend="constraint-invalid-data"/>.
</para>
</listitem>
@@ -4065,6 +4066,104 @@
</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
+ this format:
+ </para>
+
+<programlisting>
+Records: 100 Duplicates: 0 Warnings: 0
+</programlisting>
+
+ <para>
+ <literal>Records</literal> indicates the number of rows
+ processed by the statement. (This is not necessarily the number
+ of rows actually inserted because <literal>Duplicates</literal>
+ can be non-zero.) <literal>Duplicates</literal> indicates the
+ number of rows that could not be inserted because they would
+ duplicate some existing unique index value.
+ <literal>Warnings</literal> indicates the number of attempts to
+ insert column values that were problematic in some way. Warnings
+ can occur under any of the following conditions:
+ </para>
+
+ <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
+ <literal>INSERT</literal> statements or <literal>INSERT INTO
+ ... SELECT</literal> statements, the column is set to the
+ implicit default value for the column data type. This is
+ <literal>0</literal> for numeric types, the empty string
+ (<literal>''</literal>) for string types, and the
+ <quote>zero</quote> value for date and time types.
+ <literal>INSERT INTO ... SELECT</literal> statements are
+ handled the same way as multiple-row inserts because the
+ server does not examine the result set from the
+ <literal>SELECT</literal> to see whether it returns a single
+ row. (For a single-row <literal>INSERT</literal>, no warning
+ occurs when <literal>NULL</literal> is inserted into a
+ <literal>NOT NULL</literal> column. Instead, the statement
+ fails with an error.)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Setting a numeric column to a value that lies outside the
+ column's range. The value is clipped to the closest endpoint
+ of the range.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Assigning a value such as <literal>'10.34 a'</literal> to a
+ numeric column. The trailing non-numeric text is stripped
+ off and the remaining numeric part is inserted. If the
+ string value has no leading numeric part, the column is set
+ to <literal>0</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Inserting a string into a string column
+ (<literal>CHAR</literal>, <literal>VARCHAR</literal>,
+ <literal>TEXT</literal>, or <literal>BLOB</literal>) that
+ exceeds the column's maximum length. The value is truncated
+ to the column's maximum length.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Inserting a value into a date or time column that is illegal
+ for the data type. The column is set to the appropriate zero
+ value for the type.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <indexterm type="function">
+ <primary>mysql_info()</primary>
+ </indexterm>
+
+ <para>
+ If you are using the C API, the information string can be
+ obtained by invoking the <literal>mysql_info()</literal>
+ function. See <xref linkend="mysql-info"/>.
+ </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
@@ -4182,104 +4281,6 @@
<primary>mysql_insert_id()</primary>
</indexterm>
- <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
- this format:
- </para>
-
-<programlisting>
-Records: 100 Duplicates: 0 Warnings: 0
-</programlisting>
-
- <para>
- <literal>Records</literal> indicates the number of rows
- processed by the statement. (This is not necessarily the number
- of rows actually inserted because <literal>Duplicates</literal>
- can be non-zero.) <literal>Duplicates</literal> indicates the
- number of rows that could not be inserted because they would
- duplicate some existing unique index value.
- <literal>Warnings</literal> indicates the number of attempts to
- insert column values that were problematic in some way. Warnings
- can occur under any of the following conditions:
- </para>
-
- <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
- <literal>INSERT</literal> statements or <literal>INSERT INTO
- ... SELECT</literal> statements, the column is set to the
- implicit default value for the column data type. This is
- <literal>0</literal> for numeric types, the empty string
- (<literal>''</literal>) for string types, and the
- <quote>zero</quote> value for date and time types.
- <literal>INSERT INTO ... SELECT</literal> statements are
- handled the same way as multiple-row inserts because the
- server does not examine the result set from the
- <literal>SELECT</literal> to see whether it returns a single
- row. (For a single-row <literal>INSERT</literal>, no warning
- occurs when <literal>NULL</literal> is inserted into a
- <literal>NOT NULL</literal> column. Instead, the statement
- fails with an error.)
- </para>
- </listitem>
-
- <listitem>
- <para>
- Setting a numeric column to a value that lies outside the
- column's range. The value is clipped to the closest endpoint
- of the range.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Assigning a value such as <literal>'10.34 a'</literal> to a
- numeric column. The trailing non-numeric text is stripped
- off and the remaining numeric part is inserted. If the
- string value has no leading numeric part, the column is set
- to <literal>0</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Inserting a string into a string column
- (<literal>CHAR</literal>, <literal>VARCHAR</literal>,
- <literal>TEXT</literal>, or <literal>BLOB</literal>) that
- exceeds the column's maximum length. The value is truncated
- to the column's maximum length.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Inserting a value into a date or time column that is illegal
- for the data type. The column is set to the appropriate zero
- value for the type.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <indexterm type="function">
- <primary>mysql_info()</primary>
- </indexterm>
-
- <para>
- If you are using the C API, the information string can be
- obtained by invoking the <literal>mysql_info()</literal>
- function. See <xref linkend="mysql-info"/>.
- </para>
-
<section id="insert-select">
<title>&title-insert-select;</title>
@@ -4303,26 +4304,21 @@
<para>
With <literal>INSERT ... SELECT</literal>, you can quickly
- insert many rows into a table from one or many tables.
+ insert many rows into a table from one or many tables. For
+ example:
</para>
- <remark role="help-description-end"/>
-
- <para>
- For example:
- </para>
-
- <remark role="help-example"/>
-
<programlisting>
INSERT INTO tbl_temp2 (fld_id)
- SELECT tbl_temp1.fld_order_id
- FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
+ SELECT tbl_temp1.fld_order_id
+ FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
</programlisting>
+ <remark role="help-description-end"/>
+
<para>
- The following conditions hold for an <literal>INSERT ...
- SELECT</literal> statement:
+ The following conditions hold for a <literal>INSERT ...
+ SELECT</literal> statements:
</para>
<itemizedlist>
@@ -4339,7 +4335,7 @@
<listitem>
<para>
- Do not use <literal>DELAYED</literal> with <literal>INSERT
+ <literal>DELAYED</literal> is ignored with <literal>INSERT
... SELECT</literal>.
</para>
</listitem>
@@ -4371,7 +4367,7 @@
<para>
To ensure that the binary log can be used to re-create the
original tables, MySQL does not allow concurrent inserts
- during <literal>INSERT ... SELECT</literal>.
+ for <literal>INSERT ... SELECT</literal> statements.
</para>
</listitem>
@@ -4386,7 +4382,7 @@
<para>
In the values part of <literal>ON DUPLICATE KEY
- UPDATE</literal> you can refer to columns in other tables, as
+ UPDATE</literal>, you can refer to columns in other tables, as
long as you do not use <literal>GROUP BY</literal> in the
<literal>SELECT</literal> part. One side effect is that you
must qualify non-unique column names in the values part.
@@ -4428,12 +4424,12 @@
The <literal>DELAYED</literal> option for the
<literal>INSERT</literal> statement is a MySQL extension to
standard SQL that is very useful if you have clients that
- cannot wait for the <literal>INSERT</literal> to complete.
- This is a common problem when you use MySQL for logging and
- you also periodically run <literal>SELECT</literal> and
- <literal>UPDATE</literal> statements that take a long time to
- complete. <literal>DELAYED</literal> was introduced in MySQL
- 3.22.15.
+ cannot or need not wait for the <literal>INSERT</literal> to
+ complete. This is a common situation when you use MySQL for
+ logging and you also periodically run
+ <literal>SELECT</literal> and <literal>UPDATE</literal>
+ statements that take a long time to complete.
+ <literal>DELAYED</literal> was introduced in MySQL 3.22.15.
</para>
<para>
Modified: trunk/refman-5.0/optimization.xml
===================================================================
--- trunk/refman-5.0/optimization.xml 2006-01-27 16:18:29 UTC (rev 1063)
+++ trunk/refman-5.0/optimization.xml 2006-01-27 16:38:21 UTC (rev 1064)
@@ -6455,6 +6455,12 @@
deleted rows in middle of the table.
</para>
+ <para>
+ To ensure that the binary log can be used to re-create the
+ original tables, MySQL does not allow concurrent inserts for
+ <literal>INSERT ... SELECT</literal> statements.
+ </para>
+
</section>
</section>
Modified: trunk/refman-5.0/sql-syntax.xml
===================================================================
--- trunk/refman-5.0/sql-syntax.xml 2006-01-27 16:18:29 UTC (rev 1063)
+++ trunk/refman-5.0/sql-syntax.xml 2006-01-27 16:38:21 UTC (rev 1064)
@@ -3859,10 +3859,10 @@
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
+ that does not name all the columns in the table, unnamed
columns are set to their default values. Default value
assignment is described in
- <xref linkend="data-type defaults"/>. See also
+ <xref linkend="data-type-defaults"/>. See also
<xref linkend="constraint-invalid-data"/>.
</para>
@@ -4001,6 +4001,104 @@
</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
+ this format:
+ </para>
+
+<programlisting>
+Records: 100 Duplicates: 0 Warnings: 0
+</programlisting>
+
+ <para>
+ <literal>Records</literal> indicates the number of rows
+ processed by the statement. (This is not necessarily the number
+ of rows actually inserted because <literal>Duplicates</literal>
+ can be non-zero.) <literal>Duplicates</literal> indicates the
+ number of rows that could not be inserted because they would
+ duplicate some existing unique index value.
+ <literal>Warnings</literal> indicates the number of attempts to
+ insert column values that were problematic in some way. Warnings
+ can occur under any of the following conditions:
+ </para>
+
+ <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
+ <literal>INSERT</literal> statements or <literal>INSERT INTO
+ ... SELECT</literal> statements, the column is set to the
+ implicit default value for the column data type. This is
+ <literal>0</literal> for numeric types, the empty string
+ (<literal>''</literal>) for string types, and the
+ <quote>zero</quote> value for date and time types.
+ <literal>INSERT INTO ... SELECT</literal> statements are
+ handled the same way as multiple-row inserts because the
+ server does not examine the result set from the
+ <literal>SELECT</literal> to see whether it returns a single
+ row. (For a single-row <literal>INSERT</literal>, no warning
+ occurs when <literal>NULL</literal> is inserted into a
+ <literal>NOT NULL</literal> column. Instead, the statement
+ fails with an error.)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Setting a numeric column to a value that lies outside the
+ column's range. The value is clipped to the closest endpoint
+ of the range.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Assigning a value such as <literal>'10.34 a'</literal> to a
+ numeric column. The trailing non-numeric text is stripped
+ off and the remaining numeric part is inserted. If the
+ string value has no leading numeric part, the column is set
+ to <literal>0</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Inserting a string into a string column
+ (<literal>CHAR</literal>, <literal>VARCHAR</literal>,
+ <literal>TEXT</literal>, or <literal>BLOB</literal>) that
+ exceeds the column's maximum length. The value is truncated
+ to the column's maximum length.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Inserting a value into a date or time column that is illegal
+ for the data type. The column is set to the appropriate zero
+ value for the type.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <indexterm type="function">
+ <primary>mysql_info()</primary>
+ </indexterm>
+
+ <para>
+ If you are using the C API, the information string can be
+ obtained by invoking the <literal>mysql_info()</literal>
+ function. See <xref linkend="mysql-info"/>.
+ </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
@@ -4113,104 +4211,6 @@
<primary>mysql_insert_id()</primary>
</indexterm>
- <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
- this format:
- </para>
-
-<programlisting>
-Records: 100 Duplicates: 0 Warnings: 0
-</programlisting>
-
- <para>
- <literal>Records</literal> indicates the number of rows
- processed by the statement. (This is not necessarily the number
- of rows actually inserted because <literal>Duplicates</literal>
- can be non-zero.) <literal>Duplicates</literal> indicates the
- number of rows that could not be inserted because they would
- duplicate some existing unique index value.
- <literal>Warnings</literal> indicates the number of attempts to
- insert column values that were problematic in some way. Warnings
- can occur under any of the following conditions:
- </para>
-
- <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
- <literal>INSERT</literal> statements or <literal>INSERT INTO
- ... SELECT</literal> statements, the column is set to the
- implicit default value for the column data type. This is
- <literal>0</literal> for numeric types, the empty string
- (<literal>''</literal>) for string types, and the
- <quote>zero</quote> value for date and time types.
- <literal>INSERT INTO ... SELECT</literal> statements are
- handled the same way as multiple-row inserts because the
- server does not examine the result set from the
- <literal>SELECT</literal> to see whether it returns a single
- row. (For a single-row <literal>INSERT</literal>, no warning
- occurs when <literal>NULL</literal> is inserted into a
- <literal>NOT NULL</literal> column. Instead, the statement
- fails with an error.)
- </para>
- </listitem>
-
- <listitem>
- <para>
- Setting a numeric column to a value that lies outside the
- column's range. The value is clipped to the closest endpoint
- of the range.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Assigning a value such as <literal>'10.34 a'</literal> to a
- numeric column. The trailing non-numeric text is stripped
- off and the remaining numeric part is inserted. If the
- string value has no leading numeric part, the column is set
- to <literal>0</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Inserting a string into a string column
- (<literal>CHAR</literal>, <literal>VARCHAR</literal>,
- <literal>TEXT</literal>, or <literal>BLOB</literal>) that
- exceeds the column's maximum length. The value is truncated
- to the column's maximum length.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Inserting a value into a date or time column that is illegal
- for the data type. The column is set to the appropriate zero
- value for the type.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <indexterm type="function">
- <primary>mysql_info()</primary>
- </indexterm>
-
- <para>
- If you are using the C API, the information string can be
- obtained by invoking the <literal>mysql_info()</literal>
- function. See <xref linkend="mysql-info"/>.
- </para>
-
<section id="insert-select">
<title>&title-insert-select;</title>
@@ -4234,40 +4234,35 @@
<para>
With <literal>INSERT ... SELECT</literal>, you can quickly
- insert many rows into a table from one or many tables.
+ insert many rows into a table from one or many tables. For
+ example:
</para>
- <remark role="help-description-end"/>
-
- <para>
- For example:
- </para>
-
- <remark role="help-example"/>
-
<programlisting>
INSERT INTO tbl_temp2 (fld_id)
- SELECT tbl_temp1.fld_order_id
- FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
+ SELECT tbl_temp1.fld_order_id
+ FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
</programlisting>
+ <remark role="help-description-end"/>
+
<para>
- The following conditions hold for an <literal>INSERT ...
- SELECT</literal> statement:
+ The following conditions hold for a <literal>INSERT ...
+ SELECT</literal> statements:
</para>
<itemizedlist>
<listitem>
<para>
- Specify <literal>IGNORE</literal> explicitly to ignore
- rows that would cause duplicate-key violations.
+ Specify <literal>IGNORE</literal> to ignore rows that
+ would cause duplicate-key violations.
</para>
</listitem>
<listitem>
<para>
- Do not use <literal>DELAYED</literal> with <literal>INSERT
+ <literal>DELAYED</literal> is ignored with <literal>INSERT
... SELECT</literal>.
</para>
</listitem>
@@ -4291,7 +4286,7 @@
<para>
To ensure that the binary log can be used to re-create the
original tables, MySQL does not allow concurrent inserts
- during <literal>INSERT ... SELECT</literal>.
+ for <literal>INSERT ... SELECT</literal> statements.
</para>
</listitem>
@@ -4306,7 +4301,7 @@
<para>
In the values part of <literal>ON DUPLICATE KEY
- UPDATE</literal> you can refer to columns in other tables, as
+ UPDATE</literal>, you can refer to columns in other tables, as
long as you do not use <literal>GROUP BY</literal> in the
<literal>SELECT</literal> part. One side effect is that you
must qualify non-unique column names in the values part.
@@ -4348,11 +4343,11 @@
The <literal>DELAYED</literal> option for the
<literal>INSERT</literal> statement is a MySQL extension to
standard SQL that is very useful if you have clients that
- cannot wait for the <literal>INSERT</literal> to complete.
- This is a common problem when you use MySQL for logging and
- you also periodically run <literal>SELECT</literal> and
- <literal>UPDATE</literal> statements that take a long time to
- complete.
+ cannot or need not wait for the <literal>INSERT</literal> to
+ complete. This is a common situation when you use MySQL for
+ logging and you also periodically run
+ <literal>SELECT</literal> and <literal>UPDATE</literal>
+ statements that take a long time to complete.
</para>
<para>
Modified: trunk/refman-5.1/optimization.xml
===================================================================
--- trunk/refman-5.1/optimization.xml 2006-01-27 16:18:29 UTC (rev 1063)
+++ trunk/refman-5.1/optimization.xml 2006-01-27 16:38:21 UTC (rev 1064)
@@ -6463,6 +6463,12 @@
deleted rows in middle of the table.
</para>
+ <para>
+ To ensure that the binary log can be used to re-create the
+ original tables, MySQL does not allow concurrent inserts for
+ <literal>INSERT ... SELECT</literal> statements.
+ </para>
+
</section>
</section>
Modified: trunk/refman-5.1/sql-syntax.xml
===================================================================
--- trunk/refman-5.1/sql-syntax.xml 2006-01-27 16:18:29 UTC (rev 1063)
+++ trunk/refman-5.1/sql-syntax.xml 2006-01-27 16:38:21 UTC (rev 1064)
@@ -4467,10 +4467,10 @@
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
+ that does not name all the columns in the table, unnamed
columns are set to their default values. Default value
assignment is described in
- <xref linkend="data-type defaults"/>. See also
+ <xref linkend="data-type-defaults"/>. See also
<xref linkend="constraint-invalid-data"/>.
</para>
@@ -4609,6 +4609,104 @@
</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
+ this format:
+ </para>
+
+<programlisting>
+Records: 100 Duplicates: 0 Warnings: 0
+</programlisting>
+
+ <para>
+ <literal>Records</literal> indicates the number of rows
+ processed by the statement. (This is not necessarily the number
+ of rows actually inserted because <literal>Duplicates</literal>
+ can be non-zero.) <literal>Duplicates</literal> indicates the
+ number of rows that could not be inserted because they would
+ duplicate some existing unique index value.
+ <literal>Warnings</literal> indicates the number of attempts to
+ insert column values that were problematic in some way. Warnings
+ can occur under any of the following conditions:
+ </para>
+
+ <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
+ <literal>INSERT</literal> statements or <literal>INSERT INTO
+ ... SELECT</literal> statements, the column is set to the
+ implicit default value for the column data type. This is
+ <literal>0</literal> for numeric types, the empty string
+ (<literal>''</literal>) for string types, and the
+ <quote>zero</quote> value for date and time types.
+ <literal>INSERT INTO ... SELECT</literal> statements are
+ handled the same way as multiple-row inserts because the
+ server does not examine the result set from the
+ <literal>SELECT</literal> to see whether it returns a single
+ row. (For a single-row <literal>INSERT</literal>, no warning
+ occurs when <literal>NULL</literal> is inserted into a
+ <literal>NOT NULL</literal> column. Instead, the statement
+ fails with an error.)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Setting a numeric column to a value that lies outside the
+ column's range. The value is clipped to the closest endpoint
+ of the range.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Assigning a value such as <literal>'10.34 a'</literal> to a
+ numeric column. The trailing non-numeric text is stripped
+ off and the remaining numeric part is inserted. If the
+ string value has no leading numeric part, the column is set
+ to <literal>0</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Inserting a string into a string column
+ (<literal>CHAR</literal>, <literal>VARCHAR</literal>,
+ <literal>TEXT</literal>, or <literal>BLOB</literal>) that
+ exceeds the column's maximum length. The value is truncated
+ to the column's maximum length.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Inserting a value into a date or time column that is illegal
+ for the data type. The column is set to the appropriate zero
+ value for the type.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <indexterm type="function">
+ <primary>mysql_info()</primary>
+ </indexterm>
+
+ <para>
+ If you are using the C API, the information string can be
+ obtained by invoking the <literal>mysql_info()</literal>
+ function. See <xref linkend="mysql-info"/>.
+ </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
@@ -4721,104 +4819,6 @@
<primary>mysql_insert_id()</primary>
</indexterm>
- <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
- this format:
- </para>
-
-<programlisting>
-Records: 100 Duplicates: 0 Warnings: 0
-</programlisting>
-
- <para>
- <literal>Records</literal> indicates the number of rows
- processed by the statement. (This is not necessarily the number
- of rows actually inserted because <literal>Duplicates</literal>
- can be non-zero.) <literal>Duplicates</literal> indicates the
- number of rows that could not be inserted because they would
- duplicate some existing unique index value.
- <literal>Warnings</literal> indicates the number of attempts to
- insert column values that were problematic in some way. Warnings
- can occur under any of the following conditions:
- </para>
-
- <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
- <literal>INSERT</literal> statements or <literal>INSERT INTO
- ... SELECT</literal> statements, the column is set to the
- implicit default value for the column data type. This is
- <literal>0</literal> for numeric types, the empty string
- (<literal>''</literal>) for string types, and the
- <quote>zero</quote> value for date and time types.
- <literal>INSERT INTO ... SELECT</literal> statements are
- handled the same way as multiple-row inserts because the
- server does not examine the result set from the
- <literal>SELECT</literal> to see whether it returns a single
- row. (For a single-row <literal>INSERT</literal>, no warning
- occurs when <literal>NULL</literal> is inserted into a
- <literal>NOT NULL</literal> column. Instead, the statement
- fails with an error.)
- </para>
- </listitem>
-
- <listitem>
- <para>
- Setting a numeric column to a value that lies outside the
- column's range. The value is clipped to the closest endpoint
- of the range.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Assigning a value such as <literal>'10.34 a'</literal> to a
- numeric column. The trailing non-numeric text is stripped
- off and the remaining numeric part is inserted. If the
- string value has no leading numeric part, the column is set
- to <literal>0</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Inserting a string into a string column
- (<literal>CHAR</literal>, <literal>VARCHAR</literal>,
- <literal>TEXT</literal>, or <literal>BLOB</literal>) that
- exceeds the column's maximum length. The value is truncated
- to the column's maximum length.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Inserting a value into a date or time column that is illegal
- for the data type. The column is set to the appropriate zero
- value for the type.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <indexterm type="function">
- <primary>mysql_info()</primary>
- </indexterm>
-
- <para>
- If you are using the C API, the information string can be
- obtained by invoking the <literal>mysql_info()</literal>
- function. See <xref linkend="mysql-info"/>.
- </para>
-
<section id="insert-select">
<title>&title-insert-select;</title>
@@ -4842,40 +4842,35 @@
<para>
With <literal>INSERT ... SELECT</literal>, you can quickly
- insert many rows into a table from one or many tables.
+ insert many rows into a table from one or many tables. For
+ example:
</para>
- <remark role="help-description-end"/>
-
- <para>
- For example:
- </para>
-
- <remark role="help-example"/>
-
<programlisting>
INSERT INTO tbl_temp2 (fld_id)
- SELECT tbl_temp1.fld_order_id
- FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
+ SELECT tbl_temp1.fld_order_id
+ FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
</programlisting>
+ <remark role="help-description-end"/>
+
<para>
- The following conditions hold for an <literal>INSERT ...
- SELECT</literal> statement:
+ The following conditions hold for a <literal>INSERT ...
+ SELECT</literal> statements:
</para>
<itemizedlist>
<listitem>
<para>
- Specify <literal>IGNORE</literal> explicitly to ignore
- rows that would cause duplicate-key violations.
+ Specify <literal>IGNORE</literal> to ignore rows that
+ would cause duplicate-key violations.
</para>
</listitem>
<listitem>
<para>
- Do not use <literal>DELAYED</literal> with <literal>INSERT
+ <literal>DELAYED</literal> is ignored with <literal>INSERT
... SELECT</literal>.
</para>
</listitem>
@@ -4899,7 +4894,7 @@
<para>
To ensure that the binary log can be used to re-create the
original tables, MySQL does not allow concurrent inserts
- during <literal>INSERT ... SELECT</literal>.
+ for <literal>INSERT ... SELECT</literal> statements.
</para>
</listitem>
@@ -4914,7 +4909,7 @@
<para>
In the values part of <literal>ON DUPLICATE KEY
- UPDATE</literal> you can refer to columns in other tables, as
+ UPDATE</literal>, you can refer to columns in other tables, as
long as you do not use <literal>GROUP BY</literal> in the
<literal>SELECT</literal> part. One side effect is that you
must qualify non-unique column names in the values part.
@@ -4956,11 +4951,11 @@
The <literal>DELAYED</literal> option for the
<literal>INSERT</literal> statement is a MySQL extension to
standard SQL that is very useful if you have clients that
- cannot wait for the <literal>INSERT</literal> to complete.
- This is a common problem when you use MySQL for logging and
- you also periodically run <literal>SELECT</literal> and
- <literal>UPDATE</literal> statements that take a long time to
- complete.
+ cannot or need not wait for the <literal>INSERT</literal> to
+ complete. This is a common situation when you use MySQL for
+ logging and you also periodically run
+ <literal>SELECT</literal> and <literal>UPDATE</literal>
+ statements that take a long time to complete.
</para>
<para>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r1064 - in trunk: . refman-4.1 refman-5.0 refman-5.1 | paul | 27 Jan |
