How can I modify the size of column in a MySQL table?

Question

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

An example would be appreciated.

Answer 1

Have you tried this?

ALTER TABLE <table_name> MODIFY <col_name> VARCHAR(65353);

This will change the col_name's type to VARCHAR(65353)

Answer 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.

---

Miky D is correct, the MODIFY command can do this more concisely.

---

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.

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.

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

Answer 3

ALTER TABLE {table_name} MODIFY [COLUMN] {column_name} {column_type} {defaults and/or not-null};

(Including COLUMN is optional.)

Note: if your column was created with NOT NULL etc. you may need to specify those in the MODIFY statement to avoid losing them.