How to Change a MySQL Column Type

Two ways to change a MySQL column's type: ALTER TABLE ... MODIFY COLUMN when you are keeping the same name and only changing the type, and ALTER TABLE ... CHANGE COLUMN when you also want to rename. Both convert the existing data with implicit CAST rules, both can use ALGORITHM=INPLACE for online changes when the conversion is type-compatible, and both have a clear "this will truncate or lose data" failure mode you need to know about. I'll walk the real-world cases: widening VARCHAR, the INT to BIGINT upgrade story, and converting between text and numeric types.

The example table is orders, where the total_cents column was declared as INT UNSIGNED and is starting to look uncomfortably close to its 4.29 billion-cent ceiling.

CREATE TABLE orders (
  id            BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  customer_id   INT UNSIGNED  NOT NULL,
  total_cents   INT UNSIGNED  NOT NULL,
  status        VARCHAR(20)   NOT NULL,
  notes         VARCHAR(255),
  placed_at     DATETIME      NOT NULL
);

Jump to:

• MODIFY: change the type, keep the name
• CHANGE: rename and change the type together
• Widening: INT to BIGINT, the safe path
• Widening VARCHAR
• Narrowing: VARCHAR(100) to VARCHAR(50)
• Converting between text and numbers
• ALGORITHM and lock behaviour
• FAQ

MODIFY: change the type, keep the name

The shortest form, for cases where the column name is staying.

ALTER TABLE orders
MODIFY COLUMN total_cents BIGINT UNSIGNED NOT NULL;

Read it left to right: change the column called :column in table :table so that it is now BIGINT UNSIGNED NOT NULL. Every existing value is converted in place using MySQL's implicit cast rules, which for an INT UNSIGNED to BIGINT UNSIGNED change is a pure widening — no data lost, every value fits.

You must repeat the full column specification, including NOT NULL, DEFAULT, character set, and any other modifiers. If you only write MODIFY COLUMN total_cents BIGINT UNSIGNED, MySQL drops the NOT NULL constraint because it is not in the new spec. This is the most common foot-gun with MODIFY.

Works on MySQL 5.5, 5.6, 5.7, 8.0, and 8.4.

CHANGE: rename and change the type together

When the column is being renamed in addition to type-changed, use CHANGE. The syntax includes both the old and new name.

ALTER TABLE orders
CHANGE COLUMN total_cents amount_cents BIGINT UNSIGNED NOT NULL;

This renames :column to amount_cents and changes the type to BIGINT UNSIGNED NOT NULL in one statement. Same caveat about repeating the full spec applies: omitting NOT NULL drops the constraint.

CHANGE can also be used to rename without changing the type — just keep the type the same. Some style guides prefer ALTER TABLE ... RENAME COLUMN old TO new (available since MySQL 8.0) for rename-only operations because it is more explicit; both work.

Widening: INT to BIGINT, the safe path

The Y2K38-style problem. A column was declared INT UNSIGNED (max 4,294,967,295) and the business has grown enough that values are approaching the limit. The fix is to widen to BIGINT UNSIGNED (max 18.4 quintillion), but you want to do it on a live table without breaking the application.

ALTER TABLE orders
MODIFY COLUMN total_cents BIGINT UNSIGNED NOT NULL,
ALGORITHM=INPLACE, LOCK=NONE;

Three things make this safe:

1. INT UNSIGNED → BIGINT UNSIGNED is a pure widening. Every value that fits in 32 bits fits in 64. No data lost.
2. ALGORITHM=INPLACE keeps the table available for other queries during the rewrite (MySQL 5.7+). The rewrite still happens — INT and BIGINT have different on-disk sizes — but the table is not locked exclusively.
3. LOCK=NONE says fail the statement rather than acquire a blocking lock. If MySQL cannot do the change without locking, you get an error and can decide whether to retry without LOCK=NONE or schedule a maintenance window.

