标准化MySQL查询中的重音字符 [英] normalizing accented characters in MySQL queries

问题描述

我希望能够进行对重音字符进行规范化的查询，例如:

I'd like to be able to do queries that normalize accented characters, so that for example:

é, è, and ê

在使用'='和'like'的查询中，

都被视为'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)

作为替代，use可以使用字符集介绍程序"显式设置字符集:

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查询中的重音字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！