Mysql batch imports formatted text data

Mysql batch imports formatted text data
Syntax:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'    [REPLACE | IGNORE]    INTO TABLE tbl_name    [CHARACTER SET charset_name]    [FIELDS        [TERMINATED BY 'string']        [[OPTIONALLY] ENCLOSED BY 'char']        [ESCAPED BY 'char']    ]    [LINES        [STARTING BY 'string']        [TERMINATED BY 'string']    ]    [IGNORE number LINES]    [(col_name_or_user_var,...)]    [SET col_name = expr,...]

Data format of a column in the import table:

Beijing Shanghai Shenzhen Guangzhou Changsha Zhengzhou Hefei Xi'an Nanchang Nanjing Hangzhou Chengdu

SQL code:

LOAD DATA LOCAL INFILE 'C:\\Users\\XXX\\Desktop\\code.txt'INTO TABLE sys_sensitiveword_t(cityname);

Import data from the entire table to create an stu table:

create table stu_other(id int auto_increment primary key,stu_name varchar(50),age int);

After that, create a file named stu.txt. Each line contains a record, which is placed under the root directory of drive C. The default field separator is (tab), You can change it, as described below. And In the order of columns listed in the create table statement. You can use NULL for missing values (such as unknown gender, or dead dates of living animals. To represent the content in your text file, use/N (backslash, letter N ). For example, stu.txt has the following content:

100001    FLB100001     100001100002    FLB100002     100002100003    FLB100003     100003100004    FLB100004     100004100005    FLB100005     100005100006    FLB100006     100006100007    FLB100007     100007100008    FLB100008     100008

Here we use table separation. To load the upload file into the stu table, run the following command:

LOAD DATA LOCAL INFILE 'c://stu.txt' INTO TABLE stu;

The field separator and the line terminator are not specified here. The default is as follows: Field separator:

FIELDS TERMINATED BY '/t' ENCLOSED BY '' ESCAPED BY '//'

'/T' indicates that the field separator is the tab key. If you do not want to change it, change '/t' in fields terminated by'/t' to your own separator. Change the command to the following:

LOAD DATA LOCAL INFILE 'c:/stu.txt' INTO TABLE stu fields terminated by ‘ ‘;

Here I use spaces. Line terminator:

LINES TERMINATED BY '/n' STARTING BY ''

Note that if you use the editor in Windows (use/r/n as the line terminator) to create a file, use:

LOAD DATA LOCAL INFILE 'C://stu.txt' INTO TABLE stu LINES TERMINATED BY '/r/n';

(The line terminator '/R' should be used on an Apple machine running OS X '.) If you want to, you can explicitly specify the delimiter and the end mark of the row for the column value in the load data statement, but the default mark is the location and line break. This is enough to read the file into stu.txt. Export table data

select * from stu_t into outfile "c://stu_t.txt";

However, line breaks are not automatically generated using the record:

select * from stu_t into outfile "c://stu_t.txt" lines terminated by '/r/n';