MySQL 关于插入insert 相关的操作

需求分析：

MySQL中对于插入语句的操作情况有如下：

(1) 单纯的执行插入的语句（语法：insert into …）

(2) 如果插入的数据导致存在某个重复的字段（注意：该字段必须是unique），则执行更新操作（语法：insert into … onduplicate key update… 或者 replace ）

(3) 如果插入的数据导致重复的某个字段，则本次操作被忽略（语法：insert ignore into…）

举个栗子：

创建测试表 `tb_test_insert`：

create table `tb_test_insert`(`id` int unsigned not null auto_increment comment '主键id',`name` varchar(255) not null default '' comment '姓名',`app_id` int unsigned not null comment '应用id', `click_num` int unsigned not null default '0' comment '点击数',`date` varchar(255) not null comment '日期',`ctime` int unsigned not null comment '创建时间',primary key(id),key idx_name(`name`),unique key unique_date_appid(`date`,`app_id`))engine=innodb default charset=utf8 comment '测试插入相关操作表';

说明：

上述的表格中设置id为主键，给字段`name`设置了索引，`date`和`app_id`组成一个联合唯一索引。

插入数据：

1、一般用法

insert into tb_test_insert (name, app_id, click_num, date, ctime) values ('tim', 1, 100, '20170829', 1503998341);

如果重复执行上述插入操作，提示错误信息如下：

[SQL] insert into tb_test_insert (name, app_id, click_num, date, ctime) values ('tim', 1, 100, '20170829', 1503998341);[Err] 1062 - Duplicate entry '20170829-1' for key 'unique_date_appid'

2、通过insert into 忽略错误

这时在客户端会报异常，因此需要我们去捕获该异常，然后，在某些场景下，比如：秒杀场景，我们对于用户重复秒杀的操作，只需判断用户的操作是否改变了数据，此时，通过加入ignore，具体语句如下：

insert ignore into tb_test_insert (name, app_id, click_num, date, ctime) values ('tim', 1, 100, '20170829', 1503998341);

此时,提示：

受影响的行: 0时间: 0.026s

只需要判断insert 影响的行数，就可以知道用户是否是首次操作，还是执行了重复操作。

3、当联合唯一索引重复时，更新部分字段

但是，有时候我们希望但存在重复的`date`、`app_id`组成的联合值时，只需更新某个字段即可，具体的语句如下：

insert into tb_test_insert (name, app_id, click_num, date, ctime) values ('tim', 1, 100, '20170829', 1503998341) on duplicate key update click_num=click_num+1;

通过添加on duplicate key 能够用于计数，这个功能非常实用。

4、当联合唯一索引重复时，另一种方式更新字段

上面只是针对部分字段进行更新，如果我们想对所有字段进行更新，`date`、`app_id`仍然必须是联合唯一索引，如果判断存在此联合唯一索引的值，则进行更新，否则执行插入操作，此时可以用replace into，具体语句如下：

replace into tb_test_insert (name, app_id, click_num, date, ctime) values ('tim', 1, 1001, '20170829', 1503998341);

总结：

对于insert ignore into、insert into … onduplicate key update …、replace into … 更改的字段中一定要包含唯一索引。