天道酬勤,学无止境

MySQL Case Insensitive but Accent Sensitive UTF8 Unique Key

I know there are a lot of similar questions on SF, but I think mine is different enough to warrant a new question. I have a table that has a single column as utf8 with utf8_unicode_ci. It also has a unique key on this column along with another column marking a language code. The data in the column is in many different scripts (Latin with various accents, Chinese, and Russian, among others).

The problem is that I will sometimes want to enter in two words with different meanings that only differ by a diacritic (i.e. Spanish ano vs año). Since utf8_unicode_ci is both case and accent insensitive, it thinks these are the same and will only let me enter one. That sucks. Ideally, I'd just switch the whole column over to some collation that's case INsensitive but accent sensitive, but that doesn't seem to exist. A lot of different stuff uses this column, so I'd rather not change the column's default collation to utf8_bin for fear of messing stuff up with case sensitivity.

So, all of that said, I need some solution that will not affect default case sensitivity in the many existing queries that hit this column, but will let me add in words that differ by only a diacritic. Ideas? I'll switch just the unique key constraint to utf8_bin if I have to, but I'd rather not as I never want two things in the table that differ only by case.

评论

The only thing I can think of (without finding a collation that fits your needs) is to change something at the application layer (outside of MySQL) that will take care of the differentiation.

For instance, since you don't care about case, you can do something programmatically to lower the case of all the rows in the database. Then change the collation to utf8_bin.

