如何使用 SQL 重命名数据库表中的列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/174582/
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
How do I rename a column in a database table using SQL?
提问by MetroidFan2002
If I wish to simply rename a column (not change its type or constraints, just its name) in an SQL database using SQL, how do I do that? Or is it not possible?
如果我希望使用 SQL 简单地重命名 SQL 数据库中的列（不更改其类型或约束，只更改其名称），我该怎么做？或者不可能？
This is for any database claiming to support SQL, I'm simply looking for an SQL-specific query that will work regardless of actual database implementation.
这适用于任何声称支持 SQL 的数据库，我只是在寻找一个特定于 SQL 的查询，无论实际的数据库实现如何，它都可以工作。
采纳答案by bortzmeyer
On PostgreSQL (and many other RDBMS), you can do it with regular ALTER TABLEstatement:
在 PostgreSQL（以及许多其他 RDBMS）上，您可以使用正则ALTER TABLE语句来完成：
=> SELECT * FROM Test1;
id | foo | bar
----+-----+-----
2 | 1 | 2
=> ALTER TABLE Test1 RENAME COLUMN foo TO baz;
ALTER TABLE
=> SELECT * FROM Test1;
id | baz | bar
----+-----+-----
2 | 1 | 2
回答by Galwegian
回答by jaspher chloe
In MySQL, the syntax is ALTER TABLE ... CHANGE:
在 MySQL 中，语法是ALTER TABLE ... CHANGE：
ALTER TABLE <table_name> CHANGE <column_name> <new_column_name> <data_type> ...
Note that you can't just rename and leave the type and constraints as is; you must retype the data type and constraints after the new name of the column.
请注意，您不能只是重命名并将类型和约束保持原样；您必须在列的新名称后重新键入数据类型和约束。
回答by Kunal Relan
I think this is the easiest way to change column name.
我认为这是更改列名的最简单方法。
SP_RENAME 'TABLE_NAME.OLD_COLUMN_NAME','NEW_COLUMN_NAME'
回答by Shadow Man
Unfortunately, for a database independent solution, you will need to know everything about the column. If it is used in other tables as a foreign key, they will need to be modified as well.
不幸的是，对于独立于数据库的解决方案，您需要了解有关列的所有信息。如果它在其他表中用作外键，则它们也需要修改。
ALTER TABLE MyTable ADD MyNewColumn OLD_COLUMN_TYPE;
UPDATE MyTable SET MyNewColumn = MyOldColumn;
-- add all necessary triggers and constraints to the new column...
-- update all foreign key usages to point to the new column...
ALTER TABLE MyTable DROP COLUMN MyOldColumn;
For the very simplest of cases (no constraints, triggers, indexes or keys), it will take the above 3 lines. For anything more complicated it can get very messy as you fill in the missing parts.
对于最简单的情况（没有约束、触发器、索引或键），它将采用上述 3 行。对于任何更复杂的事情，当您填写缺失的部分时，它会变得非常混乱。
However, as mentioned above, there are simpler database specific methods if you know which database you need to modify ahead of time.
但是，如上所述，如果您提前知道需要修改哪个数据库，则有更简单的特定于数据库的方法。
回答by Jonathan Leffler
In Informix, you can use:
在 Informix 中，您可以使用：
RENAME COLUMN TableName.OldName TO NewName;
This was implemented before the SQL standard addressed the issue - if it is addressed in the SQL standard. My copy of the SQL 9075:2003 standard does not show it as being standard (amongst other things, RENAME is not one of the keywords). I don't know whether it is actually in SQL 9075:2008.
这是在 SQL 标准解决问题之前实现的 - 如果它在 SQL 标准中解决。我的 SQL 9075:2003 标准副本并未将其显示为标准（除其他外，RENAME 不是关键字之一）。我不知道它是否真的在 SQL 9075:2008 中。
回答by Bimzee
In sql server you can use
在 sql server 中你可以使用
exec sp_rename '<TableName.OldColumnName>','<NewColumnName>','COLUMN'
or
或者
sp_rename '<TableName.OldColumnName>','<NewColumnName>','COLUMN'
回答by Prabhat Kumar Yadav
You can use the following command to rename the column of any table in SQL Server:
您可以使用以下命令重命名 SQL Server 中任何表的列：
exec sp_rename 'TableName.OldColumnName', 'New colunmName'
回答by Paul Tomblin
ALTER TABLE is standard SQL. But it's not completely implemented in many database systems.
ALTER TABLE 是标准 SQL。但它并没有在许多数据库系统中完全实现。
回答by Rob
The standard would be ALTER TABLE, but that's not necessarily supported by every DBMS you're likely to encounter, so if you're looking for an all-encompassing syntax, you may be out of luck.
标准是ALTER TABLE，但不一定得到您可能遇到的每个 DBMS 的支持，因此如果您正在寻找一种包罗万象的语法，那么您可能不走运。
