Below is the list of changes that have just been committed into a local
mysqldoc repository of paul. When paul does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://www.mysql.com/doc/I/n/Installing_source_tree.html
ChangeSet
1.2989 05/07/11 13:20:39 paul@stripped +2 -0
Document extensions to LOAD DATA syntax.
refman/sql-syntax.xml
1.9 05/07/11 13:20:38 paul@stripped +96 -1
Document extensions to LOAD DATA syntax.
refman-5.0/sql-syntax.xml
1.16 05/07/11 13:20:38 paul@stripped +108 -13
Sync.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: paul
# Host: kite-hub.kitebird.com
# Root: /src/extern/MySQL/bk/mysqldoc
--- 1.15/refman-5.0/sql-syntax.xml 2005-07-09 11:53:58 -05:00
+++ 1.16/refman-5.0/sql-syntax.xml 2005-07-11 13:20:38 -05:00
@@ -4320,19 +4320,20 @@
<programlisting>
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE '<replaceable>file_name</replaceable>.txt'
-[REPLACE | IGNORE]
-INTO TABLE <replaceable>tbl_name</replaceable>
-[FIELDS
-[TERMINATED BY '\t']
-[[OPTIONALLY] ENCLOSED BY '']
-[ESCAPED BY '\\' ]
-]
-[LINES
-[STARTING BY '']
-[TERMINATED BY '\n']
-]
-[IGNORE <replaceable>number</replaceable> LINES]
-[(<replaceable>col_name</replaceable>,...)]
+ [REPLACE | IGNORE]
+ INTO TABLE <replaceable>tbl_name</replaceable>
+ [FIELDS
+ [TERMINATED BY '\t']
+ [[OPTIONALLY] ENCLOSED BY '']
+ [ESCAPED BY '\\' ]
+ ]
+ [LINES
+ [STARTING BY '']
+ [TERMINATED BY '\n']
+ ]
+ [IGNORE <replaceable>number</replaceable> LINES]
+ [(<replaceable>col_name_or_user_var</replaceable>,...)]
+ [SET <replaceable>col_name</replaceable> = <replaceable>expr</replaceable>,...)]
</programlisting>
<!-- description_for_help_topic LOAD DATA BY LOAD DATA LOW_PRIORITY CONCURRENT LOCAL INFILE REPLACE IGNORE FIELDS ENCLOSED TERMINATED ESCAPED LINES IMPORT CSV OPTIONALLY STARTING -->
@@ -5068,6 +5069,100 @@
the input file differs from the order of the columns in the table.
Otherwise, MySQL cannot tell how to match up input fields with table
columns.
+ </para>
+
+ <para>
+ Before MySQL 5.0.3, the column list must contain only names of
+ 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.
+ </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>:
+ </para>
+
+<programlisting>
+LOAD DATA INFILE 'file.txt'
+ INTO TABLE t1
+ (column1, @var1)
+ SET column2 = @var1/100;
+</programlisting>
+
+ <para>
+ The <literal>SET</literal> clause can be used to supply values not
+ derived from the input file. THe following statement sets
+ <literal>column3</literal> to the current date and time:
+ </para>
+
+<programlisting>
+LOAD DATA INFILE 'file.txt'
+ INTO TABLE t1
+ (column1, column2)
+ SET column3 = CURRENT_TIMESTAMP;
+</programlisting>
+
+ <para>
+ You can also discard an input value by assigning it to a user
+ variable and not assigning the variable to a table column:
+ </para>
+
+<programlisting>
+LOAD DATA INFILE 'file.txt'
+ INTO TABLE t1
+ (column1, @dummy, column2, @dummy, column3);
+</programlisting>
+
+ <para>
+ Use of the column/variable list and <literal>SET</literal> clause is
+ subject to the following restrictions:
+ </para>
+
+ <itemizedlist>
+
+ <listitem><para>
+ Assignments in the <literal>SET</literal> clause should have only
+ column names on the left hand side of the assignment operator.
+ </para></listitem>
+
+ <listitem><para>
+ You can use subqueries in the right hand side of
+ <literal>SET</literal> assignments. A subquery that returns a value
+ to be assigned to a column may be a scalar subquery only. Also, you
+ cannot use a subquery to select from the table that is being
+ loaded.
+ </para></listitem>
+
+ <listitem><para>
+ Lines ignored by an <literal>IGNORE</literal> clause are not
+ processed for the column/variable list or <literal>SET</literal>
+ clause.
+ </para></listitem>
+
+ <listitem><para>
+ User variables cannot be used when loading data with fixed-row
+ format because user variables do not have a display width.
+ </para></listitem>
+
+ </itemizedlist>
+
+ <para>
+ When processing an input line, <literal>LOAD DATA</literal> splits
+ it into fields and uses the values according to the column/variable
+ list and the <literal>SET</literal> clause, if they are present.
+ Then the resulting row is inserted into the table. If there are
+ <literal>BEFORE INSERT</literal> or <literal>AFTER INSERT</literal>
+ triggers for the table, they are activated before or after inserting
+ the row, respectively.
</para>
<para>
--- 1.8/refman/sql-syntax.xml 2005-07-09 11:53:59 -05:00
+++ 1.9/refman/sql-syntax.xml 2005-07-11 13:20:38 -05:00
@@ -4516,7 +4516,8 @@
[TERMINATED BY '\n']
]
[IGNORE <replaceable>number</replaceable> LINES]
- [(<replaceable>col_name</replaceable>,...)]
+ [(<replaceable>col_name_or_user_var</replaceable>,...)]
+ [SET <replaceable>col_name</replaceable> = <replaceable>expr</replaceable>,...)]
</programlisting>
<!-- description_for_help_topic LOAD DATA BY LOAD DATA LOW_PRIORITY CONCURRENT LOCAL INFILE REPLACE IGNORE FIELDS ENCLOSED TERMINATED ESCAPED LINES IMPORT CSV OPTIONALLY STARTING -->
@@ -5271,6 +5272,100 @@
the input file differs from the order of the columns in the table.
Otherwise, MySQL cannot tell how to match up input fields with table
columns.
+ </para>
+
+ <para>
+ Before MySQL 5.0.3, the column list must contain only names of
+ 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.
+ </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>:
+ </para>
+
+<programlisting>
+LOAD DATA INFILE 'file.txt'
+ INTO TABLE t1
+ (column1, @var1)
+ SET column2 = @var1/100;
+</programlisting>
+
+ <para>
+ The <literal>SET</literal> clause can be used to supply values not
+ derived from the input file. THe following statement sets
+ <literal>column3</literal> to the current date and time:
+ </para>
+
+<programlisting>
+LOAD DATA INFILE 'file.txt'
+ INTO TABLE t1
+ (column1, column2)
+ SET column3 = CURRENT_TIMESTAMP;
+</programlisting>
+
+ <para>
+ You can also discard an input value by assigning it to a user
+ variable and not assigning the variable to a table column:
+ </para>
+
+<programlisting>
+LOAD DATA INFILE 'file.txt'
+ INTO TABLE t1
+ (column1, @dummy, column2, @dummy, column3);
+</programlisting>
+
+ <para>
+ Use of the column/variable list and <literal>SET</literal> clause is
+ subject to the following restrictions:
+ </para>
+
+ <itemizedlist>
+
+ <listitem><para>
+ Assignments in the <literal>SET</literal> clause should have only
+ column names on the left hand side of the assignment operator.
+ </para></listitem>
+
+ <listitem><para>
+ You can use subqueries in the right hand side of
+ <literal>SET</literal> assignments. A subquery that returns a value
+ to be assigned to a column may be a scalar subquery only. Also, you
+ cannot use a subquery to select from the table that is being
+ loaded.
+ </para></listitem>
+
+ <listitem><para>
+ Lines ignored by an <literal>IGNORE</literal> clause are not
+ processed for the column/variable list or <literal>SET</literal>
+ clause.
+ </para></listitem>
+
+ <listitem><para>
+ User variables cannot be used when loading data with fixed-row
+ format because user variables do not have a display width.
+ </para></listitem>
+
+ </itemizedlist>
+
+ <para>
+ When processing an input line, <literal>LOAD DATA</literal> splits
+ it into fields and uses the values according to the column/variable
+ list and the <literal>SET</literal> clause, if they are present.
+ Then the resulting row is inserted into the table. If there are
+ <literal>BEFORE INSERT</literal> or <literal>AFTER INSERT</literal>
+ triggers for the table, they are activated before or after inserting
+ the row, respectively.
</para>
<para>
| Thread |
|---|
| • bk commit - mysqldoc@docsrva tree (paul:1.2989) | paul | 11 Jul |
