— ====================================================================
— bchat 聊天室数据表创建脚本 (UTF-8版本)
— 适用于已存在的数据库 chat_shimen8
— ====================================================================
USE chat_shimen8;
— 设置会话字符集
SET NAMES utf8mb4;
SET CHARACTER_SET_CLIENT = utf8mb4;
SET CHARACTER_SET_CONNECTION = utf8mb4;
SET CHARACTER_SET_RESULTS = utf8mb4;
— ====================================================================
— 1. room 表 – 聊天室信息表
— ====================================================================
DROP TABLE IF EXISTS room;
CREATE TABLE room (
port BIGINT NOT NULL COMMENT ‘端口号’,
roomname VARCHAR(100) DEFAULT NULL COMMENT ‘聊天室名称’,
pid VARCHAR(20) DEFAULT NULL COMMENT ‘进程ID’,
num INT DEFAULT 0 COMMENT ‘当前在线人数’,
running CHAR(1) DEFAULT ‘0’ COMMENT ‘运行状态: 0=停止, 1=运行’,
type CHAR(1) DEFAULT ‘N’ COMMENT ‘房间类型’,
host VARCHAR(200) DEFAULT NULL COMMENT ‘主机地址’,
point BIGINT DEFAULT 0 COMMENT ‘房间积分’,
gifttax BIGINT DEFAULT 0 COMMENT ‘礼物税收’,
maxnum INT UNSIGNED DEFAULT 100 COMMENT ‘最大在线人数’,
bodycolor VARCHAR(20) DEFAULT NULL COMMENT ‘主体背景色’,
bodyurl VARCHAR(255) DEFAULT NULL COMMENT ‘主体背景图片URL’,
bottomcolor VARCHAR(20) DEFAULT NULL COMMENT ‘底部背景色’,
bottomurl VARCHAR(255) DEFAULT NULL COMMENT ‘底部背景图片URL’,
topcolor VARCHAR(20) DEFAULT NULL COMMENT ‘顶部背景色’,
adnote VARCHAR(500) DEFAULT NULL COMMENT ‘广告公告’,
scripturl VARCHAR(500) DEFAULT NULL COMMENT ‘脚本URL’,
leaveurl VARCHAR(255) DEFAULT NULL COMMENT ‘离开URL’,
fullurl VARCHAR(255) DEFAULT NULL COMMENT ‘满员跳转URL’,
unauthurl VARCHAR(255) DEFAULT NULL COMMENT ‘未授权跳转URL’,
setadm CHAR(1) DEFAULT ‘1’ COMMENT ‘允许设置管理员: 0=否, 1=是’,
setimg CHAR(1) DEFAULT ‘1’ COMMENT ‘允许发送图片: 0=否, 1=是’,
setdoor CHAR(1) DEFAULT ‘1’ COMMENT ‘允许关闭房间: 0=否, 1=是’,
setsex CHAR(1) DEFAULT ‘0’ COMMENT ‘显示性别: 0=否, 1=是’,
admintor VARCHAR(2000) DEFAULT NULL COMMENT ‘管理员列表’,
channels VARCHAR(2) DEFAULT ‘1’ COMMENT ‘频道数’,
adminpasswd VARCHAR(100) DEFAULT NULL COMMENT ‘管理密码’,
rtype CHAR(1) DEFAULT ‘N’ COMMENT ‘房间类型标识’,
keyfile VARCHAR(100) DEFAULT NULL COMMENT ‘密钥文件路径’,
bindserial VARCHAR(50) DEFAULT NULL COMMENT ‘绑定序列号’,
voicemode VARCHAR(20) DEFAULT ‘HIGH’ COMMENT ‘语音模式’,
voicesamples VARCHAR(10) DEFAULT ‘8’ COMMENT ‘语音采样率’,
videorate VARCHAR(10) DEFAULT ’28’ COMMENT ‘视频码率’,
videoframerate VARCHAR(10) DEFAULT ‘6’ COMMENT ‘视频帧率’,
videonum CHAR(2) DEFAULT ‘9’ COMMENT ‘视频窗口数’,
voicenum CHAR(2) DEFAULT ‘2’ COMMENT ‘语音通道数’,
headurl VARCHAR(255) DEFAULT NULL COMMENT ‘头部URL’,
lefturl VARCHAR(255) DEFAULT NULL COMMENT ‘左侧URL’,
righturl VARCHAR(255) DEFAULT NULL COMMENT ‘右侧URL’,
footurl VARCHAR(255) DEFAULT NULL COMMENT ‘底部URL’,
headheight VARCHAR(10) DEFAULT NULL COMMENT ‘头部高度’,
leftwidth VARCHAR(10) DEFAULT NULL COMMENT ‘左侧宽度’,
rightwidth VARCHAR(10) DEFAULT NULL COMMENT ‘右侧宽度’,
footheight VARCHAR(10) DEFAULT NULL COMMENT ‘底部高度’,
welcomemsg VARCHAR(500) DEFAULT NULL COMMENT ‘欢迎消息’,
usertable VARCHAR(100) DEFAULT NULL COMMENT ‘用户表名’,
adminwebport VARCHAR(10) DEFAULT NULL COMMENT ‘管理端口’,
templatedir VARCHAR(500) DEFAULT ” COMMENT ‘模板目录’,
needauth CHAR(1) DEFAULT ‘2’ COMMENT ‘需要认证: 0=否, 1=是, 2=可选’,
extconfig VARCHAR(1000) DEFAULT NULL COMMENT ‘扩展配置’,
admincount INT UNSIGNED DEFAULT 0 COMMENT ‘管理员数量’,
outdoorcount INT UNSIGNED DEFAULT 0 COMMENT ‘室外人数’,
doorclosed INT UNSIGNED DEFAULT 0 COMMENT ‘关门状态’,
onlineadmin VARCHAR(1000) DEFAULT NULL COMMENT ‘在线管理员列表’,
PRIMARY KEY (port),
INDEX idx_roomname (roomname),
INDEX idx_running (running),
INDEX idx_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’聊天室信息表’;
— ====================================================================
— 2. user 表 – 用户信息表
— ====================================================================
DROP TABLE IF EXISTS user;
CREATE TABLE user (
name VARCHAR(63) NOT NULL COMMENT ‘用户名’,
escname VARCHAR(189) DEFAULT NULL COMMENT ‘转义用户名’,
nickname VARCHAR(63) DEFAULT NULL COMMENT ‘昵称’,
passwd VARCHAR(50) DEFAULT NULL COMMENT ‘密码’,
lasttime BIGINT UNSIGNED DEFAULT 0 COMMENT ‘最后登录时间’,
exps BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT ‘经验值’,
email VARCHAR(100) DEFAULT NULL COMMENT ‘邮箱’,
sex INT NOT NULL DEFAULT 0 COMMENT ‘性别: 0=保密, 1=男, 2=女’,
icon BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT ‘头像编号’,
extinfo VARCHAR(255) DEFAULT NULL COMMENT ‘扩展信息’,
videopoint BIGINT DEFAULT 0 COMMENT ‘视频积分’,
userpoint BIGINT DEFAULT 0 COMMENT ‘用户积分’,
admintime BIGINT DEFAULT 0 COMMENT ‘管理时长’,
giftpoint BIGINT DEFAULT 0 COMMENT ‘礼物积分’,
videopointmonthly INT DEFAULT 0 COMMENT ‘月度视频积分’,
userpointmonthly INT DEFAULT 0 COMMENT ‘月度用户积分’,
viplevel BIGINT UNSIGNED DEFAULT 0 COMMENT ‘VIP等级’,
iconurl VARCHAR(255) DEFAULT NULL COMMENT ‘头像URL’,
userlock BIGINT UNSIGNED DEFAULT 0 COMMENT ‘用户锁定状态’,
PRIMARY KEY (name),
INDEX idx_nickname (nickname),
INDEX idx_email (email),
INDEX idx_lasttime (lasttime),
INDEX idx_exps (exps),
INDEX idx_viplevel (viplevel)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’用户信息表’;
— ====================================================================
— 3. screenip 表 – IP屏蔽表
— ====================================================================
DROP TABLE IF EXISTS screenip;
CREATE TABLE screenip (
ip VARCHAR(45) NOT NULL COMMENT ‘IP地址(支持IPv6)’,
type CHAR(1) DEFAULT ‘B’ COMMENT ‘屏蔽类型: B=黑名单, W=白名单’,
no INT DEFAULT 0 COMMENT ‘编号’,
PRIMARY KEY (ip),
INDEX idx_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’IP屏蔽表’;
— ====================================================================
— 4. admindetail 表 – 管理员操作明细表
— ====================================================================
DROP TABLE IF EXISTS admindetail;
CREATE TABLE admindetail (
no BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT ‘自增主键’,
logtime BIGINT UNSIGNED DEFAULT 0 COMMENT ‘日志时间’,
name VARCHAR(63) DEFAULT NULL COMMENT ‘管理员用户名’,
admintime INT UNSIGNED DEFAULT 0 COMMENT ‘管理时长(秒)’,
roomname VARCHAR(100) DEFAULT NULL COMMENT ‘房间名称’,
INDEX idx_logtime (logtime),
INDEX idx_name (name),
INDEX idx_roomname (roomname)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’管理员操作明细表’;
— ====================================================================
— 5. giftdetail 表 – 礼物赠送明细表
— ====================================================================
DROP TABLE IF EXISTS giftdetail;
CREATE TABLE giftdetail (
no BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT ‘自增主键’,
sendtime BIGINT UNSIGNED DEFAULT 0 COMMENT ‘发送时间’,
sender VARCHAR(63) DEFAULT NULL COMMENT ‘发送者用户名’,
receiver VARCHAR(63) DEFAULT NULL COMMENT ‘接收者用户名’,
points INT DEFAULT 0 COMMENT ‘礼物积分’,
giftno INT UNSIGNED DEFAULT 0 COMMENT ‘礼物编号’,
giftnum INT UNSIGNED DEFAULT 1 COMMENT ‘礼物数量’,
roomname VARCHAR(100) DEFAULT NULL COMMENT ‘房间名称’,
INDEX idx_sendtime (sendtime),
INDEX idx_sender (sender),
INDEX idx_receiver (receiver),
INDEX idx_roomname (roomname)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’礼物赠送明细表’;
— ====================================================================
— 6. userdetail 表 – 用户积分明细表
— ====================================================================
DROP TABLE IF EXISTS userdetail;
CREATE TABLE userdetail (
no BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT ‘自增主键’,
logtime BIGINT UNSIGNED DEFAULT 0 COMMENT ‘日志时间’,
name VARCHAR(63) DEFAULT NULL COMMENT ‘用户名’,
points INT UNSIGNED DEFAULT 0 COMMENT ‘积分变动’,
roomname VARCHAR(100) DEFAULT NULL COMMENT ‘房间名称’,
INDEX idx_logtime (logtime),
INDEX idx_name (name),
INDEX idx_roomname (roomname)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’用户积分明细表’;
— ====================================================================
— 7. videodetail 表 – 视频观看明细表
— ====================================================================
DROP TABLE IF EXISTS videodetail;
CREATE TABLE videodetail (
no BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT ‘自增主键’,
endtime BIGINT UNSIGNED DEFAULT 0 COMMENT ‘结束时间’,
watcher VARCHAR(63) DEFAULT NULL COMMENT ‘观看者用户名’,
watched VARCHAR(63) DEFAULT NULL COMMENT ‘被观看者用户名’,
points INT UNSIGNED DEFAULT 0 COMMENT ‘消耗积分’,
roomname VARCHAR(100) DEFAULT NULL COMMENT ‘房间名称’,
INDEX idx_endtime (endtime),
INDEX idx_watcher (watcher),
INDEX idx_watched (watched),
INDEX idx_roomname (roomname)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’视频观看明细表’;
— ====================================================================
— 8. globaladmin 表 – 全局管理员表
— ====================================================================
DROP TABLE IF EXISTS globaladmin;
CREATE TABLE globaladmin (
no BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT ‘自增主键’,
globaladmintor VARCHAR(4000) DEFAULT NULL COMMENT ‘全局管理员列表’
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’全局管理员表’;
— 插入默认全局管理员
INSERT INTO globaladmin (globaladmintor) VALUES (‘*’);
— ====================================================================
— 9. onlineuser 表 – 在线用户表
— ====================================================================
DROP TABLE IF EXISTS onlineuser;
CREATE TABLE onlineuser (
no BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT ‘自增主键’,
name VARCHAR(63) DEFAULT NULL COMMENT ‘用户名’,
host VARCHAR(200) DEFAULT NULL COMMENT ‘主机地址’,
port BIGINT DEFAULT NULL COMMENT ‘端口号’,
roomname VARCHAR(100) DEFAULT NULL COMMENT ‘房间名称’,
logintime BIGINT UNSIGNED DEFAULT 0 COMMENT ‘登录时间’,
INDEX idx_name (name),
INDEX idx_port (port),
INDEX idx_logintime (logintime),
INDEX idx_roomname (roomname)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=’在线用户表’;
— ====================================================================
— 数据表创建完成
— ====================================================================
— 显示所有表
SHOW TABLES;
— 查看表结构示例
SHOW CREATE TABLE room;
SHOW CREATE TABLE user;
© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
暂无评论内容