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)
        
      

                         
<br data-filtered=
"filtered"
>

本文转自hcymysql51CTO博客，原文链接：http://blog.51cto.com/hcymysql/1742836
 ，如需转载请自行联系原作者