mysql insert ignore. Need to get only all inserted rows

Question

Good day everyone! I have MySQL Database with tables on

CREATE TABLE `TableWithInnoDBEngine` (  
`userID` int(11) NOT NULL,  PRIMARY KEY (`userID`),   
UNIQUE KEY `userID_UNIQUE` (`userID`) ) 
ENGINE=InnoDB  DEFAULT CHARSET=utf8;

mysql> select * from TableWithInnoDBEngine;
 +--------+
 | userID |
 +--------+
 |      1 |
 |      2 |
 |      3 |
 +--------+

I'm doing :

INSERT IGNORE INTO TableWithInnoDBEngine (UserID) VALUES (1),(2),(3),(4),(5);


2 row(s) affected Records: 5  Duplicates: 3  Warnings: 0

And want to get all affected rows?

SELECT LAST_INSERT_ID() returns only last value (5), but need to return

 +--------+
 | userID |
 +--------+
 |      4 |
 |      5 |
 +--------+

I'm using PHP 5.6.17 + MySQL 5.5.46-0+deb7u1

Thank you for your responses!

If you want prevent duplicate you can look at this http://stackoverflow.com/questions/2219786/best-way-to-avoid-duplicate-entry-into-mysql-database otherwise comment you need .. — ScaisEdge, Feb 07 '16 at 09:14
Your question is very unclear. And you query seems incorrect, it runs? select * from (select user) where..? doesn't seems like correct syntax — sagi, Feb 07 '16 at 09:16
@scaisEdge i feel like there are more "right" way to do what i need, without creating tables... — Stepan Stepanov, Feb 07 '16 at 09:29
@Strawberry in Russia at the moment we have troubles with economic and this causes very unfair RUB\USD exchange rate, that's why i use THERY cheap VPS with THERY small amount of disk space and ARCHIVE as ENGINE :D — Stepan Stepanov, Feb 07 '16 at 09:31

Gavriel · Answer 1 · 2016-02-07T12:46:32.113

0

I would create a temporary table similar to the destination table, insert all the id-s there, and then you can make 2 selects, one to select the duplicates and one to insert the non-duplicates into table.

I don't understand the second part of your question: inserting all id-s from 1 to 35000, and getting the duplicates? It's equivalent to:

SELECT DISTINCT userId FROM table;

Update:

When you do:

$mysqli->query("INSERT IGNORE INTO TableWithInnoDBEngine (UserID) VALUES (1),(2),(3),(4),(5)");
$info = $mysqli->info();

You can get the information you want in string format like "Records: 3 Duplicates: 0 Warnings: 0" see: http://php.net/manual/en/mysqli.info.php

edited Feb 07 '16 at 12:46

answered Feb 07 '16 at 09:50

Gavriel

18,880
12
68
105

or change engine to InnoDB and make UserID UNIQUE KEY `userID_UNIQUE` (`userID`) and do what i whant with two queries: INSERT ignore INTO table (userID) VALUES (1),(2),(3),(4),(5),(6),(7); SELECT userID FROM table WHERE userID iN (1,2,3,4,5,6); .... – Stepan Stepanov Feb 07 '16 at 09:56
No, because if you do it in this order, then you'll always get back each of them, 'cause either they were already in the table or you just inserted them :) But it would work the other way around – Gavriel Feb 07 '16 at 10:04
i've edited question to make it as clear as possible ... thank you for taking part in a conversation – Stepan Stepanov Feb 07 '16 at 12:16

Stepan Stepanov · Accepted Answer · 2016-06-06T09:07:46.953

Seems that fast and cheap in memory usage solution is to insert new data into temporary table and compare it with original table:

CREATE TABLE `Original_TableWithInnoDBEngine` (
  `userID` int(11) NOT NULL,
  UNIQUE KEY `userID_UNIQUE` (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tmp_TableWithInnoDBEngine` (
  `userID` int(11) NOT NULL,
  UNIQUE KEY `userID_UNIQUE` (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and then:

INSERT INTO Original_TableWithInnoDBEngine (userID) VALUES (1),(2),(3),(4),(5),(6);
select * from Original_TableWithInnoDBEngine;
+--------+
| userID |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
|      5 |
|      6 |
+--------+

INSERT INTO tmp_TableWithInnoDBEngine (userID) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
select * from tmp_TableWithInnoDBEngine;
+--------+
| userID |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
|      5 |
|      6 |
|      7 |
|      8 |
|      9 |
|     10 |
+--------+

and now i use this query to get values that is in tmp table but not in original:

SELECT tmp_TableWithInnoDBEngine.UserID FROM tmp_TableWithInnoDBEngine WHERE tmp_TableWithInnoDBEngine.UserID NOT IN(SELECT UserID FROM original_TableWithInnoDBEngine)";

+--------+
| userID |
+--------+
|      7 |
|      8 |
|      9 |
|     10 |
+--------+

score 0 · Answer 3 · answered Feb 07 '16 at 18:49

0

If you use INSERT ... ON DUPLICATE KEY UPDATE ... istead of INSERT IGNORE ... you can save all duplicate IDs into one string doing something like this:

SET @duplicates := '';

INSERT INTO TableWithInnoDBEngine (UserID) VALUES (1),(2),(3),(4),(5)
ON DUPLICATE KEY UPDATE 
    userID = userID + if(@duplicates := concat(@duplicates,',',userID),0,0);

SET @duplicates := SUBSTRING(@duplicates FROM 2);
SELECT @duplicates;

You can now parse the resulting string on the application side to filter the inserted data.

answered Feb 07 '16 at 18:49

Paul Spiegel

30,925
5
44
53

Thank you for your response, Paul ! I'll test your code tomorrow... at the moment i use @Gavriel 's variant with inserting values into temp table and getting differences between'em with code `SELECT TableWithMEMORYEngine_tmp.UserID FROM TableWithMEMORYEngine_tmp WHERE TableWithMEMORYEngine_tmp.UserID NOT IN(SELECT TableWithInnoDBEngine.UserID FROM TableWithInnoDBEngine)`... – Stepan Stepanov Feb 11 '16 at 16:17

mysql insert ignore. Need to get only all inserted rows

3 Answers3