Insert if not exists, no unique key

Question

I want the name column to be unique here. Because other problem unique keys cannot be added, cannot use on duplicate key update.

I try use insert select, but not work.

INSERT INTO test2 (NAME, hotStatus)
SELECT 'A', 1 WHERE NOT EXISTS (SELECT 1 FROM test2 WHERE NAME = 'A' LIMIT 1);

table is:

CREATE TABLE `test2` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10),
  `hotStatus` tinyint(1),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

@Test
public void testInsert() throws InterruptedException {
    final int COUNT = 20;

    Thread[] ta = new Thread[COUNT];
    for (int i = 0; i < COUNT; i++) {
        int hotStatus = i % 2;
        ta[i] = new Thread(() -> getConn()
                .update("INSERT INTO test2 (NAME, hotStatus) " +
                        "SELECT 'A', ? WHERE NOT EXISTS (SELECT 1 FROM test2 WHERE NAME = 'A' LIMIT 1);", 
                        hotStatus));
    }
    for (Thread t : ta) {
        t.start();
    }
    for (Thread t : ta) {
        t.join();
    }
}

The table is empty before running the test. After I run this test, there are a dozen more data in test2.

id  name hotStatus
3   A    1
4   A    0
5   A    0
6   A    0
7   A    1

I try select for update, but can't lock it.

select * from test2 where name = 'A' for update;

I see other question, but not got answer

score 0 · Answer 1 · answered Mar 26 '20 at 09:44

0

Cannot find any problem (except excess LIMIT in subquery).

INSERT INTO test2 (NAME, hotStatus)
SELECT @name, 1 
WHERE NOT EXISTS ( SELECT 1 
                   FROM test2 
                   WHERE NAME = @name );

where @name variable is used for name value to be inserted (literal may be used like in a question).

fiddle

answered Mar 26 '20 at 09:44

Akina

21,183
4
9
16

No data in the initial table, I tested under high concurrency and most of the inserts were successful, resulting in close to twenty data with a name of 'A' in the end – Spirytus Rektyfikowany Mar 26 '20 at 09:51
@SpirytusRektyfikowany *resulting in close to twenty data with a name of 'A' in the end* Your explanation looks like `name` column MUST be unique. Please explain *Because other problem unique keys cannot be added* more detailed in your question - maybe you should solve that problem instead of this one? – Akina Mar 26 '20 at 10:03
Very realistic question, because we have to wait long for other departments – Spirytus Rektyfikowany Mar 26 '20 at 10:24

Insert if not exists, no unique key

1 Answers1