java110-db/db/CommentService/create_table.sql · javaalpha/MicroCommunity

Fetch the repository succeeded.

Scan WeChat QR to Pay

Cancel

Complete

richgiteeai

Watch

Unwatch Watching Releases Only Ignoring

1 Star 0 Fork 4.3K

javaalpha/MicroCommunity

forked from java110/MicroCommunity

Code Issues 0 Pull Requests 0 Wiki Insights Pipelines

Service

Tencent Cloud Serverless

Create your Gitee Account

Explore and code with more than 14 million developers,Free private repositories !:)

Already have an account? Sign in

文件

master

Branches (15)

Tags (8)

Manage

master

shiyj01

duckweed

jonas

spring-cloud-1.4

shareDb

shiyj

dyaoming

wuxw

product

v0.3

v20190322

test

v0.2

add-license-1

v0.0.8

v0.0.7

v0.0.6

v0.0.5

v0.0.4

v0.0.3

v0.0.2

v0.0.1

Clone or Download

Clone/Download

HTTPS SSH SVN SVN+SSH Download ZIP

Prompt

To download the code, please copy the following command and execute it in the terminal

To ensure that your submitted code identity is correctly recognized by Gitee, please execute the following command.

git config --global user.name userName 
git config --global user.email userEmail

When using the SSH protocol for the first time to clone or push code, follow the prompts below to complete the SSH configuration.

1 Generate RSA keys.

2 Obtain the content of the RSA public key and configure it in SSH Public Keys

To use SVN on Gitee, please visit the usage guide

When using the HTTPS protocol, the command line will prompt for account and password verification as follows. For security reasons, Gitee recommends configure and use personal access tokens instead of login passwords for cloning, pushing, and other operations.

Username for 'https://gitee.com': userName

Password for 'https://userName@gitee.com': # Private Token

Branches 15

Tags 8

MicroCommunity

java110-db

CommentService

create_table.sql

create_table.sql 7.26 KB

-- 评论
create table c_comment(
 comment_id VARCHAR(30) NOT NULL COMMENT '评论ID',
 user_id varchar(30) not null comment '评论者用户ID',
 b_id VARCHAR(30) NOT NULL COMMENT '订单ID',
 comment_type_cd varchar(2) not null default 'S' comment '评论类型 S表示 商品 M表示 商户 T 物流',
 out_id varchar(30) not null comment '外部ID,如商品ID 商户ID等',
 `month` INT NOT NULL COMMENT '月份',
 create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态,详细参考c_status表,S 保存,0, 在用 1失效'
)
PARTITION BY RANGE (`month`) (
 PARTITION comment_1 VALUES LESS THAN (2),
 PARTITION comment_2 VALUES LESS THAN (3),
 PARTITION comment_3 VALUES LESS THAN (4),
 PARTITION comment_4 VALUES LESS THAN (5),
 PARTITION comment_5 VALUES LESS THAN (6),
 PARTITION comment_6 VALUES LESS THAN (7),
 PARTITION comment_7 VALUES LESS THAN (8),
 PARTITION comment_8 VALUES LESS THAN (9),
 PARTITION comment_9 VALUES LESS THAN (10),
 PARTITION comment_10 VALUES LESS THAN (11),
 PARTITION comment_11 VALUES LESS THAN (12),
 PARTITION comment_12 VALUES LESS THAN (13)
);

