有没有办法忽略 INSERT 上不存在的列? [英] Is there a way to ignore columns that don't exist on INSERT?

问题描述

我正在使用 MySQL GUI 将一些站点迁移到新版本的 CMS通过选择某些表并将备份转储生成的 INSERT 语句运行到空表(新模式)中.旧表中有几列在新表中不存在，因此脚本停止并出现如下错误:

I'm using a MySQL GUI to migrate some sites to a new version of a CMS by selecting certain tables and running the INSERT statement generated from a backup dump into an empty table (the new schema). There are a few columns in the old tables that don't exist in the new one, so the script stops with an error like this:

脚本行:字段列表"中的 1 个未知列user_id"

Script line: 1 Unknown column 'user_id' in 'field list'

选择要导出的所需列或编辑转储文件将过于繁琐和耗时.为了解决这个问题，我在生成错误时创建未使用的列，通过运行查询导入数据，然后在完成该表后删除未使用的列.我看过INSERT IGNORE，但这似乎是为了忽略重复的键(不是我要找的).

Cherry-picking the desired columns to export, or editing the dump file would be too tedious and time consuming. To work around this I'm creating the unused columns as the errors are generated, importing the data by running the query, then dropping the unused columns when I'm done with that table. I've looked at INSERT IGNORE, but this seems to be for ignoring duplicate keys (not what I'm looking for).

有什么方法可以在忽略目标表中不存在的列的同时执行 INSERT ?我正在寻找一些无痛"的东西，比如一些现有的 SQL 功能.

Is there any way to preform an INSERT while ignoring columns that don't exist in the target table? I'm looking for something "painless", like some existing SQL functionality.

澄清一下，我正在处理一堆备份文件并将数据导入本地数据库进行测试，然后再将其移动到实时服务器.我希望的解决方案示例:

To clarify, I'm working with a bunch of backup files and importing the data to a local database for testing before moving it to the live server. Example of the kind of solution I'm hoping for:

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

如果这样的事情根本不存在，我很高兴接受它作为答案并继续使用我当前的解决方法.

If something like this simply doesn't exist, I'm happy to accept that as an answer and continue to use my current workaround.

推荐答案

您目前的技术似乎足够实用.只是一个小小的改变.

Your current technique seems practical enough. Just one small change.

与其等待错误然后一一创建列，您只需导出架构，进行比较并找出所有表中所有缺失的列.

Rather than waiting for error and then creating columns one by one, you can just export the schema, do a diff and find out all the missing columns in all the tables.

这样工作量就会减少.

您的 gui 将能够仅导出架构，或者 mysqldump 上的以下开关将有助于找出所有丢失的列.

Your gui will be capable of exporting just schema or the following switch on mysqldump will be useful to find out all the missing columns.

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

区分 dbdump1.sql 和 dbdump2.sql 会给你两个数据库的所有差异.

Diffing the dbdump1.sql and dbdump2.sql will give you all the differences in both the databases.

这篇关于有没有办法忽略 INSERT 上不存在的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！