通过文件(.txt、.csv、.sql、.xml)向MySQL表中导入数据总结

准备数据表

txt

准备txt文件

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

txt导入

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

错误小结:

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指定路径并把数据文件放进去。

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’,如下:

csv

准备csv文件

csv导入

注意点:

1. CHARACTER SET utf8:因为有中文数据,所以指定utf-8编码。
2. FIELDS TERMINATED BY ‘,’:csv文件以 ‘,’ 分隔数据。
3. IGNORE 1 LINES:跳过原始文件中的第一行表头数据。如果不写,表头也会被当作数据导入。如果要跳过从头开始的多行,可以改变LINES前面的数字。
4.要导入数据的字段放在最后,将字段顺序和原始文件里的顺序保持一致。

sql

sql文件导入

xml

xml文件导入

准备xml文件

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

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

假设是要导入如下xml内容

存储过程

执行

0

发表评论

邮箱地址不会被公开。