Stepan Stepanov Stepan Stepanov - 1 year ago 45
SQL Question

mysql insert ignore. Need to get only all inserted rows

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!

Answer Source

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 |
+--------+