【背景】
某某同学执行了一下Explain结果结果发现数据库有了一条写入操作,恭喜这位同学你的锅到货了,你签收一下;
对! 你没有听错,在一种场景下就算是Explain也会引发数据的写操作,就这是外层查询访问任意表,内层查询调用function
在function有写入动作的情况下会发生写入。
【硬生生的套上一个场景】
假设我们有一个Person表,每访问一次Person表都记录一次在什么时候,访问了哪一行,表结构设计如下
create table if not exists person(
id int not null auto_increment primary key,
name varchar(16)
); create table if not exists person_opration_log(
id int not null auto_increment primary key,
pid int not null,
access_datetime datetime
); delimiter //
create function fun_person_log(pid int) returns int
begin
insert into person_opration_log(pid,access_datetime) values(pid,now());
return pid;
end // delimiter ;
正常的数据访问SQL如下,但是它并不写日志
mysql> select
-> id,
-> name
-> from person
-> where id = 1;
+----+--------+
| id | name |
+----+--------+
| 1 | 项羽 |
+----+--------+
1 row in set (0.00 sec)
如果我们要写日志可以分两步走,先访问再计一笔日志
mysql> select
-> id,
-> name
-> from person
-> where id = 1;
+----+--------+
| id | name |
+----+--------+
| 1 | 项羽 |
+----+--------+
1 row in set (0.00 sec) mysql>
mysql> select fun_person_log(1);
+-------------------+
| fun_person_log(1) |
+-------------------+
| 1 |
+-------------------+
1 row in set (0.05 sec) mysql>
mysql> select * from person_opration_log ;
+----+-----+---------------------+
| id | pid | access_datetime |
+----+-----+---------------------+
| 1 | 1 | 2018-10-06 17:12:31 |
+----+-----+---------------------+
1 row in set (0.00 sec)
【牛人想出的新点子把两步合成一步】
牛人的新点子
mysql> select
-> fun_person_log(100) as id ,
-> name
-> from person
-> where id = (select fun_person_log(100));
Empty set (0.04 sec) mysql>
mysql> select * from person_opration_log;
+----+-----+---------------------+
| id | pid | access_datetime |
+----+-----+---------------------+
| 1 | 1 | 2018-10-06 17:12:31 |
| 2 | 100 | 2018-10-06 17:15:29 |
+----+-----+--------------------
牛人的新点子刚好入坑,我们可以explain一下
mysql> explain select
-> fun_person_log(250) as id ,
-> name
-> from person
-> where id = (select fun_person_log(250));
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
2 rows in set, 1 warning (0.08 sec) mysql>
mysql> select * from person_opration_log;
+----+-----+---------------------+
| id | pid | access_datetime |
+----+-----+---------------------+
| 1 | 1 | 2018-10-06 17:12:31 |
| 2 | 100 | 2018-10-06 17:15:29 |
| 3 | 250 | 2018-10-06 17:17:23 |
+----+-----+---------------------+
3 rows in set (0.00 sec)
看吧! explain引发了写入操作!
【参考连接】
【学习交流】
-----------------------------http://www.sqlpy.com-------------------------------------------------
-----------------------------http://www.sqlpy.com-------------------------------------------------