mysql - 如何在 TEMPORARY 表上使用多个 IN 条件进行选择？

Question

问题

我想清理一个困惑的数据库并替换对重复条目的引用。在这个定制的(我的要复杂得多)示例中，我有两个表:

• Octopus
• 颜色

我们知道:

• Octopus 有颜色。
• 表colors包含重复项
• 某些 Octopus 可能与其他 Octopus 具有相同的颜色，但不同color_id .

我解决这个问题的方法涉及TEMPORARY表。为了避免错误:

Can't Reopen Table 'duplicates'

我只是复制我的 TEMPORARY多次表:

CREATE TEMPORARY TABLE duplicates1 SELECT * FROM duplicates;
CREATE TEMPORARY TABLE duplicates2 SELECT * FROM duplicates;

问题

我想避免克隆TEMPORARY表。

数据

CREATE TABLE `test`.`octopuses` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `color_id` INT NOT NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `test`.`colors` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`));

一些颜色重复:

INSERT INTO colors (name) VALUES
    ('cream'), ('sepia'), ('daffodil'), ('lipstick'),                        
    ('lipstick'), ('garnet'), ('flamingo'), ('navy'),                        
    ('chartreuse'), ('garnet'), ('flamingo'), ('juniper'),                             
    ('flint'), ('flint'), ('charcoal'), ('garnet');                     

还有一些 Octopus :

INSERT INTO octopuses (name, color_id) VALUES
    ('Bubbles', 1), ('Inky', 8), ('Octavius', 1),
    ('Sir Inks-A-Lot', 7), ('Octavia', 16), ('Kraken', 6),
    ('Oncho', 15), ('Big Floppy Sea Spider', 14), ('Calamari', 2),
    ('Scuba Doo', 13), ('Squidward Tentacles', 5), ('Wiggleton', 9),
    ('Cthulhu', 2), ('Octopussy', 3), ('Triton', 10), 
    ('Doctor Octopus', 11), ('Billy The Squid', 4), ('Stretch', 12);

示例

为了解决这个问题，我首先创建重复项列表:

CREATE TEMPORARY TABLE duplicates SELECT
    *, COUNT(*) AS count
FROM
    colors
GROUP BY name
HAVING count > 1;

这里是:

mysql> select * FROM duplicates;
+----+----------+-------+
| id | name     | count |
+----+----------+-------+
|  4 | lipstick |     2 |
|  6 | garnet   |     3 |
|  7 | flamingo |     2 |
| 13 | flint    |     2 |
+----+----------+-------+

然后我想创建一个相应的表，其中有 id的副本和 id替换为:

CREATE TEMPORARY TABLE duplicates1 SELECT * FROM duplicates;
CREATE TEMPORARY TABLE duplicates2 SELECT * FROM duplicates;

CREATE TEMPORARY TABLE corresponding SELECT
    id, name,
    (SELECT
            id
        FROM
            duplicates2
        WHERE
            duplicates2.name = colors.name) AS first_id
FROM
    colors
WHERE
    name IN (SELECT
            name
        FROM
            duplicates)
        AND id NOT IN (SELECT
            id
        FROM
            duplicates1)
ORDER BY name ASC;

内容如下:

mysql> SELECT * FROM corresponding;
+----+----------+----------+
| id | name     | first_id |
+----+----------+----------+
| 11 | flamingo |        7 |
| 14 | flint    |       13 |
| 10 | garnet   |        6 |
| 16 | garnet   |        6 |
|  5 | lipstick |        4 |
+----+----------+----------+

然后我只需更新 octopuses表:

CREATE TEMPORARY TABLE corresponding1 SELECT * FROM corresponding;

UPDATE octopuses
SET
    color_id = (SELECT
            first_id
        FROM
            corresponding1
        WHERE
            corresponding1.id = color_id)
WHERE
    color_id IN (SELECT
            id
        FROM
            corresponding)

最终我删除了重复项:

DELETE FROM colors WHERE id IN (SELECT id FROM corresponding);

摘要

这个例子也许不是最好的说明我的问题，但在这里我想避免克隆临时表并找到一种方法来选择多个 IN TEMPORARY 的条件表。

Answer 1

尝试换个角度思考。

你可以这样做:

UPDATE octopuses
        INNER JOIN
    (SELECT 
        *,
            (SELECT 
                    id
                FROM
                    colors
                WHERE
                    colors.name = (SELECT 
                            name
                        FROM
                            colors
                        WHERE
                            color_id = colors.id)
                LIMIT 1) AS first_color_id
    FROM
        octopuses
    HAVING color_id <> first_color_id) AS DUP ON dup.color_id = octopuses.color_id 
SET 
    octopuses.color_id = first_color_id
WHERE
    octopuses.color_id <> first_color_id;


CREATE TEMPORARY TABLE to_delete SELECT id FROM colors WHERE NOT EXISTS (
    SELECT id FROM octopuses WHERE color_id = colors.id
);

DELETE FROM colors WHERE id IN (SELECT id FROM to_delete);

所以你的问题的答案是:

Whenever you need to clone a temporary table, think twice and you will find another way that do no involve reopening temporary table twice!