10W条记录 20个字段 ------------------ 这个要看你的每条记录的大小select * from tabel;和 select col1 from table会随着每条记录的大小差异,每条记录越大显示越慢
DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`test1`$$CREATE PROCEDURE `test`.`test1`()declare v_cnt decimal (1) default 0 ; dd:loop insert into test.table1(t1,t2) values (0,1); commit; set v_cnt = v_cnt+1 ; if v_cnt = 1000000 then leave dd; end if; end loop dd ; BEGIN END$$DELIMITER ;这个怎么就错啦!!! Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare v_cnt decimal (1) default 0 ; dd:loop insert into test.tabl' at line 2 (0 ms taken)
注意红色部分: DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`test1`$$CREATE PROCEDURE `test`.`test1`() BEGIN declare v_cnt decimal (1) default 0 ; dd:loop insert into test.table1(t1,t2) values (0,1); commit; set v_cnt = v_cnt+1 ; if v_cnt = 1000000 then leave dd; end if; end loop dd ; END$$DELIMITER ;
DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`test1`$$CREATE PROCEDURE `test`.`test1`() BEGINDECLARE v_cnt DECIMAL (1) DEFAULT 0 ; dd:LOOP INSERT INTO test.table1(t1,t2) VALUES (0,1); COMMIT; SET v_cnt=v_cnt+1; IF p1 < 10 THEN ITERATE DD; END IF; LEAVE DD; END LOOP dd ; END$$DELIMITER ;
Error Code : 1307 Failed to CREATE PROCEDURE test1 (0 ms taken)崩溃
OR CREATE PROCEDURE `test`.`test1`() BEGINDECLARE v_cnt DECIMAL (1) DEFAULT 0 ; Dd:LOOP INSERT INTO test.table1(t1,t2) VALUES (0,1); COMMIT; SET v_cnt = v_cnt+1 ; IF v_cnt = 1000000 THEN LEAVE dd; END IF; END LOOP dd ; END$$DELIMITER ;
DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`test1`$$CREATE PROCEDURE `test`.`test1`() BEGINDECLARE v_cnt DECIMAL (1) DEFAULT 0 ; Dd:LOOP INSERT INTO test.table1(t1,t2) VALUES (0,1); COMMIT; SET v_cnt = v_cnt+1 ; IF v_cnt = 1000000 THEN LEAVE dd; END IF; END LOOP dd ; END$$DELIMITER ;
我是在MYSQL5。.1。32下测试通过的
http://topic.csdn.net/u/20090626/16/65f043cf-b9d9-4707-b660-9857461177f4.html?14339你可以直接使用下面语句,要求mysql 5.0 以上。create table t_06 ( id int not null primary key, c1 varchar(30), i2 int ) engine = myisam; delimiter //CREATE PROCEDURE prepareData_t_06 () BEGIN DECLARE i INT DEFAULT 1; WHILE i < 500000 DO insert into t_06 values (i,concat('AA',i),i); SET i = i + 1; END WHILE; END; //delimiter ;CALL prepareData_t_06();select count(*) from t_06;
10W条记录 20个字段 select* from tabel;和 select col1 from table;效率差得大不大?ms级还是s级? 100w呢?---------------------------------------- 这个还用测试吗? 那么明显的东西啦 跟每条记录的大小、网络等速度有很大关系 假如每条记录1K,而col1是100字节10W条记录,你算吧
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.14 sec)mysql> select * from t1 INTO OUTFILE 'aaa';
Query OK, 100000 rows affected (0.08 sec)mysql> select id from t1 INTO OUTFILE 'aaa1';
Query OK, 100000 rows affected (0.09 sec)mysql> select id from t1 INTO OUTFILE 'aaa2';
Query OK, 100000 rows affected (0.11 sec)mysql> select id from t1 INTO OUTFILE 'aa3';
Query OK, 100000 rows affected (0.09 sec)mysql>
你可以写个存储过程,循环 N 次.具体的你可以参考一下帮助文档中的例子。
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
------------------
这个要看你的每条记录的大小select * from tabel;和 select col1 from table会随着每条记录的大小差异,每条记录越大显示越慢
DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`test1`$$CREATE PROCEDURE `test`.`test1`()declare v_cnt decimal (1) default 0 ;
dd:loop
insert into test.table1(t1,t2) values (0,1);
commit;
set v_cnt = v_cnt+1 ;
if v_cnt = 1000000 then leave dd;
end if;
end loop dd ; BEGIN END$$DELIMITER ;这个怎么就错啦!!!
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare v_cnt decimal (1) default 0 ;
dd:loop
insert into test.tabl' at line 2
(0 ms taken)
DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`test1`$$CREATE PROCEDURE `test`.`test1`()
BEGIN
declare v_cnt decimal (1) default 0 ;
dd:loop
insert into test.table1(t1,t2) values (0,1);
commit;
set v_cnt = v_cnt+1 ;
if v_cnt = 1000000 then leave dd;
end if;
end loop dd ; END$$DELIMITER ;
BEGINDECLARE v_cnt DECIMAL (1) DEFAULT 0 ;
dd:LOOP
INSERT INTO test.table1(t1,t2) VALUES (0,1);
COMMIT;
SET v_cnt=v_cnt+1;
IF p1 < 10 THEN ITERATE DD; END IF;
LEAVE DD;
END LOOP dd ;
END$$DELIMITER ;
Failed to CREATE PROCEDURE test1
(0 ms taken)崩溃
CREATE PROCEDURE `test`.`test1`()
BEGINDECLARE v_cnt DECIMAL (1) DEFAULT 0 ;
Dd:LOOP
INSERT INTO test.table1(t1,t2) VALUES (0,1);
COMMIT;
SET v_cnt = v_cnt+1 ;
IF v_cnt = 1000000 THEN LEAVE dd;
END IF;
END LOOP dd ;
END$$DELIMITER ;
BEGINDECLARE v_cnt DECIMAL (1) DEFAULT 0 ;
Dd:LOOP
INSERT INTO test.table1(t1,t2) VALUES (0,1);
COMMIT;
SET v_cnt = v_cnt+1 ;
IF v_cnt = 1000000 THEN LEAVE dd;
END IF;
END LOOP dd ;
END$$DELIMITER ;
id int not null primary key,
c1 varchar(30),
i2 int
) engine = myisam; delimiter //CREATE PROCEDURE prepareData_t_06 ()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i < 500000 DO
insert into t_06 values (i,concat('AA',i),i);
SET i = i + 1;
END WHILE;
END;
//delimiter ;CALL prepareData_t_06();select count(*) from t_06;
select* from tabel;和 select col1 from table;效率差得大不大?ms级还是s级?
100w呢?----------------------------------------
这个还用测试吗?
那么明显的东西啦
跟每条记录的大小、网络等速度有很大关系
假如每条记录1K,而col1是100字节10W条记录,你算吧