MySQL数据表中内容大小写区分的设置
 
MYSQL在默认的情况下查询是不区分大小写的,例如: 
 
> create table t1( 
-> name varchar(10)); 
Query OK, 0 rows affected (0.09 sec) 

mysql> insert into t1 values('you'),('You'),('YOU'); 
Query OK, 3 rows affected (0.05 sec) 
Records: 3 Duplicates: 0 Warnings: 0 

 

 
对这个表,缺省情况下,下面两个查询的结果是一样的: 
 
mysql> select * from t1 where name = 'you'; 
+------+ 
| name | 
+------+ 
| you | 
| You | 
| YOU | 
+------+ 
3 rows in set (0.00 sec) 

mysql> select * from t1 where name = 'YOU'; 
+------+ 
| name | 
+------+ 
| you | 
| You | 
| YOU | 
+------+ 
3 rows in set (0.00 sec) 

 

 
如果想让MYSQL知道你输入的字母是大写还是小写的,修改表: 
 
mysql> alter table t1 change name name varchar(10) binary; 
Query OK, 3 rows affected (0.20 sec) 
Records: 3 Duplicates: 0 Warnings: 0 


mysql> select * from t1 where name = 'you'; 
+------+ 
| name | 
+------+ 
| you | 
+------+ 
1 row in set (0.00 sec) 

mysql> select * from t1 where name = 'YOU'; 
+------+ 
| name | 
+------+ 
| YOU | 
+------+ 
1 row in set (0.00 sec) 

 

如果你只是想在SQL语句中实现的话: 
 
mysql> select * from t1 where name = binary 'YOU'; 
+------+ 
| name | 
+------+ 
| YOU | 
+------+ 
1 row in set (0.02 sec) 

mysql> select * from t1 where name = binary 'you'; 
+------+ 
| name | 
+------+ 
| you | 
+------+ 
1 row in set (0.00 sec) 

 

 
如果不想这么麻烦而想服务一开启就让大小写一致的话: 
可以修改my.ini或者my.cnf 
[mysqld] 
lower_case_table_names=1 
(0:区分;1:不区分) 

 

然后重启MYSQL服务。 
 
mysql> show variables like '%case_table%'; 
+------------------------+-------+ 
| Variable_name | Value | 
+------------------------+-------+ 
| lower_case_table_names | 1 | 
+------------------------+-------+ 
1 row in set (0.00 sec)