For tables in the tens or hundreds of millions of rows, even an INPLACE rewrite takes a long time. The production pattern for those is to use gh-ost, pt-online-schema-change, or a similar tool that builds the new table side-by-side and swaps. Plain ALTER is fine for tables under ~50M rows.

Widening VARCHAR

Going from VARCHAR(50) to VARCHAR(255) (or any larger size) is a special case. As long as the new length stays within the same "small" or "large" bucket (under or over 255), MySQL can do it instantly with no row rewrite.

-- Instant change in MySQL 8.0+, same byte-length bucket
ALTER TABLE orders
MODIFY COLUMN status VARCHAR(100) NOT NULL,
ALGORITHM=INPLACE, LOCK=NONE;

The boundary is at 255 characters for single-byte charsets, fewer for utf8mb4. VARCHAR lengths under 255 use a 1-byte length prefix; lengths over 255 use 2 bytes. Crossing the boundary (e.g., 50 → 300) requires rewriting every row to add an extra byte to each value's length prefix. Staying within (50 → 200, or 256 → 1000) does not.

In practice: for VARCHAR widening within the same bucket, the change is essentially free. For widening across the boundary, treat it like any other large ALTER — schedule the time.

Narrowing: VARCHAR(100) to VARCHAR(50)

Narrowing is risky. MySQL will silently truncate values that are longer than the new column allows, unless you have strict SQL mode on (which is the default in 8.0+). With strict mode, narrowing fails when any existing row has a value that does not fit.

-- Find rows that would be truncated before narrowing
SELECT id, status, LENGTH(status) AS current_length
FROM orders
WHERE LENGTH(status) > 50
ORDER BY current_length DESC;

If this query returns rows, decide what to do with them: truncate manually, move them to a separate table, or skip the narrowing entirely. Then run the narrowing:

ALTER TABLE orders
MODIFY COLUMN status VARCHAR(50) NOT NULL;

Narrowing always requires rewriting every row because the on-disk storage changes. ALGORITHM=INPLACE is supported in some cases but the table is still being rewritten — schedule it during low-traffic hours.

Converting between text and numbers

Converting VARCHAR to INT (or vice versa) is the change that has the most ways to go wrong. MySQL applies CAST semantics: every value has to be convertible, or the statement fails (strict mode) or silently produces zeros (non-strict mode).

-- Before converting VARCHAR to INT, find rows that are not pure numeric
SELECT id, total_cents
FROM orders
WHERE total_cents NOT REGEXP '^[0-9]+$'
LIMIT 50;

If this returns any rows, the conversion will fail (strict mode) or destroy data (non-strict). Clean up first:

-- Strip non-numeric characters from string-stored prices
UPDATE orders
SET total_cents = REGEXP_REPLACE(total_cents, '[^0-9]', '')
WHERE total_cents REGEXP '[^0-9]';

-- Then convert
ALTER TABLE orders
MODIFY COLUMN total_cents BIGINT UNSIGNED NOT NULL;

REGEXP_REPLACE requires MySQL 8.0+. On 5.7 you have to apply the cleanup at the application layer first, or write a stored function.

ALGORITHM and lock behaviour

ALGORITHM=INSTANT is the fastest (milliseconds), INPLACE rewrites the table without exclusive locks, COPY rewrites with exclusive locks for the duration. Always specify the algorithm explicitly so MySQL errors out instead of silently doing a slower path than you expected.

What to do next

If you actually need a fresh column rather than altering an existing one — for example, adding a consent_version field for GDPR tracking — that has its own pattern with DEFAULT values and online-DDL considerations: How to Add a Column to a MySQL Table walks through ADD COLUMN with DEFAULT, the three-step NOT NULL backfill, and ALGORITHM=INSTANT for big tables.

For the wider reference covering ALTER TABLE options, character sets, and index changes, the MySQL Cheat Sheet keeps the syntax in one searchable page.

External reference: official documentation on ALTER TABLE syntax and InnoDB online DDL operations.

FAQ