MySQL 数据库与表

数据库与表是MySQL存储数据的基础，合理的数据库与表设计直接影响到数据库的性能、可维护性和扩展性。本文将详细介绍MySQL数据库与表的创建与管理、表设计原则、数据类型选择和生产环境最佳实践。

数据库创建与管理

创建数据库

sql

-- 基本创建数据库
CREATE DATABASE mydb;

-- 指定字符集和校对规则
CREATE DATABASE mydb 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

-- 只有当数据库不存在时才创建
CREATE DATABASE IF NOT EXISTS mydb;

-- 查看当前数据库列表
SHOW DATABASES;

修改数据库

sql

-- 修改数据库字符集和校对规则
ALTER DATABASE mydb 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

-- 查看数据库信息
SHOW CREATE DATABASE mydb;

删除数据库

sql

-- 删除数据库
DROP DATABASE mydb;

-- 只有当数据库存在时才删除
DROP DATABASE IF EXISTS mydb;

切换数据库

sql

-- 切换到指定数据库
USE mydb;

-- 查看当前使用的数据库
SELECT DATABASE();

表设计原则

范式设计

第一范式（1NF）：确保每列都是原子性的，不可再分
第二范式（2NF）：在1NF基础上，确保表中的非主键列完全依赖于主键
第三范式（3NF）：在2NF基础上，确保表中的非主键列不传递依赖于主键
BCNF（巴斯-科德范式）：在3NF基础上，确保表中的每个决定因素都是候选键

反范式设计

在某些情况下，为了提高查询性能，可以适当违反范式设计：

添加冗余列：减少JOIN操作
添加派生列：提前计算并存储结果，减少计算开销
分区表：将大表分成多个小表，提高查询性能
物化视图：存储查询结果，加速复杂查询

表命名规范

使用有意义的名称：表名应反映表的用途，如users、orders
使用小写字母：避免大小写敏感性问题
使用下划线分隔：如user_profiles，避免使用驼峰命名
避免使用保留字：如table、select等
添加前缀：对于大型系统，可以添加业务前缀，如ecom_users

表创建与管理

创建表

sql

-- 基本创建表
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 创建带索引的表
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  order_no VARCHAR(32) NOT NULL UNIQUE,
  amount DECIMAL(10,2) NOT NULL,
  status TINYINT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 创建分区表
CREATE TABLE logs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  level VARCHAR(10) NOT NULL,
  message TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION BY RANGE (YEAR(log_time)) (
  PARTITION p2020 VALUES LESS THAN (2021),
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION pfuture VALUES LESS THAN MAXVALUE
);

修改表

sql

-- 添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL;

-- 修改列
ALTER TABLE users MODIFY COLUMN phone VARCHAR(20) NOT NULL;

-- 重命名列
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(20) NOT NULL;

-- 删除列
ALTER TABLE users DROP COLUMN mobile;

-- 添加索引
ALTER TABLE users ADD INDEX idx_created_at (created_at);

-- 添加唯一索引
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

-- 添加外键约束
ALTER TABLE orders ADD CONSTRAINT fk_orders_user_id FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- 删除索引
ALTER TABLE users DROP INDEX idx_created_at;

-- 重命名表
ALTER TABLE old_table_name RENAME TO new_table_name;

删除表

sql

-- 删除表
DROP TABLE users;

-- 只有当表存在时才删除
DROP TABLE IF EXISTS users;

-- 删除多个表
DROP TABLE IF EXISTS users, orders, products;

查看表信息

sql

-- 查看数据库中的表
SHOW TABLES;

-- 查看表结构
DESCRIBE users;

-- 查看表创建语句
SHOW CREATE TABLE users;

-- 查看表状态
SHOW TABLE STATUS LIKE 'users';

数据类型选择

数值类型

类型	大小	范围	用途
TINYINT	1字节	-128 到 127	小整数值
SMALLINT	2字节	-32768 到 32767	整数值
MEDIUMINT	3字节	-8388608 到 8388607	整数值
INT	4字节	-2147483648 到 2147483647	大整数值
BIGINT	8字节	-9223372036854775808 到 9223372036854775807	极大整数值
DECIMAL	可变	取决于M和D	精确小数值
FLOAT	4字节	-3.402823466E+38 到 -1.175494351E-38，0，1.175494351E-38 到 3.402823466E+38	单精度浮点数值
DOUBLE	8字节	-1.7976931348623157E+308 到 -2.2250738585072014E-308，0，2.2250738585072014E-308 到 1.7976931348623157E+308	双精度浮点数值

字符串类型

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535字节	变长字符串
TINYTEXT	0-255字节	短文本字符串
TEXT	0-65535字节	长文本数据
MEDIUMTEXT	0-16777215字节	中等长度文本数据
LONGTEXT	0-4294967295字节	极大文本数据
BINARY	0-255字节	定长二进制字符串
VARBINARY	0-65535字节	变长二进制字符串
BLOB	0-65535字节	二进制长文本数据

日期和时间类型

类型	大小	范围	格式	用途
DATE	3字节	1000-01-01 到 9999-12-31	YYYY-MM-DD	日期值
TIME	3字节	-838:59:59 到 838:59:59	HH:MM:SS	时间值或持续时间
YEAR	1字节	1901 到 2155	YYYY	年份值
DATETIME	8字节	1000-01-01 00:00:00 到 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4字节	1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

