mysql 蠕虫复制和去重

基本介绍

有时，为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据

蠕虫复制

复制一个空表tempemp，表结构完全一样

mysql> create table tempemp like emp;
Query OK, 0 rows affected (0.68 sec)

mysql> desc tempemp;
+----------+-----------------------+------+-----+---------+-------+
| Field    | Type                  | Null | Key | Default | Extra |
+----------+-----------------------+------+-----+---------+-------+
| empno    | mediumint(8) unsigned | NO   |     | 0       |       |
| ename    | varchar(20)           | NO   |     |         |       |
| job      | varchar(9)            | NO   |     |         |       |
| mgr      | mediumint(8) unsigned | YES  |     | NULL    |       |
| hiredate | date                  | NO   |     | NULL    |       |
| sal      | decimal(7,2)          | NO   |     | NULL    |       |
| comm     | decimal(7,2)          | YES  |     | NULL    |       |
| deptno   | mediumint(8) unsigned | NO   |     | 0       |       |
+----------+-----------------------+------+-----+---------+-------+
8 rows in set (0.03 sec)

将emp数据导入tempemp

//导入全部字段
insert into tempemp select * from emp;
//导入部分字段（需要指定字段名, 而且要求没有给值的字段有默认值）
insert into tempemp (empno,ename,job,hiredate,sal) select empno,ename,job,hiredate,sal from emp;

mysql> select * from tempemp;
+-------+--------+-----------+------+------------+---------+------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+--------+-----------+------+------------+---------+------+--------+
|  7369 | smith  | clerk     | NULL | 1980-12-17 |  800.00 | NULL |      0 |
|  7499 | allen  | salesman  | NULL | 1981-02-20 | 1600.00 | NULL |      0 |
|  7521 | ward   | salesman  | NULL | 1981-02-22 | 1250.00 | NULL |      0 |
|  7566 | jones  | manager   | NULL | 1981-04-02 | 2975.00 | NULL |      0 |
|  7654 | martin | salesman  | NULL | 1981-09-28 | 1250.00 | NULL |      0 |
|  7698 | blake  | manager   | NULL | 1981-05-01 | 2850.00 | NULL |      0 |
|  7782 | clark  | manager   | NULL | 1981-06-09 | 2450.00 | NULL |      0 |
|  7788 | scott  | analyst   | NULL | 1987-04-19 | 3000.00 | NULL |      0 |
|  7839 | king   | president | NULL | 1981-11-17 | 5000.00 | NULL |      0 |
|  7844 | iurner | salesman  | NULL | 1981-09-08 | 1500.00 | NULL |      0 |
|  7900 | james  | clerk     | NULL | 1981-12-03 |  950.00 | NULL |      0 |
|  7902 | ford   | analyst   | NULL | 1981-12-03 | 3000.00 | NULL |      0 |
|  7934 | miller | clerk     | NULL | 1982-01-23 | 1300.00 | NULL |      0 |
+-------+--------+-----------+------+------------+---------+------+--------+
13 rows in set (0.00 sec)

tempemp表进行自我复制

//数据会成倍增长
mysql> insert into tempemp select * from tempemp;
Query OK, 13 rows affected (0.46 sec)
Records: 13  Duplicates: 0  Warnings: 0

删除某个表中的重复记录

复制一个空表tempemp，表结构完全一样

mysql> create table tempemp like emp;
Query OK, 0 rows affected (0.68 sec)

mysql> desc tempemp;
+----------+-----------------------+------+-----+---------+-------+
| Field    | Type                  | Null | Key | Default | Extra |
+----------+-----------------------+------+-----+---------+-------+
| empno    | mediumint(8) unsigned | NO   |     | 0       |       |
| ename    | varchar(20)           | NO   |     |         |       |
| job      | varchar(9)            | NO   |     |         |       |
| mgr      | mediumint(8) unsigned | YES  |     | NULL    |       |
| hiredate | date                  | NO   |     | NULL    |       |
| sal      | decimal(7,2)          | NO   |     | NULL    |       |
| comm     | decimal(7,2)          | YES  |     | NULL    |       |
| deptno   | mediumint(8) unsigned | NO   |     | 0       |       |
+----------+-----------------------+------+-----+---------+-------+
8 rows in set (0.03 sec)

对emp表进行 distinct 去重，并把数据导入 tempemp

mysql> insert into tempemp select distinct * from emp;
Query OK, 13 rows affected (0.46 sec)
Records: 13  Duplicates: 0  Warnings: 0

删除 emp 表

mysql> drop table emp;
Query OK, 0 rows affected (0.06 sec)

将 tempemp 表名改成 emp

mysql> alter table tempemp rename to emp;
Query OK, 0 rows affected (0.06 sec)