MySQL issue on INSERT ... SELECT ON DUPLICATE KEY UPDATE and LAST_INSERT

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?

CodePudding user response：

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