MySQL8数据表005-CSDN博客

文章目录

要理解MySQL 8中表结构的核心概念（主键、NULL约束、自增、外键等），我们分 问题拆解+ 示例演示的方式逐一说明：

一、基础概念与约束

1. 主键列的`NULL`允许性？

结论：主键列必须NOT NULL，且唯一（UNIQUE）。
MySQL主键的定义隐含了NOT NULL和UNIQUE约束，因此主键列无法插入NULL值。
错误示例：

CREATE TABLE test_pk (
  id INT PRIMARY KEY,  -- 主键隐含NOT NULL
  name VARCHAR(50)
);
INSERT INTO test_pk (id, name) VALUES (NULL, '张三');  -- 报错：Column 'id' cannot be null

2. 复合主键（多列组成主键）？

结论：可以。复合主键由多个列联合组成，确保组合值唯一。
示例（学生-课程表，确保每个学生每门课程只有一条记录）：

CREATE TABLE student_course (
  student_id INT NOT NULL,  -- 学生ID
  course_id INT NOT NULL,   -- 课程ID
  score INT NOT NULL,       -- 成绩
  PRIMARY KEY (student_id, course_id)  -- 复合主键
);

说明：student_id和course_id的组合必须唯一，比如(1, 101)和(1, 102)是允许的，但(1, 101)不能重复插入。

3. 自增列（`AUTO_INCREMENT`）的规则？

数据类型限制：自增列只能是整数类型（TINYINT、SMALLINT、INT、BIGINT等），不支持字符类型（如VARCHAR、CHAR）。
错误示例：
```
CREATE TABLE test_auto (
  id VARCHAR(10) AUTO_INCREMENT PRIMARY KEY  -- 报错：Invalid data type for auto_increment
);
```

初始值与序列连续性：
自增列的序列不会因删除数据而回滚（即删除行后，下次插入的ID会继续递增）。
示例：

CREATE TABLE test_auto (
  id INT AUTO_INCREMENT PRIMARY KEY,
  value VARCHAR(50)
);
-- 插入3行，id=1、2、3
INSERT INTO test_auto (value) VALUES ('a'), ('b'), ('c');
-- 删除id=2、3的行
DELETE FROM test_auto WHERE id IN (2, 3);
-- 再次插入，id=4（不会回到2）
INSERT INTO test_auto (value) VALUES ('d');
SELECT * FROM test_auto;  -- 结果：id=1、4

步长设置：默认步长为1，但可通过全局变量或会话变量修改（不建议全局修改，避免影响其他表）。
示例（设置步长为2）：

-- 全局设置（重启后失效）
SET GLOBAL auto_increment_increment = 2;
-- 会话设置（仅当前连接有效）
SET SESSION auto_increment_increment = 2;

CREATE TABLE test_step (
  id INT AUTO_INCREMENT PRIMARY KEY,
  value VARCHAR(50)
);
INSERT INTO test_step (value) VALUES ('a'), ('b'), ('c');
SELECT id FROM test_step;  -- 结果：1、3、5

二、插入数据与表结构完善

1. 插入数据的基本语法

示例（向users表插入数据）：

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,  -- 姓名必填
  email VARCHAR(100) UNIQUE NOT NULL,  -- 邮箱唯一且必填
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP  -- 默认当前时间
);

-- 插入1行（id自增）
INSERT INTO users (name, email) VALUES ('张三', 'zhangsan@example.com');
-- 插入多行
INSERT INTO users (name, email) VALUES 
  ('李四', 'lisi@example.com'),
  ('王五', 'wangwu@example.com');

2. 主外键关系（核心关联）

定义：外键（FOREIGN KEY）是从表中参考主表主键（或唯一键）的列，用于维护数据完整性（如订单表的user_id必须对应用户表的id）。
建立条件：

主表的参考列必须是主键或唯一键（UNIQUE）；
主外键的数据类型必须一致（如主表id是INT，从表user_id也必须是INT）；
从表的外键列可以是NOT NULL（建议），也可以是NULL（需配合ON DELETE SET NULL）。

示例（用户表-订单表的主外键关系）：

-- 主表：用户表（users）
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL
);

-- 从表：订单表（orders），建立外键参考用户表的id
CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,  -- 外键列（参考users.id）
  order_date DATE NOT NULL,
  total DECIMAL(10,2) NOT NULL,  -- 金额（精确到分）
  -- 定义外键（两种方式）
  -- 方式1：创建表时定义
  FOREIGN KEY (user_id) REFERENCES users(id) 
    ON DELETE CASCADE  -- 删除用户时， cascade 删除其订单
    ON UPDATE CASCADE  -- 更新用户id时， cascade 更新订单的user_id
);

-- 方式2：创建表后用ALTER TABLE添加（推荐，可自定义外键名称）
ALTER TABLE orders 
ADD CONSTRAINT fk_orders_users  -- 外键名称（便于管理）
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

3. 主外键关系的图形化表示（ER图）

使用MySQL Workbench（官方图形化工具）可直观展示表间关系：

