标准化 MySQL 查询中的重音字符

2021-12-28 00:00:00 sql utf-8 mysql diacritics collate

我希望能够进行标准化重音字符的查询，例如:

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 中的collat​​e"子句来做到这一点.我收到以下错误:

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 带我来这里回答一个相关问题，我认为它仍然值得回答:)

相关文章