2391 字
约 7 分钟
8
MySQL ALTER命令
2026-09-19
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
('张三', '[email protected]', 28, 8000.00, '技术部', '2020-01-15'),
('李四', '[email protected]', 32, 9500.00, '销售部', '2019-03-20'),
('王五', '[email protected]', 26, 7500.00, '技术部', '2021-06-10'),
('赵六', '[email protected]', 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命令
https://clxhxhhr.top/posts/2781/
作者
clxstart
发布于
2026-09-19
许可协议
CC BY-NC-SA 4.0
JVM调优最佳实践
🗑️ MySQL 删除数据
评论
0 条
还没有评论，先写一条吧。