CREATE INDEX idx_comment_b_id ON c_comment(b_id);
CREATE INDEX idx_comment_out_id ON c_comment(out_id);
-- 评论子表
create table c_sub_comment(
 sub_comment_id varchar(30) not null comment '子评论ID',
 comment_id varchar(30) not null comment '评论ID ',
 b_id VARCHAR(30) NOT NULL COMMENT '订单ID',
 parent_sub_comment_id varchar(30) not null default '-1' comment '父 子评论ID 如果不是回复 写成-1',
 sub_comment_type_cd varchar(2) not null default 'C' comment '评论类型 C 评论 R 回复 A 追加',
 comment_context LONGTEXT not null COMMENT '评论内容',
 `month` INT NOT NULL COMMENT '月份',
 create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态,详细参考c_status表,S 保存,0, 在用 1失效'
)
PARTITION BY RANGE (`month`) (
 PARTITION sub_comment_1 VALUES LESS THAN (2),
 PARTITION sub_comment_2 VALUES LESS THAN (3),
 PARTITION sub_comment_3 VALUES LESS THAN (4),
 PARTITION sub_comment_4 VALUES LESS THAN (5),
 PARTITION sub_comment_5 VALUES LESS THAN (6),
 PARTITION sub_comment_6 VALUES LESS THAN (7),
 PARTITION sub_comment_7 VALUES LESS THAN (8),
 PARTITION sub_comment_8 VALUES LESS THAN (9),
 PARTITION sub_comment_9 VALUES LESS THAN (10),
 PARTITION sub_comment_10 VALUES LESS THAN (11),
 PARTITION sub_comment_11 VALUES LESS THAN (12),
 PARTITION sub_comment_12 VALUES LESS THAN (13)
);

CREATE INDEX idx_sub_comment_b_id ON c_sub_comment(b_id);
CREATE INDEX idx_sub_comment_comment_id ON c_sub_comment(comment_id);
CREATE INDEX idx_sub_comment_parent_sub_comment_id ON c_sub_comment(parent_sub_comment_id);

-- 属性
create table c_sub_comment_attr(
 attr_id VARCHAR(30) NOT NULL COMMENT '属性id',
 sub_comment_id VARCHAR(30) NOT NULL COMMENT '子评论ID',
 b_id VARCHAR(30) NOT NULL COMMENT '订单ID',
 spec_cd VARCHAR(12) NOT NULL COMMENT '规格id,参考spec表',
 value VARCHAR(50) NOT NULL COMMENT '属性值',
 `month` INT NOT NULL COMMENT '月份',
 create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态,详细参考c_status表,S 保存,0, 在用 1失效'
)
PARTITION BY RANGE (`month`) (
 PARTITION sub_comment_attr_1 VALUES LESS THAN (2),
 PARTITION sub_comment_attr_2 VALUES LESS THAN (3),
 PARTITION sub_comment_attr_3 VALUES LESS THAN (4),
 PARTITION sub_comment_attr_4 VALUES LESS THAN (5),
 PARTITION sub_comment_attr_5 VALUES LESS THAN (6),
 PARTITION sub_comment_attr_6 VALUES LESS THAN (7),
 PARTITION sub_comment_attr_7 VALUES LESS THAN (8),
 PARTITION sub_comment_attr_8 VALUES LESS THAN (9),
 PARTITION sub_comment_attr_9 VALUES LESS THAN (10),
 PARTITION sub_comment_attr_10 VALUES LESS THAN (11),
 PARTITION sub_comment_attr_11 VALUES LESS THAN (12),
 PARTITION sub_comment_attr_12 VALUES LESS THAN (13)
);

CREATE INDEX idx_sub_comment_attr_b_id ON c_sub_comment_attr(b_id);
CREATE INDEX idx_sub_comment_attr_sub_comment_id ON c_sub_comment_attr(sub_comment_id);
CREATE INDEX idx_sub_comment_attr_spec_cd ON c_sub_comment_attr(spec_cd);

