MySQL ALTER 命令 - MySQL 教程 | 小傅哥技术教程

ALTER 命令概述

ALTER 命令用于修改已存在的数据库表结构，包括添加、删除、修改列，添加或删除约束，重命名表等操作。这是数据库维护和演进过程中最常用的命令之一。

                    ALTER 命令的主要功能：
                    添加、删除、修改表列
添加、删除索引和约束
修改表名和列名
修改表的存储引擎和字符集
添加、删除分区

                

注意：ALTER 操作可能会锁定表，影响生产环境的性能。在生产环境中执行 ALTER 操作前，请务必做好备份和性能评估。

示例数据准备

-- 创建测试表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    age INT,
    salary DECIMAL(10,2),
    department VARCHAR(50),
    hire_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入测试数据
INSERT INTO employees (name, email, age, salary, department, hire_date) VALUES
('张三', 'zhangsan@company.com', 28, 8000.00, '技术部', '2020-01-15'),
('李四', 'lisi@company.com', 32, 9500.00, '销售部', '2019-03-20'),
('王五', 'wangwu@company.com', 26, 7500.00, '技术部', '2021-06-10'),
('赵六', 'zhaoliu@company.com', 35, 12000.00, '管理部', '2018-11-05');

-- 查看表结构
DESC employees;
                

添加列

基本语法

-- 添加列的基本语法
ALTER TABLE table_name ADD COLUMN column_name data_type [constraints];

-- 在指定位置添加列
ALTER TABLE table_name ADD COLUMN column_name data_type AFTER existing_column;
ALTER TABLE table_name ADD COLUMN column_name data_type FIRST;
                

实际示例

-- 添加电话号码列
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);

-- 添加性别列，并设置默认值
ALTER TABLE employees ADD COLUMN gender ENUM('M', 'F', 'Other') DEFAULT 'M';

-- 在 name 列后添加员工编号
ALTER TABLE employees ADD COLUMN employee_id VARCHAR(10) AFTER name;

-- 在表的开头添加序号列
ALTER TABLE employees ADD COLUMN seq_no INT AUTO_INCREMENT FIRST, ADD PRIMARY KEY(seq_no);

-- 添加多个列
ALTER TABLE employees 
ADD COLUMN address TEXT,
ADD COLUMN status ENUM('active', 'inactive') DEFAULT 'active',
ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- 查看修改后的表结构
DESC employees;
                

删除列

-- 删除单个列
ALTER TABLE employees DROP COLUMN phone;

-- 删除多个列
ALTER TABLE employees 
DROP COLUMN address,
DROP COLUMN status;

-- 注意：删除列会永久丢失数据，请谨慎操作
                

警告：删除列操作是不可逆的，会永久删除该列的所有数据。在生产环境中执行前请务必备份数据。

修改列

修改列定义

-- 修改列的数据类型
ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12,2);

-- 修改列的数据类型和约束
ALTER TABLE employees MODIFY COLUMN email VARCHAR(150) NOT NULL;

-- 修改列的默认值
ALTER TABLE employees ALTER COLUMN gender SET DEFAULT 'F';

-- 删除列的默认值
ALTER TABLE employees ALTER COLUMN gender DROP DEFAULT;
                

重命名列

-- 重命名列（MySQL 8.0+）
ALTER TABLE employees RENAME COLUMN employee_id TO emp_id;

-- 使用 CHANGE 重命名并修改列定义
ALTER TABLE employees CHANGE COLUMN emp_id employee_code VARCHAR(15) NOT NULL;

-- 只重命名，保持原有定义
ALTER TABLE employees CHANGE COLUMN age employee_age INT;
                

修改列位置

-- 将列移动到指定位置
ALTER TABLE employees MODIFY COLUMN gender ENUM('M', 'F', 'Other') AFTER employee_age;

-- 将列移动到表的开头
ALTER TABLE employees MODIFY COLUMN employee_code VARCHAR(15) FIRST;
                

索引操作

添加索引

-- 添加普通索引
ALTER TABLE employees ADD INDEX idx_name (name);

-- 添加唯一索引
ALTER TABLE employees ADD UNIQUE INDEX idx_email (email);

-- 添加复合索引
ALTER TABLE employees ADD INDEX idx_dept_salary (department, salary);

-- 添加全文索引
ALTER TABLE employees ADD FULLTEXT INDEX idx_name_fulltext (name);

-- 查看表的索引
SHOW INDEX FROM employees;
                

删除索引

-- 删除索引
ALTER TABLE employees DROP INDEX idx_name;
ALTER TABLE employees DROP INDEX idx_email;
ALTER TABLE employees DROP INDEX idx_dept_salary;

