如果存在则更新记录，否则插入

I have a requirement to update a record if it exists else insert it. I know this has been asked a few times but I think my problem is a bit trickier.

如果存在,我需要更新记录,否则插入它。我知道这已被问过几次,但我认为我的问题有点棘手。

I have a table (tbl_settings):

我有一个表(tbl_settings):

setting_id   |    token   |   setting_value   |   setting_for
1                 1           on                  background
2                 1           off                 vibrate
3                 2           on                  vibrate

etc

This is for a mobile app which I will save the users settings in a remote DB so the can retrieve later if needed.

这是一个移动应用程序,我将用户设置保存在远程数据库中,以便以后可以在需要时检索。

Setting_id is unique and auto increment.

Setting_id是唯一且自动增量。

Token is going to be the devices token id (user specific)

令牌将成为设备令牌ID(特定于用户)

Setting_value is the value (on/off) for that particular setting

Setting_value是该特定设置的值(开/关)

Setting_for is the name of the actual setting

Setting_for是实际设置的名称

Now there are 4 setting options available to a user which can be written to the DB at individual times (when the user changes the setting on/off) or together if its better.

现在有4个可供用户使用的设置选项,可以在个别时间(当用户更改设置开/关时)或者如果更好的话一起写入DB。

So if the user changes the vibrate setting, I need a query to check if vibrate and token exist update that particular record otherwise insert with token, setting_value and setting_for.

因此,如果用户更改振动设置,我需要一个查询来检查振动和令牌是否存在更新特定记录,否则插入令牌,setting_value和setting_for。

Hopefully its possible in a single query. If not I will just use multiple queries, one to check if it exists and the other to insert or update.

希望它可以在一个查询中。如果不是,我将只使用多个查询,一个用于检查是否存在,另一个用于插入或更新。

1 个解决方案

#1

MySQL offers two options for this:

MySQL为此提供了两个选项:

replace into
insert ... on duplicate update

插入...重复更新

Do note that in both cases you'll have to drop your setting_id (which seems pointless anyway) and use a composite primary key.

请注意,在这两种情况下,您都必须删除setting_id(无论如何这似乎毫无意义)并使用复合主键。

#1