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:
通过选择某些表并将从备份转储生成的INSERT语句运行到空表(新模式)，我使用MySQL GUI将一些站点迁移到CMS的新版本。旧表中有一些列在新表中不存在，因此脚本以如下错误结束:
Script line: 1 Unknown column 'user_id' in 'field list'
脚本行:“字段列表”中1个未知列“user_id”
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 IGNORE，但这似乎是为了忽略重复的键(不是我要找的)。
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.
是否有方法在忽略目标表中不存在的列的同时对插入进行预格式化?我正在寻找一些“无痛”的东西，比如一些现有的SQL功能。
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:
为了说明这一点，我正在处理一堆备份文件，并将数据导入本地数据库进行测试，然后再将其移动到live服务器。我所希望的解决方案的例子:
-- 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.
如果像这样的事情根本不存在，我很乐意接受这个答案，并继续使用我目前的解决方案。
6 个解决方案
#1
5
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.
不必等待错误，然后逐个创建列，您只需导出模式，执行diff并找出所有表中所有丢失的列。
That way it would be less work.
这样就减少了工作量。
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.
您的gui将能够仅导出模式，或者mysqldump上的以下开关将有助于查找所有丢失的列。
mysqldump --no-data -uuser -ppassword --database dbname1 > dbdump1.sql
mysqldump --no-data -uuser -ppassword --database dbname2 > dbdump2.sql
Diffing the dbdump1.sql and dbdump2.sql will give you all the differences in both the databases.
dbdump1 dif。sql和dbdump2。sql将提供这两个数据库中的所有差异。
#2
3
you can write a store function like that:
你可以这样写一个store函数:
sf_getcolumns(table_name varchar(100))
return string contatining the filed list like this: 'field_1,field_2,field_3,...'
返回字符串，包含这样的文件列表:'field_1,field_2,field_3，…'
then create a store procedure
然后创建一个存储过程
sp_migrite (IN src_db varchar(50), IN target_db varchar(50))
that runs trugh the tables and for each table gets the filed lists and then creates a string like
运行trugh表，对每个表获取归档列表，然后创建一个类似的字符串
cmd = 'insert into ' || <target_db><table_name> '(' || <fileds_list> || ') SELECT' || <fileds_list> || ' FROM ' <src_db><table_name>
then execute the string for each table
然后为每个表执行字符串
#3
1
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.
是否有方法在忽略目标表中不存在的列的同时对插入进行预格式化?我正在寻找一些“无痛”的东西，比如一些现有的SQL功能。
No, there is no "painless" way to do so.
不，没有“无痛”的方法。
Instead, you must explicitly handle those columns which do not exist in the final tables. For example, you must remove them from the input stream, drop them after the fact, play dirty tricks (engine=BLACKHOLE + triggers to INSERT only what you want to the true target schema), whatever.
相反，您必须显式地处理那些在最终表中不存在的列。例如，您必须从输入流中删除它们，在事实发生后删除它们，玩一些卑鄙的把戏(engine=BLACKHOLE +触发器，以便只向真正的目标模式插入您想要的内容)，等等。
Now, this doesn't necessarily need to be manual -- there are tools (as Devart noted) and ways to query the db catalog to determine column names. However, it's not as easy as simply annotating your INSERT statements.
现在，这并不一定需要手工操作——有工具(如Devart所提到的)和查询db编目以确定列名的方法。然而，这并不像简单地注释插入语句那样简单。
Perhaps the CMS vendor can supply a reasonable migration script?
也许CMS供应商可以提供一个合理的迁移脚本?
#4
0
dbForge Studio for MySQL will give you an opportunity to compare and synchronize data between two databases.
MySQL的dbForge Studio将给您一个机会来比较和同步两个数据库之间的数据。
By default data comparison is performed only for the objects with the same names; you can use automatic mapping or map database objects manually. dbForge Studio allows you to customize mapping of tables and columns, so you can compare data of objects with non-equal names, owners, and structure. You may also map columns with different types, however this may result in data truncation, rounding, and errors during synchronization for certain types.
默认情况下，只对名称相同的对象执行数据比较;您可以使用自动映射或手动映射数据库对象。dbForge Studio允许您定制表和列的映射，因此您可以用不相等的名称、所有者和结构来比较对象的数据。您还可以映射具有不同类型的列，但是这可能导致某些类型的数据截断、舍入和同步期间的错误。
#5
0
I carefully read all these posts because I have the same challenge. Please review my solution:
我仔细阅读了所有这些文章，因为我也有同样的挑战。请审查我的解决方案:
I did it in c# but you can do it any language.
我用c#做的，但是你可以用任何语言做。
Check the insert statement for columnnames. If any is missing from your actual table ADD them as a TEXT column coz TEXT can eat anything.
检查列名的插入语句。如果您的实际表中缺少任何内容，请将它们添加为文本列，因为文本可以吃任何东西。
When finished inserting into that table, remove the added columns.
当完成插入到该表中时，删除添加的列。
Done.
完成了。
#6
0
found your question interesting.
发现你的问题很有趣。
I knew that there was a way to select the column names from a table in MySQL; it's show columns from tablename. What I'd forgotten was that all of the MySQL table data is held in a special database, called "information_schema".
我知道有一种方法可以从MySQL中的表中选择列名;它是来自tablename的显示列。我忘记了所有MySQL表数据都保存在一个名为“information_schema”的特殊数据库中。
This is the logical solution, but it doesn't work:
这是逻辑解决方案，但它不起作用:
mysql> insert into NEW_TABLE (select column_name from information_schema.columns where table_name='NEW_TABLE') values ...
I'll keep looking, though. If it's possible to grab a comma-delimited value from the select column_name query, you might be in business.
不过,我会继续找。如果可以从select column_name查询中获取逗号分隔的值，那么您可能在业务中。
Edit:
编辑:
You can use the select ... from ... into command to generate a one-line CSV, like the following:
您可以使用select…从…在命令中生成一行CSV，如下所示:
mysql> select column_name from information_schema.columns where table_name='NEW_TABLE' into outfile 'my_file' fields terminated by '' lines terminated by ', '
I don't know how to get this to output to the MySQL CLI stdout, though.
但是我不知道如何将它输出到MySQL CLI stdout中。
#1
5
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.
不必等待错误，然后逐个创建列，您只需导出模式，执行diff并找出所有表中所有丢失的列。
That way it would be less work.
这样就减少了工作量。
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.
您的gui将能够仅导出模式，或者mysqldump上的以下开关将有助于查找所有丢失的列。
mysqldump --no-data -uuser -ppassword --database dbname1 > dbdump1.sql
mysqldump --no-data -uuser -ppassword --database dbname2 > dbdump2.sql
Diffing the dbdump1.sql and dbdump2.sql will give you all the differences in both the databases.
dbdump1 dif。sql和dbdump2。sql将提供这两个数据库中的所有差异。
#2
3
you can write a store function like that:
你可以这样写一个store函数:
sf_getcolumns(table_name varchar(100))
return string contatining the filed list like this: 'field_1,field_2,field_3,...'
返回字符串，包含这样的文件列表:'field_1,field_2,field_3，…'
then create a store procedure
然后创建一个存储过程
sp_migrite (IN src_db varchar(50), IN target_db varchar(50))
that runs trugh the tables and for each table gets the filed lists and then creates a string like
运行trugh表，对每个表获取归档列表，然后创建一个类似的字符串
cmd = 'insert into ' || <target_db><table_name> '(' || <fileds_list> || ') SELECT' || <fileds_list> || ' FROM ' <src_db><table_name>
then execute the string for each table
然后为每个表执行字符串
#3
1
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.
是否有方法在忽略目标表中不存在的列的同时对插入进行预格式化?我正在寻找一些“无痛”的东西，比如一些现有的SQL功能。
No, there is no "painless" way to do so.
不，没有“无痛”的方法。
Instead, you must explicitly handle those columns which do not exist in the final tables. For example, you must remove them from the input stream, drop them after the fact, play dirty tricks (engine=BLACKHOLE + triggers to INSERT only what you want to the true target schema), whatever.
相反，您必须显式地处理那些在最终表中不存在的列。例如，您必须从输入流中删除它们，在事实发生后删除它们，玩一些卑鄙的把戏(engine=BLACKHOLE +触发器，以便只向真正的目标模式插入您想要的内容)，等等。
Now, this doesn't necessarily need to be manual -- there are tools (as Devart noted) and ways to query the db catalog to determine column names. However, it's not as easy as simply annotating your INSERT statements.
现在，这并不一定需要手工操作——有工具(如Devart所提到的)和查询db编目以确定列名的方法。然而，这并不像简单地注释插入语句那样简单。
Perhaps the CMS vendor can supply a reasonable migration script?
也许CMS供应商可以提供一个合理的迁移脚本?
#4
0
dbForge Studio for MySQL will give you an opportunity to compare and synchronize data between two databases.
MySQL的dbForge Studio将给您一个机会来比较和同步两个数据库之间的数据。
By default data comparison is performed only for the objects with the same names; you can use automatic mapping or map database objects manually. dbForge Studio allows you to customize mapping of tables and columns, so you can compare data of objects with non-equal names, owners, and structure. You may also map columns with different types, however this may result in data truncation, rounding, and errors during synchronization for certain types.
默认情况下，只对名称相同的对象执行数据比较;您可以使用自动映射或手动映射数据库对象。dbForge Studio允许您定制表和列的映射，因此您可以用不相等的名称、所有者和结构来比较对象的数据。您还可以映射具有不同类型的列，但是这可能导致某些类型的数据截断、舍入和同步期间的错误。
#5
0
I carefully read all these posts because I have the same challenge. Please review my solution:
我仔细阅读了所有这些文章，因为我也有同样的挑战。请审查我的解决方案:
I did it in c# but you can do it any language.
我用c#做的，但是你可以用任何语言做。
Check the insert statement for columnnames. If any is missing from your actual table ADD them as a TEXT column coz TEXT can eat anything.
检查列名的插入语句。如果您的实际表中缺少任何内容，请将它们添加为文本列，因为文本可以吃任何东西。
When finished inserting into that table, remove the added columns.
当完成插入到该表中时，删除添加的列。
Done.
完成了。
#6
0
found your question interesting.
发现你的问题很有趣。
I knew that there was a way to select the column names from a table in MySQL; it's show columns from tablename. What I'd forgotten was that all of the MySQL table data is held in a special database, called "information_schema".
我知道有一种方法可以从MySQL中的表中选择列名;它是来自tablename的显示列。我忘记了所有MySQL表数据都保存在一个名为“information_schema”的特殊数据库中。
This is the logical solution, but it doesn't work:
这是逻辑解决方案，但它不起作用:
mysql> insert into NEW_TABLE (select column_name from information_schema.columns where table_name='NEW_TABLE') values ...
I'll keep looking, though. If it's possible to grab a comma-delimited value from the select column_name query, you might be in business.
不过,我会继续找。如果可以从select column_name查询中获取逗号分隔的值，那么您可能在业务中。
Edit:
编辑:
You can use the select ... from ... into command to generate a one-line CSV, like the following:
您可以使用select…从…在命令中生成一行CSV，如下所示:
mysql> select column_name from information_schema.columns where table_name='NEW_TABLE' into outfile 'my_file' fields terminated by '' lines terminated by ', '
I don't know how to get this to output to the MySQL CLI stdout, though.
但是我不知道如何将它输出到MySQL CLI stdout中。