144 lines
10 KiB
SQL
Executable File
144 lines
10 KiB
SQL
Executable File
-- ============================================================
|
|
-- 完整的直播间模块数据库同步脚本
|
|
-- 包含所有模型与数据库不一致的字段
|
|
-- 执行前请先备份数据库
|
|
--
|
|
-- 注意：此脚本已处理字段存在的情况，可安全重复执行
|
|
-- ============================================================
|
|
|
|
-- ============================================================
|
|
-- 1. livestream_activities 表
|
|
-- ============================================================
|
|
|
|
-- 1.1 承诺机制字段
|
|
-- 检查并添加 commitment_algo
|
|
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'livestream_activities' AND column_name = 'commitment_algo');
|
|
SET @sql = IF(@col_exists = 0, "ALTER TABLE livestream_activities ADD COLUMN commitment_algo VARCHAR(32) DEFAULT 'commit-v1' COMMENT '承诺算法版本' AFTER status", 'SELECT 1');
|
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
|
|
-- 检查并添加 commitment_seed_master
|
|
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'livestream_activities' AND column_name = 'commitment_seed_master');
|
|
SET @sql = IF(@col_exists = 0, "ALTER TABLE livestream_activities ADD COLUMN commitment_seed_master BLOB COMMENT '主种子(32字节)' AFTER commitment_algo", 'SELECT 1');
|
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
|
|
-- 检查并添加 commitment_seed_hash
|
|
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'livestream_activities' AND column_name = 'commitment_seed_hash');
|
|
SET @sql = IF(@col_exists = 0, "ALTER TABLE livestream_activities ADD COLUMN commitment_seed_hash BLOB COMMENT '种子SHA256哈希' AFTER commitment_seed_master", 'SELECT 1');
|
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
|
|
-- 检查并添加 commitment_state_version
|
|
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'livestream_activities' AND column_name = 'commitment_state_version');
|
|
SET @sql = IF(@col_exists = 0, "ALTER TABLE livestream_activities ADD COLUMN commitment_state_version INT DEFAULT 0 COMMENT '状态版本' AFTER commitment_seed_hash", 'SELECT 1');
|
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
|
|
-- 1.2 下单奖励字段
|
|
-- 检查并添加 order_reward_type
|
|
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'livestream_activities' AND column_name = 'order_reward_type');
|
|
SET @sql = IF(@col_exists = 0, "ALTER TABLE livestream_activities ADD COLUMN order_reward_type VARCHAR(32) DEFAULT '' COMMENT '下单奖励类型: flip_card/minesweeper' AFTER douyin_product_id", 'SELECT 1');
|
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
|
|
-- 检查并添加 order_reward_quantity
|
|
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'livestream_activities' AND column_name = 'order_reward_quantity');
|
|
SET @sql = IF(@col_exists = 0, "ALTER TABLE livestream_activities ADD COLUMN order_reward_quantity INT DEFAULT 1 COMMENT '下单奖励数量: 1-100' AFTER order_reward_type", 'SELECT 1');
|
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
|
|
-- 1.3 门票价格字段
|
|
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'livestream_activities' AND column_name = 'ticket_price');
|
|
SET @sql = IF(@col_exists = 0, "ALTER TABLE livestream_activities ADD COLUMN ticket_price INT DEFAULT 0 COMMENT '门票价格(分)' AFTER end_time", 'SELECT 1');
|
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
|
|
-- ============================================================
|
|
-- 2. livestream_prizes 表
|
|
-- ============================================================
|
|
|
|
-- 2.1 成本价字段
|
|
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'livestream_prizes' AND column_name = 'cost_price');
|
|
SET @sql = IF(@col_exists = 0, "ALTER TABLE livestream_prizes ADD COLUMN cost_price BIGINT DEFAULT 0 COMMENT '成本价(分)' AFTER sort", 'SELECT 1');
|
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
|
|
-- ============================================================
|
|
-- 3. livestream_draw_logs 表
|
|
-- ============================================================
|
|
|
|
-- 3.1 抖店订单号快照
|
|
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'livestream_draw_logs' AND column_name = 'shop_order_id');
|
|
SET @sql = IF(@col_exists = 0, "ALTER TABLE livestream_draw_logs ADD COLUMN shop_order_id VARCHAR(64) DEFAULT '' COMMENT '抖店订单号' AFTER douyin_order_id", 'SELECT 1');
|
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
|
|
-- 3.2 用户昵称
|
|
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'livestream_draw_logs' AND column_name = 'user_nickname');
|
|
SET @sql = IF(@col_exists = 0, "ALTER TABLE livestream_draw_logs ADD COLUMN user_nickname VARCHAR(128) DEFAULT '' COMMENT '用户昵称' AFTER douyin_user_id", 'SELECT 1');
|
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
|
|
-- 3.3 是否已发放奖品
|
|
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'livestream_draw_logs' AND column_name = 'is_granted');
|
|
SET @sql = IF(@col_exists = 0, "ALTER TABLE livestream_draw_logs ADD COLUMN is_granted TINYINT DEFAULT 0 COMMENT '是否已发放奖品' AFTER weights_total", 'SELECT 1');
|
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
|
|
-- 3.4 订单是否已退款
|
|
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'livestream_draw_logs' AND column_name = 'is_refunded');
|
|
SET @sql = IF(@col_exists = 0, "ALTER TABLE livestream_draw_logs ADD COLUMN is_refunded TINYINT DEFAULT 0 COMMENT '订单是否已退款' AFTER is_granted", 'SELECT 1');
|
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
|
|
-- ============================================================
|
|
-- 4. douyin_orders 表
|
|
-- ============================================================
|
|
|
|
-- 4.1 产品ID
|
|
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'douyin_orders' AND column_name = 'douyin_product_id');
|
|
SET @sql = IF(@col_exists = 0, "ALTER TABLE douyin_orders ADD COLUMN douyin_product_id VARCHAR(64) DEFAULT '' COMMENT '关联商品ID' AFTER shop_order_id", 'SELECT 1');
|
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
|
|
-- 4.2 商品数量
|
|
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'douyin_orders' AND column_name = 'product_count');
|
|
SET @sql = IF(@col_exists = 0, "ALTER TABLE douyin_orders ADD COLUMN product_count INT NOT NULL DEFAULT 1 COMMENT '商品数量' AFTER order_status", 'SELECT 1');
|
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
|
|
-- 4.3 已发放次数
|
|
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'douyin_orders' AND column_name = 'reward_granted');
|
|
SET @sql = IF(@col_exists = 0, "ALTER TABLE douyin_orders ADD COLUMN reward_granted INT NOT NULL DEFAULT 0 COMMENT '已发放次数' AFTER product_count", 'SELECT 1');
|
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
|
|
-- 4.4 添加索引
|
|
SET @idx_exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'douyin_orders' AND index_name = 'idx_douyin_product_id');
|
|
SET @sql = IF(@idx_exists = 0, 'CREATE INDEX idx_douyin_product_id ON douyin_orders(douyin_product_id)', 'SELECT 1');
|
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
|
|
-- ============================================================
|
|
-- 5. douyin_blacklist 表
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS `douyin_blacklist` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
|
`douyin_user_id` VARCHAR(64) NOT NULL COMMENT '抖音用户ID',
|
|
`reason` VARCHAR(255) DEFAULT '' COMMENT '拉黑原因',
|
|
`operator_id` BIGINT DEFAULT 0 COMMENT '操作人ID',
|
|
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态: 1=生效, 0=已解除',
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
|
|
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_douyin_user_id` (`douyin_user_id`),
|
|
KEY `idx_status` (`status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='抖音用户黑名单表';
|
|
|
|
-- ============================================================
|
|
-- 6. douyin_product_rewards 表
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS `douyin_product_rewards` (
|
|
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
`product_id` VARCHAR(64) NOT NULL COMMENT '抖店商品ID',
|
|
`product_name` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '商品名称（便于识别）',
|
|
`reward_type` VARCHAR(32) NOT NULL COMMENT '奖励类型: game_ticket/coupon/points/product/item_card/title',
|
|
`reward_payload` JSON COMMENT '奖励参数JSON',
|
|
`quantity` INT NOT NULL DEFAULT 1 COMMENT '发放数量',
|
|
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态: 1=启用 0=禁用',
|
|
`created_at` DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updated_at` DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
|
UNIQUE KEY `uk_product_id` (`product_id`),
|
|
KEY `idx_status` (`status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='抖店商品奖励规则';
|
|
|
|
-- ============================================================
|
|
-- 完成
|
|
-- ============================================================
|
|
SELECT 'Migration completed successfully!' AS status;
|