Description:
#session1: create table and insert data
CREATE TABLE `t_temp` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
key (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#########################################################################################################
delimiter //
DROP PROCEDURE IF EXISTS proc_batch_insert;
CREATE PROCEDURE proc_batch_insert()
BEGIN
DECLARE pre_name BIGINT;
DECLARE ageVal INT;
DECLARE i INT;
SET pre_name=139;
SET ageVal=100;
SET i=1;
WHILE i <= 1000000 DO
INSERT INTO t_temp(`name`,age,create_time,update_time) VALUES(CONCAT(pre_name,'@qq.com'),(ageVal+1)*rand()%30,NOW(),NOW());
SET pre_name=pre_name+100;
SET i=i+1;
END WHILE;
END //
delimiter ;
call proc_batch_insert();
##session2
mysql> show processlist;
+----+-----------------+-----------+-----------+---------+------+----------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+-----------+---------+------+----------------------------+------------------------------------------------------------------------------------------------------+
| 7 | event_scheduler | localhost | NULL | Daemon | 534 | Waiting on empty queue | NULL |
| 10 | root | localhost | NULL | Query | 0 | init | show processlist |
| 15 | root | localhost | employees | Query | 0 | waiting for handler commit | INSERT INTO t_temp(`name`,age,create_time,update_time) VALUES(CONCAT(pre_name,'@qq.com'),(ageVal+1)* |
+----+-----------------+-----------+-----------+---------+------+----------------------------+------------------------------------------------------------------------------------------------------+
#connection id 15 is insert data ,but i explain for connection not have result
mysql> EXPLAIN FOR CONNECTION 15;
Query OK, 0 rows affected (0.00 sec)
I think this feature not works well。
How to repeat:
Description have repeat 。thanks
Description: #session1: create table and insert data CREATE TABLE `t_temp` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `age` tinyint(4) DEFAULT NULL, `create_time` datetime DEFAULT NULL, `update_time` datetime DEFAULT NULL, PRIMARY KEY (`id`), key (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ######################################################################################################### delimiter // DROP PROCEDURE IF EXISTS proc_batch_insert; CREATE PROCEDURE proc_batch_insert() BEGIN DECLARE pre_name BIGINT; DECLARE ageVal INT; DECLARE i INT; SET pre_name=139; SET ageVal=100; SET i=1; WHILE i <= 1000000 DO INSERT INTO t_temp(`name`,age,create_time,update_time) VALUES(CONCAT(pre_name,'@qq.com'),(ageVal+1)*rand()%30,NOW(),NOW()); SET pre_name=pre_name+100; SET i=i+1; END WHILE; END // delimiter ; call proc_batch_insert(); ##session2 mysql> show processlist; +----+-----------------+-----------+-----------+---------+------+----------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+-----------+---------+------+----------------------------+------------------------------------------------------------------------------------------------------+ | 7 | event_scheduler | localhost | NULL | Daemon | 534 | Waiting on empty queue | NULL | | 10 | root | localhost | NULL | Query | 0 | init | show processlist | | 15 | root | localhost | employees | Query | 0 | waiting for handler commit | INSERT INTO t_temp(`name`,age,create_time,update_time) VALUES(CONCAT(pre_name,'@qq.com'),(ageVal+1)* | +----+-----------------+-----------+-----------+---------+------+----------------------------+------------------------------------------------------------------------------------------------------+ #connection id 15 is insert data ,but i explain for connection not have result mysql> EXPLAIN FOR CONNECTION 15; Query OK, 0 rows affected (0.00 sec) I think this feature not works well。 How to repeat: Description have repeat 。thanks