432 lines
20 KiB
SQL
432 lines
20 KiB
SQL
-- =====================================================
|
||
-- 微信小程序会员绑定系统数据库表结构
|
||
-- 创建日期: 2025-09-30
|
||
-- 版本: v1.0
|
||
-- 描述: 包含会员信息、绑定记录、权益配置等核心表结构
|
||
-- =====================================================
|
||
|
||
-- 设置字符集
|
||
SET NAMES utf8mb4;
|
||
SET FOREIGN_KEY_CHECKS = 0;
|
||
|
||
-- =====================================================
|
||
-- 1. 会员信息表 (member_info)
|
||
-- 用途: 存储用户会员身份信息和状态
|
||
-- =====================================================
|
||
DROP TABLE IF EXISTS `member_info`;
|
||
CREATE TABLE `member_info` (
|
||
`member_id` varchar(64) NOT NULL COMMENT '会员ID，主键，格式：MB + 时间戳 + 随机数',
|
||
`openid` varchar(128) NOT NULL COMMENT '微信用户openid，唯一标识',
|
||
`union_id` varchar(128) DEFAULT NULL COMMENT '微信用户unionid，跨应用唯一标识',
|
||
`member_level` varchar(16) NOT NULL DEFAULT 'FREE' COMMENT '会员等级：FREE-免费用户，VIP-基础会员，SVIP-高级会员',
|
||
`member_status` varchar(16) NOT NULL DEFAULT 'ACTIVE' COMMENT '会员状态：ACTIVE-有效，EXPIRED-过期，SUSPENDED-暂停，CANCELLED-取消',
|
||
`start_time` datetime DEFAULT NULL COMMENT '会员开始时间',
|
||
`end_time` datetime DEFAULT NULL COMMENT '会员结束时间，NULL表示永久有效',
|
||
`purchase_order_id` varchar(64) DEFAULT NULL COMMENT '购买订单ID，关联支付订单',
|
||
`auto_renew` tinyint DEFAULT 0 COMMENT '是否自动续费：0-否，1-是',
|
||
`renew_count` int DEFAULT 0 COMMENT '续费次数统计',
|
||
`total_amount` decimal(10,2) DEFAULT 0.00 COMMENT '累计消费金额',
|
||
`points` int DEFAULT 0 COMMENT '会员积分',
|
||
`invite_code` varchar(32) DEFAULT NULL COMMENT '邀请码',
|
||
`invited_by` varchar(64) DEFAULT NULL COMMENT '邀请人会员ID',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` tinyint DEFAULT 0 COMMENT '是否删除：0-未删除，1-已删除',
|
||
PRIMARY KEY (`member_id`),
|
||
UNIQUE KEY `uk_openid` (`openid`),
|
||
UNIQUE KEY `uk_invite_code` (`invite_code`),
|
||
KEY `idx_member_level` (`member_level`),
|
||
KEY `idx_member_status` (`member_status`),
|
||
KEY `idx_end_time` (`end_time`),
|
||
KEY `idx_invited_by` (`invited_by`),
|
||
KEY `idx_create_time` (`create_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员信息表';
|
||
|
||
-- =====================================================
|
||
-- 2. 会员绑定记录表 (member_bind_log)
|
||
-- 用途: 记录会员绑定、升级、降级等操作历史
|
||
-- =====================================================
|
||
DROP TABLE IF EXISTS `member_bind_log`;
|
||
CREATE TABLE `member_bind_log` (
|
||
`log_id` bigint NOT NULL AUTO_INCREMENT COMMENT '日志ID，主键',
|
||
`member_id` varchar(64) NOT NULL COMMENT '会员ID',
|
||
`openid` varchar(128) NOT NULL COMMENT '微信用户openid',
|
||
`bind_type` varchar(16) NOT NULL COMMENT '绑定类型：BIND-首次绑定，UNBIND-解绑，UPGRADE-升级，DOWNGRADE-降级，RENEW-续费',
|
||
`old_level` varchar(16) DEFAULT NULL COMMENT '原会员等级',
|
||
`new_level` varchar(16) NOT NULL COMMENT '新会员等级',
|
||
`old_end_time` datetime DEFAULT NULL COMMENT '原到期时间',
|
||
`new_end_time` datetime DEFAULT NULL COMMENT '新到期时间',
|
||
`bind_source` varchar(32) DEFAULT NULL COMMENT '绑定来源：PURCHASE-购买，GIFT-赠送，ADMIN-管理员操作，INVITE-邀请奖励',
|
||
`related_order_id` varchar(64) DEFAULT NULL COMMENT '关联订单ID',
|
||
`amount` decimal(10,2) DEFAULT NULL COMMENT '相关金额',
|
||
`duration_days` int DEFAULT NULL COMMENT '增加天数',
|
||
`operator_id` varchar(64) DEFAULT NULL COMMENT '操作员ID（管理员操作时使用）',
|
||
`bind_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '绑定时间',
|
||
`client_ip` varchar(45) DEFAULT NULL COMMENT '客户端IP地址',
|
||
`user_agent` varchar(500) DEFAULT NULL COMMENT '用户代理信息',
|
||
`remark` varchar(500) DEFAULT NULL COMMENT '备注信息',
|
||
PRIMARY KEY (`log_id`),
|
||
KEY `idx_member_id` (`member_id`),
|
||
KEY `idx_openid` (`openid`),
|
||
KEY `idx_bind_type` (`bind_type`),
|
||
KEY `idx_bind_time` (`bind_time`),
|
||
KEY `idx_related_order_id` (`related_order_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员绑定记录表';
|
||
|
||
-- =====================================================
|
||
-- 3. 会员权益配置表 (member_benefit_config)
|
||
-- 用途: 配置不同等级会员的权益内容
|
||
-- =====================================================
|
||
DROP TABLE IF EXISTS `member_benefit_config`;
|
||
CREATE TABLE `member_benefit_config` (
|
||
`config_id` int NOT NULL AUTO_INCREMENT COMMENT '配置ID，主键',
|
||
`member_level` varchar(16) NOT NULL COMMENT '会员等级：FREE/VIP/SVIP',
|
||
`benefit_code` varchar(32) NOT NULL COMMENT '权益代码，唯一标识',
|
||
`benefit_name` varchar(100) NOT NULL COMMENT '权益名称',
|
||
`benefit_desc` varchar(500) DEFAULT NULL COMMENT '权益描述',
|
||
`benefit_type` varchar(16) NOT NULL DEFAULT 'FEATURE' COMMENT '权益类型：FEATURE-功能权益，QUOTA-配额权益，SERVICE-服务权益',
|
||
`benefit_value` varchar(100) DEFAULT NULL COMMENT '权益值：unlimited-无限制，数字-具体数量',
|
||
`daily_limit` int DEFAULT NULL COMMENT '每日限制数量，NULL表示无限制',
|
||
`monthly_limit` int DEFAULT NULL COMMENT '每月限制数量，NULL表示无限制',
|
||
`is_enabled` tinyint DEFAULT 1 COMMENT '是否启用：0-禁用，1-启用',
|
||
`sort_order` int DEFAULT 0 COMMENT '排序顺序，数字越小越靠前',
|
||
`icon_url` varchar(255) DEFAULT NULL COMMENT '权益图标URL',
|
||
`detail_url` varchar(255) DEFAULT NULL COMMENT '权益详情页URL',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`config_id`),
|
||
UNIQUE KEY `uk_level_code` (`member_level`, `benefit_code`),
|
||
KEY `idx_member_level` (`member_level`),
|
||
KEY `idx_benefit_code` (`benefit_code`),
|
||
KEY `idx_benefit_type` (`benefit_type`),
|
||
KEY `idx_sort_order` (`sort_order`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员权益配置表';
|
||
|
||
-- =====================================================
|
||
-- 4. 会员权益使用记录表 (member_benefit_usage)
|
||
-- 用途: 记录会员权益的使用情况和统计
|
||
-- =====================================================
|
||
DROP TABLE IF EXISTS `member_benefit_usage`;
|
||
CREATE TABLE `member_benefit_usage` (
|
||
`usage_id` bigint NOT NULL AUTO_INCREMENT COMMENT '使用记录ID，主键',
|
||
`member_id` varchar(64) NOT NULL COMMENT '会员ID',
|
||
`openid` varchar(128) NOT NULL COMMENT '微信用户openid',
|
||
`benefit_code` varchar(32) NOT NULL COMMENT '权益代码',
|
||
`usage_date` date NOT NULL COMMENT '使用日期',
|
||
`usage_count` int DEFAULT 1 COMMENT '使用次数',
|
||
`daily_total` int DEFAULT 0 COMMENT '当日累计使用次数',
|
||
`monthly_total` int DEFAULT 0 COMMENT '当月累计使用次数',
|
||
`request_id` varchar(64) DEFAULT NULL COMMENT '请求ID，用于追踪具体请求',
|
||
`usage_detail` json DEFAULT NULL COMMENT '使用详情，JSON格式存储',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`usage_id`),
|
||
UNIQUE KEY `uk_member_benefit_date` (`member_id`, `benefit_code`, `usage_date`),
|
||
KEY `idx_openid` (`openid`),
|
||
KEY `idx_benefit_code` (`benefit_code`),
|
||
KEY `idx_usage_date` (`usage_date`),
|
||
KEY `idx_create_time` (`create_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员权益使用记录表';
|
||
|
||
-- =====================================================
|
||
-- 5. 会员等级配置表 (member_level_config)
|
||
-- 用途: 配置会员等级的基本信息和规则
|
||
-- =====================================================
|
||
DROP TABLE IF EXISTS `member_level_config`;
|
||
CREATE TABLE `member_level_config` (
|
||
`level_id` int NOT NULL AUTO_INCREMENT COMMENT '等级ID，主键',
|
||
`level_code` varchar(16) NOT NULL COMMENT '等级代码：FREE/VIP/SVIP',
|
||
`level_name` varchar(50) NOT NULL COMMENT '等级名称',
|
||
`level_desc` varchar(200) DEFAULT NULL COMMENT '等级描述',
|
||
`level_order` int NOT NULL DEFAULT 0 COMMENT '等级顺序，数字越大等级越高',
|
||
`price_monthly` decimal(10,2) DEFAULT NULL COMMENT '月费价格',
|
||
`price_yearly` decimal(10,2) DEFAULT NULL COMMENT '年费价格',
|
||
`discount_yearly` decimal(5,2) DEFAULT NULL COMMENT '年费折扣率（0-1）',
|
||
`max_devices` int DEFAULT 1 COMMENT '最大设备数量',
|
||
`support_level` varchar(16) DEFAULT 'BASIC' COMMENT '支持等级：BASIC-基础，PRIORITY-优先，EXCLUSIVE-专属',
|
||
`badge_color` varchar(7) DEFAULT '#999999' COMMENT '徽章颜色，十六进制色值',
|
||
`badge_icon` varchar(255) DEFAULT NULL COMMENT '徽章图标URL',
|
||
`is_enabled` tinyint DEFAULT 1 COMMENT '是否启用：0-禁用，1-启用',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`level_id`),
|
||
UNIQUE KEY `uk_level_code` (`level_code`),
|
||
KEY `idx_level_order` (`level_order`),
|
||
KEY `idx_is_enabled` (`is_enabled`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员等级配置表';
|
||
|
||
-- =====================================================
|
||
-- 6. 会员邀请记录表 (member_invite_log)
|
||
-- 用途: 记录会员邀请关系和奖励发放
|
||
-- =====================================================
|
||
DROP TABLE IF EXISTS `member_invite_log`;
|
||
CREATE TABLE `member_invite_log` (
|
||
`invite_id` bigint NOT NULL AUTO_INCREMENT COMMENT '邀请记录ID，主键',
|
||
`inviter_id` varchar(64) NOT NULL COMMENT '邀请人会员ID',
|
||
`inviter_openid` varchar(128) NOT NULL COMMENT '邀请人openid',
|
||
`invitee_id` varchar(64) DEFAULT NULL COMMENT '被邀请人会员ID',
|
||
`invitee_openid` varchar(128) NOT NULL COMMENT '被邀请人openid',
|
||
`invite_code` varchar(32) NOT NULL COMMENT '邀请码',
|
||
`invite_status` varchar(16) DEFAULT 'PENDING' COMMENT '邀请状态：PENDING-待接受，ACCEPTED-已接受，REWARDED-已奖励',
|
||
`reward_type` varchar(16) DEFAULT NULL COMMENT '奖励类型：POINTS-积分，DAYS-天数，LEVEL-等级',
|
||
`reward_value` varchar(50) DEFAULT NULL COMMENT '奖励值',
|
||
`inviter_reward` varchar(50) DEFAULT NULL COMMENT '邀请人奖励',
|
||
`invitee_reward` varchar(50) DEFAULT NULL COMMENT '被邀请人奖励',
|
||
`invite_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '邀请时间',
|
||
`accept_time` datetime DEFAULT NULL COMMENT '接受时间',
|
||
`reward_time` datetime DEFAULT NULL COMMENT '奖励发放时间',
|
||
`expire_time` datetime DEFAULT NULL COMMENT '邀请过期时间',
|
||
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
||
PRIMARY KEY (`invite_id`),
|
||
UNIQUE KEY `uk_invite_code` (`invite_code`),
|
||
KEY `idx_inviter_id` (`inviter_id`),
|
||
KEY `idx_invitee_id` (`invitee_id`),
|
||
KEY `idx_invite_status` (`invite_status`),
|
||
KEY `idx_invite_time` (`invite_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员邀请记录表';
|
||
|
||
-- =====================================================
|
||
-- 初始化数据
|
||
-- =====================================================
|
||
|
||
-- 插入会员等级配置数据
|
||
INSERT INTO `member_level_config` (`level_code`, `level_name`, `level_desc`, `level_order`, `price_monthly`, `price_yearly`, `discount_yearly`, `max_devices`, `support_level`, `badge_color`, `is_enabled`) VALUES
|
||
('FREE', '免费用户', '基础功能使用，每日有限制', 0, NULL, NULL, NULL, 1, 'BASIC', '#999999', 1),
|
||
('VIP', 'VIP会员', '高级功能无限制使用，优先客服支持', 1, 29.90, 299.00, 0.16, 3, 'PRIORITY', '#FFD700', 1),
|
||
('SVIP', 'SVIP会员', '全功能无限制，专属客服，API访问', 2, 99.90, 999.00, 0.17, 10, 'EXCLUSIVE', '#FF6B35', 1);
|
||
|
||
-- 插入会员权益配置数据
|
||
INSERT INTO `member_benefit_config` (`member_level`, `benefit_code`, `benefit_name`, `benefit_desc`, `benefit_type`, `benefit_value`, `daily_limit`, `monthly_limit`, `sort_order`) VALUES
|
||
-- 免费用户权益
|
||
('FREE', 'basic_chat', '基础对话', '每日50次AI对话', 'QUOTA', '50', 50, 1500, 1),
|
||
('FREE', 'basic_voice', '基础语音', '每日20次语音识别', 'QUOTA', '20', 20, 600, 2),
|
||
('FREE', 'basic_image', '基础图像', '每日5次图像识别', 'QUOTA', '5', 5, 150, 3),
|
||
('FREE', 'community_support', '社区支持', '社区问答支持', 'SERVICE', 'enabled', NULL, NULL, 4),
|
||
|
||
-- VIP会员权益
|
||
('VIP', 'advanced_chat', '高级对话', '无限制AI对话功能', 'QUOTA', 'unlimited', NULL, NULL, 1),
|
||
('VIP', 'advanced_voice', '高级语音', '无限制语音识别和合成', 'QUOTA', 'unlimited', NULL, NULL, 2),
|
||
('VIP', 'advanced_image', '高级图像', '每日100次图像处理', 'QUOTA', '100', 100, 3000, 3),
|
||
('VIP', 'priority_support', '优先支持', '客服优先响应，24小时内回复', 'SERVICE', 'enabled', NULL, NULL, 4),
|
||
('VIP', 'custom_model', '自定义模型', '使用自定义AI模型', 'FEATURE', 'enabled', NULL, NULL, 5),
|
||
('VIP', 'export_data', '数据导出', '对话记录导出功能', 'FEATURE', 'enabled', NULL, NULL, 6),
|
||
('VIP', 'ad_free', '无广告', '享受无广告体验', 'FEATURE', 'enabled', NULL, NULL, 7),
|
||
|
||
-- SVIP会员权益
|
||
('SVIP', 'premium_chat', '专属对话', '专属AI模型，无限制对话', 'QUOTA', 'unlimited', NULL, NULL, 1),
|
||
('SVIP', 'premium_voice', '专属语音', '高质量语音合成，无限制', 'QUOTA', 'unlimited', NULL, NULL, 2),
|
||
('SVIP', 'premium_image', '专属图像', '无限制图像处理和生成', 'QUOTA', 'unlimited', NULL, NULL, 3),
|
||
('SVIP', 'exclusive_support', '专属服务', '一对一专属客服，1小时内响应', 'SERVICE', 'enabled', NULL, NULL, 4),
|
||
('SVIP', 'api_access', 'API访问', '开放API接口调用权限', 'FEATURE', 'enabled', NULL, NULL, 5),
|
||
('SVIP', 'advanced_export', '高级导出', '全量数据导出和分析', 'FEATURE', 'enabled', NULL, NULL, 6),
|
||
('SVIP', 'beta_features', '内测功能', '优先体验新功能', 'FEATURE', 'enabled', NULL, NULL, 7),
|
||
('SVIP', 'custom_training', '模型训练', '个人专属模型训练', 'FEATURE', 'enabled', NULL, NULL, 8),
|
||
('SVIP', 'white_label', '白标服务', '定制化品牌服务', 'SERVICE', 'enabled', NULL, NULL, 9);
|
||
|
||
-- =====================================================
|
||
-- 创建索引优化查询性能
|
||
-- =====================================================
|
||
|
||
-- 会员信息表复合索引
|
||
CREATE INDEX `idx_member_level_status` ON `member_info` (`member_level`, `member_status`);
|
||
CREATE INDEX `idx_end_time_status` ON `member_info` (`end_time`, `member_status`);
|
||
|
||
-- 绑定记录表复合索引
|
||
CREATE INDEX `idx_member_bind_time` ON `member_bind_log` (`member_id`, `bind_time`);
|
||
CREATE INDEX `idx_openid_bind_time` ON `member_bind_log` (`openid`, `bind_time`);
|
||
|
||
-- 权益使用记录表复合索引
|
||
CREATE INDEX `idx_member_benefit_month` ON `member_benefit_usage` (`member_id`, `benefit_code`, `usage_date`);
|
||
|
||
-- =====================================================
|
||
-- 创建视图简化查询
|
||
-- =====================================================
|
||
|
||
-- 会员信息视图（包含等级配置）
|
||
CREATE VIEW `v_member_info` AS
|
||
SELECT
|
||
mi.member_id,
|
||
mi.openid,
|
||
mi.member_level,
|
||
mi.member_status,
|
||
mi.start_time,
|
||
mi.end_time,
|
||
mi.auto_renew,
|
||
mi.points,
|
||
mi.total_amount,
|
||
mi.create_time,
|
||
mlc.level_name,
|
||
mlc.level_desc,
|
||
mlc.badge_color,
|
||
mlc.support_level,
|
||
CASE
|
||
WHEN mi.end_time IS NULL THEN 1
|
||
WHEN mi.end_time > NOW() THEN 1
|
||
ELSE 0
|
||
END AS is_valid,
|
||
CASE
|
||
WHEN mi.end_time IS NULL THEN NULL
|
||
ELSE DATEDIFF(mi.end_time, NOW())
|
||
END AS remaining_days
|
||
FROM member_info mi
|
||
LEFT JOIN member_level_config mlc ON mi.member_level = mlc.level_code
|
||
WHERE mi.deleted = 0;
|
||
|
||
-- 权益统计视图
|
||
CREATE VIEW `v_benefit_stats` AS
|
||
SELECT
|
||
mbu.member_id,
|
||
mbu.benefit_code,
|
||
mbu.usage_date,
|
||
mbu.daily_total,
|
||
mbu.monthly_total,
|
||
mbc.benefit_name,
|
||
mbc.daily_limit,
|
||
mbc.monthly_limit,
|
||
CASE
|
||
WHEN mbc.daily_limit IS NULL THEN 1
|
||
WHEN mbu.daily_total < mbc.daily_limit THEN 1
|
||
ELSE 0
|
||
END AS daily_available,
|
||
CASE
|
||
WHEN mbc.monthly_limit IS NULL THEN 1
|
||
WHEN mbu.monthly_total < mbc.monthly_limit THEN 1
|
||
ELSE 0
|
||
END AS monthly_available
|
||
FROM member_benefit_usage mbu
|
||
LEFT JOIN member_benefit_config mbc ON mbu.benefit_code = mbc.benefit_code;
|
||
|
||
-- =====================================================
|
||
-- 存储过程
|
||
-- =====================================================
|
||
|
||
DELIMITER $$
|
||
|
||
-- 检查会员权益存储过程
|
||
CREATE PROCEDURE `sp_check_member_benefit`(
|
||
IN p_openid VARCHAR(128),
|
||
IN p_benefit_code VARCHAR(32),
|
||
OUT p_has_permission TINYINT,
|
||
OUT p_remaining_count INT,
|
||
OUT p_message VARCHAR(255)
|
||
)
|
||
BEGIN
|
||
DECLARE v_member_level VARCHAR(16);
|
||
DECLARE v_member_status VARCHAR(16);
|
||
DECLARE v_end_time DATETIME;
|
||
DECLARE v_daily_limit INT;
|
||
DECLARE v_daily_used INT DEFAULT 0;
|
||
DECLARE v_benefit_enabled TINYINT DEFAULT 0;
|
||
|
||
-- 初始化返回值
|
||
SET p_has_permission = 0;
|
||
SET p_remaining_count = 0;
|
||
SET p_message = '';
|
||
|
||
-- 获取会员信息
|
||
SELECT member_level, member_status, end_time
|
||
INTO v_member_level, v_member_status, v_end_time
|
||
FROM member_info
|
||
WHERE openid = p_openid AND deleted = 0;
|
||
|
||
-- 检查会员是否存在
|
||
IF v_member_level IS NULL THEN
|
||
SET p_message = '用户不存在';
|
||
LEAVE sp_check_member_benefit;
|
||
END IF;
|
||
|
||
-- 检查会员状态
|
||
IF v_member_status != 'ACTIVE' THEN
|
||
SET p_message = '会员状态异常';
|
||
LEAVE sp_check_member_benefit;
|
||
END IF;
|
||
|
||
-- 检查会员是否过期
|
||
IF v_end_time IS NOT NULL AND v_end_time < NOW() THEN
|
||
SET p_message = '会员已过期';
|
||
LEAVE sp_check_member_benefit;
|
||
END IF;
|
||
|
||
-- 检查权益配置
|
||
SELECT daily_limit, is_enabled
|
||
INTO v_daily_limit, v_benefit_enabled
|
||
FROM member_benefit_config
|
||
WHERE member_level = v_member_level AND benefit_code = p_benefit_code;
|
||
|
||
-- 检查权益是否存在且启用
|
||
IF v_benefit_enabled != 1 THEN
|
||
SET p_message = '权益不存在或未启用';
|
||
LEAVE sp_check_member_benefit;
|
||
END IF;
|
||
|
||
-- 检查每日使用限制
|
||
IF v_daily_limit IS NOT NULL THEN
|
||
SELECT IFNULL(daily_total, 0)
|
||
INTO v_daily_used
|
||
FROM member_benefit_usage
|
||
WHERE member_id = (SELECT member_id FROM member_info WHERE openid = p_openid)
|
||
AND benefit_code = p_benefit_code
|
||
AND usage_date = CURDATE();
|
||
|
||
IF v_daily_used >= v_daily_limit THEN
|
||
SET p_message = '今日使用次数已达上限';
|
||
LEAVE sp_check_member_benefit;
|
||
END IF;
|
||
|
||
SET p_remaining_count = v_daily_limit - v_daily_used;
|
||
ELSE
|
||
SET p_remaining_count = -1; -- 无限制
|
||
END IF;
|
||
|
||
-- 权益验证通过
|
||
SET p_has_permission = 1;
|
||
SET p_message = '验证通过';
|
||
|
||
END$$
|
||
|
||
DELIMITER ;
|
||
|
||
-- =====================================================
|
||
-- 触发器
|
||
-- =====================================================
|
||
|
||
DELIMITER $$
|
||
|
||
-- 会员信息更新触发器
|
||
CREATE TRIGGER `tr_member_info_update`
|
||
BEFORE UPDATE ON `member_info`
|
||
FOR EACH ROW
|
||
BEGIN
|
||
-- 自动更新修改时间
|
||
SET NEW.update_time = NOW();
|
||
|
||
-- 检查会员过期状态
|
||
IF NEW.end_time IS NOT NULL AND NEW.end_time < NOW() AND NEW.member_status = 'ACTIVE' THEN
|
||
SET NEW.member_status = 'EXPIRED';
|
||
END IF;
|
||
END$$
|
||
|
||
DELIMITER ;
|
||
|
||
-- =====================================================
|
||
-- 设置外键约束
|
||
-- =====================================================
|
||
SET FOREIGN_KEY_CHECKS = 1;
|
||
|
||
-- =====================================================
|
||
-- 数据库表结构创建完成
|
||
-- =====================================================
|
||
|
||
-- 显示创建的表
|
||
SHOW TABLES LIKE 'member_%';
|
||
|
||
-- 显示表结构统计
|
||
SELECT
|
||
TABLE_NAME as '表名',
|
||
TABLE_COMMENT as '表注释',
|
||
TABLE_ROWS as '预估行数',
|
||
ROUND(DATA_LENGTH/1024/1024, 2) as '数据大小(MB)'
|
||
FROM information_schema.TABLES
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME LIKE 'member_%'
|
||
ORDER BY TABLE_NAME; |