H2数据库整理：选择什么？ [英] H2 database collation: what to choose?

问题描述

经过大量的阅读和实验，似乎我希望以PRIMARY的强度进行搜索，但是想要TERTIARY或IDENTICAL的进行排序。 主要问题：使用H2（或任何其他数据库）是否可以实现？

第二个问题：我是这里唯一的一个人还是愿意你们中的任何人也喜欢上述组合吗？进行一些确认将有助于我的理智。

背景：
似乎只能在创建数据库时一开始设置排序规则。所以我想确保选择正确的一个。我主要考虑的是这些用例（目前）：

1. 一个搜索字段，用户可以在其中开始键入以过滤表格：在这里，PRIMARY似乎是最合适的，以避免遗漏任何结果（用户习惯使用Google ...）。虽然，能够为用户提供启用二级或三级归类以进行更精确的搜索的选项会很好。




2. 订购：当用户单击表列以对内容进行排序，则TERTIARY / IDENTICAL排序似乎合适。

我在这里阅读了官方的H2文档：。

对于纯H2解决方案，您可以使用 H2别名计算列或具有查询条件的a>。第一种解决方案允许建立索引以加快查询速度。

After a lot of reading and experimentation, it seems like I want PRIMARY strength for searching, but TERTIARY or IDENTICAL for ordering. Main question: Is that possible to achieve with H2 (or any other DB)?

Secondary question: Am I the only one here or would any of you also like the above combination? Some confirmation would be helpful for my sanity.

Background: It seems like the collation can only be set at the very beginning when creating the database. So I want to make sure to pick the right one. I am mainly thinking of these use cases (for now):

1. A search field where the user can start typing to filter a table: Here PRIMARY seems the most appropriate, in order to avoid missing any results (user is used to Google...). Although, it would be nice to be able to give the user the option to enable secondary or tertiary collation to do more precise searching.

2. Ordering: When the user clicks a table column to order the contents, TERTIARY/IDENTICAL ordering seems appropriate. That's what I am used to from everyday experience.

I read the offical H2 docs here: http://www.h2database.com/html/commands.html#set_collation. and here: http://www.h2database.com/html/datatypes.html#varchar_ignorecase_type Some more related info: Collation STRENGTH and local language relation

The test sql (from https://groups.google.com/forum/?fromgroups=#!topic/h2-database/lBksrrcuGdY):

drop all objects;
set collation english STRENGTH PRIMARY;
create table test(name varchar);
insert into test values ('À'), ('Ä'), ('Â'), ('A'), ('à'), ('ä'), ('â'), ('a'), ('àa'), ('äa'), ('âa'), ('aa'), ('B'), ('b');
select * from test where name like 'a' order by name;
select * from test order by name;

解决方案

If you want to have two behaviours for a single data you have to:

• split data over two columns,
• or uses two operator sets.

For your purpose, it is common to store "canonical" representation of a raw data in order to search on canonical form and then sort/display raw data. May be you should use some "text search engine" such as Apache Lucene.

For pure H2 solutions, you can use H2 alias with Computed columns or with query criteria. First solution allows indexing to speed up your queries.

这篇关于H2数据库整理：选择什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！