像插入一样更新 [英] Update like insert

问题描述

是否可以像插入一样执行更新?

UPDATE `table` SET `value` ('N','N','N','N','Y','Y','Y','N', 'N') WHERE `my_id` = '1'

问题是我现在不插入要插入的值的数量.可以是5或10.

The problem is that the number of values ​​to be inserted i dont now. It can be a 5 or 10.

推荐答案

replace就像insert一样，它只是检查是否存在重复键，如果是则删除行，然后插入新键，否则插入

replace is just like insert, it just checks if there is duplicate key and if it is it deletes the row, and inserts the new one, otherwise it just inserts

例如，如果存在(Name，Type)的唯一索引并且键入以下命令，则可以执行此操作

you can do this if there is for example unique index of (Name,Type) and if you type the following command

REPLACE INTO table1 (Name,Type,InitialValue,FinalValue) VALUES ('A',3,50,90 )

并且已经存在名称='A'和类型= 3的行，它将被替换

and there already exists a row with Name = 'A' and Type = 3 it will be replaced

CREATE UNIQUE INDEX idx_name_type ON table1(Name,Type)

一个简短的说明-替换总是先删除然后再插入，所以在重负载下使用它永远不是一个好主意，因为删除和插入时都需要排他锁

a quick note - REPLACE always DELETES and then INSERTs, so it is never a very good idea to use it in heavy load because it needs exclusive lock when it deletes, and then when it inserts

某些数据库引擎具有

插入...在重复键更新上...

INSERT ... ON DUPLICATE KEY UPDATE ...

这篇关于像插入一样更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！