tags:

views:

718

answers:

3

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

thansk

+4  A: 

Have you tried this?

ALTER TABLE <table_name> MODIFY <col_name> VARCHAR(65536)

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

Miky Dinescu
+1 that is exactly what I needed... thx...
vehomzzz
glad to help. @Bill suggested using CHANGE instead of MODIFY which will work but CHANGE also renames the column so you have to put the column name twice..
Miky Dinescu
@Miky D good catch, I haven't noticed CHANGE/MODIFY...
vehomzzz
+3  A: 
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
Bill Karwin
+1 you're guys have the same answer. what does it mean data type of the column will be MEDIUMTEXT? thx
vehomzzz
@Bill: CHANGE is generally used to rename a column and change it's data type. MODIFY will only change the column's data type
Miky Dinescu
A: 

ALTER TABLE tablename CHANGE columnname VARCHAR(65536);

Nick Gotch