从另一个具有不同列数的表插入(MySql)

问题描述

问题，为什么这个剂量不起作用?

Question, why this dose not work?

create table one (a int(1) default 1, b int(2));

create table two (b int(1));

insert into one select * from two;

错误:

Column count doesn't match value count at row 1

一个知道，一个可以计数，但是为什么从哲学上呢?数据库知道表two中插入列的名称是b，知道表one中列a的默认值等于1.那么，执行此查询有什么问题?

a know it, a can count, but why, philosophically?database knows, what the name of inserting column from table two is b, knows that the column a in table one has a default value equal 1..so, what problem of executing this query?

而且一般-如果无法通过这种方式，如何在没有列及其列数的信息的情况下以不同的方式而不是手动进行操作?

And general - How can i do this differently, not manual, without information of a columns and their count, if this way is impossible?

我知道这一点:表two始终具有与表one相同的所有列.但是表one也有另外一列，它们具有一些默认值.

I know this:table two always have all the same columns, that the table one have. But table one have another columns too, that have a some default values.

有什么方法可以做到这一点吗?在one中插入two中的所有数据，并用一些默认值或其他值填充其余列！

Is there some way to do that? insert all data from two in one, and fill the remaining columns by some default or other values!

需要帮助！非常感谢您的配合！

Need help!Thank you very match!

推荐答案

运行时:

insert into one
    select * from two;

SQL引擎自动将其包含在隐含的列中.

The SQL engine automatically puts in the columns that are implied.

• 对于insert，这是声明顺序的列列表.
• 对于*，这是声明顺序的列列表.

• For the insert, this is the list of columns in declaration order.
• For the *, this is the list of columns in declaration order.

按名称没有列的匹配"，只有每个表中的列的列表.

There is no "matching" of columns by names, only lists of columns in each table.

因此，查询实际上是:

insert into one(a, b)
    select b from two;

在我看来这是一个错误.

That looks like an error to me.

故事的道德?编写您想要的代码.始终包括列列表，尤其是对于insert语句.所以写:

Moral of the story? Write that code that you intend. Always include columns lists, particularly for insert statements. So write:

insert into one(b)
    select b from two;

这篇关于从另一个具有不同列数的表插入(MySql)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！