[MySQL] 【原创】利用MySQL触发器高性能造数据

CREATE TABLE `tb1` ( 
  `id` varchar(255) NOT NULL, 
  `log_date` date DEFAULT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED

CREATE DEFINER = 'root'@'localhost' 
PROCEDURE db_myisam.sp_generate_tb1_data(IN cur_1    INT, 
                     IN f_input    INT, 
                     IN f_commit_num INT 
                     -- Stands for which month's date. 
) 
BEGIN 
  DECLARE t_count   INT; 
  DECLARE v_log_date DATE; 
  DECLARE c_date  DATE DEFAULT '2012-07-01'; 
 
  SET t_count = cur_1 + (f_input - 1); 
  SET @@autocommit = 0; 
 
  WHILE cur_1 <= t_count 
  DO 
  IF mod(cur_1, f_commit_num) = 1 THEN 
    START TRANSACTION; 
  END IF; 
  SET v_log_date = date_add(c_date, INTERVAL ceil(rand() * 3) * (ceil(rand() * 15)) DAY); 
  INSERT INTO tb1 (id, log_date) VALUES (cur_1, v_log_date); 
  IF mod(cur_1, f_commit_num) = 0 THEN 
    COMMIT; 
  END IF; 
  SET cur_1 = cur_1 + 1; 
  END WHILE; 
  COMMIT; 
END

mysql> call sp_generate_tb1_data(1,10000000,200); 
Query OK, 0 rows affected (8 min 20.00 sec)

mysql> select count(*) from tb1; 
+----------+ 
| count(*) | 
+----------+ 
| 10000000 | 
+----------+ 
1 row in set (34.35 sec)

create table tb2 like tb1; 
create table tb3 like tb1;

CREATE  
  DEFINER = 'root'@'localhost' 
TRIGGER db_myisam.ti_tb3_after 
  AFTER INSERT 
  ON db_myisam.tb3 
  FOR EACH ROW 
BEGIN 
  DECLARE v_cur_1  INT DEFAULT 1; 
  DECLARE v_log_date DATE; 
  DECLARE c_date   DATE DEFAULT '2012-07-01'; 
 
  WHILE v_cur_1 <= 10000000 
  DO 
  SET v_log_date = date_add(c_date, INTERVAL ceil(rand() * 3) * (ceil(rand() * 15)) DAY); 
  INSERT INTO tb2 (id, log_date) VALUES (v_cur_1, v_log_date); 
  SET v_cur_1 = v_cur_1 + 1; 
  END WHILE; 
END

mysql> insert into tb3 values (2,current_date()); 
Query OK, 1 row affected (5 min 14.07 sec)