Stepan Stepanov Stepan Stepanov - 6 months ago 17
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

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