AWS MySQL RDS数据归档导出导入字符转义问题求助

问题背景 #

在EC2实例上通过PHP构建RDS数据归档流程，需导出带JOIN条件的筛选数据。由于RDS不支持SELECT ... INTO OUTFILE，且mysqldump无法直接处理JOIN查询，采用mysql命令结合sed生成类CSV文件，但sed转义逻辑错误，导致LOAD DATA LOCAL INFILE导入数据时格式混乱、内容错误。

原命令的问题分析 #

原sed命令存在3个核心问题：

1. 单引号转义错误：s/'/\'/在shell环境下无法生效，单引号内的转义符不被解析，导致导出的单引号未被正确处理，直接破坏CSV结构。
2. 冗余处理无效：s/\\n/\n/g;s/\\t/\t/g属于多余操作，mysql -B导出的是原生制表符分隔数据，无需额外替换转义序列。
3. 路径不兼容：EC2实例多为Linux系统，原命令中的C:\xampp\htdocs\tmp\a.txt是Windows路径，在Linux环境下无法识别。

修正后的解决方案 #

1. 修正导出命令（适配Linux环境） #

使用以下命令生成格式合规的CSV文件，确保特殊字符被正确转义：

mysql -B -uroot -proot database_name -e "select a.* from api_response a LEFT JOIN user u on u.user_id=a.user_id where u.first_name LIKE 'ab%' LIMIT 100000;" | sed -e "s/'/''/g" -e 's/\t/","/g' -e 's/^/"/' -e 's/$/"/' > /tmp/a.txt

命令解析：

• s/'/''/g：将单引号替换为连续两个单引号，这是MySQL CSV导入的标准转义规则，导入时会自动解析为单个单引号。
• s/\t/","/g：把制表符分隔符替换为","，实现逗号分隔+双引号包裹字段的CSV格式。
• s/^/"/ + s/$/"/：给每行首尾添加双引号，确保字段被完整包裹。

2. 匹配格式调整导入命令 #

修正后的CSV文件采用双引号包裹、逗号分隔的格式，对应调整LOAD DATA命令：

LOAD DATA LOCAL INFILE '/tmp/temp.txt' IGNORE INTO TABLE `api_response` 
CHARACTER SET UTF8 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
ESCAPED BY '' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES;

关键调整：

• 添加ESCAPED BY ''：因为我们用双单引号转义单引号，无需使用反斜杠作为转义符，设置为空即可。
• 明确LINES TERMINATED BY '\n'：Linux环境下换行符为\n，避免因换行符不匹配导致行解析错误。

3. PHP执行shell命令的注意事项 #

在PHP中调用上述命令时，需规避风险：

• 避免明文密码：将MySQL账号密码写入~/.my.cnf配置文件，避免命令行明文泄露，示例：

  [client]
  user=root
  password=your_secure_password
  

• 防止Shell注入：若查询条件为动态参数，必须用escapeshellarg()转义，示例：

  $namePrefix = 'ab%';
  $sqlQuery = "select a.* from api_response a LEFT JOIN user u on u.user_id=a.user_id where u.first_name LIKE " . escapeshellarg($namePrefix) . " LIMIT 100000;";
  $shellCmd = "mysql -B database_name -e " . escapeshellarg($sqlQuery) . " | sed -e 's/'\\''/'\\''\\''/g' -e 's/\\t/\",\"/g' -e 's/^/\"/' -e 's/$/\"/' > /tmp/a.txt";
  exec($shellCmd, $output, $returnCode);
  

内容的提问来源于stack exchange，提问作者Abhishek kandari