MySQL/MariaDB: Is this the most efficient way to insert unique values and return nonunique values?-CodePudding

I had a statement in PSQL that did this just fine (based on an answer from this thread) But I've been trying to recreate the same statement in MySQL which has been difficult. My current workaround is ugly:

    CREATE TEMPORARY TABLE potential_duplicates (
        id VARCHAR(64)
        content TEXT,
    
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
    INSERT INTO potential_duplicates(id, content) VALUES ('1', 'some content'), ('2', 'some more content');
    SELECT id, content FROM potential_duplicates WHERE id IN (SELECT id FROM main_table);
    INSERT INTO main_table(id, content)
       SELECT id, content FROM potential_duplicates WHERE id NOT IN (SELECT id FROM main_table);

This query is going to be used very often and I feel like making a temporary table every query is inefficient. Originally I was trying to find a way to use the INSERT ON DUPLICATE feature. It would have worked fine if I was just inserting and nothing else, but i need to return the duplicate values in the end.

CodePudding user response：

Assuming id is a primary key (or at least unique), you can use INSERT IGNORE to avoid duplicates and RETURNING (available as of MariaDB 10.5) to return the inserted id values:

INSERT IGNORE main_content(id, content) 
VALUES ('2', 'some content'), ('5', 'some more content')
RETURNING id

Demo on dbfiddle