数据库随机插入数据

一、数字、字符串类型

1.创表（插入数据脚本）

CREATE TABLE t11(

id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,

dname VARCHAR(20) NOT NULL DEFAULT '',

loc VARCHAR(13) NOT NULL DEFAULT ''

)ENGINE=INNODB DEFAULT CHARSET=GBK;

2.添加随机数据

DELIMITER $$

CREATE PROCEDURE insert_t11(IN START INT(10),IN max_num INT(10))

BEGIN

DECLARE i INT DEFAULT 0;

REPEAT

SET i = i+1;

INSERT INTO t11(deptno,dname,loc) VALUES ((START+i),rand_string(10),rand_string(8));

UNTIL i = max_num

END REPEAT;

COMMIT;

END $$

3.插入t11:

先把；分号为介绍符(结束符)切换回来：

DELIMITER ;  

CALL insert_t11(100,10);

4.查阅：

SELECT COUNT(*) FROM t11;

二、只有数字类型

1.创表（插入数据脚本）

CREATE TABLE t22(

id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0

)ENGINE=INNODB DEFAULT CHARSET=GBK;

2.添加随机数据

DELIMITER $$

CREATE PROCEDURE insert_t22(IN START INT(10),IN max_num INT(10))

BEGIN

DECLARE i INT DEFAULT 0;

REPEAT

SET i = i+1;

INSERT INTO t22(deptno) VALUES (START+i);

UNTIL i = max_num

END REPEAT;

COMMIT;

END $$

3.插入t11:

先把；分号为介绍符(结束符)切换回来：

DELIMITER ;  

CALL insert_t22(100,10);

4.查阅：

SELECT COUNT(*) FROM t22;