数据的高级操作——蠕虫复制

蠕虫复制：从已有的数据表中获取数据，然后将数据进行新增操作，数据成倍（以指数形式）的增加。

根据已有表创建新表，即复制表结构，其基本语法为：

• create table + 表名 + like + [数据库名.]表名;

mysql> create table my_friends(
    ->  name varchar(20)
    -> )charset utf8;
Query OK, 0 rows affected

mysql> desc my_friends;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set

mysql> create table my_copy like my_friends;
Query OK, 0 rows affected

mysql> desc my_copy;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set

如上图所示，表my_copy和表my_friends的表结构完成相同。

蠕虫复制的步骤为：先查出数据，然后将查出的数据新增一遍。

• 基本语法：insert into + 表名 + [()] + select + 字段列表/* + from + 表名;

mysql> select * from my_friends;
+-----------+
| name      |
+-----------+
| xiaohei   |
| xiaohong  |
| xiaohuang |
| xiaoming  |
+-----------+
4 rows in set

mysql> insert into my_copy select * from my_friends;
Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from my_copy;
+-----------+
| name      |
+-----------+
| xiaohei   |
| xiaohong  |
| xiaohuang |
| xiaoming  |
+-----------+
4 rows in set

如上图所示，my_friends表中的数据已完全复制到my_copy中, 接下来，咱们再执行如下 SQL 语句，测试蠕虫复制的效果：

mysql> insert into my_copy select * from my_copy;
Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into my_copy select * from my_copy;
Query OK, 8 rows affected
Records: 8  Duplicates: 0  Warnings: 0

mysql> insert into my_copy select * from my_copy;
Query OK, 16 rows affected
Records: 16  Duplicates: 0  Warnings: 0

mysql> select * from my_copy;
+-----------+
| name      |
+-----------+
| xiaohei   |
| xiaohong  |
| xiaohuang |
| xiaoming  |
| xiaohei   |
| xiaohong  |
| xiaohuang |
| xiaoming  |
| xiaohei   |
| xiaohong  |
| xiaohuang |
| xiaoming  |
| xiaohei   |
| xiaohong  |
| xiaohuang |
| xiaoming  |
| xiaohei   |
| xiaohong  |
| xiaohuang |
| xiaoming  |
| xiaohei   |
| xiaohong  |
| xiaohuang |
| xiaoming  |
| xiaohei   |
| xiaohong  |
| xiaohuang |
| xiaoming  |
| xiaohei   |
| xiaohong  |
| xiaohuang |
| xiaoming  |
+-----------+
32 rows in set

如上图所示，通过观察每次执行 SQL 语句后影响的列数，分别为4、8和16等，咱们会发现蠕虫复制的效果呈（指数）爆炸性增长。

蠕虫复制的意义：

1. 从已有的数据表中拷贝数据到新的数据表；
2. 可以迅速的让表中的数据膨胀到一定的数量级，多用于测试表的压力及效率。