超长SELECT查询导出MySQL数据及1045权限报错解决方法

场景说明 #

本次需求和常规按查询导出MySQL数据的场景逻辑一致，核心差异是待执行的SELECT语句长度接近1000行，常规短语句的导出方式容易触发长度限制。
已尝试的两种方案及问题：

• 直接通过mysql命令行-e参数拼接查询语句重定向输出，参考命令如下，语句过长时容易触发shell参数长度上限：

mysql -e "SELECT * from myTable WHERE id NOT IN (*大量指定ID值*)" -u myuser -pxxxxxxxxx mydatabase > output.txt

• 登录数据库后在查询语句末尾添加INTO OUTFILE '/tmp/querydump.csv'直接在服务端导出文件，执行时抛出权限错误：

ERROR 1045 (28000): Access denied for user 'admin'@'%' (using password: YES)

可行方案 #

方案1：SQL写入独立文件客户端导出（无权限要求、无长度限制，优先推荐） #

该方案完全规避INTO OUTFILE所需的数据库服务端FILE权限要求，也不会触发shell命令行参数长度限制，只要账号有对应表的SELECT权限即可执行：

1. 将完整的1000行SELECT语句单独保存为纯文本文件，例如命名为long_query.sql，文件内仅保留完整SQL语句即可，无需添加额外命令。
2. 执行导出命令，直接从文件读取SQL执行，结果重定向到本地文件：

mysql -u myuser -pxxxxxxxxx mydatabase < long_query.sql > output.txt

如果需要导出标准CSV格式，添加对应参数处理格式即可，无需修改SQL语句：

mysql -u myuser -pxxxxxxxxx mydatabase --batch --raw -N < long_query.sql | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > output.csv

注意：该方案所有读写操作都在客户端侧执行，导出的文件直接保存在当前执行命令的客户端机器上，不需要登录服务端取文件。

方案2：修复权限后使用INTO OUTFILE服务端导出（适合超大数据集） #

如果结果集体量极大（千万级以上），服务端导出性能更高，按以下步骤排查解决权限问题：

1. 先确认当前账号拥有全局FILE权限，登录数据库后执行SHOW GRANTS FOR CURRENT_USER();查看权限列表，如果没有FILE权限，用高权限管理员账号执行授权：

GRANT FILE ON *.* TO 'admin'@'%';
FLUSH PRIVILEGES;

2. 确认导出路径符合服务端安全限制，执行SHOW VARIABLES LIKE 'secure_file_priv';查看允许导出的目录：
   • 如果返回值为具体路径，INTO OUTFILE后的文件路径必须写在该目录下，写其他路径会直接被拒绝
   • 如果返回值为NULL，说明服务端完全禁止文件导出，只能使用方案1的客户端导出方式
3. 注意：INTO OUTFILE生成的文件保存在MySQL服务端所在服务器的对应路径下，不是当前操作的客户端机器，导出完成后需要自行从服务端下载文件。

方案3：mysqldump按条件导出（适合备份场景） #

如果导出的结果需要用于数据备份恢复，可以使用mysqldump的条件导出功能，超长查询条件同样可以写入文件传递，避免命令行长度限制：

mysqldump -u myuser -pxxxxxxxxx mydatabase myTable --where="id NOT IN (*超长ID列表*)" > dump_output.sql

内容的提问来源于stack exchange，提问作者Melvin Magro