在 Oracle 中，是否可以将列“插入”到表中？

In Oracle, is it possible to "insert" a column into a table?

提问by larf311

When adding a column to an existing table, Oracle always puts the column at the end of the table. Is it possible to tell Oracle where it should appear in the table? If so, how?

向现有表中添加列时，Oracle 总是将该列放在表的末尾。是否可以告诉 Oracle 它应该出现在表中的哪个位置？如果是这样，如何？

回答by Michael Todd

The location of the column in the table should be unimportant (unless there are "page sizes" to consider, or whatever Oracle uses to actually store the data). What is more important to the consumer is how the results are called, i.e. the Select statement.

表中列的位置应该不重要（除非需要考虑“页面大小”，或者 Oracle 用于实际存储数据的任何内容）。对消费者来说更重要的是如何调用结果，即 Select 语句。

回答by Alex Reitbort

rename YOUR_ORIGINAL_TABLE as YOUR_NEW_TABLE;

将 YOUR_ORIGINAL_TABLE 重命名为 YOUR_NEW_TABLE；

create table YOUR_ORIGINAL_TABLE nologging /* or unrecoverable */ as select Column1, Column2, NEW_COLUMN, Column3 from YOUR_NEW_TABLE;

create table YOUR_ORIGINAL_TABLE nologging /* or unrecoverable */ as select Column1, Column2, NEW_COLUMN, Column3 from YOUR_NEW_TABLE;

Drop table YOUR_NEW_TABLE;

删除表 YOUR_NEW_TABLE;

Select * From YOUR_ORIGINAL_TABLE; <<<<< now you will see the new column in the middle of the table.

选择 * From YOUR_ORIGINAL_TABLE; <<<<< 现在您将看到表格中间的新列。

But why would you want to do it? It's seems illogical. You should never assume column ordering and just use named column list if column order is important.

但是你为什么要这样做呢？好像不合逻辑 如果列顺序很重要，您永远不应该假设列排序并且只使用命名列列表。

回答by user14834

Why does the order of the columns matter? You can always alter it in your select statement?

为什么列的顺序很重要？你总是可以在你的选择语句中改变它吗？

There's an advantage to adding new columns at the end of the table. If there's code that naively does a "SELECT *" and then parses the fields in order, you won't be breaking old code by adding new columns at the end. If you add new columns in the middle of the table, then old code may be broken.

在表的末尾添加新列有一个优势。如果有代码天真地执行“SELECT *”，然后按顺序解析字段，则不会通过在末尾添加新列来破坏旧代码。如果在表中间添加新列，则旧代码可能会损坏。

At one job, I had a DBA who was super-anal about "Never do 'SELECT *'". He insisted that you always write out the specific fields.

在一份工作中，我有一位 DBA，他对“永远不要做 'SELECT *'”非常感兴趣。他坚持要求你总是写出特定的字段。

回答by Gary Myers

Bear in mind that, under the tables, all the data in the table records are glued together. Adding a column to the end of a table [if it is nullable or (in later versions) not null with a default] just means a change to the table's metadata. Adding a column in the middle would require re-writing every record in that table to add the appropriate value (or markers) for that column. In some cases, that might mean the records take up more room on the blocks and some records need to be migrated. In short, it's a VAST amount of IO effort for a table of any real size.

请记住，在表格下方，表格记录中的所有数据都粘在一起。在表的末尾添加一列[如果它可以为空或（在更高版本中）默认不为空] 仅意味着对表元数据的更改。在中间添加一列需要重写该表中的每条记录，以便为该列添加适当的值（或标记）。在某些情况下，这可能意味着记录在块上占用更多空间，并且某些记录需要迁移。简而言之，对于任何实际大小的表，这都是大量的 IO 工作。

You can always create a view over the table that has the columns in the preferred order and use that view in a DML statement just as you would the table

您始终可以在具有首选顺序的列的表上创建视图，并在 DML 语句中使用该视图，就像使用表一样

回答by Edwin

What I normally do is:

我通常做的是：

1. Rename the old table.
2. Create the new table with columns in the right order.
3. Create the constraints for that new table.
4. Populate with data:Insert into new_table select * from renamed table.

1. 重命名旧表。
2. 以正确的顺序创建包含列的新表。
3. 为该新表创建约束。
4. 填充数据：插入 new_table 从重命名的表中选择 *。

回答by tvanfosson

I don't think that this can be done without saving the data to a temporary table, dropping the table, and recreating it. On the other hand, it really shouldn't matter where the column is. As long as you specify the columns you are retrieving in your select statement, you can order them however you want.

我认为如果不将数据保存到临时表、删除该表并重新创建它，就无法做到这一点。另一方面，列在哪里真的不重要。只要您在 select 语句中指定要检索的列，就可以根据需要对它们进行排序。

回答by SqlRyan

I don't believe so - SQL Server doesn't allow these either. The method I always have to use is:

我不相信 - SQL Server 也不允许这些。我总是必须使用的方法是：

1. Create new table that looks right (including additional column
2. Begin transaction
3. select all data from old table into new one
4. Drop old table
5. Rename new table
6. Commit transaction.

1. 创建看起来正确的新表（包括附加列
2. 开始交易
3. 选择旧表中的所有数据到新表中
4. 放下旧桌子
5. 重命名新表
6. 提交交易。

Not exactly pretty, but gets the job done.

不完全漂亮，但可以完成工作。

回答by Nick

No, its not possible via an "ALTER TABLE" statement. However, you could create a new table with the same definition as your current one, albeit with a different name, with the columns in the correct order in the way you want them. Copy the data into the new table. Drop the old table. Rename the new table to match the old table name.

不，它不可能通过“ALTER TABLE”语句实现。但是，您可以创建一个与当前表具有相同定义的新表，尽管名称不同，但列的顺序正确且符合您的需要。将数据复制到新表中。放下旧桌子。重命名新表以匹配旧表名。

Tom Kyte has an article on this on AskTom link text

Tom Kyte 在 AskTom链接文本上有一篇关于此的文章

回答by Barnaby

1) Ok so you can't do it directly. We don't need post after post saying the same thing, do we?

