MySQL - How to increase varchar size of an existing column in a database without breaking existing data?
MysqlMysql Problem Overview
I have a column that is currently varchar(100)
and I want to make it 10000
.
is it as simple as
alter table table_name set column col_name varchar (10000);
I am afraid to corrupt the exiting data. Will I be ok if I run this query? Or should I do I alter
the column another way?
Thanks!
Mysql Solutions
Solution 1 - Mysql
It's safe to increase the size of your varchar column. You won't corrupt your data.
If it helps your peace of mind, keep in mind, you can always run a database backup before altering your data structures.
By the way, correct syntax is:
ALTER TABLE table_name MODIFY col_name VARCHAR(10000)
Also, if the column previously allowed/did not allow nulls, you should add the appropriate syntax to the end of the alter table statement, after the column type.
Solution 2 - Mysql
I normally use this statement:
ALTER TABLE `table_name`
CHANGE COLUMN `col_name` `col_name` VARCHAR(10000);
But, I think SET will work too, never have tried it. :)
Solution 3 - Mysql
I'd like explain the different alter table syntaxes - See the MySQL documentation
For adding/removing defaults on a column:
ALTER TABLE table_name
ALTER COLUMN col_name {SET DEFAULT literal | DROP DEFAULT}
For renaming a column, changing it's data type and optionally changing the column order:
ALTER TABLE table_name
CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]
For changing a column's data type and optionally changing the column order:
ALTER TABLE table_name
MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
Solution 4 - Mysql
For me worked this one:
ALTER TABLE tablename MODIFY fieldname VARCHAR(128) NOT NULL;
Solution 5 - Mysql
I am using mysql and below syntax worked well for me,
ALTER TABLE table_name MODIFY col_name VARCHAR(12);
Solution 6 - Mysql
For me this has worked-
ALTER TABLE table_name ALTER COLUMN column_name VARCHAR(50)
Solution 7 - Mysql
use this syntax: alter table table_name modify column col_name varchar (10000);