-- 扩展消费者（会员）“身价”相关修改 -- 为消费者（即会员表）增加当前身价值、预增加的身价值、增加后的身价总值、 直接推荐人id字段等 ALTER TABLE `member_user` ADD COLUMN `recommenderd_id` bigint COMMENT '直接推荐人id', ADD COLUMN `current_member_worth_value` bigint COMMENT '当前身价值', ADD COLUMN `pre_added_member_worth_value` bigint COMMENT '预增加身价值', ADD COLUMN `future_member_worth_value` bigint COMMENT '未来身价值，未来身价值=当前身价值+预增加身价值', ADD COLUMN `current_member_worth_level_name` varchar(64) COMMENT '当前身价级别名称', ADD COLUMN `current_member_worth_level_id` bigint COMMENT '当前身价级别id', ADD COLUMN `last_sign_in_time` datetime COMMENT '最后签到时间', ADD COLUMN `continuous_sign_in_days` datetime COMMENT '连续签到天数'; -- 身价级别表设置表 CREATE TABLE `member_worth_level_setting` ( `id` bigint NOT NULL COMMENT '身价级别id', `level_name` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '身价级别名称', `member_worth_value` bigint NOT NULL DEFAULT 0 COMMENT '本级别需达到的身价值', `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', `tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '铺身价级别表设置表' ROW_FORMAT = Dynamic; -- 增加 会员资产变化类别表, 此表主要用于设置各种导致用户资产发生变化的行为的分值变化 -- 字段有id、类别码（数字类型，在java中对应常量）、类别名称、类别描述、变化值（就是签到身价加100那个100） CREATE TABLE `member_assets_change_category` ( `id` bigint NOT NULL COMMENT '会员资产变化类别id', `name` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '类别名称', `code` int NOT NULL DEFAULT -1 COMMENT '类别码', `variation_value` int NOT NULL DEFAULT 0 COMMENT '身价、积分、看点在本类别操作的变化值', `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '描述', `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', `tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '会员资产变化类别表，含身价、积分、看点变化类别' ROW_FORMAT = Dynamic; -- 会员身价变化日志表 -- 字段有会员资产变化类别id、分值、操作时间、对象名(如 订单表名)、对象id（如 订单id）、是否已生效、计划生效时间、实际生效时间、描述 CREATE TABLE `member_worth_value_change_log` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '会员身价变化日志id', `member_assets_change_category_id` bigint NOT NULL COMMENT '会员资产变化类别id',-- 新加字段 `member_worth_value` bigint NOT NULL COMMENT '身价值', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间', `object_name` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '操作对应业务对象名称', `object_id` bigint NOT NULL DEFAULT 0 COMMENT '操作对应业务对象id', `effective` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否已生效', `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '描述', `plan_effective_time` datetime NULL COMMENT '计划生效时间', `actual_effective_time` datetime NULL COMMENT '实际生效时间', `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '创建者', `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', `tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '店铺表' ROW_FORMAT = Dynamic; -- 增加 会员签到表 CREATE TABLE `member_sign_in` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '会员签到表id', `member_user_id` bigint NOT NULL DEFAULT 0 COMMENT '会员用户id', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '签到时间', `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '创建者', `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', `tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '店铺表' ROW_FORMAT = Dynamic; -- 为消费者（即会员表）增加当前的积分值、预增加的积分值、累计消费额（只算人民币） ALTER TABLE `member_user` ADD COLUMN `current_member_points` bigint COMMENT '当前积分值', ADD COLUMN `pre_added_member_worth` bigint COMMENT '预增加积分值' ; -- 消费者（即会员表）增加累计消费金额（只算人民币） ALTER TABLE `member_user` ADD COLUMN `cumulative_spending` bigint COMMENT '累计消费金额（只算人民币，单位为分）'; /* 会员积分变化日志表：会员资产变化类别id、分值、操作时间、对象名(如 订单表名)、对象id（如 订单id）、 是否已生效、计划生效时间、实际生效时间、描述。定时器直接扫描本表未生效，且过了计划生效时间的数据进行处理。 */ CREATE TABLE `member_points_change_log` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '会员积分变化日志id', `member_assets_change_category_id` bigint NOT NULL COMMENT '会员资产变化类别id', `member_points` bigint NOT NULL COMMENT '积分值', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间', `object_name` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '操作对应业务对象名称(如 订单表名)', `object_id` bigint NOT NULL DEFAULT 0 COMMENT '操作对应业务对象id（如 订单id）', `effective` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否已生效', `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '描述', `plan_effective_time` datetime NULL COMMENT '计划生效时间', `actual_effective_time` datetime NULL COMMENT '实际生效时间', `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '创建者', `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', `tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '店铺表' ROW_FORMAT = Dynamic;