mysql构造100万假数据

建表

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

结束！！

---

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