txt存入mysql和csv_通过文件（.txt、.csv、.sql、.xml）向MySQL表中导入数据总结

准备数据表

mysql> use testdb2;

mysql> create table tbl_pet (

-> name varchar(10),

-> owner varchar(10),

-> species varchar(10),

-> sex varchar(1),

-> birth date,

-> death date)CHARSET = utf8;

1

2

3

4

5

6

7

8

mysql>usetestdb2;

mysql>createtabletbl_pet(

->namevarchar(10),

->ownervarchar(10),

->speciesvarchar(10),

->sexvarchar(1),

->birthdate,

->deathdate)CHARSET=utf8;

txt

准备txt文件

将空值用\N代替，每行数据内的分隔符为\t，行与行之间换行符为\r\n。然后将txt文件存放到/var/lib/mysql-files路径下

txt导入

mysql> LOAD DATA INFILE '/var/lib/mysql-files/pet.txt' INTO TABLE tbl_pet LINES TERMINATED BY '\r\n';

1

mysql>LOADDATAINFILE'/var/lib/mysql-files/pet.txt'INTOTABLEtbl_petLINESTERMINATEDBY'\r\n';

向表内继续导入新的数据，会自动追加在原有记录的后面。

错误小结：

Issue1：ERROR 1148 (42000): The used command is not allowed with this MySQL version

错误语句：mysql> LOAD DATA LOCAL INFILE ‘/var/lib/mysql/pet.txt’ INTO TABLE tbl_pet;

出错原因：多了一个LOCAL关键词。当文件在客户机端时要写LOCAL，在服务器端时不用写。我的txt文件是和MySQL数据库在同一虚拟机上的，所以不用加LOCAL。

Issue2：ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

出错原因：MySQL设置了secure-file-priv属性对应的路径，当数据文件从其他路径导入时便会报错。

解决办法：查看secure-file-priv指定路径并把数据文件放进去。

mysql> show variables like '%secure%';

+--------------------------+-----------------------+

| Variable_name | Value |

+--------------------------+-----------------------+

| secure_file_priv | /var/lib/mysql-files/ |

+--------------------------+-----------------------+

1

2

3

4

5

6

mysql>showvariableslike'%secure%';

+--------------------------+-----------------------+

|Variable_name|Value|

+--------------------------+-----------------------+

|secure_file_priv|/var/lib/mysql-files/|

+--------------------------+-----------------------+

Issue3：ERROR 1292 (22007): Incorrect date value: ” for column ‘death’ at row 1

出错原因：一开始直接把原始表格里的数据复制到txt文件里没有处理，空值的地方是空的，不符合MySQL里空值的表示方式，所以报错。

解决办法：将空值用\N代替。参考https://dev.mysql.com/doc/refman/8.0/en/loading-tables.html

Issue4：使用 LOAD DATA INFILE ‘/var/lib/mysql-files/pet.txt’ INTO TABLE tbl_pet; 导入数据仍然失败

错误提示：’ for column ‘death’ at row 1 date value: ‘N

出错原因：fields terminated by 默认值是’\t’，lines terminated by 默认值是’\n’，而Windows上的文件换行是’\r\n’，Mac OS X是’\r’。

解决办法：加上LINES TERMINATED BY ‘\r\n’，如下：

mysql> LOAD DATA INFILE '/var/lib/mysql-files/pet.txt' INTO TABLE tbl_pet LINES TERMINATED BY '\r\n';

1

mysql>LOADDATAINFILE'/var/lib/mysql-files/pet.txt'INTOTABLEtbl_petLINESTERMINATEDBY'\r\n';

csv

准备csv文件

csv导入

mysql> LOAD DATA INFILE '/var/lib/mysql-files/Movies.csv' INTO TABLE movie_tbl

-> CHARACTER SET utf8

-> FIELDS TERMINATED BY ','

-> LINES TERMINATED BY '\r\n'

-> IGNORE 1 LINES

