Mysql区分大小写问题以及两表处理
Mysql区分大小写问题
修改MySql Server安装目录下的 my.ini 文件,在mysqld节下加入下面一行 set-variable=lower_case_table_names=0 (0:大小写敏感;1:大小写不敏感)最后重启一下MySql服务即可。
Mysql查询语句中字符型字段不区分大小写解决方法
项目中和前端联调的时候,发现Mysql查询语句中字符型字段值过滤是不区分大小写的,之前没有关注过这个设置,特意去网上看了下,原因是Mysql中“COLLATE”属性区分大小写,而该属性默认值为“utf8_general_ci”,这个值表示是不区分大小写的。现将解决方法记录如下:
1.设置“COLLATE”属性值为“utf8_bin”或者“utf8_gerneral_cs”,utf8_bin表示二进制比较,同时也支持区分大小写;在Mysql5.6.10版本中,不支持utf8_genral_cs;
2.在创建表的时候,指定表字段COLLATE 为utf8_bin或者utf8_general_cs(注意版本),如:
alter table sample modify column module varchar(128) COLLATE utf8_bin ;
3.修改字段为BINARY:
alter table sample modify column module varchar(128) BINARY;
4.查询语句字段前面加BINARY:
select * from sample where BINARY module='SAMPLE';
Keep moving.
这边补充一下,建表时候utf8各个字段微小的差异。
utf8_general_ci 不区分大小写,这个你在注册用户名和邮箱的时候就要使用。
utf8_general_cs 区分大小写,如果用户名和邮箱用这个 就会造成不良后果
utf8_bin:字符串每个字符串用二进制数据编译存储。 区分大小写,而且可以存二进制的内容
utf8_general_ci校对速度快,但准确度稍差。
utf8_unicode_ci准确度高,但校对速度稍慢。
下面两种查询方式都帮我解决了大小写的问题:
SELECT DISTINCT BINARY text_pw FROM `dictionary_library`.`test001`
SELECT DISTINCT
(CAST(text_pw AS CHAR CHARACTER SET utf8) COLLATE utf8_bin) AS text_pw FROM `dictionary_library`.`test001`
测试语句:
insert into
test003
SELECT DISTINCT BINARY text_pw FROM `dictionary_library`.`test001`
UNION
SELECT DISTINCT BINARY text_pw FROM `dictionary_library`.`test002`
--------------------------------------------------------------------
insert into
dictionary_kaspar
SELECT DISTINCT BINARY text_pw FROM `dictionary_library`.`dictionary_passper_15466385`
UNION
SELECT DISTINCT BINARY text_pw FROM `dictionary_library`.`rockyou_kali202101_14343751`
UNION
SELECT DISTINCT BINARY text_pw FROM `dictionary_library`.`shentouzidian_17140`
Distinct可以说是数据查询中最耗时最耗性能的操作了,去重统计是数据查询不可言说的痛,所以不到万不得已不要用,另外,union all 后在distinct的效率如果更高的话,那union存在的意义是什么?所以可想而知,union的效率更高啊”
在 MySQL 和 Oracle 中,SELECT 执行顺序基本相同)
FROM > WHERE > GROUP BY > HAVING > SELECT的字段 > DISTINCT > ORDER BY > LIMIT
修改过后(两张表区分大小写去重插入第三张表):
INSERT INTO
test003
SELECT BINARY text_pw FROM `dictionary_library`.`test001`
UNION
SELECT BINARY text_pw FROM `dictionary_library`.`test002`
区分大小写查询一张表在另一张表没有的数据:
(使用关键字:NOT EXISTS或NOT IN)
select BINARY text_pw from test001 AS t1 WHERE NOT EXISTS (SELECT BINARY text_pw FROM test002 AS t2 WHERE BINARY t2.text_pw=BINARY t1.text_pw)
select BINARY text_pw from test001 AS t1 WHERE BINARY text_pw NOT IN (SELECT BINARY text_pw FROM test002 AS t2 WHERE BINARY t2.text_pw=BINARY t1.text_pw)
插入第三张表选择其一前面加上
INSERT INTO test003