关于mysql的大小写排序及比较

chylli

刚来这里不久，看到有几个帖子问这样两个问题:

• mysql的表排序时不考虑大小写怎么解决
• select a from a where a="hello" 为啥结果集中包含"HELLO"

对于此类问题，建议仔细看看手册中的character set and collations 的相关章节.在此不再重复。下面举一个示例：

mysql> show create table a \G
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `a` char(16) collate latin1_general_ci default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
1 row in set (0.00 sec)
这里我们指定了a表的collate,ci的意思是 insensitive,忽略大小写

mysql> show create table b \G
*************************** 1. row ***************************
       Table: b
Create Table: CREATE TABLE `b` (
  `a` char(16) collate latin1_general_cs default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs
1 row in set (0.00 sec)
b表是latin1_general_cs, case sensitive, 不忽略大小写

mysql> show full columns from a\G
*************************** 1. row ***************************
     Field: a
      Type: char(16)
Collation: latin1_general_ci
      Null: YES
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment:
1 row in set (0.01 sec)
从这里可以看到用到的collation

mysql> show full columns from b\G
*************************** 1. row ***************************
     Field: a
      Type: char(16)
Collation: latin1_general_cs
      Null: YES
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment:
1 row in set (0.00 sec)

mysql> select * from a\G
*************************** 1. row ***************************
a: hello
*************************** 2. row ***************************
a: How are you
*************************** 3. row ***************************
a: HELLO
3 rows in set (0.00 sec)

mysql> select * from b\G
*************************** 1. row ***************************
a: hello
*************************** 2. row ***************************
a: How are you
*************************** 3. row ***************************
a: HELLO
3 rows in set (0.00 sec)
这里有三条数据


mysql> select * from a order by a\G
*************************** 1. row ***************************
a: hello
*************************** 2. row ***************************
a: HELLO
*************************** 3. row ***************************
a: How are you
3 rows in set (0.00 sec)
用a排序，由于a表的collation用的是ci,所以忽略大小写

mysql> select * from b order by a\G
*************************** 1. row ***************************
a: HELLO
*************************** 2. row ***************************
a: How are you
*************************** 3. row ***************************
a: hello
3 rows in set (0.00 sec)
按a排序，由于b表用的是cs,所以排序时注意大小写

mysql> select * from a order by a collate latin1_general_ci\G
*************************** 1. row ***************************
a: hello
*************************** 2. row ***************************
a: HELLO
*************************** 3. row ***************************
a: How are you
3 rows in set (0.00 sec)
我们可以在排序时指定用哪种collation

mysql> select * from b order by a collate latin1_general_ci\G
*************************** 1. row ***************************
a: hello
*************************** 2. row ***************************
a: HELLO
*************************** 3. row ***************************
a: How are you
3 rows in set (0.00 sec)
现在两个表的显示结果一样的，这是因为在sql里指定了同样的collation

mysql> select * from a order by a collate latin1_general_cs\G
*************************** 1. row ***************************
a: HELLO
*************************** 2. row ***************************
a: How are you
*************************** 3. row ***************************
a: hello
3 rows in set (0.01 sec)

mysql> select * from b order by a collate latin1_general_cs\G
*************************** 1. row ***************************
a: HELLO
*************************** 2. row ***************************
a: How are you
*************************** 3. row ***************************
a: hello
3 rows in set (0.00 sec)

mysql> select * from a where a="hello"\G
*************************** 1. row ***************************
a: hello
*************************** 2. row ***************************
a: HELLO
2 rows in set (0.01 sec)
这里用的是ci,所以hello = HELLO
mysql> select * from b where a="hello"\G
*************************** 1. row ***************************
a: hello
1 row in set (0.00 sec)
这里用的cs,所以HELLO 不等于 hello


mysql> select * from a where a="hello" collate latin1_general_ci\G
*************************** 1. row ***************************
a: hello
*************************** 2. row ***************************
a: HELLO
2 rows in set (0.00 sec)

mysql> select * from b where a="hello" collate latin1_general_ci\G
*************************** 1. row ***************************
a: hello
*************************** 2. row ***************************
a: HELLO
2 rows in set (0.00 sec)
比较时指定了collation,所以hello=HELLO;

mysql> select * from a where a="hello" collate latin1_general_cs\G
*************************** 1. row ***************************
a: hello
1 row in set (0.00 sec)

mysql> select * from b where a="hello" collate latin1_general_cs\G
*************************** 1. row ***************************
a: hello
1 row in set (0.00 sec)
比较指定了cs,则hello 和HELLO是不相等的

tomorrow819

学习了，这个问题我也遇到过

chylli

我以为即使不加精，也得有人顶啊

linda1001666

正在学习mysql，希望看到更多的这种帖子哦，谢谢