数据库设计测试用例¶
测试目标: 验证数据库设计的正确性和性能 测试类型: 设计测试、范式测试、约束测试、性能测试 涉及组件: 表设计、索引设计、约束设计、关系设计
📋 测试概述¶
测试目标¶
- 设计测试: 验证数据库设计的合理性
- 范式测试: 验证数据库范式符合性
- 约束测试: 验证约束的正确性
- 性能测试: 验证设计的性能
测试环境¶
- 数据库: MySQL 8.0+, PostgreSQL 13+
- 测试框架: pytest
- 设计工具: ER图工具
🧪 测试用例列表¶
1. 表设计测试¶
测试用例1.1: 表创建¶
测试目标: 验证表创建的正确性
测试代码:
Python
import pytest
import mysql.connector
from mysql.connector import Error
def test_table_creation():
"""测试表创建"""
conn = mysql.connector.connect(
host='localhost',
user='test_user',
password='test_password',
database='test_db'
)
cursor = conn.cursor(dictionary=True)
try:
# 创建表
create_table_query = """
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
age INT CHECK (age >= 0 AND age <= 150),
status ENUM('active', 'inactive', 'pending') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
"""
cursor.execute(create_table_query)
# 验证表创建
cursor.execute("SHOW TABLES LIKE 'users'")
result = cursor.fetchone()
assert result is not None, "表创建失败"
assert 'users' in result
# 验证表结构
cursor.execute("DESCRIBE users")
columns = cursor.fetchall()
column_names = [col['Field'] for col in columns]
assert 'id' in column_names
assert 'name' in column_names
assert 'email' in column_names
assert 'age' in column_names
assert 'status' in column_names
assert 'created_at' in column_names
assert 'updated_at' in column_names
print("✓ 表创建测试通过")
finally:
cursor.execute("DROP TABLE IF EXISTS users")
conn.commit()
cursor.close()
conn.close()
预期结果: 表成功创建，结构正确
测试用例1.2: 数据类型¶
测试目标: 验证数据类型选择
测试代码:
Python
def test_data_types():
"""测试数据类型"""
conn = mysql.connector.connect(
host='localhost',
user='test_user',
password='test_password',
database='test_db'
)
cursor = conn.cursor()
try:
# 创建包含各种数据类型的表
create_table_query = """
CREATE TABLE test_types (
id INT AUTO_INCREMENT PRIMARY KEY,
int_col INT,
varchar_col VARCHAR(255),
text_col TEXT,
decimal_col DECIMAL(10, 2),
date_col DATE,
datetime_col DATETIME,
boolean_col BOOLEAN,
json_col JSON
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
"""
cursor.execute(create_table_query)
# 插入测试数据
insert_query = """
INSERT INTO test_types (
int_col, varchar_col, text_col, decimal_col,
date_col, datetime_col, boolean_col, json_col
) VALUES (
100, 'Test', 'Long text content', 123.45,
'2024-01-01', '2024-01-01 12:00:00', TRUE,
'{"key": "value"}'
)
"""
cursor.execute(insert_query)
conn.commit()
# 验证数据插入
cursor.execute("SELECT * FROM test_types")
result = cursor.fetchone()
assert result[1] == 100 # int_col
assert result[2] == 'Test' # varchar_col
assert result[3] == 'Long text content' # text_col
assert abs(result[4] - 123.45) < 0.01 # decimal_col
assert result[5] == '2024-01-01' # date_col
assert result[6] is not None # datetime_col
assert result[7] == 1 # boolean_col
assert result[8] is not None # json_col
print("✓ 数据类型测试通过")
finally:
cursor.execute("DROP TABLE IF EXISTS test_types")
conn.commit()
cursor.close()
conn.close()
预期结果: 所有数据类型正确存储和检索
2. 范式测试¶
测试用例2.1: 第一范式(1NF)¶
测试目标: 验证第一范式
测试代码:
Python
def test_first_normal_form():
"""测试第一范式"""
conn = mysql.connector.connect(
host='localhost',
user='test_user',
password='test_password',
database='test_db'
)
cursor = conn.cursor()
try:
# 创建符合1NF的表
create_table_query = """
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
price DECIMAL(10, 2) NOT NULL,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
"""
cursor.execute(create_table_query)
# 验证原子性
# 每个字段都是原子的，不可再分
cursor.execute("DESCRIBE orders")
columns = cursor.fetchall()
# 验证没有重复组
# 1NF要求没有重复的列组
# 这里简化验证，实际应该检查所有列的组合
print("✓ 第一范式测试通过")
finally:
cursor.execute("DROP TABLE IF EXISTS orders")
conn.commit()
cursor.close()
conn.close()
预期结果: 表符合第一范式
测试用例2.2: 第二范式(2NF)¶
测试目标: 验证第二范式
测试代码:
Python
def test_second_normal_form():
"""测试第二范式"""
conn = mysql.connector.connect(
host='localhost',
user='test_user',
password='test_password',
database='test_db'
)
cursor = conn.cursor()
try:
# 创建符合2NF的表
# 2NF要求：1) 符合1NF 2) 所有非主属性完全依赖于主键
create_table_query = """
CREATE TABLE order_items (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(id),
FOREIGN KEY (product_id) REFERENCES products(id),
UNIQUE KEY (order_id, product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
"""
cursor.execute(create_table_query)
# 验证完全依赖
# 所有非主属性都完全依赖于主键
# quantity和unit_price依赖于order_id和product_id的组合
print("✓ 第二范式测试通过")
finally:
cursor.execute("DROP TABLE IF EXISTS order_items")
conn.commit()
cursor.close()
conn.close()
预期结果: 表符合第二范式
测试用例2.3: 第三范式(3NF)¶
测试目标: 验证第三范式
测试代码:
Python
def test_third_normal_form():
"""测试第三范式"""
conn = mysql.connector.connect(
host='localhost',
user='test_user',
password='test_password',
database='test_db'
)
cursor = conn.cursor()
try:
# 创建符合3NF的表
# 3NF要求：1) 符合2NF 2) 没有传递依赖
# 用户表
create_users_query = """
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
city_id INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (city_id) REFERENCES cities(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
"""
# 城市表
create_cities_query = """
CREATE TABLE cities (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
country VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
"""
cursor.execute(create_cities_query)
cursor.execute(create_users_query)
# 验证无传递依赖
# 用户表中的city_id依赖于id，而不是name
# 避免了用户->城市->国家的传递依赖
print("✓ 第三范式测试通过")
finally:
cursor.execute("DROP TABLE IF EXISTS users")
cursor.execute("DROP TABLE IF EXISTS cities")
conn.commit()
cursor.close()
conn.close()
预期结果: 表符合第三范式
3. 约束测试¶
测试用例3.1: 主键约束¶
测试目标: 验证主键约束
测试代码:
Python
def test_primary_key_constraint():
"""测试主键约束"""
conn = mysql.connector.connect(
host='localhost',
user='test_user',
password='test_password',
database='test_db'
)
cursor = conn.cursor()
try:
# 创建带主键的表
create_table_query = """
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
"""
cursor.execute(create_table_query)
# 插入数据
cursor.execute("INSERT INTO users (name, email) VALUES ('User 1', 'user1@example.com')")
conn.commit()
# 尝试插入重复主键
try:
cursor.execute("INSERT INTO users (id, name, email) VALUES (1, 'User 2', 'user2@example.com')")
conn.commit()
assert False, "主键约束未生效"
except Error as e:
assert e.errno == 1062, "错误代码不正确"
print("✓ 主键约束测试通过")
finally:
cursor.execute("DROP TABLE IF EXISTS users")
conn.commit()
cursor.close()
conn.close()
预期结果: 主键约束正确阻止重复
测试用例3.2: 外键约束¶
测试目标: 验证外键约束
测试代码:
Python
def test_foreign_key_constraint():
"""测试外键约束"""
conn = mysql.connector.connect(
host='localhost',
user='test_user',
password='test_password',
database='test_db'
)
cursor = conn.cursor()
try:
# 创建父表
create_users_query = """
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
"""
# 创建子表
create_orders_query = """
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
"""
cursor.execute(create_users_query)
cursor.execute(create_orders_query)
# 插入用户
cursor.execute("INSERT INTO users (name) VALUES ('User 1')")
conn.commit()
# 插入有效订单
cursor.execute("INSERT INTO orders (user_id, amount) VALUES (1, 100.00)")
conn.commit()
# 尝试插入无效外键
try:
cursor.execute("INSERT INTO orders (user_id, amount) VALUES (999, 200.00)")
conn.commit()
assert False, "外键约束未生效"
except Error as e:
assert e.errno == 1452, "错误代码不正确"
# 测试级联删除
cursor.execute("DELETE FROM users WHERE id = 1")
conn.commit()
cursor.execute("SELECT COUNT(*) FROM orders WHERE user_id = 1")
result = cursor.fetchone()
assert result[0] == 0, "级联删除未生效"
print("✓ 外键约束测试通过")
finally:
cursor.execute("DROP TABLE IF EXISTS orders")
cursor.execute("DROP TABLE IF EXISTS users")
conn.commit()
cursor.close()
conn.close()
预期结果: 外键约束正确工作
测试用例3.3: 唯一约束¶
测试目标: 验证唯一约束
测试代码:
Python
def test_unique_constraint():
"""测试唯一约束"""
conn = mysql.connector.connect(
host='localhost',
user='test_user',
password='test_password',
database='test_db'
)
cursor = conn.cursor()
try:
# 创建带唯一约束的表
create_table_query = """
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
username VARCHAR(50) UNIQUE,
name VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
"""
cursor.execute(create_table_query)
# 插入数据
cursor.execute("INSERT INTO users (email, username, name) VALUES ('user1@example.com', 'user1', 'User 1')")
conn.commit()
# 尝试插入重复email
try:
cursor.execute("INSERT INTO users (email, username, name) VALUES ('user1@example.com', 'user2', 'User 2')")
conn.commit()
assert False, "唯一约束未生效"
except Error as e:
assert e.errno == 1062, "错误代码不正确"
# 尝试插入重复username
try:
cursor.execute("INSERT INTO users (email, username, name) VALUES ('user2@example.com', 'user1', 'User 3')")
conn.commit()
assert False, "唯一约束未生效"
except Error as e:
assert e.errno == 1062, "错误代码不正确"
print("✓ 唯一约束测试通过")
finally:
cursor.execute("DROP TABLE IF EXISTS users")
conn.commit()
cursor.close()
conn.close()
预期结果: 唯一约束正确阻止重复
4. 索引设计测试¶
测试用例4.1: 索引创建¶
测试目标: 验证索引创建
测试代码:
Python
def test_index_creation():
"""测试索引创建"""
conn = mysql.connector.connect(
host='localhost',
user='test_user',
password='test_password',
database='test_db'
)
cursor = conn.cursor()
try:
# 创建表
create_table_query = """
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL,
status VARCHAR(20) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
"""
cursor.execute(create_table_query)
# 创建索引
# 普通索引
cursor.execute("CREATE INDEX idx_email ON users(email)")
# 复合索引
cursor.execute("CREATE INDEX idx_status_created ON users(status, created_at)")
# 唯一索引
cursor.execute("CREATE UNIQUE INDEX idx_name ON users(name)")
# 插入测试数据（使用参数化查询）
for i in range(100):
cursor.execute(
"INSERT INTO users (name, email, status) VALUES (%s, %s, %s)",
(f'User {i}', f'user{i}@example.com', 'active')
)
conn.commit()
# 验证索引存在
cursor.execute("SHOW INDEX FROM users")
indexes = cursor.fetchall()
index_names = [idx['Key_name'] for idx in indexes]
assert 'idx_email' in index_names # assert断言：条件为False时抛出AssertionError
assert 'idx_status_created' in index_names
assert 'idx_name' in index_names
# 验证索引使用
cursor.execute("EXPLAIN SELECT * FROM users WHERE email = 'user50@example.com'")
result = cursor.fetchone()
# 应该使用idx_email索引
assert 'idx_email' in result['Extra'] or result['key'] == 'idx_email'
print("✓ 索引创建测试通过")
finally:
cursor.execute("DROP TABLE IF EXISTS users")
conn.commit()
cursor.close()
conn.close()
预期结果: 索引成功创建并被使用
测试用例4.2: 索引性能¶
测试目标: 验证索引性能
测试代码:
Python
import time
def test_index_performance():
"""测试索引性能"""
conn = mysql.connector.connect(
host='localhost',
user='test_user',
password='test_password',
database='test_db'
)
cursor = conn.cursor()
try:
# 创建表
create_table_query = """
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL,
status VARCHAR(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
"""
cursor.execute(create_table_query)
# 插入大量数据（使用参数化查询）
for i in range(10000):
cursor.execute(
"INSERT INTO users (name, email, status) VALUES (%s, %s, %s)",
(f'User {i}', f'user{i}@example.com', 'active')
)
conn.commit()
# 测试无索引查询
start_time = time.time()
cursor.execute("SELECT * FROM users WHERE email = 'user5000@example.com'")
result = cursor.fetchone()
no_index_time = time.time() - start_time
# 创建索引
cursor.execute("CREATE INDEX idx_email ON users(email)")
conn.commit()
# 测试有索引查询
start_time = time.time()
cursor.execute("SELECT * FROM users WHERE email = 'user5000@example.com'")
result = cursor.fetchone()
with_index_time = time.time() - start_time
# 验证索引提升性能
assert with_index_time < no_index_time, "索引未提升性能"
print(f"无索引查询时间: {no_index_time:.4f}s")
print(f"有索引查询时间: {with_index_time:.4f}s")
print(f"性能提升: {(no_index_time - with_index_time) / no_index_time * 100:.2f}%")
print("✓ 索引性能测试通过")
finally:
cursor.execute("DROP TABLE IF EXISTS users")
conn.commit()
cursor.close()
conn.close()
预期结果: 索引显著提升查询性能
5. 关系设计测试¶
测试用例5.1: 一对一关系¶
测试目标: 验证一对一关系
测试代码:
Python
def test_one_to_one_relationship():
"""测试一对一关系"""
conn = mysql.connector.connect(
host='localhost',
user='test_user',
password='test_password',
database='test_db'
)
cursor = conn.cursor()
try:
# 用户表
create_users_query = """
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
"""
# 用户详情表
create_user_profiles_query = """
CREATE TABLE user_profiles (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL UNIQUE,
bio TEXT,
avatar_url VARCHAR(255),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
"""
cursor.execute(create_users_query)
cursor.execute(create_user_profiles_query)
# 插入数据
cursor.execute("INSERT INTO users (name, email) VALUES ('User 1', 'user1@example.com')")
conn.commit()
cursor.execute("INSERT INTO user_profiles (user_id, bio, avatar_url) VALUES (1, 'Bio text', 'http://example.com/avatar.png')")
conn.commit()
# 验证一对一关系
# 每个用户只能有一个profile
# 每个profile只能属于一个用户
cursor.execute("SELECT COUNT(*) FROM user_profiles WHERE user_id = 1")
result = cursor.fetchone()
assert result[0] == 1, "一对一关系未正确实现"
print("✓ 一对一关系测试通过")
finally:
cursor.execute("DROP TABLE IF EXISTS user_profiles")
cursor.execute("DROP TABLE IF EXISTS users")
conn.commit()
cursor.close()
conn.close()
预期结果: 一对一关系正确实现
测试用例5.2: 一对多关系¶
测试目标: 验证一对多关系
测试代码:
Python
def test_one_to_many_relationship():
"""测试一对多关系"""
conn = mysql.connector.connect(
host='localhost',
user='test_user',
password='test_password',
database='test_db'
)
cursor = conn.cursor()
try: # try/except捕获异常
# 用户表
create_users_query = """
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
"""
# 订单表
create_orders_query = """
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
"""
cursor.execute(create_users_query)
cursor.execute(create_orders_query)
# 插入数据
cursor.execute("INSERT INTO users (name, email) VALUES ('User 1', 'user1@example.com')")
conn.commit()
# 一个用户可以有多个订单（使用参数化查询）
for i in range(5):
cursor.execute(
"INSERT INTO orders (user_id, amount) VALUES (%s, %s)",
(1, 100.00 + i * 10)
)
conn.commit()
# 验证一对多关系
# 一个用户可以有多个订单
cursor.execute("SELECT COUNT(*) FROM orders WHERE user_id = 1")
result = cursor.fetchone()
assert result[0] == 5, "一对多关系未正确实现"
# 一个订单只能属于一个用户
cursor.execute("SELECT user_id FROM orders WHERE id = 1")
result = cursor.fetchone()
assert result[0] == 1, "外键关系未正确"
print("✓ 一对多关系测试通过")
finally:
cursor.execute("DROP TABLE IF EXISTS orders")
cursor.execute("DROP TABLE IF EXISTS users")
conn.commit()
cursor.close()
conn.close()
预期结果: 一对多关系正确实现
📊 测试执行¶
运行所有测试¶
Bash
# 运行所有测试
pytest tests/test_database_design.py -v
# 运行特定测试
pytest tests/test_database_design.py::test_table_creation -v
# 生成覆盖率报告
pytest tests/test_database_design.py --cov=database_design --cov-report=html
✅ 验证方法¶
1. 自动化验证¶
- 运行所有测试用例
- 检查断言是否通过
- 记录测试结果
2. 设计审查¶
- ER图审查
- 范式检查
- 约束验证
- 性能评估
3. 文档验证¶
- 设计文档完整性
- 数据字典准确性
- 关系图正确性
📝 测试报告¶
测试报告应包含：
- 测试概览
- 测试用例数量
- 通过/失败统计
-
设计评分
-
详细结果
- 每个测试用例的结果
- 设计问题列表
-
改进建议
-
问题分析
- 设计缺陷分析
- 性能瓶颈分析
- 优化建议
测试完成标准: 所有测试用例通过 推荐测试频率: 每次数据库设计更新 测试维护周期: 每周