在MySQL中使用希腊字符搜索功能 [英] Search function with Greek characters in MySQL

问题描述

我有一个古希腊语单词的搜索功能（ Wearch.nl ）。

旧希腊语的单词有不同的口音，ῦ与ὐ不同，但我希望如果你输入u，你会得到ῦ和ὐ的结果（以及其他5个变体）。我正在使用MySQL的LIKE函数来获取结果。

我可以搜索所有这些，但我希望它可以更短，更快。

解决方案

如果您可以更改列（或表）的字符集，然后将其设置为 utf8_general_ci （链接到手册）：

pre $ ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8_general_ci;


使用此字符集（不区分大小写，如 _ci ），重音字符具有相同的权重（用于 collat​​ion ），所以它们在相互比较时返回true（ link to manual ）：

非UCA排序规则有一对一
从字符代码映射到权重。
在MySQL中，这种排序规则是
不区分大小写和不区分变音的情况。
utf8_general_ci就是一个例子：'a'，
'A'，'À'和'á'每个都有不同的
字符代码，但是都有一个0x0041的权重
和比较等于。

  mysql> SET NAMES'utf8'COLLATE'utf8_general_ci'; 
查询OK，0行受影响（0.00秒）
 
 mysql> SELECT'a'='A'，'a'='À'，'a'='á'; 
 + ----------- + ----------- + ----------- + 
 | 'a'='A'| 'a'='À'| 'a'='á'| 
 + ----------- + ----------- + ----------- + 
 | 1 | 1 | 1 | 
 + ----------- + ----------- + ----------- + 
 1行（set）（ 0.06秒）
  

或者，如果您不能以这种方式更改数据库配置，则可以编写一个函数用它们的非重音等价物（即é - > e ）替换重音字符并将其写入专用搜索字段（建议使用全文搜索字段）。对此字段执行搜索并将重音字段返回给应用程序。

I have a search function for old Greek words (Wearch.nl).
The old greek words have mutch accents, ῦ is not the same as ὐ, but I want that if you type a "u" you get the results for ῦ and ὐ (and the other 5 variations). I am using the LIKE function of MySQL to get the results.
I could search for all of them but I hope it can be shorter and faster.

解决方案

If you are able to change the character set of your column (or table) then set it to utf8_general_ci (link to manual):

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8_general_ci;

With this character set (which is case insensitive, as denoted by _ci), accented characters have the same weight (the value used for collation), so they return true when compared with each other (link to manual):

Non-UCA collations have a one-to-one mapping from character code to weight. In MySQL, such collations are case insensitive and accent insensitive. utf8_general_ci is an example: 'a', 'A', 'À', and 'á' each have different character codes but all have a weight of 0x0041 and compare as equal.

mysql> SET NAMES 'utf8' COLLATE 'utf8_general_ci';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT 'a' = 'A', 'a' = 'À', 'a' = 'á';
+-----------+-----------+-----------+
| 'a' = 'A' | 'a' = 'À' | 'a' = 'á' |
+-----------+-----------+-----------+
|         1 |         1 |         1 |
+-----------+-----------+-----------+
1 row in set (0.06 sec)

Alternatively, or if you cannot alter the database configuration in this way, you could write a function to replace accented characters with their non-accented equivalents (i.e. é -> e) and write this into a dedicated search field (a full-text search field is recommended). Perform searches on this field and return the accented field to the application.

这篇关于在MySQL中使用希腊字符搜索功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！