MySQL如何“不区分大小写"工作和“不区分重音"在UTF-8中 [英] How to MySQL work "case insensitive" and "accent insensitive" in UTF-8
问题描述
我在"utf8-UTF-8 Unicode"中有一个模式作为字符集，并有一个排序规则"utf8_spanish_ci".
I have a schema in "utf8 -- UTF-8 Unicode" as charset and a collation of "utf8_spanish_ci".
所有内部表都是具有相同字符集和排序规则的InnoDB.
All the inside tables are InnoDB with same charset and collation as mentioned.
问题来了:
带有类似
SELECT *
FROM people p
WHERE p.NAME LIKE '%jose%';
我得到83条结果行.我应该有84个结果，因为我知道.
I get 83 result rows. I should have 84 results, because I know it.
更改位置:
WHERE p.NAME LIKE '%JOSE%';
我得到完全相同的83行. 使用JoSe，Jose，JOSe等组合时，将报告所有相同的83行.
I get the exact same 83 rows. With combinations like JoSe, Jose, JOSe, etc. All the same 83 rows are reported.
在游戏中玩口音时会出现问题.如果可以:
The problem comes when accents play in game. If do:
WHERE p.NAME LIKE '%josé%';
我没有结果. 0行.
但如果我这样做:
WHERE p.NAME LIKE '%JOSÉ%';
我只得到一行结果，所以只有1行.这是唯一用重音符号"jose"大写的行.
I get just one resulting row, so 1 row. This is the only row wich hace accented "jose" and capitalized.
我尝试过使用josÉ或JoSÉ或我所做的任何组合，只要带重音的字母保持大写或不大写，因为它实际上存储在数据库中，并且仍然返回唯一的行.如果我突然用JOSE中的大写字母将É"更改为é"，它将不返回任何行.
I've tried with josÉ, or JoSÉ or whatever combination I do, as long as the accented letter stays capitalized or not, as it really is stored in the database and it stills returning the only row. If I suddenly change "É" for "é" in whatever combination I do with the capitalization in JOSE, it returns no rows.
所以结论:
- 如果游戏中没有拉丁字符，则不区分大小写.
- 如果出现拉丁字符，则区分大小写.
- 对口音敏感，就像我搜索JOSE或jose一样，我只得到83行，而不是我需要的84行.
我想要什么?
- 要搜索"jose"，"JOSE"，José"，JOSÉ"，JÒSE"，jöse"，JoSÈ"，...必须返回我知道存在的84行.我该如何使搜索不区分大小写和拉丁"不区分大小写.
LIKE上的COLLATION之类的解决方案对我不起作用，不知道为什么...
Solutions like COLLATION on LIKE doesn't work for me, don't know why...
我该怎么办?
提前谢谢！
如果我喜欢:
WHERE p.NAME LIKE '%jose%' COLLATE utf8_general_ci;
我得到了错误:
COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'我也更改了列上所有可能的排序规则！
And I've changed all the possible collations on the columns too!
如果我做类似的事情:
WHERE p.NAME LIKE _utf8 '%jose%' COLLATE utf8_general_ci;
报告了相同的83行，好像我什么都没做...
The same 83 rows are reported, as if I've made nothing...
推荐答案
您已经尝试使用不区分重音的排序规则进行搜索和排序.
You have already tried to use an accent-insensitive collation for your search and ordering.
http://dev.mysql.com/doc/refman/5.0 /en/charset-collation-implementations.html
问题是，您的NAME列似乎存储在latin1(8位)字符集中.这就是为什么mySQL像这样抱怨您的原因:
The thing is, your NAME column seems to be stored in the latin1 (8-bit) character set. That's why mySQL is grumbling at you like this:
COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'
如果尝试，您可能会得到想要的结果
You may get the results you want if you try
WHERE CONVERT(p.NAME USING utf8) LIKE _utf8 '%jose%' COLLATE utf8_general_ci;
但是，要小心！
当在WHERE语句的列上使用任何类型的函数(在此示例中为CONVERT)时，您将挫败MySQL尝试使用索引优化搜索的尝试.如果该项目将变得很大(也就是说，如果表中将有很多行)，则需要以utf8格式而不是latin1格式存储数据. (您可能已经知道LIKE '%whatever%'搜索词也会击败MySQL的索引.)
When you use any kind of function (in this example, CONVERT) on the column in a WHERE statement, you defeat MySQL's attempts to optimize your search with indexes. If this project is going to get large (that is, if you will have lots of rows in your tables) you need to store your data in utf8 format, not latin1. (You probably already know that your LIKE '%whatever%' search term also defeats MySQL's indexing.)
这篇关于MySQL如何“不区分大小写"工作和“不区分重音"在UTF-8中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
