如何修改mysql表中的列的大小?

I have created a table and accidentally put varchar length as 300 instead of 65353. How can I fix that?

我创建了一个表格，不小心将varchar长度设置为300而不是65353。我怎样才能解决这个问题呢?

An example would be appreciated.

请举个例子。

2 个解决方案

#1

470

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)

#2

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是正确的，修改命令可以更简洁地执行此操作。

Re the MEDIUMTEXT thing: 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 MEDIUMTEXT data 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)， 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

#1

470