mysql的collation区分大小写设置(二)
ecords: 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是不区分的，默认的。
还可以从排序语句中进行比较，看看测试效果的.....
Sql代码
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)校验规则，修改默认的大小写敏感问题，满足实际需要，这本身就是数据库的一种设置，熟悉标准、规则，适当利用为项目所用，可以针对具体的数据库或者表或者表的列进行设置。