独特的约束条件为MySQL和SQLite插入或更新 [英] Unique constraints & insert or update for both MySQL and SQLite

问题描述

我正在寻找一种方法来将一组列定义为唯一，然后在表中插入新条目，或者如果列不是唯一的则更新行.我已经进行了一些研究并找到了解决方法，但是找不到与MySQL和SQLite都兼容的任何东西.

I am looking for a way to define a set of columns as unique and then insert a new entry into the table or update the row if the columns aren't unique. I have done some research and found ways to do it, but I couldn't find anything that is compatible with both MySQL and SQLite.

说我有下表:

CREATE TABLE `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `uuid` VARCHAR ( 64 ) NOT NULL,
  `name` VARCHAR ( 32 ) NOT NULL,
  `date` BIGINT NULL,
  PRIMARY KEY ( id )
);

我希望uuid和date是唯一的，以便一个uuid或一个date可以有多个条目，但uuid和date的一种组合不能有多个条目.我最初想要做的是将主键设置为这些键:

I want uuid and date to be unique so that there can be multiple entries for one uuid or one date, but not for one combination of uuid and date. What I initially wanted to do is set the primary key to those:

PRIMARY KEY ( uuid, date )

但是，由于某些原因，在执行此操作时，我将无法为date使用空值.
我还发现了一些有关约束的信息，但不确定是否可行:

However, for some reason I won't be able to use null values for date when doing this.
I have also found something about a constraint, but I am not sure if this works:

CONSTRAINT user UNIQUE ( `uuid`, `date` )

现在，我想在该表中插入新行，但是如果已经存在具有相同uuid和date的行，请更新现有行.我发现了几种方法，但它们要么不执行我想要的操作，要么与MySQL和SQLite都不兼容:

Now I want to insert a new row into this table, but update the existing row if a row with the same uuid and date already exists. I have found a few ways but they are either not doing what I want or not compatible with both MySQL and SQLite:

• INSERT INTO ... ON DUPLICATE KEY不适用于SQLite
• REPLACE INTO将删除我未指定的所有内容，而不是进行更新

• INSERT INTO ... ON DUPLICATE KEY doesn't work with SQLite
• REPLACE INTO will delete anything I don't specify instead of updating

我已经进行了一段时间的研究，但是找不到适合我的解决方案.任何帮助表示赞赏.

I have been doing research for quite a while but I couldn't find a solution that worked for me. Any help appreciated.

推荐答案

我已经搜索了几个小时，并使用MySQL和SQLite进行了一些测试，我认为我找到了一个可行的解决方案.
为了使uuid和date的组合唯一，我在表中添加了唯一约束.要插入新行或更新"现有行，我正在使用REPLACE INTO ... SELECT.

I have been googling for a few hours and did some testing with both MySQL and SQLite and I think I found a working solution.
To make the combination of uuid and date unique, I have added a unique constraint to the table. To insert a new row or 'update' an existing row, I am using REPLACE INTO ... SELECT.

要创建表:

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT NOT NULL AUTO_INCREMENT, // use INTEGER NOT NULL for SQLite
  `uuid` VARCHAR ( 64 ) NOT NULL,
  `name` VARCHAR ( 32 ) NOT NULL,
  `date` BIGINT NULL,
  CONSTRAINT `uuid_date` UNIQUE ( `uuid`, `date` ),
  PRIMARY KEY ( `id` )
);

CONSTRAINT将确保uuid和date的组合始终是唯一的.
对于插入数据，我使用REPLACE INTO ... SELECT，在其中我在SELECT查询中输入所有(新)值，并为所有未指定值的列输入列名，以确保其值保持不变.而不是在替换现有行时将其删除.

The CONSTRAINT will make sure the combination of uuid and date is always unique.
For inserting data, I use REPLACE INTO ... SELECT, where I enter all (new) values in the SELECT query and enter the column names for all columns I haven't specified a value for, to ensure it will keep their values intact rather than deleting them when the existing row is replaced.

REPLACE INTO `users`
SELECT `id`, `uuid`, ?, `date`
FROM `users` WHERE `uuid` = ? AND `date` = ?;

当然，因为在这种情况下，使用普通的REPLACE INTO时不会丢失任何列，因此我也可以使用:

Of course, because in this case there are no columns that can be lost when using a normal REPLACE INTO, so I could also use:

REPLACE INTO `users` ( `uuid`, `name`, `date` ) VALUES ( ?, ?, ? );

但是，当我的表中的列更多时，REPLACE INTO ... SELECT查询可能会很有用，并且当不选择它们时，实际上还有一些列可能会丢失.

However, the REPLACE INTO ... SELECT query can be useful when I have a table with more columns and there are actually columns that can be lost when not selecting them.

这篇关于独特的约束条件为MySQL和SQLite插入或更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！