mysql查询设置大小写敏感以及性能(explain)对比

2018-10-16

2019-08-07

数据与中间件

MySQL

/

EXPLAIN

/

索引

/

性能优化

受到此文某些似懂非懂话语影响而折腾的笔记

解决方法:

查询时加上`binary`#

explain 分析 sql语句其中type有变动(sk已加上索引) type值解析

使用 binary 查询时的第一组 EXPLAIN 结果

使用 binary 查询时的第二组 EXPLAIN 结果

而不加binary 不区分大小写时

不区分大小写查询时的 EXPLAIN 结果

查询时强制二进制对比，尤其不好，这会让你代码中多出很多判断，且还有可能被绕过。至于直接使用二进制字段，mysql不会将它当成字符来处理，我觉得也可能存在问题。

字段建立时指定字符类型#

点我查看建立时的字符类型

MySQL 字段字符集与排序规则设置

使用utf8_bin

select * from api_key where sk = 'Gsxg583i+JhD4nHFCroeLKlMpUAI0XV9Rzjf6YqO_1Svy7QZkmw2PB'

Select 语句可以区分大小写执行效率:

explain select * from api_key where sk = 'GSxg583i+JhD4nHFCroeLKlMpUAI0XV9Rzjf6YqO_1Svy7QZkmw2PB'
使用BINARY

MySQL BINARY 字段定义示例

1
`select * from api_key where sk = 'Gsxg583i+JhD4nHFCroeLKlMpUAI0XV9Rzjf6YqO_1Svy7QZkmw2PB'`
2
Select 语句可以区分大小写
3
执行效率:
4
`explain select * from api_key where sk = 'GSxg583i+JhD4nHFCroeLKlMpUAI0XV9Rzjf6YqO_1Svy7QZkmw2PB'`

BINARY 字段查询的 EXPLAIN 结果

数据库创建时修改#

方式1 方式2

laravel 数据库迁移解决方案#

1
public function up()
2
{
3
    Schema::create('api_key', function (Blueprint $table) {
4

5
        //一堆建表语句
6
    });
7
    //创建表成功后添加大小写敏感
8
    DB::statement("alter table api_key modify column ak varchar(255)  BINARY DEFAULT '' NOT NULL COMMENT 'as'");
9
    DB::statement("alter table api_key modify column sk varchar(255)  BINARY DEFAULT '' NOT NULL COMMENT 'as'");
10
}