sql语句批量导入数据库数据

 方法一 字符串随机生成批量导入表中

创建t_admin表

CREATE TABLE `t_admin` (
	 `userId` int(11) NOT NULL AUTO_INCREMENT,
  `userName` varchar(55) DEFAULT NULL,
  `userPw` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`userId`
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

创建 函数rand_string

CREATE 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 1;
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

 创建存储过程

CREATE  PROCEDURE `add_t_admin`(IN n int)
BEGIN  
  DECLARE i INT DEFAULT 1;
    WHILE (i <= n ) DO
      INSERT into t_admin_memory (userName,userPw ) VALUEs (rand_string(5),rand_string(10) );
            set i=i+1;
    END WHILE;
END

 CALL 语句调用 CREATE PROCEDURE定义的存储过程。

//导入1000个数据

CALL add_t_admin (1000)

//可能会用到的删除 查询操作

DROP TABLE t_admin;
DROP PROCEDURE add_t_admin;
DROP FUNCTION rand_string;

SELECT * from t_admin;

方法二 具有一定规范性生成批量数据导入表

删除或创建表

DROP TABLE IF EXISTS `member`;
CREATE TABLE `member` (
                             `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
                             `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '书籍名称',
                             `pwd` varchar(255) CHARACTER SET `utf8` COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '书籍编号',
                             `typeId` int(11) COMMENT 'typeId',
                             `balance` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '单价',
                             `regrate` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '归还时间',
                             `tel` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '联系方式',
                             `idnumber` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '身份证号',
                              PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 308 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '图书管理' ROW_FORMAT = Compact;

创建存储过程 call语句调用

//学号后三位数据大小

//格式为shuaige_变量，汉字格式可以用xxx代替

CREATE PROCEDURE Insertmember()
BEGIN
    DECLARE i INT;
    SET i=1;
    WHILE(i<=right("201907020103", 3)) DO
            insert into member values(i,CONCAT('shuaige_',i,"name"),CONCAT('shuaige_',i,"pwd"),i,CONCAT('shuaige_',i,"balance"),'2022-05-08',CONCAT('shuaige_',i,"tel"),CONCAT('shuaige_',i,"idnumber"));
            SET i=i+1;
        END WHILE;
END;
call Insertmember();

小结：不论哪种方法，希望读者可以看懂，并且根据自己的数据表对代码进行修改。