SET @db_name = DATABASE();
|
|
-- ----------------------------
|
-- 养生课程小节资源表结构升级
|
-- 目标：
|
-- 1. 新增 course_type / video_source_type / video_file_name
|
-- 2. 迁移旧 sale_type 到 sale_price 语义
|
-- 3. 删除 chapter_remark / sale_type / play_count
|
-- 4. 新增 course_type 维度索引
|
-- ----------------------------
|
|
SET @video_table_exists = (
|
SELECT COUNT(1)
|
FROM information_schema.TABLES
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'tcm_health_course_video'
|
);
|
|
SET @sql_assert_video_table = IF(
|
@video_table_exists = 0,
|
'SELECT ''skip: tcm_health_course_video not exists''',
|
'SELECT 1'
|
);
|
PREPARE stmt_assert_video_table FROM @sql_assert_video_table;
|
EXECUTE stmt_assert_video_table;
|
DEALLOCATE PREPARE stmt_assert_video_table;
|
|
SET @has_course_type = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'tcm_health_course_video'
|
AND COLUMN_NAME = 'course_type'
|
);
|
SET @sql_add_course_type = IF(
|
@video_table_exists = 1 AND @has_course_type = 0,
|
'ALTER TABLE `tcm_health_course_video` ADD COLUMN `course_type` TINYINT NOT NULL DEFAULT 1 COMMENT ''课程类型：1养生课程'' AFTER `id`',
|
'SELECT 1'
|
);
|
PREPARE stmt_add_course_type FROM @sql_add_course_type;
|
EXECUTE stmt_add_course_type;
|
DEALLOCATE PREPARE stmt_add_course_type;
|
|
SET @has_video_source_type = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'tcm_health_course_video'
|
AND COLUMN_NAME = 'video_source_type'
|
);
|
SET @sql_add_video_source_type = IF(
|
@video_table_exists = 1 AND @has_video_source_type = 0,
|
'ALTER TABLE `tcm_health_course_video` ADD COLUMN `video_source_type` TINYINT NOT NULL DEFAULT 1 COMMENT ''视频来源：1视频地址 2本地上传'' AFTER `chapter_name`',
|
'SELECT 1'
|
);
|
PREPARE stmt_add_video_source_type FROM @sql_add_video_source_type;
|
EXECUTE stmt_add_video_source_type;
|
DEALLOCATE PREPARE stmt_add_video_source_type;
|
|
SET @has_video_file_name = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'tcm_health_course_video'
|
AND COLUMN_NAME = 'video_file_name'
|
);
|
SET @sql_add_video_file_name = IF(
|
@video_table_exists = 1 AND @has_video_file_name = 0,
|
'ALTER TABLE `tcm_health_course_video` ADD COLUMN `video_file_name` VARCHAR(255) DEFAULT NULL COMMENT ''本地上传原始文件名'' AFTER `video_url`',
|
'SELECT 1'
|
);
|
PREPARE stmt_add_video_file_name FROM @sql_add_video_file_name;
|
EXECUTE stmt_add_video_file_name;
|
DEALLOCATE PREPARE stmt_add_video_file_name;
|
|
SET @has_sale_type = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'tcm_health_course_video'
|
AND COLUMN_NAME = 'sale_type'
|
);
|
|
SET @sql_migrate_sale_price = IF(
|
@video_table_exists = 1 AND @has_sale_type = 1,
|
'UPDATE `tcm_health_course_video`
|
SET `sale_price` = CASE
|
WHEN `sale_type` = 0 THEN 0.00
|
WHEN `sale_price` IS NULL THEN 0.00
|
ELSE `sale_price`
|
END',
|
'SELECT 1'
|
);
|
PREPARE stmt_migrate_sale_price FROM @sql_migrate_sale_price;
|
EXECUTE stmt_migrate_sale_price;
|
DEALLOCATE PREPARE stmt_migrate_sale_price;
|
|
SET @sql_fill_course_type = IF(
|
@video_table_exists = 1,
|
'UPDATE `tcm_health_course_video` SET `course_type` = 1 WHERE `course_type` IS NULL OR `course_type` = 0',
|
'SELECT 1'
|
);
|
PREPARE stmt_fill_course_type FROM @sql_fill_course_type;
|
EXECUTE stmt_fill_course_type;
|
DEALLOCATE PREPARE stmt_fill_course_type;
|
|
SET @sql_fill_video_source_type = IF(
|
@video_table_exists = 1,
|
'UPDATE `tcm_health_course_video` SET `video_source_type` = 1 WHERE `video_source_type` IS NULL OR `video_source_type` = 0',
|
'SELECT 1'
|
);
|
PREPARE stmt_fill_video_source_type FROM @sql_fill_video_source_type;
|
EXECUTE stmt_fill_video_source_type;
|
DEALLOCATE PREPARE stmt_fill_video_source_type;
|
|
SET @has_sale_type_index = (
|
SELECT COUNT(1)
|
FROM information_schema.STATISTICS
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'tcm_health_course_video'
|
AND INDEX_NAME = 'idx_health_course_video_sale_type'
|
);
|
SET @sql_drop_sale_type_index = IF(
|
@video_table_exists = 1 AND @has_sale_type_index = 1,
|
'ALTER TABLE `tcm_health_course_video` DROP INDEX `idx_health_course_video_sale_type`',
|
'SELECT 1'
|
);
|
PREPARE stmt_drop_sale_type_index FROM @sql_drop_sale_type_index;
|
EXECUTE stmt_drop_sale_type_index;
|
DEALLOCATE PREPARE stmt_drop_sale_type_index;
|
|
SET @has_course_type_status_index = (
|
SELECT COUNT(1)
|
FROM information_schema.STATISTICS
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'tcm_health_course_video'
|
AND INDEX_NAME = 'idx_health_course_video_type_status'
|
);
|
SET @sql_add_course_type_status_index = IF(
|
@video_table_exists = 1 AND @has_course_type_status_index = 0,
|
'ALTER TABLE `tcm_health_course_video` ADD INDEX `idx_health_course_video_type_status` (`course_type`, `status`)',
|
'SELECT 1'
|
);
|
PREPARE stmt_add_course_type_status_index FROM @sql_add_course_type_status_index;
|
EXECUTE stmt_add_course_type_status_index;
|
DEALLOCATE PREPARE stmt_add_course_type_status_index;
|
|
SET @has_chapter_remark = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'tcm_health_course_video'
|
AND COLUMN_NAME = 'chapter_remark'
|
);
|
SET @sql_drop_chapter_remark = IF(
|
@video_table_exists = 1 AND @has_chapter_remark = 1,
|
'ALTER TABLE `tcm_health_course_video` DROP COLUMN `chapter_remark`',
|
'SELECT 1'
|
);
|
PREPARE stmt_drop_chapter_remark FROM @sql_drop_chapter_remark;
|
EXECUTE stmt_drop_chapter_remark;
|
DEALLOCATE PREPARE stmt_drop_chapter_remark;
|
|
SET @sql_drop_sale_type_column = IF(
|
@video_table_exists = 1 AND @has_sale_type = 1,
|
'ALTER TABLE `tcm_health_course_video` DROP COLUMN `sale_type`',
|
'SELECT 1'
|
);
|
PREPARE stmt_drop_sale_type_column FROM @sql_drop_sale_type_column;
|
EXECUTE stmt_drop_sale_type_column;
|
DEALLOCATE PREPARE stmt_drop_sale_type_column;
|
|
SET @has_play_count = (
|
SELECT COUNT(1)
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @db_name
|
AND TABLE_NAME = 'tcm_health_course_video'
|
AND COLUMN_NAME = 'play_count'
|
);
|
SET @sql_drop_play_count = IF(
|
@video_table_exists = 1 AND @has_play_count = 1,
|
'ALTER TABLE `tcm_health_course_video` DROP COLUMN `play_count`',
|
'SELECT 1'
|
);
|
PREPARE stmt_drop_play_count FROM @sql_drop_play_count;
|
EXECUTE stmt_drop_play_count;
|
DEALLOCATE PREPARE stmt_drop_play_count;
|
