oracle 有没有办法在Oracle表的指定位置添加列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18809155/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a way to add column at a specified position in Oracle table?
提问by Mistu4u
Consider this inital table I have created in Oracle 10G:
考虑我在 Oracle 10G 中创建的这个初始表：
╔═════════════════════════════════╗
║ CUSTOMER_ID ACC_NO ACC_BALANCE ║
╠═════════════════════════════════╣
║ 100 200 1000 ║
║ 101 150 4000 ║
║ 102 350 2000 ║
║ 103 450 2500 ║
║ 104 550 2200 ║
╚═════════════════════════════════╝
Now I want to add another column customer_nameinto the table. I used:
现在我想customer_name在表中添加另一列。我用了：
ALTER TABLE BANK_ACCOUNT
ADD (CUSTOMER_NAME VARCHAR2(30));
and the column is being inserted as the last column in the table whereas I want the column to be added to the table as the second column. Now the SQL code I mentioned is unable to do so. So how can I add the column at a specified position? Is it even possible in SQL?
并且该列作为表中的最后一列插入，而我希望将该列作为第二列添加到表中。现在我提到的 SQL 代码无法这样做。那么如何在指定位置添加列呢？在 SQL 中甚至可能吗？
回答by paxdiablo
It really doesn't matterwhere the column is physically since selectwill allow you to specify the order (logically) in which they're retrieved.
列的物理位置实际上并不重要，因为select它将允许您指定检索它们的顺序（逻辑上）。
And, if you're using select *which doesn't let you specify the order, you probably shouldn't be. There are precious few situations where you should be doing that (DB analysis tools, for example), most of the time it's better to select the individual columns you want, even if you want them all. This allows you to catch schema changes quickly so you can adapt your programs to them.
而且，如果您使用的是select *which 不允许您指定顺序，那么您可能不应该这样做。在极少数情况下您应该这样做（例如 DB 分析工具），大多数情况下最好选择您想要的单个列，即使您想要它们。这使您可以快速捕获模式更改，以便您可以根据这些更改调整您的程序。
In any case, SQL itself makes no guarantees about the order in which columns are returned unless you explicitlylist them. select *may give them to you in ordinal order today and alphabetic order tomorrow. Even if a particular implementation allows you to do it (by creating a new table with the column in the "right" place and copying the data across, or providing an SQL extension like alter table T insert column C1 before C2), I'd advise against it. It won't be portable to other implementations and I prefer to have my code as portable as practicable.
在任何情况下，SQL 本身都不保证列的返回顺序，除非您明确列出它们。select *可能今天按顺序给你，明天按字母顺序给你。即使特定的实现允许您这样做（通过在“正确”的位置创建一个包含列的新表并复制数据，或提供类似的 SQL 扩展alter table T insert column C1 before C2），我建议您不要这样做。它不能移植到其他实现，我更喜欢让我的代码尽可能可移植。
In addition, beyond what you can specify in SQL, a DBMS should be able to totally control howit stores data. It may be that your primary key is a composite of the seventh and forty-second column and it may be more efficient to have them at the front of the physical records.
此外，除了你可以在SQL中指定，数据库管理系统应该能够完全控制如何它存储的数据。可能您的主键是第 7 和 40 列的组合，将它们放在物理记录的前面可能更有效。