数据类型选择最佳实践

选择合适的数值类型：根据实际数据范围选择最小的数值类型
优先使用VARCHAR而非CHAR：除非数据长度固定，如身份证号、手机号
避免使用TEXT/BLOB存储小数据：对于小于64KB的数据，优先使用VARCHAR
使用DATETIME而非TIMESTAMP：除非需要自动更新时间戳或与Unix时间戳兼容
使用DECIMAL存储货币和精确数值：避免浮点精度问题
使用ENUM存储固定选项：如性别、状态等

表优化

分区表

分区表可以将大表分成多个小表，提高查询性能：

sql

-- 按范围分区
CREATE TABLE sales (
  id INT PRIMARY KEY AUTO_INCREMENT,
  sale_date DATE NOT NULL,
  amount DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(sale_date)) (
  PARTITION p2020 VALUES LESS THAN (2021),
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION pfuture VALUES LESS THAN MAXVALUE
);

-- 按列表分区
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  status TINYINT NOT NULL,
  amount DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB
PARTITION BY LIST (status) (
  PARTITION p_active VALUES IN (1, 2, 3),
  PARTITION p_inactive VALUES IN (0, 4, 5),
  PARTITION p_other VALUES IN (6, 7, 8, 9)
);

-- 按哈希分区
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL
) ENGINE=InnoDB
PARTITION BY HASH (id) PARTITIONS 4;

分表

对于非常大的表，可以考虑垂直分表或水平分表：

垂直分表：将表中的列分成多个表，减少每行的宽度
水平分表：将表中的行分成多个表，减少每个表的行数

表结构优化

删除不必要的列：只保留必要的数据
使用合适的数据类型：避免使用过大的数据类型
添加适当的索引：提高查询性能
避免过多的索引：索引会增加插入和更新的开销
使用NOT NULL约束：避免NULL值带来的性能问题

版本差异处理

MySQL 5.6 表特性

InnoDB默认引擎：从MySQL 5.5开始，InnoDB成为默认引擎
全文索引支持：InnoDB支持全文索引
虚拟列：支持生成列（虚拟列）
表空间管理：支持独立表空间

MySQL 5.7 表特性

JSON数据类型：原生支持JSON数据类型
生成列：支持存储生成列和虚拟生成列
空间数据类型：增强了空间数据类型支持
表级锁优化：减少了表级锁的使用

MySQL 8.0 表特性

隐藏列：支持隐藏列，不显示在SELECT *结果中
降序索引：支持真正的降序索引
原子DDL：支持原子DDL操作，要么全部成功，要么全部失败
自增列增强：自增列的处理更加灵活
通用表表达式：支持WITH子句
窗口函数：支持复杂的数据分析

生产环境最佳实践

表设计最佳实践

遵循3NF原则：确保数据的一致性和完整性
适当反范式：在性能需求明确时，适当违反范式
使用合适的数据类型：根据实际数据选择最小的数据类型
添加适当的索引：提高查询性能
使用NOT NULL约束：避免NULL值带来的性能问题
添加适当的注释：提高代码的可维护性

表管理最佳实践

定期检查表结构：确保表结构符合业务需求
定期优化表：使用OPTIMIZE TABLE命令优化表
监控表空间使用情况：及时发现并处理表空间增长过快的问题
定期备份表结构：避免表结构丢失
使用事务管理：确保数据的一致性和完整性

性能优化最佳实践

使用分区表：提高大表的查询性能
避免大表：对于超过1000万行的表，考虑分表
使用连接池：减少连接开销
优化查询语句：避免全表扫描，使用适当的索引
监控慢查询：及时发现并优化慢查询

高可用最佳实践

使用主从复制：提高数据库的可用性
使用集群：如MySQL Group Replication、Percona XtraDB Cluster
定期备份：确保数据的安全性和可恢复性
使用延迟从库：用于应对误删除等场景

结论

数据库与表是MySQL存储数据的基础，合理的设计和管理直接影响到数据库的性能、可维护性和扩展性。通过遵循表设计原则、选择合适的数据类型、优化表结构和管理，可以提高数据库的性能和可靠性。

在实际生产环境中，应该根据业务需求和技术条件选择合适的表设计和管理策略，并定期进行优化和维护，确保数据库的高性能和高可用性。

MySQL 数据库与表 ​

数据库创建与管理 ​

创建数据库 ​

修改数据库 ​

删除数据库 ​

切换数据库 ​

表设计原则 ​

范式设计 ​

反范式设计 ​

表命名规范 ​

表创建与管理 ​

创建表 ​

修改表 ​

删除表 ​

查看表信息 ​

数据类型选择 ​

数值类型 ​

字符串类型 ​

日期和时间类型 ​

数据类型选择最佳实践 ​

表优化 ​

分区表 ​

分表 ​

表结构优化 ​

版本差异处理 ​

MySQL 5.6 表特性 ​

MySQL 5.7 表特性 ​

MySQL 8.0 表特性 ​

生产环境最佳实践 ​

表设计最佳实践 ​

表管理最佳实践 ​

性能优化最佳实践 ​

高可用最佳实践 ​

结论 ​