Mysql大小写敏感

Author：Skate
Time:2015/03/11

Mysql大小写敏感

字符串大小写敏感和Mysql的数据库的名字、表名字、字段名字、还有字段值有关。

1.和数据库名字、表名字、存储过程和触发器有关
Mysql中控制数据库名和表名的大小写敏感是由参数lower_case_table_names控制，
为0时；
表示区分大小写，使用CREATE TABLE或CREATE DATABASE语句指定的大小写字母在硬盘上保存表名和数据库名。
名称比较对大小写敏感。在大小写不敏感的操作系统如windows或Mac OS x上我们不能将该参数设为0，如果在
大小写不敏感的文件系统上将--lowercase-table-names强制设为0，并且使用不同的大小写访问MyISAM表名，
可能会导致索引破坏。

为1时；
表示将名字转化为小写后存储，名称比较对大小写不敏感。MySQL将所有表名转换为小写在存储和查找表上。
该行为也适合数据库名和表的别名。该值为Windows的默认值。

为2时；
表名和数据库名在硬盘上使用CREATE TABLE或CREATE DATABASE语句指定的大小写字母进行保存，但MySQL将它们
转换为小写在查找表上。名称比较对大小写不敏感，即按照大小写来保存，按照小写来比较。注释：只在对大小
写不敏感的文件系统上使用! innodb表名用小写保存。

如果你使用innodb表，为了避免避免大小写敏感问题，可以把lower_case_table_names=1

把lower_case_table_names从0改变为1
在你把lower_case_table_names设置为1时，在restart你的mysqld之前，请把数据库名和表名更改为小写
 mysql> RENAME TABLE T1 TO t1;

2.和字段名字有关
字段名是不区分大小写的

3.和字段值有关
字段值的大小写由Mysql的collate来控制。提到collate，就不得不说字符集。字符集是一套符号和编码，collate
是在字符集内用于比较字符的一套规则，比如定义'A'<'B'这样的关系的规则。不同的字符集有多种校对规则，一般
而言，collate以其相关的字符集名开始，通常包括一个语言名，并且以_ci（大小写不敏感）、_cs（大小写敏感）
或_bin（二元）结束 。

比如 utf8字符集：
utf8_general_ci,表示不区分大小写，这个是utf8字符集默认的collate；
utf8_general_cs表示区分大小写，
utf8_bin表示二进制比较，同样也区分大小写。

查看数据库的字符集相关信息
mysql> show variables like '%character%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)

查看数据库collation相关信息
mysql> show variables like '%collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.01 sec)

test是默认创建的数据库
mysql> show create database test;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

testx是指定字符集和collation的数据库
mysql> create database testx default character set utf8 collate utf8_bin;
Query OK, 1 row affected (0.00 sec)

mysql> show create database testx;
+----------+---------------------------------------------------------------------------------+
| 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