I would like to insert a new row into the database if a row with given name
, value
and id
don't exist.
Option 1
You can write a query that checks if the record already exists before inserting it:
INSERT INTO list (name, value, id, note)
SELECT 'test', 2, 221222, 'this is a test'
WHERE NOT EXISTS (
SELECT 1
FROM list
WHERE name = 'test' AND value = 2 AND id = 221222 AND note = 'this is a test'
);
The downside of this approach is that you need to specify the values to insert twice. The advantage is that it does not require you to alter the table strucutre, hence it won't have side effects on other potential queries.
Option 2
You can take advantage of MySQL INSERT ... ON DUPLICATE KEY
syntax. First of all, you would need to create a composiye unique constraint on all 3 columns:
ALTER TABLE list ADD UNIQUE list_unique_index(id, name, value);
This actually prevents duplicates from being inserted (by any mean). Then you can express the query as follows:
INSERT INTO list (name, value, id, note)
VALUES("test", 2, 221222, "this is a test")
ON DUPLICATE KEYS UPDATE id = id
If the unique constraint validation fails, MySQL executes the UPDATE id = id
clause, which is basically a no-op.
Note: there is a third option that consists in using INSERT ... IGNORE
. I would not recommend that since that actually hides all error that could happen in the query (not only those related to the unique constraint); this behavior can bite you somehow.
name
andvalue
fields ? Why not just define them asvarchar
? If these fields are varchar, then you can easily define a UNIQUE constraint, or even better would be PRIMARY key on the combination of(name, value, id)
. Afterwards, you can employIODKU
latin1
character columns also cause huge problems for pretty much everyone,utf8mb4
is a safer default.REPLACE INTO db (
withVALUES (
? I think this would allow you to not only add the values, but also permit you to change some of the values as suggested in an earlier comment.