Mysql select默认不区分大小写

http://blog.chinaunix.net/u/29134/showart_355336.html MYSQL 在默认的情况下查询是不区分 大小写 的，例如： mysql create table t1( - name varchar(10)); Query OK, 0 rows affected (0.09 sec) mysql insert into t1 values('you'),('You'),('YOU'); Q

http://blog.chinaunix.net/u/29134/showart_355336.html

MYSQL在默认的情况下查询是不区分大小写的，例如：<br> <br> mysql> create table t1(<br>     -> name varchar(10));<br> Query OK, 0 rows affected (0.09 sec)<br> <br> mysql> insert into t1 values('you'),('You'),('YOU');<br> Query OK, 3 rows affected (0.05 sec)<br> Records: 3 Duplicates: 0 Warnings: 0<br> 对这个表，缺省情况下，下面两个查询的结果是一样的：<br> <br>

<br>

mysql> select * from t1 where name = 'you';<br> +------+<br> | name |<br> +------+<br> | you |<br> | You |<br> | YOU |<br> +------+<br> 3 rows in set (0.00 sec)<br> <br> mysql> select * from t1 where name = 'YOU';<br> +------+<br> | name |<br> +------+<br> | you |<br> | You |<br> | YOU |<br> +------+<br> 3 rows in set (0.00 sec) 如果想让MYSQL知道你输入的字母是大写还是小写的，修改表：<br> <br>

mysql> alter table t1 change name name varchar(10) binary;<br> Query OK, 3 rows affected (0.20 sec)<br> Records: 3 Duplicates: 0 Warnings: 0<br>

<br>

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

如果你只是想在SQL语句中实现的话：<br> <br> mysql> select * from t1 where name = binary 'YOU';<br> +------+<br> | name |<br> +------+<br> | YOU |<br> +------+<br> 1 row in set (0.02 sec)<br> <br> mysql> select * from t1 where name = binary 'you';<br> +------+<br> | name |<br> +------+<br> | you |<br> +------+<br> 1 row in set (0.00 sec)<br> 如果不想这么麻烦而想服务一开启就让大小写一致的话：<br> 可以修改my.ini或者my.cnf<br> [mysqld]<br> lower_case_table_names=1<br> （0：区分；1：不区分）<br> 然后重启MYSQL服务。<br> <br> mysql> show variables like '%case_table%';<br> +------------------------+-------+<br> | Variable_name          | Value |<br> +------------------------+-------+<br> | lower_case_table_names | 1     |<br> +------------------------+-------+<br> 1 row in set (0.00 sec)<br> 注：WINDOWS系统不用修改，系统默认就是1<br> LINUX 系统默认是0。因为LINUX下的脚本都是区分大小写的。