MySQL INSERT if one of primary key value doesn't match
I would like to do MySQL query like this:
if((value1 != dbPrimaryValue1) OR (value2 != dbPrimaryValue2))
INSERT ROW
else
DO NOTHING
Lets try example:
CREATE TABLE `tmp` (
`one` int NOT NULL,
`two` int NOT NULL,
`three` int NOT NULL);
开发者_JAVA技巧
ALTER TABLE `tmp`
ADD PRIMARY KEY (`one`, `two`);
INSERT INTO `tmp`
(`one`, `two`, `three`)
VALUES (1,2,3);
INSERT INTO `tmp`
(`one`,`two`,`three`)
VALUES (10,20,30),
(1,999,999),
(999,2,999),
(1,2,999)
ON DUPLICATE KEY
UPDATE `one` = `one`; // or some dummy no-source-drain operation
Result is here:
select * from tmp;
+-----+-----+-------+
| one | two | three |
+-----+-----+-------+
| 1 | 2 | 3 |
| 10 | 20 | 30 |
| 1 | 999 | 999 |
| 999 | 2 | 999 |
+-----+-----+-------+
U would like to have result like this:
select * from tmp;
+-----+-----+-------+
| one | two | three |
+-----+-----+-------+
| 1 | 2 | 3 |
| 10 | 20 | 30 |
+-----+-----+-------+
Is it possible to make this query? I'm operating with huge data and procedure like load -> compare -> save is not possible. THANKS!
Simply make both fields unique separately. For example:
CREATE TABLE `tmp` (
`one` int NOT NULL UNIQUE,
`two` int NOT NULL UNIQUE,
`three` int NOT NULL);
Or add the constraints via:
ALTER TABLE `tmp` ADD UNIQUE (`one`);
ALTER TABLE `tmp` ADD UNIQUE (`two`);
If you create a UNIQUE
key constraint, the database will not allow you to insert them automatically.
From MySQL forum:
A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.
精彩评论