mysql 如果数据不存在,则插入新数据,否则更新的实现方法

时间:2022-12-31 07:25:47

CREATE TABLE `table_test` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `my_key` int(11) NOT NULL DEFAULT '0',

  `value` varchar(21) NOT NULL DEFAULT '',

  `count` int(11) NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`),

  UNIQUE KEY `my_key` (`my_key`),

  UNIQUE KEY `value` (`value`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

 

对于这样一个表.当要记录my_key=1,value='a',count=0时,一般的处理流程是:先select,查看是否存在my_key=1的数据:如果有,则使用update进行更新;如果没有,则使用insert进行插入。

(1) 上述操作流程可以归纳为: key存在则更新,不存在则插入,采用以下sql语句可以实现:

insert into .... on duplicate key update

注意:key需要是唯一索引。

对于需要根据原记录进行操作的。如表中count字段用于计数,当没有记录时,插入的value为0;当有记录时,value需要更新为value+1,sql语句为:

insert into table_test set my_key=1,value='a',value=1 on duplicate key update value=value+1; 


(2) 不过在大并发量的数据操作时,可能有时一个有主键的select查询耗时较长,如果对旧数据不关心,可以采用先disable即删除原来my_key=1的数据,再插入新的数据。使用replace一个语句可以完成上述操纵流程的功能,其语法与insert差不多。可以写为replace into table_test set ikey=1,value='a',icount=0;则表中有my_key=1时,先删除旧数据,然后插入新数据.否则直接插入数据,sql语句如下:

replace into table_test set my_key=1,value='a',count=0;

需要注意的是:如果表中有多个唯一索引,例如:my_key和value字段都是unique key,replace会把所有与其唯一索引值相同的数据项删除,再插入新记录。如表中有两个记录,replace into table_test set my_key=5,value='c',count=0;会将两条数据同时删除再插入;

mysql> select * from table_test;

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

| id | my_key | value | count |

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

| 10 |      5 | a     |     0 |

| 11 |      3 | c     |     0 |

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

2 rows in set (0.04 sec)


mysql> replace into table_test set my_key=5,value='c',count=0;

Query OK, 3 rows affected (0.04 sec)


mysql> select * from table_test;

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

| id | my_key | value | count |

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

| 12 |      5 | c     |     0 |

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

1 row in set (0.04 sec)

(3)对比 replace into 和 insert into on duplicate key:

replace into table_test set my_key=1,value='a',count=0;会把表中的两条记录都删除,然后插入新记录;

insert into table_test set my_key=1,value='a',count=0 on duplicate key update count=count+1则只更新一条记录.其效果相当于

if (exist){

update table_testset count=count+1where my_key=1orvalue='a'limit1;

}else{

  into table_test set my_key=1,value='a',count=0

}