MySQL Alter Table - Modify Column

Last updated: October 19, 2026Oct 19, 2026

Columns sometimes need to change after a table is in use: a name that's too short, a price that needs more digits, a date stored as text, or a column that should never be empty. MySQL changes an existing column's definition with ALTER TABLE … MODIFY COLUMN (and its relatives CHANGE and ALTER COLUMN), converting the existing data as it goes.

1. Syntax

ALTER TABLE table_name
  MODIFY [COLUMN] column_name new_definition [FIRST | AFTER other_column];

2. Starting Table

DESCRIBE items;

Result: 6 rows

Table: items (3 rows)

3. Increasing a Column's Length

Item names are limited to 20 characters, which is too short:

INSERT INTO items VALUES (4, 'Ergonomic Keyboard Pro', 2499, 20, 'Accessories', '2026-02-10');

ERROR 1406 (22001): Data too long for column 'name' at row 1

ALTER TABLE items MODIFY COLUMN name VARCHAR(80);
INSERT INTO items VALUES (4, 'Ergonomic Keyboard Pro', 2499, 20, 'Accessories', '2026-02-10');
SELECT item_id, name FROM items WHERE item_id = 4;

Result: 1 row

4. Changing Numeric Precision

DECIMAL(6,2) can't store 10,000 or more:

UPDATE items SET price = 12999 WHERE item_id = 2;

ERROR 1264 (22003): Out of range value for column 'price' at row 1

ALTER TABLE items MODIFY price DECIMAL(10,2) NOT NULL;
UPDATE items SET price = 12999 WHERE item_id = 2;
SELECT item_id, price FROM items;

Result: 4 rows

5. Making a Column NOT NULL

Changing to NOT NULL fails while NULLs exist (in strict mode):

ALTER TABLE items MODIFY category VARCHAR(30) NOT NULL;

ERROR 1138 (22004): Invalid use of NULL value

Fill the NULLs first, then modify:

UPDATE items SET category = 'Uncategorised' WHERE category IS NULL;
ALTER TABLE items MODIFY category VARCHAR(30) NOT NULL DEFAULT 'Uncategorised';
SELECT item_id, name, category FROM items;

Result: 4 rows

6. Converting a Data Type

The added_on column holds dates as text. Convert it to a real DATE; MySQL converts each value:

ALTER TABLE items MODIFY added_on DATE;
SELECT item_id, added_on, DAYNAME(added_on) AS weekday FROM items;

Result: 4 rows

If any value can't be converted, the whole statement fails and the table is left unchanged:

CREATE TABLE legacy (id INT PRIMARY KEY, amount VARCHAR(10));
INSERT INTO legacy VALUES (1, '120'), (2, '75.5'), (3, 'N/A');
ALTER TABLE legacy MODIFY amount DECIMAL(8,2);

Query OK, 0 rows affected
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0
ERROR 1366 (HY000): Incorrect DECIMAL value: '0' for column '' at row -1

Find and fix the bad rows first:

SELECT id, amount FROM legacy WHERE amount NOT REGEXP '^[0-9]+(\\.[0-9]+)?$';
UPDATE legacy SET amount = NULL WHERE id = 3;
ALTER TABLE legacy MODIFY amount DECIMAL(8,2);
SELECT * FROM legacy;

Result: 1 row

Result: 3 rows

7. Shrinking a Column Can Lose Data

Making a column smaller than its data is refused in strict mode, protecting you from truncation:

ALTER TABLE items MODIFY name VARCHAR(10);

ERROR 1406 (22001): Data too long for column 'name' at row 2

SELECT MAX(CHAR_LENGTH(name)) AS longest_name FROM items;

Check the longest value before shrinking

8. Setting or Dropping a Default with ALTER COLUMN

To change only the default, ALTER COLUMN is simpler and instant:

ALTER TABLE items ALTER COLUMN qty SET DEFAULT 0;
INSERT INTO items (item_id, name, price, category) VALUES (5, 'Mouse Pad', 199, 'Accessories');
SELECT item_id, name, qty FROM items WHERE item_id = 5;

ALTER TABLE items ALTER COLUMN qty DROP DEFAULT;

Result: 1 row

9. Moving a Column

ALTER TABLE items MODIFY category VARCHAR(30) NOT NULL DEFAULT 'Uncategorised' AFTER name;
SHOW COLUMNS FROM items;

Result: 6 rows

10. CHANGE COLUMN: Rename and Modify Together

ALTER TABLE items CHANGE COLUMN qty stock_qty INT UNSIGNED NOT NULL DEFAULT 0;
SHOW COLUMNS FROM items LIKE 'stock%';

Result: 1 row

For a rename only, RENAME COLUMN is safer because you can't accidentally drop attributes; see MySQL Rename Column.

11. Several Changes in One Statement

ALTER TABLE items
  MODIFY name VARCHAR(100) NOT NULL,
  MODIFY price DECIMAL(12,2) NOT NULL DEFAULT 0.00;
SHOW COLUMNS FROM items WHERE Field IN ('name', 'price');

Result: 2 rows

12. Safety Tips

• Changing a type usually rebuilds the table (COPY or INPLACE). On large tables plan for time and disk space; tools such as gh-ost or pt-online-schema-change can help.
• Run SHOW CREATE TABLE first and keep every attribute you still want in the new definition.
• Check the data fits: MAX(CHAR_LENGTH(col)), MAX(col), invalid values.
• Back up the table, or test on a copy (CREATE TABLE t_test LIKE t; INSERT INTO t_test SELECT * FROM t;).

13. Summary

• MODIFY COLUMN changes a column's type, size, nullability, default or position, keeping its name.
• CHANGE COLUMN does the same and renames; ALTER COLUMN only sets or drops the default.
• Restate the full definition: omitted attributes are removed.
• Strict mode stops conversions that would truncate or invalidate data; clean the data first.
• Type changes may rebuild large tables, so plan them.