|
<pre class="programlisting">LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE '<em class="replaceable">file_name'
[REPLACE | IGNORE]
INTO TABLE <em class="replaceable">tbl_name
[CHARACTER SET <em class="replaceable">charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY '<em class="replaceable">string']
[[OPTIONALLY] ENCLOSED BY '<em class="replaceable">char']
[ESCAPED BY '<em class="replaceable">char']
]
[LINES
[STARTING BY '<em class="replaceable">string']
[TERMINATED BY '<em class="replaceable">string']
]
[IGNORE <em class="replaceable">number {LINES | ROWS}]
[(<em class="replaceable">col_name_or_user_var,...)]
[SET <em class="replaceable">col_name = <em class="replaceable">expr,...]
Thestatement reads rows from a text file into a table at a very high speed.is the complement of. (SeeSyntax”.) To write data from a table to a file,use. To read the file back into a table,use. The syntax of theandclauses is the same for both statements. Both clauses are optional,butmust precedeif both are specified.
You can also load data files by using themysqlimportutility; it operates by sending astatement to the server. Theoption causesmysqlimportto read data files from the client host. You can specify theoption to get better performance over slow networks if the client and server support the compressed protocol. Seemysqlimport— A Data Import Program”.
For more information about the efficiency ofversusand speeding up,seeStatements”.
The file name must be given as a literal string. On Windows,specify backslashes in path names as forward slashes or doubled backslashes. Thesystem variable controls the interpretation of the file name.
The character set indicated by thesystem variable is used to interpret the information in the file.and the setting ofdo not affect interpretation of input. If the contents of the input file use a character set that differs from the default,it is usually preferable to specify the character set of the file by using theclause. A character set ofspecifies
interprets all fields in the file as having the same character set,regardless of the data types of the columns into which field values are loaded. For proper interpretation of file contents,you must ensure that it was written with the correct character set. For example,if you write a data file withmysqldump -Tor by issuing astatement inmysql,be sure to use aoption so that output is written in the character set to be used when the file is loaded with.
It is not possible to load data files that use the,,orcharacter set.
If you use,execution of thestatement is delayed until no other clients are reading from the table. This affects only storage engines that use only table-level locking (such as,,and).
If you specifywith atable 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 whileis executing. This option affects the performance ofa bit,even if no other thread is using the table at the same time.
With row-based replication,is replicated regardless of MySQL version. With statement-based replicationis not replicated prior to MySQL 5.5.1 (see Bug #34628). For more information,see”.
Thekeyword affects expected location of the file and error handling,as described later.works only if your server and your client both have been configured to permit it. For example,ifmysqldwas started with,does not work. See”.
Thekeyword affects where the file is expected to be found:
Ifis specified,the file is read by the client program on the client host and sent to the server. The file can be given as a full path name to specify its exact location. If given as a relative path name,the name is interpreted relative to the directory in which the client program was started.
When usingwith,a copy of the file is created in the server's temporary directory. This isnotthe directory determined by the value ofor,but rather the operating system's temporary directory,and is not configurable in the MySQL Server. (Typically the system temporary directory ison Linux systems andon Windows.) Lack of sufficient space for the copy in this directory can cause thestatement to fail.
Ifis not specified,the file must be located on the server host and is read directly by the server. The server uses the following rules to locate the file:
If the file name is an absolute path name,the server uses it as given.
If the file name is a relative path name with one or more leading components,the server searches for the file relative to the server's data directory.
If a file name with no leading components is given,the server looks for the file in the database directory of the default database.
In the non-case,these rules mean that a file named asis read from the server's data directory,whereas the file named asis read from the database directory of the default database. For example,ifis the default database,the followingstatement reads the filefrom the database directory for,even though the statement explicitly loads the file into a table in thedatabase:
For security reasons,when reading text files located on the server,the files must either reside in the database directory or be readable by all. Also,to useon server files,you must have theprivilege. See. For non-load operations,if thesystem variable is set to a nonempty directory name,the file to be loaded must be located in that directory.
Usingis 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 server. On the other hand,you do not need theprivilege to load local files.
also affects error handling:
With,data-interpretation and duplicate-key errors terminate the operation.
With,data-interpretation and duplicate-key errors become warnings and the operation continues because the server has no way to stop transmission of the file in the middle of the operation. For duplicate-key errors,this is the same as ifis specified.is explained further later in this section.
（编辑：安卓应用网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
