mysql构造100万假数据

Myth丶恋晨 2024-03-25 22:20 46阅读 0赞

### 建表 ###

CREATE TABLE `user_text`  (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `user_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      `age` int(3) ,
      `sex` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

### 创建数据脚本 ###

这里测试100条，有需要可以改数据

DELIMITER ;;
    DROP PROCEDURE IF EXISTS batch_insert_log;
    CREATE PROCEDURE batch_insert_log()
    BEGIN
      DECLARE i INT DEFAULT 1;
      DECLARE userId INT DEFAULT 10;
     set @execSql = 'INSERT INTO `user_text`(`user_id`, `name`, `age`, `sex`) VALUES';
     set @execData = '';
      WHILE i<=100 DO
       set @attr = "'男'";
      set @execData = concat(@execData, "(", userId + i, ", '用户', '18'", ",", @attr,")");
      if i % 10 = 0
      then
         set @stmtSql = concat(@execSql, @execData,";");
        prepare stmt from @stmtSql;
        execute stmt;
        DEALLOCATE prepare stmt;
        commit;
        set @execData = "";
       else
         set @execData = concat(@execData, ",");
       end if;
      SET i=i+1;
      END WHILE;
    
    END;;
    DELIMITER ;
    
    -- 调用方法执行
    CALL batch_insert_log();

提示：我这里每10条提交一次，一次性提交全部可以参考别人的方法：[https://blog.csdn.net/u011983531/article/details/67639678][https_blog.csdn.net_u011983531_article_details_67639678]

结束！！

--------------------

天行健，君子以自强不息；地势坤，君子以厚德载物。

[https_blog.csdn.net_u011983531_article_details_67639678]: https://blog.csdn.net/u011983531/article/details/67639678