MySQL 如何修改mysql表中列的大小？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1279568/
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 can I modify the size of column in a mysql table?
提问by vehomzzz
I have created a table and accidentally put varcharlength as 300instead of 65353. How can I fix that?
我创建了一个表，不小心把varcharlength300换成了65353. 我该如何解决？
An example would be appreciated.
一个例子将不胜感激。
回答by Mike Dinescu
Have you tried this?
你试过这个吗？
ALTER TABLE <table_name> MODIFY <col_name> VARCHAR(65353);
This will change the col_name's type to VARCHAR(65353)
这会将col_name的类型更改为VARCHAR(65353)
回答by Bill Karwin
ALTER TABLE <tablename> CHANGE COLUMN <colname> <colname> VARCHAR(65536);
You have to list the column name twice, even if you aren't changing its name.
您必须列出列名两次，即使您没有更改其名称。
Note that after you make this change, the data type of the column will be MEDIUMTEXT.
请注意，进行此更改后，列的数据类型将为MEDIUMTEXT.
Miky D is correct, the MODIFYcommand can do this more concisely.
Miky D 是正确的，MODIFY命令可以更简洁地做到这一点。
Re the MEDIUMTEXTthing: a MySQL row can be only 65535 bytes (not counting BLOB/TEXT columns). If you try to change a column to be too large, making the total size of the row 65536 or greater, you may get an error. If you try to declare a column of VARCHAR(65536)then it's too large even if it's the only column in that table, so MySQL automatically converts it to a MEDIUMTEXTdata type.
重提一下MEDIUMTEXT：一个 MySQL 行只能是 65535 字节（不包括 BLOB/TEXT 列）。如果您尝试将列更改为太大，使行的总大小为 65536 或更大，您可能会收到错误消息。如果您尝试声明一列，VARCHAR(65536)那么即使它是该表中唯一的列，它也太大了，因此 MySQL 会自动将其转换为MEDIUMTEXT数据类型。
mysql> create table foo (str varchar(300));
mysql> alter table foo modify str varchar(65536);
mysql> show create table foo;
CREATE TABLE `foo` (
`str` mediumtext
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
I misread your original question, you want VARCHAR(65353), which MySQL can do, as long as that column size summed with the other columns in the table doesn't exceed 65535.
我误读了您的原始问题，您想要VARCHAR(65353)，只要该列大小与表中的其他列相加不超过 65535，MySQL 就可以做到。
mysql> create table foo (str1 varchar(300), str2 varchar(300));
mysql> alter table foo modify str2 varchar(65353);
ERROR 1118 (42000): Row size too large.
The maximum row size for the used table type, not counting BLOBs, is 65535.
You have to change some columns to TEXT or BLOBs
