侠客的博客

略懂Html,Css,JavaScript,JQuery,Linux,PHP,Java,Vue,Python

mysql into outfile 导出百万级大数据,三秒搞定

php尽管是世界上最好的语言,但是有一个问题,一直是困惑人的,就是在做数据库导出的时候,此时此刻,示例的标配是:php+mysql开发
很多人习惯用phpexcel插件导出mysql数据库的数据,数据量不多的情况下,基本上是没问题的,如果数据量不多的情况下,也可以把这个数据交给前端js完成,相应的插件也很多


但是如果,遇到大数据量,百万级的,想要导出,基本上就卡死,崩溃,502资源耗尽,等等


我这里示例一个三秒钟搞定的快速方法

select id,
       userid,
       ifnull(username, ''),
       ifnull(realname, ''),
       ifnull(recommend, ''),
       ifnull(reg_userid, ''),
       ifnull(parent_userid, ''),
       ifnull(phone, ''),
       ifnull(pos, ''),
       concat("`", membercard),
       ifnull(membercard_start, ''),
       ifnull(membercard_end, ''),
       ifnull(bank_name, ''),
       ifnull(bank_username, ''),
       concat("`", bank_card)
from xaake_user
into outfile '/Applications/MAMP/htdocs/user.csv' fields terminated by ',' escaped by '"' lines terminated by '\r\n';
//    这个项目主要是做分销,拉人头的,所以,里面用到了很多基础数据,不过老板不懂技术,要导出excel,所以,我就直接操作导出csv文件
//    这个导出文件是没有加上表头的

如果 要加上表头文件,那么就是下面的这个语法了

select *
into outfile '/Applications/MAMP/htdocs/user.csv' fields terminated by ',' escaped by '"' lines terminated by '\r\n'
from (select '字段1',
             '字段2',
             '字段3',
             '字段4',
             '字段5',
             '字段6',
             '字段7',
             '字段8',
             '字段9',
             '字段10',
             '字段11',
             '字段12',
             '字段13',
             '字段14',
             '字段15'
      union
      select id,
             userid,
             ifnull(username, ''),
             ifnull(realname, ''),
             ifnull(recommend, ''),
             ifnull(reg_userid, ''),
             ifnull(parent_userid, ''),
             ifnull(phone, ''),
             ifnull(pos, ''),
             concat("`", membercard),
             ifnull(membercard_start, ''),
             ifnull(membercard_end, ''),
             ifnull(bank_name, ''),
             ifnull(bank_username, ''),
             concat("`", bank_card)
      from xaake_user) b;
//    加上了表头了,就是在这样的

这个语法中间有三个参数

(1)TERMINATED BY用来指定字段值之间的符号,例如,“TERMINATED BY ','”指定了逗号作为两个字段值之间的标志。

(2)ENCLOSED BY子句用来指定包裹文件中字符值的符号,例如,“ENCLOSED BY ' " '”表示文件中字符值放在双引号之间,若加上关键字OPTIONALLY表示所有的值都放在双引号之间。

(3)ESCAPED BY子句用来指定转义字符,例如,“ESCAPED BY '*'”将“*”指定为转义字符,取代“\”,如空格将表示为“*N”。


急速三秒搞定,这里面牵涉到一个问题,就是,如果在php中执行这个,会出现啥情况,会不会报错,或者是否执行成功,那么我贴上php代码

$sql = <<<html
select *
into outfile '/Applications/MAMP/htdocs/chenshizhao/user.csv' fields terminated by ',' escaped by '"' lines terminated by '\r\n'
from (select '字段1',
             '字段2',
             '字段3',
             '字段4',
             '字段5',
             '字段6',
             '字段7',
             '字段8',
             '字段9',
             '字段10',
             '字段11',
             '字段12',
             '字段13',
             '字段14',
             '字段15'
      union
      select id,
             userid,
             ifnull(username, ''),
             ifnull(realname, ''),
             ifnull(recommend, ''),
             ifnull(reg_userid, ''),
             ifnull(parent_userid, ''),
             ifnull(phone, ''),
             ifnull(pos, ''),
             concat("`", membercard),
             ifnull(membercard_start, ''),
             ifnull(membercard_end, ''),
             ifnull(bank_name, ''),
             ifnull(bank_username, ''),
             concat("`", bank_card)
      from xaake_user) b;
html;
        dump(Db::execute($sql));

运行结果为:

int(1099845)
耗时:19.323156s

当然,直接在mysql命令行中操作,三秒钟就搞定了

这中间还有一个问题就是,这个操作 into outfile必须具有全局权限才行,不然的话,是无法导出成功的


这中间会有一个mysql的配置问题,就是提示secure_file_priv,mysql的这个配置参数,直接影响到账号是否可以读写文件

一般默认情况下,这个配置secure_file_priv=null
如果我们要开启这个权限的话,可以直接配置secure_file_priv=‘’即可


发表评论:

©Copyright 2019 52400073.com Author by xaake,基于 Zblog