-- 评论 照片
CREATE TABLE c_sub_comment_photo(
 comment_photo_id VARCHAR(30) NOT NULL COMMENT '评论照片ID',
 b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
 sub_comment_id VARCHAR(30) NOT NULL COMMENT '商店ID',
 comment_photo_type_cd VARCHAR(2) NOT NULL default 'S' COMMENT '评论照片类型,S 商品照片 M 商户ID',
 photo VARCHAR(100) NOT NULL COMMENT '照片',
 `month` INT NOT NULL COMMENT '月份',
 create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态,详细参考c_status表,S 保存,0, 在用 1失效'
)
PARTITION BY RANGE (`month`) (
 PARTITION sub_comment_photo_1 VALUES LESS THAN (2),
 PARTITION sub_comment_photo_2 VALUES LESS THAN (3),
 PARTITION sub_comment_photo_3 VALUES LESS THAN (4),
 PARTITION sub_comment_photo_4 VALUES LESS THAN (5),
 PARTITION sub_comment_photo_5 VALUES LESS THAN (6),
 PARTITION sub_comment_photo_6 VALUES LESS THAN (7),
 PARTITION sub_comment_photo_7 VALUES LESS THAN (8),
 PARTITION sub_comment_photo_8 VALUES LESS THAN (9),
 PARTITION sub_comment_photo_9 VALUES LESS THAN (10),
 PARTITION sub_comment_photo_10 VALUES LESS THAN (11),
 PARTITION sub_comment_photo_11 VALUES LESS THAN (12),
 PARTITION sub_comment_photo_12 VALUES LESS THAN (13)
);
CREATE INDEX idx_sub_comment_photo_b_id ON c_sub_comment_photo(b_id);
CREATE INDEX idx_sub_comment_photo_sub_comment_id ON c_sub_comment_photo(sub_comment_id);
-- 评论分数
CREATE TABLE c_comment_score(
 comment_score_id VARCHAR(30) NOT NULL COMMENT '评论分数ID',
 comment_id VARCHAR(30) NOT NULL COMMENT '评论ID',
 b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
 score_type_cd VARCHAR(2) NOT NULL COMMENT '打分类别,S 商品相符,U 卖家打分,T 物流打分',
 `value` INT NOT NULL COMMENT '分数',
 `month` INT NOT NULL COMMENT '月份',
 create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态,详细参考c_status表,S 保存,0, 在用 1失效'
)
PARTITION BY RANGE (`month`) (
 PARTITION comment_score_1 VALUES LESS THAN (2),
 PARTITION comment_score_2 VALUES LESS THAN (3),
 PARTITION comment_score_3 VALUES LESS THAN (4),
 PARTITION comment_score_4 VALUES LESS THAN (5),
 PARTITION comment_score_5 VALUES LESS THAN (6),
 PARTITION comment_score_6 VALUES LESS THAN (7),
 PARTITION comment_score_7 VALUES LESS THAN (8),
 PARTITION comment_score_8 VALUES LESS THAN (9),
 PARTITION comment_score_9 VALUES LESS THAN (10),
 PARTITION comment_score_10 VALUES LESS THAN (11),
 PARTITION comment_score_11 VALUES LESS THAN (12),
 PARTITION comment_score_12 VALUES LESS THAN (13)
);

CREATE INDEX idx_comment_score_b_id ON c_comment_score(b_id);
CREATE INDEX idx_comment_score_comment_id ON c_comment_score(comment_id);

Copy Edit Raw Blame History

java110 authored 2019年05月13日 18:01 +08:00 . 加入分片功能呢

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146

-- 评论
create table c_comment(
 comment_id VARCHAR(30) NOT NULL COMMENT '评论ID',
 user_id varchar(30) not null comment '评论者用户ID',
 b_id VARCHAR(30) NOT NULL COMMENT '订单ID',
 comment_type_cd varchar(2) not null default 'S' comment '评论类型 S表示 商品 M表示 商户 T 物流',
 out_id varchar(30) not null comment '外部ID,如商品ID 商户ID等',
 `month` INT NOT NULL COMMENT '月份',
 create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态,详细参考c_status表,S 保存,0, 在用 1失效'
)
PARTITION BY RANGE (`month`) (
 PARTITION comment_1 VALUES LESS THAN (2),
 PARTITION comment_2 VALUES LESS THAN (3),
 PARTITION comment_3 VALUES LESS THAN (4),
 PARTITION comment_4 VALUES LESS THAN (5),
 PARTITION comment_5 VALUES LESS THAN (6),
 PARTITION comment_6 VALUES LESS THAN (7),
 PARTITION comment_7 VALUES LESS THAN (8),
 PARTITION comment_8 VALUES LESS THAN (9),
 PARTITION comment_9 VALUES LESS THAN (10),
 PARTITION comment_10 VALUES LESS THAN (11),
 PARTITION comment_11 VALUES LESS THAN (12),
 PARTITION comment_12 VALUES LESS THAN (13)
);

