本文翻译自:How can I modify the size of column in a mysql table?
I have created a table and accidentally put varchar
length as 300
instead of 65353
. 我创建了一个表,并且意外地将varchar
长度设置为300
而不是65353
。 How can I fix that? 我该如何解决这个问题?
An example would be appreciated. 一个例子将不胜感激。
#1楼
参考:https://stackoom.com/question/5MsC/如何修改mysql表中列的大小
#2楼
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)
#3楼
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 MODIFY
command can do this more concisely. Miky D是正确的, MODIFY
命令可以更简洁地做到这一点。
Re the MEDIUMTEXT
thing: a MySQL row can be only 65535 bytes (not counting BLOB/TEXT columns). 重新启动MEDIUMTEXT
:MySQL行只能是65535字节(不包括BLOB / TEXT列)。 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. 如果您尝试将列更改为太大,使行的总大小为65536或更大,则可能会出错。 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 MEDIUMTEXT
data type. 如果您尝试声明VARCHAR(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)
,MySQL可以做,只要与表中其他列相加的列大小不超过65535。
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