With MySQL 5.6, given the following table structure:
mysql> describe groups;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(500) | YES | | NULL | |
| description | varchar(200) | YES | | NULL | |
| created_at | datetime | YES | | NULL | |
| updated_at | datetime | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
And the following data in the
`groups` table:
mysql> select id, name from groups;
+----+------------------+
| id | name |
+----+------------------+
| 1 | some-users |
| 2 | SOME-ADMINS |
| 3 | customers-group1 |
| 4 | customers-group2 |
| 5 | customers-group3 |
+----+------------------+
5 rows in set (0.00 sec)
The following query is badly sorted in MySQL (whereas it works correctly in at least PostgreSQL, Oracle, and MSSQL):
mysql> select id, name from groups order by upper(name);
+----+------------------+
| id | name |
+----+------------------+
| 3 | customers-group1 |
| 4 | customers-group2 |
| 5 | customers-group3 |
| 1 | some-users |
| 2 | SOME-ADMINS |
+----+------------------+
5 rows in set (0.00 sec)
I would expect :
SOME-ADMINS to appear before some-users, as is the case with other DB vendors.
Is this a bug of MySQL?
According to the documentation, it is case insensitive but you can make it case sensitive using BINARY :
On character type columns, sorting—like all other comparison operations—is normally performed in a case-insensitive fashion. This means that the order is undefined for columns that are identical except for their case. You can force a case-sensitive sort for a column by using BINARY like so: ORDER BY BINARY col_name.