You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
96 lines
5.5 KiB
96 lines
5.5 KiB
#初始table |
|
CREATE TABLE `up_rank` ( |
|
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID', |
|
`mid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'up主id', |
|
`type` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '排行榜类型', |
|
`value` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '排行榜数值,根据type不同，代表的含义不同', |
|
`generate_date` date NOT NULL DEFAULT '0000-00-00' COMMENT '排行榜日', |
|
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', |
|
`mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间', |
|
`value2` int(11) NOT NULL DEFAULT '0' COMMENT '分数2', |
|
PRIMARY KEY (`id`), |
|
UNIQUE KEY `uk_type_date` (`generate_date`,`type`), |
|
KEY `ix_mtime` (`mtime`) |
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Up蹿升榜' |
|
|
|
|
|
# fat uat表增加字段 |
|
# 增加默认值 |
|
alter table up_base_info alter column active_tid set default 0; |
|
alter table up_base_info alter column attr set default 0; |
|
alter table up_base_info alter column mid set default 0; |
|
alter table up_base_info alter column mid set default 0; |
|
alter table up_play_info alter column mid set default 0; |
|
alter table up_play_info alter column business_type set default 0; |
|
alter table up_play_info alter column article_count set default 0; |
|
alter table up_play_info alter column play_count_90day set default 0; |
|
alter table up_play_info alter column play_count_7day set default 0; |
|
alter table up_play_info alter column play_count_30day set default 0; |
|
alter table up_play_info alter column play_count_accumulate set default 0; |
|
|
|
alter table up_stats_history alter column type set default 0; |
|
alter table up_stats_history alter column sub_type set default 0; |
|
alter table up_stats_history alter column generate_date set default '0000-00-00'; |
|
|
|
alter table up_rank alter column mid set default 0; |
|
alter table up_rank alter column type set default 0; |
|
alter table up_rank alter column value set default 0; |
|
alter table up_rank alter column generate_date set default '0000-00-00'; |
|
|
|
alter table task_info alter column generate_date set default '0000-00-00'; |
|
alter table task_info alter column task_type set default 0; |
|
|
|
alter table up_base_info add column active_tid smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '最多稿件分区'; |
|
alter table up_base_info add column attr int(11) NOT NULL COMMENT '属性，以位区分'; |
|
alter table up_rank add column value2 int(11) NOT NULL DEFAULT 0 COMMENT '分数2'; |
|
ALTER TABLE up_base_info MODIFY active_tid smallint(6) unsigned NOT NULL DEFAULT 0 COMMENT '最多稿件分区'; |
|
|
|
#FAT |
|
alter table up_play_info drop column play_count_avg; |
|
alter table up_play_info drop column play_count_avg_90day; |
|
alter table up_play_info add column `article_count` int(11) NOT NULL DEFAULT '0' COMMENT '总稿件数'; |
|
alter table up_play_info add column `play_count_90day` int(11) NOT NULL DEFAULT '0' COMMENT '90天内稿件总播放次数'; |
|
alter table up_play_info add column `play_count_30day` int(11) NOT NULL DEFAULT '0' COMMENT '30天内稿件总播放次数'; |
|
alter table up_play_info add column `play_count_7day` int(11) NOT NULL DEFAULT '0' COMMENT '7天内稿件总播放次数'; |
|
|
|
#FAT UAT |
|
DROP INDEX ix_mid ON up_base_info; |
|
alter table up_base_info add unique key uk_mid_type (`mid`,`business_type`); |
|
|
|
#每天一条记录 增加分数段表， (uat 1, fat 1, prod 1) |
|
create table score_section_history( |
|
id int(11) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', |
|
generate_date date NOT NULL COMMENT '生成日期', |
|
score_type SMALLINT(6) NOT NULL DEFAULT 0 COMMENT '类型, 1质量分，2影响分，3信用分', |
|
section_0 int(11) NOT NULL DEFAULT 0 COMMENT '0~100的人数', |
|
section_1 int(11) NOT NULL DEFAULT 0 COMMENT '101~200', |
|
section_2 int(11) NOT NULL DEFAULT 0 COMMENT '201~300', |
|
section_3 int(11) NOT NULL DEFAULT 0 COMMENT '301~400', |
|
section_4 int(11) NOT NULL DEFAULT 0 COMMENT '401~500', |
|
section_5 int(11) NOT NULL DEFAULT 0 COMMENT '501~600', |
|
section_6 int(11) NOT NULL DEFAULT 0 COMMENT '601~700', |
|
section_7 int(11) NOT NULL DEFAULT 0 COMMENT '701~800', |
|
section_8 int(11) NOT NULL DEFAULT 0 COMMENT '801~900', |
|
section_9 int(11) NOT NULL DEFAULT 0 COMMENT '901~1000', |
|
ctime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', |
|
mtime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间', |
|
UNIQUE uk_date_type(generate_date, score_type), |
|
KEY ix_mtime (mtime) |
|
) engine=innodb DEFAULT charset=utf8 comment='up分数段人数分布表'; |
|
|
|
#增加信用分、影响分、质量分字段 (uat 1, fat 1, prod 1) |
|
alter table up_base_info add COLUMN credit_score INT NOT NULL DEFAULT 500 COMMENT '信用分'; |
|
alter table up_base_info add COLUMN pr_score INT NOT NULL DEFAULT 0 COMMENT '影响分'; |
|
alter table up_base_info add COLUMN quality_score INT NOT NULL DEFAULT 0 COMMENT '质量分'; |
|
|
|
#修复key错误 (uat 1, fat 1, prod 1) |
|
DROP INDEX uk_type_date ON up_rank; |
|
alter table up_rank add unique key uk_date_type_mid (`generate_date`,`type`, `mid`); |
|
|
|
#增加生日、地域等字段 (uat 1, fat 1, prod 1) |
|
alter table up_base_info add COLUMN birthday DATE NOT NULL DEFAULT '0000-00-00' COMMENT '生日'; |
|
alter table up_base_info add COLUMN active_province varchar(32) NOT NULL DEFAULT '' COMMENT '省份'; |
|
alter table up_base_info add COLUMN active_city varchar(32) NOT NULL DEFAULT '' COMMENT '城市'; |
|
|
|
#增加task info的unique key(uat 1, fat 1, prod 1) |
|
alter table task_info add unique key uk_date_type (`generate_date`,`task_type`); |