Объединение 5 или более таблиц с одинаковой структурой и первичным ключом

SQL 5.7.37

Я использую этот код для объединения двух таблиц sql в третью таблицу. Но теперь мне нужно сделать то же самое с 5 или более таблицами.

create table table3 as
    select *
    from table1
    union all
    select *
    from table2
    where not exists (select 1 from table1 where table1.title = table2.title);

Как добавить больше таблиц в эту команду?


Вот примерный набор данных и желаемый результат:

Обратите внимание, что в таблицах есть строки некоторый, точно такие же, как и в других таблицах. Я не уверен, имеет ли это значение.

Структура: ID (первичный ключ, автоинкремент), заголовок (уникальный, индекс), DESC, URL

Ex. Table 1
ID, Title, DESC, URL
 1 Bob thisisbob bob.com
 2 Tom thisistom tom.com
 3 Chad thisischad chad.com

Ex. Table 2
ID, Title, DESC, URL
 1 Chris thisischris chris.com
 2 Chad thisischad chad.com
 3 Dough thisisdough doug.com

Ex. Table 3
ID, Title, DESC, URL
 1 Morgan thisismorgan morgan.com
 2 Jerome thisisjerome jerome.com
 3 Mike thisismike mike.com

Ex. Table 4
ID, Title, DESC, URL
 1 Chris thisischris chris.com
 2 Chad thisischad chad.com
 3 Luke thisisluke luke.com

Result:
What I need in Table 5
ID, Title, DESC, URL
 1 Bob thisisbob bob.com
 2 Tom thisistom tom.com
 3 Chad thisischad chad.com
 4 Chris thisischris chris.com
 5 Dough thisisdough doug.com
 6 Morgan thisismorgan morgan.com
 7 Jerome thisisjerome jerome.com
 8 Mike thisismike mike.com
 9 Luke thisisluke luke.com

Как я могу добавить больше таблиц в мою команду union sql?

По сути, нет никаких ограничений на то, сколько раз вы можете использовать UNION, но я думаю, что вам может быть лучше объединить все столбцы, не являющиеся PK, и обеспечить перенумерацию для формирования нового PK. Какая версия майскл?

Caius Jard 09.04.2022 11:32

@CaiusJard, я бы тоже повторил строку «где не существует»? Вот где я теряюсь.

Mae Webb 09.04.2022 11:34
Обратите внимание, что некоторые строки в таблицах точно такие же, как и в других таблицах. Я не уверен, имеет ли это значение. - а вы хотите их дедупликацию?
Caius Jard 09.04.2022 11:35

@CaiusJard да, не хочу, чтобы он копировал дубликаты на основе уникального столбца заголовка SQL 5.7.37

Mae Webb 09.04.2022 11:37

Если исходные таблицы не слишком велики, используйте UNION DISTINCT. В противном случае используйте отдельную CREATE TABLE с полным определением таблицы и соответствующим ограничением UNIQUE, а также 5 отдельных INSERT IGNORE.

Akina 09.04.2022 11:57

@Akina, я пробовал разные, но таблицы были слишком большими, и время истекло. Код в верхней части вопроса по-прежнему объединял их очень быстро, но только для 2 таблиц. В моих реальных таблицах более 50 столбцов, и я не уверен, что создание таблицы будет работать хорошо. Когда я пытаюсь получить полный текст в phpmyadmin, он говорит: «# 1070 — указано слишком много ключевых частей; разрешено не более 16 частей».

Mae Webb 09.04.2022 12:11

Другой вариант может заключаться в том, чтобы сначала собрать все объединенные данные (используя UNION_ALL), а затем один раз выбрать отдельные значения, так как это более эффективно, чем выполнение 4 UNION DISTINCT. Может ли это решение работать для вас?

