MySQL: The Complete Guide to Modifying MySQL Table Structures
Last updated: 2026-08-26
Table structures aren't set in stone—when business needs change, ALTER TABLE is your lifesaver.
This lesson provides a systematic explanation of all operations for modifying table structures.
graph TB
A[ALTER TABLE Operation] --> B[ADD COLUMN<br/>Add Field]
A --> C[DROP COLUMN<br/>Delete Field]
A --> D[MODIFY<br/>Modification Type/Constraints]
A --> E[CHANGE<br/>Rename Field]
A --> F[RENAME<br/>Modify Table Name]
A --> G[AUTO_INCREMENT<br/>Auto-increment Settings]
A --> H[ADD/DROP CONSTRAINT<br/>Constraint Management]
1. What You'll Learn
- ALTER TABLE: Add, Delete, or Modify Columns
- Modify table and column names
- Modify field types and constraints
- AUTO_INCREMENT Auto-increment Settings
- DEFAULT Default Value Management
2. A True Story of a Requirement Change
(1) Pain Point: Changing Needs
The product manager said:
"Add a 'mobile number' field to the user table, and change the username length from 50 to 100."
If you don't know how to use ALTER TABLE, your only option is to delete the table and recreate it—which will result in the loss of all data.
(2) Solutions for ALTER TABLE
SQL
-- Add a mobile phone number field
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- Change the username length
ALTER TABLE users MODIFY COLUMN username VARCHAR(100) NOT NULL;
Benefits: No data loss, completed in seconds.
3. Add a Field
▶ Example: ADD COLUMN
Output:
TEXT
📖 Display only
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
SQL
-- Add a Single Field
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- Add a field and specify its position
ALTER TABLE users ADD COLUMN avatar VARCHAR(255) AFTER username;
-- Add to the beginning of the table
ALTER TABLE users ADD COLUMN uuid CHAR(36) FIRST;
-- Add Multiple Fields
ALTER TABLE users
ADD COLUMN address VARCHAR(200),
ADD COLUMN city VARCHAR(50),
ADD COLUMN country VARCHAR(50) DEFAULT 'China';
Output:
TEXT
📖 Display only
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
Output:
TEXT
📖 Display only
Output displayed
4. Deleting Fields
▶ Example: DROP COLUMN
SQL
-- Delete a Single Field
ALTER TABLE users DROP COLUMN phone;
-- Delete Multiple Fields
ALTER TABLE users DROP COLUMN address, DROP COLUMN city;
Output:
TEXT
📖 Display only
Output displayed
⚠️ Note: Deleting a field will permanently delete the data in that column, and it cannot be recovered!
5. Modify Fields
(1) MODIFY vs CHANGE
| Command | Editable Content | Syntax |
|---|---|---|
| MODIFY | Type, Constraint, Location | MODIFY column new_type [constraints] |
| CHANGE | Name, Type, Constraints | CHANGE old_name new_name new_type [constraints] |
▶ Example: MODIFY—Modifying a Type
SQL
-- Modify Field Type
ALTER TABLE users MODIFY COLUMN username VARCHAR(100) NOT NULL;
-- Change the field type and move it to the specified location
ALTER TABLE users MODIFY COLUMN email VARCHAR(150) FIRST;
-- Modify Field Default Values
ALTER TABLE users MODIFY COLUMN status VARCHAR(20) DEFAULT 'active';
Output:
TEXT
📖 Display only
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
▶ Example: CHANGE—Renaming a Field
SQL
-- Rename Field
ALTER TABLE users CHANGE COLUMN username user_name VARCHAR(100) NOT NULL;
-- Rename and Change Type
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(30);
Output:
TEXT
📖 Display only
Output displayed
6. Renaming a Table
▶ Example: RENAME
SQL
-- Method 1: RENAME TO
ALTER TABLE users RENAME TO customers;
-- Method 2: RENAME TABLE
RENAME TABLE users TO customers;
-- Rename Multiple Tables at Once
RENAME TABLE
old_users TO users,
old_orders TO orders;
Output:
TEXT
📖 Display only
Output displayed
7. AUTO_INCREMENT Settings
▶ Example: Auto-increment Configuration
Output:
TEXT
📖 Display only
Query OK, 0 rows affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 0 rows affected
Query OK, 1 row affected
SQL
-- Create a table with an auto-increment column
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100)
);
-- Insert Data (id auto-generated)
INSERT INTO products (name) VALUES ('iPhone');
INSERT INTO products (name) VALUES ('MacBook');
-- View Current Accumulated Value
SHOW CREATE TABLE products\G
-- Modify the Auto-Increment Start Value
ALTER TABLE products AUTO_INCREMENT = 1000;
-- Insert New Data (id starts from 1000)
INSERT INTO products (name) VALUES ('iPad');
Output:
TEXT
📖 Display only
Output displayed
💡
\G is a MySQL command-line client directive that displays query results vertically. It only works in the mysql CLI, not in application code or GUI tools.
Output:
TEXT
📖 Display only
+----+---------+
| id | name |
+----+---------+
| 1 | iPhone |
| 2 | MacBook |
| 1000| iPad |
+----+---------+
8. Managing DEFAULT Values
9. Add/Remove Constraints
10. Hands-On Guide to Batch Editing
❓ FAQ
Q Does
ALTER TABLE lock the table?A In MySQL 8.0, most
ALTER TABLE operations support online DDL and do not lock the table. However, operations such as modifying the primary key or column types may lock the table.Q Will modifying a field result in data loss?
A When using MODIFY or CHANGE to change a column's data type, if the new data type cannot store the original data, the data will be truncated or an error will be returned. Renaming a column does not result in data loss.
Q Can an ALTER TABLE statement be rolled back?
A No. ALTER TABLE is a DDL operation that is automatically committed upon execution and cannot be rolled back. It is recommended that you back up your data before performing this operation.
Q How do I modify a table that’s too large?
A An
ALTER TABLE operation on a large table (tens of millions of rows) can be very slow. We recommend using tools like pt-online-schema-change or gh-ost to modify the table online.📖 Summary
- ADD COLUMN Add a field; you can specify its position (FIRST/AFTER)
- DROP COLUMN Deletes a column; the data is permanently lost.
- MODIFY changes the field type and constraints; CHANGE allows you to rename the field as well
- RENAME TABLE Rename a table
- AUTO_INCREMENT Controls the starting value for auto-increment
- ALTER COLUMN SET/DROP DEFAULT Manage default values
- ADD/DROP CONSTRAINT Manage primary key, foreign key, unique, and CHECK constraints
📝 Exercises
-
Basic Question (Difficulty ⭐): Add the
phonefield (VARCHAR(20)) to theuserstable, placing it after theemailfield. -
Advanced Exercise (Difficulty ⭐⭐): After creating the
orderstable, change the data type of theamountfield from INT to DECIMAL(10,2), and add thestatusfield with a default value of 'pending'. -
Challenge Question (Difficulty: ⭐⭐⭐): Design a table structure evolution plan: To go from v1 to v2, you need to add 3 columns, delete 1 column, and change the data types of 2 columns. Write the complete
ALTER TABLEstatements.
