mysql的collation区分大小写设立

                                        mysql的collation区分大小写设置

      mysql数据库在做查询时候，有时候是英文字母大小写敏感的，有时候又不是的，主要是由于mysql的字符校验规则的设置。通常默认是不支持的大小写字母敏感的，在主动设置mysql数据库的collation后，可以使得数据库满足大小写敏感，适合客户的一定要求。通过下面的试验进行理解学习…..

mysql> select version(); +-----------+ | version() | +-----------+ | 5.5.25    | +-----------+ 1 row in set (0.00 sec)

欢迎大家阅读《mysql的collation区分大小写设立》,跪求各位点评，by 搞代码

mysql> show variables like '%character%'; +--------------------------+------------------------------------------------------- | Variable_name            | Value +--------------------------+------------------------------------------------------- | character_set_client     | utf8 | character_set_connection | utf8 | character_set_database   | latin1 | character_set_filesystem | binary | character_set_results    | utf8 | character_set_server     | latin1 | character_set_system     | utf8 | character_sets_dir       | D:/database/mysql/mysql-5.5.25-winx64/share/charsets/

mysql> show variables like '%collation%'; +----------------------+-------------------+ | Variable_name        | Value             | +----------------------+-------------------+ | collation_connection | utf8_general_ci   | | collation_database   | latin1_swedish_ci | | collation_server     | latin1_swedish_ci | +----------------------+-------------------+ 3 rows in set (0.00 sec)

    默认即为：collation_connection = utf8_general_ci   大小写不敏感校验规则；

mysql> show collation like '%utf8%'; +--------------------------+---------+-----+---------+----------+---------+ | Collation                | Charset | Id  | Default | Compiled | Sortlen | +--------------------------+---------+-----+---------+----------+---------+ | utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 | | utf8_bin                 | utf8    |  83 |         | Yes      |       1 | | utf8_unicode_ci          | utf8    | 192 |         | Yes      |       8 | | utf8_icelandic_ci        | utf8    | 193 |         | Yes      |       8 |

    客户端字符集：utf8, 校验规则: utf8_general_ci, 默认为yes，即不是大小写敏感的匹配；

    而utf8_bin是区分大小写的校验规则；

    创建表做测试，看数据效果：

mysql> create table T_collation(first varchar(30) character set utf8     -> collate utf8_bin,second varchar(30) character set utf8 collate     -> utf8_general_ci); Query OK, 0 rows affected (0.32 sec)  mysql> show create table t_collation/G; *************************** 1. row ***************************        Table: t_collation Create Table: CREATE TABLE `t_collation` (   `first` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,   `second` varchar(30) CHARACTER SET utf8 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)  ERROR: No query specified  mysql> insert into t_collation values('M','M'),('N','N'),('a','a'),('b','b'); Query OK, 4 rows affected (0.13 sec) Records: 4  Duplicates: 0  Warnings: 0  mysql> select * from t_collation; +-------+--------+ | first | second | +-------+--------+ | M     | M      | | N     | N      | | a     | a      | | b     | b      | +-------+--------+ 4 rows in set (0.00 sec)

    比较查询结果：

mysql> insert into t_collation values('m','m'),('n','n'); Query OK, 2 rows affected (0.10 sec) Records: 2  Duplicates: 0  Warnings: 0  mysql> select * from t_collation; +-------+--------+ | first | second | +-------+--------+ | M     | M      | | N     | N      | | a     | a      | | b     | b      | | m     | m      | | n     | n      | +-------+--------+ 6 rows in set (0.00 sec)  mysql> select * from t_collation where first='m'; +-------+--------+ | first | second | +-------+--------+ | m     | m      | +-------+--------+ 1 row in set (0.02 sec)  mysql> select * from t_collation where second='m'; +-------+--------+ | first | second | +-------+--------+ | M     | M      | | m     | m      | +-------+--------+ 2 rows in set (0.00 sec) mysql> select * from t_collation where second='M'; +-------+--------+ | first | second | +-------+--------+ | M     | M      | | m     | m      | +-------+--------+ 2 rows in set (0.00 sec)  mysql> select * from t_collation where first='M'; +-------+--------+ | first | second | +-------+--------+ | M     | M      | +-------+--------+ 1 row in set (0.00 sec)

    比较各自的校验规则，utf8_bin是区分大小写的，而utf8_general_ci是不区分的，默认的。

    还可以从排序语句中进行比较，看看测试效果的…..

mysql> select * from t_collation; +-------+--------+ | first | second | +-------+--------+ | M     | M      | | N     | N      | | a     | a      | | b     | b      | | m     | m      | | n     | n      | +-------+--------+ 6 rows in set (0.00 sec)  mysql> select * from t_collation order by first; +-------+--------+ | first | second | +-------+--------+ | M     | M      | | N     | N      | | a     | a      | | b     | b      | | m     | m      | | n     | n      | +-------+--------+ 6 rows in set (0.00 sec)  mysql> select * from t_collation order by second; +-------+--------+ | first | second | +-------+--------+ | a     | a      | | b     | b      | | M     | M      | | m     | m      | | N     | N      | | n     | n      | +-------+--------+ 6 rows in set (0.00 sec)

   同样符合校验规则的检查。

  结论： 在MYSQL数据库中，根据实际业务需要，适当可以调整字符集的(collation)校验规则，修改默认的大小写敏感问题，满足实际需要，这本身就是数据库的一种设置，熟悉标准、规则，适当利用为项目所用，可以针对具体的数据库或者表或者表的列进行设置。