mysql+inser+select_mysql insert select的使用

mysql使用 INSERT...SELECT 可以认为表复制，您可以快速地从一个或多个表中向一个表中插入多个行。

INSERT ... SELECT语法

首先来看下INSERT ... SELECT语法：INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,...)]

SELECT ...

[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

如何使用使用呢？

首先2建表个表作为演示用：-- 用户表

CREATE TABLE `user` (

`id` int unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(50) DEFAULT NULL,

`type` int DEFAULT '0',

PRIMARY KEY (`id`),

KEY `name` (`name`),

KEY `type` (`type`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 学生表

CREATE TABLE `student` (

`id` int unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(50) DEFAULT NULL,

`type` int DEFAULT '0',

PRIMARY KEY (`id`),

KEY `name` (`name`),

KEY `type` (`type`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

现在向用户表user插入几条数据INSERT INTO `user`(`name`,`type`) values('张三',1);

INSERT INTO `user`(`name`,`type`) values('李四',1);

INSERT INTO `user`(`name`,`type`) values('王五',2);

现在使用 insert select 复制用户表user数据到学生表student-- 完整复制

INSERT INTO `student` SELECT * FROM `user`;

-- 使用条件(主键自增情况)

INSERT INTO `student`(`name`,`type`) SELECT `name`,`type` FROM `user` WHERE `type`=1;

-- 指定某列(主键自增情况)

INSERT INTO `student`(`name`,`type`) SELECT `name`,`type` FROM `user`;

复制后mysql> select * from student;

+----+------+------+

| id | name | type |

+----+------+------+

|  1 | 张三 |    1 |

|  2 | 李四 |    1 |

|  3 | 王五 |    2 |

+----+------+------+

3 rows in set (0.00 sec)

为了确保二进制日志可以被用于再次创建原表，MySQL不允许在INSERT...SELECT运行期间同时进行插入操作。

目前，您不能在向一个表插入的同时，又在一个子查询中从同一个表中选择。

怎么使用ON DUPLICATE KEY UPDATE

重复键值更新

先去除用户表的自增，例如有数据像：DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (

`id` int unsigned NOT NULL,

`name` varchar(50) DEFAULT NULL,

`type` int DEFAULT '0',

KEY `name` (`name`),

KEY `type` (`type`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `user`(`id`,`name`,`type`) values(1,'张三',1);

INSERT INTO `user`(`id`,`name`,`type`) values(2,'李四',1);

INSERT INTO `user`(`id`,`name`,`type`) values(2,'王五',2);

注意看数据id列为非主键非唯一索引列，有2个从重复值mysql> select * from user;

+----+------+------+

| id | name | type |

+----+------+------+

|  1 | 张三 |    1 |

|  2 | 李四 |    1 |

|  2 | 王五 |    2 |

+----+------+------+

3 rows in set (0.00 sec)

再使用 insert select 复制用户表user数据到学生表student

如果在插入student过程中id键值有重复，则更新指定为的值，如：INSERT INTO `student`(`id`,`name`,`type`) SELECT `id`,`name`,`type` FROM `user` u ON DUPLICATE KEY UPDATE `name`=u.`name`;

复制后：mysql> select * from student;

+----+------+------+

| id | name | type |

+----+------+------+

|  1 | 张三 |    1 |

|  2 | 王五 |    1 |

+----+------+------+

2 rows in set (0.00 sec)

发现李四跟王五重复键值，则在`name`=u.`name`条件下李四设置为王五。

在ON DUPLICATE KEY UPDATE的值部分中，只要您不使用SELECT部分中的GROUP BY，您就可以引用在其它表中的列。有一个副作用是，您必须使值部分中的非唯一列的名称符合要求。

原创文章，转载请注明出处：https://www.weizhixi.com/article/72.html