浅谈将文本导入 mysql 的两种方法

转载请注明出处：http://hi.baidu.com/leejun_2005/blog/item/ed2b3947084c35176a63e530.html

1、 load data infile ... into table ... fields terminated by ...  enclosed by ...'"'  (...)  set oo='xx';

# select * from temp_decli into outfile '/tmp/test3' fields terminated by '####'; 

今天要将一个文本文件导入 mysql 中，但遇到了一个奇怪的语法错误：

mysql>  load data infile '/tmp/test3' into table pub_app_base (uin , nick , app_name , app_url , app_desc , app_brief , app_type , icon_url , thumb_url , create_time , upload_time , update_time , upload_ip , comment_id , category , flag , reserve , exinfo , banner_url , come_from , secretkey , secrettoken , platform , gaid)  fields terminated by '####' enclosed by '"';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fields terminated by '#

坦率的讲，这个错误提示没啥用。。。只要是语法有问题，都会报这玩意，具体哪错了鬼才知道。。。查了下官方手册，有个例子，如下：

详见：http://dev.mysql.com/doc/refman/5.1/en/load-data.html

load data infile '/tmp/xxx.dat'
into table xxx
fields terminated by '|'
lines terminated by '\n'
(col1,
col2,
@col3,
@col4,
col5)
set
col3 = str_to_date(@col3, '%m/%d/%Y'),
col4 = str_to_date(@col4, '%d/%m/%Y')
;

也就说刚才的分隔符的位置应当放在数据前面做初始化，要不然，mysql引擎根本不知道你的数据应当如何进行域分割。

还有个问题：load data infile这个东东，数据库和文件必须在一个机器上才行，避免权限问题（mysql--Can't get stat of '' (Errcode: 13)  ），一般放在 /tmp 目录下，这个是mysql默认临时目录，可以 show variables like '%tmpdir%'; 查询：

mysql> show variables like '%tmpdir%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| slave_load_tmpdir | /tmp |
| tmpdir | /tmp |
+-------------------+-------+
2 rows in set (0.00 sec)

使用local命令可以解决路径问题。

文件的路径

如果指定了LOCAL，则被认为与连接的客户端有关：

•  如果指定了LOCAL，则文件会被客户主机上的客户端读取，并被发送到服务器。文件会被给予一个完整的路径名称，以指定确切的位置。如果给定的是一个相对的路径名称，则此名称会被理解为相对于启动客户端时所在的目录。
•  如果LOCAL没有被指定，则文件必须位于服务器主机上，并且被服务器直接读取。

当在服务器主机上为文件定位时，服务器使用以下规则：

• 如果给定了一个绝对的路径名称，则服务器使用此路径名称。
• 如果给定了带有一个或多个引导组件的相对路径名称，则服务器会搜索相对于服务器数据目录的文件。
• 如果给定了一个不带引导组件的文件名称，则服务器会在默认数据库的数据库目录中寻找文件。

注意，这些规则意味着名为./myfile.txt的文件会从服务器数据目录中被读取，而名为myfile.txt的同样的文件会从默认数据库的数据库目录中读取。

 

2、可以使用 mysqldump：

大致过程如下：

create table temp_xx as select * from pub_app_base where xxx;
mysqldump -t temp_xx;

mysql < xxx.sql

insert into table pub_app_base select xx,xx,xx from temp_xx;

 

3、关于mysql load infile 和 mysqldump 的详细用法请参见：

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

http://hi.baidu.com/leejun_2005/blog/item/afe1123e9d97e1f655e723f1.html

http://hi.baidu.com/leejun_2005/blog/item/9ad3b44519668056500ffeff.html