MySQL字符集和归类：不区分变音不起作用 [英] MySQL charsets and collations: accent insensitive doesn't work

问题描述

我知道答案很简单，但我要去香蕉。我想我已经尝试过每个解决方案。这里我们去...

我有一个数据库，字符集 latin1 。是的，我应该在 utf8 ，但我有几个正在运行的项目，所以我不想打扰他们。

问题出在 SELECT 与 LIKE％...％

表是utf8和COLLATE utf8_general_ci。这些字段也是utf8与utf8_general_ci排序规则。我的脚本文件（php）是utf-8编码，服务器还提供utf-8中的文件。所以，一切都是utf-8。

好吧，因为一切都与utf8_general_ci整理，我应该能够搜索不区分大小写和重音的内容。例如：

在表中提供者 ...

  id providerName 
 1 Jose 
 2José
  

我应该能够...

  SELECT * FROM providers WHERE providerName LIKE％jose％ 
  

或

  SELECT * FROM providers WHERE providerName LIKE％josé％
  

case，两行返回。但是，用第一个查询，我只得到行1;和第二个查询，我只得到第二行。不区分大小写的搜索似乎工作得很好，但不重音不敏感。

所以我试图在LIKE ％...％后面添加 COLLATE utf8_general_ci 。结果相同。

然后，我发现连接是在latin1（víaPHP函数 mysql_client_encoding（））。所以我每次建立连接时添加一个查询，指示使用utf8。我使用 SET NAMES UTF8 COLLATE utf8_general_ci AND php的 mysql_set_charset（）。当我添加这个配置，第一个查询返回第1行，但第二个查询不返回任何结果。此外，所有结果返回罕见的字符（你知道，像ð，即使所有设置为utf8）。

这是溺爱我。一切都设置为UTF8，但它不工作（我）期望。

  MySQL服务器5.0.95 
 PHP 5.2.14 
 Win7 
  
 
 
解决方案
停止机器！
 
 
 
我发现我做的一切OK，它的DID响应如预期。唯一的问题是，即使表，字段，文件和服务器在utf8，当填充表（过去的一段时间），连接是用latin1。 
 
 
 
所以我重新填充表，现在用utf8连接，它工作很好。 
 
 
 
谢谢你们！
 
I know that the answer is very simple, but I'm going bananas. I think I've tried every solution available. Here we go...

I have a database with charset latin1. Yeah, i should have it in utf8, but I have several running projects on it, so I don't want to mess them. 

The issue comes with SELECT with LIKE "%...%"

The table is utf8 with COLLATE utf8_general_ci. The fields are also utf8 with utf8_general_ci collation. My script files (php) are utf-8 encoded, and the server also serves files in utf-8. So, everything is utf-8. 

Ok, as everything is collated with utf8_general_ci, I should be able to search case insensitive and accent insentive. For example:

Having in table providers...
id     providerName
1      Jose
2      José
I should be able to do...
SELECT * FROM providers WHERE providerName LIKE "%jose%"
or
SELECT * FROM providers WHERE providerName LIKE "%josé%"
And have, in both cases, the two rows returned. But, with the first query, I only get row 1; and with second query, I only get row two. Case insensitive search seems to work well, but accent insensitive does not. 

So I tried adding COLLATE utf8_general_ci after the LIKE "%...%". Same result. 

Then, I discovered that the connection was been made in latin1 (vía PHP function mysql_client_encoding()). So I added a query everytime a connection was made, indicating to use utf8. I used both SET NAMES UTF8 COLLATE utf8_general_ci AND php's mysql_set_charset(). When I add this configuration, the first query return row 1, but the second query does not return any result. In addition, all results returns rare characters (you know, like í°, even if all was set to utf8). 

This is pluzzing me. Everything is set in UTF8, but it doesn't work as (I) expect. 
MySQL Server 5.0.95
PHP 5.2.14
Win7

解决方案
Stop the machines!!

I found out that I was doing everything OK and it DID respond as expected. The only problem was that, even if the table, fields, files and server were in utf8, when the table was populated (some time in the past), the connection was been made with latin1. 

So I re-populated the table, now with utf8 connection, and it worked just fine. 

Thank you guys!

                        
这篇关于MySQL字符集和归类：不区分变音不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！