mysql - 我可以在 postgresql 数据库中导入 sql 转储吗

Question

我有 mysql 转储 localhost.sql。但是当我尝试将其导入到 postgresql 数据库时，我收到了如下错误:

psql:localhost.sql:10: ERROR:  unrecognized configuration parameter "sql_mode"
psql:localhost.sql:11: ERROR:  unrecognized configuration parameter "autocommit"
START TRANSACTION
psql:localhost.sql:13: ERROR:  unrecognized configuration parameter "time_zone"
psql:localhost.sql:24: ERROR:  syntax error at or near "NOT"
LINE 1: CREATE DATABASE IF NOT EXISTS `peoplfv0_WPGHJ` DEFAULT CHARA...
                           ^
psql:localhost.sql:25: ERROR:  syntax error at or near "USE"
LINE 1: USE `peoplfv0_WPGHJ`;
        ^
psql:localhost.sql:39: ERROR:  syntax error at or near "`"
LINE 1: CREATE TABLE `dl_affiliates` (
                     ^
psql:localhost.sql:47: ERROR:  syntax error at or near "`"
LINE 1: INSERT INTO `dl_affiliates` (`affiliates_id`, `affiliates_na...
                    ^
psql:localhost.sql:65: ERROR:  syntax error at or near "`"
LINE 1: CREATE TABLE `dl_artist` (
                     ^
psql:localhost.sql:94: ERROR:  syntax error at or near "`"

我可以将此 sql 转储转换为 postgresql 格式以从 mysql 转储导入数据吗？

Answer 1

您可以尝试 mysqldump 的 --兼容 选项:

mysqldump --compatible=postgresql dbname > dbname.sql

根据 MySQL 文档:

--compatible=name

Produce output that is more compatible with other database systems or with older MySQL servers. The value of name can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, or no_field_options. To use several values, separate them by commas. These values have the same meaning as the corresponding options for setting the server SQL mode. See Section 5.1.10, “Server SQL Modes”.

This option does not guarantee compatibility with other servers. It only enables those SQL mode values that are currently available for making dump output more compatible. For example, --compatible=oracle does not map data types to Oracle types or use Oracle comment syntax.

正如文档所说，它不能保证与 Postgres 语法完全兼容，但它有很大帮助，有时可能是解决方案。