blocks|key|1512016|text|在PostgreSQL+(和许多其他关系型数据库管理系统)上，您可以使用常规的ALTER+TABLE语句来完成此操作：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1512017|=>+SELECT+*+FROM+Test1;
+id+%7C+foo+%7C+bar+
----%2B-----%2B-----
++2+%7C+++1+%7C+++2

=>+ALTER+TABLE+Test1+RENAME+COLUMN+foo+TO+baz;
ALTER+TABLE

=>+SELECT+*+FROM+Test1;
+id+%7C+baz+%7C+bar+
----%2B-----%2B-----
++2+%7C+++1+%7C+++2|code-block|syntax|javascript|1512018|entityMap^0|13|B|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

On PostgreSQL (and many other RDBMS), you can do it with regular <code>ALTER TABLE</code> statement:

<pre><code>=&gt; SELECT * FROM Test1;
 id | foo | bar 
----+-----+-----
 2 | 1 | 2

=&gt; ALTER TABLE Test1 RENAME COLUMN foo TO baz;
ALTER TABLE

=&gt; SELECT * FROM Test1;
 id | baz | bar 
----+-----+-----
 2 | 1 | 2
</code></pre>

blocks|key|1727064|text|特别针对SQL+Server，请使用sp_rename|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1727065|USE+AdventureWorks;
GO
EXEC+sp_rename+'Sales.SalesTerritory.TerritoryID',+'TerrID',+'COLUMN';
GO|code-block|syntax|javascript|1727066|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms188351.aspx^0|I|9|I|9|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@$9|V|A|W|1|X]]|E|$]]|$1|F|3|G|5|H|7|Y|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]]]

Specifically for SQL Server, use <a href="http://msdn.microsoft.com/en-us/library/ms188351.aspx" rel="noreferrer"><code>sp_rename</code></a>

<pre><code>USE AdventureWorks;
GO
EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';
GO
</code></pre>

blocks|key|1512251|text|在MySQL中，语法为ALTER+TABLE+...+CHANGE|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1512252|ALTER+TABLE+<table_name>+CHANGE+<column_name>+<new_column_name>+<data_type>+...|code-block|syntax|javascript|1512253|请注意，不能只是重命名并保持类型和约束不变；必须在列的新名称后重新键入数据类型和约束。|1512254|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/en/alter-table.html^0|B|M|B|M|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@$9|X|A|Y|1|Z]]|E|$]]|$1|F|3|G|5|H|7|10|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|11|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]]]

In MySQL, the syntax is <a href="http://dev.mysql.com/doc/en/alter-table.html"><code>ALTER TABLE ... CHANGE</code></a>:

<pre><code>ALTER TABLE &lt;table_name&gt; CHANGE &lt;column_name&gt; &lt;new_column_name&gt; &lt;data_type&gt; ...
</code></pre>

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.

blocks|key|1512193|text|不幸的是，对于独立于数据库的解决方案，您需要了解列的所有信息。如果它在其他表中用作外键，则也需要对其进行修改。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1512194|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;|code-block|syntax|javascript|1512195|对于最简单的情况(没有约束、触发器、索引或键)，它将采用上述3行代码。对于任何更复杂的东西，当你填充缺失的部分时，它可能会变得非常混乱。|1512196|但是，如上所述，如果您知道需要提前修改哪个数据库，则有更简单的数据库特定方法。|1512197|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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.

<pre><code>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;
</code></pre>

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.

However, as mentioned above, there are simpler database specific methods if you know which database you need to modify ahead of time.

blocks|key|1512298|text|我认为这是更改列名的最简单方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1512299|SP_RENAME+'TABLE_NAME.OLD_COLUMN_NAME','NEW_COLUMN_NAME'|code-block|syntax|javascript|1512300|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I think this is the easiest way to change column name.

<pre><code>SP_RENAME 'TABLE_NAME.OLD_COLUMN_NAME','NEW_COLUMN_NAME'
</code></pre>

blocks|key|1727199|text|在Informix中，您可以使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1727200|RENAME+COLUMN+TableName.OldName+TO+NewName;|code-block|syntax|javascript|1727201|这是在SQL标准解决这个问题之前实现的-如果它是在SQL标准中解决的话。我的SQL9075:2003标准的副本没有将其显示为标准的(其中，重命名不是关键字之一)。我不知道它是否真的在SQL9075:2008中。|1727202|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

In Informix, you can use:

<pre><code>RENAME COLUMN TableName.OldName TO NewName;
</code></pre>

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.

blocks|key|1512311|text|在sql+server中，您可以使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1512312|exec+sp_rename+'<TableName.OldColumnName>','<NewColumnName>','COLUMN'|code-block|syntax|javascript|1512313|或|1512314|sp_rename+'<TableName.OldColumnName>','<NewColumnName>','COLUMN'|1512315|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

In sql server you can use 

<pre><code>exec sp_rename '&lt;TableName.OldColumnName&gt;','&lt;NewColumnName&gt;','COLUMN'
</code></pre>

or

<pre><code>sp_rename '&lt;TableName.OldColumnName&gt;','&lt;NewColumnName&gt;','COLUMN'
</code></pre>

blocks|key|1727472|text|您可以使用以下命令重命名SQL+Server中任何表的列：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1727473|exec+sp_rename+'TableName.OldColumnName',+'New+colunmName'|code-block|syntax|javascript|1727474|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can use the following command to rename the column of any table in SQL Server:

<pre><code>exec sp_rename 'TableName.OldColumnName', 'New colunmName'
</code></pre>

blocks|key|1512060|text|ALTER+TABLE是标准SQL。但它并没有在许多数据库系统中完全实现。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1512061|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

ALTER TABLE is standard SQL. But it's not completely implemented in many database systems.

blocks|key|1727255|text|标准应该是ALTER+TABLE，但并不是您可能遇到的每个DBMS都支持它，所以如果您正在寻找一个包罗万象的语法，那么您可能就不走运了。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1727256|entityMap^0|5|B|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

The standard would be <code>ALTER TABLE</code>, 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.

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?

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.

How do I rename a column in a database table using SQL?

数据库管理系统

关系型数据库

数据库管理

SQL数据库

SQL Server

PostgreSQL

数据库

MySQL

如果我希望使用SQL简单地重命名SQL数据库中的列(不更改它的类型或约束，只重命名它的名称)，我该怎么做呢？或者这是不可能的？这是对于任何声称支持SQL的数据库，我只是寻找一个特定于SQL的查询，无论实际的数据库实现如何，它都可以工作。

问如何使用SQL重命名数据库表中的列？
EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用SQL重命名数据库表中的列？EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用SQL重命名数据库表中的列？
EN