一文吃透 MySQL 表结构：字段增删改、约束设置与索引优化实操

一、字段管理

1. 字段新增​

-- 基础语法：追加字段（默认添加到表尾）
ALTER TABLE 表名 ADD 字段名 数据类型 [约束条件]; 

-- 示例：在用户表新增注册时间字段
ALTER TABLE user ADD reg_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

-- 进阶：指定字段位置（AFTER/BFFORE/FIRST）
ALTER TABLE user ADD phone VARCHAR(11) AFTER email; -- 插入到email字段之后
ALTER TABLE user ADD user_code CHAR(32) FIRST; -- 作为第一个字段

2. 字段修改​

2.1. 字段重命名​

-- 标准语法（需指定原字段类型）
ALTER TABLE 表名 CHANGE COLUMN 原字段名 新字段名 数据类型 [约束];

-- 简化语法（仅重命名，类型不变）
ALTER TABLE 表名 RENAME COLUMN 原字段名 TO 新字段名;

-- 示例：将user表的tel字段重命名为phone
ALTER TABLE user CHANGE COLUMN tel phone VARCHAR(11) NOT NULL;

2.2. 数据类型修改​

-- 语法：修改字段数据类型
ALTER TABLE 表名 ALTER COLUMN 字段名 TYPE 新数据类型;

-- 示例：将age字段从TINYINT升级为INT
ALTER TABLE user ALTER COLUMN age INT;

3. 字段删除​

-- 危险操作！删除字段会永久丢失数据
ALTER TABLE 表名 DROP COLUMN 字段名;

-- 示例：删除冗余的temp_field字段
ALTER TABLE user DROP COLUMN temp_field;

-- 最佳实践：删除前建议备份
CREATE TABLE user_backup LIKE user;
INSERT INTO user_backup SELECT * FROM user;

二、约束管理

1. 默认值约束​

-- 添加默认值（新增字段时）
ALTER TABLE user ADD gender VARCHAR(2) DEFAULT 'M';

-- 修改现有字段默认值
ALTER TABLE user MODIFY COLUMN reg_ip VARCHAR(45) DEFAULT '127.0.0.1';

-- 删除默认值（设置为无默认值）
ALTER TABLE user MODIFY COLUMN remark VARCHAR(255) DEFAULT NULL;

2. 非空约束​

-- 添加非空约束（字段已有NULL值时会报错）
ALTER TABLE user MODIFY COLUMN email VARCHAR(50) NOT NULL;

-- 移除非空约束
ALTER TABLE user MODIFY COLUMN email VARCHAR(50) NULL;

3. 主键约束​

-- 添加主键（自动创建PRIMARY KEY索引）
ALTER TABLE order ADD PRIMARY KEY(order_id);

-- 删除主键（需先删除外键依赖）
ALTER TABLE order DROP PRIMARY KEY;

-- 注意：一张表只能有一个主键，主键字段自动NOT NULL

4. 外键约束​

-- 添加外键（需先创建索引）
ALTER TABLE order_detail 
ADD CONSTRAINT fk_order_id 
FOREIGN KEY(order_id) 
REFERENCES orders(order_id)
ON UPDATE CASCADE -- 级联更新
ON DELETE SET NULL; -- 主表删除时设为NULL

-- 删除外键
ALTER TABLE order_detail DROP FOREIGN KEY fk_order_id;

三、索引优化

1. 索引分类与语法​

索引类型	语法格式​	适用场景​
主键索引​	ALTER TABLE 表名 ADD PRIMARY KEY (字段);​	唯一标识记录，加速等值查询​
唯一索引​	ALTER TABLE 表名 ADD UNIQUE 索引名;​	确保数据唯一性​
普通索引​	ALTER TABLE 表名 ADD INDEX 索引名;​	加速 WHERE/JOIN 查询​
全文索引​	ALTER TABLE 表名 ADD FULLTEXT 索引名;​	全文搜索（MyISAM/InnoDB 5.6+）​
组合索引​	ALTER TABLE 表名 ADD INDEX [索引名](字段 1, 字段 2, 字段 3);​	多条件联合查询​

2. 实战示例

2.1. 唯一索引防止重复邮箱​

ALTER TABLE user ADD UNIQUE idx_email(email);

2.2. 组合索引优化多条件查询​

-- 为（status, create_time）创建组合索引
ALTER TABLE orders ADD INDEX idx_status_time(status, create_time);

-- 有效利用索引的查询：WHERE status=1 AND create_time>'2023-01-01'

2.3. 全文索引实现模糊搜索​

-- 在MySQL 5.7+的InnoDB引擎创建全文索引
ALTER TABLE articles ADD FULLTEXT ft_content(content);

-- 使用MATCH AGAINST进行搜索
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('数据库优化' IN NATURAL LANGUAGE MODE);

3. 索引设计原则​

• 覆盖原则： 常用查询字段尽量包含在索引中​
• 最左匹配： 组合索引遵循左前缀匹配规则​
• 空间平衡： 避免在低选择性字段（如性别）创建索引​
• 写入性能： 过多索引会影响 INSERT/UPDATE/DELETE 速度

四、特殊场景处理​

1. 非主键自增字段​

-- 场景：为历史表添加自增流水号（非主键）
ALTER TABLE sys_log 
ADD COLUMN log_id BIGINT AUTO_INCREMENT FIRST,
ADD UNIQUE (log_id); -- 必须添加唯一约束

-- 注意：一张表只能有一个AUTO_INCREMENT字段，且需为索引字段

2. 批量修改操作​

-- 同时新增字段+修改约束+创建索引
ALTER TABLE user 
ADD COLUMN nick_name VARCHAR(50) NOT NULL AFTER username,
MODIFY COLUMN age TINYINT DEFAULT 18,
ADD INDEX idx_name_age(nick_name, age);

​