CREATE INDEX idx_comment_b_id ON c_comment(b_id);
CREATE INDEX idx_comment_out_id ON c_comment(out_id);
-- 评论子表
create table c_sub_comment(
 sub_comment_id varchar(30) not null comment '子评论ID',
 comment_id varchar(30) not null comment '评论ID ',
 b_id VARCHAR(30) NOT NULL COMMENT '订单ID',
 parent_sub_comment_id varchar(30) not null default '-1' comment '父 子评论ID 如果不是回复 写成-1',
 sub_comment_type_cd varchar(2) not null default 'C' comment '评论类型 C 评论 R 回复 A 追加',
 comment_context LONGTEXT not null COMMENT '评论内容',
 `month` INT NOT NULL COMMENT '月份',
 create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态,详细参考c_status表,S 保存,0, 在用 1失效'
)
PARTITION BY RANGE (`month`) (
 PARTITION sub_comment_1 VALUES LESS THAN (2),
 PARTITION sub_comment_2 VALUES LESS THAN (3),
 PARTITION sub_comment_3 VALUES LESS THAN (4),
 PARTITION sub_comment_4 VALUES LESS THAN (5),
 PARTITION sub_comment_5 VALUES LESS THAN (6),
 PARTITION sub_comment_6 VALUES LESS THAN (7),
 PARTITION sub_comment_7 VALUES LESS THAN (8),
 PARTITION sub_comment_8 VALUES LESS THAN (9),
 PARTITION sub_comment_9 VALUES LESS THAN (10),
 PARTITION sub_comment_10 VALUES LESS THAN (11),
 PARTITION sub_comment_11 VALUES LESS THAN (12),
 PARTITION sub_comment_12 VALUES LESS THAN (13)
);

CREATE INDEX idx_sub_comment_b_id ON c_sub_comment(b_id);
CREATE INDEX idx_sub_comment_comment_id ON c_sub_comment(comment_id);
CREATE INDEX idx_sub_comment_parent_sub_comment_id ON c_sub_comment(parent_sub_comment_id);

-- 属性
create table c_sub_comment_attr(
 attr_id VARCHAR(30) NOT NULL COMMENT '属性id',
 sub_comment_id VARCHAR(30) NOT NULL COMMENT '子评论ID',
 b_id VARCHAR(30) NOT NULL COMMENT '订单ID',
 spec_cd VARCHAR(12) NOT NULL COMMENT '规格id,参考spec表',
 value VARCHAR(50) NOT NULL COMMENT '属性值',
 `month` INT NOT NULL COMMENT '月份',
 create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态,详细参考c_status表,S 保存,0, 在用 1失效'
)
PARTITION BY RANGE (`month`) (
 PARTITION sub_comment_attr_1 VALUES LESS THAN (2),
 PARTITION sub_comment_attr_2 VALUES LESS THAN (3),
 PARTITION sub_comment_attr_3 VALUES LESS THAN (4),
 PARTITION sub_comment_attr_4 VALUES LESS THAN (5),
 PARTITION sub_comment_attr_5 VALUES LESS THAN (6),
 PARTITION sub_comment_attr_6 VALUES LESS THAN (7),
 PARTITION sub_comment_attr_7 VALUES LESS THAN (8),
 PARTITION sub_comment_attr_8 VALUES LESS THAN (9),
 PARTITION sub_comment_attr_9 VALUES LESS THAN (10),
 PARTITION sub_comment_attr_10 VALUES LESS THAN (11),
 PARTITION sub_comment_attr_11 VALUES LESS THAN (12),
 PARTITION sub_comment_attr_12 VALUES LESS THAN (13)
);

