mysql 创建存储过程 创建100w测试数据表

表

CREATE TABLE `s_post_comments` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '数据ID',
  `user_id` int(10) NOT NULL COMMENT '用户ID',
  `passive_id` int(10) DEFAULT '0' COMMENT '被评者用户ID',
  `post_id` int(10) NOT NULL COMMENT '帖子ID',
  `post_user_id` int(10) NOT NULL COMMENT '帖子用户ID',
  `pid` int(11) NOT NULL DEFAULT '0' COMMENT '评论ID',
  `head_id` int(11) DEFAULT '0' COMMENT '初级评论ID',
  `content` varchar(500) CHARACTER SET utf8 DEFAULT '' COMMENT '评论内容',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态:0=关闭,1=开启',
  `ip` varchar(50) CHARACTER SET utf8 DEFAULT '' COMMENT 'IP',
  `is_read` tinyint(1) DEFAULT '0' COMMENT '是否已读:0=否,1=是',
  `createtime` int(10) DEFAULT NULL COMMENT '创建时间',
  `updatetime` int(10) DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='帖子评论'

存储过程

DELIMITER $$

USE `api4_tuanduiyun`$$

DROP PROCEDURE IF EXISTS `add_vote_memory`$$

CREATE DEFINER=`api4_tuanduiyun`@`%` PROCEDURE `add_vote_memory`(IN n INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  
  WHILE
    (i <= n) DO
    INSERT INTO s_post_comments (
      user_id,
      passive_id,
      post_id,
      head_id,
      content,
      createtime,
      updatetime
    )
    VALUES
      (
        4,
        2,
        14,
        266,
        '非常棒',
        UNIX_TIMESTAMP(NOW()),
        UNIX_TIMESTAMP(NOW())
      );
    
    SET i = i + 1;
    
  END WHILE;
  

	END$$

DELIMITER ;

DELIMITER $$

USE `api4_tuanduiyun`$$

DROP FUNCTION IF EXISTS `rand_string`$$

CREATE DEFINER=`api4_tuanduiyun`@`%` FUNCTION `rand_string`(n INT) RETURNS VARCHAR(255) CHARSET latin1
BEGIN
  DECLARE chars_str VARCHAR (100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
  
  DECLARE return_str VARCHAR (255) DEFAULT '';
  
  DECLARE i INT DEFAULT 0;
  
  WHILE
    i < n DO SET return_str = CONCAT(
      return_str,
      SUBSTRING(chars_str, FLOOR(1 + RAND() * 62), 1)
    );
    
    SET i = i + 1;
    
  END WHILE;
  
  RETURN return_str;

    END$$

DELIMITER ;