MySQL ALTER命令

概述

ALTER TABLE语句用于修改现有表的结构。您可以在表创建后添加、删除或修改列、索引和约束。

常见ALTER操作

添加列：向表中添加新列 删除列：从表中删除列 修改列：更改列定义 重命名列：更改列名 添加约束：添加主键、外键等 删除约束：删除约束 更改选项：修改表选项

ALTER TABLE语法

基本语法

  | ADD [COLUMN] (column_definition, ...)
  | DROP [COLUMN] column_name
  | CHANGE [COLUMN] old_name new_name definition
  | MODIFY [COLUMN] column_definition
  | RENAME [TO] new_table_name

添加列

添加单列

-- Add column at end

-- Add column with default value

-- Add column with NOT NULL

-- Add column with auto increment

添加多列

-- Add several columns

-- Add columns with specific options

在特定位置添加列

-- Add as first column

-- Add after specific column

-- Add at end (default)

添加带约束的列

-- Add with PRIMARY KEY

-- Add with UNIQUE constraint

-- Add with FOREIGN KEY

-- Add with CHECK constraint (MySQL 8.0+)

删除列

删除单列

-- Drop column

-- Drop if exists (MySQL 8.0+)

删除多列

-- Drop several columns

删除并添加列

-- Drop and re-add column

修改列

CHANGE与MODIFY

-- MODIFY: Change definition only

-- CHANGE: Change name and definition

更改数据类型

-- Change VARCHAR length

-- Change INT to BIGINT

-- Change TEXT to LONGTEXT

-- Change to NOT NULL

更改默认值

-- Add default value

-- Change default value

-- Remove default value

添加自增

-- Add AUTO_INCREMENT to existing column

-- Set starting value

重命名列

-- Rename single column

-- Rename and change type

-- Rename with constraint

重命名表

-- Rename table

-- Alternative syntax

-- Rename multiple tables

修改约束

添加主键

-- Add single column primary key

-- Add composite primary key

-- Add primary key with auto increment

删除主键

-- Drop primary key

-- Drop and add new primary key

添加外键

-- Add foreign key

-- Add with ON DELETE

-- Add with ON UPDATE

删除外键

-- Drop specific foreign key

-- Find foreign key names

-- Drop foreign key by name

添加唯一约束

-- Add single column unique

-- Add composite unique

-- Add named unique constraint

删除唯一约束

-- Drop unique index

-- Drop named unique constraint

修改索引

添加索引

-- Add simple index

-- Add composite index

-- Add FULLTEXT index

-- Add spatial index

删除索引

-- Drop index

-- Drop primary key (also removes index)

修改表选项

更改引擎

-- Change storage engine

-- Convert multiple tables

更改字符集

-- Change character set

-- Change collation

-- Change both

更改自增

-- Set next auto increment value

-- Reset auto increment

添加表注释

-- Add or modify table comment

-- Add column comment

复杂ALTER操作

重新排序列

-- MySQL doesn't have direct REORDER
-- Must use multiple MODIFY statements

转换为不同格式

-- Convert to compressed row format

-- Convert to dynamic row format

-- Convert to fixed row format

性能考虑

ALTER表性能

-- ALTER TABLE can be slow on large tables
-- Consider these strategies:

-- 1. Use pt-online-schema-change (external tool)
-- Allows online schema changes

-- 2. Create new table and copy data
-- Add/modify columns
-- Copy data
-- Swap tables
-- Drop old

-- 3. Use ALGORITHM clause (MySQL 5.6+)

-- 4. Use LOCK clause

ALGORITHM选项

-- ALGORITHM = COPY (default for some changes)
-- Creates copy of table, slow, locks table

-- ALGORITHM = INPLACE (faster, minimal locking)
-- Modifies table in place

-- ALGORITHM = INSTANT (MySQL 8.0+, fastest)
-- No table copy, no locking

LOCK选项

-- LOCK = DEFAULT (MySQL decides)
-- LOCK = NONE (allows concurrent DML)
-- LOCK = SHARED (allows concurrent reads)
-- LOCK = EXCLUSIVE (blocks all DML/DDL)

实用示例

迁移：添加用户状态

-- Add status column with default

-- Update existing users

-- Add index for status

迁移：添加软删除

-- Add deleted_at column

-- Update application logic to use soft deletes
-- Instead of: DELETE FROM users WHERE id = 1;
-- Use: UPDATE users SET deleted_at = NOW() WHERE id = 1;

迁移：添加审计列

-- Add audit columns

-- Add foreign keys for audit

迁移：规范化数据

-- Create normalized table

-- Migrate data

-- Add phone column (optional)

-- Drop old column

故障排除

常见错误

-- Error: Duplicate column name
-- ALTER TABLE users ADD COLUMN name VARCHAR(100);
-- Solution: Column already exists

-- Error: Key column doesn't exist
-- ALTER TABLE users ADD PRIMARY KEY (nonexistent);
-- Solution: Check column name

-- Error: Foreign key constraint fails
-- ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);
-- Solution: Ensure referenced column exists and has matching type

-- Error: Cannot delete column referenced by foreign key
-- ALTER TABLE users DROP COLUMN id;
-- Solution: Drop foreign key first

检查表结构

-- Show table structure

-- Show create statement

-- Show table information

-- Check columns

小结

ALTER TABLE允许您：

修改结构：添加、删除、更改列 管理约束：添加和删除键 更改选项：引擎、字符集、自增 在线DDL：使用ALGORITHM和LOCK选项

修改大型表时考虑性能影响，并使用适当的ALGORITHM和LOCK选项。

---

上一个：事务

下一个：索引