mysql 存储过程一实例

1、创建测试表

CREATE TABLE `test_user` (

`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`info` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

2、创建存储过程 

 

CREATE PROCEDURE insertTestUser(IN num INT)
BEGIN
	DECLARE I INT;
	DECLARE str_json VARCHAR(100);
	DECLARE str VARCHAR(100);
	SET I = 0; 
  WHILE I<num DO
		SET str_json = JSON_OBJECT('age',ROUND(RAND()*100),'sex',ROUND(RAND()),'nick name',concat( '小萌', i ));
		INSERT INTO test_user ( `name`, `info` )VALUES( concat( '小萌', i ), str_json );
		SET I = I+1;
	END WHILE;
END

　　

3、调存储过程 插入100个记录

CALL insertTestUser(100);