数据库设计（5）
在MySQL数据库中，有时为了建立测试表格，需要向一张表中插入很多条数据，可以使用下面的存储过程实现：
首先建立一个测试表：
mysql& create table t_1 (id serial,name char(5)) ;
Query OK, 0 rows affected (0.26 sec)
然后建立一个存储过程：
delimiter $$
SET AUTOCOMMIT = 0$$
create &procedure test01()&
declare v_cnt decimal (10) &default 0 ;
& & & & & insert into t_1 values (null,'aaaaa'),(null,'aaaaa'),(null,'aaaaa'),(null,'aaaaa'),(null,'aaaaa'),(null,'aaaaa'),(null,'aaaaa'),(null,'aaaaa'),(null,'aaaaa'),(null,'aaaaa');
& & & & & & & & &
& & & & & & & & & & set v_cnt = v_cnt+10 ;
& & & & & & & & & & & & & &if &v_cnt = 10000
& & & & & & & & & & & & &
调用存储过程向表格中插入数据：
mysql& select count(*) from t_1;
+----------+
| count(*) |
+----------+
| & & & &0 |
+----------+
1 row in set (0.00 sec)
mysql& call test01;
Query OK, 0 rows affected (21.25 sec)
mysql& select count(*) from t_1;
+----------+
| count(*) |
+----------+
| & &10000 |
+----------+
1 row in set (0.01 sec)
再次执行存储过程，会插入更多数据：
mysql& call test01;
Query OK, 0 rows affected (21.70 sec)
mysql& select count(*) from t_1;
+----------+
| count(*) |
+----------+
| & &20000 |
+----------+
1 row in set (0.01 sec)
注：上面的测试表结构，插入数据，表格行数，都可以根据需要进行修改，以便生成需要的数据测试环境。
对于MySQL的存储过程，有以下几个命令进行操作：
select name from mysql.proc where db='test';
select routine_name from information_schema.routines where routine_schema='test';
show procedure status where db='test';
show create procedure test.test01;
删除存储过程的命令：
drop procedure test01;
查询数据库中的存储过程：
方法一：（直接查询）
select `specific_name` from mysql.proc where db = 'your_db_name' and `type` = 'procedure'
方法二：（查看数据库里所有存储过程+内容）
方法三：（查看当前数据库里存储过程列表）
select specific_name from mysql.
方法四：(查看某一个存储过程的具体内容)
select body from mysql.proc where specific_name = 'your_proc_name';
查看存储过程或函数的创建代码 ：
show create procedure your_proc_
show create function your_func_
删除存储过程：
drop procedure your_proc_
但如果测试表格中存在主键，使用上面的存储过程可能出现问题，可以使用下面的存储过程：
建立测试表：
CREATE TABLE t1(id1 INT,a1 INT,b1 INT,PRIMARY KEY(id1));
CREATE TABLE t2(id2 INT,a2 INT,b2 INT);
CREATE TABLE t3(id3 INT NUIQUE,a3 INT,b3 INT);
CREATE TABLE t4(id4 INT,a4 INT,b4 INT);
CREATE TABLE t5(id5 INT UNIQUE,a5 INT,b5 INT);
建立插入数据的存储过程：
建立 while ···· end while存储过程（执行前检查执行条件）：
DELIMITER // &
CREATE PROCEDURE proc4() &
set var=0; &
while var&6 do &
insert into t1 values(var,var,var); &
set var=var+1; &
DELIMITER ;&
建立 repeat......end repeat 存储过程（执行后检查执行条件）：
DELIMITER // &
CREATE PROCEDURE proc01 () &
set v=0; &
insert into t1 values(v,v,v); &
set v=v+1; &
until v&=100&
DELIMITER ;&
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：130203次
积分：2538
积分：2538
排名：第11493名
原创：103篇
转载：117篇
(1)(1)(1)(1)(1)(1)(2)(2)(3)(5)(3)(10)(5)(2)(6)(11)(9)(1)(6)(43)(101)(9)查看:9250|回复：15
初级工程师
declare& &@i& &int&&
&&set& &@i=1
&&while& &@i&
& && &INSERT INTO table
& && && &&&()
& &&&VALUES
& && && &&&()
& && &set& &@i=@i+1&&
这样插入太慢了。请大神传授一个快速点的方法
1000万太大了
create table test(a bigint)
declare @b int
insert into test select 1
while (@b*2&=)
insert into test
select @b+a from test
set @b=@b*2
insert into test
select @b+a from test where @b+a&=
select * from test
初级工程师
引用:原帖由 lzsl922 于
15:16 发表
1000万太大了
create table test(a bigint)
declare @b int
insert into test select 1
while (@b*2 我自己写了一个双循环，插入了一些数据，后来 又用with本表数据翻倍插入。已经插入了一千万条，后来发现数据库一直在变大，吓尿了。现在终于停了。待会再试了。先删掉数据看看数据库大小的变化
初级工程师
引用:原帖由 lzsl922 于
15:16 发表
1000万太大了
create table test(a bigint)
declare @b int
insert into test select 1
while (@b*2 这段代码你执行过吗？我想问一下大概的执行时间是多少啊？
初级工程师
引用:原帖由 lzsl922 于
15:16 发表
1000万太大了
create table test(a bigint)
declare @b int
insert into test select 1
while (@b*2 亲啊，你这个太慢了
引用:原帖由 sql一级菜鸟 于
17:49 发表
亲啊，你这个太慢了 100万的话 2分多 1000万 用什么方法都慢吧...
初级工程师
引用:原帖由 lzsl922 于
09:39 发表
100万的话 2分多 1000万 用什么方法都慢吧... 所以才求高手，学习。还有你添加的也太随意了。要正规点，5个必备的字符串。不然后面的测试无法走下去
本帖最后由 sql一级菜鸟 于
10:19 编辑
引用:原帖由 sql一级菜鸟 于
10:09 发表
所以才求高手，学习。还有你添加的也太随意了。要正规点，5个必备的字符串。不然后面的测试无法走下去 你到底要添加什么数据？我这个是从1到1000万的数列
初级工程师
引用:原帖由 lzsl922 于
10:31 发表
你到底要添加什么数据？我这个是从1到1000万的数列 类似正规数据方便操作，优化。有点头痛啊，项目跑了没有一年有个表已经1700万数据了。不过你那个循环，逻辑确实写得好
直接BULK插入啊，不要一条一条循环插入。
Microsoft SQL Server MVP
初级工程师
引用:原帖由 lzf328 于
11:40 发表
直接BULK插入啊，不要一条一条循环插入。 表示这个BULK没用过，在网上看过，不过日语水平较低都没有看懂。可以举个例子吗。谢版主
在第一行加个begin tran
一般人我不告诉他
SQL Server MCITP, MVP, MCT, DBA
初级工程师
引用:原帖由 seusoftware 于
11:05 发表
在第一行加个begin tran
一般人我不告诉他 在这里我隆重的感谢好 基友
初级工程师
我测试了下，用cte 的话，插入100 万条26秒完成
1000 万条的话 342 秒完成，如果可以把数据库改成简单模式，插入时候不写log 的话应该会更快
create table test(a bigint)
with cte(ID) AS
(SELECT 1 UNION ALL
SELECT ID +1FROM cte where id& )
insert into test with(tablock)
select ID FROM cte
option (maxrecursion 0)
引用:原帖由 lantshirt 于
17:49 发表
我测试了下，用cte 的话，插入100 万条26秒完成
1000 万条的话 342 秒完成，如果可以把数据库改成简单模式，插入时候不写log 的话应该会更快
create table test(a bigint)
with cte(ID) AS
(SELECT 1 UNION ALL
SELECT ... 简单模式是log自动重绕，不是不写，相当于oracle的非归档模式
SQL Server MCITP, MVP, MCT, DBA
Copyright&
本论坛言论纯属发布者个人意见，不代表51CTO网站立场！如有疑义，请与管理员联系：mysql中迅速插入百万条测试数据的方法
字体：[ ] 类型：转载 时间：
最近想到创建一个大量数据的测试环境，于是找了一下怎么插入100W条数据，我用的是20个字段
对比一下，首先是用 mysql 的存储过程弄的:
代码如下: mysql&delimiter $ mysql&SET AUTOCOMMIT = 0$$ mysql& create procedure test() begin declare i decimal (10) default 0 ; dd:loop INSERT INTO `million` (`categ_id`, `categ_fid`, `SortPath`, `address`, `p_identifier`, `pro_specification`, `name`, `add_date`, `picture_url`, `thumb_url`, `is_display_front`, `create_html_time`, `hit`, `buy_sum`, `athor`, `templete _style`, `is_hot`, `is_new`, `is_best`) VALUES (268, 2, '0,262,268,', 0, '2342', '423423', '123123', ' 09:55:43', 'upload/product/32_53211.jpg', 'upload/product/thumb_32_53211.jpg', 1, 0, 0, 0, 'admin', '0', 0, 0, 0);
set i = i+1; if i= 1000000
$ mysql& mysql&
结果 mysql& Query OK, 0 rows affected (58 min 30.83 sec) 非常耗时。 于是我又找了一个方法 先用PHP代码生成数据，再导入：
代码如下: &?php $t=mktime(); set_time_limit(1000); $myFile="e:/insert.sql"; $fhandler=fopen($myFile,'wb'); if($fhandler){ $sql="268\t2\t'0,262,268,'\t0\t '2342'\t'423423'\t'123123'\t''\t' 09:55:43'\t'upload/product/32_53211.jpg'\t'upload/product/thumb_32_53211.jpg'\tNULL\tNULL\t38\t'件'\t''\t123\t123\t0"; $i=0; while($i&1000000)//1,000,000 { $i++; fwrite($fhandler,$sql."\r\n"); } echo"写入成功,耗时：",mktime()-$t; }
然后再导入
代码如下: LOAD DATA local INFILE 'e:/insert.sql' INTO TABLE tenmillion(`categ_id`, `categ_fid`, `SortPath`, `address`, `p_identifier`, `pro_specification`, `name`, `description`, `add_date`, `picture_url`, `thumb_url`, `shop_url`, `shop_thumb_url`, `brand_id`, `unit`, `square_meters_unit`, `market_price`, `true_price`, `square_meters_price`);
注意字段不再以逗号分割，以\t分割，条记录以\r\n分割。结果我插入10次数据，100W平均只要1分钟搞定。 第二种方式mysql中间省略了很多中间步骤，导致插入速度远胜于第一种，具体的没有研究。 快速生成mysql上百万条测试数据由于测试需要，原表中只有1万条数据，现在随机复制插入记录，快速达到100万条。 itemid是主键。 运行几次下面代码。随机取1000条插入， insert into downitems (chid,catid,softid,....) SELECT chid,catid,softid... FROM `downitems` WHERE itemid &= (SELECT floor(RAND() * (SELECT MAX(itemid) FROM `downitems`))) ORDER BY itemid LIMIT 1000; 然后可以修改1000的数字了。改为5000或者1万。很快可以达到100万的数据量了。
您可能感兴趣的文章:
大家感兴趣的内容
12345678910
最近更新的内容
常用在线小工具2010年 总版技术专家分年内排行榜第二
2009年 总版技术专家分年内排行榜第三
2015年6月 其他数据库开发大版内专家分月排行榜第二2015年5月 其他数据库开发大版内专家分月排行榜第二2013年6月 其他数据库开发大版内专家分月排行榜第二2013年3月 其他数据库开发大版内专家分月排行榜第二2012年12月 其他数据库开发大版内专家分月排行榜第二2012年10月 其他数据库开发大版内专家分月排行榜第二2012年9月 其他数据库开发大版内专家分月排行榜第二2012年7月 其他数据库开发大版内专家分月排行榜第二2012年6月 其他数据库开发大版内专家分月排行榜第二2011年11月 其他数据库开发大版内专家分月排行榜第二2011年10月 其他数据库开发大版内专家分月排行榜第二2011年9月 其他数据库开发大版内专家分月排行榜第二2011年6月 其他数据库开发大版内专家分月排行榜第二2011年5月 其他数据库开发大版内专家分月排行榜第二2011年4月 其他数据库开发大版内专家分月排行榜第二2011年3月 其他数据库开发大版内专家分月排行榜第二2011年2月 其他数据库开发大版内专家分月排行榜第二2010年12月 其他数据库开发大版内专家分月排行榜第二2010年11月 其他数据库开发大版内专家分月排行榜第二2010年9月 其他数据库开发大版内专家分月排行榜第二2010年7月 其他数据库开发大版内专家分月排行榜第二2010年4月 其他数据库开发大版内专家分月排行榜第二2010年3月 其他数据库开发大版内专家分月排行榜第二2010年1月 其他数据库开发大版内专家分月排行榜第二2009年12月 其他数据库开发大版内专家分月排行榜第二2008年6月 其他数据库开发大版内专家分月排行榜第二2008年5月 其他数据库开发大版内专家分月排行榜第二2008年4月 其他数据库开发大版内专家分月排行榜第二2007年5月 其他数据库开发大版内专家分月排行榜第二2007年1月 其他数据库开发大版内专家分月排行榜第二2006年10月 其他数据库开发大版内专家分月排行榜第二2006年8月 其他数据库开发大版内专家分月排行榜第二2006年6月 其他数据库开发大版内专家分月排行榜第二2006年4月 其他数据库开发大版内专家分月排行榜第二2005年9月 其他数据库开发大版内专家分月排行榜第二
2013年12月 其他数据库开发大版内专家分月排行榜第三2013年5月 其他数据库开发大版内专家分月排行榜第三2013年4月 其他数据库开发大版内专家分月排行榜第三2013年2月 其他数据库开发大版内专家分月排行榜第三2013年1月 其他数据库开发大版内专家分月排行榜第三2012年5月 其他数据库开发大版内专家分月排行榜第三2012年4月 其他数据库开发大版内专家分月排行榜第三2012年3月 其他数据库开发大版内专家分月排行榜第三2012年2月 其他数据库开发大版内专家分月排行榜第三2011年8月 其他数据库开发大版内专家分月排行榜第三2011年7月 其他数据库开发大版内专家分月排行榜第三2010年10月 其他数据库开发大版内专家分月排行榜第三2010年5月 其他数据库开发大版内专家分月排行榜第三2010年2月 其他数据库开发大版内专家分月排行榜第三2009年11月 其他数据库开发大版内专家分月排行榜第三2009年10月 其他数据库开发大版内专家分月排行榜第三2009年5月 其他数据库开发大版内专家分月排行榜第三2009年4月 其他数据库开发大版内专家分月排行榜第三2008年12月 其他数据库开发大版内专家分月排行榜第三2008年11月 其他数据库开发大版内专家分月排行榜第三2008年10月 其他数据库开发大版内专家分月排行榜第三2008年9月 其他数据库开发大版内专家分月排行榜第三2007年9月 其他数据库开发大版内专家分月排行榜第三2007年8月 其他数据库开发大版内专家分月排行榜第三2007年4月 其他数据库开发大版内专家分月排行榜第三2007年3月 其他数据库开发大版内专家分月排行榜第三2007年2月 其他数据库开发大版内专家分月排行榜第三2006年7月 其他数据库开发大版内专家分月排行榜第三2006年2月 其他数据库开发大版内专家分月排行榜第三2006年1月 其他数据库开发大版内专家分月排行榜第三2005年11月 其他数据库开发大版内专家分月排行榜第三2005年10月 其他数据库开发大版内专家分月排行榜第三
2008年10月 PHP大版内专家分月排行榜第二2008年9月 PHP大版内专家分月排行榜第二2008年4月 PHP大版内专家分月排行榜第二2007年12月 PHP大版内专家分月排行榜第二
本帖子已过去太久远了，不再提供回复功能。