H2数据库特殊字符排序错误

huangapple go评论52阅读模式
英文:

H2 DB Incorrect special characters sorting

问题

I'm trying to sort column data in my h2 database, but it isn't correct.

I think that change of collation may help. I tried to set collation in datasource url in many ways e.g.:
spring.datasource.url = jdbc:h2:mem:testdb;COLLATION='ENGLISH'

spring.datasource.url = jdbc:h2:mem:testdb;SET COLLATION='EN'

spring.datasource.url = jdbc:h2:mem:testdb;SET COLLATION ENGLISH STRENGTH PRIMARY'

And I'm still having a syntax error like this Syntax error in SQL statement "SET COLLATION 'ENGLISH'[*]"; expected "identifier"; SQL statement: SET COLLATION 'ENGLISH' [42001-200]

Am I doing something wrong? Or is there some other way to solve my problem?
Thanks in advance!

英文:

I'm trying to sort column data in my h2 database, but it isn't correct.

H2数据库特殊字符排序错误

I think that change of collation may help. I tried to set collation in datasource url in many ways e.g.:
spring.datasource.url = jdbc:h2:mem:testdb;COLLATION='ENGLISH'

spring.datasource.url = jdbc:h2:mem:testdb;SET COLLATION='EN''

spring.datasource.url = jdbc:h2:mem:testdb;SET COLLATION ENGLISH STRENGTH PRIMARY'

And I'm still have an syntax error like this Syntax error in SQL statement "SET COLLATION 'ENGLISH'[*]"; expected "identifier"; SQL statement:
SET COLLATION 'ENGLISH' [42001-200]

Am I doing something wrong? Or there is some other way to solve my problem?
Thanks in advance!

答案1

得分: 0

H2控制台使用浏览器提供的简单Array.sort()排序功能,不受H2的排序设置影响。我为这个问题创建了一个功能请求:
https://github.com/h2database/h2database/issues/2694

URL jdbc:h2:mem:testdb;COLLATION=POLISH是正确的。您可以测试它是否起作用:

SELECT * FROM
    (VALUES 'AAAAAA', 'LLLLLLLLL', 'ZZZZZZ', 'ĄĄĄĄĄĄ', 'ŁŁŁŁŁŁ') T(V)
    ORDER BY V;
> V
> --------- 
> AAAAAA
> ĄĄĄĄĄĄ
> LLLLLLLLL
> ŁŁŁŁŁŁ
> ZZZZZZ

如果没有POLISH排序,结果将与您的截图一样。

因此,明显的解决方法是在您的查询中添加ORDER BY BUILDINGS_NUMBER

请注意,有不同强度级别的排序。例如,对于SECONDARY,您可以使用jdbc:h2:mem:testdb;COLLATION=POLISH STRENGTH SECONDARY。有关更多详细信息,请参见文档:
https://h2database.com/html/commands.html#set_collation

英文:

H2 Console uses browser's sorting capabilities provided by simple Array.sort(), they aren't affected by collation setting of H2. I created a feature request for this problem:
https://github.com/h2database/h2database/issues/2694

The URL jdbc:h2:mem:testdb;COLLATION=POLISH is correct. You can test that it works:

SELECT * FROM
    (VALUES 'AAAAAA', 'LLLLLLLLL', 'ZZZZZZ', 'ĄĄĄĄĄĄ', 'ŁŁŁŁŁŁ') T(V)
    ORDER BY V;
> V
> --------- 
> AAAAAA
> ĄĄĄĄĄĄ
> LLLLLLLLL
> ŁŁŁŁŁŁ
> ZZZZZZ

Without POLISH collation result will be just like on yours screenshot.

So the oblivious workaround is to append ORDER BY BUILDINGS_NUMBER to your query.

Note that there are different strength levels of collation. For example, for SECONDARY you can use jdbc:h2:mem:testdb;COLLATION=POLISH STRENGTH SECONDARY. See documentation for more details:
https://h2database.com/html/commands.html#set_collation

答案2

得分: 0

非常感谢您将这个问题带来。
我的 Quarkus 应用程序与 H2 数据库存在类似问题。在我的 application.properties 文件中添加:

quarkus.datasource.jdbc.url=jdbc:h2:mem:testdb;COLLATION=POLISH

可以解决这个问题,并且所有的 ORDER BY 查询都会返回预期的结果。

英文:

Thanks very much for bringing that in.
Had similar problem with my Quarkus application hooked to H2 database. Adding:

quarkus.datasource.jdbc.url=jdbc:h2:mem:testdb;COLLATION=POLISH

at my application.properties file solves the problem and all ORDER BY queries returning expected results.

huangapple
  • 本文由 发表于 2020年6月6日 00:23:03
  • 转载请务必保留本文链接:https://go.coder-hub.com/62219932.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定