java - MySQL 和 SQLite 的唯一约束和插入或更新

Question

我正在寻找一种方法来将一组列定义为唯一，然后在表中插入一个新条目或在列不唯一时更新行。我做了一些研究并找到了实现它的方法，但我找不到任何与 MySQL 和 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 .我最初想做的是将主键设置为:

PRIMARY KEY ( uuid, date )

但是，出于某种原因，我无法为 date 使用空值这样做的时候。
我还发现了一些关于约束的信息，但我不确定这是否有效:

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

现在我想在此表中插入一个新行，但如果有相同的行 uuid 则更新现有行和 date已经存在。我已经找到了一些方法，但它们要么没有按照我的意愿行事，要么与 MySQL 和 SQLite 不兼容:

• INSERT INTO ... ON DUPLICATE KEY不适用于 SQLite
• REPLACE INTO将删除我未指定的任何内容而不是更新

我已经研究了很长时间，但找不到适合我的解决方案。任何帮助表示赞赏。

Answer 1

SQLite解决方案(同理mysql)

您可以简单地添加一个 UNIQUE 索引(至少对于 SQLite 来说是这样)这样您就可以:-

DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
  `id` INTEGER, //<<<<<<<<<< See notes below
  `uuid` VARCHAR ( 64 ) NOT NULL,
  `name` VARCHAR ( 32 ) NOT NULL,
  `date` BIGINT NULL,
  PRIMARY KEY ( `id` )
);
CREATE UNIQUE INDEX IF NOT EXISTS uuid_date ON `users` (`uuid`,`date`); //<<<<<<<<<<

• 注意 AUTO_INCREMENT 导致 SQLite 失败，因为它不是关键字，SQlite 中正确的关键字是 AUTOINCREMENT。但是，它被省略了，因为它可能不是必需的，因为如果没有为该列提供值，则 INTEGER PRIMARY KEY(或通过指定 PRIMARY KEY (id) 隐式)将导致自动生成 uniqiue id插入时。

• 对于自动生成的 ID，SQLite 要求使用 INTEGER，而不是 INT。隐含了 NOT NULL 和 UNIQUE，因此无需指定它们。

这里有两组示例插入，每组都复制 uuid/date 组合，从而更新而不是插入，并且还插入相同的 uuid 但不同的日期，反之亦然:-

INSERT OR REPLACE INTO `users` VALUES(null,'Fred01234567','Fred Bloggs the 1st','20180101');
INSERT OR REPLACE INTO `users` VALUES(null,'Fred01234567','Fred Bloggs the 2nd','20180101'); -- <<<< DUPLICATE 
INSERT OR REPLACE INTO `users` VALUES(null,'Fred99999999','Fred Bloggs the 2nd','20180101'); -- <<<< different uuid same date
INSERT OR REPLACE INTO `users` VALUES(null,'Fred01234567','Fred Bloggs the 2nd','99999999'); -- <<<< same uuid different date

INSERT OR REPLACE INTO `users` (`uuid`,'name','date') VALUES('Fred76543210','Fred NotBloggs the 1st','20180202');
INSERT OR REPLACE INTO `users` (`uuid`,'name','date') VALUES('Fred76543210','Fred NotBloggs the 1st','20180202');
INSERT OR REPLACE INTO `users` (`uuid`,'name','date') VALUES('Fred99999999','Fred NotBloggs the 1st','20180202');
INSERT OR REPLACE INTO `users` (`uuid`,'name','date') VALUES('Fred76543210','Fred NotBloggs the 1st','99999999');

SELECT * FROM `users`;

结果是:-