MySQL表操作深度剖析：构建与管控全流程揭秘

MySQL表操作全面解析：构造与管理全流程揭秘

MySQL研习：

前言：

在上一次我们掌握了库的创建和运用，表是存储数据的关键构造。本文将全方位讲解MySQL里关于表的各类操作，涵盖创建、修改、删除等，并且深入探究相关的知识点和需要留意的地方。

表的基础概念

在MySQL中，表是存储数据的主要对象，由行和列构成。理解表的基本构造对数据库设计至关重要。

表的主要组成部分

创建表

基本语法

CREATE TABLE [IF NOT EXISTS] table_name (
    column1 datatype [constraints] [COMMENT 'column_comment'],
    column2 datatype [constraints] [COMMENT 'column_comment'],
    ...,
    [table_constraints]
) 
[ENGINE=storage_engine] 
[CHARACTER SET charset] 
[COLLATE collation]
[COMMENT 'table_comment']
[PARTITION BY partition_type (partition_expression)];

语法各部分详细解读 ：

1. CREATE TABLE ：创建表的核心关键字

2. [IF NOT EXISTS] ：可选子句，防止表已存在时出错

3. table_name ：要创建的表的名称（遵循命名规则）

4. 列定义部分 ：

   • column1, column2：列名（字段名）

   • datatype：列的数据类型（如INT, VARCHAR等）

   • [constraints]：可选的列约束（如NOT NULL, UNIQUE等）

   • [COMMENT]：可选的列注释

5. table_constraints ：表级约束（如PRIMARY KEY, FOREIGN KEY等）

6. ENGINE ：指定表的存储引擎（如InnoDB, MyISAM）

7. CHARACTER SET ：指定表的字符集（如utf8mb4）

8. COLLATE ：指定表的排序规则（如utf8mb4_general_ci）

9. COMMENT ：可选的表注释

10. PARTITION BY ：可选的分区定义

关键参数说明

数据类型详解

MySQL支持多种数据类型，主要分为几大类：

数值类型 ：

• 整数：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT

• 小数：FLOAT, DOUBLE, DECIMAL(精确小数)

字符串类型 ：

• 定长：CHAR(0-255)

• 变长：VARCHAR(0-65535)

• 文本：TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT

• 二进制：BLOB系列

日期时间类型 ：

• DATE：YYYY-MM-DD

• TIME：HH:MM:SS

• DATETIME：YYYY-MM-DD HH:MM:SS

• TIMESTAMP：时间戳（1970-2038）

• YEAR：年份

其他类型 ：

• ENUM：枚举值

• SET：集合

• JSON：MySQL 5.7+支持

约束类型详解

完整示例

创建一个学生表student

CREATE TABLE Student
    (Sno CHAR(8) PRIMARY KEY,  /*列级完整性约束条件，Sno是主码*/
     Sname VARCHAR(20) UNIQUE, /*sname取唯一值*/
     Ssex CHAR(6),
     Sbirthdate Date,
     Smajor VARCHAR(40)
    )character set utf8 engine MyISAM;

创建一个员工表，此表较为复杂，涉及约束问题，简单了解即可，后续会针对约束问题专门开一篇

CREATE TABLE IF NOT EXISTS employees (
    emp_id INT AUTO_INCREMENT COMMENT '员工ID，自动递增',
    first_name VARCHAR(50) NOT NULL COMMENT '名字',
    last_name VARCHAR(50) NOT NULL COMMENT '姓氏',
    email VARCHAR(100) UNIQUE COMMENT '邮箱地址，唯一',
    hire_date DATE NOT NULL COMMENT '入职日期',
    salary DECIMAL(10,2) CHECK (salary > 0) COMMENT '薪水，必须大于0',
    dept_id INT COMMENT '部门ID',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录最后更新时间',

    -- 表级约束
    PRIMARY KEY (emp_id),
    INDEX idx_name (last_name, first_name) COMMENT '姓名组合索引',
    CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
) 
ENGINE=InnoDB 
CHARACTER SET=utf8mb4 
COLLATE=utf8mb4_unicode_ci
COMMENT='公司员工信息表';

创建表时的留意事项

1. 命名规范 ：

 * 使用有意义的名称（如employees而非tbl1）

 * 建议使用小写字母和下划线组合（如employee_salaries）

 * 避免使用MySQL保留字（如order, group等）

 * 保持一致性（如全部单数或复数形式）

1. 数据类型选取 ：

 * 选取能满足需求的最小数据类型（如用TINYINT存储0-100的值）

 * 考虑未来扩展性（如VARCHAR长度适当放大）

 * 对于字符串，VARCHAR比CHAR更节省空间（除非长度固定）

 * 金额等精确计算使用DECIMAL而非FLOAT/DOUBLE

1. 主键设计 ：

 * 每表应有主键

 * 优先使用自增整数（简单高效）

 * 复合主键谨慎使用

 * 避免使用业务数据作为主键

1. 存储引擎选取 ：

 * InnoDB：支持事务、外键（MySQL 5.5+默认）

 * MyISAM：全文本搜索，但不支持事务（MySQL 5.5前默认）

 * MEMORY：数据存储在内存中