Then you can, in the application, convert everything to lowercase before it enters the database (I'm guessing this will not affect the diacritic characters). That way, you will still get errors if people try to enter multiple cases, you should only have to change a few lines of code to precondition stuff entering the table, and you won't have the diacritic problem.

You don't have to reinvent the flat tire (reinvent the wheel) here.

There are two Spanish-language collations in MySQL:

utf8_spanish_ci (modern Spanish) and utf8_spanish2_ci (traditional Spanish)

These know the language well enough to know that N and n should be collated together, but that Ñ and ñ are different letters between N and O. In Spanish, the Ñ is actually a different letter, not an accent.

Set your column collation to utf8_spanish_ci and all will work the way you want.

受限制的 HTML

  • 允许的HTML标签:<a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • 自动断行和分段。
  • 网页和电子邮件地址自动转换为链接。

相关推荐
  • MySQL如何在UTF-8中“不区分大小写”和“不区分重音”的工作方式(How to MySQL work “case insensitive” and “accent insensitive” in UTF-8)
    问题 我在“ utf8-UTF-8 Unicode”中有一个模式作为字符集,并有一个排序规则“ utf8_spanish_ci”。 所有内部表都是具有相同字符集和排序规则的InnoDB。 问题来了: 用类似的查询 SELECT * FROM people p WHERE p.NAME LIKE '%jose%'; 我得到83个结果行。 我应该有84个结果,因为我知道。 更改位置: WHERE p.NAME LIKE '%JOSE%'; 我得到完全相同的83行。 使用JoSe,Jose,JOSe等组合时,将报告所有相同的83行。 当游戏中出现口音时,问题就来了。 如果这样做: WHERE p.NAME LIKE '%josé%'; 我没有结果。 0行。 但是,如果我这样做: WHERE p.NAME LIKE '%JOSÉ%'; 我只得到一行结果,所以只有1行。 这是唯一强调“ jose”并大写的行。 我尝试过使用josÉ或JoSÉ或我所做的任何组合,只要带重音的字母保持为大写或不大写,因为它实际上存储在数据库中,并且仍然返回唯一的行。 如果我突然用JOSE中的大写字母将“É”更改为“é”,它将不返回任何行。 因此得出结论: 如果游戏中没有拉丁字符,则不区分大小写。 如果出现拉丁字符,则区分大小写。 口音敏感,就像我搜索JOSE或jose一样,我只得到83行,而不是我需要的84行。
  • How to MySQL work “case insensitive” and “accent insensitive” in UTF-8
    I have a schema in "utf8 -- UTF-8 Unicode" as charset and a collation of "utf8_spanish_ci". All the inside tables are InnoDB with same charset and collation as mentioned. Here comes the problem: with a query like SELECT * FROM people p WHERE p.NAME LIKE '%jose%'; I get 83 result rows. I should have 84 results, because I know it. Changing where for: WHERE p.NAME LIKE '%JOSE%'; 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é%'; I get no results. 0 rows. But
  • MySQL中大小写问题
    MySQL中大小写问题 1.MySQL大小写敏感规则MySQL中,一个库会对应一个文件夹,库里的表会则以文件的方式存放在文件夹内,所以,操作系统对大小写的敏感性决定了数据库和表的大小写敏感(MySQL有一个只读的系统变量lower_case_file_system,其值反映的正是当前文件系统是否区分大小写)因此:在Windows下Mysql的数据库和表名是大小写不敏感的,而在大多数类型的Unix系统中是大小写敏感的。 以下是MySQL详细的大小写区分规则:在Linux下: 1、数据库名与表名是严格区分大小写的; 2、表的别名是严格区分大小写的; 3、列名与列的别名在所有的情况下均是忽略大小写的; 4、变量名也是严格区分大小写的; 在Windows下: 全部不区分大小写 补充:1.MySQL中有一个系统变量:lower_case_table_names,专门用来配置是否区分据库名与表名的大小写。 如果你的系统是Windows,而你又希望mysql能够区分大小写,那么你可以在my.ini文件中,在[mysqld]一节的最后,加入如下部分:[plain] view plain copy #If set to 0, table names are stored as specified and comparisons are case sensitive. #If set to 1
  • 如何在整个数据库中更改CHARACTER SET(和COLLATION)?(How to change the CHARACTER SET (and COLLATION) throughout a database?)
    问题 我们以前的程序员在表(Mysql)中设置了错误的排序规则。 他使用拉丁文归类(应为UTF8)进行设置,现在我遇到了问题。 每个带有中文和日文字符的记录都变成??? 特点。 是否可以更改排序规则并找回角色的详细信息? 回答1 更改数据库排序规则: ALTER DATABASE <database_name> CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; 更改表排序规则: ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; 更改列排序规则: ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; utf8mb4_0900_ai_ci的各个部分是utf8mb4_0900_ai_ci意思? 3 bytes -- utf8 4 bytes -- utf8mb4 (new) v4.0 -- _unicode_ v5.20 -- _unicode_520_ v9.0 -- _0900_ (new) _bin -- just compare the bits
  • MySQL变音符号不敏感搜索(西班牙口音)(MySQL diacritic insensitive search (spanish accents))
    问题 我有一个MySQL数据库,其单词包含西班牙语(áéíóú)的重音。 我想知道是否可以进行变音符号不敏感搜索。 例如,如果我搜索“ lapiz”(不带重音),我想从数据库中获取包含“lápiz”一词的结果。 我当前执行查询的方式如下: $result = mysql_query("SELECT * FROM $lookuptable WHERE disabled = '0' AND name LIKE '%$q%' OR productCode LIKE '%$q%' LIMIT $sugglimit"); 这是一家在线商店,所以我不知道人们会搜索什么……“ lapiz”只是一个例子。 替代文字http://www.freeimagehosting.net/uploads/0e7c2ae7d5.png 谢谢! 回答1 字符集和排序规则,不是我的最爱,但它们确实起作用: mysql> SET NAMES latin1; mysql> SELECT 'lápiz' LIKE 'lapiz'; +-----------------------+ | 'lápiz' LIKE 'lapiz' | +-----------------------+ | 0 | +-----------------------+ 1 row in set (0.01 sec) mysql> SET
  • 'COLLATE SQL_Latin1_General_CP1_CI_AS'是做什么的?(What does 'COLLATE SQL_Latin1_General_CP1_CI_AS' do?)
    问题 我有一个SQL查询可以在SQLServer中创建数据库,如下所示: create database yourdb on ( name = 'yourdb_dat', filename = 'c:\program files\microsoft sql server\mssql.1\mssql\data\yourdbdat.mdf', size = 25mb, maxsize = 1500mb, filegrowth = 10mb ) log on ( name = 'yourdb_log', filename = 'c:\program files\microsoft sql server\mssql.1\mssql\data\yourdblog.ldf', size = 7mb, maxsize = 375mb, filegrowth = 10mb ) COLLATE SQL_Latin1_General_CP1_CI_AS; go 运行正常。 尽管其余的SQL显然很明确,但我对COLLATE SQL_Latin1_General_CP1_CI_AS的功能感到非常困惑。 谁能向我解释一下? 另外,我想知道以这种方式创建数据库是否是最佳做法? 回答1 它设置数据库服务器的排序方式(比较文本)。 在这种情况下: SQL_Latin1_General_CP1_CI_AS
  • 在MySQL中搜索“全字匹配”(Search for “whole word match” in MySQL)
    问题 我想编写一个SQL查询,该查询在文本字段中搜索关键字,但是仅当它是“全字匹配”时(例如,当我搜索“ rid”时,它不应该与“ arid”匹配,但应该)匹配“摆脱”。 我正在使用MySQL。 幸运的是,在该应用程序中性能并不是至关重要的,并且数据库大小和字符串大小都非常小,但是我更喜欢在SQL中而不是在PHP中进行驱动。 回答1 您可以使用REGEXP以及[[:<:]] ::: [[:<:]]和[[:>:]] ::: [[:>:]]字边界标记: SELECT * FROM table WHERE keywords REGEXP '[[:<:]]rid[[:>:]]' 2020年更新:(实际上是2018年以上) MySQL在8.0.4版中更新了RegExp-Engine,因此您现在需要使用“标准”字边界标记\ b: SELECT * FROM table WHERE keywords REGEXP '\\brid\\b' 另请注意,您需要通过放置第二个反斜杠来逃避反斜杠。 回答2 找到一个防止经典单词边界[[::<::]]与特殊字符冲突的答案,例如。@#$%^&* 代替.. SELECT * FROM table WHERE keywords REGEXP '[[:<:]]rid[[:>:]]' 有了这个.. SELECT * FROM table WHERE keywords
  • MYSQL case sensitive search for utf8_bin field
    I created a table and set the collation to utf8 in order to be able to add a unique index to a field. Now I need to do case insensitive searches, but when I performed some queries with the collate keyword and I got: mysql> select * from page where pageTitle="Something" Collate utf8_general_ci; ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1' mysql> select * from page where pageTitle="Something" Collate latin1_general_ci; ERROR 1267 (HY000): Illegal mix of collations (utf8_bin,IMPLICIT) and (latin1_general_ci,EXPLICIT) for operation '=' I am pretty new to
  • SQL字符串比较,大于和小于运算符(SQL string comparison, greater than and less than operators)
    问题 今天,我查看了一些查询示例,并在WHERE条件下找到了一些字符串比较。 比较是使用大于(>)和小于(<)符号进行的,这是在SQL中比较字符串的一种可行方法吗? 以及它如何运作? 以字典顺序排在前面的字符串比另一个少吗? 例如,球小于水? 这个比较是否区分大小写? 例如BALL <water,大写字符会影响这些比较吗? 我已经搜索了几个小时,但找不到任何能使我摆脱这些疑虑的东西。 回答1 比较运算符(包括<和> )“使用”字符串值和数字。 对于MySQL 默认情况下,字符串比较不区分大小写,并使用当前字符集。 缺省值为latin1 (西欧cp1252),它也适用于英语。 当要比较的字符串的字符集排序规则区分大小写时,即字符集的名称以_cs而不是_ci结尾, _cs字符串比较将区分大小写。 重复这里的MySQL参考手册中提供的所有信息确实没有意义。 MySQL比较运算符参考: http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html 有关MySQL字符集/排序规则的更多信息: http://dev.mysql.com/doc/refman/5.5/zh-CN/charset.html 要回答您提出的特定问题: 问:这是在SQL中比较字符串的一种可行方法吗? 答:是的,在MySQL和SQL Server中 问
  • Mysql怎样运行的学习笔记之01字符集与比较规则
    01字符集与比较规则 1、编码 在计算机底层,比如'liu'在计算机并不是文字的形式,而是一串二进制数字,例如: 'a' -> 00000001 (⼗六进制:0x01) 'b' -> 00000010 (⼗六进制:0x02) 'A' -> 00000011 (⼗六进制:0x03) 'B' -> 00000100 (⼗六进制:0x04) 人类---计算机之间的交互(互相不认识) 从我们可以看到的文字到 0、1 的映射称为编码,反过来从 0、1 到文字叫解码。这个就是编码的含义。 2、字符 字符(Character)是各种文字和符号的总称,包括各国家文字、标点符号、图形符号、数字等。 3、字符集 字符的集合,叫做字符集。ASCII、UTF-8、UTF-16、UTF-32就是字符集。 (1)ASCII 因为计算机只能处理数字,如果要处理文本,就必须先把文本转换为数字才能处理。最早的计算机在设计时采用 8 个比特(bit)作为一个字节(byte),所以,一个字节能表示的最大的整数就是 255(二进制11111111=十进制 255),0 - 255 被用来表示大小写英文字母、数字和一些符号,这个编码表被称为 ASCII 编码,比如大写字母 A 的编码是 65,小写字母 z 的编码是 122。 (2)GB2312 收录了汉字以及拉丁字⺟、希腊字⺟、⽇⽂平假名及⽚假名字⺟、俄语⻄⾥尔字⺟
  • 如何在MySql中进行口音敏感搜索(How to conduct an Accent Sensitive search in MySql)
    问题 我有一个带有utf8常规ci排序规则的MySQL表。 在表中,我可以看到两个条目: 世纪阿巴德 我正在使用如下查询: SELECT * FROM `words` WHERE `word` = 'abád' 查询结果给出两个词: 世纪阿巴德 有没有办法表明我只希望MySQL找到带重音的单词? 我希望查询仅返回 阿巴德 我也尝试过以下查询: SELECT * FROM `words` WHERE BINARY `word` = 'abád' 它没有给我任何结果。 感谢您的帮助。 回答1 如果您在该字段上的搜索始终对重音敏感,则将该字段的排序规则声明为utf8_bin(它将对utf8编码的字节进行相等性比较),或使用特定于语言的排序规则来区分重音和un -重音字符。 col_name varchar(10) collate utf8_bin 如果搜索通常对重音不敏感,但您想对此搜索例外,请尝试; WHERE col_name = 'abád' collate utf8_bin 回答2 在我的版本(MySql 5.0)中,没有任何utf8字符集归类用于不区分大小写,区分重音的搜索。 utf8唯一的重音敏感整理是utf8_bin。 但是,它也是区分大小写的。 我的解决方法是使用类似以下的方法: SELECT * FROM `words` WHERE LOWER(column) =
  • 2021-2-18:请你说说MySQL的字符集与排序规则对开发有哪些影响?
    任何计算机存储数据,都需要字符集,因为计算机存储的数据其实都是二进制编码,将一个个字符,映射到对应的二进制编码的这个映射就是字符编码(字符集)。这些字符如何排序呢?决定字符排序的规则就是排序规则。 查看内置字符集与比较规则 通过show charset;命令,可以查看所有的字符集。以下仅展示了我们常用的字符集: +----------+---------------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+---------------------------------+---------------------+--------+ | latin1 | cp1252 West European | latin1_swedish_ci | 1 | | ascii | US ASCII | ascii_general_ci | 1 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | | cp1250 | Windows Central European | cp1250_general_ci | 1 | | gbk
  • MySQL全文索引-fulltext
    MySQL 5.6 之前的版本只有MyISAM存储引擎支持全文索引,5.6 及之后的版本,InnoDB 才开始支持。全文索引相对于like %这种模糊查询效率更高,查询方式更多样,查询结果也更准确。在全文检索领域,全文索引功能非常有用,比如博客类网站,可以对文章标题,文章内容创建全文索引,用户可以根据关键词搜索想要的文章。 1、创建全文索引 能够创建全文索引的字段类型必须是char,varchar,text等这类字符文本类型。 创建一个带有全文索引的表: CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) ) ENGINE=InnoDB; 通过ALTER TABLE创建、删除全文索引: 创建全文索引 ALTER TABLE articles ADD FULLTEXT KEY ft_title(title); 删除索引 ALTER TABLE articles DROP INDEX ft_title; 2、查询全文索引 MySQL使用MATCH AGAINST语法来查询全文索引,支持自然语言和布尔类型两种查询模式。 自然语言模式查询: 示例: mysql> SELECT *
  • MySQL REGEXP query - accent insensitive search
    I'm looking to query a database of wine names, many of which contain accents (but not in a uniform way, and so similar wines may be entered with or without accents) The basic query looks like this: SELECT * FROM `table` WHERE `wine_name` REGEXP '[[:<:]]Faugères[[:>:]]' which will return entries with 'Faugères' in the title, but not 'Faugeres' SELECT * FROM `table` WHERE `wine_name` REGEXP '[[:<:]]Faugeres[[:>:]]' does the opposite. I had thought something like: SELECT * FROM `table` WHERE `wine_name` REGEXP '[[:<:]]Faug[eèêéë]r[eèêéë]s[[:>:]]' might do the trick, but this only returns the
  • Accent insensitive search query in MySQL
    Is there any way to make search query accent insensitive? the column's and table's collation are utf8_polish_ci and I don't want to change them. example word : toruń select * from pages where title like '%torun%' It doesn't find "toruń". How can I do that?
  • MySQL中区分大小写的排序规则(Case Sensitive collation in MySQL)
    问题 MySQL中是否有任何支持区分大小写的排序规则类型。 我在MySQL中使用了所有类型的排序规则,它们的名称末尾都带有_ci,因此它们是不区分大小写的排序规则。 回答1 根据MySQL手册http://dev.mysql.com/doc/refman/5.0/en/charset-mysql.html,您应该能够将排序_cs设置为_cs以便区分大小写。 您可以通过执行SHOW COLLATION WHERE COLLATION LIKE "%_cs"查询来获得_cs归类列表 经过一点研究: 显然,MySQL中还没有utf8_*_cs (尚未)。 如果需要utf8字段区分大小写的排序规则,则应使用utf8_bin 。 这会弄乱ORDER BY ,但是可以通过ORDER BY column COLLATE utf8_general_ci 来源:http://forums.mysql.com/read.php?103,19380,200971#msg-200971和http://forums.mysql.com/read.php?103,156527,198794#msg-198794 回答2 尝试以_bin结尾的排序_bin ,例如latin1_bin或utf8_bin ,这取决于您的字符集。 回答3 MySQL的新版本(8.0.1和更高版本)(最终)带有一组utf8mb4_*
  • MySQL字符集和校对规则(Collation)
    MySQL字符集和校对规则(Collation) 阅读目录:MySQL的字符集和校对规则MySQL的字符集 MySQL与字符集正确使用字符集MySQL客户端与字符集字符集编码转换原理字符集常见处理操作字符集的正确实践MySQL的校对规则一、字符集(Character set)  是多个字符(英文字符,汉字字符,或者其他国家语言字符)的集合,字符集种类较多,每个字符集包含的字符个数不同。特点:  ①字符编码方式是用一个或多个字节表示字符集中的一个字符  ②每种字符集都有自己特有的编码方式,因此同一个字符,在不同字符集的编码方式下,会产生不同的二进制常见字符集:  ASCII字符集:基于罗马字母表的一套字符集,它采用1个字节的低7位表示字符,高位始终为0。  LATIN1字符集:相对于ASCII字符集做了扩展,仍然使用一个字节表示字符,但启用了高位,扩展了字符集的表示范围。  GBK字符集:支持中文,字符有一字节编码和两字节编码方式。  UTF8字符集:Unicode字符集的一种,是计算机科学领域里的一项业界标准,支持了所有国家的文字字符,utf8采用1-4个字节表示字符。1、MySQL与字符集  只要涉及到文字的地方,就会存在字符集和编码方式。MySQL系统变量值: 2、正确使用字符集  数据库服务端的字符集具体要看存储什么字符以上这些参数如何起作用:1.库、表、列字符集的由来
  • MySQL字符集和校对规则(Collation)
    MySQL字符集和校对规则(Collation) 阅读目录:MySQL的字符集和校对规则 MySQL的字符集 MySQL与字符集 正确使用字符集 MySQL客户端与字符集 字符集编码转换原理 字符集常见处理操作 字符集的正确实践 MySQL的校对规则 一、字符集(Character set)   是多个字符(英文字符,汉字字符,或者其他国家语言字符)的集合,字符集种类较多,每个字符集包含的字符个数不同。 特点:   ①字符编码方式是用一个或多个字节表示字符集中的一个字符   ②每种字符集都有自己特有的编码方式,因此同一个字符,在不同字符集的编码方式下,会产生不同的二进制 常见字符集:   ASCII字符集:基于罗马字母表的一套字符集,它采用1个字节的低7位表示字符,高位始终为0。   LATIN1字符集:相对于ASCII字符集做了扩展,仍然使用一个字节表示字符,但启用了高位,扩展了字符集的表示范围。   GBK字符集:支持中文,字符有一字节编码和两字节编码方式。   UTF8字符集:Unicode字符集的一种,是计算机科学领域里的一项业界标准,支持了所有国家的文字字符,utf8采用1-4个字节表示字符。 1、MySQL与字符集   只要涉及到文字的地方,就会存在字符集和编码方式。MySQL系统变量值: 2、正确使用字符集   数据库服务端的字符集具体要看存储什么字¬
  • Case-insensitive REPLACE in MySQL?
    MySQL runs pretty much all string comparisons under the default collation... except the REPLACE command. I have a case-insensitive collation and need to run a case-insensitive REPLACE. Is there any way to force REPLACE to use the current collation rather than always doing case-sensitive comparisons? I'm willing to upgrade my MySQL (currently running 5.1) to get added functionality... mysql> charset utf8 collation utf8_unicode_ci; Charset changed mysql> select 'abc' like '%B%'; +------------------+ | 'abc' like '%B%' | +------------------+ | 1 | +------------------+ mysql> select replace(
  • 最强MySQL笔记(超详细版)(一)
    第一章 数据库概述 1.1 数据库概述 DBMS:数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件,例如建立、使用和维护数据库。 DB:数据库(Database) SQL:结构化查询语言,(Structure Query Language),专门用来操作/访问数据库的通用语言。 如果把数据库比作盘子,那数据就是盘子里的菜,SQL则是你的筷子。 数据库类型:数据库经过几十年的发展,出现了多种类型。目前最常见的数据库模型主要是:关系型数据库和(noSQL)非关系型数据库。 MySQL:其中的一款关系型数据库管理系统 关系型数据库模型是将复杂的数据结构用较为简单的二元关系(二维表)来表示,如图所示。在该类型数据库中,对数据的操作基本上都建立在一个或多个表格上,我们可以采用结构化查询语言(SQL)对数据库进行操作。关系型数据库是目前主流的数据库技术,其中具有代表性的数据库管理系统有:Oracle、DB2、SQL Server、MySQL等。 非关系型数据库: 随着互联网web2.0网站的兴起,传统的关系数据库在处理web2.0网站,特别是超大规模和高并发的SNS( 社交网络服务 )类型的web2.0纯动态网站已经显得力不从心,出现了很多难以克服的问题,而非关系型的数据库则由于其本身的特点得到了非常迅速的发展