7天前
-- 聊天室数据库表结构（GBK 优化版）
-- 直接在你已有的数据库里执行即可
DROP TABLE IF EXISTS room;
CREATE TABLE room (
port BIGINT NOT NULL COMMENT '房间端口号',
roomname VARCHAR(40) COMMENT '房间名称',
pid VARCHAR(6) COMMENT '进程ID',
num INT COMMENT '当前人数',
running CHAR(1) COMMENT '运行状态',
type CHAR(1) COMMENT '房间类型',
host VARCHAR(80) COMMENT '主机地址',
point BIGINT DEFAULT 0 COMMENT '房间积分',
gifttax BIGINT DEFAULT 0 COMMENT '礼物税收',
maxnum INT UNSIGNED DEFAULT 0 COMMENT '最大人数',
bodycolor VARCHAR(8) COMMENT '主体颜色',
bodyurl VARCHAR(80) COMMENT '主体背景图',
bottomcolor VARCHAR(8) COMMENT '底部颜色',
bottomurl VARCHAR(80) COMMENT '底部背景图',
topcolor VARCHAR(8) COMMENT '顶部颜色',
adnote VARCHAR(200) COMMENT '广告备注',
scripturl VARCHAR(400) COMMENT '脚本地址',
leaveurl VARCHAR(80) COMMENT '离开跳转地址',
fullurl VARCHAR(80) COMMENT '全屏地址',
unauthurl VARCHAR(80) COMMENT '未授权地址',
setadm CHAR(1) DEFAULT '1' COMMENT '是否允许设置管理员',
setimg CHAR(1) DEFAULT '1' COMMENT '是否允许设置图片',
setdoor CHAR(1) DEFAULT '1' COMMENT '是否允许设置房门',
setsex CHAR(1) DEFAULT '0' COMMENT '性别限制',
admintor VARCHAR(1000) COMMENT '管理员列表',
channels CHAR(1) COMMENT '频道',
adminpasswd VARCHAR(50) COMMENT '管理密码',
rtype CHAR(1) COMMENT '房间子类型',
keyfile VARCHAR(50) COMMENT '密钥文件',
bindserial VARCHAR(20) COMMENT '绑定序列号',
voicemode VARCHAR(10) DEFAULT 'HIGH' COMMENT '语音模式',
voicesamples VARCHAR(3) DEFAULT '8' COMMENT '语音采样率',
videorate VARCHAR(4) DEFAULT '28' COMMENT '视频码率',
videoframerate VARCHAR(4) DEFAULT '6' COMMENT '视频帧率',
videonum CHAR(1) DEFAULT '9' COMMENT '视频数量',
voicenum CHAR(1) DEFAULT '2' COMMENT '语音数量',
headurl VARCHAR(255) COMMENT '头部URL',
lefturl VARCHAR(255) COMMENT '左侧URL',
righturl VARCHAR(255) COMMENT '右侧URL',
footurl VARCHAR(255) COMMENT '底部URL',
headheight VARCHAR(10) COMMENT '头部高度',
leftwidth VARCHAR(10) COMMENT '左侧宽度',
rightwidth VARCHAR(10) COMMENT '右侧宽度',
footheight VARCHAR(10) COMMENT '底部高度',
welcomemsg VARCHAR(255) COMMENT '欢迎消息',
usertable VARCHAR(255) COMMENT '用户表配置',
adminwebport VARCHAR(5) COMMENT '管理后台端口',
templatedir VARCHAR(255) DEFAULT '' COMMENT '模板目录',
needauth CHAR(1) DEFAULT '2' COMMENT '是否需要认证',
extconfig VARCHAR(400) COMMENT '扩展配置',
admincount INT UNSIGNED DEFAULT 0 COMMENT '在线管理员数',
outdoorcount INT UNSIGNED DEFAULT 0 COMMENT '室外人数',
doorclosed INT UNSIGNED DEFAULT 0 COMMENT '房门关闭状态',
onlineadmin VARCHAR(400) COMMENT '在线管理员',
PRIMARY KEY (port)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT='聊天室房间表';
DROP TABLE IF EXISTS user;
CREATE TABLE user (
name VARCHAR(21) NOT NULL COMMENT '用户名',
escname VARCHAR(63) COMMENT '转义用户名',
nickname VARCHAR(21) COMMENT '用户昵称',
passwd VARCHAR(32) COMMENT '用户密码',
lasttime BIGINT UNSIGNED DEFAULT 0 COMMENT '最后在线时间',
exps BIGINT UNSIGNED NOT NULL COMMENT '经验值',
email VARCHAR(50) COMMENT '邮箱',
sex INT NOT NULL COMMENT '性别',
icon BIGINT UNSIGNED NOT NULL COMMENT '头像ID',
extinfo VARCHAR(100) 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(100) COMMENT '头像地址',
userlock BIGINT UNSIGNED DEFAULT 0 COMMENT '用户锁定状态',
PRIMARY KEY (name)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT='聊天室用户表';
DROP TABLE IF EXISTS screenip;
CREATE TABLE screenip (
ip VARCHAR(15) NOT NULL COMMENT 'IP地址',
type CHAR(1) COMMENT '屏蔽类型',
no INT COMMENT '序号',
PRIMARY KEY (ip)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT='IP屏蔽表';
DROP TABLE IF EXISTS admindetail;
CREATE TABLE admindetail (
no BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增ID',
logtime BIGINT UNSIGNED COMMENT '操作时间',
name VARCHAR(21) COMMENT '管理员名称',
admintime INT UNSIGNED COMMENT '授权时长',
roomname VARCHAR(50) COMMENT '房间名称',
INDEX idx_logtime (logtime),
INDEX idx_name (name),
INDEX idx_roomname (roomname)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT='管理员操作详情表';
DROP TABLE IF EXISTS giftdetail;
CREATE TABLE giftdetail (
no BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增ID',
sendtime BIGINT UNSIGNED COMMENT '赠送时间',
sender VARCHAR(21) COMMENT '赠送者',
receiver VARCHAR(21) COMMENT '接收者',
points INT COMMENT '积分',
giftno INT UNSIGNED COMMENT '礼物编号',
giftnum INT UNSIGNED COMMENT '礼物数量',
roomname VARCHAR(50) COMMENT '房间名称',
INDEX idx_sendtime (sendtime),
INDEX idx_sender (sender),
INDEX idx_receiver (receiver),
INDEX idx_roomname (roomname)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT='礼物赠送详情表';
DROP TABLE IF EXISTS userdetail;
CREATE TABLE userdetail (
no BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增ID',
logtime BIGINT UNSIGNED COMMENT '记录时间',
name VARCHAR(21) COMMENT '用户名',
points INT UNSIGNED COMMENT '积分',
roomname VARCHAR(50) COMMENT '房间名称',
INDEX idx_logtime (logtime),
INDEX idx_name (name),
INDEX idx_roomname (roomname)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT='用户积分变动表';
DROP TABLE IF EXISTS videodetail;
CREATE TABLE videodetail (
no BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增ID',
endtime BIGINT UNSIGNED COMMENT '结束时间',
watcher VARCHAR(21) COMMENT '观看者',
watched VARCHAR(21) COMMENT '被观看者',
points INT UNSIGNED COMMENT '消费积分',
roomname VARCHAR(50) COMMENT '房间名称',
INDEX idx_endtime (endtime),
INDEX idx_watcher (watcher),
INDEX idx_watched (watched),
INDEX idx_roomname (roomname)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT='视频消费记录表';
DROP TABLE IF EXISTS globaladmin;
CREATE TABLE globaladmin (
no BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增ID',
globaladmintor VARCHAR(4000) COMMENT '全局管理员列表'
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT='全局管理员表';
INSERT INTO globaladmin (globaladmintor) VALUES ('*');
DROP TABLE IF EXISTS onlineuser;
CREATE TABLE onlineuser (
no BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增ID',
name VARCHAR(21) COMMENT '用户名',
host VARCHAR(80) COMMENT '主机地址',
port BIGINT COMMENT '房间端口',
roomname VARCHAR(40) COMMENT '房间名称',
logintime BIGINT UNSIGNED COMMENT '登录时间',
INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT='在线用户表';
评论于：聊天室数据库建立代码
5个月前
9个月前
2年前
评论于：serv00免费主机注册2
