MySQL Import data infile usage

Source: Internet
Author: User
Tags mysql import

MySQL Import data infile usage

Basic Syntax: load data [low_priority] [local] infile ' file_name txt ' [replace | ignore]
into table Tbl_name
[Fields
[Terminated by ' t ']
[optionally] enclosed by ']
[escaped by ']]
[Lines terminated by ' n ']
[Ignore number lines]
[(Col_name,)]

The load data infile statement reads from a text file into a table at a high speed. before using this command, the MYSQLD process (service) must already be running. for security reasons, when reading a text file located on the server, the file must be in the database directory or read by everyone. Also, in order to use the load data infileon the files on the server, you must have file permissions on the server host . 1 If you specify the keyword low_priority, then MySQL will wait until no one else reads the table before inserting the data. You can use the following command:
Load Data low_priority infile "/home/mark/data sql" into table Orders; 2 if the local keyword is specified, the file is read from the client host. If local is not specified, the file must be located on the server. 3 Replace and ignore keyword controls duplicate processing of existing unique key records. If you specify replace, the new row replaces the existing rows that have the same unique key value. If you specify ignore, skip the input of the duplicate rows of existing rows that have unique keys. If you do not specify any of the options, an error occurs when a duplicate key is found, and the remainder of the text file is ignored. For example: Load Data low_priority infile "/home/mark/data SQL" replace into table Orders; 4 separators (1) The fields keyword specifies the segmentation format of the file segment, and if this keyword is used, the MySQL profiler wants to see at least one of the following options:
terminated by delimiter: What character is used as the delimiter
Enclosed by Fields Enclose characters
escaped by Escape character terminated byThe delimiter that describes the field, which by default is the tab character (\ t)
Enclosed by describes the enclosed character of a field.
escaped by describes the escape character. The default is a backslash (backslash:\) For example: Load data infile "/home/mark/orders txt" replace into table Orders fields terminated by ', ' enclosed by ' "; (2) lines keyword specifies the separation of each record defaults think ' \ n ' is a line break If all two fields are specified, that field must precede lines . If you do not specify The fields keyword default value is the same as if you wrote this: field terminated by' \ t ' enclosed by ' "escaped by ' \ \ ' If you do not specify a lines clause, the default value is the same as if you write:lines terminated by ' \ n ' For example:load Data infile "/jiaoben/load.txt" replace into table test fields terminated by ', ' lines termin Ated by '/n '; 5 load data infile can import files into the database by the specified columns. When we want to import part of the data, we need to add some columns (column/field/field) to the MySQL database to accommodate some additional needs. For example, when we were upgrading from an Access database to a MySQL database The following example shows how to import data into a specified column (field):
load Data infile "/home/order txt" into table Orders (Order_number, order_date, customer_id); 6 when looking for files on a server host, the server uses the following rules:
(1) If an absolute pathname is given, the server uses that path name.
(2) If a relative pathname with one or more predecessor parts is given, the server searches for files relative to the server's data directory.
(3) If you give a file name that does not have a predecessor, the server looks for the file in the database directory of the current database.
For example: /myfile txt " The file is read from the server's data directory, and as " MyFile txt " The given file is read from the database directory of the current database. Note: null values in the field are denoted by \ n

MySQL Import data infile usage

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.