渐进式可扩展数据库模型（Progressive Extensible Database Model, pedm）
渐进式可扩展数据库模型（Progressive Extensible Database Model, pedm）
常用字段综合演示表
-- ----------------------------
-- Table structure for tbl_sample
-- [MySQL 5.7 GIS特性] https://www.jianshu.com/p/8833f68c3026
-- BLOB, TEXT, GEOMETRY or JSON column can't have a default value
-- ----------------------------
DROP TABLE IF EXISTS `tbl_sample`;
CREATE TABLE `tbl_sample` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`pid` INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '上级ID(下拉单选) [cdl:component=select]',
`fk_id` INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '外键ID(下拉单选可分页) [cdl:component=selectpage]',
`fk_ids` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '外键ID(下拉多选) [cdl:component=selectmultiple]',
`week` ENUM('unkonw','mon', 'tues', 'wed', 'thur', 'fri', 'sat', 'sun') NOT NULL DEFAULT 'unkonw' COMMENT '星期(下拉单选) [cdl:component=select]:unkonw=未知,mon=星期一,tues=星期二,wed=星期三,thur=星期四,fri=星期五,sta=星期六,sun=星期天',
`gender` ENUM('male', 'female') NOT NULL DEFAULT 'male' COMMENT '性别(单选) [cdl:component=radio]:male=男,female=女',
`hobby` SET('unkonw','music', 'reading', 'swimming') NOT NULL DEFAULT '' COMMENT '爱好(多选) [cdl:component=checkbox]:unkonw=未知,music=音乐,reading=读书,swimming=游泳',
`title` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '标题 [cdl:search==,like]',
`password` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '密码 [cdl:component=passwordbox]',
`color` VARCHAR(16) NOT NULL DEFAULT '' COMMENT '颜色 [cdl:component=color]',
`bankcardno` VARCHAR(19) NOT NULL DEFAULT '' COMMENT '银行卡号 [cdl:component=bankcardno] [cdl:search]',
`image` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '图片 [cdl:component=imagepicker]',
`images` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '图片组 [cdl:component=imagespicker]',
`attachfile` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '附件 [cdl:component=filepicker]',
`attachfiles` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '附件 [cdl:component=filespicker]',
`keywords` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '关键字 [cdl:component=textarea] [cdl:search=like]',
`description` TEXT NULL COMMENT '描述 [cdl:component=editor]',
`city` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '省市 [cdl:component=citypicker]',
`mapgis` geometry NULL COMMENT '地理位置经纬度 [cdl:component=mappicker]',
`json` VARCHAR(255) DEFAULT NULL COMMENT '配置 [cdl:component=jsontable][key=名称,value=值,remark=描述,url=超链接,image=图片]',
`price` FLOAT(5, 2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '价格 [cdl:component=numberbox] [cdl:search=between]',
`subtotal` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '小计金额',
`clickcount` INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '点击次数 [cdl:component=number] [cdl:search==,>,<,>=,<=]',
`rate` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '评分 [cdl:component=rate-picker]',
`startdate` DATE DEFAULT NULL COMMENT '开始日期 [cdl:component=date]',
`activitytime` DATETIME DEFAULT NULL COMMENT '活动时间 [cdl:component=datetime]',
`year` YEAR(4) DEFAULT NULL COMMENT '年 [cdl:component=year]',
`times` TIME DEFAULT NULL COMMENT '时间 [cdl:component=time]',
`created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
`updated_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '更新时间',
`sort` INT(10) NOT NULL DEFAULT 0 COMMENT '排序权重 [cdl:component=sortable]',
`switch` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '开关 [cdl:component=switch]',
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态(0=隐藏,1=显示)',
`stage` ENUM('0', '1', '2') NOT NULL DEFAULT '1' COMMENT '阶段:0=未开始,1=第一步,2=第二步',
`volume` TINYINT NOT NULL DEFAULT 0 COMMENT '音量 [cdl:component=range]',
`code` TEXT NULL COMMENT '代码 [cdl:component=code]',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='演示表';
渐进式可扩展数据库模型数据表
参考资料：
[数据库字段备注信息声明语法 CDL (Comment Declaration Language) - V2.0] https://www.cnblogs.com/sochishun/p/14132178.html
[数据库设计规范] https://www.cnblogs.com/sochishun/p/14109946.html
[利于复用资料的项目文件命名规范] https://www.cnblogs.com/sochishun/p/14510842.html
[电子商务(电销)平台中订单模块(Order)数据库设计明细] https://www.cnblogs.com/sochishun/p/7040628.html
[电子商务(电销)平台中用户模块(User)数据库设计明细] https://www.cnblogs.com/sochishun/p/7049771.html
[电子商务(电销)平台中内容模块(Content)数据库设计明细] https://www.cnblogs.com/sochishun/p/7060826.html
[电子商务(电销)平台中财务模块(Finance)数据库设计明细] https://www.cnblogs.com/sochishun/p/7060808.html
[电子商务(电销)平台中商品模块(Product)数据库设计明细] https://www.cnblogs.com/sochishun/p/7055490.html
[电子商务(电销)平台中系统设置模块(SysSetting)数据库设计明细] https://www.cnblogs.com/sochishun/p/7050239.html
数据表结构SQL脚本 - 基础模型：
文件：pedm.sql
/**
* 渐进式可扩展数据库模型-基础表-V2
* Progressive Extensible Database Model, pedm
* 版本：2.0.0
* CDL 2.0 语法说明：（建议 CDL 声明写在注释内容的最后面，每个 CDL 声明之间以一个空格隔开，这样排版才不会不影响注释内容的阅读）
* [cdl:json={"disable":true}] 禁用，当前不可用。
* [cdl:json={"component":"checkbox"}] 控件类型，详见附1。
* [cdl:json={"searchable":true,"operate":"like,="}] 是否允许搜索，支持操作符：=,>=,<=,like,null,not_null。默认根据字段数据类型使用贪婪模式。
* [cdl:json={"display":"index"}] 显示范围:list=列表,create=新建表单,edit=编辑表单,all=全部,none=无，默认 all。
* [cdl:json={"required":true}] 必填字段
* CDL 支持的控件类型：
* // H5 原生组件
* color, date, datetime-local, month, week, time, email, file, hidden, image, number, password, range, search, text, url
* // 业务常用组件
* radio, checkbox, select, selectpage, selectmulti, passwordbox, bankcardno, textarea, editor, jsontable, numberbox,
* image-uploader, images-uploader, file-uploader, files-uploader, city-picker, map-picker, rate-picker, sortable, switch, code
*/
-- DROP DATABASE IF EXISTS db_pedm_v2;
-- CREATE DATABASE IF NOT EXISTS db_pedm_v2 DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
-- use db_pedm_v2;
-- SET NAMES utf8mb4;
-- SET FOREIGN_KEY_CHECKS = 0;
-- =============================================================================
-- 权限类 auth
-- =============================================================================
-- ----------------------------
-- 管理员表
-- ----------------------------
DROP TABLE IF EXISTS `pedm_auth_admin`;
CREATE TABLE `pedm_auth_admin` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_name` VARCHAR(60) NOT NULL COMMENT '用户名',
`nick_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '昵称',
`contact_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '姓名',
`group_name` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '分组名称',
`password` VARCHAR(32) NOT NULL COMMENT '密码',
`salt` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '密码盐',
`avatar` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '头像',
`email` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '电子邮箱',
`mobile` VARCHAR(11) NOT NULL DEFAULT '' COMMENT '手机号码',
`gender` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '性别(0=女,1=男,2=保密)',
`login_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '登录次数',
`login_time` DATETIME COMMENT '登录时间',
`login_ip` VARCHAR(46) NOT NULL DEFAULT '' COMMENT '登录IP',
`register_ip` VARCHAR(46) NOT NULL COMMENT '注册IP',
`created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
`updated_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '更新时间',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态(0=无效,1=有效)',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_user_name` (`user_name`),
KEY `idx_email` (`email`),
KEY `idx_mobile` (`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管理员表';
-- ----------------------------
-- 权限分组表
-- ----------------------------
DROP TABLE IF EXISTS `pedm_auth_group`;
CREATE TABLE `pedm_auth_group` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`group_name` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '分组名称',
`group_title` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '分组标题',
`rules` VARCHAR(2048) NOT NULL DEFAULT '' COMMENT '规则ID',
`app_flag` VARCHAR(16) NOT NULL DEFAULT 'admin' COMMENT '模块标志',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态(0=隐藏,1=显示)',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_group_name` (`group_name`,`app_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='权限分组表';
-- ----------------------------
-- 权限规则表
-- ----------------------------
DROP TABLE IF EXISTS `pedm_auth_rule`;
CREATE TABLE `pedm_auth_rule` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`pid` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '父级ID',
`rule_title` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '规则标题',
`rule_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '规则名称',
`url` VARCHAR(150) NOT NULL DEFAULT '' COMMENT '规则路径',
`icon` VARCHAR(150) NOT NULL DEFAULT '' COMMENT '图标',
`is_menu` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否菜单(0=否,1=是)',
`extra` VARCHAR(150) NOT NULL DEFAULT '' COMMENT '扩展数据',
`sort` SMALLINT UNSIGNED NOT NULL DEFAULT 999 COMMENT '排序权重',
`app_flag` VARCHAR(16) NOT NULL DEFAULT 'admin' COMMENT '模块标志',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态(0=隐藏,1=显示)',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_rule_name` (`rule_name`,`app_flag`),
KEY `idx_pid` (`pid`),
KEY `idx_sort` (`sort`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='权限规则表';
-- ----------------------------
-- 管理员日志表
-- ----------------------------
DROP TABLE IF EXISTS `pedm_auth_admin_log`;
CREATE TABLE `pedm_auth_admin_log` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '用户名',
`url` VARCHAR(150) NOT NULL DEFAULT '' COMMENT '操作页面',
`title` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '日志标题',
`content` VARCHAR(255) NOT NULL COMMENT '操作内容',
`ip` VARCHAR(46) NOT NULL DEFAULT '' COMMENT 'IP',
`user_agent` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '浏览器User-Agent',
`app_flag` VARCHAR(16) NOT NULL DEFAULT 'admin' COMMENT '模块标志',
`created_at` INT UNSIGNED NOT NULL COMMENT '操作时间',
PRIMARY KEY (`id`),
KEY `idx_user_name` (`user_name`),
KEY `idx_app_flag`(`app_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管理员日志表';
-- =============================================================================
-- 系统类 sys
-- =============================================================================
-- ----------------------------
-- 系统配置表
-- ----------------------------
DROP TABLE IF EXISTS `pedm_sys_config`;
CREATE TABLE `pedm_sys_config` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`group_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '分组名称', -- 这是行内注释
`conf_name` VARCHAR(60) NOT NULL COMMENT '变量名',
`conf_title` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '变量标题 -- 这是注释',
`conf_tip` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '变量描述',
`conf_type` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '类型(string,TEXT,int,bool,array,datetime,date,file)',
`conf_value` TEXT NOT NULL COMMENT '变量值',
`conf_content` TEXT NOT NULL COMMENT '变量字典数据',
`conf_rule` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '验证规则',
`conf_extend` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '扩展属性',
`sort` SMALLINT UNSIGNED NOT NULL DEFAULT 999 COMMENT '排序权重',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_conf_name` (`conf_name`),
KEY `idx_sort` (`sort`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统配置表';
-- ----------------------------
-- 附件表
-- ----------------------------
DROP TABLE IF EXISTS `pedm_sys_attachment`;
CREATE TABLE `pedm_sys_attachment` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
`file_name` varchar(150) NOT NULL DEFAULT '' COMMENT '文件名',
`url` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '网址路径',
`path` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '物理路径',
`storage` VARCHAR(100) NOT NULL DEFAULT 'local' COMMENT '存储位置',
`image_width` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '宽度',
`image_height` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '高度',
`image_type` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '图片类型',
`image_frames` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '图片帧数',
`file_size` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '文件大小',
`mime_type` VARCHAR(30) NOT NULL DEFAULT '' COMMENT 'mime类型',
`ext_param` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '透传数据',
`created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
`updated_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='附件表';
-- ----------------------------
-- 定时任务表
-- ----------------------------
DROP TABLE IF EXISTS `pedm_sys_crontab`;
CREATE TABLE IF NOT EXISTS `pedm_sys_crontab` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`crontab_type` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '任务类型(url=请求URL,sql=执行SQL,shell=执行Shell)',
`crontab_title` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '任务标题',
`crontab_content` TEXT COMMENT '任务内容(url,sql,shell)',
`crontab_schedule` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '执行周期（Crontab格式:min* hour* day* month* weekday*）',
`max_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '最大执行次数(0为不限)',
`exec_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '已经执行的次数',
`created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
`updated_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '更新时间',
`exec_time` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '最后执行时间',
`begin_time` DATETIME NOT NULL COMMENT '开始时间',
`end_time` DATETIME NOT NULL COMMENT '结束时间',
`sort` SMALLINT UNSIGNED NOT NULL DEFAULT 999 COMMENT '排序权重',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态(0=禁用,1=启用,2=过期,3=完成)',
PRIMARY KEY (`id`),
KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='定时任务表';
-- ----------------------------
-- 定时任务日志表
-- ----------------------------
DROP TABLE IF EXISTS `pedm_sys_crontab_log`;
CREATE TABLE IF NOT EXISTS `pedm_sys_crontab_log` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`crontab_id` INT UNSIGNED NOT NULL COMMENT '任务ID',
`exec_time` DATETIME COMMENT '执行时间',
`end_time` DATETIME COMMENT '结束时间',
`result` TEXT COMMENT '执行结果',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态(0=失败,1=成功)',
PRIMARY KEY (`id`),
KEY `idx_crontab_id` (`crontab_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='定时任务日志表';
-- ----------------------------
-- 邮箱验证码表
-- ----------------------------
DROP TABLE IF EXISTS `pedm_sys_email_captcha`;
CREATE TABLE `pedm_sys_email_captcha` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`event` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '事件',
`email` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '邮箱',
`code` VARCHAR(6) NOT NULL DEFAULT '' COMMENT '验证码',
`count` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '验证次数',
`ip` VARCHAR(46) NOT NULL DEFAULT '' COMMENT 'IP',
`created_at` INT UNSIGNED NULL DEFAULT 0 COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `idx_email`(`email`,`event`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='邮箱验证码表';
-- ----------------------------
-- 短信验证码表
-- ----------------------------
DROP TABLE IF EXISTS `pedm_sys_sms_captcha`;
CREATE TABLE `pedm_sys_sms_captcha` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`event` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '事件',
`mobile` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '手机号',
`code` VARCHAR(6) NOT NULL DEFAULT '' COMMENT '验证码',
`count` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '验证次数',
`ip` VARCHAR(46) NOT NULL DEFAULT '' COMMENT 'IP',
`created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `idx_mobile`(`mobile`,`event`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='短信验证码表';
-- ----------------------------
-- 用户反馈表
-- ----------------------------
DROP TABLE IF EXISTS `pedm_sys_feedback`;
CREATE TABLE IF NOT EXISTS `pedm_sys_feedback` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
`user_name` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '用户名',
`contact_name` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '姓名',
`mobile` VARCHAR(11) NOT NULL DEFAULT '' COMMENT '手机号',
`email` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '电子邮箱',
`category` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '反馈类别',
`content` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '反馈内容',
`images` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '截图',
`admin_name` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '管理员用户名',
`reply_content` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '回复内容',
`is_read` TINYINT NOT NULL DEFAULT 0 COMMENT '是否已阅(0=否,1=是)',
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态(0=隐藏,1=正常)',
`created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
`updated_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '回复时间',
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户反馈表';
数据表结构SQL脚本 - 业务扩展模型：
文件：biz.sql
/**
* 渐进式可扩展数据库模型-业务扩展模型-V2
* Progressive Extensible Database Model, pedm
* 版本：2.0.0
* CDL 2.0 语法说明：（建议 CDL 声明写在注释内容的最后面，每个 CDL 声明之间以一个空格隔开，这样排版才不会不影响注释内容的阅读）
* [cdl:json={"disable":true}] 禁用，当前不可用。
* [cdl:json={"component":"checkbox"}] 控件类型，详见附1。
* [cdl:json={"searchable":true,"operate":"like,="}] 是否允许搜索，支持操作符：=,>=,<=,like,null,not_null。默认根据字段数据类型使用贪婪模式。
* [cdl:json={"display":"index"}] 显示范围:list=列表,create=新建表单,edit=编辑表单,all=全部,none=无，默认 all。
* [cdl:json={"required":true}] 必填字段
* CDL 支持的控件类型：
* // H5 原生组件
* color, date, datetime-local, month, week, time, email, file, hidden, image, number, password, range, search, text, url
* // 业务常用组件
* radio, checkbox, select, selectpage, selectmulti, passwordbox, bankcardno, textarea, editor, jsontable, numberbox,
* image-uploader, images-uploader, file-uploader, files-uploader, city-picker, map-picker, rate-picker, sortable, switch, code
*/
-- =============================================================================
-- 文章内容类 art
-- =============================================================================
-- ----------------------------
-- 文章表
-- ----------------------------
DROP TABLE IF EXISTS `art_article`;
CREATE TABLE `art_article` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`category_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '分类ID [cdl:component=select(table=ps_article_category,textfield=category_name,idfield=id)]',
`category_name` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '分类名称',
`title` VARCHAR(255) NOT NULL COMMENT '文章标题',
`author` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '文章作者',
`source_link` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '原文链接 [cdl:component=url]',
`icon` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '文章图标 [cdl:component=image]',
`summary` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '文章摘要 [cdl:component=textarea]',
`share_title` VARCHAR(180) NOT NULL DEFAULT '' COMMENT '文章分享标题 [cdl:disable]',
`share_intro` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '文章分享简介 [cdl:component=textarea] [cdl:disable]',
`stat_visit_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '浏览次数 [cdl:search=search(like,=)] [cdl:display=index]',
`is_hot` TINYINT NOT NULL DEFAULT 0 COMMENT '是否热门(0=否,1=是)',
`is_recommend` TINYINT NOT NULL DEFAULT 0 COMMENT '是否推荐(0=否,1=是)',
`sort` SMALLINT UNSIGNED NOT NULL DEFAULT 999 COMMENT '排序权重',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态(0=隐藏,1=显示)',
`created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
`content` TEXT NOT NULL COMMENT '文章内容 [cdl:component=editor]',
PRIMARY KEY (`id`),
KEY `idx_title` (`title`),
KEY `idx_category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章表';
-- ----------------------------
-- 文章分类表
-- ----------------------------
DROP TABLE IF EXISTS `art_article_category`;
CREATE TABLE `art_article_category` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`pid` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '父级ID [cdl:component=select(table=ps_article_category,textfield=category_name,idfield=id)]',
`category_name` VARCHAR(30) NOT NULL COMMENT '标题',
`category_code` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '代码 [cdl:disable]',
`intro` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '简介 [cdl:component=textarea]',
`seo_keywords` VARCHAR(150) NOT NULL DEFAULT '' COMMENT 'SEO 关键词 [cdl:disable]',
`seo_description` VARCHAR(500) NOT NULL DEFAULT '' COMMENT 'SEO 描述 [cdl:disable]',
`icon` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '图标',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态(0=隐藏,1=显示)',
`sort` SMALLINT UNSIGNED NOT NULL DEFAULT 999 COMMENT '排序权重',
`created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `idx_pid` (`pid`),
KEY `idx_sort`(`sort`),
KEY `idx_status`(`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章分类表';
-- =============================================================================
-- 电商类 eb
-- =============================================================================
-- ----------------------------
-- 购物车表
-- ----------------------------
DROP TABLE IF EXISTS `eb_shopping_cart`;
CREATE TABLE `eb_shopping_cart` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
`product_id` INT UNSIGNED NOT NULL COMMENT '商品ID',
`product_spec_id` INT NOT NULL DEFAULT 0 COMMENT '商品规格ID',
`quantity` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品数量',
`extra` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '扩展数据',
`flag` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '标识(buynow=立即购买,mobile=手机充值)',
`created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态(0=失效,1=有效)',
PRIMARY KEY (`id`),
KEY `idx_user_id`(`user_id`),
KEY `idx_status`(`user_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='购物车表';
-- ----------------------------
-- 用户商品收藏表
-- ----------------------------
DROP TABLE IF EXISTS `eb_product_favorite`;
CREATE TABLE `eb_product_favorite` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
`product_id` INT UNSIGNED NOT NULL COMMENT '商品ID',
`tag` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '标签（逗号隔开）',
`created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `idx_user_id`(`user_id`),
KEY `idx_product_id`(`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户商品收藏表';
-- ----------------------------
-- 商品表
-- ----------------------------
DROP TABLE IF EXISTS `eb_product_goods`;
CREATE TABLE `eb_product_goods` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`category_id` INT UNSIGNED NOT NULL COMMENT '分类ID [cdl:component=select(table=ps_product_category,textfield=category_name,idfield=id)]',
`product_name` VARCHAR(150) NOT NULL COMMENT '商品名称',
`model` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '型号编码 [cdl:disable]',
`sku` varchar(64) NOT NULL DEFAULT '' COMMENT '商品唯一标识编码 [cdl:disable]',
`upc` varchar(12) NOT NULL DEFAULT '' COMMENT 'UPC条形码 [cdl:disable]',
`ean` varchar(14) NOT NULL DEFAULT '' COMMENT 'EAN条形码 [cdl:disable]',
`isbn` VARCHAR(17) NOT NULL DEFAULT '' COMMENT '国际标准书号 [cdl:disable]',
`tag` VARCHAR(150) NOT NULL DEFAULT '' COMMENT '商品标签（逗号隔开） [cdl:disable]',
`icon` VARCHAR(255) NOT NULL COMMENT '图标',
`carousel_images` TEXT NOT NULL COMMENT '轮播图 [cdl:display=create,edit]',
`video_url` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '短视频 [cdl:disable]',
`intro` VARCHAR(255) NOT NULL COMMENT '商品简介 [cdl:component=textarea] [cdl:display=create,edit]',
`seo_keywords` VARCHAR(96) NOT NULL DEFAULT '' COMMENT 'SEO 关键词 [cdl:disable]',
`price` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '商品价格',
`market_price` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '市场价',
`original_price` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '原价 [cdl:disable]',
`cost_price` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '成本价 [cdl:disable]',
`vip_price` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '贵宾价格 [cdl:disable]',
`promotion_price` DECIMAL(8,2) NOT NULL DEFAULT 0.00 COMMENT '促销价格',
`promotion_type` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '促销类型(0=无促销,1=抢购,2=限时折扣,3=秒杀)',
`give_integral` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '赠送积分',
`postage_amount` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '邮费(0=免运费)',
`is_issue_invoice` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否开具增值税发票(0=否,1=是) [cdl:disable]',
`unit_name` VARCHAR(15) NOT NULL DEFAULT '' COMMENT '库存单位 [cdl:disable]',
`stat_stock_quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '总库存',
`stat_sales_quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '销量 [cdl:display=index]',
`stat_visited_quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '访问量 [cdl:display=index]',
`stat_favorite_quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '收藏量 [cdl:display=index]',
`stat_star_quantity` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '好评星级 [cdl:disable]',
`stat_evaluation_quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '评价数 [cdl:disable]',
`is_limited_quantity` TINYINT NOT NULL DEFAULT 0 COMMENT '是否限量销售(0=否,1=是)',
`is_limited_time` TINYINT NOT NULL DEFAULT 0 COMMENT '是否限时销售(0=否,1=是)',
`limit_quantity` INT NOT NULL DEFAULT 0 COMMENT '销售限量',
`begin_time` DATETIME COMMENT '开始时间 [cdl:disable]',
`end_time` DATETIME COMMENT '结束时间 [cdl:disable]',
`is_virtual` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否为虚拟商品(0=否,1=是) [cdl:disable]',
`is_deduction_inventory` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否扣减库存(0=否,1=是) [cdl:disable]',
`is_appoint` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否是预约商品(0=否,1=是) [cdl:disable]',
`is_presell` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否是预售商品(0=否,1=是) [cdl:disable]',
`is_have_gift` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否拥有赠品(0=否,1=是) [cdl:disable]',
`is_hot` TINYINT NOT NULL DEFAULT 0 COMMENT '是否热销(0=否,1=是)',
`is_recommend` TINYINT NOT NULL DEFAULT 0 COMMENT '是否推荐(0=否,1=是)',
`is_new` TINYINT NOT NULL DEFAULT 0 COMMENT '是否新品(0=否,1=是)',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态(0=未上架,1=上架)',
`created_at` INT UNSIGNED DEFAULT NULL COMMENT '创建时间',
`updated_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '更新时间',
`sort` SMALLINT UNSIGNED NOT NULL DEFAULT 999 COMMENT '排序权重 [cdl:disable]',
`soure_link` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '外部链接（淘宝、天猫、1688商品保存标识，避免商品重复入库） [cdl:component=url]',
`brand_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '品牌ID [cdl:disable]',
`mch_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商户ID(0=管理员,n=商户) [cdl:disable]',
`content` TEXT NOT NULL COMMENT '商品描述（PC端） [cdl:component=editor]',
`content_m` TEXT NOT NULL COMMENT '商品描述（移动端） [cdl:component=editor]',
PRIMARY KEY (`id`),
KEY `idx_category_id` (`category_id`),
KEY `idx_product_name` (`product_name`),
KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';
-- ----------------------------
-- 商品分类表
-- ----------------------------
DROP TABLE IF EXISTS `eb_product_category`;
CREATE TABLE `eb_product_category` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`pid` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '父级ID [cdl:component=select(table=ps_product_category,textfield=category_name,idfield=id)]',
`category_name` VARCHAR(60) NOT NULL COMMENT '分类名称',
`subtitle` VARCHAR(80) NOT NULL DEFAULT '' COMMENT '副标题 [cdl:disable]',
`is_allow_virtual` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否允许发布虚拟商品(0=否,1=是) [cdl:disable]',
`seo_keywords` VARCHAR(150) NOT NULL DEFAULT '' COMMENT 'SEO 关键词 [cdl:disable]',
`seo_description` VARCHAR(500) NOT NULL DEFAULT '' COMMENT 'SEO 描述 [cdl:disable]',
`icon` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '图标',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态(0=隐藏,1=显示)',
`sort` SMALLINT UNSIGNED NOT NULL DEFAULT 999 COMMENT '排序权重',
`created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `idx_pid`(`pid`),
KEY `idx_status` (`status`),
KEY `idx_sort`(`sort`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品分类表';
-- ----------------------------
-- 商品评论表
-- ----------------------------
DROP TABLE IF EXISTS `eb_product_comments`;
CREATE TABLE `eb_product_comments` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
`product_id` INT UNSIGNED NOT NULL COMMENT '商品ID',
`order_id` INT UNSIGNED NOT NULL COMMENT '订单ID',
`rating` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '评价级别',
`product_score` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品分数',
`service_score` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '服务分数',
`content` VARCHAR(200) NOT NULL COMMENT '评论内容',
`images` TEXT COMMENT '图片',
`created_at` INT UNSIGNED NOT NULL COMMENT '评论时间',
`reply_time` DATETIME COMMENT '管理员回复时间',
`reply_content` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '管理员回复内容',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态(0=隐藏,1=显示)',
`is_anonymous` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否匿名(0=否,1=是)',
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_product_id` (`product_id`),
KEY `idx_status`(`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品评论表';
-- ----------------------------
-- 商品分类规格表
-- ----------------------------
DROP TABLE IF EXISTS `eb_product_specification`;
CREATE TABLE `eb_product_specification` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`spec_name` VARCHAR(60) NOT NULL COMMENT '规格名称',
`spec_code` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '规格代码',
`category_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品分类ID',
`category_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '商品分类名称',
`sort` SMALLINT UNSIGNED NOT NULL DEFAULT 999 COMMENT '排序权重',
PRIMARY KEY (`id`),
KEY `idx_category_id`(`category_id`),
KEY `idx_sort`(`sort`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品分类规格表';
-- ----------------------------
-- 商品分类规格值表
-- ----------------------------
DROP TABLE IF EXISTS `eb_product_specification_value`;
CREATE TABLE `eb_product_specification_value` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`spec_id` INT UNSIGNED NOT NULL COMMENT '所属规格ID',
`category_id` INT UNSIGNED NOT NULL COMMENT '分类ID',
`spec_value_name` VARCHAR(60) NOT NULL COMMENT '规格值名称',
`spec_value_color` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '规格颜色',
`sort` SMALLINT UNSIGNED NOT NULL DEFAULT 999 COMMENT '排序权重',
PRIMARY KEY (`id`),
KEY `idx_spec_id`(`spec_id`),
KEY `idx_sort`(`sort`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品分类规格值表';
-- ----------------------------
-- 商品规格信息表
-- ----------------------------
DROP TABLE IF EXISTS `eb_product_specification_info`;
CREATE TABLE `eb_product_specification_info` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`product_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品ID',
`spec_value_id` INT UNSIGNED NOT NULL COMMENT '所属规格ID',
`price` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '销售价格',
`cost` DECIMAL(8,2) UNSIGNED NOT NULL COMMENT '成本价',
`stock` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '库存',
`sales` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '销量',
`image` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '图片',
`weight` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '重量（kg） ',
`barcode` VARCHAR(80) NOT NULL DEFAULT '' COMMENT '条形码',
`created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `idx_product_id` (`product_id`),
KEY `idx_spec_value_id` (`spec_value_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品规格信息表';
-- ----------------------------
-- 优惠券表
-- ----------------------------
DROP TABLE IF EXISTS `eb_coupon`;
CREATE TABLE `eb_coupon` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`coupon_name` VARCHAR(60) NOT NULL COMMENT '优惠券名称',
`coupon_code` VARCHAR(32) NOT NULL COMMENT '优惠券代码',
`integral` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '兑换消耗积分值',
`coupon_value` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '优惠券面值',
`use_min_amount` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '最低消费多少金额可使用用优惠券',
`expiry_time` DATETIME COMMENT '优惠券有效期',
`begin_time` DATETIME COMMENT '开始领取时间',
`end_time` DATETIME COMMENT '结束领取时间',
`total_quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '优惠券总数量',
`quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '优惠券可领取数量',
`is_unlimited` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否无限张数(0=否,1=是)',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态(0=禁用,1=启用,2=过期,3=领完)',
`sort` SMALLINT UNSIGNED NOT NULL DEFAULT 999 COMMENT '排序权重',
`created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_coupon_code`(`coupon_code`),
KEY `idx_time_range`(`begin_time`, `end_time`),
KEY `idx_quantity`(`quantity`),
KEY `idx_status`(`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='优惠券表';
-- ----------------------------
-- 优惠券领取表
-- ----------------------------
DROP TABLE IF EXISTS `eb_coupon_receive`;
CREATE TABLE `eb_coupon_receive` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
`coupon_id` INT UNSIGNED NOT NULL COMMENT '优惠券ID',
`coupon_title` VARCHAR(60) NOT NULL COMMENT '优惠券名称',
`coupon_value` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '优惠券面值',
`use_min_amount` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '最低消费多少金额可用优惠券',
`created_at` INT UNSIGNED NOT NULL COMMENT '领取时间',
`expiry_time` DATETIME COMMENT '优惠券有效期',
`use_time` DATETIME COMMENT '使用时间',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态(0=未使用,1=已使用,2=已过期)',
PRIMARY KEY (`id`),
KEY `idx_coupon_id`(`coupon_id`),
KEY `idx_user_id`(`user_id`),
KEY `idx_expiry_time`(`expiry_time`),
KEY `idx_status`(`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='优惠券领取表';
-- ----------------------------
-- 快递公司表
-- ----------------------------
DROP TABLE IF EXISTS `eb_express`;
CREATE TABLE `eb_express` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`express_name` VARCHAR(30) NOT NULL COMMENT '快递公司名称',
`express_code` VARCHAR(50) NOT NULL COMMENT '快递公司代码',
`homepage` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '快递公司网址 [cdl:required]',
`icon` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '图标',
`sort` SMALLINT UNSIGNED NOT NULL DEFAULT 999 COMMENT '排序权重',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态(0=隐藏,1=显示)',
`api_status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '接口API状态(0=不可用,1=可用)',
`api_url` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '接口API地址',
`api_key` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '接口API参数签名密钥',
`api_additional_param` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '接口API附加参数（URL请求参数格式，例如：id=1,2,3&name=a,b,c）',
`api_method` CHAR(4) NOT NULL DEFAULT 'POST' COMMENT '接口API的请求方法(GET=GET请求,POST=POST请求)',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_code` (`express_code`),
KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='快递公司表';
-- ----------------------------
-- 订单表
-- ----------------------------
DROP TABLE IF EXISTS `eb_order`;
CREATE TABLE `eb_order` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
`order_no` VARCHAR(32) NOT NULL COMMENT '订单编号',
`order_from` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '订单来源(1=PC,2=手机)',
`total_quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '订单商品总数',
`total_amount` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '订单总价',
`postage_amount` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '邮费',
`pay_amount` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '实际支付金额',
`pay_postage_amount` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '支付邮费',
`deduction_amount` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '抵扣金额',
`give_integral` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '订单赠送积分',
`gain_integral` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '消费赚取积分',
`use_integral` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '使用积分',
`coupon_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '优惠券ID',
`coupon_amount` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '优惠券金额',
`created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
`pay_time` DATETIME COMMENT '支付时间',
`payment_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '支付方式ID',
`trade_no` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '第三方交易单号',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '订单状态(0=待确认,1=待发货,2=待收货,3=已收货,4=已完成,5=已退款,6=已取消,7=已关闭)',
`evaluation_status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '评价状态(0=未评价,1=已评价,2=已过期未评价)',
`pay_status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '支付状态(0=未支付,1=已支付,2=已退款,3=部分退款)',
`refund_status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '退款状态(0=未退款,1=申请中,2=已退款)',
`refund_reason_image` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '退款图片',
`refund_reason_explain` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '退款用户说明',
`refund_reason_time` DATETIME COMMENT '退款时间',
`refund_reject_explain` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '不退款的理由',
`refund_amount` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '退款金额',
`delivery_name` VARCHAR(60) NOT NULL COMMENT '收货人姓名',
`delivery_phone` VARCHAR(18) NOT NULL COMMENT '收货人电话',
`delivery_address` VARCHAR(100) NOT NULL COMMENT '收货人详细地址',
`express_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '快递公司ID',
`express_no` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '快递单号',
`buyer_remark` VARCHAR(512) NOT NULL COMMENT '买家备注',
`admin_remark` VARCHAR(512) NOT NULL DEFAULT '' COMMENT '管理员备注',
`confirm_remark` VARCHAR(512) NOT NULL DEFAULT '' COMMENT '订单确认备注',
`confirm_time` DATETIME COMMENT '订单确认时间',
`delivery_time` DATETIME COMMENT '发货时间',
`cancel_time` DATETIME COMMENT '取消时间',
`receiving_time` DATETIME COMMENT '收货时间',
`close_time` DATETIME COMMENT '关闭时间',
`mch_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商户ID',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_order_no`(`order_no`),
KEY `idx_user_id`(`user_id`),
KEY `idx_status`(`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';
-- ----------------------------
-- 订单商品表
-- ----------------------------
DROP TABLE IF EXISTS `eb_order_detail`;
CREATE TABLE `eb_order_detail` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`order_id` INT UNSIGNED NOT NULL COMMENT '订单ID',
`user_id` INT UNSIGNED NOT NULL COMMENT '买家ID',
`product_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品ID',
`product_name` VARCHAR(60) NOT NULL COMMENT '商品名称',
`product_price` DECIMAL(10,2) NOT NULL COMMENT '商品价格',
`product_quantity` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品数量',
`product_image` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '商品图片',
`tax` DECIMAL(8,2) NOT NULL DEFAULT 0.00 COMMENT '税额',
`subtotal` DECIMAL(10,2) UNSIGNED NOT NULL COMMENT '小计金额',
`product_type` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品类型(0=默认,1=抢购商品,2=限时折扣商品,3=组合套装,4=赠品,5=拼团,6=用户等级折扣)',
`promotions_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '促销活动ID（抢购ID/限时折扣ID/优惠套装ID）与 product_type 搭配使用',
PRIMARY KEY (`id`),
KEY `idx_order_id`(`order_id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_product_id`(`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单商品表';
-- ----------------------------
-- 订单退货表
-- ----------------------------
DROP TABLE IF EXISTS `eb_order_return`;
CREATE TABLE `eb_order_return` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`order_id` INT UNSIGNED NOT NULL COMMENT '订单ID',
`order_no` VARCHAR(32) NOT NULL COMMENT '订单编号',
`refund_no` VARCHAR(32) NOT NULL COMMENT '退货编号',
`user_id` INT UNSIGNED NOT NULL COMMENT '买家ID',
`user_name` VARCHAR(60) NOT NULL COMMENT '买家用户名',
`order_detail_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '订单详情id(0=全部退款)',
`product_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品id(0=全部退款)',
`product_name` VARCHAR(60) NOT NULL COMMENT '商品名称',
`product_quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品退货数量',
`refund_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '退款金额',
`refund_type` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '申请类型(1=仅退款,2=退货退款)',
`return_type` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '退货类型(1=不用退货,2=需要退货)',
`audit_status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '审核状态(1=待审核,2=同意,3=不同意)',
`delivery_status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '物流状态(1=待发货,2=待收货,3=未收到,4=已收货)',
`images` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '退款退货凭证图片',
`buyer_message` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '退款退货申请原因',
`admin_message` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '管理员备注',
`express_id` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '物流公司编号',
`express_no` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '物流单号',
`delivery_time` DATETIME COMMENT '发货时间',
`delay_time` DATETIME COMMENT '收货延迟时间',
`receive_time` DATETIME COMMENT '收货时间',
`receive_message` VARCHAR(300) NOT NULL DEFAULT '' COMMENT '收货备注',
`created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
`admin_operate_time` DATETIME COMMENT '管理员处理时间',
PRIMARY KEY (`id`),
KEY `idx_order_id` (`order_id`),
KEY `idx_product_id` (`product_id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_audit_status` (`audit_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单退货表';
-- ----------------------------
-- 订单操作日志表
-- ----------------------------
DROP TABLE IF EXISTS `eb_order_log`;
CREATE TABLE `eb_order_log` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`admin_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '操作者用户名',
`order_id` INT UNSIGNED NOT NULL COMMENT '订单ID',
`operate` VARCHAR(32) NOT NULL COMMENT '操作名称',
`message` VARCHAR(255) NOT NULL COMMENT '操作备注',
`created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `idx_order_id`(`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单操作日志表';
-- ----------------------------
-- 发票信息表
-- ----------------------------
DROP TABLE IF EXISTS `eb_invoice`;
CREATE TABLE `eb_invoice` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
`order_id` INT UNSIGNED NOT NULL COMMENT '订单ID',
`invoice_type` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '发票类型(1=普通发票,2=增值税发票)',
`invoice_title` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '发票抬头(普通发票)',
`invoice_code` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '纳税人识别号(普通发票)',
`invoice_content` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '发票内容(普通发票)',
`invoice_company` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '单位名称',
`invoice_company_code` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '纳税人识别号',
`invoice_reg_address` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '注册地址',
`invoice_reg_phone` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '注册电话',
`invoice_reg_bank_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '开户银行',
`invoice_reg_bank_account` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '银行帐户',
`invoice_delivery_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '收票人姓名',
`invoice_delivery_phone` VARCHAR(15) NOT NULL DEFAULT '' COMMENT '收票人手机号',
`invoice_delivery_addrress` VARCHAR(150) NOT NULL DEFAULT '' COMMENT '收票人地址',
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_order_id`(`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='发票信息表';
-- =============================================================================
-- 用户类 usr
-- =============================================================================
-- ----------------------------
-- 用户表
-- ----------------------------
DROP TABLE IF EXISTS `usr_user`;
CREATE TABLE `usr_user` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_name` VARCHAR(60) NOT NULL COMMENT '用户账号',
`nick_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '用户昵称',
`contact_name` VARCHAR(25) NOT NULL DEFAULT '' COMMENT '真实姓名',
`password` VARCHAR(32) NOT NULL COMMENT '用户密码（MD5加密）[cdl:display=create,edit]',
`salt` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '密码盐 [cdl:display=none]',
`trade_pwd` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '交易密码 [cdl:display=create]',
`birthday` DATE COMMENT '生日 [cdl:display=none]',
`gender` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '性别(0=女,1=男,2=保密)',
`idcard_no` VARCHAR(18) NOT NULL DEFAULT '' COMMENT '身份证号码 [cdl:disable]',
`group_name` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '分组名称 [cdl:disable]',
`level` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户等级',
`avatar` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '用户头像',
`email` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '电子邮箱',
`mobile` VARCHAR(11) NOT NULL DEFAULT '' COMMENT '手机号码',
`created_at` INT UNSIGNED NOT NULL COMMENT '注册时间 [cdl:display=none]',
`register_ip` VARCHAR(46) NOT NULL COMMENT '注册IP [cdl:display=none]',
`login_time` DATETIME COMMENT '上次登录时间',
`login_ip` VARCHAR(46) NOT NULL DEFAULT '' COMMENT '上次登录IP [cdl:display=none]',
`stat_total_money` DECIMAL(10,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '累计金额 [cdl:display=index]',
`blance_money` DECIMAL(10,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '用户余额 [cdl:display=index]',
`frozen_money` DECIMAL(10,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '用户冻结金额 [cdl:display=index]',
`integral` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户积分 [cdl:display=index]',
`experience` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户经验值 [cdl:display=index]',
`signin_days_total` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '签到总天数 [cdl:display=none]',
`signin_days_series` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '持续签到天数总数（非连续周期清零）',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态(1=正常,0=禁止)',
`remark` VARCHAR(120) NOT NULL DEFAULT '' COMMENT '备注信息',
`inviter_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '邀请人ID [cdl:display=index]',
`alipay_openid` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '支付宝openID [cdl:display=none]',
`weixin_openid` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '微信openID [cdl:display=none]',
`weixin_unionid` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '微信unionID [cdl:display=none]',
`weixin_web_openid` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '微信web用户openID [cdl:display=none]',
`qq_openid` VARCHAR(60) NOT NULL DEFAULT '' COMMENT 'QQopenID [cdl:display=none]',
`qq_unionid` VARCHAR(60) NOT NULL DEFAULT '' COMMENT 'QQunionID [cdl:display=none]',
`baidu_openid` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '百度openID [cdl:display=none]',
`toutiao_openid` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '今日头条openID [cdl:display=none]',
`sina_openid` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '新浪微博openID [cdl:display=none]',
PRIMARY KEY (`id`),
KEY `idx_user_name`(`user_name`),
KEY `idx_mobile` (`mobile`),
KEY `idx_email` (`email`),
KEY `idx_status`(`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
-- ----------------------------
-- 用户分组表
-- ----------------------------
DROP TABLE IF EXISTS `usr_group`;
CREATE TABLE `usr_group` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`group_name` VARCHAR(30) NOT NULL COMMENT '分组名称',
`group_title` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '分组标题',
`rules` VARCHAR(2048) NOT NULL DEFAULT '' COMMENT '规则ID',
`sort` SMALLINT UNSIGNED NOT NULL DEFAULT 999 COMMENT '排序权重',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态(0=隐藏,1=显示)',
PRIMARY KEY (`id`),
KEY `idx_group_name`(`group_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户分组表';
-- ----------------------------
-- 用户等级表
-- ----------------------------
DROP TABLE IF EXISTS `usr_level`;
CREATE TABLE `usr_level` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`level_value` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '等级值',
`level_name` VARCHAR(30) NOT NULL COMMENT '等级名称',
`level_title` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '等级标题',
`icon` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '图标',
`experience` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '所需经验值',
`extra` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '扩展数据',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态(0=隐藏,1=显示)',
PRIMARY KEY (`id`),
KEY `idx_level_value`(`level_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户等级表';
-- ----------------------------
-- 推广员表
-- ----------------------------
DROP TABLE IF EXISTS `usr_promoter`;
CREATE TABLE IF NOT EXISTS `usr_promoter` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态(0=审核中,1=已审核,2=已清退)',
`stat_total_amount` DECIMAL(10,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '累计佣金 ',
`stat_lv1_quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '1级成员数量',
`stat_lv2_quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '2级成员数量',
`stat_lv3_quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '3级成员数量',
`created_at` INT UNSIGNED NOT NULL COMMENT '申请时间',
`stat_product_quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '分销商品累计件数',
`stat_product_amount` DECIMAL(10,0) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '分销商品累计金额',
PRIMARY KEY (`id`),
KEY `idx_user_id`(`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='推广员表';
-- ----------------------------
-- 用户收货地址表
-- ----------------------------
DROP TABLE IF EXISTS `usr_address`;
CREATE TABLE `usr_address` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
`contact_name` VARCHAR(60) NOT NULL COMMENT '姓名',
`mobile` VARCHAR(11) NOT NULL COMMENT '手机号',
`province` VARCHAR(60) NOT NULL COMMENT '省份',
`city` VARCHAR(60) NOT NULL COMMENT '城市',
`street` VARCHAR(60) NOT NULL COMMENT '街道',
`detail` VARCHAR(150) NOT NULL COMMENT '详细地址',
`post_code` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '邮编',
`longitude` DOUBLE(10,6) NOT NULL DEFAULT 0 COMMENT '经度（分米级）',
`latitude` DOUBLE(10,6) NOT NULL DEFAULT 0 COMMENT '纬度（分米级）',
`is_default` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否默认(0=否,1=是)',
`created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `idx_user_id`(`user_id`),
KEY `idx_is_default`(`is_default`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户收货地址表';
-- ----------------------------
-- 用户积分日志表
-- ----------------------------
DROP TABLE IF EXISTS `usr_integral_log`;
CREATE TABLE `usr_integral_log` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
`user_name` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '用户名',
`integral` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '变更积分',
`memo` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '变更原因',
`created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `idx_user_id`(`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户积分日志表';
-- =============================================================================
-- 财务类 fin
-- =============================================================================
-- ----------------------------
-- 财务提现表
-- ----------------------------
DROP TABLE IF EXISTS `fin_withdrawal`;
CREATE TABLE `fin_withdrawal` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
`serial_no` VARCHAR(16) NOT NULL COMMENT '流水号',
`contact_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '姓名',
`payment_type` VARCHAR(16) NOT NULL DEFAULT '' COMMENT '支付方式(bank=银行卡,alipay=支付宝,weixin=微信)',
`trade_no` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '第三方交易单号',
`bank_name` VARCHAR(60) NOT NULL COMMENT '银行名称',
`bank_no` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '银行卡号',
`bank_holder` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '开户人姓名',
`alipay_no` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '支付宝账号',
`wechat_no` VARCHAR(15) NOT NULL DEFAULT '' COMMENT '微信号',
`amount` DECIMAL(10,2) UNSIGNED NULL DEFAULT 0.00 COMMENT '提现金额',
`commission_amount` DECIMAL(6,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '手续费',
`user_remark` VARCHAR(150) NOT NULL DEFAULT '' COMMENT '用户备注',
`admin_remark` VARCHAR(150) NOT NULL DEFAULT '' COMMENT '管理员备注',
`created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
`updated_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '审核时间',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态(0=审核中,1=已提现,2=未通过)',
PRIMARY KEY (`id`),
KEY `idx_user_id`(`user_id`),
KEY `idx_status`(`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户提现表';
-- ----------------------------
-- 财务充值表
-- ----------------------------
DROP TABLE IF EXISTS `fin_recharge`;
CREATE TABLE `fin_recharge` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
`serial_no` VARCHAR(16) NOT NULL COMMENT '流水号',
`amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '充值金额',
`user_remark` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '用户备注',
`admin_remark` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '管理员备注',
`payment_type` VARCHAR(16) NOT NULL DEFAULT '' COMMENT '支付方式(cash=现金,bank=银行转账,alipay=支付宝,weixin=微信)',
`trade_no` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '第三方交易单号',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态(0=未支付,1=已支付,2=已取消)',
`created_at` INT UNSIGNED NOT NULL COMMENT '充值时间',
`updated_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '支付时间',
PRIMARY KEY (`id`),
KEY `idx_user_id`(`user_id`),
KEY `idx_status`(`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户充值表';
-- ----------------------------
-- 财务日志表
-- ----------------------------
DROP TABLE IF EXISTS `fin_log`;
CREATE TABLE IF NOT EXISTS `fin_log` (
`id` INT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`memo` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '变更原因',
`user_id` INT NOT NULL COMMENT '用户ID [cdl:display=none]',
`user_name` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '用户名',
`currency_type` TINYINT NOT NULL DEFAULT 1 COMMENT '货币类型(1=人民币,2=虚拟币)',
`amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '变更金额',
`item_id` INT NOT NULL DEFAULT 0 COMMENT '品项ID',
`item_name` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '品项名称',
`item_event` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '交易事件',
`status` TINYINT NOT NULL COMMENT '资金流向(1=充值成功,2=提现申请,3=提现成功,4=提现撤回,11=商城下单,12=商城退货,13=取消订单,61=兑换礼包,62=转账扣款,63=转账收款)',
`created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_status` (`status`),
KEY `idx_currency_type` (`currency_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户财务日志表';
-- =============================================================================
-- 统计类 stat
-- =============================================================================
-- ----------------------------
-- 用户浏览商品统计分析表
-- ----------------------------
DROP TABLE IF EXISTS `stat_user_product_visit`;
CREATE TABLE `stat_user_product_visit` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
`product_id` INT UNSIGNED NOT NULL COMMENT '商品ID',
`category_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品分类ID',
`created_at` INT UNSIGNED NOT NULL COMMENT '添加时间',
`date_ymd` DATE NOT NULL DEFAULT '1970-1-1' COMMENT '日期',
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户浏览商品统计分析表';
-- =============================================================================
-- 其他类
-- =============================================================================
-- ----------------------------
-- 代理商表
-- ----------------------------
DROP TABLE IF EXISTS `agt_agent_admin`;
CREATE TABLE IF NOT EXISTS `agt_agent_admin` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_name` VARCHAR(60) NOT NULL COMMENT '用户名',
`nick_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '昵称',
`contact` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '联系人',
`wechat` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '微信',
`password` VARCHAR(32) NOT NULL COMMENT '密码 [cdl:display=create,edit]',
`salt` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '密码盐 [cdl:display=none]',
`avatar` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '头像',
`email` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '电子邮箱',
`mobile` VARCHAR(11) NOT NULL DEFAULT '' COMMENT '手机号码',
`gender` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '性别(0=女,1=男,2=保密)',
`login_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '登录次数',
`login_time` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '登录时间 [cdl:display=index]',
`login_ip` VARCHAR(46) NOT NULL DEFAULT '' COMMENT '登录IP [cdl:display=none]',
`register_ip` VARCHAR(46) NOT NULL COMMENT '注册IP [cdl:display=none]',
`created_at` INT UNSIGNED NOT NULL COMMENT '注册时间 [cdl:display=none]',
`updated_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '审核时间 [cdl:display=none]',
`status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态(0=待审核,1=审核通过,2=审核失败,3=禁用)',
`audit_comment` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '审核备注',
`commission_rate` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '提成比率',
`total_amount` INT NOT NULL DEFAULT 0 COMMENT '累计金额 [cdl:display=index]',
`blance_amount` INT NOT NULL DEFAULT 0 COMMENT '可用余额 [cdl:display=index]',
`frozen_amount` INT NOT NULL DEFAULT 0 COMMENT '冻结金额 [cdl:display=index]',
`stat_withdrawal_amount` INT NOT NULL DEFAULT 0 COMMENT '提现总金额 [cdl:display=none]',
`stat_income_amount` INT NOT NULL DEFAULT 0 COMMENT '收益总金额 [cdl:display=none]',
`stat_referral_quantity` INT NOT NULL DEFAULT 0 COMMENT '推广量 [cdl:display=none]',
PRIMARY KEY (`id`),
KEY `idx_user_name` (`user_name`),
KEY `idx_mobile` (`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='代理商表';
