mysql 学习存储过程：一次插入 10W 条记录、卡死了怎么办-Toy模板网

这篇具有很好参考价值的文章主要介绍了mysql 学习存储过程：一次插入 10W 条记录、卡死了怎么办。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

存储过程简要规范：

所有的存储过程必须有前缀“proc_”，所有的系统存储过程都有前缀“sp_”。
输入参数以i_开头，输出参数以o_开头。

实验示例

mysql> use test_20230414;

mysql> CREATE TABLE person (
id int NOT NULL AUTO_INCREMENT,
name varchar(15) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=10001 ;

创建 2 个存储过程

使用事务

 delimiter $
 CREATE PROCEDURE proc_insert_person(IN i_total INT)
 BEGIN
 DECLARE i INT DEFAULT 1;
 START TRANSACTION;
 WHILE i<= i_total DO
 INSERT INTO person(NAME) VALUE (CONCAT("wzh",i));
 SET i=i+1;
 END WHILE;
 COMMIT;
 END$
 delimiter ;

不使用事务

 delimiter $
 CREATE PROCEDURE proc_insert_person_without_transaction(IN i_total INT)
 BEGIN
 DECLARE i INT DEFAULT 1;
 /* START TRANSACTION;*/
 WHILE i<=i_total DO
 INSERT INTO person(NAME) VALUE (CONCAT("wzh",i));
 SET i=i+1;
 END WHILE;
 /* COMMIT; */
 END$
 delimiter ;

查询存储过程

 mysql> SHOW PROCEDURE STATUS like 'proc_insert_person%'\G;
 
 mysql> SHOW PROCEDURE STATUS where db = 'test_20230414'\G;
 
 *************************** 1. row ***************************
                   Db: test_20230414
                 Name: proc_insert_person
                 Type: PROCEDURE
              Definer: root@%
             Modified: 2023-04-22 16:29:46
              Created: 2023-04-22 16:29:46
        Security_type: DEFINER
              Comment: 
 character_set_client: utf8mb4
 collation_connection: utf8mb4_0900_ai_ci
   Database Collation: utf8mb4_0900_ai_ci
 *************************** 2. row ***************************
                   Db: test_20230414
                 Name: proc_insert_person_without_transaction
                 Type: PROCEDURE
              Definer: root@%
             Modified: 2023-04-22 16:39:52
              Created: 2023-04-22 16:39:52
        Security_type: DEFINER
              Comment: 
 character_set_client: utf8mb4
 collation_connection: utf8mb4_0900_ai_ci
   Database Collation: utf8mb4_0900_ai_ci
 2 rows in set (0.00 sec)
 
 ERROR: 
 No query specified

先测试 1W 条

mysql> truncate person;

mysql> CALL proc_insert_person(10000);
Query OK, 0 rows affected (1.76 sec)

mysql> truncate person;
mysql> CALL proc_insert_person_without_transaction(10000);
Query OK, 1 row affected (9 min 25.57 sec)

不使用事务好慢！

再测试 10W 条

 mysql> CALL proc_insert_person(100000);
 Query OK, 0 rows affected (10.66 sec)
 
 mysql> CALL proc_insert_person_without_transaction(100000);

10W 条不带事务，停在那里不动，卡死了一样，怎么办？只好想办法中止他

另开一个Terminal 来中止

 mysql> show processlist;

想要筛选加不上： show processlist where User=‘root’;
改用 slect 查询

 mysql> select * from information_schema.processlist where user = 'root';
 +--------+------+-----------+---------------+---------+------+----------------------------+------------------------------------------------------------------+
 | ID     | USER | HOST      | DB            | COMMAND | TIME | STATE                      | INFO                                                             |
 +--------+------+-----------+---------------+---------+------+----------------------------+------------------------------------------------------------------+
 | 156362 | root | localhost | test_20230414 | Query   |    0 | waiting for handler commit | INSERT INTO person(NAME) VALUE (CONCAT("wzh",i))                 |
 | 155356 | root | localhost | NULL          | Query   |    0 | executing                  | select * from information_schema.processlist where user = 'root' |
 +--------+------+-----------+---------------+---------+------+----------------------------+------------------------------------------------------------------+
 2 rows in set (0.00 sec)

kill 掉哪个进程文章来源地址https://www.toymoban.com/news/detail-427970.html

 mysql> kill 156362;
 Query OK, 0 rows affected (0.00 sec)

到了这里，关于mysql 学习存储过程：一次插入 10W 条记录、卡死了怎么办的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

mysql 学习存储过程：一次插入 10W 条记录、卡死了怎么办

存储过程简要规范：

实验示例

创建 2 个存储过程

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏

支付宝扫一扫领取红包，优惠每天领

二维码1

二维码2