🔧 MySQL Change Column Type – ALTER COLUMN Explained with Examples

---

🧲 Introduction – Why Learn to Change Column Types in MySQL?

As your application evolves, so do its data requirements. You might need to change a column’s data type to:

• Increase storage (e.g., from INT to BIGINT)
• Allow longer text (VARCHAR(50) → VARCHAR(255))
• Convert a DATE column to DATETIME
• Fix a bad schema design decision

Learning how to safely and efficiently change column types is crucial for schema maintenance, data migrations, and performance tuning.

🎯 In this guide, you’ll learn:

• How to change column types using ALTER TABLE
• Syntax and real-world examples
• Best practices and common pitfalls
• Tools and use cases for schema refactoring

---

🛠️ Syntax – ALTER TABLE to Change Column Type

ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype [NULL | NOT NULL];

📝 Note: In MySQL, MODIFY is used to change the type, size, nullability, and default values of existing columns.

---

💡 Examples – MySQL Change Column Type in Action

✅ Example 1: INT to BIGINT

ALTER TABLE orders
MODIFY COLUMN order_id BIGINT;

📌 Use case: When INT max value (2,147,483,647) is no longer sufficient.

---

✅ Example 2: VARCHAR(100) to VARCHAR(255)

ALTER TABLE users
MODIFY COLUMN email VARCHAR(255) NOT NULL;

📌 Use case: Supporting longer email addresses.

---

✅ Example 3: DATE to DATETIME

ALTER TABLE events
MODIFY COLUMN start_time DATETIME;

📌 Use case: Storing time component alongside date.

---

✅ Example 4: Allow NULL values

ALTER TABLE customers
MODIFY COLUMN phone_number VARCHAR(20) NULL;

📌 Use case: Make a field optional instead of mandatory.

---

✅ Example 5: Change data type and rename column

ALTER TABLE products
CHANGE COLUMN old_price new_price DECIMAL(10,2);

📌 Difference: CHANGE COLUMN is used to rename and change type simultaneously.

---

🧱 ALTER COLUMN vs CHANGE COLUMN – Quick Comparison

Feature	MODIFY COLUMN	CHANGE COLUMN
Rename column?	❌ Not supported	✅ Yes
Change data type?	✅ Yes	✅ Yes
Change NULL/DEFAULT?	✅ Yes	✅ Yes

---

⚠️ Pitfalls & How to Avoid Them

⚠️ Data Truncation
Changing from a larger to smaller data type (e.g., VARCHAR(255) → VARCHAR(100)) can lose data.

✅ Check max length before shrinking:

SELECT MAX(CHAR_LENGTH(column_name)) FROM table_name;

---

⚠️ Column Locking
Large tables may be locked during the schema change, impacting application uptime.

✅ Use pt-online-schema-change from Percona Toolkit for large tables (zero downtime).

---

⚠️ Incompatible Conversion
Converting between incompatible types (e.g., TEXT → INT) will fail or produce incorrect values.

✅ Always back up data and test the conversion on a staging environment.

---

📦 Real-World Use Cases

Scenario	Change
Increase precision for currency	FLOAT → DECIMAL(10,2)
Add time tracking	DATE → DATETIME
Expand product name field	VARCHAR(50) → VARCHAR(200)
Schema normalization	CHAR(1) → ENUM('Y','N')
Optimize storage	BIGINT → INT UNSIGNED (if safe)

---

🧰 Tools and Techniques

🔄 pt-online-schema-change (Percona)

Avoids table locks during ALTERs on large tables.

pt-online-schema-change --alter "MODIFY COLUMN amount DECIMAL(10,2)" D=shop,T=payments --execute

🧪 DBeaver / MySQL Workbench

GUI-based editing of column types with validation and preview.

---

📌 Summary – Recap & Next Steps

Changing column types is a common but critical operation in MySQL schema management. Use ALTER TABLE ... MODIFY or CHANGE with caution and validate with backups.

🔍 Key Takeaways:

• Use MODIFY COLUMN to change data types or constraints.
• Use CHANGE COLUMN to rename and modify together.
• Test for compatibility and backup data before altering.
• Use Percona tools for large-scale production tables.

⚙️ Real-World Relevance:
Essential for database versioning, schema refactoring, and performance tuning across evolving apps.

---

❓ FAQ – MySQL Change Column Type

❓ What’s the difference between MODIFY and CHANGE in MySQL?

✅ MODIFY changes only the column definition; CHANGE also allows renaming.

---

❓ Can I change a column type without losing data?

✅ Yes, if the new type can store all current values. Always check with:

SELECT MAX(CHAR_LENGTH(column)) FROM your_table;

---

❓ How to change a column from NOT NULL to NULL?

ALTER TABLE mytable MODIFY COLUMN colname VARCHAR(100) NULL;

---

❓ Can I rollback a column type change?

❌ Not directly. You need to:

1. Backup data
2. Recreate original type if needed
3. Restore data manually

---

❓ How to make schema changes without downtime?

✅ Use pt-online-schema-change for live production tables.

---

Share Now :