Блог

Проблема

Я хотел бы очистить беспорядочную базу данных и заменить ссылки на повторяющиеся записи. В этом пользовательском (моем гораздо более сложном) примере у меня есть две таблицы:

• Octopuses
• Цвет

Мы знаем, что:

• Осьминог имеет цвет.
• Таблица colors содержит дубликаты
• Некоторые осьминоги могут иметь тот же цвет, что и другие осьминоги, но отличаться 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');                     
  

И несколько осьминогов:

 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 таблицах.

Попробуйте подумать наоборот.

Вы могли бы сделать:

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

Итак, ответ на ваш вопрос таков:

Всякий раз, когда вам нужно клонировать временную таблицу, подумайте дважды, и вы найдете другой способ, который не требует повторного открытия временной таблицы дважды!