Mysql数据库(大小写敏感)区分大小写的问题总结 |
您所在的位置:网站首页 › mysql查询不区分大小写语句怎么办 › Mysql数据库(大小写敏感)区分大小写的问题总结 |
MySQL默认是不区分大小写的,但是在很多情况下需要大小敏感,以下总结了多种设置MySQL大小写敏感的方法。
方法一: 1、Linux 系统默认情况下MySQL数据库是区分大小写的;因此要将mysql设置成不区分大小写,在/etc/my.cnf 配置后面加上一行 lower_case_table_names=0(0:大小写敏感;1:大小写不敏感) 最后重启一下MySql服务即可。
2、Windows系统默认是区分大小写的,修改MySql Server安装目录下配置my.ini 文件 ,在mysqld节下加入下面一行配置忽略大小写 set-variable=lower_case_table_names=0 (0:大小写敏感;1:大小写不敏感) 最后重启一下MySql服务即可。 3、数据库大小写敏感配置查询语句 mysql> show variables like 'lower%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_file_system | OFF | | lower_case_table_names | 1 | +------------------------+-------+ 2 rows in set (0.00 sec)
方法二: 一种方法是可以设置表或行的collation,使其为binary或case sensitive。在MySQL中,对于Column Collate其约定的命名方法如下: *_bin: 表示的是binary case sensitive collation,也就是说是区分大小写的 *_cs: case sensitive collation,区分大小写 *_ci: case insensitive collation,不区分大小写 ########### # Start binary collation example ########### mysql> create table case_bin_test (word VARCHAR(10)) CHARACTER SET latin1 COLLATE latin1_bin; Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO case_bin_test VALUES ('Frank'),('Google'),('froogle'),('flickr'),('FlicKr'); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM case_bin_test WHERE word LIKE 'f%'; +---------+ | word | +---------+ | froogle | | flickr | +---------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM case_bin_test WHERE word LIKE 'F%'; +---------+ | word | +---------+ | Frank | | FlicKr | +---------+ 4 rows in set (0.00 sec) ########### # End ########### 方法三: ########### # Start case sensitive collation example ########### mysql> create table case_cs_test (word VARCHAR(10)) CHARACTER SET latin1 COLLATE latin1_general_cs; Query OK, 0 rows affected (0.08 sec) mysql> INSERT INTO case_cs_test VALUES ('Frank'),('Google'),('froogle'),('flickr'),('FlicKr'); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM case_cs_test WHERE word LIKE 'F%'; +---------+ | word | +---------+ | Frank | | FlicKr | +---------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM case_cs_test WHERE word LIKE 'f%'; +---------+ | word | +---------+ | froogle | | flickr | +---------+ 2 rows in set (0.00 sec) ########### # end ########### 方法四:就是在查询时指定collation mysql> create table case_test (word VARCHAR(10)) CHARACTER SET latin1; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO case_test VALUES ('Frank'),('Google'),('froogle'),('flickr'),('FlicKr'); Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM case_test WHERE word LIKE 'f%'; +---------+ | word | +---------+ | Frank | | froogle | | flickr | | FlicKr | +---------+ 6 rows in set (0.01 sec) mysql> SELECT * FROM case_test WHERE word LIKE 'F%'; +---------+ | word | +---------+ | Frank | | froogle | | flickr | | FlicKr | +---------+ 6 rows in set (0.01 sec) mysql> SELECT * FROM case_test WHERE word COLLATE latin1_bin LIKE 'F%'; +---------+ | word | +---------+ | Frank | | FlicKr | +---------+ 4 rows in set (0.05 sec) mysql> SELECT * FROM case_test WHERE word COLLATE latin1_bin LIKE 'f%'; +---------+ | word | +---------+ | froogle | | flickr | +---------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM case_test WHERE word LIKE 'f%' COLLATE latin1_bin; +---------+ | word | +---------+ | froogle | | flickr | +---------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM case_test WHERE word LIKE 'F%' COLLATE latin1_bin; +---------+ | word | +---------+ | Frank | | FlicKr | +---------+ 4 rows in set (0.01 sec) mysql> SELECT * FROM case_test WHERE word LIKE 'F%' COLLATE latin1_general_cs; +---------+ | word | +---------+ | Frank | | FlicKr | +---------+ 4 rows in set (0.04 sec) 方法五: 要让mysql查询区分大小写,可以: select * from table_name where binary a like 'a%' select * from table_name where binary a like 'A%'也可以在建表时,加以标识 create table table_name( a varchar (20) binary ) |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |