将MySQL去重操作优化到极致的操作方法

源表t_source结构如下：

item_id int, created_time datetime, modified_time datetime, item_name varchar(20), other varchar(20)

1.源表中有100万条数据，其中有50万created_time和item_name重复。
2.要把去重后的50万数据写入到目标表。
3.重复created_time和item_name的多条数据，可以保留任意一条，不做规则限制。

•实验环境

Linux虚机：CentOS release 6.4；8G物理内存（MySQL配置4G）；100G机械硬盘；双物理CPU双核，共四个处理器；MySQL 8.0.16。

•建立测试表和数据

-- 建立源表create table t_source ( item_id int,  created_time datetime,  modified_time datetime,  item_name varchar(20),  other varchar(20) ); -- 建立目标表create table t_target like t_source; -- 生成100万测试数据，其中有50万created_time和item_name重复delimiter // create procedure sp_generate_data() begin  set @i := 1;  while @i<=500000 do  set @created_time := date_add('2017-01-01',interval @i second);  set @modified_time := @created_time;  set @item_name := concat('a',@i);  insert into t_source  values (@i,@created_time,@modified_time,@item_name,'other');  set @i:=@i+1;  end while;  commit;  set @last_insert_id := 500000;  insert into t_source  select item_id + @last_insert_id,  created_time,  date_add(modified_time,interval @last_insert_id second),  item_name,  'other'  from t_source;  commit;end // delimiter ; call sp_generate_data(); -- 源表没有主键或唯一性约束，有可能存在两条完全一样的数据，所以再插入一条记录模拟这种情况。insert into t_source select * from t_source where item_id=1; 源表中有1000001条记录，去重后的目标表应该有500000条记录。mysql> select count(*),count(distinct created_time,item_name) from t_source;+----------+----------------------------------------+| count(*) | count(distinct created_time,item_name) |+----------+----------------------------------------+| 1000001 |   500000 |+----------+----------------------------------------+1 row in set (1.92 sec)

truncate t_target; insert into t_target select distinct t1.* from t_source t1 where item_id in (select min(item_id) from t_source t2 where t1.created_time=t2.created_time and t1.item_name=t2.item_name);

mysql> explain select distinct t1.* from t_source t1 where item_id in  -> (select min(item_id) from t_source t2 where t1.created_time=t2.created_time and t1.item_name=t2.item_name); +----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra  |+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 997282 | 100.00 | Using where; Using temporary || 2 | DEPENDENT SUBQUERY | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 997282 | 1.00 | Using where  |+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+2 rows in set, 3 warnings (0.00 sec)

（2）使用表连接

truncate t_target; insert into t_target select distinct t1.* from t_source t1, (select min(item_id) item_id,created_time,item_name from t_source group by created_time,item_name) t2 where t1.item_id = t2.item_id;

mysql> explain select distinct t1.* from t_source t1, (select min(item_id) item_id,created_time,item_name from t_source group by created_time,item_name) t2 where t1.item_id = t2.item_id;+----+-------------+------------+------------+------+---------------+-------------+---------+-----------------+--------+----------+------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra  |+----+-------------+------------+------------+------+---------------+-------------+---------+-----------------+--------+----------+------------------------------+| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 997282 | 100.00 | Using where; Using temporary || 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 5 | test.t1.item_id | 10 | 100.00 | Distinct  || 2 | DERIVED | t_source | NULL | ALL | NULL | NULL | NULL | NULL | 997282 | 100.00 | Using temporary |+----+-------------+------------+------------+------+---------------+-------------+---------+-----------------+--------+----------+------------------------------+3 rows in set, 1 warning (0.00 sec)

（3）使用变量

set @a:='1000-01-01 00:00:00'; set @b:=' '; set @f:=0; truncate t_target; insert into t_target select item_id,created_time,modified_time,item_name,other  from (select t0.*,if(@a=created_time and @b=item_name,@f:=0,@f:=1) f, @a:=created_time,@b:=item_name  from (select * from t_source order by created_time,item_name) t0) t1 where f=1;

mysql> explain select item_id,created_time,modified_time,item_name,other  -> from  -> (select t0.*,if(@a=created_time and @b=item_name,@f:=0,@f:=1) f, @a:=created_time,@b:=item_name  -> from  -> (select * from t_source order by created_time,item_name) t0) t1 where f=1; +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 4 | const | 10 | 100.00 | NULL || 2 | DERIVED | <derived3> | NULL | ALL | NULL | NULL | NULL | NULL | 997282 | 100.00 | NULL || 3 | DERIVED | t_source | NULL | ALL | NULL | NULL | NULL | NULL | 997282 | 100.00 | Using filesort |+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+3 rows in set, 5 warnings (0.00 sec)

与上面方法2比较，总的扫描行数不变，都是200万行。只存在一点微小的差别，这次自动生成的索引是在常量列 f 上，而表关联自动生成的索引是在item_id列上，所以查询时间几乎相同。

至此，我们还没有在源表上创建任何索引。无论使用哪种写法，要查重都需要对created_time和item_name字段进行排序，因此很自然地想到，如果在这两个字段上建立联合索引，利用索引本身有序的特性消除额外排序，从而提高查询性能。

2. 建立created_time和item_name上的联合索引对比测试

-- 建立created_time和item_name字段的联合索引create index idx_sort on t_source(created_time,item_name,item_id); analyze table t_source;

truncate t_target; insert into t_target select distinct t1.* from t_source t1 where item_id in (select min(item_id) from t_source t2 where t1.created_time=t2.created_time and t1.item_name=t2.item_name);

mysql> explain select distinct t1.* from t_source t1 where item_id in  -> (select min(item_id) from t_source t2 where t1.created_time=t2.created_time and t1.item_name=t2.item_name); +----+--------------------+-------+------------+------+---------------+----------+---------+----------------------------------------+--------+----------+------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref   | rows | filtered | Extra  |+----+--------------------+-------+------------+------+---------------+----------+---------+----------------------------------------+--------+----------+------------------------------+| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL   | 997281 | 100.00 | Using where; Using temporary || 2 | DEPENDENT SUBQUERY | t2 | NULL | ref | idx_sort | idx_sort | 89 | test.t1.created_time,test.t1.item_name | 2 | 100.00 | Using index  |+----+--------------------+-------+------------+------+---------------+----------+---------+----------------------------------------+--------+----------+------------------------------+2 rows in set, 3 warnings (0.00 sec)

•对于驱动表每行的item_id，通过idx_sort索引查询出两行数据。

（2）使用表连接

truncate t_target; insert into t_target select distinct t1.* from t_source t1, (select min(item_id) item_id,created_time,item_name from t_source group by created_time,item_name) t2 where t1.item_id = t2.item_id;

mysql> explain select distinct t1.* from t_source t1,  -> (select min(item_id) item_id,created_time,item_name from t_source group by created_time,item_name) t2  -> where t1.item_id = t2.item_id; +----+-------------+------------+------------+-------+---------------+-------------+---------+-----------------+--------+----------+------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra  |+----+-------------+------------+------------+-------+---------------+-------------+---------+-----------------+--------+----------+------------------------------+| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 997281 | 100.00 | Using where; Using temporary || 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 5 | test.t1.item_id | 10 | 100.00 | Distinct  || 2 | DERIVED | t_source | NULL | index | idx_sort | idx_sort | 94 | NULL | 997281 | 100.00 | Using index  |+----+-------------+------------+------------+-------+---------------+-------------+---------+-----------------+--------+----------+------------------------------+3 rows in set, 1 warning (0.00 sec)

（3）使用变量

set @a:='1000-01-01 00:00:00'; set @b:=' '; set @f:=0; truncate t_target; insert into t_target select item_id,created_time,modified_time,item_name,other  from (select t0.*,if(@a=created_time and @b=item_name,@f:=0,@f:=1) f, @a:=created_time,@b:=item_name  from (select * from t_source order by created_time,item_name) t0) t1 where f=1;

（4）使用变量，并且消除嵌套查询

set @a:='1000-01-01 00:00:00'; set @b:=' '; truncate t_target; insert into t_target select * from t_source force index (idx_sort)  where (@a!=created_time or @b!=item_name) and (@a:=created_time) is not null and (@b:=item_name) is not null  order by created_time,item_name;

mysql> explain select * from t_source force index (idx_sort)  -> where (@a!=created_time or @b!=item_name) and (@a:=created_time) is not null and (@b:=item_name) is not null  -> order by created_time,item_name;+----+-------------+----------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+----------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+| 1 | SIMPLE | t_source | NULL | index | NULL | idx_sort | 94 | NULL | 997281 | 99.00 | Using where |+----+-------------+----------+------------+-------+---------------+----------+---------+------+--------+----------+-------------+1 row in set, 3 warnings (0.00 sec)

•消除了嵌套子查询，只需要对t_source表进行一次全索引扫描，查询计划已达最优。
•无需distinct二次查重。
•变量判断与赋值只出现在where子句中。
•利用索引消除了filesort。

在MySQL 8之前，该语句是单线程去重的最佳解决方案。仔细分析这条语句，发现它巧妙地利用了SQL语句的逻辑查询处理步骤和索引特性。一条SQL查询的逻辑步骤为：

1.执行笛卡尔乘积（交叉连接）
2.应用ON筛选器（连接条件）
3.添加外部行（outer join）
4.应用where筛选器
5.分组
6.应用cube或rollup
7.应用having筛选器
8.处理select列表
9.应用distinct子句
10.应用order by子句
11.应用limit子句

每条查询语句的逻辑执行步骤都是这11步的子集。拿这条查询语句来说，其执行顺序为：强制通过索引idx_sort查找数据行 -> 应用where筛选器 -> 处理select列表 -> 应用order by子句。

为了使变量能够按照created_time和item_name的排序顺序进行赋值和比较，必须按照索引顺序查找数据行。这里的force index (idx_sort)提示就起到了这个作用，必须这样写才能使整条查重语句成立。否则，因为先扫描表才处理排序，因此不能保证变量赋值的顺序，也就不能确保查询结果的正确性。order by子句同样不可忽略，否则即使有force index提示，MySQL也会使用全表扫描而不是全索引扫描，从而使结果错误。索引同时保证了created_time,item_name的顺序，避免了文件排序。force index (idx_sort)提示和order by子句缺一不可，索引idx_sort在这里可谓恰到好处、一举两得。

查询语句开始前，先给变量初始化为数据中不可能出现的值，然后进入where子句从左向右判断。先比较变量和字段的值，再将本行created_time和item_name的值赋给变量，按created_time、item_name的顺序逐行处理。item_name是字符串类型，(@b:=item_name)不是有效的布尔表达式，因此要写成(@b:=item_name) is not null。

最后补充一句，这里忽略了“insert into t_target select * from t_source group by created_time,item_name;”的写法，因为它受“sql_mode='ONLY_FULL_GROUP_BY'”的限制。

二、利用窗口函数

MySQL 8中新增的窗口函数使得原来麻烦的去重操作变得很简单。

truncate t_target; insert into t_target select item_id, created_time, modified_time, item_name, other from (select *, row_number() over(partition by created_time,item_name) as rn from t_source) t where rn=1;

mysql> explain select item_id, created_time, modified_time, item_name, other -> from (select *, row_number() over(partition by created_time,item_name) as rn -> from t_source) t where rn=1;+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 8 | const | 10 | 100.00 | NULL || 2 | DERIVED | t_source | NULL | ALL | NULL | NULL | NULL | NULL | 997281 | 100.00 | Using filesort |+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------+2 rows in set, 2 warnings (0.00 sec)

从执行计划看，窗口函数去重语句似乎没有消除嵌套查询的变量去重好，但此方法实际执行是最快的。

MySQL窗口函数说明参见“https://dev.mysql.com/doc/refman/8.0/en/window-functions.html”。

三、多线程并行执行

前面已经将单条查重语句调整到最优，但还是以单线程方式执行。能否利用多处理器，让去重操作多线程并行执行，从而进一步提高速度呢？比如我的实验环境是4处理器，如果使用4个线程同时执行查重SQL，理论上应该接近4倍的性能提升。

1. 数据分片

在生成测试数据时，created_time采用每条记录加一秒的方式，也就是最大和在最小的时间差为50万秒，而且数据均匀分布，因此先把数据平均分成4份。

（1）查询出4份数据的created_time边界值

mysql> select date_add('2017-01-01',interval 125000 second) dt1, -> date_add('2017-01-01',interval 2*125000 second) dt2, -> date_add('2017-01-01',interval 3*125000 second) dt3, -> max(created_time) dt4 -> from t_source;+---------------------+---------------------+---------------------+---------------------+| dt1   | dt2   | dt3   | dt4   |+---------------------+---------------------+---------------------+---------------------+| 2017-01-02 10:43:20 | 2017-01-03 21:26:40 | 2017-01-05 08:10:00 | 2017-01-06 18:53:20 |+---------------------+---------------------+---------------------+---------------------+1 row in set (0.00 sec)

mysql> select case when created_time >= '2017-01-01'  ->  and created_time < '2017-01-02 10:43:20' ->  then '2017-01-01' ->  when created_time >= '2017-01-02 10:43:20' ->  and created_time < '2017-01-03 21:26:40' ->  then '2017-01-02 10:43:20' ->  when created_time >= '2017-01-03 21:26:40'  ->  and created_time < '2017-01-05 08:10:00' ->  then '2017-01-03 21:26:40'  ->  else '2017-01-05 08:10:00' ->  end min_dt, -> case when created_time >= '2017-01-01'  ->  and created_time < '2017-01-02 10:43:20' ->  then '2017-01-02 10:43:20' ->  when created_time >= '2017-01-02 10:43:20' ->  and created_time < '2017-01-03 21:26:40' ->  then '2017-01-03 21:26:40' ->  when created_time >= '2017-01-03 21:26:40'  ->  and created_time < '2017-01-05 08:10:00' ->  then '2017-01-05 08:10:00' ->  else '2017-01-06 18:53:20' ->  end max_dt, -> count(*) -> from t_source -> group by case when created_time >= '2017-01-01'  ->  and created_time < '2017-01-02 10:43:20' ->  then '2017-01-01' ->  when created_time >= '2017-01-02 10:43:20' ->  and created_time < '2017-01-03 21:26:40' ->  then '2017-01-02 10:43:20' ->  when created_time >= '2017-01-03 21:26:40'  ->  and created_time < '2017-01-05 08:10:00' ->  then '2017-01-03 21:26:40'  ->  else '2017-01-05 08:10:00' ->  end, -> case when created_time >= '2017-01-01'  ->  and created_time < '2017-01-02 10:43:20' ->  then '2017-01-02 10:43:20' ->  when created_time >= '2017-01-02 10:43:20' ->  and created_time < '2017-01-03 21:26:40' ->  then '2017-01-03 21:26:40' ->  when created_time >= '2017-01-03 21:26:40'  ->  and created_time < '2017-01-05 08:10:00' ->  then '2017-01-05 08:10:00' ->  else '2017-01-06 18:53:20' ->  end;+---------------------+---------------------+----------+| min_dt  | max_dt  | count(*) |+---------------------+---------------------+----------+| 2017-01-01  | 2017-01-02 10:43:20 | 249999 || 2017-01-02 10:43:20 | 2017-01-03 21:26:40 | 250000 || 2017-01-03 21:26:40 | 2017-01-05 08:10:00 | 250000 || 2017-01-05 08:10:00 | 2017-01-06 18:53:20 | 250002 |+---------------------+---------------------+----------+4 rows in set (4.86 sec)

2. 建立查重的存储过程

有了以上信息我们就可以写出4条语句处理全部数据。为了调用接口尽量简单，建立下面的存储过程。

delimiter //create procedure sp_unique(i smallint) begin  set @a:='1000-01-01 00:00:00';  set @b:=' ';  if (i<4) then insert into t_target  select * from t_source force index (idx_sort)   where created_time >= date_add('2017-01-01',interval (i-1)*125000 second)   and created_time < date_add('2017-01-01',interval i*125000 second)   and (@a!=created_time or @b!=item_name)   and (@a:=created_time) is not null   and (@b:=item_name) is not null   order by created_time,item_name;  else  insert into t_target  select * from t_source force index (idx_sort)   where created_time >= date_add('2017-01-01',interval (i-1)*125000 second)   and created_time <= date_add('2017-01-01',interval i*125000 second)   and (@a!=created_time or @b!=item_name)   and (@a:=created_time) is not null   and (@b:=item_name) is not null   order by created_time,item_name;  end if; end //

mysql> explain select * from t_source force index (idx_sort)  ->  where created_time >= date_add('2017-01-01',interval (1-1)*125000 second)  ->  and created_time < date_add('2017-01-01',interval 1*125000 second)  ->  and (@a!=created_time or @b!=item_name)  ->  and (@a:=created_time) is not null  ->  and (@b:=item_name) is not null  ->  order by created_time,item_name; +----+-------------+----------+------------+-------+---------------+----------+---------+------+--------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra   |+----+-------------+----------+------------+-------+---------------+----------+---------+------+--------+----------+-----------------------+| 1 | SIMPLE | t_source | NULL | range | idx_sort | idx_sort | 6 | NULL | 498640 | 100.00 | Using index condition |+----+-------------+----------+------------+-------+---------------+----------+---------+------+--------+----------+-----------------------+1 row in set, 3 warnings (0.00 sec)

3. 并行执行

下面分别使用shell后台进程和MySQL Schedule Event实现并行。

（1）shell后台进程

•建立duplicate_removal.sh文件，内容如下：

#!/bin/bashmysql -vvv -u root -p123456 test -e "truncate t_target" &>/dev/null date '+%H:%M:%S'for y in {1..4}do sql="call sp_unique($y)" mysql -vvv -u root -p123456 test -e "$sql" &>par_sql1_$y.log &donewaitdate '+%H:%M:%S'

./duplicate_removal.sh

执行输出如下：

[mysql@hdp2~]$./duplicate_removal.sh14:27:3014:27:35

[mysql@hdp2~]$cat par_sql1_1.log | sed '/^$/d'mysql: [Warning] Using a password on the command line interface can be insecure.--------------call sp_unique(1)--------------Query OK, 124999 rows affected (4.87 sec)Bye[mysql@hdp2~]$cat par_sql1_2.log | sed '/^$/d'mysql: [Warning] Using a password on the command line interface can be insecure.--------------call sp_unique(2)--------------Query OK, 125000 rows affected (4.88 sec)Bye[mysql@hdp2~]$cat par_sql1_3.log | sed '/^$/d'mysql: [Warning] Using a password on the command line interface can be insecure.--------------call sp_unique(3)--------------Query OK, 125000 rows affected (4.91 sec)Bye[mysql@hdp2~]$cat par_sql1_4.log | sed '/^$/d'mysql: [Warning] Using a password on the command line interface can be insecure.--------------call sp_unique(4)--------------Query OK, 125001 rows affected (4.73 sec)Bye[mysql@hdp2~]$

（2）MySQL Schedule Event

•建立事件历史日志表

-- 用于查看事件执行时间等信息create table t_event_history (  dbname varchar(128) not null default '',  eventname varchar(128) not null default '',  starttime datetime(3) not null default '1000-01-01 00:00:00',  endtime datetime(3) default null,  issuccess int(11) default null,  duration int(11) default null,  errormessage varchar(512) default null,  randno int(11) default null);

delimiter //create event ev1 on schedule at current_timestamp + interval 1 hour on completion preserve disable do begin declare r_code char(5) default '00000';  declare r_msg text;  declare v_error integer;  declare v_starttime datetime default now(3);  declare v_randno integer default floor(rand()*100001);  insert into t_event_history (dbname,eventname,starttime,randno)  #作业名  values(database(),'ev1', v_starttime,v_randno);  begin  #异常处理段  declare continue handler for sqlexception  begin   set v_error = 1;   get diagnostics condition 1 r_code = returned_sqlstate , r_msg = message_text;  end;  #此处为实际调用的用户程序过程  call sp_unique(1);  end;  update t_event_history set endtime=now(3),issuccess=isnull(v_error),duration=timestampdiff(microsecond,starttime,now(3)), errormessage=concat('error=',r_code,', message=',r_msg),randno=null where starttime=v_starttime and randno=v_randno; end// create event ev2 on schedule at current_timestamp + interval 1 hour on completion preserve disable do begin declare r_code char(5) default '00000';  declare r_msg text;  declare v_error integer;  declare v_starttime datetime default now(3);  declare v_randno integer default floor(rand()*100001);  insert into t_event_history (dbname,eventname,starttime,randno)  #作业名  values(database(),'ev2', v_starttime,v_randno);  begin  #异常处理段  declare continue handler for sqlexception  begin   set v_error = 1;   get diagnostics condition 1 r_code = returned_sqlstate , r_msg = message_text;  end;  #此处为实际调用的用户程序过程  call sp_unique(2);  end;  update t_event_history set endtime=now(3),issuccess=isnull(v_error),duration=timestampdiff(microsecond,starttime,now(3)), errormessage=concat('error=',r_code,', message=',r_msg),randno=null where starttime=v_starttime and randno=v_randno; end// create event ev3 on schedule at current_timestamp + interval 1 hour on completion preserve disable do begin declare r_code char(5) default '00000';  declare r_msg text;  declare v_error integer;  declare v_starttime datetime default now(3);  declare v_randno integer default floor(rand()*100001);  insert into t_event_history (dbname,eventname,starttime,randno)  #作业名  values(database(),'ev3', v_starttime,v_randno);  begin  #异常处理段  declare continue handler for sqlexception  begin   set v_error = 1;   get diagnostics condition 1 r_code = returned_sqlstate , r_msg = message_text;  end;  #此处为实际调用的用户程序过程  call sp_unique(3);  end;  update t_event_history set endtime=now(3),issuccess=isnull(v_error),duration=timestampdiff(microsecond,starttime,now(3)), errormessage=concat('error=',r_code,', message=',r_msg),randno=null where starttime=v_starttime and randno=v_randno; end// create event ev4 on schedule at current_timestamp + interval 1 hour on completion preserve disable do begin declare r_code char(5) default '00000';  declare r_msg text;  declare v_error integer;  declare v_starttime datetime default now(3);  declare v_randno integer default floor(rand()*100001);  insert into t_event_history (dbname,eventname,starttime,randno)  #作业名  values(database(),'ev4', v_starttime,v_randno);  begin  #异常处理段  declare continue handler for sqlexception  begin   set v_error = 1;   get diagnostics condition 1 r_code = returned_sqlstate , r_msg = message_text;  end;  #此处为实际调用的用户程序过程  call sp_unique(4);  end;  update t_event_history set endtime=now(3),issuccess=isnull(v_error),duration=timestampdiff(microsecond,starttime,now(3)), errormessage=concat('error=',r_code,', message=',r_msg),randno=null where starttime=v_starttime and randno=v_randno; end//

•触发事件执行

mysql -vvv -u root -p123456 test -e "truncate t_target;alter event ev1 on schedule at current_timestamp enable;alter event ev2 on schedule at current_timestamp enable;alter event ev3 on schedule at current_timestamp enable;alter event ev4 on schedule at current_timestamp enable;"

[mysql@hdp2~]$mysql -vvv -u root -p123456 test -e "truncate t_target;alter event ev1 on schedule at current_timestamp enable;alter event ev2 on schedule at current_timestamp enable;alter event ev3 on schedule at current_timestamp enable;alter event ev4 on schedule at current_timestamp enable;"mysql: [Warning] Using a password on the command line interface can be insecure.--------------truncate t_target--------------Query OK, 0 rows affected (0.06 sec)--------------alter event ev1 on schedule at current_timestamp enable--------------Query OK, 0 rows affected (0.02 sec)--------------alter event ev2 on schedule at current_timestamp enable--------------Query OK, 0 rows affected (0.00 sec)--------------alter event ev3 on schedule at current_timestamp enable--------------Query OK, 0 rows affected (0.02 sec)--------------alter event ev4 on schedule at current_timestamp enable--------------Query OK, 0 rows affected (0.00 sec)Bye[mysql@hdp2~]$

mysql> select * from test.t_event_history;+--------+-----------+-------------------------+-------------------------+-----------+----------+--------------+--------+| dbname | eventname | starttime  | endtime   | issuccess | duration | errormessage | randno |+--------+-----------+-------------------------+-------------------------+-----------+----------+--------------+--------+| test | ev1 | 2019-07-31 14:38:04.000 | 2019-07-31 14:38:09.389 |  1 | 5389000 | NULL  | NULL || test | ev2 | 2019-07-31 14:38:04.000 | 2019-07-31 14:38:09.344 |  1 | 5344000 | NULL  | NULL || test | ev3 | 2019-07-31 14:38:05.000 | 2019-07-31 14:38:09.230 |  1 | 4230000 | NULL  | NULL || test | ev4 | 2019-07-31 14:38:05.000 | 2019-07-31 14:38:09.344 |  1 | 4344000 | NULL  | NULL |+--------+-----------+-------------------------+-------------------------+-----------+----------+--------------+--------+4 rows in set (0.00 sec)

总结