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

灰太狼 2022-06-15 01:26 171阅读 0赞

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

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服务。