关于 INSERT 的 MySQL 问题 ... SELECT ON DUPLICATE KEY UPDATE 和 LAST_INSERT

小编典典

关于 INSERT 的 MySQL 问题 ... SELECT ON DUPLICATE KEY UPDATE 和 LAST_INSERT_ID()

all

在 MySQL 中，我有INSERT ... SELECT ON DUPLICATE KEY UPDATE如下查询：

$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)";

更新：这是上述查询的结果。

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

现在我想创建一个user_id插入或更新记录的数组。

所以，我期望的数组应该是

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

我试过这样的东西，但它对我不起作用。这意味着我只能得到一个 id。

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

那么，我可以知道有没有办法从上面运行的查询的受影响用户 ID 创建一个数组？

阅读 86

2022-07-14

共1个答案

小编典典

DEMO:

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

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

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

```sql
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 |
```

```sql
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 |
```

```sql
– 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 |
```

2022-07-14