聊天室数据库建立代码-简单印记

— 1. 创建数据库（指定字符集为GBK，适合老系统中文环境）
CREATE DATABASE IF NOT EXISTS chatdb
DEFAULT CHARACTER SET gbk
COLLATE gbk_chinese_ci;

USE chatdb;

— 2. 房间表 room
CREATE TABLE `room` (
`port` bigint(20) NOT NULL,
`roomname` varchar(40) DEFAULT NULL,
`pid` varchar(6) DEFAULT NULL,
`num` int(11) DEFAULT ‘0’,
`running` char(1) DEFAULT ‘0’,
`type` char(1) DEFAULT ‘T’,
`host` varchar(80) DEFAULT NULL,
`point` bigint(20) DEFAULT ‘0’,
`gifttax` bigint(20) DEFAULT ‘0’,
PRIMARY KEY (`port`),
KEY `idx_roomname` (`roomname`),
KEY `idx_running` (`running`),
KEY `idx_type` (`type`),
KEY `idx_host` (`host`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT=’聊天室房间表’;

— 3. 用户表 user
CREATE TABLE `user` (
`name` varchar(21) NOT NULL,
`escname` varchar(63) DEFAULT NULL,
`nickname` varchar(21) DEFAULT NULL,
`passwd` varchar(11) DEFAULT NULL,
`lasttime` bigint(20) unsigned DEFAULT ‘0’,
`exps` bigint(20) unsigned NOT NULL DEFAULT ‘0’,
`email` varchar(50) DEFAULT NULL,
`sex` int(11) NOT NULL DEFAULT ‘3’,
`icon` bigint(20) unsigned NOT NULL DEFAULT ‘0’,
`extinfo` varchar(100) DEFAULT NULL,
`videopoint` bigint(20) DEFAULT ‘0’,
`userpoint` bigint(20) DEFAULT ‘0’,
`admintime` bigint(20) DEFAULT ‘0’,
`giftpoint` bigint(20) DEFAULT ‘0’,
`videopointmonthly` int(11) DEFAULT ‘0’,
`userpointmonthly` int(11) DEFAULT ‘0’,
`viplevel` bigint(20) unsigned DEFAULT ‘0’,
`iconurl` varchar(100) DEFAULT NULL,
`userlock` bigint(20) unsigned DEFAULT ‘0’,
`reg_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`name`),
KEY `idx_email` (`email`),
KEY `idx_lasttime` (`lasttime`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT=’用户表’;

— 4. IP屏蔽表 screenip
CREATE TABLE `screenip` (
`ip` varchar(15) NOT NULL,
`type` char(1) DEFAULT NULL,
`no` int(11) DEFAULT NULL,
`created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ip`),
KEY `idx_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT=’IP屏蔽表’;

— 5. 管理记录表 admindetail
CREATE TABLE `admindetail` (
`no` bigint(20) NOT NULL AUTO_INCREMENT,
`logtime` bigint(20) unsigned DEFAULT NULL,
`name` varchar(21) DEFAULT NULL,
`admintime` int(10) unsigned DEFAULT NULL,
`roomname` varchar(50) DEFAULT NULL,
PRIMARY KEY (`no`),
KEY `idx_logtime` (`logtime`),
KEY `idx_name` (`name`),
KEY `idx_roomname` (`roomname`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT=’管理记录表’;

— 6. 礼物记录表 giftdetail
CREATE TABLE `giftdetail` (
`no` bigint(20) NOT NULL AUTO_INCREMENT,
`sendtime` bigint(20) unsigned DEFAULT NULL,
`sender` varchar(21) DEFAULT NULL,
`receiver` varchar(21) DEFAULT NULL,
`points` int(11) DEFAULT NULL,
`giftno` int(10) unsigned DEFAULT NULL,
`giftnum` int(10) unsigned DEFAULT NULL,
`roomname` varchar(50) DEFAULT NULL,
PRIMARY KEY (`no`),
KEY `idx_sendtime` (`sendtime`),
KEY `idx_sender` (`sender`),
KEY `idx_receiver` (`receiver`),
KEY `idx_roomname` (`roomname`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT=’礼物记录表’;

— 7. 用户详情表 userdetail
CREATE TABLE `userdetail` (
`no` bigint(20) NOT NULL AUTO_INCREMENT,
`logtime` bigint(20) unsigned DEFAULT NULL,
`name` varchar(21) DEFAULT NULL,
`points` int(10) unsigned DEFAULT NULL,
`roomname` varchar(50) DEFAULT NULL,
PRIMARY KEY (`no`),
KEY `idx_logtime` (`logtime`),
KEY `idx_name` (`name`),
KEY `idx_roomname` (`roomname`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT=’用户详情表’;

— 8. 视频记录表 videodetail
CREATE TABLE `videodetail` (
`no` bigint(20) NOT NULL AUTO_INCREMENT,
`endtime` bigint(20) unsigned DEFAULT NULL,
`watcher` varchar(21) DEFAULT NULL,
`watched` varchar(21) DEFAULT NULL,
`points` int(10) unsigned DEFAULT NULL,
`roomname` varchar(50) DEFAULT NULL,
PRIMARY KEY (`no`),
KEY `idx_endtime` (`endtime`),
KEY `idx_watcher` (`watcher`),
KEY `idx_watched` (`watched`),
KEY `idx_roomname` (`roomname`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT=’视频记录表’;

— 9. 全局管理员表 globaladmin
CREATE TABLE `globaladmin` (
`no` bigint(20) NOT NULL AUTO_INCREMENT,
`globaladmintor` varchar(4000) DEFAULT NULL,
PRIMARY KEY (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT=’全局管理员表’;

— 10. 在线用户表 onlineuser
CREATE TABLE `onlineuser` (
`no` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(21) DEFAULT NULL,
`host` varchar(80) DEFAULT NULL,
`port` bigint(20) DEFAULT NULL,
`roomname` varchar(40) DEFAULT NULL,
`logintime` bigint(20) unsigned DEFAULT NULL,
PRIMARY KEY (`no`),
KEY `idx_name` (`name`),
KEY `idx_host` (`host`),
KEY `idx_port` (`port`),
KEY `idx_logintime` (`logintime`),
KEY `idx_roomname` (`roomname`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT=’在线用户表’;

— 11. 插入全局管理员初始数据
INSERT INTO `globaladmin` (`globaladmintor`) VALUES (‘*’);

— 12. 增加后台管理功能字段到room表（优化为单独执行，避免语法错误）
ALTER TABLE `room`
ADD `maxnum` int(10) unsigned DEFAULT ‘100’,
ADD `bodycolor` varchar(8) DEFAULT ‘#FFFFFF’,
ADD `bodyurl` varchar(80) DEFAULT NULL,
ADD `bottomcolor` varchar(8) DEFAULT ‘#EFF5FE’,
ADD `bottomurl` varchar(80) DEFAULT NULL,
ADD `topcolor` varchar(8) DEFAULT ‘#EFF5FE’,
ADD `adnote` varchar(200) DEFAULT NULL,
ADD `scripturl` varchar(400) DEFAULT NULL,
ADD `leaveurl` varchar(80) DEFAULT NULL,
ADD `fullurl` varchar(80) DEFAULT NULL,
ADD `unauthurl` varchar(80) DEFAULT NULL,
ADD `setadm` char(1) DEFAULT ‘1’,
ADD `setimg` char(1) DEFAULT ‘1’,
ADD `setdoor` char(1) DEFAULT ‘1’,
ADD `setsex` char(1) DEFAULT ‘0’,
ADD `admintor` varchar(1000) DEFAULT NULL,
ADD `channels` varchar(1) DEFAULT NULL,
ADD `adminpasswd` varchar(50) DEFAULT NULL,
ADD `rtype` char(1) DEFAULT NULL,
ADD `keyfile` varchar(50) DEFAULT NULL,
ADD `bindserial` varchar(20) DEFAULT NULL,
ADD `voicemode` varchar(10) DEFAULT ‘HIGH’,
ADD `voicesamples` varchar(3) DEFAULT ‘8’,
ADD `videorate` varchar(4) DEFAULT ’28’,
ADD `videoframerate` varchar(4) DEFAULT ‘6’,
ADD `videonum` char(1) DEFAULT ‘9’,
ADD `voicenum` char(1) DEFAULT ‘2’,
ADD `headurl` varchar(1) DEFAULT NULL,
ADD `lefturl` varchar(1) DEFAULT NULL,
ADD `righturl` varchar(1) DEFAULT NULL,
ADD `footurl` varchar(1) DEFAULT NULL,
ADD `headheight` varchar(1) DEFAULT NULL,
ADD `leftwidth` varchar(1) DEFAULT NULL,
ADD `rightwidth` varchar(1) DEFAULT NULL,
ADD `footheight` varchar(1) DEFAULT NULL,
ADD `welcomemsg` varchar(1) DEFAULT NULL,
ADD `usertable` varchar(1) DEFAULT NULL,
ADD `adminwebport` varchar(5) DEFAULT ’80’,
ADD `templatedir` varchar(255) DEFAULT ”,
ADD `needauth` char(1) DEFAULT ‘2’,
ADD `extconfig` varchar(400) DEFAULT NULL,
ADD `admincount` int(10) unsigned DEFAULT ‘0’,
ADD `outdoorcount` int(10) unsigned DEFAULT ‘0’,
ADD `doorclosed` int(10) unsigned DEFAULT ‘0’,
ADD `onlineadmin` varchar(400) DEFAULT NULL,
ADD `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
ADD `updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;