How to Change Column Type in MySQL

How do I change the data type of a column in MySQL without data loss?

ALTER TABLE … MODIFY/CHANGE lets you safely convert a column to a new data type in MySQL.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why change a column’s data type?

Update a column’s type to store larger numbers, switch to an optimized type, or add precision—without creating a new column. Refactoring keeps application code and reports consistent.

What SQL command changes a column type?

Use ALTER TABLE with either MODIFY COLUMN (keep the same name) or CHANGE COLUMN (rename and change type in one step).

When should I use MODIFY COLUMN?

Choose MODIFY when the column name stays the same.It rewrites metadata only, leaving the name untouched.

When should I use CHANGE COLUMN?

Pick CHANGE to rename the column while converting the data type. You supply both old and new names.

How do I avoid data loss?

Always cast to a compatible type or create a backup. Test with a SELECT CAST() preview to check for truncation before running the DDL.

Do indexes or foreign keys matter?

Yes. MySQL rebuilds indexes on the altered column.Make sure referenced columns share identical types and lengths to prevent constraint errors.

Can I make the change online?

Add ALGORITHM=INPLACE, LOCK=NONE to let MySQL apply the change with minimal blocking when possible (InnoDB only).

Best practices for production

Run on a replica first, verify metrics, schedule during low-traffic windows, and monitor the slow query log for regressions.

Example: widening a DECIMAL

The Orders.total_amount needs more precision after a currency switch.

ALTER TABLE Orders MODIFY COLUMN total_amount DECIMAL(12,2) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;

Example: renaming and changing a VARCHAR

Marketing wants Customers.email renamed to email_address and lengthened.

ALTER TABLE Customers CHANGE COLUMN email email_address VARCHAR(320) NOT NULL;.

Why How to Change Column Type in MySQL is important

How to Change Column Type in MySQL Example Usage


-- Upgrade monetary precision in Orders
ALTER TABLE Orders 
  MODIFY total_amount DECIMAL(12,2) NOT NULL, 
  ALGORITHM=INPLACE, LOCK=NONE;

-- Align Product price to DECIMAL(10,2)
ALTER TABLE Products 
  MODIFY price DECIMAL(10,2) NOT NULL;

How to Change Column Type in MySQL Syntax


-- Keep the column name
a. ALTER TABLE table_name
       MODIFY [COLUMN] column_name new_data_type
       [NULL | NOT NULL]
       [DEFAULT default_value]
       [ALGORITHM=INPLACE]
       [LOCK=NONE | SHARED | EXCLUSIVE];

-- Rename and change in one step
b. ALTER TABLE table_name
       CHANGE [COLUMN] old_name new_name new_data_type
       [NULL | NOT NULL]
       [DEFAULT default_value]
       [ALGORITHM=INPLACE]
       [LOCK=NONE | SHARED | EXCLUSIVE];

-- Ecommerce examples
1. ALTER TABLE Orders
       MODIFY total_amount DECIMAL(12,2) NOT NULL;
2. ALTER TABLE Customers
       CHANGE COLUMN email email_address VARCHAR(320) NOT NULL;

Common Mistakes

Truncating data when shrinking types—e.g., VARCHAR(255) to VARCHAR(100)—without first scanning for long values. Fix by running SELECT LENGTH() checks and trimming or keeping a larger size.
Locking large tables by omitting ALGORITHM=INPLACE or LOCK clauses. Fix by adding ALGORITHM=INPLACE, LOCK=NONE when supported, or using pt-online-schema-change for zero-downtime.