-- 删除主键（需要先删除 AUTO_INCREMENT 属性）
ALTER TABLE employees MODIFY COLUMN id INT;
ALTER TABLE employees DROP PRIMARY KEY;

-- 重新添加主键
ALTER TABLE employees ADD PRIMARY KEY (id);
ALTER TABLE employees MODIFY COLUMN id INT AUTO_INCREMENT;
                

约束操作

添加约束

-- 创建部门表用于外键演示
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    manager VARCHAR(50)
);

INSERT INTO departments (name, manager) VALUES
('技术部', '张经理'),
('销售部', '李经理'),
('管理部', '王经理');

-- 添加外键约束
ALTER TABLE employees ADD COLUMN dept_id INT;
ALTER TABLE employees ADD CONSTRAINT fk_dept 
    FOREIGN KEY (dept_id) REFERENCES departments(id);

-- 添加检查约束（MySQL 8.0.16+）
ALTER TABLE employees ADD CONSTRAINT chk_age 
    CHECK (employee_age >= 18 AND employee_age <= 65);

ALTER TABLE employees ADD CONSTRAINT chk_salary 
    CHECK (salary > 0);

-- 添加唯一约束
ALTER TABLE employees ADD CONSTRAINT uk_employee_code 
    UNIQUE (employee_code);
                

删除约束

-- 删除外键约束
ALTER TABLE employees DROP FOREIGN KEY fk_dept;

-- 删除检查约束
ALTER TABLE employees DROP CHECK chk_age;
ALTER TABLE employees DROP CHECK chk_salary;

-- 删除唯一约束
ALTER TABLE employees DROP INDEX uk_employee_code;

-- 查看表的约束信息
SELECT 
    CONSTRAINT_NAME,
    CONSTRAINT_TYPE,
    TABLE_NAME
FROM information_schema.TABLE_CONSTRAINTS 
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'employees';
                

表属性修改

重命名表

-- 重命名表
ALTER TABLE employees RENAME TO staff;

-- 或者使用 RENAME TABLE 语句
RENAME TABLE staff TO employees;

-- 同时重命名多个表
RENAME TABLE 
    employees TO staff,
    departments TO dept;

-- 恢复原名
RENAME TABLE 
    staff TO employees,
    dept TO departments;
                

修改存储引擎

-- 查看当前存储引擎
SHOW CREATE TABLE employees;

-- 修改存储引擎
ALTER TABLE employees ENGINE = MyISAM;

-- 改回 InnoDB
ALTER TABLE employees ENGINE = InnoDB;
                

修改字符集和排序规则

-- 修改表的字符集
ALTER TABLE employees CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 只修改表的默认字符集（不影响现有数据）
ALTER TABLE employees DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 修改特定列的字符集
ALTER TABLE employees MODIFY COLUMN name VARCHAR(50) 
    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
                

修改自增起始值

-- 修改自增起始值
ALTER TABLE employees AUTO_INCREMENT = 1000;

-- 查看当前自增值
SELECT AUTO_INCREMENT 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'employees';
                

分区操作

添加分区

-- 创建分区表示例
CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    sale_date DATE,
    amount DECIMAL(10,2),
    region VARCHAR(50),
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023)
);

-- 添加新分区
ALTER TABLE sales ADD PARTITION (
    PARTITION p2023 VALUES LESS THAN (2024)
);

-- 删除分区
ALTER TABLE sales DROP PARTITION p2020;

-- 重新组织分区
ALTER TABLE sales REORGANIZE PARTITION p2023 INTO (
    PARTITION p2023_h1 VALUES LESS THAN (2023-07-01),
    PARTITION p2023_h2 VALUES LESS THAN (2024)
);
                

复杂的 ALTER 操作

多个操作组合

-- 在一个 ALTER 语句中执行多个操作
ALTER TABLE employees 
    ADD COLUMN middle_name VARCHAR(50) AFTER name,
    MODIFY COLUMN salary DECIMAL(15,2) NOT NULL,
    ADD INDEX idx_hire_date (hire_date),
    DROP COLUMN updated_at,
    RENAME TO company_employees;

-- 恢复表名
ALTER TABLE company_employees RENAME TO employees;
                

条件性 ALTER 操作

-- 检查列是否存在再添加
SET @sql = (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE employees ADD COLUMN bonus DECIMAL(10,2) DEFAULT 0',
        'SELECT "Column bonus already exists" as message'
    )
    FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'employees' 
    AND COLUMN_NAME = 'bonus'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
                

ALTER 操作的性能考虑

                    性能优化建议：
                    在低峰期执行 ALTER 操作
