Enable ANSI compatibility when dumping inserts with mysqldump-CodePudding

I need to dump data with mysqldump and have it use ANSI quoting, so

"tablename"

instead of

`tablename`

quoting.

I can't change the server settings and --compatible options are not working, nor is --ansi a valid option to mysqldump.

How do I set this for the mysqldump session?

I don't want to pipe through sed.

CodePudding user response：

https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_compatible says:

--compatible=name

Produce output that is more compatible with other database systems or with older MySQL servers. The only permitted value for this option is ansi, which has the same meaning as the corresponding option for setting the server SQL mode.

(emphasis mine)

I just tested this on MySQL 8.0.32 and it works. It does not produce an error, it changes the identifier delimiter to " for compatibility with non-MySQL instances.

That's only part of the compatibility you need, though. There are many other statements in the dump output that will not work in other brands of RDBMS.