表记录:
(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)