1、select into outfield 功能:

导出数据到pc的指定目录下。

1.1 MYSQL需要以管理员权限启动,否则可能会出现无法读写文件的现象

可以手动以管理员权限启动,或
windows下可以在服务中找到MYSQL 右键——》属性——》登录对话框中选择本地系统账户,然后重启mysql服务

1.2 修改配置文件mysql.ini(没有mysql.ini就是my.ini)

配置文件可以使用everything一类的工具进行搜索
注释原来的 secure_file_priv = "path" 添加 secure_file_priv =
表示不对文件目录进行要求,否则只能指定path 目录输出

2、语法:

SELECT ... INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        [export_options]

export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]

example:

SELECT customer_id, firstname, surname INTO OUTFILE '/exportdata/customers.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM `tb_run_record`;

SELECT * into OUTFILE  'C:/Users/Administrator/Desktop/t1.txt' FROM `tb_run_record` ;
SELECT * into OUTFILE  'C:/Users/Administrator/Desktop/t1.xls' FROM `tb_run_record` ;
SELECT * into OUTFILE  'C:/Users/Administrator/Desktop/t2.xlsx' FROM `tb_run_record` ;

3、使用

3.1 导出为txt格式文本文档

输入:
SELECT * FROM `tb_run_record` WHERE `user`= '1' into OUTFILE  'C:/Users/Administrator/Desktop/t1.txt';
输出:

20210301095426.png

输入:
SELECT * FROM `tb_run_record` WHERE `user`= '1' into OUTFILE  'C:/Users/Administrator/Desktop/t1.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'  ;
输出:

20210301095449.png

3.2 导出为Excel文档

语法没有区别,就是输出文件格式改成Excel的后缀就行

SELECT * FROM `tb_run_record` WHERE `user`= '1' into OUTFILE 'C:/Users/Administrator/Desktop/t1.xls' ;
SELECT * FROM `tb_run_record` WHERE `user`= '1' into OUTFILE 'C:/Users/Administrator/Desktop/t2.xlsx' ;

支持Excel的两种格式文档

小结

导出到文件关键在于 into OUTFILE 'path'.与前面的select ··· from xxx where ··· 语法没有关系,前面的查询语句按需求来写即可
mysql需要管理员权限才可以读写文件
mysql配置文件需要修改,允许输出文件到任意位置

最后编辑:2021年03月01日 ©著作权归作者所有

发表评论