通过csv从mysql导出数据并导到另一数据库的命令
导出数据（不包含表结构）：
SELECT fileds1,fileds2,fileds3 INTO OUTFILE ‘/path/to/csvfile/tmp.csv’ FIELDS TERMINATED BY ‘|’ OPTIONALLY ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’ FROM table_test order by fileds1; //从mysql中导出csv文件，也可以指定导出数据量的多少，注意，这里如果是远程连到另一mysql上的话则保存的csv文件也在另一服务器上。
LOAD DATA INFILE ‘/path/to/csvfile/tmp.csv’ IGNORE INTO TABLE `table_test` character set utf8 FIELDS TERMINATED BY ‘|’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’;//
加载CSV文件到mysql里面。
这条语句把SQL结果保存成csv文件，便于在excel下查看
select recdate,cid,uid ,CONVERT(game,CHAR(20)) as game,ip ,did,did_count INTO OUTFILE ‘/tmp/20140618repetition.csv’ FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ FROM (select concat(did,’_',cid) as d_d,recdate,cid,uid,game,ip,did from first_login_20140618 where recdate = ’2014-06-18′ and game = ’1389689822864230′ ) as a right join (select count(did) as did_count,concat(did,’_',cid) as d_d from sy_logs.first_login_20140618 where recdate = ’2014-06-18′ and game = ’1389689822864230′ group by cid,game,ip having did_count > 1) as b on a.d_d = b.d_d;
——————————————————-分割线————————————————————-
通过load data导数据的时候出现了这样的错误，
#1045 – Access denied for user ‘user’@'localhost’ (using password: YES)
解决方案是在sql加上local关键字，这样mysql就不会通过tcp获取数据，而是从本地拉取。例如：
load data local infile ‘/tmp/co_user_test.csv’ into table test.co_user_test character set utf8 fields terminated by “,” enclo
sed by ‘”‘ lines terminated by ‘\r\n’;
——————————————————-分割线————————————————————-
注意，我们数据库如果是使用utf8字符集编码的，导出的数据如果包含了多字节数据在Windows下（GBK环境）是会乱码的（WPS会兼容编码，EXCEL没兼容编码），此时我们可以加多字符集的设置CHARACTER SET gbk，例如上面的SQL改装成：
SELECT fileds1,fileds2,fileds3 INTO OUTFILE ‘/path/to/csvfile/tmp.csv’ CHARACTER SET gbk FIELDS TERMINATED BY ‘|’ OPTIONALLY ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’ ;
这样导出的数据在Windows EXCEL下就不会乱码了。
按二维数组中某一元素的值来排序 strtotime,计算日期很好用的一个函数