插入更新,就是无则插入,有则更新
(一)目前以我的了解,mysql有两种方法实现。
这两种方式都是通过唯一索引来判断有无的,所以插入的语句中一定要包含有唯一索引的字段
1.replace into(不推荐)
-- id为主键
REPLACE INTO aa_mytest(id,i_name,i_value)
VALUES(1,'ass','123')
replace into 在遇到唯一索引时,会执行delete和insert,导致除了name,value之外的字段给改成null了,所以不推荐使用。
2.insert into on duplicate key update(推荐)
-- id为主键
INSERT INTO aa_mytest(id,name,value)
VALUES(1,'ass','123')
ON DUPLICATE KEY UPDATE
name = VALUES(name),
value = VALUES(value)
insert into on duplicate key update在遇到唯一索引时,会执行update, 更新name和value,不会影响到其他字段。
(二)批量插入更新
REPLACE INTO aa_mytest(id,name,value)
VALUES(1,'ass','123'),(2,'asssd','1232'),(3,'asszz','123333')
INSERT INTO aa_mytest(id,name,value)
VALUES(1,'ass','123'),(2,'asssd','1232'),(3,'asszz','123333')
ON DUPLICATE KEY UPDATE
name = VALUES(name),
value = VALUES(value)
(三)高级用法
下列sql是我在工作中真实用到的,结合了批量插入更新和把一个表数据同步到另一个表。
FLIGHT_ID为唯一索引,foc_flight新增6个字段,不改变其他数据的情况下把这6个字段补上
INSERT INTO foc_flight (
FLIGHT_ID,
OPEN_DOOR_TIME,
BLOCK_OUT,
ACARS_OUT,
ACARS_INN,
OUTT,
INN,
FLI_DATE,
FLI_NUMBER,
APT_ID_TAKE_OFF,
APT_ID_LANDING,
STD,
MATCH_STATUS,
id
) SELECT
FLIGHT_ID,OPEN_DOOR_TIME,BLOCK_OUT,ACARS_OUT,ACARS_INN,OUTT,INN,
NOW(),'test','1234','1234',NOW(),0,MD5(UUID())
FROM t2001
WHERE FLIGHT_ID IN (
SELECT
ff.flight_id
FROM
der_certain_flight dcf
LEFT JOIN foc_flight ff
ON dcf.ID = ff.qar_flight_id
)
ON DUPLICATE KEY UPDATE
OPEN_DOOR_TIME = VALUES(OPEN_DOOR_TIME),
ACARS_OUT = VALUES(ACARS_OUT),
ACARS_INN = VALUES(ACARS_INN),
OUTT = VALUES(OUTT),
INN = VALUES(INN),
BLOCK_OUT = VALUES(BLOCK_OUT);