Insert into table_name (key1,key2,key3)VALUEs(? ,? ,?) ON DUPLICATE KEY UPDATE key1 = VALUES(value1),key2=VALUES(value2),updatetime = CURRENT_TIMESTAMP;

Insert insert inert into table_name(key1,key2,key3)VALUES(? ,? ,?)

ON DUPLICATE KEY UPDATE KEY1 =VALUES(value1); SQL > create table UNIQUE;

For example, set id_only to a unique field

CREATE TABLE `get_query_shop` ( `id` int(11) NOT NULL AUTO_INCREMENT, 'shopId' varchar(100) DEFAULT NULL COMMENT 'shopId ',' cid1Name 'varchar(100) DEFAULT NULL COMMENT' shopId ', Cid2Name 'varchar(1000) DEFAULT NULL COMMENT ',' id_only 'varchar(100) DEFAULT NULL COMMENT' ', 'orderTime' varchar(100) DEFAULT NULL COMMENT 'orderTime ', 'createtime' datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Createtime ', Updatetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updatetime ', PRIMARY KEY (' id'), UNIQUE KEY `id_only` (`id_only`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8Copy the code

ON DUPLICATE KEY UPDATE (ON DUPLICATE KEY UPDATE, orderTime, cid1Name)

insert into get_query_shop (shopId,cid1Name,cid2Name,id_only,orderTime)VALUEs
("10010","1","1-2","a","20210202") ON DUPLICATE KEY UPDATE orderTime = 
VALUES("20210202"),cid1Name=VALUES("1-2"),updatetime = CURRENT_TIMESTAMP;
Copy the code

Note that with this method, the ID field of the data will not be contiguous, and direct inster inserts will fail because there are unique fields and duplicate data will not be inserted

If you forget to set the table during table building or set the table incorrectly, you can modify and add the table in the future

Add a unique field: ALTER TABLE get_query_shop ADD unique(' shopId ') ALTER TABLE get_query_shop ADD unique(' shopId ') Alter table get_query_pdd drop index 'id_only';Copy the code