Есть ли способ игнорировать столбцы, которые не существуют в INSERT?

Я использую MySQL GUI для переноса некоторых сайтов на новую версию CMS, выбирая определенные таблицы и запуская инструкцию INSERT, сгенерированную из резервной копии дамп в пустой стол (новая схема). В старых таблицах есть несколько столбцов, которые не существуют в новой, поэтому script останавливается с такой ошибкой:

Script строка: 1 Неизвестный столбец 'user_id' в 'списке полей'

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

Есть ли способ преподносить INSERT при игнорировании столбцов, которые не существуют в целевой таблице? Я ищу что-то "безболезненное", как некоторые существующие функции SQL.

Чтобы уточнить, я работаю с кучей файлов резервных копий и импортирую данные в локальную базу данных для тестирования, прежде чем переместить его на живой сервер. Пример такого решения, на которое я надеюсь:

-- Don't try to insert data from columns that don't exist in "new_table"
INSERT INTO `new_table` {IGNORE UNKNOWN COLUMNS} (`id`, `col1`, `col2`) VALUES 
  (1, '', ''),
  (2, '', '');

Если что-то вроде этого просто не существует, я рад принять это как ответ и продолжать использовать свое текущее решение.

Ответы

Ответ 1

Ваша нынешняя техника кажется достаточно практичной. Только одно небольшое изменение.

Вместо того, чтобы ждать ошибки и затем создавать столбцы один за другим, вы можете просто экспортировать схему, выполнить diff и узнать все недостающие столбцы во всех таблицах.

Таким образом, это будет меньше работы.

Ваш gui будет способен экспортировать только схему, или следующий ключ на mysqldump будет полезен, чтобы узнать все отсутствующие столбцы.

mysqldump --no-data -uuser -ppassword --database dbname1 > dbdump1.sql
mysqldump --no-data -uuser -ppassword --database dbname2 > dbdump2.sql

Разграничение dbdump1.sql и dbdump2.sql даст вам все различия в обеих базах данных.

Ответ 2

вы можете написать такую функцию хранения:

sf_getcolumns(table_name varchar(100))

возвращаемая строка, связанная с таким списком: 'Field_1, field_2, field_3,...

затем создайте процедуру хранения

sp_migrite (IN src_db varchar(50), IN target_db varchar(50))

который запускает trugh таблицы и для каждой таблицы получает список, а затем создает строку типа

cmd = 'insert into ' || <target_db><table_name> '(' || <fileds_list> || ') SELECT' || <fileds_list> || ' FROM ' <src_db><table_name>

затем выполните строку для каждой таблицы

Ответ 3

Есть ли способ преподать INSERT, игнорируя столбцы, которые не существуют в целевой таблице? Я ищу что-то "безболезненное", как некоторые существующие функции SQL.

Нет, нет никакого "безболезненного" способа сделать это.

Вместо этого вы должны явно обрабатывать те столбцы, которые не существуют в финальных таблицах. Например, вы должны удалить их из входного потока, отбросить их после факта, сыграть грязные трюки (engine=BLACKHOLE + триггеры только для INSERT что вы хотите для истинной целевой схемы), что угодно.

Теперь это необязательно должно быть ручным - есть инструменты (как Devart отметил) и способы запроса каталога db для определения имен столбцов. Однако это не так просто, как просто аннотировать ваши инструкции INSERT.

Возможно, поставщик CMS может обеспечить разумную миграцию script?

Ответ 4

dbForge Studio for MySQL даст вам возможность сравнить и синхронизировать между двумя базами данных.

По умолчанию сравнение данных выполняется только для объектов с одинаковыми именами; вы можете использовать автоматическое сопоставление или объекты базы данных карты вручную. dbForge Studio позволяет настраивать сопоставление таблиц и столбцов, чтобы вы могли сравнивать данные объектов с именами, владельцами и структурой, не равными. Вы также можете сопоставлять столбцы с разными типами, однако это может привести к усечению, округлению данных и ошибкам во время синхронизации для определенных типов.

Ответ 5

наш интересный вопрос.

Я знал, что есть способ выбрать имена столбцов из таблицы в MySQL; это show columns from tablename. Я забыл, что все данные таблицы MySQL хранятся в специальной базе данных, называемой "information_schema".

Это логическое решение, но оно не работает:

mysql> insert into NEW_TABLE (select column_name from information_schema.columns where table_name='NEW_TABLE') values ...

Я продолжу искать. Если возможно получить значение с разделителями-запятыми из запроса select column_name, вы можете быть в бизнесе.

Edit:

Вы можете использовать команду select ... from ... into для создания однострочного CSV, например:

mysql> select column_name from information_schema.columns where table_name='NEW_TABLE' into outfile 'my_file' fields terminated by '' lines terminated by ', '

Я не знаю, как получить это для вывода на стандартный вывод CLI MySQL.

Ответ 6

Я внимательно прочитал все эти сообщения, потому что у меня такая же проблема. Просмотрите мое решение:

Я сделал это в С#, но вы можете сделать это на любом языке.

Проверьте инструкцию insert для имен столбцов. Если какой-либо из вашей фактической таблицы отсутствует, добавьте их как столбец TEXT coz ТЕКСТ может есть что угодно.

По завершении вставки в эту таблицу удалите добавленные столбцы.

Готово.