MySQL数据表中内容大小写区分的设置

页面导航:首页 > 数据库 > Mysql > MySQL数据表中内容大小写区分的设置

MySQL数据表中内容大小写区分的设置

来源: 作者: 时间:2016-02-05 10:56 【

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

 


Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<