1）好的，所以你不能直接做。我们不需要一个又一个的帖子说同样的话，对吗？

2) Ok so the order of columns in a table doesn't technically matter. But that's not the point, the original question simply asked if you could or couldn't be done. Don't presume that you know everybody else's requirements. Maybe they have a table with 100 columns that is currently being queried using "SELECT * ..." inside some monstrously hacked together query that they would just prefer not to try to untangle, let alone replace "*" with 100 column names. Or maybe they are just anal about the order of things and like to have related fields next to each other when browsing schema with, say SQL Developer. Maybe they are dealing with non-technical staff that won't know to look at the end of a list of 100 columns when, logically, it should be somewhere near the beginning.

2）好的，所以表中列的顺序在技术上并不重要。但这不是重点，最初的问题只是问您是否可以完成。不要假设你知道其他人的要求。也许他们有一个包含 100 列的表，目前正在使用“SELECT * ...”在一些骇人听闻的组合查询中进行查询，他们只是不想尝试解开，更不用说用 100 个列名替换“*”了。或者也许他们只是对事物的顺序进行分析，并且在浏览模式时喜欢将相关字段彼此相邻，例如 SQL Developer。也许他们正在与非技术人员打交道，他们不知道查看 100 列列表的末尾，而从逻辑上讲，它应该位于开头附近。

Nothing is more irritating than asking an honest question and getting an answer that says: "you shouldn't be doing that". It's MY job, not YOURS! Please don't tell me how to do my job. Just help if you can. Thanks!

没有什么比问一个诚实的问题并得到一个说“你不应该那样做”的回答更令人恼火的了。这是我的工作，不是你的！请不要告诉我如何做我的工作。如果可以，请帮忙。谢谢！

Ok... sorry for the rant. Now...at www.orafaq.com it suggests this workaround.

好吧……抱歉我的咆哮。现在...在 www.orafaq.com 上，它建议了这种解决方法。

First suppose you have already run:

首先假设您已经运行：

CREATE TABLE tab1 ( col1 NUMBER );

创建表 tab1 ( col1 NUMBER );

Now say you want to add a column named "col2", but you want them ordered "col2", "col1" when doing a "SELECT * FROM tbl1;"

现在假设您想添加一个名为“col2”的列，但是您希望在执行“SELECT * FROM tbl1;”时它们被排序为“col2”、“col1”。

The suggestion is to run:

建议是运行：

ALTER TABLE tab1 ADD (col2 DATE); RENAME tab1 TO tab1_old; CREATE TABLE tab1 AS SELECT 0 AS col1, col1 AS col2 FROM tab1_old;

ALTER TABLE tab1 添加（col2 日期）；将 tab1 重命名为 tab1_old；CREATE TABLE tab1 AS SELECT 0 AS col1, col1 AS col2 FROM tab1_old;

I found this to be incredibly misleading. First of all, you're filling "col1" with zero's so, if you had any data, then you are losing it by doing this. Secondly, it's actually renaming "col1" to "col2" and fails to mention this. So, here's my example, hopefully it's a little clearer:

我发现这是令人难以置信的误导。首先，您用零填充“col1”，因此，如果您有任何数据，那么这样做就会丢失它。其次，它实际上是将“col1”重命名为“col2”而没有提及这一点。所以，这是我的例子，希望它更清楚一点：

Suppose you have a table that was created with the following statement:

假设您有一个使用以下语句创建的表：

CREATE TABLE users (first_name varchar(25), last_name varchar(25));

创建表用户 (first_name varchar(25), last_name varchar(25));

Now say you want to insert middle_name in between first_name and last_name. Here's one way:

现在假设您想在 first_name 和 last_name 之间插入 middle_name。这是一种方法：

ALTER TABLE users ADD middle_name varchar(25); RENAME users TO users_tmp; CREATE TABLE users AS SELECT first_name, middle_name, last_name FROM users_tmp; /* and for good measure... */ DROP TABLE testusers_tmp;

ALTER TABLE users ADD middle_name varchar(25); 将用户重命名为 users_tmp；CREATE TABLE users AS SELECT first_name, middle_name, last_name FROM users_tmp; /* 为了更好的衡量... */ DROP TABLE testusers_tmp;

Note that middle_name will default to NULL (implied by the ALTER TABLE statement). You can alternatively set a different default value in the CREATE TABLE statement like so:

请注意， middle_name 将默认为 NULL（由 ALTER TABLE 语句暗示）。您也可以在 CREATE TABLE 语句中设置不同的默认值，如下所示：

CREATE TABLE users AS SELECT first_name, 'some default value' AS middle_name, last_name FROM users_tmp;

CREATE TABLE users AS SELECT first_name, 'some default value' AS middle_name, last_name FROM users_tmp;

This trick could come in handy if you're adding a date field with a default of sysdate, but you want all of the existing records to have some other (e.g. earlier) date value.

如果您添加一个默认为 sysdate 的日期字段，这个技巧可能会派上用场，但您希望所有现有记录都具有其他（例如更早的）日期值。