《MySQL性能调优与架构设计》示例库结构脚本
示例库结构脚本的所有创建语句：
–创建数据库
Create DATABASE example;
–创建表
–索引是根据应用中使用的 Query 的情况而决定，所有表开始都仅仅只有主键，没有其他的索引
–各表没有特定存储引擎和字符集，各位读者朋友可根据自己的环境决定
Create TABLE event (
id int(11) NOT NULL AUTO_INCREMENT,
gmt_create datetime NOT NULL,
user_id int(11) NOT NULL,
event_type varchar(8) NOT NULL,
event_msg varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
);
–组内讨论信息summary
Create TABLE group_message (
id int(11) NOT NULL AUTO_INCREMENT,
gmt_create datetime NOT NULL,
gmt_modified datetime NOT NULL,
group_id int(11) NOT NULL,
user_id int(11) NOT NULL,
author varchar(32) NOT NULL,
subject varchar(128) NOT NULL,
PRIMARY KEY (id)
);
–组内讨论信息内容
Create TABLE group_message_content (
group_msg_id int(11) NOT NULL,
gmt_create datetime NOT NULL,
gmt_modified datetime NOT NULL,
content text NOT NULL,
PRIMARY KEY (group_msg_id)
);
–组信息
Create TABLE groups (
id int(11) NOT NULL AUTO_INCREMENT,
gmt_create datetime NOT NULL,
gmt_modified datetime NOT NULL,
name varchar(32) NOT NULL,
status varchar(16) NOT NULL,
description varchar(1024) DEFAULT NULL,
PRIMARY KEY (id)
);
–特殊消息
Create TABLE message (
id int(11) NOT NULL AUTO_INCREMENT,
gmt_create datetime NOT NULL,
gmt_modified datetime NOT NULL,
subject varchar(64) NOT NULL,
status varchar(16) NOT NULL,
PRIMARY KEY (id)
);
–消息
Create TABLE message_content (
msg_id int(11) NOT NULL,
gmt_create datetime NOT NULL,
gmt_modified datetime NOT NULL,
content varchar(512) DEFAULT NULL,
PRIMARY KEY (msg_id)
);
–照片表
Create TABLE photo (
id int(11) NOT NULL AUTO_INCREMENT,
gmt_create datetime NOT NULL,
gmt_modified datetime NOT NULL,
user_id int(11) NOT NULL,
status varchar(16) NOT NULL,
subject varchar(64) NOT NULL,
url varchar(64) NOT NULL,
PRIMARY KEY (id)
);
–相册表
Create TABLE photo_album (
id int(11) NOT NULL AUTO_INCREMENT,
gmt_create datetime NOT NULL,
gmt_modified datetime NOT NULL,
album_name varchar(64) NOT NULL,
user_id int(11) NOT NULL,
PRIMARY KEY (id)
);
–照片相册关系表
Create TABLE photo_album_relation (
id int(11) NOT NULL auto_increment,
gmt_create datetime NOT NULL,
gmt_modified datetime NOT NULL,
album_id int(11) NOT NULL,
photo_id int(11) NOT NULL,
PRIMARY KEY (id)
);
–照片回复信息表
Create TABLE photo_comment (
id int(11) NOT NULL AUTO_INCREMENT,
gmt_create datetime NOT NULL,
gmt_modified datetime NOT NULL,
photo_id int(11) NOT NULL,
comments varchar(512) NOT NULL,
PRIMARY KEY (id)
);
–系统各组广播消息表
Create TABLE top_message (
id int(11) NOT NULL AUTO_INCREMENT,
gmt_create datetime NOT NULL,
gmt_modified datetime NOT NULL,
user_id int(11) NOT NULL,
author varchar(32) NOT NULL,
subject varchar(128) NOT NULL,
PRIMARY KEY (id)
);
–用户基本信息表
Create TABLE user (
id int(11) NOT NULL AUTO_INCREMENT,
gmt_create datetime NOT NULL,
gmt_modified datetime NOT NULL,
nick_name varchar(32) NOT NULL,
password char(64) DEFAULT NULL,
email_b varchar(32) DEFAULT NULL,
status varchar(16) DEFAULT NULL,
PRIMARY KEY (id)
);
–用户个人详细信息表
Create TABLE user_profile (
user_id int(11) NOT NULL,
gmt_create datetime NOT NULL,
gmt_modified datetime NOT NULL,
sexuality char(1) NOT NULL,
msn varchar(32) DEFAULT NULL,
sign varchar(64) DEFAULT NULL,
birthday date DEFAULT NULL,
hobby varchar(64) DEFAULT NULL,
location varchar(64) DEFAULT NULL,
description varchar(1024) DEFAULT NULL,
PRIMARY KEY (user_id)
);
–用户分组表
Create TABLE user_group (
user_id int(11) NOT NULL,
gmt_create datetime NOT NULL,
gmt_modified datetime NOT NULL,
group_id int(11) NOT NULL,
user_type int(11) NOT NULL,
status varchar(16) NOT NULL,
PRIMARY KEY (user_id,group_id)
);
