70

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?

Wai Ha Lee
  • 8,598
  • 83
  • 57
  • 92
Genadinik
  • 18,153
  • 63
  • 185
  • 284
  • Don't forget to include any other attributes that the column already has! For example `default null` or whatever displays when you do a `show create table someTableName` – Kzqai Nov 20 '13 at 01:01
  • 3
    possible duplicate of [How can I modify the size of column in a mysql table?](http://stackoverflow.com/questions/1279568/how-can-i-modify-the-size-of-column-in-a-mysql-table) – tripleee Mar 04 '15 at 06:06

6 Answers6

127

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.

Paolo
  • 20,112
  • 21
  • 72
  • 113
Lynn Crumbling
  • 12,985
  • 8
  • 57
  • 95
  • 9
    Use `show create table someTableName` to determine the additional features of the column before altering it! – Kzqai Nov 20 '13 at 01:02
  • 1
    Also, be careful with your case of the column, as it'll change it to whatever case you give it! I was just bitten by that fun feature. – Kzqai Nov 20 '13 at 01:12
  • For safe schema changes, we roll Percona w/ `pt-online-schema-change`. It makes a copy of the table with the desired change, then renames the copy to the original table's name. Obv you can do that yourself, but it has a bunch of other nice safety checks, too. – einnocent Nov 21 '14 at 00:12
  • This did not work: alter table my_table modify my_col varchar(100); This did work: alter table my_table change column my_col my_col varchar(100) not null; MySQL 5.6 – William T. Mallard Jul 06 '15 at 01:50
  • @WilliamT.Mallard Sounds like you had your syntax wrong when you entered it. Did you accidentally double-up the field name in an attempt to perform a rename as well? In that case, yes, you need to use `CHANGE`. – Lynn Crumbling Jul 06 '15 at 12:03
  • I suppose it might be a MySQL Workbench error. The command "alter table my_table modify my_col varchar(100);" gives a squiggly red line under "varchar" and hovering gives the error message "Syntax error: unexpected 'varchar' (varchar)". – William T. Mallard Jul 06 '15 at 21:23
  • Currently with mysql 8, running `VARCHAR(10000)` gives this error in MySQL Workbench: `Error Code: 1071. Specified key was too long; max key length is 3072 bytes`. Using smaller values (for example `200`) worked. – Paolo Jan 25 '21 at 12:17
  • 1
    @Paolo From MySQL docs: `The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.` I'm guessing you're already using a large portion (65535-3072=62463 used) of the space, and 3072 is what's left. – Lynn Crumbling Jan 25 '21 at 23:39
64

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. :)

hjpotter92
  • 78,589
  • 36
  • 144
  • 183
  • 4
    Just be careful with your casing, as mysql will be happy to change the case of the column for you. – Kzqai Nov 20 '13 at 01:12
  • 3
    In postgres it's `ALTER TABLE \`tablename\` ALTER COLUMN \`columnname\` TYPE VARCHAR(64);` I mention it because a search not mentioning mysql got me here – crackpotHouseplant Jun 10 '16 at 21:58
24

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]
Mike Causer
  • 8,196
  • 2
  • 43
  • 63
  • 2
    +1 for giving a much more documented response with the use of different commands (`alter`, `change` and `modify`) in different situations. – Francisco Zarabozo Mar 23 '15 at 07:39
1

For me worked this one:

ALTER TABLE tablename MODIFY fieldname VARCHAR(128) NOT NULL;

-3

I am using mysql and below syntax worked well for me,

ALTER TABLE table_name MODIFY col_name VARCHAR(12);
sam
  • 5
  • 6
-3

For me this has worked-

ALTER TABLE table_name ALTER COLUMN column_name VARCHAR(50)

Tejas
  • 39
  • 1