M
M
Mir2015-10-31 22:16:36
MySQL
Mir, 2015-10-31 22:16:36

INSERT and IF. How right?

There is a table table. Three fields: autoincrement, two, three.
You need to add a row with values ​​in the fields two, three if it is not in this table.
I thought to use ignore, but the first field is autoincrement, and two, three have duplicate elements, so you can't make them PRIMARY or UNIQUE.
After 2-3 hours of trying, I came up with this code:

SELECT IF((SELECT three FROM table WHERE two='100' AND three='1') IS NULL,  @res:=1, 0) RES;
INSERT INTO table (two, three) (SELECT '100','1' FROM table  WHERE @res=1  LIMIT 1)

Are there better options? Or is my code quite good?
#UPD:
Another option using a variable
SELECT @res:=autoincrement FROM table WHERE two=46 AND three=24;
INSERT IGNORE INTO table  (autoincrement,two, three) VALUES (@res, 46, 24)

But in the previous version, INSERT is 2-3 times faster

Answer the question

In order to leave comments, you need to log in

2 answer(s)
R
Rsa97, 2015-10-31
@Rsa97

And who prevents to make a composite unique key UNIQUE KEY `two_three` (`two`, `three`)
PS. Well, or something like this:

INSERT INTO `table` (`two`, `three`)
  SELECT `t1`.`two`, `t1`.`three`
    FROM (
      SELECT :two AS `two`, :three AS `three`
    ) AS `t1`
    LEFT JOIN `table` AS `t2` USING (`two`, `three`)
    WHERE `t2`.`two` IS NULL;

R
Ruslan Fedoseev, 2015-10-31
@martin74ua

and you can't assign default values ​​to fields two and three? Or is there some sort of dependency?

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question