MySQL charsets and collations: accent insensitive

Question

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

Answer 1

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!

Answer 2

I don't have the setup to test this properly, but here's a possible solution. So many places to set UTF8! :)