主表（users）的主键（id）用钥匙图标标记；
从表（orders）的外键（user_id）用链条图标标记；
主外键之间用线段连接，箭头指向主表（表示“参考”关系）。
示例ER图结构：

users（用户表）          orders（订单表）
+----+---------+---------+  +----------+---------+------------+--------+
| id | name    | email   |  | order_id | user_id | order_date | total  |
+----+---------+---------+  +----------+---------+------------+--------+
| 1  | 张三    | zs@xx.com|  | 1001     | 1       | 2025-07-11 | 100.50 |
| 2  | 李四    | ls@xx.com|  | 1002     | 2       | 2025-07-12 | 200.00 |
+----+---------+---------+  +----------+---------+------------+--------+
      ↓（主键）                ↑（外键）
      └────────────────────────┘（参考关系）

三、主外键建立后的注意事项

数据完整性检查：
从表插入/更新外键值时，必须确保主表存在对应记录，否则报错。
错误示例：

-- 用户表无id=3的记录
INSERT INTO orders (user_id, order_date, total) VALUES (3, '2025-07-13', 300.00);  
-- 报错：Cannot add or update a child row: a foreign key constraint fails

删除/更行为：
外键约束的ON DELETE和ON UPDATE选项决定了主表数据变化时从表的处理方式：
- CASCADE：主表删除/更新时，从表对应的行同步删除/更新（最常用，如删除用户时删除其订单）；
- SET NULL：主表删除/更新时，从表对应的外键列设为NULL（需从表外键列允许NULL）；
- RESTRICT/NO ACTION：主表删除/更新时，若从表有对应记录，则禁止操作（默认行为）。
性能影响：
外键约束会增加插入/更新/删除的开销（需检查主表数据），高并发场景下可考虑应用层维护数据完整性（如用代码校验外键存在性）。

四、练习：创建数据库关系图（MySQL Workbench）

打开MySQL Workbench，连接数据库；
点击Database → Reverse Engineer（反向工程），选择要生成ER图的数据库；
选择要包含的表（如users、orders），点击Next直到完成；
在ER图界面，拖动表调整位置，可看到主外键的连接关系（如orders.user_id参考users.id）；
点击File → Save Model保存ER图（.mwb格式）。

五、总结：创建表的最佳实践

字段类型选择：
- 字符串：优先用VARCHAR（可变长度，节省空间），而非CHAR（固定长度）；
- 整数：根据需求选择TINYINT（1字节）、INT（4字节）、BIGINT（8字节）；
- 日期：DATE（仅日期，如2025-07-11）、DATETIME（日期+时间，如2025-07-11 14:30:00）；
- 小数：DECIMAL（精确小数，适合金额，如DECIMAL(10,2)表示最多10位，2位小数）。
约束使用：
- 主键：必选（PRIMARY KEY），用于唯一标识行；
- 非空：必填字段用NOT NULL（如name、email）；
- 唯一：唯一字段用UNIQUE（如email，避免重复注册）；
- 外键：如需关联表，用FOREIGN KEY（维护数据完整性）。
自增列：
- 仅用于整数类型；
- 必为主键或唯一键的一部分（否则报错）；
- 避免修改自增序列（如ALTER TABLE test_auto AUTO_INCREMENT = 100;，可能导致重复ID）。

六、扩展示例：复杂表结构（含主外键）

需求：创建一个简单的电商系统表结构，包含用户表、订单表、商品表、订单商品表（多对多关系）。
SQL实现：

-- 1. 用户表（主表）
CREATE TABLE users (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 2. 商品表（主表）
CREATE TABLE products (
  product_id INT AUTO_INCREMENT PRIMARY KEY,
  product_name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0  -- 库存默认0
);

-- 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(10,2) NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

-- 4. 订单商品表（中间表，连接订单表与商品表，复合主键）
CREATE TABLE order_items (
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL DEFAULT 1,  -- 数量默认1
  unit_price DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id),  -- 复合主键（订单+商品唯一）
  FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE
);

说明：

订单表（orders）的user_id参考用户表（users）的user_id；
订单商品表（order_items）是中间表，用于处理订单与商品的多对多关系（一个订单可包含多个商品，一个商品可出现在多个订单中）；
中间表的复合主键（order_id+product_id）确保每个商品在订单中只出现一次。

七、常见问题排查

外键创建失败：
- 检查主表是否存在；
- 检查主外键数据类型是否一致；
- 检查主表的参考列是否是主键或唯一键。
自增列重复：
- 避免手动插入自增列的值（如INSERT INTO users (user_id, username) VALUES (1, '张三');）；
- 若必须手动插入，插入后需用ALTER TABLE users AUTO_INCREMENT = (SELECT MAX(user_id) + 1 FROM users);重置自增序列。
NULL值问题：
- 避免将NULL插入NOT NULL列；
- 使用DEFAULT约束设置默认值（如created_at DATETIME DEFAULT CURRENT_TIMESTAMP），减少NULL的使用。