lemon 09.04.2022 12:27
Формы c голосовым вводом в React с помощью Speechly
Формы c голосовым вводом в React с помощью Speechly
Пытались ли вы когда-нибудь заполнить веб-форму в области электронной коммерции, которая требует много кликов и выбора? Вас попросят заполнить дату,...
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Будучи разработчиком веб-приложений, легко впасть в заблуждение, считая, что приложение без JavaScript не имеет права на жизнь. Нам становится удобно...
Flatpickr: простой модуль календаря для вашего приложения на React
Flatpickr: простой модуль календаря для вашего приложения на React
Если вы ищете пакет для быстрой интеграции календаря с выбором даты в ваше приложения, то библиотека Flatpickr отлично справится с этой задачей....
В чем разница между Promise и Observable?
В чем разница между Promise и Observable?
Разберитесь в этом вопросе, и вы значительно повысите уровень своей компетенции.
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Клиент для URL-адресов, cURL, позволяет взаимодействовать с множеством различных серверов по множеству различных протоколов с синтаксисом URL.
Четыре эффективных способа центрирования блочных элементов в CSS
Четыре эффективных способа центрирования блочных элементов в CSS
У каждого из нас бывали случаи, когда нам нужно отцентрировать блочный элемент, но мы не знаем, как это сделать. Даже если мы реализуем какой-то...
0
7
45
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Идея может заключаться в том, чтобы отложить создание каждого уникального значения только после того, как вы агрегируете все свои таблицы, если UNION_ALL намного быстрее, чем UNION, и вы будете выполнять операцию DISTINCT один раз, а не пять раз таким образом.

SET @cnt = 0;

SELECT 
    (@cnt := @cnt + 1) AS rowNumber,
    distinct_aggr_tables.* 
FROM 
    ( 
    SELECT DISTINCT * FROM (
        SELECT `Title`, `DESC`, `url` FROM Table1
        UNION ALL
        SELECT `Title`, `DESC`, `url` FROM Table2
        UNION ALL
        SELECT `Title`, `DESC`, `url` FROM Table3
        UNION ALL
        SELECT `Title`, `DESC`, `url` FROM Table4
        ) aggr_tables
    ) distinct_aggr_tables

Вот соответствующая рабочий пример: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ea1fa1f668e769cc5b1824dcfb9feb40.

Может ли это решение работать для вас?


Обновлено: Как выбрать все поля, кроме одного, из таблицы MySQL для этой задачи

Есть два интересных способа для этого:

1) Первый подход копирует каждую таблицу в разные временные таблицы, затем использует оператор ALTER для удаления столбца, который нас не интересует, поэтому мы используем эти таблицы с первой версией этого кода.

    # for each table
    CREATE TEMPORARY TABLE temp_Table1 AS
    SELECT * FROM Table1;

    ALTER TABLE temp_Table1
    DROP Id;

2) Второй подход использует подготовленное заявление, что позволяет построить запрос в виде строки. Это может помочь в этом упражнении, потому что мы можем захотеть получить все имена столбцов из таблицы INFORMATION_SCHEMA.COLUMNS в запросе, а затем удалить имя поля, которое нас не интересует, следовательно, передать список имен столбцов в исходный запрос.

    SET @sql = CONCAT(
        'CREATE OR REPLACE VIEW AllTables AS ', 
        'SELECT
            ROW_NUMBER() OVER(ORDER BY Title ASC) AS rowNumber,
            distinct_aggr_tables.* 
         FROM 
            (',
            'SELECT DISTINCT 
                * 
            FROM 
                (
                SELECT ',
                    (SELECT CONCAT(REPLACE(GROUP_CONCAT(COLUMN_NAME SEPARATOR '`,`'), 'Id`,', ''), '` ')
                     FROM INFORMATION_SCHEMA.COLUMNS cols
                     WHERE cols.TABLE_NAME = 'Table1' AND cols.TABLE_SCHEMA = 'test'),
                'FROM 
                    Table1
                UNION ALL
                SELECT ',
                    (SELECT CONCAT(REPLACE(GROUP_CONCAT(COLUMN_NAME SEPARATOR '`,`'), 'Id`,', ''), '` ')
                     FROM INFORMATION_SCHEMA.COLUMNS cols
                     WHERE cols.TABLE_NAME = 'Table2' AND cols.TABLE_SCHEMA = 'test'),
                'FROM 
                    Table2
                UNION ALL
                SELECT ',
                    (SELECT CONCAT(REPLACE(GROUP_CONCAT(COLUMN_NAME SEPARATOR '`,`'), 'Id`,', ''), '` ')
                     FROM INFORMATION_SCHEMA.COLUMNS cols
                     WHERE cols.TABLE_NAME = 'Table3' AND cols.TABLE_SCHEMA = 'test'),
                'FROM 
                    Table3
                UNION ALL
                SELECT ',
                    (SELECT CONCAT(REPLACE(GROUP_CONCAT(COLUMN_NAME SEPARATOR '`,`'), 'Id`,', ''), '` ')
                     FROM INFORMATION_SCHEMA.COLUMNS cols
                     WHERE cols.TABLE_NAME = 'Table4' AND cols.TABLE_SCHEMA = 'test'),
                'FROM 
                    Table4

            ) aggr_tables
        ) distinct_aggr_tables;'
    );
                 
    PREPARE stmt FROM @sql;
    EXECUTE stmt;

    SELECT * FROM AllTables;

