Unique constraints & insert or update for both MyS

问题:

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.

Say I have the following table:

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 )
);

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 )

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` )

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 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.

回答1:

SQLite solution (same principle should apply in mysql)

You could simply add a UNIQUE index (at least for SQLite for which this is for) so you could have :-

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`); //<<<<<<<<<<

• Note AUTO_INCREMENT results in a failure for SQLite as it's not a keyword, the correct keyword in SQlite is AUTOINCREMENT. However, it's been omitted as it's probably not required as INTEGER PRIMARY KEY (or the implicit by specifiying PRIMARY KEY (id)) will result in a uniqiue id being automatically generated if no value is supplied for the column when inserting.

• SQLite requires INTEGER, not INT, for the automatically generated id. NOT NULL and also UNIQUE are implied so no need to specify them.

Here's two sets of example inserts each duplicating the uuid/date combination thus updating instead of inserting and also inserting with same uuid but different date and vice-versa :-

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`;

Results are :-

回答2:

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.

To create the table:

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` )
);

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` = ?;

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 ( ?, ?, ? );

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.

回答3:

Sorry about all the comments. Here is how I achieved what I think you are going for. You are going to lose the id as Primary Key on your users table. You will also have to stage your insert variables in a table. But you will get your end results. Sorry I do not have a better solution.

DROP TABLE users;
DROP TABLE users2;

CREATE TABLE `users` (
`uuid` VARCHAR ( 64 ) NOT NULL,
`name` VARCHAR ( 32 ) NOT NULL,
`date` BIGINT NULL

);

ALTER TABLE `users` ADD PRIMARY KEY(`uuid`,`date`);

INSERT INTO users (`name`,`uuid`,`date`) SELECT '','123','2018-04-01';

CREATE TABLE `users2` (

`uuid` VARCHAR ( 64 ) NOT NULL,
`name` VARCHAR ( 32 ) NOT NULL,
`date` BIGINT NULL

);

INSERT INTO users2 (`name`,`uuid`,`date`) SELECT 'brad','123','2018-04-01';

REPLACE INTO users SELECT `uuid`,`name`,`date` FROM users2 GROUP BY `uuid`,`date`;

SELECT * FROM users;`