MySQL issue on INSERT ... SELECT ON DUPLICATE KEY UPDATE and LAST_INSERT_ID()

Question

In MySQL, I have INSERT ... SELECT ON DUPLICATE KEY UPDATE query as below:

$sql = "INSERT INTO user ( name
                         , mobile
                         , email
                         , sex
                         , username
                         , password
                         )
          SELECT u.name
               , u.mobile
               , u.email
               , u.sex
               , u.username
               , u.password
          FROM import_user u 
          WHERE u.name <> '' AND u.mobile <> ''
        ON DUPLICATE KEY UPDATE
              user_id     = LAST_INSERT_ID(user_id),
              name        = VALUES (name),
              mobile      = VALUES (mobile),
              email       = VALUES (email),
              sex         = VALUES (sex)";

UPDATE: This is the result from above query.

select user_id, role_id, name,sex, mobile from user;
+---------+---------------------------+--------+-------------+
| user_id | name                      | sex    | mobile      | 
+---------+---------------------------+--------+-------------+
|     131 | Name 1                    | Male   | 435345345   |
|     132 | Name 2                    | Male   | 43543534    |
|     133 | Name 3                    | Male   | 45645644    |
|     134 | Name 4                    | Male   | 5345        |
|     135 | Name 5                    | Male   | 5465475     |
|     136 | Name 6                    | Male   | 56456546    |
+---------+---------------------------+--------+-------------+

Now I want to create an array of the user_id of either the insert or the update the records.

So, my expecting array should be

$uid = [131,132,133,134,135,136]

I tried it something like this, but it doesn't work for me. That mean I can get only one id.

$stmt = $pdo->prepare($sql);
$stmt->execute();
$uids[] = $pdo->lastInsertId();

So, May I know Is there a way to create an array from the effected user ID of the above query running?

MySQL does not support RETURNING clause. You may use according triggers pack which gathers needed information somewhere (user-defined variable, lo table, etc.) then query to this data. — Akina, Jul 14 '22 at 10:02
which database you are using ? MySQL ? MariaDB has this feature: see https://mariadb.com/kb/en/insertreturning/ — Bernd Buffen, Jul 14 '22 at 10:44
@BerndBuffen. On localhost I have MariaDB, but on liveserver it has MySQL. — ugsgknt, Jul 14 '22 at 11:07

score 1 · Accepted Answer · answered Jul 14 '22 at 10:57

DEMO:

CREATE TABLE test (id INT AUTO_INCREMENT PRIMARY KEY, 
                   category INT, 
                   value INT,
                   UNIQUE (category, value) );

CREATE TRIGGER tr_ai
AFTER INSERT ON test
FOR EACH ROW 
SET @ids_array := CONCAT_WS(',', @ids_array, NEW.id);

CREATE TRIGGER tr_au
AFTER UPDATE ON test
FOR EACH ROW 
SET @ids_array := CONCAT_WS(',', @ids_array, NEW.id);

SET @ids_array := NULL;
INSERT INTO test (category, value)
VALUES (1,11), (2,22);
SELECT * FROM test;
SELECT @ids_array;

id | category | value
-: | -------: | ----:
 1 |        1 |    11
 2 |        2 |    22

| @ids_array |
| :--------- |
| 1,2        |

SET @ids_array := NULL;
INSERT INTO test (category, value)
VALUES (1,111), (2,22)
ON DUPLICATE KEY 
UPDATE value = NULL;
SELECT * FROM test;
SELECT @ids_array;

id | category | value
-: | -------: | ----:
 1 |        1 |    11
 3 |        1 |   111
 2 |        2 |  null

| @ids_array |
| :--------- |
| 3,2        |

-- do not reset @ids_array
INSERT INTO test (id, category, value)
VALUES (1,4,44), (22,2,22)
ON DUPLICATE KEY 
UPDATE value = NULL;
SELECT * FROM test;
SELECT @ids_array;

id | category | value
-: | -------: | ----:
 1 |        1 |  null
 3 |        1 |   111
 2 |        2 |  null
22 |        2 |    22

| @ids_array |
| :--------- |
| 3,2,1,22   |

db<>fiddle here

MySQL issue on INSERT ... SELECT ON DUPLICATE KEY UPDATE and LAST_INSERT_ID()

1 Answers1