CODEWITHSUNDEEP
mysqlsqldjangocharacter-encodingcollation
RevolutionTech
RevolutionTech

Reputation: 1404

MySQL SELECT statements are not case-sensitive

I am trying to select items in MySQL with consideration for case sensitivity, but I am having issues getting MySQL to perform any case distinction.

For example, all of the following statements

mysql> SELECT * FROM mytable WHERE name="alpha";
mysql> SELECT * FROM mytable WHERE name="ALPHA";
mysql> SELECT * FROM mytable WHERE name="aLpHa";

return the same results:

+----+-------+
| id | name  |
+----+-------+
| 1  | alpha |
+----+-------+
1 row in set (0.00 sec)

I recognize that some character sets and collations in MySQL do not distinguish between case, and so changing the character set and collation from the default is a necessary step. I have tried changing and converting the table's character set and collate to utf8 and utf8_unicode_ci respectively, but I am uncertain if it worked correctly.

mysql> SHOW TABLE STATUS;
+---------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name    | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+---------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| mytable | InnoDB |      10 | Compact    | 68886 |             83 |     5783552 |               0 |      7372800 |  17825792 |          67039 | 2014-08-02 15:05:07 | NULL        | NULL       | utf8_unicode_ci |     NULL |                |         |
+---------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.04 sec)
mysql> SHOW FULL COLUMNS FROM mytable;
+-------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type        | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| id    | int(11)     | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| name  | varchar(10) | utf8_unicode_ci | NO   |     | NULL    |                | select,insert,update,references |         |
+-------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
2 rows in set (0.01 sec)

As you can see from above, the collation of the table has now been changed to utf8_unicode_ci and the collation of the relevant column has been changed as well.

Is there any additional step I need to perform so that MySQL will make a distinction with respect to character case? I am using MySQL Server version: 5.5.37-0ubuntu0.13.10.1 (Ubuntu).

Note that I cannot simply use queries with the BINARY keyword, such as:

SELECT * FROM mytable WHERE BINARY name="alpha";

because I am actually just using MySQL through Django's ORM, which does not support this type of lookup as far as I know. Thank you in advance for your help!

Upvotes: 1

Views: 557

Answers (1)

Brian DeMilia
Brian DeMilia

Reputation: 13248

You can use option: COLLATE utf8_bin

Example: http://sqlfiddle.com/#!2/60d73/1/0 vs. http://sqlfiddle.com/#!2/60d73/2/0

You can alter your table using:

alter table mytable ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;

Upvotes: 1

Related Questions