在表中间插入新列?

When one uses "ALTER TABLE tab ADD col", the new column gets added to the end of the table. For example:

当使用“ALTER TABLE”选项卡添加col时，新列将被添加到表的末尾。例如:

TABLE: TAB
COL_1 COL_2 COL_4

ALTER TABLE TAB ADD COL_3

table will become

表将成为

TABLE: TAB
COL_1 COL_2 COL_4 COL_3

However as the naming of my example columns suggests I'd actually like the table to end up like this:

但是，正如我的示例列的命名所显示的那样，我实际上希望这个表可以这样结束:

TABLE: TAB
COL_1 COL_2 COL_3 COL_4

With COL_3 before COL_4.

与COL_3 COL_4之前。

Besides rebuilding the table from scratch, is there any standard SQL that will get the job done? However if there is no standard SQL, I could still use some vendor dependent solutions for Oracle, but again a standard solution would be best.

除了从头重新构建表之外，是否有任何标准SQL可以完成这项工作?但是，如果没有标准的SQL，我仍然可以为Oracle使用一些与供应商相关的解决方案，但是标准的解决方案还是最好的。

Thanks.

谢谢。

5 个解决方案

#1

By default, columns are only added at the end.

默认情况下，只在末尾添加列。

To insert a column in the middle, you have to drop and recreate the table and all related objects (constraints, indices, defaults, relationships, etc).

要在中间插入一列，必须删除并重新创建表和所有相关对象(约束、索引、默认值、关系等)。

Several tools do this for you, and depending on the size of the table, this may be an intensive operation.

有几个工具可以为您实现这一点，根据表的大小，这可能是一个密集的操作。

You may also consider creating views on the table that display columns in the order of preferrence (overriding the actual order in the table).

您还可以考虑在表中创建显示列的首选项(重写表中实际的顺序)。

#2

It works.

它的工作原理。

ALTER TABLE tablename ADD columnname datatype AFTER columnname;

#3

http://www.orafaq.com/wiki/SQL_FAQ#How_does_one_add_a_column_to_the_middle_of_a_table.3F says it can't be done, and suggests workarounds of renaming the table and doing a create table as select... or (something I am unfamiliar with) "Use the DBMS_REDEFINITION package to change the structure".

说它是不可能完成的，并建议将表重命名为select…或者(我不熟悉的)“使用DBMS_REDEFINITION包更改结构”。

#4

ALTER TABLE TABLENAME ALTER COLUMN COLUMNNAME POSITION X;

改变表TABLENAME改变列柱名位置X;

#5

I know it's old subject (2009) but maybe it will help someone that still looks for an answer. In MySQL, it works 2 add a column anywhere in the table.

我知道这是一个古老的主题(2009)，但它可能会帮助那些仍在寻找答案的人。在MySQL中，它可以在表的任何地方添加一列。

ALTER TABLE `tablename` ADD `column_name1` TEXT NOT NULL AFTER `column_name2`;

This is 2 enter a text column, but u can set whatever properties u want for the new column, just make sure u write them with caps.

这是2输入一个文本列，但是u可以设置你想要的新列的任何属性，只要确保你用大写字母写它们。

I found it with Xampp, MySQL admin, when i used it 2 insert a column in the middle of a MySQL table.

我在Xampp中找到了它，MySQL admin，当我使用它时，在MySQL表中间插入一列。

Hope it helps.

希望它可以帮助。

#1