存储过程简要规范：

1. 所有的存储过程必须有前缀“proc_”，所有的系统存储过程都有前缀“sp_”。
2. 输入参数以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 个存储过程

1. 使用事务

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

2. 不使用事务

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

3. 查询存储过程

    mysql> SHOW PROCEDURE STATUS like 'proc_insert_person%'\G;mysql> SHOW PROCEDURE STATUS where db = 'test_20230414'\G;*************************** 1. row ***************************Db: test_20230414Name: proc_insert_personType: PROCEDUREDefiner: root@%Modified: 2023-04-22 16:29:46Created: 2023-04-22 16:29:46Security_type: DEFINERComment: character_set_client: utf8mb4collation_connection: utf8mb4_0900_ai_ciDatabase Collation: utf8mb4_0900_ai_ci*************************** 2. row ***************************Db: test_20230414Name: proc_insert_person_without_transactionType: PROCEDUREDefiner: root@%Modified: 2023-04-22 16:39:52Created: 2023-04-22 16:39:52Security_type: DEFINERComment: character_set_client: utf8mb4collation_connection: utf8mb4_0900_ai_ciDatabase Collation: utf8mb4_0900_ai_ci2 rows in set (0.00 sec)ERROR: No query specified
   

4. 先测试 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)

   不使用事务好慢！

5. 再测试 10W 条

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

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

6. 另开一个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 掉哪个进程

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