MySQL大小写问题

| Database | Create Database                                                                 |

+----------+---------------------------------------------------------------------------------+

| testx    | CREATE DATABASE `testx` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */ |

+----------+---------------------------------------------------------------------------------+

1 row in set (0.00 sec)

case2是默认创建的表

mysql> show create table case2;

+-------+------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                           |

+-------+------------------------------------------------------------------------------------------------------------------------+

| case2 | CREATE TABLE `case2` (

`a` int(11) DEFAULT NULL,

`B` varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------+------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

case4是指定字符集和collate的表

mysql> create table case4(a int(11),B varchar(10)) default character set utf8 collate utf8_bin;;

Query OK, 0 rows affected (0.12 sec)

mysql> show create table case4;

+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                             |

+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+

| case4 | CREATE TABLE `case4` (

`a` int(11) DEFAULT NULL,

`B` varchar(10) COLLATE utf8_bin DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

我们可以在创建数据库或表时指定是否大小写敏感，如果没有指定的话，通过语句级的collate和binary也可以实现

mysql> select * from case1;

+------+------+

| a    | B    |

+------+------+

|    1 | AAA  |

|    2 | bbb  |

|    4 | AAA  |

|    3 | BBB  |

+------+------+

4 rows in set (0.00 sec)

mysql> select * from case1 where B like '%b%';

+------+------+

| a    | B    |

+------+------+

|    2 | bbb  |

|    3 | BBB  |

+------+------+

2 rows in set (0.00 sec)

指定Collation

mysql> select * from case1 where B like '%b%' collate utf8_bin;

+------+------+

| a    | B    |

+------+------+

|    2 | bbb  |

+------+------+

1 row in set (0.01 sec)

通过binary把字符串转化为二进制比较，由于大小写字符的二进制肯定不同，因此也是区分大小的一种方式

mysql> select * from case1 where binary B like '%b%';

+------+------+

| a    | B    |

+------+------+

|    2 | bbb  |

+------+------+

1 row in set (0.00 sec)

mysql>

最后要说明一点的是Collation与索引存储的关系。因为Collation是用于字符串之间比较，而索引是基于比较有序排列的，

因此Collation会影响记录的索引顺序

参考：

http://dev.mysql.com/doc/refman/5.0/en/charset-collation-names.html

http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

（编辑：核心网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!