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

MYSQL在默认的情况下查询是不区分大小写的，例如： 

 

?

1 2 3 4 5 6 7

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

 

 

对这个表，缺省情况下，下面两个查询的结果是一样的： 

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

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知道你输入的字母是大写还是小写的，修改表： 

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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语句中实现的话： 

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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 

?

1 2 3

[mysqld] lower_case_table_names=1 （0：区分；1：不区分）

 

然后重启MYSQL服务。 

 

?

1

2

3

4

5

6

7

mysql> show variables like '%case_table%';

+------------------------+-------+

| Variable_name | Value |

+------------------------+-------+

| lower_case_table_names | 1 |

+------------------------+-------+

1 row in set (0.00 sec)

 

 

在跨平台的程序设计中要注意到mysql的一些系统变量在windows和linux上的缺省值是不同的, 比如mysql表名称的大小写变量.

在windows上lower_case_table_names变量的缺省值为1; 在linux上为0; 在mac os上为2; 

该变量值的详细定义如下 

Value	Meaning
0	Table and database names are stored on disk using the lettercase specified in the CREATE TABLE orCREATE DATABASE statement. Name comparisons are case sensitive. You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or Mac OS X). If you force this variable to 0 with --lower-case-table-names=0 on a case-insensitive file system and access MyISAM tablenames using different lettercases, index corruption may result.
1	Table names are stored in lowercase on disk and name comparisons are not case sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.
2	Table and database names are stored on disk using the lettercase specified in the CREATE TABLE orCREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on file systems that are not case sensitive! InnoDB table names are stored in lowercase, as forlower_case_table_names=1.