227 lines
9.8 KiB
MySQL
227 lines
9.8 KiB
MySQL
|
create database traceabilityCode;
|
||
|
|
|
||
|
|
use traceabilityCode;
|
||
|
|
-- 用户表
|
||
|
|
CREATE TABLE users (
|
||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
user_account VARCHAR(255) NOT NULL COMMENT '用户账号',
|
||
|
|
username VARCHAR(50) UNIQUE NOT NULL COMMENT '用户名',
|
||
|
|
password VARCHAR(255) NOT NULL COMMENT '密码(加密)',
|
||
|
|
email VARCHAR(100) COMMENT '邮箱',
|
||
|
|
phone VARCHAR(20) COMMENT '手机号',
|
||
|
|
status TINYINT DEFAULT 1 COMMENT '状态:0-禁用,1-启用',
|
||
|
|
last_login_time DATETIME COMMENT '最后登录时间',
|
||
|
|
created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
|
isDelete TINYINT DEFAULT 0 comment '逻辑删除'
|
||
|
|
);
|
||
|
|
-- 角色表（优化后）
|
||
|
|
CREATE TABLE roles (
|
||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
name VARCHAR(50) NOT NULL COMMENT '角色名称',
|
||
|
|
code VARCHAR(50) UNIQUE NOT NULL COMMENT '角色编码',
|
||
|
|
description TEXT COMMENT '角色描述',
|
||
|
|
permissions JSON COMMENT '角色权限配置，如：["user:read", "user:write", "product:manage"]',
|
||
|
|
status TINYINT DEFAULT 1 COMMENT '状态:0-禁用,1-启用',
|
||
|
|
created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
-- 用户角色关联表
|
||
|
|
CREATE TABLE user_roles (
|
||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
user_id BIGINT NOT NULL,
|
||
|
|
role_id BIGINT NOT NULL,
|
||
|
|
created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
UNIQUE KEY uk_user_role (user_id, role_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- 产品表
|
||
|
|
CREATE TABLE products (
|
||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
name VARCHAR(100) NOT NULL COMMENT '产品名称',
|
||
|
|
category_id BIGINT COMMENT '分类ID',
|
||
|
|
sku VARCHAR(50) UNIQUE COMMENT '产品编码',
|
||
|
|
status TINYINT DEFAULT 1 COMMENT '状态',
|
||
|
|
created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
-- 属性定义表（定义每种产品可能有的属性）
|
||
|
|
CREATE TABLE product_attributes (
|
||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
category_id BIGINT COMMENT '适用的产品分类',
|
||
|
|
name VARCHAR(50) NOT NULL COMMENT '属性名称',
|
||
|
|
code VARCHAR(50) NOT NULL COMMENT '属性编码',
|
||
|
|
data_type ENUM('text', 'number', 'date', 'select') COMMENT '数据类型',
|
||
|
|
is_required TINYINT DEFAULT 0 COMMENT '是否必填',
|
||
|
|
options JSON COMMENT '选项值（用于下拉选择）',
|
||
|
|
section ENUM('raw_material', 'production', 'storage', 'inspection') COMMENT '所属板块'
|
||
|
|
);
|
||
|
|
-- 产品属性值表（存储具体的属性值）
|
||
|
|
CREATE TABLE product_attribute_values (
|
||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
product_id BIGINT NOT NULL COMMENT '产品ID',
|
||
|
|
attribute_id BIGINT NOT NULL COMMENT '属性ID',
|
||
|
|
value TEXT COMMENT '属性值',
|
||
|
|
created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
INDEX idx_product_attribute (product_id, attribute_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- 直播管理表
|
||
|
|
CREATE TABLE live_streams (
|
||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
stream_name VARCHAR(100) NOT NULL COMMENT '流名称',
|
||
|
|
stream_key VARCHAR(255) UNIQUE NOT NULL COMMENT '流密钥',
|
||
|
|
push_url VARCHAR(500) COMMENT '推流地址',
|
||
|
|
play_url VARCHAR(500) COMMENT '播放地址',
|
||
|
|
platform VARCHAR(50) COMMENT '平台',
|
||
|
|
account_id BIGINT COMMENT '关联账号ID',
|
||
|
|
status TINYINT DEFAULT 0 COMMENT '状态:0-离线,1-在线',
|
||
|
|
quality JSON COMMENT '画质配置',
|
||
|
|
created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
-- 直播记录表
|
||
|
|
CREATE TABLE live_records (
|
||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
stream_id BIGINT NOT NULL,
|
||
|
|
start_time DATETIME NOT NULL COMMENT '开始时间',
|
||
|
|
end_time DATETIME COMMENT '结束时间',
|
||
|
|
duration INT COMMENT '直播时长(秒)',
|
||
|
|
viewer_count INT DEFAULT 0 COMMENT '观看人数',
|
||
|
|
peak_viewer INT DEFAULT 0 COMMENT '峰值观看人数',
|
||
|
|
status ENUM('live', 'ended', 'error') DEFAULT 'live',
|
||
|
|
created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
-- 素材分类表
|
||
|
|
CREATE TABLE material_categories (
|
||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
name VARCHAR(100) NOT NULL COMMENT '分类名称',
|
||
|
|
type ENUM('image', 'video', 'document', 'other') NOT NULL COMMENT '素材类型',
|
||
|
|
parent_id BIGINT DEFAULT 0 COMMENT '父分类ID',
|
||
|
|
sort_order INT DEFAULT 0,
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
-- 视频表
|
||
|
|
CREATE TABLE videos (
|
||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
title VARCHAR(200) NOT NULL COMMENT '视频标题',
|
||
|
|
description TEXT COMMENT '视频描述',
|
||
|
|
file_path VARCHAR(500) NOT NULL COMMENT '视频文件路径',
|
||
|
|
file_url VARCHAR(500) COMMENT '播放URL',
|
||
|
|
thumbnail VARCHAR(500) COMMENT '缩略图',
|
||
|
|
duration INT COMMENT '时长(秒)',
|
||
|
|
file_size BIGINT COMMENT '文件大小',
|
||
|
|
resolution VARCHAR(20) COMMENT '分辨率',
|
||
|
|
format VARCHAR(20) COMMENT '视频格式',
|
||
|
|
bitrate INT COMMENT '码率',
|
||
|
|
fps INT COMMENT '帧率',
|
||
|
|
category_id BIGINT COMMENT '分类ID',
|
||
|
|
tags JSON COMMENT '标签',
|
||
|
|
view_count INT DEFAULT 0 COMMENT '播放次数',
|
||
|
|
status TINYINT DEFAULT 1 COMMENT '状态:0-处理中,1-正常,2-失败',
|
||
|
|
user_id BIGINT COMMENT '上传用户',
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
-- 扫码记录表
|
||
|
|
CREATE TABLE qr_codes (
|
||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
name VARCHAR(100) NOT NULL COMMENT '二维码名称',
|
||
|
|
code VARCHAR(100) UNIQUE NOT NULL COMMENT '二维码标识',
|
||
|
|
content TEXT NOT NULL COMMENT '二维码内容',
|
||
|
|
type ENUM('url', 'text', 'product', 'live') NOT NULL COMMENT '类型',
|
||
|
|
related_id BIGINT COMMENT '关联对象ID',
|
||
|
|
qr_image VARCHAR(500) COMMENT '二维码图片',
|
||
|
|
expire_time DATETIME COMMENT '过期时间',
|
||
|
|
scan_limit INT DEFAULT 0 COMMENT '扫描次数限制,0为无限制',
|
||
|
|
status TINYINT DEFAULT 1 COMMENT '状态:0-禁用,1-启用',
|
||
|
|
user_id BIGINT COMMENT '创建用户',
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
-- 扫码统计表
|
||
|
|
CREATE TABLE qr_scan_statistics (
|
||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
qr_code_id BIGINT NOT NULL,
|
||
|
|
date DATE NOT NULL COMMENT '统计日期',
|
||
|
|
scan_count INT DEFAULT 0 COMMENT '扫描次数',
|
||
|
|
unique_scan_count INT DEFAULT 0 COMMENT '独立扫描次数',
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
-- 操作日志表
|
||
|
|
CREATE TABLE operation_logs (
|
||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
user_id BIGINT COMMENT '操作用户ID',
|
||
|
|
username VARCHAR(50) COMMENT '用户名',
|
||
|
|
operation VARCHAR(100) NOT NULL COMMENT '操作类型',
|
||
|
|
method VARCHAR(10) COMMENT '请求方法',
|
||
|
|
path VARCHAR(200) COMMENT '请求路径',
|
||
|
|
ip_address VARCHAR(45) COMMENT 'IP地址',
|
||
|
|
user_agent TEXT COMMENT '用户代理',
|
||
|
|
request_params JSON COMMENT '请求参数',
|
||
|
|
response_data JSON COMMENT '响应数据',
|
||
|
|
execution_time INT COMMENT '执行时间(毫秒)',
|
||
|
|
status TINYINT DEFAULT 1 COMMENT '状态:0-失败,1-成功',
|
||
|
|
error_message TEXT COMMENT '错误信息',
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
INDEX idx_user_time (user_id, created_at),
|
||
|
|
INDEX idx_operation_time (operation, created_at)
|
||
|
|
);
|
||
|
|
-- 溯源码生成表
|
||
|
|
CREATE TABLE traceability_codes (
|
||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
code VARCHAR(100) UNIQUE NOT NULL COMMENT '溯源码',
|
||
|
|
product_id BIGINT NOT NULL COMMENT '关联产品',
|
||
|
|
batch_no VARCHAR(50) COMMENT '批次号',
|
||
|
|
production_date DATE COMMENT '生产日期',
|
||
|
|
expire_date DATE COMMENT '过期日期',
|
||
|
|
status ENUM('active', 'used', 'expired') DEFAULT 'active',
|
||
|
|
scan_count INT DEFAULT 0 COMMENT '扫描次数',
|
||
|
|
last_scan_time DATETIME COMMENT '最后扫码时间',
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
-- 二维码表（修改后）
|
||
|
|
CREATE TABLE qr_codes (
|
||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
name VARCHAR(100) NOT NULL COMMENT '二维码名称',
|
||
|
|
code VARCHAR(100) UNIQUE NOT NULL COMMENT '二维码标识',
|
||
|
|
content TEXT NOT NULL COMMENT '二维码内容',
|
||
|
|
type ENUM('url', 'text', 'product', 'live') NOT NULL COMMENT '类型',
|
||
|
|
related_id BIGINT COMMENT '关联对象ID',
|
||
|
|
traceability_code_id BIGINT COMMENT '关联溯源码ID',
|
||
|
|
qr_image VARCHAR(500) COMMENT '二维码图片',
|
||
|
|
expire_time DATETIME COMMENT '过期时间',
|
||
|
|
scan_limit INT DEFAULT 0 COMMENT '扫描次数限制,0为无限制',
|
||
|
|
scan_count INT DEFAULT 0 COMMENT '总扫码次数',
|
||
|
|
unique_scan_count INT DEFAULT 0 COMMENT '独立用户扫码次数',
|
||
|
|
last_scan_time DATETIME COMMENT '最后扫码时间',
|
||
|
|
status TINYINT DEFAULT 1 COMMENT '状态:0-禁用,1-启用',
|
||
|
|
user_id BIGINT COMMENT '创建用户',
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
-- 扫码记录表
|
||
|
|
CREATE TABLE scan_records (
|
||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
|
|
qr_code_id BIGINT NOT NULL COMMENT '二维码ID',
|
||
|
|
scan_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '扫码时间',
|
||
|
|
ip_address VARCHAR(45) COMMENT '扫码IP地址',
|
||
|
|
user_agent TEXT COMMENT '用户代理信息',
|
||
|
|
location JSON COMMENT 'GPS位置信息 {"lat": 39.9042, "lng": 116.4074, "address": "北京市朝阳区"}',
|
||
|
|
device_info JSON COMMENT '设备信息 {"os": "iOS", "browser": "Safari", "version": "14.0"}',
|
||
|
|
session_id VARCHAR(100) COMMENT '会话ID',
|
||
|
|
referrer VARCHAR(500) COMMENT '来源页面',
|
||
|
|
scan_source ENUM('wechat', 'alipay', 'browser', 'app') COMMENT '扫码来源',
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
INDEX idx_qr_time (qr_code_id, scan_time),
|
||
|
|
INDEX idx_session (session_id),
|
||
|
|
INDEX idx_ip_time (ip_address, scan_time)
|
||
|
|
);
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|