有没有一个简单的工具可以将 mysql 转换为 postgresql 语法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/92043/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a simple tool to convert mysql to postgresql syntax?
提问by meleyal
采纳答案by vog
There's a mysqldumpoption which makes it output PostgreSQL code:
有一个mysqldump选项可以让它输出 PostgreSQL 代码：
mysqldump --compatible=postgresql ...
回答by Linus Oleander
After some time on Google I found this post.
在谷歌上一段时间后，我找到了这篇文章。
- Install the mysql2psqlgem using
[sudo] gem install mysql2psql. - Create a config file by running
mysql2psql. You'll see an error but amysql2psql.ymlfile should have been created. - Edit
mysql2psql.yml - Run
mysql2psqlagain to migrate you data.
- 安装mysql2psqlgem 使用
[sudo] gem install mysql2psql. - 通过运行创建一个配置文件
mysql2psql。您会看到一个错误，但mysql2psql.yml应该已经创建了一个文件。 - 编辑
mysql2psql.yml mysql2psql再次运行以迁移您的数据。
Tip: Set force_truncateto truein your mysql2psql.ymlconfig file if you want the postgresql database to be cleared before migrating your data.
提示：如果您希望在迁移数据之前清除 postgresql 数据库，请在您的配置文件中设置force_truncate为。truemysql2psql.yml
回答by Micha? Powaga
I've used py-mysql2pgsql. After installation it needs only simple configuration file in yml format (source, destination), e.g.:
我用过py-mysql2pgsql。安装后只需要yml格式的简单配置文件（源、目标），例如：
# if a socket is specified we will use that
# if tcp is chosen you can use compression
mysql:
hostname: localhost
port: 3306
socket: /tmp/mysql.sock
username: mysql2psql
password:
database: mysql2psql_test
compress: false
destination:
# if file is given, output goes to file, else postgres
file:
postgres:
hostname: localhost
port: 5432
username: mysql2psql
password:
database: mysql2psql_test
Usage:
用法：
> py-mysql2pgsql -h
usage: py-mysql2pgsql [-h] [-v] [-f FILE]
Tool for migrating/converting data from mysql to postgresql.
optional arguments:
-h, --help show this help message and exit
-v, --verbose Show progress of data migration.
-f FILE, --file FILE Location of configuration file (default:
mysql2pgsql.yml). If none exists at that path,
one will be created for you.
More on its home page https://github.com/philipsoutham/py-mysql2pgsql.
回答by R.Sehdev
Try this one , it works like charm !!
试试这个，它就像魅力一样！
http://www.sqlines.com/online
回答by Arthur Thomas
There is one piece of pay software listed on this postgresql page: http://www.postgresql.org/download/products/1
此 postgresql 页面上列出了一种付费软件：http: //www.postgresql.org/download/products/1
and this is on pgFoundry: http://pgfoundry.org/projects/mysql2pgsql/
这是在 pgFoundry 上：http://pgfoundry.org/projects/mysql2pgsql/
回答by Dana the Sane
Have a look at PG Foundry, extra utilities for Postgres tend to live there. I believe that the tool you're looking for does exist though.
看看PG Foundry，Postgres 的额外实用程序往往存在于那里。我相信您正在寻找的工具确实存在。
回答by Cees Timmerman
This pagelists the syntax differences, but a simple working query converter i haven't found yet. Using an ORMpackage instead of raw SQL could prevent these issues.
此页面列出了语法差异，但我还没有找到一个简单的工作查询转换器。使用ORM包而不是原始 SQL 可以防止这些问题。
I'm currently hacking up a converter for a legacy codebase:
我目前正在为旧代码库编写转换器：
function mysql2pgsql($mysql){
return preg_replace("/limit (\d+), *(\d+)/i", "limit offset ", preg_replace("/as '([^']+)'/i", 'as ""', $mysql)); // Note: limit needs order
}
For CREATEstatements, SQLinesconverts most of them online. I still had to edit the mysqldump afterwards, though:
对于CREATE语句，SQLines在线转换了大部分。不过，我之后仍然必须编辑 mysqldump：
"mediumtext" -> "text", "^LOCK.*" -> "", "^UNLOCK.*" -> "", "`" -> '"', "'" -> "''" in 'data', "0000-00-00" -> "2000-01-01", deduplicate constraint names, " CHARACTER SET utf8 " -> " ".
"int(10)" -> "int" was missed in the last table, so pass that part of the mysqldump through http://www.sqlines.com/online again.
回答by Ilya Kochetov
you will most likely never get a tool for such task which would do all of your job for you. be prepared to do some refactoring work yourself.
您很可能永远不会获得可以为您完成所有工作的此类任务的工具。准备好自己做一些重构工作。
