Before  , there was a small test project that needed to synchronize some local data to Alibaba cloud database regularly. It was implemented by executing LOAD DATA with the most mechanical script. Here is a brief introduction.

LOAD DATA can quickly read text file data into the table (LOAD DATA is usually 20 times faster than INSERT statement when importing data), LOAD DATA and SELECT To export data from a table to a text file, SELECT INTO OUTFILE, otherwise LOAD DATA is used. The syntax of the FIELDS and LINES clauses of the two is the same.

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL] --LOCAL: Read text file from client host
    INFILE 'file_name'
    [REPLACE | IGNORE] --REPLACE: The input line replaces the existing duplicate line, IGNORE: Skip duplicate lines
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name] --Specifies that all fields in the file are interpreted to have the same character set, regardless of the data type of the column to which the field value is loaded
    [{FIELDS | COLUMNS} --FIELDS Must be on LINES before
        [TERMINATED BY 'string'] --Column field separator, default\t
        [[OPTIONALLY] ENCLOSED BY 'char'] --Specifies the inclusion character of the column value, which is empty by default
        [ESCAPED BY 'char'] --Escape character, default\
    ]
    [LINES
        [STARTING BY 'string'] --Line start
        [TERMINATED BY 'string'] --Line break, default\n
    ]
    [IGNORE number {LINES | ROWS}] --Skip the first few lines specified when importing
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT}
        [, col_name={expr | DEFAULT}] ...]

FIELDS ESCAPED BY controls how special characters are read or written:

Several official examples:

# Fields are separated by commas, field values are enclosed in double quotation marks, the first line of the data file begins with the column name, and the line ends with a carriage return / line feed pair.
LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES;
# Lines ending with a carriage return / line feed pair
LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes
  FIELDS TERMINATED BY ''
  LINES TERMINATED BY '\n%%\n' (joke);

                         . Here's an explanation: LOCAL can only be used when both the server and the client are configured to allow, for example, if mysqld is in LOCAL_ If the system variable is disabled by infile, LOCAL cannot be used.

• If LOCAL is specified, the file will be read by the client program on the client host and sent to the server; the file can be specified as a full path name to specify its exact location; if given as a relative path name, the name will be interpreted relative to the directory where the client program is started;
• If LOCAL is not specified, the file must be on the server host and can be read directly by the server. The server uses the following rules to locate files: if the file name is an absolute path name, the server will use it according to the given name; if the file name is a relative path name with one or more leading components, the server will search for files relative to the server data directory; if the given file name does not have a leading component, the server will search for files in the default database Find the file in the database directory.

                    
[root@mysqlplus2 ~]# crontab -e

01 00 * * 1-6 /home/mysqlbak/script/bigdata_impinc.sh

   export the required data first:

SELECT insId,areaNo,jointId,contractId,sold
FROM tb_jyx_ins_bizarea into outfile "/home/databak/tb_bizarea.txt" fields terminated by ',';

SELECT tb_member.mid,openid,unionid,cellPhone,nickname,
    sex,headImgUrl,country,city,province,xlanguage,privilege,
    nextMid,createDatetime,bindphoneDatetime
FROM tb_member where TO_DAYS(NOW())-TO_DAYS(createDatetime)=1 into outfile "/home/databak/tb_member.txt" fields terminated by ',';

SELECT orgID，orgname，disno,province,city,orgnum,fullname,orgtype
 FROM tb_organization into outfile "/home/databak/tb_organization.txt" fields terminated by '|';

Take a look at LOAD DATA:

set autocommit=0;
set unique_checks=0;
load data local infile "/home/databak/tb_bizarea.txt" into table tb_test_bizareafields terminated by ',';
load data local infile "/home/databak/tb_member.txt" replace into table tb_test_member fields terminated by ',';
load data local infile "/home/databak/tb_organization.txt" into table tb_test_organization(orgID，orgname，disno,province,city,orgnum,fullname,orgtype) fields terminated by '|';
set unique_checks=1;