I am currently using mysql as my database and use flyway to manage database schema. All my unit tests are running against mysql and they are running really slow with adding more unit tests. Now I want to change the database from mysql to h2 memory database in unit tests. Below is my setting for h2 db connection:

我目前使用mysql作为数据库，并使用flyway管理数据库模式。我所有的单元测试都运行在mysql上，随着添加更多的单元测试，它们运行得非常慢。现在我想在单元测试中将数据库从mysql更改为h2内存数据库。下面是我对h2 db连接的设置:

#Datasource
spring.datasource.url=jdbc:h2:mem:testDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE;DATABASE_TO_UPPER=true
spring.datasource.username=
spring.datasource.password=
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.default-transaction-isolation-level=1

When I run flywayMigrate, I got some sql errors. Below is one example, this sql is used to create a table on mysql but failed to run on h2.

当我运行flywaymigration时，我得到了一些sql错误。下面是一个例子，这个sql用于在mysql上创建表，但是在h2上运行失败。

CREATE TABLE `file_storage` (
  `id` BIGINT(64) NOT NULL AUTO_INCREMENT,
  `file_name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
DEFAULT CHARACTER SET = utf8;

below is the error I got from h2. I don't know what wrong with my sql. Is there a way for h2 to accept mysql database schema?

下面是我从h2得到的误差。我不知道我的sql有什么问题。h2是否有办法接受mysql数据库模式?

Execution failed for task ':dbschema:flywayMigrate'.
> Error occurred while executing flywayMigrate

  Migration V2016_02_26_12_59__create_file_storage.sql failed
  -----------------------------------------------------------
  SQL State  : 42000
  Error Code : 42000
  Message    : Syntax error in SQL statement "CREATE TABLE ""FILE_STORAGE"" (
    ""ID"" BIGINT(64) NOT NULL AUTO_INCREMENT,
    ""FILE_NAME"" VARCHAR(45) NULL,
    PRIMARY KEY (""ID""))
  DEFAULT CHARACTER[*] SET = UTF8 "; SQL statement:
  CREATE TABLE `file_storage` (
    `id` BIGINT(64) NOT NULL AUTO_INCREMENT,
    `file_name` VARCHAR(45) NULL,
    PRIMARY KEY (`id`))
  DEFAULT CHARACTER SET = utf8 [42000-190]
  Location   : db/migration/V2016_02_26_12_59__create_file_storage.sql (/Users/yzzhao/dev/cooltoo/cooltoo_backend/dbschema/build/resources/main/db/migration/V2016_02_26_12_59__create_file_storage.sql)
  Line       : 1
  Statement  : CREATE TABLE `file_storage` (
    `id` BIGINT(64) NOT NULL AUTO_INCREMENT,
    `file_name` VARCHAR(45) NULL,
    PRIMARY KEY (`id`))
  DEFAULT CHARACTER SET = utf8

  Syntax error in SQL statement "CREATE TABLE ""FILE_STORAGE"" (
    ""ID"" BIGINT(64) NOT NULL AUTO_INCREMENT,
    ""FILE_NAME"" VARCHAR(45) NULL,
    PRIMARY KEY (""ID""))
  DEFAULT CHARACTER[*] SET = UTF8 "; SQL statement:
  CREATE TABLE `file_storage` (
    `id` BIGINT(64) NOT NULL AUTO_INCREMENT,
    `file_name` VARCHAR(45) NULL,
    PRIMARY KEY (`id`))
  DEFAULT CHARACTER SET = utf8 [42000-190]

EDIT

I have hundreds of sql scripts which is running fine in mysql. So I don't want to change anything in these scripts. Is there a way to allow h2 accepts mysql script?

我有数百个sql脚本在mysql中运行良好。所以我不想改变这些脚本中的任何内容。有没有办法让h2接受mysql脚本?

2 个解决方案

#1

According to this description, you may try to use your H2 database in MySQL Compatibility Mode, by setting it in the connection string as MODE=MySQL. Here is exactly what is said about it:

根据此描述，您可以尝试在MySQL兼容模式下使用H2数据库，方法是将其设置为Mode =MySQL连接字符串。下面是关于它的一些确切的说法:

To use the MySQL mode, use the database URL jdbc:h2:~/test;MODE=MySQL or the SQL statement SET MODE MySQL.

要使用MySQL模式，请使用数据库URL jdbc:h2:~/test; mode =MySQL或SQL语句设置模式MySQL。

When inserting data, if a column is defined to be NOT NULL and NULL is inserted, then a 0 (or empty string, or the current timestamp for timestamp columns) value is used. Usually, this operation is not allowed and an exception is thrown.

当插入数据时，如果一个列被定义为非空，并且插入了NULL，那么将使用一个0(或空字符串，或时间戳列的当前时间戳)值。通常，不允许执行此操作并抛出异常。

Creating indexes in the CREATE TABLE statement is allowed using INDEX(..) or KEY(..). Example: create table test(id int primary key, name varchar(255), key idx_name(name));

可以使用INDEX(..)或KEY(..)在CREATE TABLE语句中创建索引。示例:创建表测试(id int primary key, name varchar(255)， key idx_name(name)));

Meta data calls return identifiers in lower case.

元数据调用返回标识符。

When converting a floating point number to an integer, the fractional digits are not truncated, but the value is rounded.

当将浮点数转换为整数时，小数位数不会被截断，但其值是整数。

Concatenating NULL with another value results in the other value.

将NULL与另一个值连接在一起会产生另一个值。

Text comparison in MySQL is case insensitive by default, while in H2 it is case sensitive (as in most other databases). H2 does support case insensitive text comparison, but it needs to be set separately, using SET IGNORECASE TRUE. This affects comparison using =, LIKE, REGEXP.

MySQL中的文本比较在默认情况下是不敏感的，而在H2中则是区分大小写(与大多数其他数据库一样)。H2确实支持不区分大小写的文本比较，但是需要使用set IGNORECASE TRUE分别设置它。这影响使用= REGEXP进行比较。

#2

Your issue can be seen with your example

你的问题可以从你的例子中看到。

CREATE TABLE `file_storage`
(
   'id` BIGINT(64) NOT NULL AUTO_INCREMENT, 
   `file_name` VARCHAR(45) NULL, 
   PRIMARY KEY (`id`)
)
DEFAULT CHARACTER SET = utf8;

The last line "DEFAULT CHARACTER SET = utf8" is setting a mySQL table option. H2 does not have such an option at either the table or schema level as it operates using Unicode at all times.

最后一行“默认字符集= utf8”设置了一个mySQL表选项。H2在表或模式级别都没有这样的选项，因为它一直使用Unicode进行操作。

If you have a lot of SQL DDL statements that have been written over the years for MySQL you are likely to see a lot of such issues.

如果您有许多SQL DDL语句，这些语句是多年为MySQL编写的，那么您可能会看到很多这样的问题。

#1