问题描述
我希望能够进行标准化重音字符的查询，例如:
I'd like to be able to do queries that normalize accented characters, so that for example:
é, è, and ê
在使用="和喜欢"的查询中都被视为e".我有一行用户名字段设置为rené"，我希望能够同时匹配rene"和rené"'.
are all treated as 'e', in queries using '=' and 'like'. I have a row with username field set to 'rené', and I'd like to be able to match on it with both 'rene' and 'rené'.
我正在尝试使用 MySQL 5.0.8 中的collate"子句来做到这一点.我收到以下错误:
I'm attempting to do this with the 'collate' clause in MySQL 5.0.8. I get the following error:
mysql> select * from User where username = 'rené' collate utf8_general_ci;
ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'
FWIW，我的表是用以下方法创建的:
FWIW, my table was created with:
CREATE TABLE `User` (
`id` bigint(19) NOT NULL auto_increment,
`username` varchar(32) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniqueUsername` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=56790 DEFAULT CHARSET=utf8
推荐答案
错误的原因不是表格，而是您输入的字符集，即查询中的rené".行为取决于 character_set_connection 变量:
The reason for the error is not the table but the characterset of your input, i.e. the 'rené' in your query. The behaviour depends on the character_set_connection variable:
用于没有字符集介绍器的文字和用于数字到字符串转换的字符集.
The character set used for literals that do not have a character set introducer and for number-to-string conversion.
使用 MySQL 客户端，使用 SET NAMES 更改它:
Using the MySQL Client, change it using SET NAMES:
一个 SET NAMES 'charset_name' 语句相当于这三个语句:
A SET NAMES 'charset_name' statement is equivalent to these three statements:
SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;
(来自 http://dev.mysql.com/doc/refman/5.5/en/charset-connection.html)
示例输出:
mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from User where username = 'rené' collate utf8_general_ci;
ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from User where username = 'rené' collate utf8_general_ci;
Empty set (0.00 sec)
或者，使用可以使用字符集介绍器"显式设置字符集:
Altenatively, use can explicitly set the character set using a 'character set introducer':
mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from User where username = _utf8'rené' collate utf8_general_ci;
Empty set (0.00 sec)
我知道这个问题已经很老了，但由于 Google 带我来这里回答一个相关问题，我认为它仍然值得回答:)
这篇关于标准化 MySQL 查询中的重音字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！