mysql中ALTER TABLE修改表结构的语法与应用

ALTER TABLE 添加字段必须指定数据类型和约束；改名改类型用 CHANGE COLUMN，仅改类型用 MODIFY COLUMN；删字段高危且锁表；建索引需显式命名并注意前缀长度。

ALTER TABLE 添加字段时必须指定数据类型和非空约束

MySQL 不允许添加没有类型的列，ADD COLUMN 后必须跟完整定义。常见错误是漏写 NOT NULL 却又没设默认值，导致执行失败。

• 正确写法：

  ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL DEFAULT '';

• 如果允许为空，显式写 NULL 更安全（避免某些版本默认行为差异）：

  ALTER TABLE users ADD COLUMN remark TEXT NULL;

• 添加自增主键需同时满足：字段为整型、NOT NULL、AUTO_INCREMENT、且表无其他自增列：

  ALTER TABLE logs ADD COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

修改字段类型或名称要用 CHANGE COLUMN 或 MODIFY COLUMN

CHANGE COLUMN 可改名+改类型，MODIFY COLUMN 只能改类型（不改名）。二者都不能跳过字段定义——即使只改类型，也得把整个新定义写全。

• 重命名并调类型：

  ALTER TABLE orders CHANGE COLUMN order_time created_at DATETIME NOT NULL;

• 仅扩大长度（比如从 VARCHAR(50) 到 VARCHAR(200)）：

  ALTER TABLE products MODIFY COLUMN description VARCHAR(200);

• 注意：缩小长度可能失败（若已有数据超长），MySQL 会报错 ERROR 1406: Data too long for column

删除字段前务必确认外键和应用层依赖

DROP COLUMN 是高危操作，一旦执行无法回滚（除非有备份或 binlog 可恢复）。InnoDB 表删字段实际会重建表，大表耗时久、锁表时间长。

• 先查是否有外键引用：

  SELECT CONSTRAINT_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE COLUMN_NAME = 'user_id' AND REFERENCED_TABLE_NAME = 'users';

• 删字段语句很简单，但别手快：

  ALTER TABLE user_profiles DROP COLUMN avatar_url;

• 生产环境建议在低峰期执行，并提前在测试库验证锁表现和耗时

给字段加索引要区分 UNIQUE 和普通 INDEX，避免重复创建

用 ADD INDEX 或 ADD UNIQUE INDEX 添加索引时，MySQL 不会提示“索引已存在”，而是直接报错 ERROR 1061: Duplicate key name。名字冲突、字段顺序不同都会视为不同索引。

• 推荐显式命名索引，方便后续管理：

  ALTER TABLE comments ADD INDEX idx_post_id_status (post_id, status);

• 唯一约束可直接在 ADD COLUMN 时加上：

  ALTER TABLE accounts ADD COLUMN email VARCHAR(255) NOT NULL UNIQUE;

• 注意：对 TEXT/BLOB 字段建索引必须指定前缀长度，否则报错：

  ALTER TABLE articles ADD INDEX idx_title (title(100));