MySQL ALTER 命令详解
MySQL ALTER命令用于修改现有的数据库表结构，包括添加、删除、修改字段，重命名字段和表，以及修改表的其他属性。这是数据库维护和升级中非常重要的命令。
ALTER命令的主要用途：
添加、删除或修改表字段
修改字段的数据类型和属性
重命名字段和表
添加、删除或修改索引
修改表的存储引擎和字符集
准备工作：创建测试表
在开始学习ALTER命令之前，我们先创建一个测试表：
-- 登录MySQL并选择数据库
mysql -u root -p
Enter password: *******
mysql> USE TUTORIALS;
Database changed
-- 创建测试表
mysql> CREATE TABLE testalter_tbl (
-> i INT,
-> c CHAR(1)
-> );
Query OK, 0 rows affected (0.02 sec)
-- 查看表结构
mysql> DESC testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i | int(11) | YES | | NULL | |
| c | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
添加、删除和修改表字段
删除字段
-- 删除字段i
mysql> ALTER TABLE testalter_tbl DROP i;
Query OK, 0 rows affected (0.02 sec)
-- 验证表结构
mysql> DESC testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
注意：如果数据表中只剩余一个字段，则无法使用DROP来删除字段。
添加字段
-- 添加INT类型的字段i
mysql> ALTER TABLE testalter_tbl ADD i INT;
Query OK, 0 rows affected (0.02 sec)
-- 验证表结构
mysql> DESC testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c | char(1) | YES | | NULL | |
| i | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
指定字段位置
-- 删除字段i，然后重新添加为第一列
mysql> ALTER TABLE testalter_tbl DROP i;
mysql> ALTER TABLE testalter_tbl ADD i INT FIRST;
Query OK, 0 rows affected (0.02 sec)
mysql> DESC testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i | int(11) | YES | | NULL | |
| c | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-- 删除字段i，然后重新添加到c字段之后
mysql> ALTER TABLE testalter_tbl DROP i;
mysql> ALTER TABLE testalter_tbl ADD i INT AFTER c;
Query OK, 0 rows affected (0.02 sec)
mysql> DESC testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c | char(1) | YES | | NULL | |
| i | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
位置控制关键字：
FIRST - 将字段设置为第一列
AFTER column_name - 将字段插入到指定列之后
修改字段类型和名称
使用MODIFY修改字段类型
-- 将字段c从CHAR(1)改为CHAR(10)
mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);
Query OK, 0 rows affected (0.02 sec)
mysql> DESC testalter_tbl;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c | char(10) | YES | | NULL | |
| i | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
使用CHANGE修改字段名称和类型
-- 将字段i改为j，类型改为BIGINT
mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;
Query OK, 0 rows affected (0.02 sec)
mysql> DESC testalter_tbl;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c | char(10) | YES | | NULL | |
| j | bigint(20) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-- 只修改类型，不修改名称（字段名写两次）
mysql> ALTER TABLE testalter_tbl CHANGE j j INT;
Query OK, 0 rows affected (0.02 sec)
mysql> DESC testalter_tbl;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c | char(10) | YES | | NULL | |
| j | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
MODIFY vs CHANGE 的区别
命令
用途
语法
示例
MODIFY
修改字段类型和属性
ALTER TABLE table MODIFY column definition
MODIFY c VARCHAR(100)
CHANGE
修改字段名称、类型和属性
ALTER TABLE table CHANGE old_column new_column definition
CHANGE c description VARCHAR(100)
设置字段属性和默认值
设置NOT NULL约束和默认值
-- 设置字段j为NOT NULL，默认值为100
mysql> ALTER TABLE testalter_tbl MODIFY j INT NOT NULL DEFAULT 100;
Query OK, 0 rows affected (0.02 sec)
mysql> DESC testalter_tbl;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c | char(10) | YES | | NULL | |
| j | int(11) | NO | | 100 | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
修改字段默认值
-- 修改字段j的默认值为1000
mysql> ALTER TABLE testalter_tbl ALTER j SET DEFAULT 1000;
Query OK, 0 rows affected (0.02 sec)
mysql> DESC testalter_tbl;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c | char(10) | YES | | NULL | |
| j | int(11) | NO | | 1000 | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
删除字段默认值
-- 删除字段j的默认值
mysql> ALTER TABLE testalter_tbl ALTER j DROP DEFAULT;
Query OK, 0 rows affected (0.02 sec)
mysql> DESC testalter_tbl;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c | char(10) | YES | | NULL | |
| j | int(11) | NO | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
修改表名和存储引擎
重命名表
-- 将testalter_tbl重命名为alter_tbl
mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;
Query OK, 0 rows affected (0.02 sec)
-- 验证表名已更改
mysql> SHOW TABLES;
+--------------------+
| Tables_in_TUTORIALS|
+--------------------+
| alter_tbl |
+--------------------+
1 row in set (0.00 sec)
修改存储引擎
-- 修改表的存储引擎为MyISAM（使用ENGINE关键字）
mysql> ALTER TABLE alter_tbl ENGINE = MYISAM;
Query OK, 0 rows affected (0.02 sec)
-- 查看表状态
mysql> SHOW TABLE STATUS LIKE 'alter_tbl'\G
*************************** 1. row ***************************
Name: alter_tbl
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 1970324836974591
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2024-01-15 10:30:45
Update_time: 2024-01-15 10:30:45
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
注意：在现代MySQL版本中，使用ENGINE关键字而不是TYPE来指定存储引擎，因为TYPE已被废弃。
高级ALTER操作
添加和删除索引
-- 添加普通索引
ALTER TABLE alter_tbl ADD INDEX idx_c (c);
-- 添加唯一索引
ALTER TABLE alter_tbl ADD UNIQUE INDEX unique_j (j);
-- 添加主键（如果表没有主键）
ALTER TABLE alter_tbl ADD PRIMARY KEY (j);
-- 删除索引
ALTER TABLE alter_tbl DROP INDEX idx_c;
-- 删除主键
ALTER TABLE alter_tbl DROP PRIMARY KEY;
添加和删除外键
-- 添加外键约束
ALTER TABLE orders
ADD CONSTRAINT fk_customer_id
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE
ON UPDATE CASCADE;
-- 删除外键约束
ALTER TABLE orders DROP FOREIGN KEY fk_customer_id;
修改表字符集和排序规则
-- 修改表字符集为utf8mb4
ALTER TABLE alter_tbl
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
-- 只修改默认字符集（不影响现有列）
ALTER TABLE alter_tbl
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
完整的表修改示例
用户表结构升级示例
-- 原始表结构
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(100)
);
-- 使用ALTER命令升级表结构
-- 1. 修改id为自增主键
ALTER TABLE users MODIFY id INT AUTO_INCREMENT;
-- 2. 添加新字段
ALTER TABLE users
ADD COLUMN phone VARCHAR(20) AFTER name,
ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
-- 3. 修改现有字段
ALTER TABLE users
MODIFY name VARCHAR(100) NOT NULL,
MODIFY email VARCHAR(150) NOT NULL UNIQUE;
-- 4. 添加索引
ALTER TABLE users
ADD INDEX idx_email (email),
ADD INDEX idx_created_at (created_at);
-- 5. 修改表注释和字符集
ALTER TABLE users
COMMENT='用户信息表',
CHARACTER SET=utf8mb4,
COLLATE=utf8mb4_unicode_ci;
-- 查看最终表结构
DESC users;
使用PHP脚本执行ALTER操作
重要提醒： 请使用MySQLi或PDO扩展，原始的mysql扩展已在PHP 7.0中移除。
使用MySQLi执行ALTER操作
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "TUTORIALS";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检查连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
// 执行ALTER操作 - 添加新字段
$sql = "ALTER TABLE testalter_tbl ADD COLUMN description TEXT AFTER c";
if ($conn->query($sql) === TRUE) {
echo "表结构修改成功";
} else {
echo "错误: " . $conn->error;
}
// 执行多个ALTER操作
$alter_operations = [
"ALTER TABLE testalter_tbl MODIFY c VARCHAR(255)",
"ALTER TABLE testalter_tbl ADD INDEX idx_c (c)",
"ALTER TABLE testalter_tbl ADD COLUMN status ENUM('active','inactive') DEFAULT 'active'"
];
foreach ($alter_operations as $sql) {
if ($conn->query($sql) !== TRUE) {
echo "执行失败: " . $sql . " - " . $conn->error . "
";
}
}
$conn->close();
?>
使用PDO执行ALTER操作
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "TUTORIALS";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// 执行ALTER操作
$sql = "ALTER TABLE testalter_tbl ADD COLUMN notes TEXT";
$conn->exec($sql);
echo "表结构修改成功";
} catch(PDOException $e) {
echo "错误: " . $e->getMessage();
}
$conn = null;
?>
ALTER命令最佳实践
操作前准备
备份数据：在执行ALTER操作前备份表数据
测试环境验证：先在测试环境验证ALTER操作
选择合适时间：在系统低峰期执行表结构修改
检查依赖关系：确认没有其他进程正在使用该表
性能优化建议
合并操作：将多个ALTER操作合并为一个语句
使用在线DDL：MySQL 5.6+支持在线DDL操作
避免锁表：使用ALGORITHM=INPLACE和LOCK=NONE选项
批量操作：对大表使用pt-online-schema-change工具
在线DDL示例
-- 使用在线DDL添加索引（MySQL 5.6+）
ALTER TABLE large_table
ADD INDEX idx_column (column_name),
ALGORITHM=INPLACE,
LOCK=NONE;
-- 使用在线DDL修改字段
ALTER TABLE large_table
MODIFY column_name VARCHAR(500),
ALGORITHM=INPLACE,
LOCK=NONE;
常见问题与解决方案
1. 表被锁定错误
ERROR 1064 (42000): You have an error in your SQL syntax
解决方案：检查SQL语法，确保关键字和参数正确。
2. 重复字段名错误
ERROR 1060 (42S21): Duplicate column name 'column_name'
解决方案：检查字段名是否已存在，或使用CHANGE重命名字段。
3. 数据截断警告
Warning: Data truncated for column 'column_name'
解决方案：确保新数据类型能容纳现有数据。
4. 外键约束错误
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
解决方案：先删除或修改相关的外键约束。
实用技巧
生成ALTER语句的脚本
-- 为所有VARCHAR字段生成修改字符集的语句
SELECT
CONCAT('ALTER TABLE `', TABLE_NAME,
'` MODIFY `', COLUMN_NAME,
'` ', COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS alter_statement
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
AND DATA_TYPE = 'varchar'
AND CHARACTER_SET_NAME != 'utf8mb4';
检查表结构变化
-- 在执行ALTER前后检查表结构
SHOW CREATE TABLE table_name\G
-- 或者使用
DESC table_name;
-- 查看表的索引
SHOW INDEX FROM table_name;
使用信息模式监控表变化
-- 查看表的修改历史
SELECT * FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database'
AND TABLE_NAME = 'your_table';
上一章: MySQL 事务
下一章: MySQL 索引