垦荒学园
  • 中文

      • 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选项。

      上一个：事务

      下一个：索引