mysql search select case insensitive

From my development experience, I always think that mysql SELECT query with LIKE is not case sensitive, but anyhow I did experience such a situation that mysql is case sensitive. After few hours research on this area finally I got the clue to solve.

Some people suggest that, when you insert to database, you can lower / upper the character so that it will not have case sensitive issue, but sometimes you just change the coding of people framework. So how to solve it?

The clue is from the table structure. When you create a field, there is an option for the Encoding and Collation. If you need something case sensitive make sure you set it as utf8_bin. On the other hand, if you want something NOT case sensitive the set it as utf_general_ci. the CI at the base stand for CASE INSENSITIVE.

Below is the screen shot for my sql client.

Screen Shot 2015-04-15 at 5.23.01 PM

*Tips: If you have problem to click select fromt he Collation, please make sure the Binary column is not checked.

 

Thank you for reading, I hope this trick can help you solve your SELECT statement.

Leave a Reply

Your email address will not be published. Required fields are marked *