CREATE INDEX idx_sub_comment_attr_b_id ON c_sub_comment_attr(b_id);
CREATE INDEX idx_sub_comment_attr_sub_comment_id ON c_sub_comment_attr(sub_comment_id);
CREATE INDEX idx_sub_comment_attr_spec_cd ON c_sub_comment_attr(spec_cd);



-- 评论 照片
CREATE TABLE c_sub_comment_photo(
 comment_photo_id VARCHAR(30) NOT NULL COMMENT '评论照片ID',
 b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
 sub_comment_id VARCHAR(30) NOT NULL COMMENT '商店ID',
 comment_photo_type_cd VARCHAR(2) NOT NULL default 'S' COMMENT '评论照片类型,S 商品照片 M 商户ID',
 photo VARCHAR(100) NOT NULL COMMENT '照片',
 `month` INT NOT NULL COMMENT '月份',
 create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态,详细参考c_status表,S 保存,0, 在用 1失效'
)
PARTITION BY RANGE (`month`) (
 PARTITION sub_comment_photo_1 VALUES LESS THAN (2),
 PARTITION sub_comment_photo_2 VALUES LESS THAN (3),
 PARTITION sub_comment_photo_3 VALUES LESS THAN (4),
 PARTITION sub_comment_photo_4 VALUES LESS THAN (5),
 PARTITION sub_comment_photo_5 VALUES LESS THAN (6),
 PARTITION sub_comment_photo_6 VALUES LESS THAN (7),
 PARTITION sub_comment_photo_7 VALUES LESS THAN (8),
 PARTITION sub_comment_photo_8 VALUES LESS THAN (9),
 PARTITION sub_comment_photo_9 VALUES LESS THAN (10),
 PARTITION sub_comment_photo_10 VALUES LESS THAN (11),
 PARTITION sub_comment_photo_11 VALUES LESS THAN (12),
 PARTITION sub_comment_photo_12 VALUES LESS THAN (13)
);
CREATE INDEX idx_sub_comment_photo_b_id ON c_sub_comment_photo(b_id);
CREATE INDEX idx_sub_comment_photo_sub_comment_id ON c_sub_comment_photo(sub_comment_id);
-- 评论分数
CREATE TABLE c_comment_score(
 comment_score_id VARCHAR(30) NOT NULL COMMENT '评论分数ID',
 comment_id VARCHAR(30) NOT NULL COMMENT '评论ID',
 b_id VARCHAR(30) NOT NULL COMMENT '业务Id',
 score_type_cd VARCHAR(2) NOT NULL COMMENT '打分类别,S 商品相符,U 卖家打分,T 物流打分',
 `value` INT NOT NULL COMMENT '分数',
 `month` INT NOT NULL COMMENT '月份',
 create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 status_cd VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '数据状态,详细参考c_status表,S 保存,0, 在用 1失效'
)
PARTITION BY RANGE (`month`) (
 PARTITION comment_score_1 VALUES LESS THAN (2),
 PARTITION comment_score_2 VALUES LESS THAN (3),
 PARTITION comment_score_3 VALUES LESS THAN (4),
 PARTITION comment_score_4 VALUES LESS THAN (5),
 PARTITION comment_score_5 VALUES LESS THAN (6),
 PARTITION comment_score_6 VALUES LESS THAN (7),
 PARTITION comment_score_7 VALUES LESS THAN (8),
 PARTITION comment_score_8 VALUES LESS THAN (9),
 PARTITION comment_score_9 VALUES LESS THAN (10),
 PARTITION comment_score_10 VALUES LESS THAN (11),
 PARTITION comment_score_11 VALUES LESS THAN (12),
 PARTITION comment_score_12 VALUES LESS THAN (13)
);

CREATE INDEX idx_comment_score_b_id ON c_comment_score(b_id);
CREATE INDEX idx_comment_score_comment_id ON c_comment_score(comment_id);