1. 字符集选取 ：

 * 推荐utf8mb4而非utf8（完整支持Unicode包括emoji）

 * 数据库、表、列字符集保持一致

1. 索引设计 ：

 * 不要过度索引（影响写入性能）

 * 为常用查询条件创建索引

 * 考虑组合索引的顺序

关于索引和表的约束的问题，我们留在后面几篇细讲，下面出现的所有的关于索引和表的约束的问题都可以先忽略

查看表结构

show tables;

通过这个可以查看当前所在数据库中有哪些表

desc 表名;

而这个语句能帮助直接查看指定数据表的结构

修改表结构

随着需求变化，常需修改已有表的结构。

常用ALTER TABLE操作详解

修改表示例

-- 添加新列（指定位置）
ALTER TABLE employees ADD COLUMN phone VARCHAR(20) NOT NULL AFTER email;

-- 修改列数据类型和约束
ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12,2) CHECK (salary >= 0);

-- 重命名列（必须指定数据类型）
ALTER TABLE employees CHANGE COLUMN phone mobile_phone VARCHAR(20);

-- 添加外键约束（命名约束）
ALTER TABLE employees ADD CONSTRAINT fk_department 
FOREIGN KEY (dept_id) REFERENCES departments(id)
ON DELETE SET NULL ON UPDATE CASCADE;

-- 添加组合索引
ALTER TABLE employees ADD INDEX idx_name_department (last_name, dept_id);

-- 修改表注释
ALTER TABLE employees COMMENT='公司员工基本信息表';

-- 重命名表（两种方式）
ALTER TABLE employees RENAME TO staff;
-- 或
RENAME TABLE employees TO staff;

-- 修改存储引擎
ALTER TABLE employees ENGINE=InnoDB;

-- 修改字符集（转换现有数据）
ALTER TABLE employees CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

修改表时的留意事项

1. 大表修改 ：

 * 大表结构变更可能锁表很长时间（百万级以上）

 * 考虑使用在线DDL工具（pt-online-schema-change）

 * 在低峰期执行

 * 先创建新表再迁移数据（对于重大变更）

1. 数据类型变更风险 ：

 * 缩小数据类型可能导致数据截断（如VARCHAR(100)改为VARCHAR(50)）

 * 某些类型转换可能不支持（如TEXT转INT）

 * 变更前检查数据兼容性

1. 外键约束 ：

 * 添加外键前确保引用数据已存在

 * 删除有外键引用的表需先删除约束

 * 考虑外键操作（ON DELETE/UPDATE）

1. 测试环境验证 ：

 * 所有结构变更先在测试环境验证

 * 备份重要数据（特别是生产环境）

 * 考虑使用事务（部分ALTER支持）

1. 索引管理 ：

 * 添加索引可能加快查询但减慢写入

 * 监控未使用索引

 * 组合索引顺序很重要（最左前缀原则）

删除表

删除表是不可逆操作，需谨慎执行。

基本语法详解

DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name2...] 
[RESTRICT | CASCADE];

语法各部分详细说明 ：

1. DROP TABLE ：删除表的核心关键字

2. [TEMPORARY] ：可选，仅删除临时表

3. [IF EXISTS] ：避免表不存在时报错

4. table_name ：要删除的表名（可多个，逗号分隔）

5. [RESTRICT] ：默认选项，如果有依赖对象则拒绝删除

6. [CASCADE] ：级联删除依赖对象（慎用）

删除表示例

-- 安全删除单个表
DROP TABLE IF EXISTS temp_users;

-- 删除多个表
DROP TABLE temp_table1, temp_table2;

-- 仅删除临时表
DROP TEMPORARY TABLE temp_session_data;

-- 使用CASCADE强制删除（慎用）
DROP TABLE departments CASCADE;

相关删除操作对比

删除表时的留意事项

1. 备份数据 ：

   • 删除前确认数据已备份

   • 重要表考虑先重命名而非直接删除

   • 使用CREATE TABLE new_table SELECT * FROM old_table快速备份

2. 依赖关系检查 ：

   • 检查是否有视图、存储过程、触发器依赖该表

   • 检查外键约束

     SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME,
     REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
     FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
     WHERE REFERENCED_TABLE_NAME = '要删除的表名';

   • 权限控制 ：

   • 限制DROP TABLE权限

   • 使用数据库角色管理权限

   • 生产环境实施权限分离

   • 替代方案 ：

   • 考虑TRUNCATE TABLE清空数据但保留结构

   • 临时表可使用CREATE TEMPORARY TABLE自动会话结束时删除

   • 归档数据而非删除

   • 事务考虑 ：

   • DROP TABLE是隐式提交的操作（即使在使用事务中）

   • 不能在事务中回滚DROP TABLE操作

表操作的最佳实践

设计阶段

1. 规范化设计 ：

 * 遵循适当的范式（通常3NF）

 * 平衡规范化和性能需求

 * 避免过度规范化导致的复杂连接

1. 命名约定 ：

2. 文档化 ：

 * 维护数据字典

 * 使用注释说明表和列的用途

 * 记录变更历史