1:只要已经存在表结构的
第一种方式:
CREATE TABLE T1 SELECT * FROM mysql.user WHERE 1=2;
第二种方式:
CREATE TABLE T2 LIKE mysql.user;
mysql> CREATE TABLE t1
-> LIKE mysql.user;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE t2
-> SELECT * FROM mysql.user
-> WHERE 1=2;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
2:表结构和数据都要的
CREATE TABLE T1
SELECT * FROM mysql.user
3:需要100W的数据量
DROP TABLE IF EXISTS testdata;
CREATE TABLE testdata
(id int,name varchar(10));
DROP PROCEDURE IF EXISTS pro_testdata;
DELIMITER &&
CREATE PROCEDURE pro_testdata(IN inr int)
BEGIN
DECLARE i INT DEFAULT 0;
START TRANSACTION;
WHILE i<inr DO
INSERT INTO testdata(id,name) values(i,'gechong');
SET i=i+1;
END WHILE;
COMMIT;
END &&
DELIMITER ;
call pro_testdata(1000000);
mysql> SELECT COUNT(1) FROM testdata;
+----------+
| COUNT(1) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> CALL pro_testdata(10000);
Query OK, 0 rows affected (0.24 sec)
mysql> SELECT COUNT(1) FROM testdata;
+----------+
| COUNT(1) |
+----------+
| 10000 |
+----------+
1 row in set (0.00 sec)
如果表结构不满足要求做简单修改即可。
参数类型:in、out、inout
in表示:调用者传给存储过程
out表示:存储过程返回给调用者的
inout:MySQL 存储过程 inout 参数跟 out 类似,都可以从存储过程内部传值给调用者。不同的是:调用者还可以通过 inout 参数传递值给存储过程。
DROP PROCEDURE IF EXISTS proinout;
DELIMITER &&
CREATE PROCEDURE proinout (in id1 int,out id2 int)
BEGIN
SELECT id INTO id2 FROM t
WHERE id = id1;
END &&
DELIMITER ;
调用
call proinout(100,@id_num);
SELECT @id_num;