[MySQL] Case insensitive search without additional rules?

Question

Hello,

I'm trying to find a collation in MySQL (my version is 5.0) where strings that differ in case are considered the same but there're no other rules like:

á = a

and so on.

I tried to find the proper collation here: http://www.collation-charts.org/mysql60/by-charset.html but it seems that the collation I'm looking for doesn't exist.

I can't use in SQL query: SELECT ... WHERE lower(column1) = lower(column2) because indices on columns column1 and column2 are not used then and my query is terrible slow.

Thanks for any suggestion!

Answer 1

Which type of collation set in the tables that in question? I'm currently using a lot of tables with utf8_hungarian_ci because of this one is case-insensitive.

Answer 2

http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html indicates that nonbinary strings are case insensitive by default. Have you tested to see that it is not working properly without using lower()?

Answer 3

Why don't you use the full text search functions of MySQL for your search query?

For tasks like yours I am using the MATCH AGAINST function.

Read the Specifications at mysql.com to make it clear - Link

One example:

SELECT * FROM customer WHERE status = 1 AND MATCH (person, city, company, zipcode, tags) AGAINST ('".$searchstring."' IN BOOLEAN MODE)

And this will be executed case insensitive.

Answer 4

I was given an advice: simply have table like this: id, word, word_in_lowercase.. it's true that data are redundant but otherwise it fulfils all my needs.

Automatic update of word_in_lowercase may be done via trigger or some additional programming.