如何将 H2Database 数据库文件转换为 MySQL 数据库 .sql 文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11359813/
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
How to convert H2Database database file to MySQL database .sql file?
提问by Lasith Malinga
I have some data in H2Databasefile and I want to convert it to MySQL .sqldatabase file. What are the methods I can follow?
我在H2Database文件中有一些数据，我想将其转换为 MySQL.sql数据库文件。我可以遵循哪些方法？
回答by Elo
In answer to Thomas Mueller, SquirrelSQL worked fine for me. Here is the procedure for Windows to convert a H2 database:
在回答 Thomas Mueller 时，SquirrelSQL 对我来说效果很好。以下是 Windows 转换 H2 数据库的过程：
Go to "drivers list", where everything is red by default.
Select "H2" driver, and specify the full path to "h2-1.3.173.jar" (for example) in "Extra Class Path". The H2 driver should display a blue check in the list.
Select your target driver (PostgreSQL, MySQL), and do the same, for example for PostgreSQL, specify the full path to "postgresql-9.4-1201.jdbc41.jar" in Extra Class Path.
Go to "Aliases", then click on "+" for H2 : configure your JDBC chain, for example copy/paste the jdbc chain you obtain when you launch H2, and do the same for your target database: click on "+", configure and "test".
When you double click on your alias, you should see everything inside your database in a new Tab. Go to the tables in source database, do a multi-select on all your tables and do a right-click : "Copy Table".
Go to your target database from Alias, and do a "Paste Table". When all tables are copied altogether, the foreign key references are also generated.
Check your primary keys : from H2 to PostgreSQL, I lost the Primary Key constraints, and the auto-increment capability. You could also rename columns and tables by a right click : "refactor". I used it to rename reserved words columns after full copy, by disabling name check in options.
This worked well for me.
转到“驱动程序列表”，默认情况下所有内容都是红色的。
选择“H2”驱动，在“Extra Class Path”中指定“h2-1.3.173.jar”的完整路径（例如）。H2 驱动程序应在列表中显示蓝色勾号。
选择您的目标驱动程序（PostgreSQL、MySQL），并执行相同操作，例如对于 PostgreSQL，在 Extra Class Path 中指定“postgresql-9.4-1201.jdbc41.jar”的完整路径。
转到“别名”，然后单击 H2 的“+”：配置您的 JDBC 链，例如复制/粘贴您在启动 H2 时获得的 jdbc 链，并对目标数据库执行相同操作：单击“+”，配置和“测试”。
当您双击别名时，您应该会在新选项卡中看到数据库中的所有内容。转到源数据库中的表，对所有表进行多选并右键单击：“复制表”。
从 Alias 转到目标数据库，然后执行“粘贴表”。当所有表都被完全复制时，也会生成外键引用。
检查您的主键：从 H2 到 PostgreSQL，我丢失了主键约束和自动递增功能。您还可以通过右键单击重命名列和表：“重构”。通过禁用名称签入选项，我使用它在完整复制后重命名保留字列。
这对我来说效果很好。
回答by Thomas Mueller
The SQL script generated by the H2 database is not fully compatible with the SQL supported by MySQL. You would have to change the SQL script manually. This requires that you know both H2 and MySQL quite well.
H2数据库生成的SQL脚本与MySQL支持的SQL不完全兼容。您必须手动更改 SQL 脚本。这要求您非常了解 H2 和 MySQL。
To avoid this problem, an alternative, probably simpler way to copy the data from H2 to MySQL is to use a 3rd party tool such as the SQuirreL SQLtogether with the SQuirreL DB Copy Pluginplugin. (First you need to install SQuirreL SQL and on top of that the SQuirreL DB Copy Plugin.)
为了避免这个问题，将数据从 H2 复制到 MySQL 的另一种可能更简单的方法是使用 3rd 方工具，例如SQuirreL SQL和SQuirreL DB Copy Plugin插件。（首先你需要安装 SQuirreL SQL 和 SQuirreL DB Copy Plugin。）
回答by Jens Baitinger
I created a Groovy script that does the migration from h2 to mysql. From there you could do a mysqldump. It requires that the tables exists in the Mysql database. It should work for ohter DBMS with minor changes.
我创建了一个 Groovy 脚本来执行从 h2 到 mysql 的迁移。从那里你可以做一个mysqldump。它要求表存在于 Mysql 数据库中。它应该适用于其他 DBMS，只需稍作更改。
@Grapes(
[
@Grab(group='mysql', module='mysql-connector-java', version='5.1.26'),
@Grab(group='com.h2database', module='h2', version='1.3.166'),
@GrabConfig(systemClassLoader = true)
])
import groovy.sql.Sql
def h2Url='jdbc:h2:C:\Users\xxx\Desktop\h2\sonardata\sonar'
def h2User='sonar'
def h2Passwd='sonar'
def mysqlUrl='jdbc:mysql://10.56.xxx.xxx:3306/sonar?useunicode=true&characterencoding=utf8&rewritebatchedstatements=true'
def mysqlUser='sonar'
def mysqlPasswd='xxxxxx'
def mysqlDatabase='sonar'
sql = Sql.newInstance(h2Url, h2User, h2Passwd, 'org.h2.Driver' )
def tables = [:]
sql.eachRow("select * from information_schema.columns where table_schema='PUBLIC'") {
if(!it.TABLE_NAME.endsWith("_MY")) {
if (tables[it.TABLE_NAME] == null) {
tables[it.TABLE_NAME] = []
}
tables[it.TABLE_NAME] += it.COLUMN_NAME;
}
}
tables.each{tab, cols ->
println("processing $tab")
println("droppin $tab"+"_my")
sql.execute("DROP TABLE IF EXISTS "+tab+"_my;")
sql.execute("create linked table "+tab+"_my ('com.mysql.jdbc.Driver', '"+mysqlUrl+"', '"+mysqlUser+"', '"+mysqlPasswd+"', '"+mysqlDatabase+"."+tab.toLowerCase()+"');")
sql.eachRow("select count(*) as c from " + tab + "_my"){println("deleting $it.c entries from mysql table")}
result = sql.execute("delete from "+tab+"_my")
colString = cols.join(", ")
sql.eachRow("select count(*) as c from " + tab){println("starting to copy $it.c entries")}
sql.execute("insert into " + tab + "_my ("+colString+") select "+colString+" from " + tab)
}
回答by Thomas Mueller
The H2 database allows you to create a SQL script using the SCRIPTSQL statement or the Script command line tool. Possibly you will need to tweak the script before you can run it against the MySQL database.
H2 数据库允许您使用SCRIPTSQL 语句或脚本命令行工具创建 SQL 脚本。可能您需要先调整脚本，然后才能针对 MySQL 数据库运行它。
回答by vidy
You can use fullconvert to convert database. it's easy to use.
您可以使用 fullconvert 来转换数据库。它很容易使用。
Follow steps shown here
按照此处显示的步骤操作