Обратите внимание, что этот код точно воспроизводит первый код этого поста, за исключением того факта, что он использует оконную функцию ROW_NUMBER вместо глобальной переменной, которая обновляет себя.

Это решение делает некоторые предположения, согласно которому его следует тщательно быстро исправить:

  • столов ровно 4: чтобы изменить это количество, необходимо реплицировать следующий код в нужном месте для каждой новой таблицы:

       SELECT ',
          (SELECT CONCAT(REPLACE(GROUP_CONCAT(COLUMN_NAME SEPARATOR '`,`'), 'Id`,', ''), '` ')
           FROM INFORMATION_SCHEMA.COLUMNS cols
           WHERE cols.TABLE_NAME = <new_table_name> AND cols.TABLE_SCHEMA = 'test'),
      'FROM 
          <new_table_name>
    
  • текущие имена таблиц — Table1, Table2, Table3, Table4, а имя базы данных — test: эти ссылки следует заменить, когда мы ищем имена полей конкретной таблицы (фильтрация по имени таблицы и имени базы данных):

     SELECT '
         (SELECT CONCAT ...
          FROM ...
          WHERE cols.TABLE_NAME = <table_name> AND cols.TABLE_SCHEMA = <db_name>),
    'FROM 
         <table_name>
    
  • имя удаляемого поля — «Id», и оно находится в качестве первого столбца всех таблиц: если имя отличается, необходимо изменить его имя при удалении этого столбца. Более того, если это не первая колонка, здесь нужны некоторые доработки:

      # COLUMN_NAME: 
      # ['Id', 'Title', 'DESC', 'url']
      #
      # GROUP_CONCAT(COLUMN_NAME SEPARATOR '`,`'):
      # 'Id`,`Title`,`DESC`,`url'
      # 
      # REPLACE(GROUP_CONCAT(COLUMN_NAME SEPARATOR '`,`'), 'Id`,', '')
      # '`Title`,`DESC`,`url'
      # 
      # CONCAT(REPLACE(GROUP_CONCAT(COLUMN_NAME SEPARATOR '`,`'), 'Id`,', ''), '` ')
      # '`Title`,`DESC`,`url`'
    

(обратные кавычки добавлены, чтобы избежать исключений из-за DESC)

Примечание1: Генерация четырех групп SELECT для каждой таблицы может быть автоматизирована (простой пример внизу эта страница) путем циклического использования переменной в именах таблиц, содержащихся в INFORMATION_SCHEMA.TABLES. Тем не менее, я бы не рискнул идти по этому пути, так как становится сложно иметь дело со строковым текстом, который нужно оценить с помощью подготовленного оператора и CONCAT вычисленного значения из другой таблицы (INFORMATION_SCHEMA.COLUMNS cols).

Заметка 2: Не удалось увидеть эффекты этого кода в рабочий примерх sql, потому что не удалось получить доступ к таблицам INFORMATION_SCHEMA db. Код был протестирован в автономном режиме на базе данных MySQL 8.0.


Первый подход может быть довольно затратным по памяти, в то время как второй может быть более эффективным, если его тщательно обрабатывать в исправлениях для адаптации вашей базы данных.

В целом нет идеального решения, хотя некоторые из них могут решить вашу проблему.

PS: любые предлагаемые изменения для улучшения этого поста более чем приветствуются.

есть ли способ сделать это без указания всех столбцов?

Mae Webb 09.04.2022 13:33

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

lemon 09.04.2022 14:53

Обратите внимание, что требование состоит в том, чтобы различать один конкретный столбец, а не все столбцы

Caius Jard 09.04.2022 16:30

@CaiusJard Я думал, что он имел в виду, что в разных таблицах есть несколько строк, для которых все столбцы одинаковы на Notice the tables have some rows that are exactly the same as other tables.. Можете ли вы перепроверить этот момент, чтобы понять @MaeWebb?

lemon 09.04.2022 20:43

Я сделал вывод, что «отличается по названию» от EXISTS в «рабочей» попытке.. Был также последний опубликованный мной комментарий, который, казалось, указывал на то, что заголовок был единственным дискриминатором.. но я согласен, что это стоит перепроверить, потому что я не думайте, что какие-либо существующие образцы данных являются для него убедительными

Caius Jard 09.04.2022 20:51

Другие вопросы по теме