独特的约束和为 MySQL 和 SQLite 插入或更新

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 查询中输入所有(新)值并输入我没有的所有列的列名t 指定了一个值，以确保在替换现有行时它将保持其值不变而不是删除它们.

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 插入或更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！