-> (movie_id,movie_name,movie_score,region,year,movie_type,director,actor);

1

2

3

4

5

6

mysql>LOADDATAINFILE'/var/lib/mysql-files/Movies.csv'INTOTABLEmovie_tbl

->CHARACTERSETutf8

->FIELDSTERMINATEDBY','

->LINESTERMINATEDBY'\r\n'

->IGNORE1LINES

->(movie_id,movie_name,movie_score,region,year,movie_type,director,actor);

注意点：

1. CHARACTER SET utf8：因为有中文数据，所以指定utf-8编码。

2. FIELDS TERMINATED BY ‘,’：csv文件以 ‘,’ 分隔数据。

3. IGNORE 1 LINES：跳过原始文件中的第一行表头数据。如果不写，表头也会被当作数据导入。如果要跳过从头开始的多行，可以改变LINES前面的数字。

4.要导入数据的字段放在最后，将字段顺序和原始文件里的顺序保持一致。

sql

sql文件导入

mysql>source /var/lib/mysql-files/movies.sql; # 方法1

mysql>mysql -uroot -p movies < movies.sql # 方法2

1

2

mysql>source/var/lib/mysql-files/movies.sql;# 方法1

mysql>mysql-uroot-pmovies

xml

xml文件导入

准备xml文件

1

Free

2333.3333

2

Niki

1289.2333

1

2

3

4

5

6

7

8

9

10

11

12

1

Free

2333.3333

2

Niki

1289.2333

LOAD XML LOCAL INFILE '/var/lib/mysql-files/movies.xml' INTO TABLE movies;

1

LOADXMLLOCALINFILE'/var/lib/mysql-files/movies.xml'INTOTABLEmovies;

MySQL v5.0.7以后，MySQL的Stored Procedure中不能再运行LOAD XML INFILE 或者LOAD DATA INFILE。所以转换的程序(procedure)的编写方式与在此之前有所不同。这里，我们需要使用Load_File()和ExtractValue()这两个函数。

5.0.7以后版本可以使用如下存储过程实现

假设是要导入如下xml内容

1

2

3

4

5

6

存储过程

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `import_some_xml`(path varchar(255), node varchar(255))

BEGIN

declare xml_content text;

declare v_row_index int unsigned default 0;

declare v_row_count int unsigned;

declare v_xpath_row varchar(255);

set xml_content = load_file(path);

-- calculate the number of row elements.

set v_row_count = extractValue(xml_content, concat('count(', node, ')'));

-- loop through all the row elements

while v_row_index < v_row_count do

set v_row_index = v_row_index + 1;

set v_xpath_row = concat(node, '[', v_row_index, ']/@*');

insert into applicants values (

extractValue(xml_content, concat(v_xpath_row, '[1]')),

extractValue(xml_content, concat(v_xpath_row, '[2]')),

extractValue(xml_content, concat(v_xpath_row, '[3]'))

);

end while;

END

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

DELIMITER$$

CREATEDEFINER=`root`@`localhost`PROCEDURE`import_some_xml`(pathvarchar(255),nodevarchar(255))

BEGIN

declarexml_contenttext;

declarev_row_indexintunsigneddefault0;

declarev_row_countintunsigned;

declarev_xpath_rowvarchar(255);

setxml_content=load_file(path);

--calculatethenumberofrowelements.

setv_row_count=extractValue(xml_content,concat('count(',node,')'));

--loopthroughalltherowelements

whilev_row_index

setv_row_index=v_row_index+1;

setv_xpath_row=concat(node,'[',v_row_index,']/@*');

insertintoapplicantsvalues(

extractValue(xml_content,concat(v_xpath_row,'[1]')),

extractValue(xml_content,concat(v_xpath_row,'[2]')),

extractValue(xml_content,concat(v_xpath_row,'[3]'))

);

endwhile;

END

执行

call import_some_xml('你的XML文件路径', '/movies/movie');

1

callimport_some_xml('你的XML文件路径','/movies/movie');

浏览量:

91

0