独特的约束和为 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 查询中输入所有(新)值并输入我没有的所有列的列名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 插入或更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！