mysql - H2数据库脚本无法导入MySQL

Question

我创建了一个本地H2数据库，我总是以MySQL模式打开它来插入数据。现在，我想使用 SCRIPT 导出它，以便使用 phpMyAdmin 将其导入远程服务器上的远程 MySQL 数据库。我得到以下信息:

SET LOCK_MODE 3;
;             
CREATE USER IF NOT EXISTS SA SALT '...' HASH '...' ADMIN;           
CREATE CACHED TABLE PUBLIC.RAWVALUEITEM(
    LANGUAGE VARCHAR(2) NOT NULL SELECTIVITY 1,
    RAWVALUE VARCHAR(40) NOT NULL SELECTIVITY 99,
    STRIPPED VARCHAR(40) NOT NULL SELECTIVITY 96
);
...

不幸的是，phpMyAdmin 导入并不顺利:

#1193 - Unknown system variable 'LOCK_MODE'

当我手动删除 set 指令时，我收到更多错误:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS SA SALT '...' HASH '...' at line 1

我用来将数据库导出为脚本的代码是:

public static final String DB_DIR_LOCATION = "E:/Temp/FWDB/";
public static final String H2_CONNECTION = "jdbc:h2:file:"
    + DB_DIR_LOCATION + "FWDB_PHP_TEST" + "Mode=MySQL;IFEXISTS=TRUE";

public static void main(String[] args) throws SQLException {

    Connection conn = DriverManager.
        getConnection(H2_CONNECTION, "sa", "");

    PreparedStatement ps;

    ps = conn.prepareStatement("SCRIPT TO 'E:/Temp/FWDB/FWDB_EXPORT.gz' " 
        + "COMPRESSION GZIP");
    ps.execute();

}

如何从我的数据库生成一个脚本，该脚本将由远程服务器上的 phpMyAdmin 成功导入？

Answer 1

SCRIPT TO command生成的SQL脚本不是跨平台的。更好的解决方案可能是使用数据库工具，例如 SQuirreL DB Copy Plugin或另一个database tool .