(目录)
1、语法
INSERT INTO SELECT 语句
- 从一个表中复制数据并将其插入到另一个表中
- 要求源表和目标表中的数据类型匹配
语法
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;
2、准备
准备数据表和数据
-- 建表
CREATE TABLE `tb_user` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
`name` varchar(20) NOT NULL COMMENT '姓名',
`age` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
PRIMARY KEY (`id`),
) ENGINE=InnoDB COMMENT='用户表';
-- 插入一条数据
insert into tb_user (name, age) values ('Tom', 20);
-- 查看数据
mysql> select * from tb_user;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | Tom | 20 |
+----+------+-----+
1 row in set (0.01 sec)
3、执行
执行insert into select 语句
insert into tb_user (name, age)
select name, age
from tb_user;
执行结果
-- 第一次执行
mysql> select * from tb_user;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | Tom | 20 |
| 2 | Tom | 20 |
+----+------+-----+
2 rows in set (0.00 sec)
-- 第二次执行
mysql> select * from tb_user;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | Tom | 20 |
| 2 | Tom | 20 |
| 3 | Tom | 20 |
| 4 | Tom | 20 |
+----+------+-----+
4 rows in set (0.00 sec)
每次执行都会将整个表的数据复制一份,2倍增长
2N:1 2 4 8 16 32