InnoDB MySQL 表中的重音不敏感搜索！
我正在编写一个简单的搜索脚本，该脚本可查看特定表的两列。本质上，我正在寻找公司号码或其名称之间的匹配项。我在 SQL 中使用 LIKE 语句，因为我使用 InnoDB 表（这意味着没有全文搜索）。
问题是我在双语环境（法语和英语）中工作，并且法语中的一些字符有重音。我希望重音字符被视为与非重音字符相同，换句话说 é = e、e = é、à = a 等。SO 有很多与此问题相关的问题，但似乎没有一个有效为我。
这是我的 SQL 语句：
SELECT id, name FROM clients WHERE id LIKE '%éc%' OR name LIKE '%éc%';
我希望找到“école”和“ecole”，但它只找到“école”。
我还想指出，我的表都是 utf8_general_ci。
帮助我 StackOverflow，你是我唯一的希望！ :)
I am working on a simple search script that looks through two columns of a specific table. Essentially I'm looking for a match between either a company's number or their name. I'm using the LIKE statement in SQL because I am using InnoDB tables (which means no fulltext searches).
The problem is that I am working in a bilingual environment (french and english) and some of the characters in french have accents. I would like accented characters to be considered the same as their non-accented counterpart, in other words é = e, e = é, à = a, etc. SO has a lot of questions pertaining to the issue but none seem to be working for me.
Here is my SQL statement:
SELECT id, name FROM clients WHERE id LIKE '%éc%' OR name LIKE '%éc%';
I would like that to find "école" and "ecole" but it only finds "école".
I would also like to note that my tables are all utf8_general_ci.
Help me StackOverflow, you're my only hope! :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我将为您提供另一个答案。
我刚刚读到
utf8_general_ci不区分重音，所以你应该没问题。一种解决方案是使用
This 告诉客户端发送 SQL 语句的字符集。
另一种解决方案似乎是使用 MySQL 的
HEX()函数将重音字符转换为其十六进制值。但我找不到任何很好的例子来说明这一点，并且在阅读了HEX()的 MySQL 文档后，它看起来可能不起作用。I am going to offer up another answer for you.
I just read that
utf8_general_ciis accent-insensitive so you should be OK.One solution is to use
This tells the client what char set to send SQL statements in.
Another solution seems to be to use MySQL's
HEX()function to convert the accented chars into their Hex value. But I could not find any good examples of this working and after reading the MySQL docs forHEX()it looks like it probably will not work.您也许应该考虑将问题字符转换为对应的英文字符，然后将它们存储在不同的列中，可能称为“可搜索”或类似的列。每当您的主栏更新时，您必然需要更新此内容。
然后，您将有两列，一列包含重音字符，一列包含纯英语可搜索内容。
You maybe should consider converting the problem characters to their English counterparts, then storing them in a different column, perhaps called
searchableor similar. You would of cause need to update this whenever your main column was updated.You would then have two columns, one containing the accented characters and one containing the plain English searchable content.