MySQL数据库表设计与创建指南

一、数据库设计基础

1. 设计原则

• 规范化设计：遵循1NF、2NF、3NF等范式减少数据冗余
• 明确主键：每个表应有主键(单列或复合主键)
• 合理命名：使用有意义的表名和字段名(建议下划线命名法)
• 数据类型选择：根据数据特性选择合适的数据类型

2. 设计步骤

1. 确定业务需求和数据关系
2. 绘制ER图(实体关系图)
3. 将ER图转换为关系模型
4. 应用规范化理论优化设计

二、MySQL表创建实操

1. 创建数据库

CREATE DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE my_database;

2. 基本表创建语法

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
    PRIMARY KEY (one_or_more_columns)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3. 常用数据类型

• 整数：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
• 小数：FLOAT, DOUBLE, DECIMAL(精确小数)
• 字符串：CHAR(定长), VARCHAR(变长), TEXT(长文本)
• 日期时间：DATE, TIME, DATETIME, TIMESTAMP
• 二进制：BLOB, LONGBLOB

4. 约束类型

• PRIMARY KEY - 主键约束
• FOREIGN KEY - 外键约束
• NOT NULL - 非空约束
• UNIQUE - 唯一约束
• DEFAULT - 默认值
• CHECK - 检查约束(MySQL 8.0+支持)

三、完整示例

1. 用户表设计

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
) ENGINE=InnoDB;

2. 产品表设计

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    category_id INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
) ENGINE=InnoDB;

3. 订单表设计

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(12,2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    FOREIGN KEY (user_id) REFERENCES users(user_id)
) ENGINE=InnoDB;

四、表结构修改

1. 添加列

ALTER TABLE users ADD COLUMN last_login DATETIME AFTER updated_at;

2. 修改列

ALTER TABLE products MODIFY COLUMN product_name VARCHAR(150) NOT NULL;

3. 删除列

ALTER TABLE users DROP COLUMN phone;

4. 添加索引

ALTER TABLE products ADD INDEX idx_category (category_id);

五、设计工具推荐

1. MySQL Workbench：官方可视化工具，支持ER图设计
2. Navicat：功能强大的数据库管理工具
3. dbdiagram.io：在线数据库设计工具
4. PowerDesigner：专业数据建模工具

六、最佳实践建议

1. 为每个表添加注释说明用途
2. 为重要字段添加注释
3. 考虑使用存储引擎(InnoDB推荐)
4. 合理设置字符集(推荐utf8mb4)
5. 为常用查询条件创建适当索引
6. 考虑分区表设计处理大数据量
7. 设计时考虑未来可能的扩展需求

通过以上步骤和方法，您可以系统地设计和创建MySQL数据库表结构，为应用程序提供高效、可靠的数据存储基础。