测试：MySQL与H2不兼容

Question

我试图用H2测试我的JPA原生@查询。我的原生查询如下：

  @Query(
      value = "SELECT * FROM accounts " +
              " WHERE 'account_name' LIKE LOWER(CONCAT('%', COALESCE(:searchTerm, ''), '%')) ",
      countQuery = "SELECT count(*) FROM accounts " +
                   " WHERE 'account_name' LIKE LOWER(CONCAT('%', COALESCE(:searchTerm, ''), '%')) ",
      nativeQuery = true
  )

在编写单元测试时，我得到以下H2错误：

原因:org.h2.jdbc.jdbcsqlexception：找不到表“accounts”；SQL语句：从'aws_account_name'喜欢LOWER（CONCAT（“%”,COALESCE（？,“”）,“%”））限制的帐户中选择*？[42102-197]

我可以通过更改SQL语法将表名置于双引号中来修复H2错误：

  @Query(
      value = "SELECT * FROM \"accounts\" " +
              " WHERE 'account_name' LIKE LOWER(CONCAT('%', COALESCE(:searchTerm, ''), '%')) ",
      countQuery = "SELECT count(*) FROM \"accounts\" " +
                   " WHERE 'account_name' LIKE LOWER(CONCAT('%', COALESCE(:searchTerm, ''), '%')) ",
      nativeQuery = true
  )

然而，然后我的MySQL（实际的非测试环境）抱怨：

原因：java.SQL.SqlSyntaxerRoreXception：您的SQL语法中有错误；查看与您的MySQL server版本相对应的手册，以了解在“account_name”附近使用的正确语法，其中“account_name”在第1行类似LOWER(CONCAT('%',COALESCE('ab',''),‘

如何用MySQL和H2测试本机查询？

我之所以使用本机查询而不是JPQL，是因为我需要不区分大小写的搜索，并且允许“包含”匹配。

Answer 1

默认情况下，MySQL将双引号中的令牌识别为字符串文字，而不是标识符（即表名或列名）。

如果我们修改MySQLsql_mode以包含ansi_quotes，那么双引号中包含的令牌将被视为标识符。但这将在SQL中引起一个问题，因为字符串文字用双引号括起来，而不是用SQL标准的单引号括起来。

如果H2兼容模式设置为MySQL，那么我们应该可以使用普通的MySQL反勾字符来转义标识符。例如：

 SELECT *
   FROM `accounts`
        ^        ^

而且，MySQL也不会反对这个构造：

WHERE 'aws_account_name' LIKE
      ^                ^

但在单引号中，MySQL将标记'aws_account_name'视为字符串文字，而不是对列的引用。

如果这是一个列名，我们可以在列引用周围使用MySQL的反勾字符。

为了减少混淆并使将来的读者更容易理解，我们通常喜欢限定列引用，即使在不是严格要求的情况下也是如此。例如，使用短表别名：

 SELECT t.*
   FROM `accounts` t
  WHERE t.`aws_account_name` LIKE ...