MySQL ALTER 命令详解

MySQL 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)

添加字段

-- 添加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)

修改字段类型和名称

使用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 的区别

设置字段属性和默认值

设置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)

高级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执行ALTER操作

<?php
$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 . "<br>";
    }
}

$conn->close();
?>

使用PDO执行ALTER操作

<?php
$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';