Author: paul
Date: 2006-01-27 19:33:37 +0100 (Fri, 27 Jan 2006)
New Revision: 1067
Log:
r6761@frost: paul | 2006-01-27 12:33:27 -0600
General revisions.
Modified:
trunk/
trunk/refman-4.1/client-utility-programs.xml
trunk/refman-4.1/optimization.xml
trunk/refman-4.1/sql-syntax.xml
trunk/refman-5.0/client-utility-programs.xml
trunk/refman-5.0/optimization.xml
trunk/refman-5.0/sql-syntax.xml
trunk/refman-5.0/triggers.xml
trunk/refman-5.1/client-utility-programs.xml
trunk/refman-5.1/optimization.xml
trunk/refman-5.1/sql-syntax.xml
trunk/refman-5.1/triggers.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6759
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2564
+ b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6761
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2564
Modified: trunk/refman-4.1/client-utility-programs.xml
===================================================================
--- trunk/refman-4.1/client-utility-programs.xml 2006-01-27 18:00:24 UTC (rev 1066)
+++ trunk/refman-4.1/client-utility-programs.xml 2006-01-27 18:33:37 UTC (rev 1067)
@@ -8498,7 +8498,7 @@
<para>
This option takes a comma-separated list of column names
as its value. The order of the column names indicates how
- to match up data file columns with table columns.
+ to match data file columns with table columns.
</para>
</listitem>
Modified: trunk/refman-4.1/optimization.xml
===================================================================
--- trunk/refman-4.1/optimization.xml 2006-01-27 18:00:24 UTC (rev 1066)
+++ trunk/refman-4.1/optimization.xml 2006-01-27 18:33:37 UTC (rev 1067)
@@ -5001,12 +5001,30 @@
</para>
<para>
+ Under circumstances where concurrent inserts can be used, there
+ is seldom any need to use the <literal>DELAYED</literal>
+ modifier for <literal>INSERT</literal> statements.
+ <xref linkend="insert-delayed"/>.
+ </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>
+ <para>
+ With <literal>LOAD DATA INFILE</literal>, if you specify
+ <literal>CONCURRENT</literal> with a <literal>MyISAM</literal>
+ table that satisfies the condition for concurrent inserts (that
+ is, it contains no free blocks in the middle), other threads can
+ retrieve data from the table while <literal>LOAD DATA</literal>
+ is executing. Using this option affects the performance of
+ <literal>LOAD DATA</literal> a bit, even if no other thread is
+ using the table at the same time.
+ </para>
+
</section>
</section>
Modified: trunk/refman-4.1/sql-syntax.xml
===================================================================
--- trunk/refman-4.1/sql-syntax.xml 2006-01-27 18:00:24 UTC (rev 1066)
+++ trunk/refman-4.1/sql-syntax.xml 2006-01-27 18:33:37 UTC (rev 1067)
@@ -4446,6 +4446,25 @@
</para>
<para>
+ Note that <literal>INSERT DELAYED</literal> is slower than a
+ normal <literal>INSERT</literal> if the table is not otherwise
+ in use. There is also the additional overhead for the server
+ to handle a separate thread for each table for which there are
+ delayed rows. This means that you should use <literal>INSERT
+ DELAYED</literal> only when you are really sure that you need
+ it.
+ </para>
+
+ <para>
+ The queued rows are held only in memory until they are
+ inserted into the table. This means that if you terminate
+ <command>mysqld</command> forcibly (for example, with
+ <literal>kill -9</literal>) or if <command>mysqld</command>
+ dies unexpectedly, <emphasis>any queued rows that have not
+ been written to disk are lost</emphasis>.
+ </para>
+
+ <para>
There are some constraints on the use of
<literal>DELAYED</literal>:
</para>
@@ -4456,16 +4475,19 @@
<para>
<literal>INSERT DELAYED</literal> works only with
<literal>ISAM</literal>, <literal>MyISAM</literal>, and
- (beginning with MySQL 4.1) <literal>MEMORY</literal>. For
- <literal>MyISAM</literal> tables, if there are no free
+ (beginning with MySQL 4.1) <literal>MEMORY</literal>
+ tables. See <xref linkend="myisam-storage-engine"/>,
+ <xref linkend="memory-storage-engine"/>, and
+ <xref linkend="archive-storage-engine"/>.
+ </para>
+
+ <para>
+ For <literal>MyISAM</literal> tables, if there are no free
blocks in the middle of the data file, concurrent
<literal>SELECT</literal> and <literal>INSERT</literal>
statements are supported. Under these circumstances, you
very seldom need to use <literal>INSERT DELAYED</literal>
- with <literal>MyISAM</literal>. See
- <xref linkend="myisam-storage-engine"/>,
- <xref linkend="memory-storage-engine"/>, and
- <xref linkend="archive-storage-engine"/>.
+ with <literal>MyISAM</literal>.
</para>
</listitem>
@@ -4482,18 +4504,18 @@
<listitem>
<para>
The server ignores <literal>DELAYED</literal> for
- <literal>INSERT DELAYED ... ON DUPLICATE UPDATE</literal>
- statements.
+ <literal>INSERT ... SELECT</literal> or <literal>INSERT
+ ... ON DUPLICATE KEY UPDATE</literal> statements.
</para>
</listitem>
<listitem>
<para>
- Because the statement returns immediately before the rows
- are inserted, you cannot use
- <literal>LAST_INSERT_ID()</literal> to get the
- <literal>AUTO_INCREMENT</literal> value which the
- statement might generate.
+ Because the <literal>INSERT DELAYED</literal> statement
+ returns immediately, before the rows are inserted, you
+ cannot use <literal>LAST_INSERT_ID()</literal> to get the
+ <literal>AUTO_INCREMENT</literal> value that the statement
+ might generate.
</para>
</listitem>
@@ -4518,16 +4540,6 @@
<remark role="help-description-end"/>
<para>
- Note that currently the queued rows are held only in memory
- until they are inserted into the table. This means that if you
- terminate <command>mysqld</command> forcibly (for example,
- with <literal>kill -9</literal>) or if
- <command>mysqld</command> dies unexpectedly, <emphasis>any
- queued rows that have not been written to disk are
- lost</emphasis>.
- </para>
-
- <para>
The following describes in detail what happens when you use
the <literal>DELAYED</literal> option to
<literal>INSERT</literal> or <literal>REPLACE</literal>. In
@@ -4545,7 +4557,7 @@
When a thread executes a <literal>DELAYED</literal>
statement for a table, a handler thread is created to
process all <literal>DELAYED</literal> statements for the
- table, if no such handler previously exists.
+ table, if no such handler already exists.
</para>
</listitem>
@@ -4558,8 +4570,8 @@
other threads have a <literal>READ</literal> or
<literal>WRITE</literal> lock on the table. However, the
handler waits for all <literal>ALTER TABLE</literal> locks
- or <literal>FLUSH TABLES</literal> to ensure that the
- table structure is up to date.
+ or <literal>FLUSH TABLES</literal> statements to finish,
+ to ensure that the table structure is up to date.
</para>
</listitem>
@@ -4638,7 +4650,8 @@
<literal>delayed_insert</literal> in the
<literal>Command</literal> column. It is killed if you
execute a <literal>FLUSH TABLES</literal> statement or
- kill it with <literal>KILL thread_id</literal>. However,
+ kill it with <literal>KILL
+ <replaceable>thread_id</replaceable></literal>. However,
before exiting, it first stores all queued rows into the
table. During this time it does not accept any new
<literal>INSERT</literal> statements from other threads.
@@ -4653,8 +4666,9 @@
<literal>INSERT DELAYED</literal> handler running. Other
update statements have to wait until the <literal>INSERT
DELAYED</literal> queue is empty, someone terminates the
- handler thread (with <literal>KILL thread_id</literal>),
- or someone executes a <literal>FLUSH TABLES</literal>.
+ handler thread (with <literal>KILL
+ <replaceable>thread_id</replaceable></literal>), or
+ someone executes a <literal>FLUSH TABLES</literal>.
</para>
</listitem>
@@ -4698,16 +4712,6 @@
</itemizedlist>
- <para>
- Note that <literal>INSERT DELAYED</literal> is slower than a
- normal <literal>INSERT</literal> if the table is not in use.
- There is also the additional overhead for the server to handle
- a separate thread for each table for which there are delayed
- rows. This means that you should use <literal>INSERT
- DELAYED</literal> only when you are really sure that you need
- it.
- </para>
-
</section>
<section id="insert-on-duplicate">
@@ -4815,13 +4819,13 @@
<remark role="help-syntax"/>
<programlisting>
-LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE '<replaceable>file_name</replaceable>.txt'
+LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE '<replaceable>file_name</replaceable>'
[REPLACE | IGNORE]
INTO TABLE <replaceable>tbl_name</replaceable>
[FIELDS
[TERMINATED BY '<replaceable>string</replaceable>']
[[OPTIONALLY] ENCLOSED BY '<replaceable>char</replaceable>']
- [ESCAPED BY '<replaceable>char</replaceable>' ]
+ [ESCAPED BY '<replaceable>char</replaceable>']
]
[LINES
[STARTING BY '<replaceable>string</replaceable>']
@@ -4840,6 +4844,14 @@
</para>
<para>
+ The syntax for the <literal>FIELDS</literal> and
+ <literal>LINES</literal> clauses also applies to the
+ <literal>SELECT ... INTO OUTFILE</literal> statement, as
+ described later in this section. (See also
+ <xref linkend="select"/>.)
+ </para>
+
+ <para>
For more information about the efficiency of
<literal>INSERT</literal> versus <literal>LOAD DATA
INFILE</literal> and speeding up <literal>LOAD DATA
@@ -4856,8 +4868,8 @@
</para>
<para>
- Note that it's currently not possible to load
- <literal>UCS2</literal> data files.
+ Note that it is currently not possible to load data files that
+ use the <literal>ucs2</literal> character set.
</para>
<indexterm>
@@ -4893,8 +4905,8 @@
</para>
<para>
- If <literal>LOCAL</literal> is specified, it is interpreted with
- respect to the client end of the connection:
+ The <literal>LOCAL</literal> keyword, if specified, is
+ interpreted with respect to the client end of the connection:
</para>
<itemizedlist>
@@ -4908,76 +4920,73 @@
name is interpreted relative to the directory in which the
client program was started.
</para>
+
+ <para>
+ <literal>LOCAL</literal> is available in MySQL 3.22.6 or
+ later.
+ </para>
</listitem>
<listitem>
<para>
If <literal>LOCAL</literal> is not specified, the file must
be located on the server host and is read directly by the
- server.
+ server. The server uses the following rules to locate the
+ file:
</para>
- </listitem>
- </itemizedlist>
+ <itemizedlist>
- <para>
- <literal>LOCAL</literal> is available in MySQL 3.22.6 or later.
- </para>
+ <listitem>
+ <para>
+ If the filename is an absolute pathname, the server uses
+ it as given.
+ </para>
+ </listitem>
- <para>
- When locating files on the server host, the server uses the
- following rules:
- </para>
+ <listitem>
+ <para>
+ If the filename is a relative pathname with one or more
+ leading components, the server searches for the file
+ relative to the server's data directory.
+ </para>
+ </listitem>
- <itemizedlist>
+ <listitem>
+ <para>
+ If a filename with no leading components is given, the
+ server looks for the file in the database directory of
+ the default database.
+ </para>
+ </listitem>
- <listitem>
- <para>
- If an absolute pathname is given, the server uses the
- pathname as is.
- </para>
+ </itemizedlist>
</listitem>
- <listitem>
- <para>
- If a relative pathname with one or more leading components
- is given, the server searches for the file relative to the
- server's data directory.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If a filename with no leading components is given, the
- server looks for the file in the database directory of the
- default database.
- </para>
- </listitem>
-
</itemizedlist>
<para>
- Note that these rules mean that a file named as
- <filename>./myfile.txt</filename> is read from the server's data
- directory, whereas the same file named as
+ Note that, in the non-<literal>LOCAL</literal> case, these rules
+ mean that a file named as <filename>./myfile.txt</filename> is
+ read from the server's data directory, whereas the file named as
<filename>myfile.txt</filename> is read from the database
- directory of the default database. For example, the following
+ directory of the default database. For example, if
+ <literal>db1</literal> is the default database, the following
<literal>LOAD DATA</literal> statement reads the file
<filename>data.txt</filename> from the database directory for
- <literal>db1</literal> because <literal>db1</literal> is the
- default database, even though the statement explicitly loads the
- file into a table in the <literal>db2</literal> database:
+ <literal>db1</literal>, even though the statement explicitly
+ loads the file into a table in the <literal>db2</literal>
+ database:
</para>
<programlisting>
-mysql> <userinput>USE db1;</userinput>
-mysql> <userinput>LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;</userinput>
+LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;
</programlisting>
<para>
- Note that Windows pathnames are specified using forward slashes
- rather than backslashes. If you do use backslashes, you must
- double them.
+ Windows pathnames are specified using forward slashes rather
+ than backslashes. If you do use backslashes, you must double
+ them.
</para>
<para>
@@ -4985,16 +4994,13 @@
server, the files must either reside in the database directory
or be readable by all. Also, to use <literal>LOAD DATA
INFILE</literal> on server files, you must have the
- <literal>FILE</literal> privilege.
+ <literal>FILE</literal> privilege. See
+ <xref linkend="privileges-provided"/>.
</para>
<remark role="help-description-end"/>
<para>
- See <xref linkend="privileges-provided"/>.
- </para>
-
- <para>
Using <literal>LOCAL</literal> is a bit slower than letting the
server access the files directly, because the contents of the
file must be sent over the connection by the client to the
@@ -5012,9 +5018,9 @@
</para>
<para>
- If you need <literal>LOAD DATA</literal> to read from a pipe,
- you can use the following technique (here we load the listing of
- the <filename>/</filename> directory into a table):
+ On Unix, if you need <literal>LOAD DATA</literal> to read from a
+ pipe, you can use the following technique (here we load the
+ listing of the <filename>/</filename> directory into a table):
</para>
<programlisting>
@@ -5050,8 +5056,8 @@
<para>
If you specify <literal>REPLACE</literal>, input rows replace
- existing rows (in other words, rows that have the same value for
- a primary key or unique index as an existing row). See
+ existing rows. In other words, rows that have the same value for
+ a primary key or unique index as an existing row. See
<xref linkend="replace"/>.
</para>
@@ -5079,14 +5085,13 @@
If you use <literal>LOAD DATA INFILE</literal> on an empty
<literal>MyISAM</literal> table, all non-unique indexes are
created in a separate batch (as for <literal>REPAIR
- TABLE</literal>). This normally makes <literal>LOAD DATA
- INFILE</literal> much faster when you have many indexes.
- Normally this is very fast, but in some extreme cases, you can
- create the indexes even faster by turning them off with
- <literal>ALTER TABLE ... DISABLE KEYS</literal> before loading
- the file into the table and using <literal>ALTER TABLE ...
- ENABLE KEYS</literal> to re-create the indexes after loading the
- file. See <xref linkend="insert-speed"/>.
+ TABLE</literal>). Normally, this makes <literal>LOAD DATA
+ INFILE</literal> much faster when you have many indexes. In some
+ extreme cases, you can create the indexes even faster by turning
+ them off with <literal>ALTER TABLE ... DISABLE KEYS</literal>
+ before loading the file into the table and using <literal>ALTER
+ TABLE ... ENABLE KEYS</literal> to re-create the indexes after
+ loading the file. See <xref linkend="insert-speed"/>.
</para>
<para>
@@ -5111,8 +5116,8 @@
</para>
<para>
- If you do not specify a <literal>FIELDS</literal> clause, the
- defaults are the same as if you had written this:
+ If you specify no <literal>FIELDS</literal> clause, the defaults
+ are the same as if you had written this:
</para>
<programlisting>
@@ -5120,8 +5125,8 @@
</programlisting>
<para>
- If you do not specify a <literal>LINES</literal> clause, the
- default is the same as if you had written this:
+ If you specify no <literal>LINES</literal> clause, the defaults
+ are the same as if you had written this:
</para>
<programlisting>
@@ -5207,8 +5212,9 @@
</itemizedlist>
<para>
- Note that to write <literal>FIELDS ESCAPED BY '\\'</literal>,
- you must specify two backslashes for the value to be read as a
+ Backslash is the MySQL escape character within strings, so to
+ write <literal>FIELDS ESCAPED BY '\\'</literal>, you must
+ specify two backslashes for the value to be interpreted as a
single backslash.
</para>
@@ -5227,34 +5233,30 @@
If all the lines you want to read in have a common prefix that
you want to ignore, you can use <literal>LINES STARTING BY
'<replaceable>prefix_string</replaceable>'</literal> to skip
- over the prefix (and anything before it). If a line does not
- include the prefix, the entire line is skipped.
- <emphasis role="bold">Note</emphasis>:
- <replaceable>prefix_string</replaceable> may occur in the middle
- of a line.
+ over the prefix, <emphasis>and anything before it</emphasis>. If
+ a line does not include the prefix, the entire line is skipped.
+ Suppose that you issue the following statement:
</para>
- <para>
- Example:
- </para>
-
<programlisting>
-mysql> <userinput>LOAD DATA INFILE '/tmp/test.txt'</userinput>
- -> <userinput>INTO TABLE test LINES STARTING BY "xxx";</userinput>
+LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
+ FIELDS TERMINATED BY ',' LINES STARTING BY 'xxx';
</programlisting>
<para>
- With this you can read in a file that contains something like:
+ If the data file looks like this:
</para>
<programlisting>
-xxx"row",1
-something xxx"row",2
+xxx"abc",1
+something xxx"def",2
+"ghi",3
</programlisting>
<para>
- And just get the data <literal>("row",1)</literal> and
- <literal>("row",2)</literal>.
+ The resulting rows will be <literal>("abc",1)</literal> and
+ <literal>("def",2)</literal>. The third row in the file will be
+ skipped because it does not contain the prefix.
</para>
<para>
@@ -5266,8 +5268,7 @@
</para>
<programlisting>
-mysql> <userinput>LOAD DATA INFILE '/tmp/test.txt'</userinput>
- -> <userinput>INTO TABLE test IGNORE 1 LINES;</userinput>
+LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;
</programlisting>
<para>
@@ -5276,16 +5277,16 @@
from a database into a file and then read the file back into the
database later, the field- and line-handling options for both
statements must match. Otherwise, <literal>LOAD DATA
- INFILE</literal> does not interpret the contents of the file
+ INFILE</literal> will not interpret the contents of the file
properly. Suppose that you use <literal>SELECT ... INTO
OUTFILE</literal> to write a file with fields delimited by
commas:
</para>
<programlisting>
-mysql> <userinput>SELECT * INTO OUTFILE 'data.txt'</userinput>
- -> <userinput>FIELDS TERMINATED BY ','</userinput>
- -> <userinput>FROM table2;</userinput>
+SELECT * INTO OUTFILE 'data.txt'
+ FIELDS TERMINATED BY ','
+ FROM table2;
</programlisting>
<para>
@@ -5294,19 +5295,20 @@
</para>
<programlisting>
-mysql> <userinput>LOAD DATA INFILE 'data.txt' INTO TABLE table2</userinput>
- -> <userinput>FIELDS TERMINATED BY ',';</userinput>
+LOAD DATA INFILE 'data.txt' INTO TABLE table2
+ FIELDS TERMINATED BY ',';
</programlisting>
<para>
If instead you tried to read in the file with the statement
- shown here, it wouldn't work because it instructs <literal>LOAD
- DATA INFILE</literal> to look for tabs between fields:
+ shown following, it wouldn't work because it instructs
+ <literal>LOAD DATA INFILE</literal> to look for tabs between
+ fields:
</para>
<programlisting>
-mysql> <userinput>LOAD DATA INFILE 'data.txt' INTO TABLE table2</userinput>
- -> <userinput>FIELDS TERMINATED BY '\t';</userinput>
+LOAD DATA INFILE 'data.txt' INTO TABLE table2
+ FIELDS TERMINATED BY '\t';
</programlisting>
<para>
@@ -5314,19 +5316,28 @@
as a single field.
</para>
+ <indexterm>
+ <primary>CSV data, reading</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>comma-separate values data, reading</primary>
+ </indexterm>
+
<para>
<literal>LOAD DATA INFILE</literal> can be used to read files
- obtained from external sources, too. For example, a file in
- dBASE format has fields separated by commas and enclosed within
- double quotes. If lines in the file are terminated by newlines,
- the statement shown here illustrates the field- and
- line-handling options you would use to load the file:
+ obtained from external sources. For example, many programs can
+ export data in comma-separate values (CSV) format, such that
+ lines have fields separated by commas and enclosed within double
+ quotes. If lines in such a file are terminated by newlines, the
+ statement shown here illustrates the field- and line-handling
+ options you would use to load the file:
</para>
<programlisting>
-mysql> <userinput>LOAD DATA INFILE 'data.txt' INTO TABLE <replaceable>tbl_name</replaceable></userinput>
- -> <userinput>FIELDS TERMINATED BY ',' ENCLOSED BY '"'</userinput>
- -> <userinput>LINES TERMINATED BY '\n';</userinput>
+LOAD DATA INFILE 'data.txt' INTO TABLE <replaceable>tbl_name</replaceable>
+ FIELDS TERMINATED BY ',' ENCLOSED BY '"'
+ LINES TERMINATED BY '\n';
</programlisting>
<para>
@@ -5348,12 +5359,12 @@
</para>
<programlisting>
-mysql> <userinput>CREATE TABLE jokes</userinput>
- -> <userinput>(a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,</userinput>
- -> <userinput>joke TEXT NOT NULL);</userinput>
-mysql> <userinput>LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes</userinput>
- -> <userinput>FIELDS TERMINATED BY ''</userinput>
- -> <userinput>LINES TERMINATED BY '\n%%\n' (joke);</userinput>
+CREATE TABLE jokes
+ (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+ joke TEXT NOT NULL);
+LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes
+ FIELDS TERMINATED BY ''
+ LINES TERMINATED BY '\n%%\n' (joke);
</programlisting>
<para>
@@ -5375,7 +5386,7 @@
<para>
If you specify <literal>OPTIONALLY</literal>, the
<literal>ENCLOSED BY</literal> character is used only to enclose
- values in columns that have a string data type (such as
+ values from columns that have a string data type (such as
<literal>CHAR</literal>, <literal>BINARY</literal>,
<literal>TEXT</literal>, or <literal>ENUM</literal>):
</para>
@@ -5392,12 +5403,12 @@
character within a field value are escaped by prefixing them
with the <literal>ESCAPED BY</literal> character. Also note that
if you specify an empty <literal>ESCAPED BY</literal> value, it
- is possible to generate output that cannot be read properly by
- <literal>LOAD DATA INFILE</literal>. For example, the preceding
- output just shown would appear as follows if the escape
- character is empty. Observe that the second field in the fourth
- line contains a comma following the quote, which (erroneously)
- appears to terminate the field:
+ is possible to inadvertently generate output that cannot be read
+ properly by <literal>LOAD DATA INFILE</literal>. For example,
+ the preceding output just shown would appear as follows if the
+ escape character is empty. Observe that the second field in the
+ fourth line contains a comma following the quote, which
+ (erroneously) appears to terminate the field:
</para>
<programlisting>
@@ -5410,8 +5421,8 @@
<para>
For input, the <literal>ENCLOSED BY</literal> character, if
present, is stripped from the ends of field values. (This is
- true whether or not <literal>OPTIONALLY</literal> is specified;
- <literal>OPTIONALLY</literal> has no effect on input
+ true regardless of whether <literal>OPTIONALLY</literal> is
+ specified; <literal>OPTIONALLY</literal> has no effect on input
interpretation.) Occurrences of the <literal>ENCLOSED
BY</literal> character preceded by the <literal>ESCAPED
BY</literal> character are interpreted as part of the current
@@ -5593,7 +5604,7 @@
<listitem>
<para>
- With fixed-row format (which happens when <literal>FIELDS
+ With fixed-row format (which is used when <literal>FIELDS
TERMINATED BY</literal> and <literal>FIELDS ENCLOSED
BY</literal> are both empty), <literal>NULL</literal> is
written as an empty string. Note that this causes both
@@ -5657,7 +5668,7 @@
</para>
<programlisting>
-mysql> <userinput>LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;</userinput>
+LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;
</programlisting>
<para>
@@ -5668,14 +5679,13 @@
</para>
<programlisting>
-mysql> <userinput>LOAD DATA INFILE 'persondata.txt'</userinput>
- -> <userinput>INTO TABLE persondata (col1,col2,...);</userinput>
+LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);
</programlisting>
<para>
You must also specify a column list if the order of the fields
in the input file differs from the order of the columns in the
- table. Otherwise, MySQL cannot tell how to match up input fields
+ table. Otherwise, MySQL cannot tell how to match input fields
with table columns.
</para>
@@ -5688,7 +5698,7 @@
If an input line has too few fields, the table columns for which
input fields are missing are set to their default values.
Default value assignment is described in
- <xref linkend="create-table"/>.
+ <xref linkend="data-type-defaults"/>.
</para>
<para>
@@ -5728,20 +5738,13 @@
<literal>UPDATE</literal> statement.
</para>
- <remark role="todo">
- with the new DEFAULT ... ON UPDATE syntax for declaring
- TIMESTAMP columns, this should be amended to reflect that it's
- not necessarily the *first* TIMESTAMP that is set to current
- date and time, it's the one with DEFAULT NOW.
- </remark>
-
<para>
<literal>TIMESTAMP</literal> columns are set to the current date
and time only if there is a <literal>NULL</literal> value for
- the column (that is, <literal>\N</literal>), or (for the first
- <literal>TIMESTAMP</literal> column only) if the
- <literal>TIMESTAMP</literal> column is omitted from the field
- list when a field list is specified.
+ the column (that is, <literal>\N</literal>), or if the
+ <literal>TIMESTAMP</literal> column's default value is the
+ current timestamp and it is omitted from the field list when a
+ field list is specified.
</para>
<para>
@@ -12802,12 +12805,13 @@
You cannot use <literal>ONE_SHOT</literal> with other than
the allowed set of variables; if you try, you get an error
like this:
+ </para>
<programlisting>
mysql> <userinput>SET ONE_SHOT max_allowed_packet = 1;</userinput>
-ERROR 1382 (HY000): The 'SET ONE_SHOT' syntax is reserved for purposes internal to the MySQL server
+ERROR 1382 (HY000): The 'SET ONE_SHOT' syntax is reserved for purposes
+internal to the MySQL server
</programlisting>
- </para>
<para>
If <literal>ONE_SHOT</literal> is used with the allowed
@@ -12818,6 +12822,7 @@
next statement is a <literal>SET</literal> statement. In
other words, resetting takes place after the next
non-<literal>SET</literal> statement. Example:
+ </para>
<programlisting>
mysql> <userinput>SET ONE_SHOT character_set_connection = latin5;</userinput>
@@ -12840,7 +12845,6 @@
| collation_connection | latin1_swedish_ci |
+--------------------------+-------------------+
</programlisting>
- </para>
</listitem>
<listitem>
Modified: trunk/refman-5.0/client-utility-programs.xml
===================================================================
--- trunk/refman-5.0/client-utility-programs.xml 2006-01-27 18:00:24 UTC (rev 1066)
+++ trunk/refman-5.0/client-utility-programs.xml 2006-01-27 18:33:37 UTC (rev 1067)
@@ -8651,7 +8651,7 @@
<para>
This option takes a comma-separated list of column names
as its value. The order of the column names indicates how
- to match up data file columns with table columns.
+ to match data file columns with table columns.
</para>
</listitem>
Modified: trunk/refman-5.0/optimization.xml
===================================================================
--- trunk/refman-5.0/optimization.xml 2006-01-27 18:00:24 UTC (rev 1066)
+++ trunk/refman-5.0/optimization.xml 2006-01-27 18:33:37 UTC (rev 1067)
@@ -6456,11 +6456,29 @@
</para>
<para>
+ Under circumstances where concurrent inserts can be used, there
+ is seldom any need to use the <literal>DELAYED</literal>
+ modifier for <literal>INSERT</literal> statements.
+ <xref linkend="insert-delayed"/>.
+ </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>
+ <para>
+ With <literal>LOAD DATA INFILE</literal>, if you specify
+ <literal>CONCURRENT</literal> with a <literal>MyISAM</literal>
+ table that satisfies the condition for concurrent inserts (that
+ is, it contains no free blocks in the middle), other threads can
+ retrieve data from the table while <literal>LOAD DATA</literal>
+ is executing. Using this option affects the performance of
+ <literal>LOAD DATA</literal> a bit, even if no other thread is
+ using the table at the same time.
+ </para>
+
</section>
</section>
Modified: trunk/refman-5.0/sql-syntax.xml
===================================================================
--- trunk/refman-5.0/sql-syntax.xml 2006-01-27 18:00:24 UTC (rev 1066)
+++ trunk/refman-5.0/sql-syntax.xml 2006-01-27 18:33:37 UTC (rev 1067)
@@ -4364,6 +4364,25 @@
</para>
<para>
+ Note that <literal>INSERT DELAYED</literal> is slower than a
+ normal <literal>INSERT</literal> if the table is not otherwise
+ in use. There is also the additional overhead for the server
+ to handle a separate thread for each table for which there are
+ delayed rows. This means that you should use <literal>INSERT
+ DELAYED</literal> only when you are really sure that you need
+ it.
+ </para>
+
+ <para>
+ The queued rows are held only in memory until they are
+ inserted into the table. This means that if you terminate
+ <command>mysqld</command> forcibly (for example, with
+ <literal>kill -9</literal>) or if <command>mysqld</command>
+ dies unexpectedly, <emphasis>any queued rows that have not
+ been written to disk are lost</emphasis>.
+ </para>
+
+ <para>
There are some constraints on the use of
<literal>DELAYED</literal>:
</para>
@@ -4374,16 +4393,19 @@
<para>
<literal>INSERT DELAYED</literal> works only with
<literal>MyISAM</literal>, <literal>MEMORY</literal>, and
- <literal>ARCHIVE</literal> tables. For
- <literal>MyISAM</literal> tables, if there are no free
+ <literal>ARCHIVE</literal> tables. See
+ <xref linkend="myisam-storage-engine"/>,
+ <xref linkend="memory-storage-engine"/>, and
+ <xref linkend="archive-storage-engine"/>.
+ </para>
+
+ <para>
+ For <literal>MyISAM</literal> tables, if there are no free
blocks in the middle of the data file, concurrent
<literal>SELECT</literal> and <literal>INSERT</literal>
statements are supported. Under these circumstances, you
very seldom need to use <literal>INSERT DELAYED</literal>
- with <literal>MyISAM</literal>. See
- <xref linkend="myisam-storage-engine"/>,
- <xref linkend="memory-storage-engine"/>, and
- <xref linkend="archive-storage-engine"/>.
+ with <literal>MyISAM</literal>.
</para>
</listitem>
@@ -4399,18 +4421,18 @@
<listitem>
<para>
The server ignores <literal>DELAYED</literal> for
- <literal>INSERT DELAYED ... ON DUPLICATE UPDATE</literal>
- statements.
+ <literal>INSERT ... SELECT</literal> or <literal>INSERT
+ ... ON DUPLICATE KEY UPDATE</literal> statements.
</para>
</listitem>
<listitem>
<para>
- Because the statement returns immediately before the rows
- are inserted, you cannot use
- <literal>LAST_INSERT_ID()</literal> to get the
- <literal>AUTO_INCREMENT</literal> value which the
- statement might generate.
+ Because the <literal>INSERT DELAYED</literal> statement
+ returns immediately, before the rows are inserted, you
+ cannot use <literal>LAST_INSERT_ID()</literal> to get the
+ <literal>AUTO_INCREMENT</literal> value that the statement
+ might generate.
</para>
</listitem>
@@ -4435,16 +4457,6 @@
<remark role="help-description-end"/>
<para>
- Note that currently the queued rows are held only in memory
- until they are inserted into the table. This means that if you
- terminate <command>mysqld</command> forcibly (for example,
- with <literal>kill -9</literal>) or if
- <command>mysqld</command> dies unexpectedly, <emphasis>any
- queued rows that have not been written to disk are
- lost</emphasis>.
- </para>
-
- <para>
The following describes in detail what happens when you use
the <literal>DELAYED</literal> option to
<literal>INSERT</literal> or <literal>REPLACE</literal>. In
@@ -4462,7 +4474,7 @@
When a thread executes a <literal>DELAYED</literal>
statement for a table, a handler thread is created to
process all <literal>DELAYED</literal> statements for the
- table, if no such handler previously exists.
+ table, if no such handler already exists.
</para>
</listitem>
@@ -4475,8 +4487,8 @@
other threads have a <literal>READ</literal> or
<literal>WRITE</literal> lock on the table. However, the
handler waits for all <literal>ALTER TABLE</literal> locks
- or <literal>FLUSH TABLES</literal> to ensure that the
- table structure is up to date.
+ or <literal>FLUSH TABLES</literal> statements to finish,
+ to ensure that the table structure is up to date.
</para>
</listitem>
@@ -4555,7 +4567,8 @@
<literal>delayed_insert</literal> in the
<literal>Command</literal> column. It is killed if you
execute a <literal>FLUSH TABLES</literal> statement or
- kill it with <literal>KILL thread_id</literal>. However,
+ kill it with <literal>KILL
+ <replaceable>thread_id</replaceable></literal>. However,
before exiting, it first stores all queued rows into the
table. During this time it does not accept any new
<literal>INSERT</literal> statements from other threads.
@@ -4570,8 +4583,9 @@
<literal>INSERT DELAYED</literal> handler running. Other
update statements have to wait until the <literal>INSERT
DELAYED</literal> queue is empty, someone terminates the
- handler thread (with <literal>KILL thread_id</literal>),
- or someone executes a <literal>FLUSH TABLES</literal>.
+ handler thread (with <literal>KILL
+ <replaceable>thread_id</replaceable></literal>), or
+ someone executes a <literal>FLUSH TABLES</literal>.
</para>
</listitem>
@@ -4615,16 +4629,6 @@
</itemizedlist>
- <para>
- Note that <literal>INSERT DELAYED</literal> is slower than a
- normal <literal>INSERT</literal> if the table is not in use.
- There is also the additional overhead for the server to handle
- a separate thread for each table for which there are delayed
- rows. This means that you should use <literal>INSERT
- DELAYED</literal> only when you are really sure that you need
- it.
- </para>
-
</section>
<section id="insert-on-duplicate">
@@ -4732,13 +4736,13 @@
<remark role="help-syntax"/>
<programlisting>
-LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE '<replaceable>file_name</replaceable>.txt'
+LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE '<replaceable>file_name</replaceable>'
[REPLACE | IGNORE]
INTO TABLE <replaceable>tbl_name</replaceable>
[FIELDS
[TERMINATED BY '<replaceable>string</replaceable>']
[[OPTIONALLY] ENCLOSED BY '<replaceable>char</replaceable>']
- [ESCAPED BY '<replaceable>char</replaceable>' ]
+ [ESCAPED BY '<replaceable>char</replaceable>']
]
[LINES
[STARTING BY '<replaceable>string</replaceable>']
@@ -4758,6 +4762,14 @@
</para>
<para>
+ The syntax for the <literal>FIELDS</literal> and
+ <literal>LINES</literal> clauses also applies to the
+ <literal>SELECT ... INTO OUTFILE</literal> statement, as
+ described later in this section. (See also
+ <xref linkend="select"/>.)
+ </para>
+
+ <para>
For more information about the efficiency of
<literal>INSERT</literal> versus <literal>LOAD DATA
INFILE</literal> and speeding up <literal>LOAD DATA
@@ -4774,8 +4786,8 @@
</para>
<para>
- Note that it's currently not possible to load
- <literal>UCS2</literal> data files.
+ Note that it is currently not possible to load data files that
+ use the <literal>ucs2</literal> character set.
</para>
<indexterm>
@@ -4811,8 +4823,8 @@
</para>
<para>
- If <literal>LOCAL</literal> is specified, it is interpreted with
- respect to the client end of the connection:
+ The <literal>LOCAL</literal> keyword, if specified, is
+ interpreted with respect to the client end of the connection:
</para>
<itemizedlist>
@@ -4832,66 +4844,62 @@
<para>
If <literal>LOCAL</literal> is not specified, the file must
be located on the server host and is read directly by the
- server.
+ server. The server uses the following rules to locate the
+ file:
</para>
- </listitem>
- </itemizedlist>
+ <itemizedlist>
- <para>
- When locating files on the server host, the server uses the
- following rules:
- </para>
+ <listitem>
+ <para>
+ If the filename is an absolute pathname, the server uses
+ it as given.
+ </para>
+ </listitem>
- <itemizedlist>
+ <listitem>
+ <para>
+ If the filename is a relative pathname with one or more
+ leading components, the server searches for the file
+ relative to the server's data directory.
+ </para>
+ </listitem>
- <listitem>
- <para>
- If an absolute pathname is given, the server uses the
- pathname as is.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ If a filename with no leading components is given, the
+ server looks for the file in the database directory of
+ the default database.
+ </para>
+ </listitem>
- <listitem>
- <para>
- If a relative pathname with one or more leading components
- is given, the server searches for the file relative to the
- server's data directory.
- </para>
+ </itemizedlist>
</listitem>
- <listitem>
- <para>
- If a filename with no leading components is given, the
- server looks for the file in the database directory of the
- default database.
- </para>
- </listitem>
-
</itemizedlist>
<para>
- Note that these rules mean that a file named as
- <filename>./myfile.txt</filename> is read from the server's data
- directory, whereas the same file named as
+ Note that, in the non-<literal>LOCAL</literal> case, these rules
+ mean that a file named as <filename>./myfile.txt</filename> is
+ read from the server's data directory, whereas the file named as
<filename>myfile.txt</filename> is read from the database
- directory of the default database. For example, the following
+ directory of the default database. For example, if
+ <literal>db1</literal> is the default database, the following
<literal>LOAD DATA</literal> statement reads the file
<filename>data.txt</filename> from the database directory for
- <literal>db1</literal> because <literal>db1</literal> is the
- default database, even though the statement explicitly loads the
- file into a table in the <literal>db2</literal> database:
+ <literal>db1</literal>, even though the statement explicitly
+ loads the file into a table in the <literal>db2</literal>
+ database:
</para>
<programlisting>
-mysql> <userinput>USE db1;</userinput>
-mysql> <userinput>LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;</userinput>
+LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;
</programlisting>
<para>
- Note that Windows pathnames are specified using forward slashes
- rather than backslashes. If you do use backslashes, you must
- double them.
+ Windows pathnames are specified using forward slashes rather
+ than backslashes. If you do use backslashes, you must double
+ them.
</para>
<para>
@@ -4899,16 +4907,13 @@
server, the files must either reside in the database directory
or be readable by all. Also, to use <literal>LOAD DATA
INFILE</literal> on server files, you must have the
- <literal>FILE</literal> privilege.
+ <literal>FILE</literal> privilege. See
+ <xref linkend="privileges-provided"/>.
</para>
<remark role="help-description-end"/>
<para>
- See <xref linkend="privileges-provided"/>.
- </para>
-
- <para>
Using <literal>LOCAL</literal> is a bit slower than letting the
server access the files directly, because the contents of the
file must be sent over the connection by the client to the
@@ -4925,9 +4930,9 @@
</para>
<para>
- If you need <literal>LOAD DATA</literal> to read from a pipe,
- you can use the following technique (here we load the listing of
- the <filename>/</filename> directory into a table):
+ On Unix, if you need <literal>LOAD DATA</literal> to read from a
+ pipe, you can use the following technique (here we load the
+ listing of the <filename>/</filename> directory into a table):
</para>
<programlisting>
@@ -4945,8 +4950,8 @@
<para>
If you specify <literal>REPLACE</literal>, input rows replace
- existing rows (in other words, rows that have the same value for
- a primary key or unique index as an existing row). See
+ existing rows. In other words, rows that have the same value for
+ a primary key or unique index as an existing row. See
<xref linkend="replace"/>.
</para>
@@ -4974,14 +4979,13 @@
If you use <literal>LOAD DATA INFILE</literal> on an empty
<literal>MyISAM</literal> table, all non-unique indexes are
created in a separate batch (as for <literal>REPAIR
- TABLE</literal>). This normally makes <literal>LOAD DATA
- INFILE</literal> much faster when you have many indexes.
- Normally this is very fast, but in some extreme cases, you can
- create the indexes even faster by turning them off with
- <literal>ALTER TABLE ... DISABLE KEYS</literal> before loading
- the file into the table and using <literal>ALTER TABLE ...
- ENABLE KEYS</literal> to re-create the indexes after loading the
- file. See <xref linkend="insert-speed"/>.
+ TABLE</literal>). Normally, this makes <literal>LOAD DATA
+ INFILE</literal> much faster when you have many indexes. In some
+ extreme cases, you can create the indexes even faster by turning
+ them off with <literal>ALTER TABLE ... DISABLE KEYS</literal>
+ before loading the file into the table and using <literal>ALTER
+ TABLE ... ENABLE KEYS</literal> to re-create the indexes after
+ loading the file. See <xref linkend="insert-speed"/>.
</para>
<para>
@@ -5006,8 +5010,8 @@
</para>
<para>
- If you do not specify a <literal>FIELDS</literal> clause, the
- defaults are the same as if you had written this:
+ If you specify no <literal>FIELDS</literal> clause, the defaults
+ are the same as if you had written this:
</para>
<programlisting>
@@ -5015,8 +5019,8 @@
</programlisting>
<para>
- If you do not specify a <literal>LINES</literal> clause, the
- default is the same as if you had written this:
+ If you specify no <literal>LINES</literal> clause, the defaults
+ are the same as if you had written this:
</para>
<programlisting>
@@ -5102,8 +5106,9 @@
</itemizedlist>
<para>
- Note that to write <literal>FIELDS ESCAPED BY '\\'</literal>,
- you must specify two backslashes for the value to be read as a
+ Backslash is the MySQL escape character within strings, so to
+ write <literal>FIELDS ESCAPED BY '\\'</literal>, you must
+ specify two backslashes for the value to be interpreted as a
single backslash.
</para>
@@ -5122,34 +5127,30 @@
If all the lines you want to read in have a common prefix that
you want to ignore, you can use <literal>LINES STARTING BY
'<replaceable>prefix_string</replaceable>'</literal> to skip
- over the prefix (and anything before it). If a line does not
- include the prefix, the entire line is skipped.
- <emphasis role="bold">Note</emphasis>:
- <replaceable>prefix_string</replaceable> may occur in the middle
- of a line.
+ over the prefix, <emphasis>and anything before it</emphasis>. If
+ a line does not include the prefix, the entire line is skipped.
+ Suppose that you issue the following statement:
</para>
- <para>
- Example:
- </para>
-
<programlisting>
-mysql> <userinput>LOAD DATA INFILE '/tmp/test.txt'</userinput>
- -> <userinput>INTO TABLE test LINES STARTING BY "xxx";</userinput>
+LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
+ FIELDS TERMINATED BY ',' LINES STARTING BY 'xxx';
</programlisting>
<para>
- With this you can read in a file that contains something like:
+ If the data file looks like this:
</para>
<programlisting>
-xxx"row",1
-something xxx"row",2
+xxx"abc",1
+something xxx"def",2
+"ghi",3
</programlisting>
<para>
- And just get the data <literal>("row",1)</literal> and
- <literal>("row",2)</literal>.
+ The resulting rows will be <literal>("abc",1)</literal> and
+ <literal>("def",2)</literal>. The third row in the file will be
+ skipped because it does not contain the prefix.
</para>
<para>
@@ -5161,8 +5162,7 @@
</para>
<programlisting>
-mysql> <userinput>LOAD DATA INFILE '/tmp/test.txt'</userinput>
- -> <userinput>INTO TABLE test IGNORE 1 LINES;</userinput>
+LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;
</programlisting>
<para>
@@ -5171,16 +5171,16 @@
from a database into a file and then read the file back into the
database later, the field- and line-handling options for both
statements must match. Otherwise, <literal>LOAD DATA
- INFILE</literal> does not interpret the contents of the file
+ INFILE</literal> will not interpret the contents of the file
properly. Suppose that you use <literal>SELECT ... INTO
OUTFILE</literal> to write a file with fields delimited by
commas:
</para>
<programlisting>
-mysql> <userinput>SELECT * INTO OUTFILE 'data.txt'</userinput>
- -> <userinput>FIELDS TERMINATED BY ','</userinput>
- -> <userinput>FROM table2;</userinput>
+SELECT * INTO OUTFILE 'data.txt'
+ FIELDS TERMINATED BY ','
+ FROM table2;
</programlisting>
<para>
@@ -5189,19 +5189,20 @@
</para>
<programlisting>
-mysql> <userinput>LOAD DATA INFILE 'data.txt' INTO TABLE table2</userinput>
- -> <userinput>FIELDS TERMINATED BY ',';</userinput>
+LOAD DATA INFILE 'data.txt' INTO TABLE table2
+ FIELDS TERMINATED BY ',';
</programlisting>
<para>
If instead you tried to read in the file with the statement
- shown here, it wouldn't work because it instructs <literal>LOAD
- DATA INFILE</literal> to look for tabs between fields:
+ shown following, it wouldn't work because it instructs
+ <literal>LOAD DATA INFILE</literal> to look for tabs between
+ fields:
</para>
<programlisting>
-mysql> <userinput>LOAD DATA INFILE 'data.txt' INTO TABLE table2</userinput>
- -> <userinput>FIELDS TERMINATED BY '\t';</userinput>
+LOAD DATA INFILE 'data.txt' INTO TABLE table2
+ FIELDS TERMINATED BY '\t';
</programlisting>
<para>
@@ -5209,19 +5210,28 @@
as a single field.
</para>
+ <indexterm>
+ <primary>CSV data, reading</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>comma-separate values data, reading</primary>
+ </indexterm>
+
<para>
<literal>LOAD DATA INFILE</literal> can be used to read files
- obtained from external sources, too. For example, a file in
- dBASE format has fields separated by commas and enclosed within
- double quotes. If lines in the file are terminated by newlines,
- the statement shown here illustrates the field- and
- line-handling options you would use to load the file:
+ obtained from external sources. For example, many programs can
+ export data in comma-separate values (CSV) format, such that
+ lines have fields separated by commas and enclosed within double
+ quotes. If lines in such a file are terminated by newlines, the
+ statement shown here illustrates the field- and line-handling
+ options you would use to load the file:
</para>
<programlisting>
-mysql> <userinput>LOAD DATA INFILE 'data.txt' INTO TABLE <replaceable>tbl_name</replaceable></userinput>
- -> <userinput>FIELDS TERMINATED BY ',' ENCLOSED BY '"'</userinput>
- -> <userinput>LINES TERMINATED BY '\n';</userinput>
+LOAD DATA INFILE 'data.txt' INTO TABLE <replaceable>tbl_name</replaceable>
+ FIELDS TERMINATED BY ',' ENCLOSED BY '"'
+ LINES TERMINATED BY '\n';
</programlisting>
<para>
@@ -5243,12 +5253,12 @@
</para>
<programlisting>
-mysql> <userinput>CREATE TABLE jokes</userinput>
- -> <userinput>(a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,</userinput>
- -> <userinput>joke TEXT NOT NULL);</userinput>
-mysql> <userinput>LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes</userinput>
- -> <userinput>FIELDS TERMINATED BY ''</userinput>
- -> <userinput>LINES TERMINATED BY '\n%%\n' (joke);</userinput>
+CREATE TABLE jokes
+ (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+ joke TEXT NOT NULL);
+LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes
+ FIELDS TERMINATED BY ''
+ LINES TERMINATED BY '\n%%\n' (joke);
</programlisting>
<para>
@@ -5270,7 +5280,7 @@
<para>
If you specify <literal>OPTIONALLY</literal>, the
<literal>ENCLOSED BY</literal> character is used only to enclose
- values in columns that have a string data type (such as
+ values from columns that have a string data type (such as
<literal>CHAR</literal>, <literal>BINARY</literal>,
<literal>TEXT</literal>, or <literal>ENUM</literal>):
</para>
@@ -5287,12 +5297,12 @@
character within a field value are escaped by prefixing them
with the <literal>ESCAPED BY</literal> character. Also note that
if you specify an empty <literal>ESCAPED BY</literal> value, it
- is possible to generate output that cannot be read properly by
- <literal>LOAD DATA INFILE</literal>. For example, the preceding
- output just shown would appear as follows if the escape
- character is empty. Observe that the second field in the fourth
- line contains a comma following the quote, which (erroneously)
- appears to terminate the field:
+ is possible to inadvertently generate output that cannot be read
+ properly by <literal>LOAD DATA INFILE</literal>. For example,
+ the preceding output just shown would appear as follows if the
+ escape character is empty. Observe that the second field in the
+ fourth line contains a comma following the quote, which
+ (erroneously) appears to terminate the field:
</para>
<programlisting>
@@ -5305,8 +5315,8 @@
<para>
For input, the <literal>ENCLOSED BY</literal> character, if
present, is stripped from the ends of field values. (This is
- true whether or not <literal>OPTIONALLY</literal> is specified;
- <literal>OPTIONALLY</literal> has no effect on input
+ true regardless of whether <literal>OPTIONALLY</literal> is
+ specified; <literal>OPTIONALLY</literal> has no effect on input
interpretation.) Occurrences of the <literal>ENCLOSED
BY</literal> character preceded by the <literal>ESCAPED
BY</literal> character are interpreted as part of the current
@@ -5488,7 +5498,7 @@
<listitem>
<para>
- With fixed-row format (which happens when <literal>FIELDS
+ With fixed-row format (which is used when <literal>FIELDS
TERMINATED BY</literal> and <literal>FIELDS ENCLOSED
BY</literal> are both empty), <literal>NULL</literal> is
written as an empty string. Note that this causes both
@@ -5552,7 +5562,7 @@
</para>
<programlisting>
-mysql> <userinput>LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;</userinput>
+LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;
</programlisting>
<para>
@@ -5563,14 +5573,13 @@
</para>
<programlisting>
-mysql> <userinput>LOAD DATA INFILE 'persondata.txt'</userinput>
- -> <userinput>INTO TABLE persondata (col1,col2,...);</userinput>
+LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);
</programlisting>
<para>
You must also specify a column list if the order of the fields
in the input file differs from the order of the columns in the
- table. Otherwise, MySQL cannot tell how to match up input fields
+ table. Otherwise, MySQL cannot tell how to match input fields
with table columns.
</para>
@@ -5579,19 +5588,18 @@
columns in the table being loaded, and the
<literal>SET</literal> clause is not supported. As of MySQL
5.0.3, the column list can contain either column names or user
- variables, and the <literal>SET</literal> clause is supported.
- This enables you to assign input values to user variables, and
- then perform transformations on those values before assigning
- the result to columns.
+ variables. With user variables, the <literal>SET</literal>
+ clause enables you to perform transformations on their values
+ before assigning the result to columns.
</para>
<para>
User variables in the <literal>SET</literal> clause can be used
- in several ways. The following example uses the first column in
- the data file directly for the value of
- <literal>t1.column1</literal>, and assigns the second column to
- a user variable that is subjected to a division operation before
- being used for the value of <literal>t2.column2</literal>:
+ in several ways. The following example uses the first input
+ column directly for the value of <literal>t1.column1</literal>,
+ and assigns the second input column to a user variable that is
+ subjected to a division operation before being used for the
+ value of <literal>t1.column2</literal>:
</para>
<programlisting>
@@ -5635,8 +5643,8 @@
<listitem>
<para>
Assignments in the <literal>SET</literal> clause should have
- only column names on the left hand side of the assignment
- operator.
+ only column names on the left hand side of assignment
+ operators.
</para>
</listitem>
@@ -5687,7 +5695,7 @@
If an input line has too few fields, the table columns for which
input fields are missing are set to their default values.
Default value assignment is described in
- <xref linkend="create-table"/>.
+ <xref linkend="data-type-defaults"/>.
</para>
<para>
@@ -5727,20 +5735,13 @@
<literal>UPDATE</literal> statement.
</para>
- <remark role="todo">
- with the new DEFAULT ... ON UPDATE syntax for declaring
- TIMESTAMP columns, this should be amended to reflect that it's
- not necessarily the *first* TIMESTAMP that is set to current
- date and time, it's the one with DEFAULT NOW.
- </remark>
-
<para>
<literal>TIMESTAMP</literal> columns are set to the current date
and time only if there is a <literal>NULL</literal> value for
- the column (that is, <literal>\N</literal>), or (for the first
- <literal>TIMESTAMP</literal> column only) if the
- <literal>TIMESTAMP</literal> column is omitted from the field
- list when a field list is specified.
+ the column (that is, <literal>\N</literal>), or if the
+ <literal>TIMESTAMP</literal> column's default value is the
+ current timestamp and it is omitted from the field list when a
+ field list is specified.
</para>
<para>
@@ -13999,12 +14000,13 @@
You cannot use <literal>ONE_SHOT</literal> with other than
the allowed set of variables; if you try, you get an error
like this:
+ </para>
<programlisting>
mysql> <userinput>SET ONE_SHOT max_allowed_packet = 1;</userinput>
-ERROR 1382 (HY000): The 'SET ONE_SHOT' syntax is reserved for purposes internal to the MySQL server
+ERROR 1382 (HY000): The 'SET ONE_SHOT' syntax is reserved for purposes
+internal to the MySQL server
</programlisting>
- </para>
<para>
If <literal>ONE_SHOT</literal> is used with the allowed
@@ -14015,6 +14017,7 @@
next statement is a <literal>SET</literal> statement. In
other words, resetting takes place after the next
non-<literal>SET</literal> statement. Example:
+ </para>
<programlisting>
mysql> <userinput>SET ONE_SHOT character_set_connection = latin5;</userinput>
@@ -14037,7 +14040,6 @@
| collation_connection | latin1_swedish_ci |
+--------------------------+-------------------+
</programlisting>
- </para>
</listitem>
<listitem>
Modified: trunk/refman-5.0/triggers.xml
===================================================================
--- trunk/refman-5.0/triggers.xml 2006-01-27 18:00:24 UTC (rev 1066)
+++ trunk/refman-5.0/triggers.xml 2006-01-27 18:33:37 UTC (rev 1067)
@@ -160,7 +160,7 @@
<para>
A potentially confusing example of this is the <literal>INSERT
- INTO ... ON DUPLICATE UPDATE ...</literal> syntax: a
+ INTO ... ON DUPLICATE KEY UPDATE ...</literal> syntax: a
<literal>BEFORE INSERT</literal> trigger will activate for every
row, followed by either an <literal>AFTER INSERT</literal> trigger
or both the <literal>BEFORE UPDATE</literal> and <literal>AFTER
Modified: trunk/refman-5.1/client-utility-programs.xml
===================================================================
--- trunk/refman-5.1/client-utility-programs.xml 2006-01-27 18:00:24 UTC (rev 1066)
+++ trunk/refman-5.1/client-utility-programs.xml 2006-01-27 18:33:37 UTC (rev 1067)
@@ -8687,7 +8687,7 @@
<para>
This option takes a comma-separated list of column names
as its value. The order of the column names indicates how
- to match up data file columns with table columns.
+ to match data file columns with table columns.
</para>
</listitem>
Modified: trunk/refman-5.1/optimization.xml
===================================================================
--- trunk/refman-5.1/optimization.xml 2006-01-27 18:00:24 UTC (rev 1066)
+++ trunk/refman-5.1/optimization.xml 2006-01-27 18:33:37 UTC (rev 1067)
@@ -6464,11 +6464,29 @@
</para>
<para>
+ Under circumstances where concurrent inserts can be used, there
+ is seldom any need to use the <literal>DELAYED</literal>
+ modifier for <literal>INSERT</literal> statements.
+ <xref linkend="insert-delayed"/>.
+ </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>
+ <para>
+ With <literal>LOAD DATA INFILE</literal>, if you specify
+ <literal>CONCURRENT</literal> with a <literal>MyISAM</literal>
+ table that satisfies the condition for concurrent inserts (that
+ is, it contains no free blocks in the middle), other threads can
+ retrieve data from the table while <literal>LOAD DATA</literal>
+ is executing. Using this option affects the performance of
+ <literal>LOAD DATA</literal> a bit, even if no other thread is
+ using the table at the same time.
+ </para>
+
</section>
</section>
Modified: trunk/refman-5.1/sql-syntax.xml
===================================================================
--- trunk/refman-5.1/sql-syntax.xml 2006-01-27 18:00:24 UTC (rev 1066)
+++ trunk/refman-5.1/sql-syntax.xml 2006-01-27 18:33:37 UTC (rev 1067)
@@ -4972,6 +4972,25 @@
</para>
<para>
+ Note that <literal>INSERT DELAYED</literal> is slower than a
+ normal <literal>INSERT</literal> if the table is not otherwise
+ in use. There is also the additional overhead for the server
+ to handle a separate thread for each table for which there are
+ delayed rows. This means that you should use <literal>INSERT
+ DELAYED</literal> only when you are really sure that you need
+ it.
+ </para>
+
+ <para>
+ The queued rows are held only in memory until they are
+ inserted into the table. This means that if you terminate
+ <command>mysqld</command> forcibly (for example, with
+ <literal>kill -9</literal>) or if <command>mysqld</command>
+ dies unexpectedly, <emphasis>any queued rows that have not
+ been written to disk are lost</emphasis>.
+ </para>
+
+ <para>
There are some constraints on the use of
<literal>DELAYED</literal>:
</para>
@@ -4982,16 +5001,19 @@
<para>
<literal>INSERT DELAYED</literal> works only with
<literal>MyISAM</literal>, <literal>MEMORY</literal>, and
- <literal>ARCHIVE</literal> tables. For
- <literal>MyISAM</literal> tables, if there are no free
+ <literal>ARCHIVE</literal> tables. See
+ <xref linkend="myisam-storage-engine"/>,
+ <xref linkend="memory-storage-engine"/>, and
+ <xref linkend="archive-storage-engine"/>.
+ </para>
+
+ <para>
+ For <literal>MyISAM</literal> tables, if there are no free
blocks in the middle of the data file, concurrent
<literal>SELECT</literal> and <literal>INSERT</literal>
statements are supported. Under these circumstances, you
very seldom need to use <literal>INSERT DELAYED</literal>
- with <literal>MyISAM</literal>. See
- <xref linkend="myisam-storage-engine"/>,
- <xref linkend="memory-storage-engine"/>, and
- <xref linkend="archive-storage-engine"/>.
+ with <literal>MyISAM</literal>.
</para>
</listitem>
@@ -5007,18 +5029,18 @@
<listitem>
<para>
The server ignores <literal>DELAYED</literal> for
- <literal>INSERT DELAYED ... ON DUPLICATE UPDATE</literal>
- statements.
+ <literal>INSERT ... SELECT</literal> or <literal>INSERT
+ ... ON DUPLICATE KEY UPDATE</literal> statements.
</para>
</listitem>
<listitem>
<para>
- Because the statement returns immediately before the rows
- are inserted, you cannot use
- <literal>LAST_INSERT_ID()</literal> to get the
- <literal>AUTO_INCREMENT</literal> value which the
- statement might generate.
+ Because the <literal>INSERT DELAYED</literal> statement
+ returns immediately, before the rows are inserted, you
+ cannot use <literal>LAST_INSERT_ID()</literal> to get the
+ <literal>AUTO_INCREMENT</literal> value that the statement
+ might generate.
</para>
</listitem>
@@ -5043,16 +5065,6 @@
<remark role="help-description-end"/>
<para>
- Note that currently the queued rows are held only in memory
- until they are inserted into the table. This means that if you
- terminate <command>mysqld</command> forcibly (for example,
- with <literal>kill -9</literal>) or if
- <command>mysqld</command> dies unexpectedly, <emphasis>any
- queued rows that have not been written to disk are
- lost</emphasis>.
- </para>
-
- <para>
The following describes in detail what happens when you use
the <literal>DELAYED</literal> option to
<literal>INSERT</literal> or <literal>REPLACE</literal>. In
@@ -5070,7 +5082,7 @@
When a thread executes a <literal>DELAYED</literal>
statement for a table, a handler thread is created to
process all <literal>DELAYED</literal> statements for the
- table, if no such handler previously exists.
+ table, if no such handler already exists.
</para>
</listitem>
@@ -5083,8 +5095,8 @@
other threads have a <literal>READ</literal> or
<literal>WRITE</literal> lock on the table. However, the
handler waits for all <literal>ALTER TABLE</literal> locks
- or <literal>FLUSH TABLES</literal> to ensure that the
- table structure is up to date.
+ or <literal>FLUSH TABLES</literal> statements to finish,
+ to ensure that the table structure is up to date.
</para>
</listitem>
@@ -5163,7 +5175,8 @@
<literal>delayed_insert</literal> in the
<literal>Command</literal> column. It is killed if you
execute a <literal>FLUSH TABLES</literal> statement or
- kill it with <literal>KILL thread_id</literal>. However,
+ kill it with <literal>KILL
+ <replaceable>thread_id</replaceable></literal>. However,
before exiting, it first stores all queued rows into the
table. During this time it does not accept any new
<literal>INSERT</literal> statements from other threads.
@@ -5178,8 +5191,9 @@
<literal>INSERT DELAYED</literal> handler running. Other
update statements have to wait until the <literal>INSERT
DELAYED</literal> queue is empty, someone terminates the
- handler thread (with <literal>KILL thread_id</literal>),
- or someone executes a <literal>FLUSH TABLES</literal>.
+ handler thread (with <literal>KILL
+ <replaceable>thread_id</replaceable></literal>), or
+ someone executes a <literal>FLUSH TABLES</literal>.
</para>
</listitem>
@@ -5223,16 +5237,6 @@
</itemizedlist>
- <para>
- Note that <literal>INSERT DELAYED</literal> is slower than a
- normal <literal>INSERT</literal> if the table is not in use.
- There is also the additional overhead for the server to handle
- a separate thread for each table for which there are delayed
- rows. This means that you should use <literal>INSERT
- DELAYED</literal> only when you are really sure that you need
- it.
- </para>
-
</section>
<section id="insert-on-duplicate">
@@ -5340,13 +5344,13 @@
<remark role="help-syntax"/>
<programlisting>
-LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE '<replaceable>file_name</replaceable>.txt'
+LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE '<replaceable>file_name</replaceable>'
[REPLACE | IGNORE]
INTO TABLE <replaceable>tbl_name</replaceable>
[FIELDS
[TERMINATED BY '<replaceable>string</replaceable>']
[[OPTIONALLY] ENCLOSED BY '<replaceable>char</replaceable>']
- [ESCAPED BY '<replaceable>char</replaceable>' ]
+ [ESCAPED BY '<replaceable>char</replaceable>']
]
[LINES
[STARTING BY '<replaceable>string</replaceable>']
@@ -5366,6 +5370,14 @@
</para>
<para>
+ The syntax for the <literal>FIELDS</literal> and
+ <literal>LINES</literal> clauses also applies to the
+ <literal>SELECT ... INTO OUTFILE</literal> statement, as
+ described later in this section. (See also
+ <xref linkend="select"/>.)
+ </para>
+
+ <para>
For more information about the efficiency of
<literal>INSERT</literal> versus <literal>LOAD DATA
INFILE</literal> and speeding up <literal>LOAD DATA
@@ -5382,8 +5394,8 @@
</para>
<para>
- Note that it's currently not possible to load
- <literal>UCS2</literal> data files.
+ Note that it is currently not possible to load data files that
+ use the <literal>ucs2</literal> character set.
</para>
<indexterm>
@@ -5419,8 +5431,8 @@
</para>
<para>
- If <literal>LOCAL</literal> is specified, it is interpreted with
- respect to the client end of the connection:
+ The <literal>LOCAL</literal> keyword, if specified, is
+ interpreted with respect to the client end of the connection:
</para>
<itemizedlist>
@@ -5440,66 +5452,62 @@
<para>
If <literal>LOCAL</literal> is not specified, the file must
be located on the server host and is read directly by the
- server.
+ server. The server uses the following rules to locate the
+ file:
</para>
- </listitem>
- </itemizedlist>
+ <itemizedlist>
- <para>
- When locating files on the server host, the server uses the
- following rules:
- </para>
+ <listitem>
+ <para>
+ If the filename is an absolute pathname, the server uses
+ it as given.
+ </para>
+ </listitem>
- <itemizedlist>
+ <listitem>
+ <para>
+ If the filename is a relative pathname with one or more
+ leading components, the server searches for the file
+ relative to the server's data directory.
+ </para>
+ </listitem>
- <listitem>
- <para>
- If an absolute pathname is given, the server uses the
- pathname as is.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ If a filename with no leading components is given, the
+ server looks for the file in the database directory of
+ the default database.
+ </para>
+ </listitem>
- <listitem>
- <para>
- If a relative pathname with one or more leading components
- is given, the server searches for the file relative to the
- server's data directory.
- </para>
+ </itemizedlist>
</listitem>
- <listitem>
- <para>
- If a filename with no leading components is given, the
- server looks for the file in the database directory of the
- default database.
- </para>
- </listitem>
-
</itemizedlist>
<para>
- Note that these rules mean that a file named as
- <filename>./myfile.txt</filename> is read from the server's data
- directory, whereas the same file named as
+ Note that, in the non-<literal>LOCAL</literal> case, these rules
+ mean that a file named as <filename>./myfile.txt</filename> is
+ read from the server's data directory, whereas the file named as
<filename>myfile.txt</filename> is read from the database
- directory of the default database. For example, the following
+ directory of the default database. For example, if
+ <literal>db1</literal> is the default database, the following
<literal>LOAD DATA</literal> statement reads the file
<filename>data.txt</filename> from the database directory for
- <literal>db1</literal> because <literal>db1</literal> is the
- default database, even though the statement explicitly loads the
- file into a table in the <literal>db2</literal> database:
+ <literal>db1</literal>, even though the statement explicitly
+ loads the file into a table in the <literal>db2</literal>
+ database:
</para>
<programlisting>
-mysql> <userinput>USE db1;</userinput>
-mysql> <userinput>LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;</userinput>
+LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;
</programlisting>
<para>
- Note that Windows pathnames are specified using forward slashes
- rather than backslashes. If you do use backslashes, you must
- double them.
+ Windows pathnames are specified using forward slashes rather
+ than backslashes. If you do use backslashes, you must double
+ them.
</para>
<para>
@@ -5507,16 +5515,13 @@
server, the files must either reside in the database directory
or be readable by all. Also, to use <literal>LOAD DATA
INFILE</literal> on server files, you must have the
- <literal>FILE</literal> privilege.
+ <literal>FILE</literal> privilege. See
+ <xref linkend="privileges-provided"/>.
</para>
<remark role="help-description-end"/>
<para>
- See <xref linkend="privileges-provided"/>.
- </para>
-
- <para>
Using <literal>LOCAL</literal> is a bit slower than letting the
server access the files directly, because the contents of the
file must be sent over the connection by the client to the
@@ -5533,9 +5538,9 @@
</para>
<para>
- If you need <literal>LOAD DATA</literal> to read from a pipe,
- you can use the following technique (here we load the listing of
- the <filename>/</filename> directory into a table):
+ On Unix, if you need <literal>LOAD DATA</literal> to read from a
+ pipe, you can use the following technique (here we load the
+ listing of the <filename>/</filename> directory into a table):
</para>
<programlisting>
@@ -5553,8 +5558,8 @@
<para>
If you specify <literal>REPLACE</literal>, input rows replace
- existing rows (in other words, rows that have the same value for
- a primary key or unique index as an existing row). See
+ existing rows. In other words, rows that have the same value for
+ a primary key or unique index as an existing row. See
<xref linkend="replace"/>.
</para>
@@ -5582,14 +5587,13 @@
If you use <literal>LOAD DATA INFILE</literal> on an empty
<literal>MyISAM</literal> table, all non-unique indexes are
created in a separate batch (as for <literal>REPAIR
- TABLE</literal>). This normally makes <literal>LOAD DATA
- INFILE</literal> much faster when you have many indexes.
- Normally this is very fast, but in some extreme cases, you can
- create the indexes even faster by turning them off with
- <literal>ALTER TABLE ... DISABLE KEYS</literal> before loading
- the file into the table and using <literal>ALTER TABLE ...
- ENABLE KEYS</literal> to re-create the indexes after loading the
- file. See <xref linkend="insert-speed"/>.
+ TABLE</literal>). Normally, this makes <literal>LOAD DATA
+ INFILE</literal> much faster when you have many indexes. In some
+ extreme cases, you can create the indexes even faster by turning
+ them off with <literal>ALTER TABLE ... DISABLE KEYS</literal>
+ before loading the file into the table and using <literal>ALTER
+ TABLE ... ENABLE KEYS</literal> to re-create the indexes after
+ loading the file. See <xref linkend="insert-speed"/>.
</para>
<para>
@@ -5614,8 +5618,8 @@
</para>
<para>
- If you do not specify a <literal>FIELDS</literal> clause, the
- defaults are the same as if you had written this:
+ If you specify no <literal>FIELDS</literal> clause, the defaults
+ are the same as if you had written this:
</para>
<programlisting>
@@ -5623,8 +5627,8 @@
</programlisting>
<para>
- If you do not specify a <literal>LINES</literal> clause, the
- default is the same as if you had written this:
+ If you specify no <literal>LINES</literal> clause, the defaults
+ are the same as if you had written this:
</para>
<programlisting>
@@ -5710,8 +5714,9 @@
</itemizedlist>
<para>
- Note that to write <literal>FIELDS ESCAPED BY '\\'</literal>,
- you must specify two backslashes for the value to be read as a
+ Backslash is the MySQL escape character within strings, so to
+ write <literal>FIELDS ESCAPED BY '\\'</literal>, you must
+ specify two backslashes for the value to be interpreted as a
single backslash.
</para>
@@ -5730,34 +5735,30 @@
If all the lines you want to read in have a common prefix that
you want to ignore, you can use <literal>LINES STARTING BY
'<replaceable>prefix_string</replaceable>'</literal> to skip
- over the prefix (and anything before it). If a line does not
- include the prefix, the entire line is skipped.
- <emphasis role="bold">Note</emphasis>:
- <replaceable>prefix_string</replaceable> may occur in the middle
- of a line.
+ over the prefix, <emphasis>and anything before it</emphasis>. If
+ a line does not include the prefix, the entire line is skipped.
+ Suppose that you issue the following statement:
</para>
- <para>
- Example:
- </para>
-
<programlisting>
-mysql> <userinput>LOAD DATA INFILE '/tmp/test.txt'</userinput>
- -> <userinput>INTO TABLE test LINES STARTING BY "xxx";</userinput>
+LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
+ FIELDS TERMINATED BY ',' LINES STARTING BY 'xxx';
</programlisting>
<para>
- With this you can read in a file that contains something like:
+ If the data file looks like this:
</para>
<programlisting>
-xxx"row",1
-something xxx"row",2
+xxx"abc",1
+something xxx"def",2
+"ghi",3
</programlisting>
<para>
- And just get the data <literal>("row",1)</literal> and
- <literal>("row",2)</literal>.
+ The resulting rows will be <literal>("abc",1)</literal> and
+ <literal>("def",2)</literal>. The third row in the file will be
+ skipped because it does not contain the prefix.
</para>
<para>
@@ -5769,8 +5770,7 @@
</para>
<programlisting>
-mysql> <userinput>LOAD DATA INFILE '/tmp/test.txt'</userinput>
- -> <userinput>INTO TABLE test IGNORE 1 LINES;</userinput>
+LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;
</programlisting>
<para>
@@ -5779,16 +5779,16 @@
from a database into a file and then read the file back into the
database later, the field- and line-handling options for both
statements must match. Otherwise, <literal>LOAD DATA
- INFILE</literal> does not interpret the contents of the file
+ INFILE</literal> will not interpret the contents of the file
properly. Suppose that you use <literal>SELECT ... INTO
OUTFILE</literal> to write a file with fields delimited by
commas:
</para>
<programlisting>
-mysql> <userinput>SELECT * INTO OUTFILE 'data.txt'</userinput>
- -> <userinput>FIELDS TERMINATED BY ','</userinput>
- -> <userinput>FROM table2;</userinput>
+SELECT * INTO OUTFILE 'data.txt'
+ FIELDS TERMINATED BY ','
+ FROM table2;
</programlisting>
<para>
@@ -5797,19 +5797,20 @@
</para>
<programlisting>
-mysql> <userinput>LOAD DATA INFILE 'data.txt' INTO TABLE table2</userinput>
- -> <userinput>FIELDS TERMINATED BY ',';</userinput>
+LOAD DATA INFILE 'data.txt' INTO TABLE table2
+ FIELDS TERMINATED BY ',';
</programlisting>
<para>
If instead you tried to read in the file with the statement
- shown here, it wouldn't work because it instructs <literal>LOAD
- DATA INFILE</literal> to look for tabs between fields:
+ shown following, it wouldn't work because it instructs
+ <literal>LOAD DATA INFILE</literal> to look for tabs between
+ fields:
</para>
<programlisting>
-mysql> <userinput>LOAD DATA INFILE 'data.txt' INTO TABLE table2</userinput>
- -> <userinput>FIELDS TERMINATED BY '\t';</userinput>
+LOAD DATA INFILE 'data.txt' INTO TABLE table2
+ FIELDS TERMINATED BY '\t';
</programlisting>
<para>
@@ -5817,19 +5818,28 @@
as a single field.
</para>
+ <indexterm>
+ <primary>CSV data, reading</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>comma-separate values data, reading</primary>
+ </indexterm>
+
<para>
<literal>LOAD DATA INFILE</literal> can be used to read files
- obtained from external sources, too. For example, a file in
- dBASE format has fields separated by commas and enclosed within
- double quotes. If lines in the file are terminated by newlines,
- the statement shown here illustrates the field- and
- line-handling options you would use to load the file:
+ obtained from external sources. For example, many programs can
+ export data in comma-separate values (CSV) format, such that
+ lines have fields separated by commas and enclosed within double
+ quotes. If lines in such a file are terminated by newlines, the
+ statement shown here illustrates the field- and line-handling
+ options you would use to load the file:
</para>
<programlisting>
-mysql> <userinput>LOAD DATA INFILE 'data.txt' INTO TABLE <replaceable>tbl_name</replaceable></userinput>
- -> <userinput>FIELDS TERMINATED BY ',' ENCLOSED BY '"'</userinput>
- -> <userinput>LINES TERMINATED BY '\n';</userinput>
+LOAD DATA INFILE 'data.txt' INTO TABLE <replaceable>tbl_name</replaceable>
+ FIELDS TERMINATED BY ',' ENCLOSED BY '"'
+ LINES TERMINATED BY '\n';
</programlisting>
<para>
@@ -5851,12 +5861,12 @@
</para>
<programlisting>
-mysql> <userinput>CREATE TABLE jokes</userinput>
- -> <userinput>(a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,</userinput>
- -> <userinput>joke TEXT NOT NULL);</userinput>
-mysql> <userinput>LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes</userinput>
- -> <userinput>FIELDS TERMINATED BY ''</userinput>
- -> <userinput>LINES TERMINATED BY '\n%%\n' (joke);</userinput>
+CREATE TABLE jokes
+ (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+ joke TEXT NOT NULL);
+LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes
+ FIELDS TERMINATED BY ''
+ LINES TERMINATED BY '\n%%\n' (joke);
</programlisting>
<para>
@@ -5878,7 +5888,7 @@
<para>
If you specify <literal>OPTIONALLY</literal>, the
<literal>ENCLOSED BY</literal> character is used only to enclose
- values in columns that have a string data type (such as
+ values from columns that have a string data type (such as
<literal>CHAR</literal>, <literal>BINARY</literal>,
<literal>TEXT</literal>, or <literal>ENUM</literal>):
</para>
@@ -5895,12 +5905,12 @@
character within a field value are escaped by prefixing them
with the <literal>ESCAPED BY</literal> character. Also note that
if you specify an empty <literal>ESCAPED BY</literal> value, it
- is possible to generate output that cannot be read properly by
- <literal>LOAD DATA INFILE</literal>. For example, the preceding
- output just shown would appear as follows if the escape
- character is empty. Observe that the second field in the fourth
- line contains a comma following the quote, which (erroneously)
- appears to terminate the field:
+ is possible to inadvertently generate output that cannot be read
+ properly by <literal>LOAD DATA INFILE</literal>. For example,
+ the preceding output just shown would appear as follows if the
+ escape character is empty. Observe that the second field in the
+ fourth line contains a comma following the quote, which
+ (erroneously) appears to terminate the field:
</para>
<programlisting>
@@ -5913,8 +5923,8 @@
<para>
For input, the <literal>ENCLOSED BY</literal> character, if
present, is stripped from the ends of field values. (This is
- true whether or not <literal>OPTIONALLY</literal> is specified;
- <literal>OPTIONALLY</literal> has no effect on input
+ true regardless of whether <literal>OPTIONALLY</literal> is
+ specified; <literal>OPTIONALLY</literal> has no effect on input
interpretation.) Occurrences of the <literal>ENCLOSED
BY</literal> character preceded by the <literal>ESCAPED
BY</literal> character are interpreted as part of the current
@@ -6096,7 +6106,7 @@
<listitem>
<para>
- With fixed-row format (which happens when <literal>FIELDS
+ With fixed-row format (which is used when <literal>FIELDS
TERMINATED BY</literal> and <literal>FIELDS ENCLOSED
BY</literal> are both empty), <literal>NULL</literal> is
written as an empty string. Note that this causes both
@@ -6160,7 +6170,7 @@
</para>
<programlisting>
-mysql> <userinput>LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;</userinput>
+LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;
</programlisting>
<para>
@@ -6171,32 +6181,30 @@
</para>
<programlisting>
-mysql> <userinput>LOAD DATA INFILE 'persondata.txt'</userinput>
- -> <userinput>INTO TABLE persondata (col1,col2,...);</userinput>
+LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);
</programlisting>
<para>
You must also specify a column list if the order of the fields
in the input file differs from the order of the columns in the
- table. Otherwise, MySQL cannot tell how to match up input fields
+ table. Otherwise, MySQL cannot tell how to match input fields
with table columns.
</para>
<para>
The column list can contain either column names or user
- variables, and the <literal>SET</literal> clause is supported.
- This enables you to assign input values to user variables, and
- then perform transformations on those values before assigning
- the result to columns.
+ variables. With user variables, the <literal>SET</literal>
+ clause enables you to perform transformations on their values
+ before assigning the result to columns.
</para>
<para>
User variables in the <literal>SET</literal> clause can be used
- in several ways. The following example uses the first column in
- the data file directly for the value of
- <literal>t1.column1</literal>, and assigns the second column to
- a user variable that is subjected to a division operation before
- being used for the value of <literal>t2.column2</literal>:
+ in several ways. The following example uses the first input
+ column directly for the value of <literal>t1.column1</literal>,
+ and assigns the second input column to a user variable that is
+ subjected to a division operation before being used for the
+ value of <literal>t1.column2</literal>:
</para>
<programlisting>
@@ -6240,8 +6248,8 @@
<listitem>
<para>
Assignments in the <literal>SET</literal> clause should have
- only column names on the left hand side of the assignment
- operator.
+ only column names on the left hand side of assignment
+ operators.
</para>
</listitem>
@@ -6292,7 +6300,7 @@
If an input line has too few fields, the table columns for which
input fields are missing are set to their default values.
Default value assignment is described in
- <xref linkend="create-table"/>.
+ <xref linkend="data-type-defaults"/>.
</para>
<para>
@@ -6332,20 +6340,13 @@
<literal>UPDATE</literal> statement.
</para>
- <remark role="todo">
- with the new DEFAULT ... ON UPDATE syntax for declaring
- TIMESTAMP columns, this should be amended to reflect that it's
- not necessarily the *first* TIMESTAMP that is set to current
- date and time, it's the one with DEFAULT NOW.
- </remark>
-
<para>
<literal>TIMESTAMP</literal> columns are set to the current date
and time only if there is a <literal>NULL</literal> value for
- the column (that is, <literal>\N</literal>), or (for the first
- <literal>TIMESTAMP</literal> column only) if the
- <literal>TIMESTAMP</literal> column is omitted from the field
- list when a field list is specified.
+ the column (that is, <literal>\N</literal>), or if the
+ <literal>TIMESTAMP</literal> column's default value is the
+ current timestamp and it is omitted from the field list when a
+ field list is specified.
</para>
<para>
@@ -14487,12 +14488,13 @@
You cannot use <literal>ONE_SHOT</literal> with other than
the allowed set of variables; if you try, you get an error
like this:
+ </para>
<programlisting>
mysql> <userinput>SET ONE_SHOT max_allowed_packet = 1;</userinput>
-ERROR 1382 (HY000): The 'SET ONE_SHOT' syntax is reserved for purposes internal to the MySQL server
+ERROR 1382 (HY000): The 'SET ONE_SHOT' syntax is reserved for purposes
+internal to the MySQL server
</programlisting>
- </para>
<para>
If <literal>ONE_SHOT</literal> is used with the allowed
@@ -14503,6 +14505,7 @@
next statement is a <literal>SET</literal> statement. In
other words, resetting takes place after the next
non-<literal>SET</literal> statement. Example:
+ </para>
<programlisting>
mysql> <userinput>SET ONE_SHOT character_set_connection = latin5;</userinput>
@@ -14525,7 +14528,6 @@
| collation_connection | latin1_swedish_ci |
+--------------------------+-------------------+
</programlisting>
- </para>
</listitem>
<listitem>
Modified: trunk/refman-5.1/triggers.xml
===================================================================
--- trunk/refman-5.1/triggers.xml 2006-01-27 18:00:24 UTC (rev 1066)
+++ trunk/refman-5.1/triggers.xml 2006-01-27 18:33:37 UTC (rev 1067)
@@ -158,7 +158,7 @@
<para>
A potentially confusing example of this is the <literal>INSERT
- INTO ... ON DUPLICATE UPDATE ...</literal> syntax: a
+ INTO ... ON DUPLICATE KEY UPDATE ...</literal> syntax: a
<literal>BEFORE INSERT</literal> trigger will activate for every
row, followed by either an <literal>AFTER INSERT</literal> trigger
or both the <literal>BEFORE UPDATE</literal> and <literal>AFTER
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r1067 - in trunk: . refman-4.1 refman-5.0 refman-5.1 | paul | 27 Jan |
