SQL 更改 varchar 列的最大长度？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8829053/
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
Changing the maximum length of a varchar column?
提问by Michael A
I'm trying to update the length of a varchar column from 255 characters to 500 without losing the contents. I've dropped and re-created tables before but I've never been exposed to the alter statement which is what I believe I need to use to do this. I found the documentation here: ALTER TABLE (Transfact-SQL)however I can't make heads or tails of it.
我试图在不丢失内容的情况下将 varchar 列的长度从 255 个字符更新为 500 个字符。我之前删除并重新创建了表，但我从未接触过 alter 语句，我相信我需要使用它来执行此操作。我在这里找到了文档：ALTER TABLE (Transfact-SQL)但是我无法理解它。
I have the following so far (essentially nothing unfortunately):
到目前为止，我有以下内容（不幸的是，基本上没有）：
alter table [progennet_dev].PROGEN.LE
alter column UR_VALUE_3
How do I approach this? Is there better documentation for this statement out there (I did some searches for an example statement but came up empty)?
我该如何处理？是否有关于此语句的更好的文档（我搜索了一些示例语句，但结果为空）？
回答by Martin Smith
You need
你需要
ALTER TABLE YourTable ALTER COLUMN YourColumn <<new_datatype>> [NULL | NOT NULL]
But remember to specify NOT NULLexplicitly if desired.
但请记住，NOT NULL如果需要，请明确指定。
ALTER TABLE YourTable ALTER COLUMN YourColumn VARCHAR (500) NOT NULL;
If you leave it unspecified as below...
如果您将其保留为未指定如下...
ALTER TABLE YourTable ALTER COLUMN YourColumn VARCHAR (500);
Then the column will default to allowing nulls even if it was originally defined as NOT NULL. i.e. omitting the specification in an ALTER TABLE ... ALTER COLUMNis always treated as.
然后该列将默认允许空值，即使它最初定义为NOT NULL. 即在 an 中省略规范ALTER TABLE ... ALTER COLUMN总是被视为。
ALTER TABLE YourTable ALTER COLUMN YourColumn VARCHAR (500) NULL;
This behaviour is different from that used for new columns created with ALTER TABLE(or at CREATE TABLEtime). There the default nullability depends on the ANSI_NULL_DFLTsettings.
此行为不同于用于ALTER TABLE（或在CREATE TABLE时间）创建的新列的行为。默认的可空性取决于ANSI_NULL_DFLT设置。
回答by Mitch Wheat
Increasing column size with ALTERwill not lose any data:
增加列大小ALTER不会丢失任何数据：
alter table [progennet_dev].PROGEN.LE
alter column UR_VALUE_3 varchar(500)
As @Martin points out, remember to explicitly specify NULL | NOT NULL
正如@Martin 指出的，记得明确指定 NULL | NOT NULL
回答by anonymous
You can use modify:
您可以使用modify：
ALTER TABLE `table name`
modify COLUMN `column name` varchar("length");
回答by Rahul Sharma
Using Maria-DB and DB-Navigator tool inside IntelliJ, MODIFY Columnworked for me instead of Alter Column
在 IntelliJ 中使用 Maria-DB 和 DB-Navigator 工具，MODIFY Column对我有用，而不是Alter Column
回答by Jedo
ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME VARCHAR(40);
Late to the question - but I am using Oracle SQL Developerand @anonymous's answer was the closest but kept receiving syntax errors until I edited the query to this.
问题迟到了 - 但我使用的是Oracle SQL Developer，@anonymous 的答案是最接近的，但一直收到语法错误，直到我将查询编辑为此。
Hope this helps someone
希望这有助于某人
回答by Mukesh
This worked for me in db2:
这在 db2 中对我有用：
alter table "JOBS" alter column "JOB_TITLE" set data type varchar(30);
回答by vlad
As an alternative, you can save old data and create a new table with new parameters.
作为替代方案，您可以保存旧数据并使用新参数创建新表。
In SQL Server Management Studio: "your database" => task => generatescripts => select specific database object => "your table" => advanced => types of data to script - schema and data => generate
在 SQL Server Management Studio 中：“你的数据库”=> 任务 => 生成脚本 => 选择特定的数据库对象 =>“你的表”=> 高级 => 脚本的数据类型 - 架构和数据 => 生成
Personally, I did so.
就我个人而言，我是这样做的。
回答by R. Gurung
I was also having above doubt, what worked for me is
我也有上述疑问，对我有用的是
ALTER TABLE `your_table` CHANGE `property` `property`
VARCHAR(whatever_you_want) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
回答by hammadshahir
For MariaDB, use modify column:
对于 MariaDB，请使用修改列：
ALTER TABLE table_name MODIFY COLUMN column_name VARCHAR (500);
It will work.
它会起作用。
