select 忽略查询字段值大小写【小技巧】

表记录：

(root@localhost) [test]> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | A    |
|  3 | A    |
|  4 | B    |
|  5 | b    |
|  6 | c    |
|  7 | d    |
|  8 | e    |
+----+------+
8 rows in set (0.00 sec)

现在查询name=a记录，并忽略大小写

(root@localhost) [test]> select name collate utf8_general_ci from t where name like 'a%';
+------------------------------+
| name collate utf8_general_ci |
+------------------------------+
| a                            |
| A                            |
| A                            |
+------------------------------+
3 rows in set (0.00 sec)

统计name字段相同记录有多少条

(root@localhost) [test]> select name,count(*) from t group by name collate utf8_general_ci DESC;
+------+----------+
| name | count(*) |
+------+----------+
| e    |        1 |
| d    |        1 |
| c    |        1 |
| B    |        2 |
| a    |        3 |
+------+----------+
5 rows in set (0.00 sec)