使用mysqldump导出数据时对字段中包含的单引号的处理

最近在做一个日志统计项目，有一个辅助表是在MySQL数据库的，现在要将其迁移到Postgresql，自然是先用mysqldump将MySQL里面的数据导出，然后再导入到Postgresql即可。但在实际操作过程中，发现一些字段中本身就含有单引号，在导出的sql语句中，对这样的单引号使用’\’做了转义处理，比如原来的某个字段的值为：It’s very popular，导出的sql中大概是这样的格式：’It\’s very popular’，可以看到，字段中的单引号被转义。但使用导出的sql往Postgresql中插入数据时，却报告错误，因为Postgresql对’\’作为转义字符并不识别。

Question：
I use mysqldump to backup my mysql database. The problem is that the sql file generated by mysqldump doesn’t escapes single quotes properly.

Here is an example of the mysqldump generated sql script :

INSERT INTO someTable VALUES (1,’This ain\’t escaped correctly’);

That single quote escaping in “ain’t” doesn’t work and it makes the rest of the script being inside that string. Is there a way around this?

Answer：
That output of mysqldump is working as designed, and it is properly escaped, unless you try to restore the dump on a MySQL instance with SQL_MODE=NO_BACKSLASH_ESCAPES set.

There’s an outstanding feature request to make mysqldump use a pair of single-quotes to escape literal single-quotes, as per ANSI SQL. See http://bugs.mysql.com/bug.php?id=65941

In the meantime you might be able to convert from backslash-singlequote to pair-of-singlequotes with a command line this:

mysqldump test | sed -e “s/\\’/”/g” > test-dump.sql

I tried that out briefly by creating a dummy table in my test database and inserting the string “O’Hare” into the table. But that’s hardly a comprehensive test – I take no responsibility for this suggestion working in all cases.

结合另外一篇博客，完整的导出命令应写成：

mysqldump -u root -ppassword –no-create-db –no-create-info
–complete-insert –compatible=mssql –default-character-set=utf8 –skip-opt –compact –extended-insert=false dbname tablename|sed “s/\\’/”/g”>tablename.sql

参数的含义如下：

–no-create-db 不输出建database的脚本
–no-create-info 不输出创建table的脚本
–complete-insert 完整的插入，输出每个字段(如: insert into table(field1,field2,….) values(value1,value2,…))
–compatible=mssql 教本兼容格式,这里是mssql 这样教本里就会把table的名字和字段名用“号引起来，而不是mssql不能识别的`号。
–default-character-set=utf8 默认编码
–compact 输出尽量少的信息
–extended-insert=false 禁用它，可以每行生成一句insert语句，否则只输出一个。

至此，问题解决。在Postgresql中，两个单引号表示一个单引号，因此在导出时使用sed命令直接将\’替换成”，就可以将数据正确插入到Postgresql中了。

另外一篇文章放在下面，对解决类似的问题应该也有一些启发意义：
http://stackoverflow.com/questions/11647281/how-to-change-escaping-of-single-quotes-with-mysqldump-for-postgres-format

Postgresql官方文档中对于转义字符的说明：
http://www.postgresql.org/docs/9.4/interactive/sql-syntax-lexical.html（4.1.2.2节）