对大表使用在线 DDL 功能
合并多个 ALTER 操作到一个语句中
监控 ALTER 操作的进度
考虑使用第三方工具（如 pt-online-schema-change）

                

在线 DDL

-- 使用在线 DDL（MySQL 5.6+）
ALTER TABLE employees 
    ADD COLUMN new_column VARCHAR(100),
    ALGORITHM = INPLACE,
    LOCK = NONE;

-- 查看支持的 ALTER 操作类型
SELECT 
    OPERATION,
    INSTANT,
    IN_PLACE,
    COPIES_TABLE,
    ALLOWS_CONCURRENT_DML,
    ALLOWS_CONCURRENT_QUERY
FROM information_schema.INNODB_ONLINE_ALTER_LOG;
                

监控 ALTER 进度

-- 查看正在执行的 ALTER 操作
SHOW PROCESSLIST;

-- 查看 InnoDB 状态
SHOW ENGINE INNODB STATUS;

-- 查看 ALTER 操作的详细信息
SELECT 
    EVENT_NAME,
    WORK_COMPLETED,
    WORK_ESTIMATED,
    ROUND(100 * WORK_COMPLETED / WORK_ESTIMATED, 2) as PCT_COMPLETED
FROM performance_schema.events_stages_current
WHERE EVENT_NAME LIKE 'stage/innodb/alter%';
                

ALTER 操作的最佳实践

重要提醒：

在生产环境执行 ALTER 前务必备份数据
在测试环境先验证 ALTER 操作
评估 ALTER 操作对性能的影响
准备回滚方案
在维护窗口期执行大型 ALTER 操作

备份和恢复策略

-- 执行 ALTER 前的备份
mysqldump -u username -p database_name table_name > table_backup.sql

-- 或者创建表的副本
CREATE TABLE employees_backup AS SELECT * FROM employees;

-- 如果需要回滚，可以从备份恢复
-- mysql -u username -p database_name < table_backup.sql

-- 或者从副本恢复
-- DROP TABLE employees;
-- CREATE TABLE employees AS SELECT * FROM employees_backup;
                

分步执行策略

-- 对于大表，分步执行 ALTER 操作
-- 步骤1：添加新列
ALTER TABLE large_table ADD COLUMN new_column VARCHAR(100);

-- 步骤2：填充数据（分批进行）
UPDATE large_table SET new_column = 'default_value' WHERE id BETWEEN 1 AND 10000;
UPDATE large_table SET new_column = 'default_value' WHERE id BETWEEN 10001 AND 20000;
-- 继续分批更新...

-- 步骤3：添加约束
ALTER TABLE large_table MODIFY COLUMN new_column VARCHAR(100) NOT NULL;
                

常见错误和解决方案

错误类型	原因	解决方案
表被锁定	其他事务正在使用表	等待事务完成或终止阻塞事务
磁盘空间不足	ALTER 操作需要额外空间	清理磁盘空间或使用在线 DDL
外键约束冲突	删除被外键引用的列	先删除外键约束再执行操作
数据类型不兼容	新数据类型无法容纳现有数据	先清理数据或选择兼容的数据类型
唯一约束冲突	添加唯一约束时存在重复数据	先清理重复数据再添加约束

错误处理示例

-- 处理外键约束错误
-- 查看外键约束
SELECT 
    CONSTRAINT_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'employees';

-- 临时禁用外键检查
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE employees DROP COLUMN dept_id;
SET FOREIGN_KEY_CHECKS = 1;

-- 处理重复数据
-- 查找重复的邮箱
SELECT email, COUNT(*) 
FROM employees 
GROUP BY email 
HAVING COUNT(*) > 1;

-- 删除重复记录（保留 ID 最小的）
DELETE e1 FROM employees e1
INNER JOIN employees e2 
WHERE e1.id > e2.id AND e1.email = e2.email;

-- 然后添加唯一约束
ALTER TABLE employees ADD UNIQUE INDEX uk_email (email);
                

🔧 MySQL ALTER 命令

ALTER 命令概述

示例数据准备

添加列

基本语法

实际示例

删除列

修改列

修改列定义

重命名列

修改列位置

索引操作

添加索引

删除索引

约束操作

添加约束

删除约束

表属性修改

重命名表

修改存储引擎

修改字符集和排序规则

修改自增起始值

分区操作

添加分区

复杂的 ALTER 操作

多个操作组合

条件性 ALTER 操作

ALTER 操作的性能考虑

在线 DDL

监控 ALTER 进度

ALTER 操作的最佳实践

备份和恢复策略

分步执行策略

常见错误和解决方案

错误处理示例