jian.wang/HomeServer
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
master
HomeServer/NewMiniGame.sql

476 lines
24 KiB
MySQL
Raw Permalink Normal View History

提交
2024-11-20 15:41:09 +08:00
/*
Navicat Premium Data Transfer
Source Server : 内网测试数据库
Source Server Type : MySQL
Source Server Version : 50731
Source Host : 172.16.2.122:3306
Source Schema : NewMiniGame
Target Server Type : MySQL
Target Server Version : 50731
File Encoding : 65001
Date: 20/12/2021 21:35:55
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for ad_convert_record
-- ----------------------------
DROP TABLE IF EXISTS `ad_convert_record`;
CREATE TABLE `ad_convert_record` (
`recordTime` int(10) NULL DEFAULT NULL COMMENT '记录时间',
`requestIP` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '请求的IP地址',
`adMd5` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'MD5',
`adStatus` int(10) NULL DEFAULT NULL COMMENT '0-展示 1-点击 2-已使用',
`adDeviceParam` json NULL COMMENT '广告平台记录参数',
`endTime` int(10) NULL DEFAULT NULL COMMENT '结束时间'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of ad_convert_record
-- ----------------------------
-- ----------------------------
-- Table structure for adinfo
-- ----------------------------
DROP TABLE IF EXISTS `adinfo`;
CREATE TABLE `adinfo` (
`firmId` int(10) NOT NULL COMMENT '平台id',
`ratio` int(10) NOT NULL COMMENT '分成比例 范围 1- 100',
`todayEcpm` int(10) NULL DEFAULT NULL COMMENT '当前平台的 ecpm （指广告端当日的千次视频展示获得的收入）',
`yesterdayEcpm` int(10) NULL DEFAULT NULL COMMENT '昨天的ecpm',
`recordTime` int(10) NULL DEFAULT NULL COMMENT '最后一次记录时间',
PRIMARY KEY (`firmId`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of adinfo
-- ----------------------------
-- ----------------------------
-- Table structure for admin
-- ----------------------------
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`pwd` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`lastLoginTime` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of admin
-- ----------------------------
-- ----------------------------
-- Table structure for alluserrecord
-- ----------------------------
DROP TABLE IF EXISTS `alluserrecord`;
CREATE TABLE `alluserrecord` (
`ID` int(10) NULL DEFAULT NULL COMMENT '玩家ID',
`CreationValue` float(255, 2) NULL DEFAULT NULL COMMENT '创造价值',
`GameTime` int(10) NULL DEFAULT NULL COMMENT '游戏时长',
`WatchCount` int(10) NULL DEFAULT NULL COMMENT '广告数量',
`RecordTime` int(11) NULL DEFAULT NULL COMMENT '记录时间'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of alluserrecord
-- ----------------------------
-- ----------------------------
-- Table structure for day_data_record
-- ----------------------------
DROP TABLE IF EXISTS `day_data_record`;
CREATE TABLE `day_data_record` (
`ID` int(10) NOT NULL COMMENT '玩家ID',
`AdCount` int(10) NULL DEFAULT NULL COMMENT '广告数量',
`RecordTime` int(10) NULL DEFAULT NULL COMMENT '记录时间',
PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of day_data_record
-- ----------------------------
-- ----------------------------
-- Table structure for everyday
-- ----------------------------
DROP TABLE IF EXISTS `everyday`;
CREATE TABLE `everyday` (
`id` bigint(20) NOT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`stime` int(11) NULL DEFAULT NULL,
`headurl` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of everyday
-- ----------------------------
-- ----------------------------
-- Table structure for gaincash
-- ----------------------------
DROP TABLE IF EXISTS `gaincash`;
CREATE TABLE `gaincash` (
`id` int(10) NOT NULL,
`gameId` int(10) NULL DEFAULT NULL,
`source` int(10) NULL DEFAULT NULL COMMENT '来源',
`type` int(10) NULL DEFAULT NULL COMMENT '1-红包提现 2-直接提现',
`money` int(10) NULL DEFAULT NULL COMMENT '提现金额',
`redPacket` int(10) NULL DEFAULT NULL COMMENT '此次消耗的红包券',
`WatchCount` int(10) NULL DEFAULT NULL COMMENT '累计观看次数',
`GameTime` int(10) NULL DEFAULT NULL COMMENT '累计游戏时间',
`GainCashCount` int(10) NULL DEFAULT NULL COMMENT '累计提现数量',
`GainRedCount` int(10) NULL DEFAULT NULL COMMENT '累计获得红包',
`CostRedCount` int(10) NULL DEFAULT NULL COMMENT '累计消耗红包',
`recordTime` int(12) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of gaincash
-- ----------------------------
-- ----------------------------
-- Table structure for gamedatarecord
-- ----------------------------
DROP TABLE IF EXISTS `gamedatarecord`;
CREATE TABLE `gamedatarecord` (
`CreationValue` float(255, 2) NULL DEFAULT NULL COMMENT '创造价值',
`GainCash` float(255, 2) NULL DEFAULT NULL COMMENT '提现金额',
`OnlineCount` int(10) NULL DEFAULT NULL COMMENT '在线人数',
`RecordTime` int(11) NULL DEFAULT NULL COMMENT '记录时间'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of gamedatarecord
-- ----------------------------
-- ----------------------------
-- Table structure for loginrecord
-- ----------------------------
DROP TABLE IF EXISTS `loginrecord`;
CREATE TABLE `loginrecord` (
`ID` int(10) NULL DEFAULT NULL COMMENT '玩家ID',
`Status` int(10) NULL DEFAULT NULL COMMENT '1-登陆 2-离开',
`IP` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'IP地址',
`RecordTime` int(11) NULL DEFAULT NULL COMMENT '记录时间'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of loginrecord
-- ----------------------------
-- ----------------------------
-- Table structure for moneyrecord
-- ----------------------------
DROP TABLE IF EXISTS `moneyrecord`;
CREATE TABLE `moneyrecord` (
`id` int(10) NOT NULL COMMENT '玩家ID',
`opType` int(10) NULL DEFAULT NULL COMMENT '操作类型1-获得 2-支付',
`itemType` int(10) NULL DEFAULT NULL COMMENT '1-钻石 2-红包券 3-经验',
`isUseRed` int(10) NULL DEFAULT NULL COMMENT '是否使用红包，opType为2进生效',
`changeCount` int(10) NULL DEFAULT NULL COMMENT '变化的数量',
`lastCount` int(10) NULL DEFAULT NULL COMMENT '最新的数量',
`source` int(10) NULL DEFAULT NULL COMMENT '来源类型',
`recordTime` int(11) NULL DEFAULT NULL COMMENT '记录时间',
INDEX `TimeIndex`(`recordTime`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of moneyrecord
-- ----------------------------
-- ----------------------------
-- Table structure for player
-- ----------------------------
DROP TABLE IF EXISTS `player`;
CREATE TABLE `player` (
`id` int(11) NOT NULL,
`data` json NULL,
`lastRecordTime` int(11) NULL DEFAULT NULL COMMENT '最后一次刷新记录时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of player
-- ----------------------------
INSERT INTO `player` VALUES (100025, '{\"id\": 100025, \"bag\": [], \"exp\": 0, \"gain\": {\"farmCount\": 0, \"platCount\": 0, \"shopCount\": 0}, \"item\": [], \"name\": \"\", \"plat\": [], \"shop\": {\"times1\": [], \"times2\": [], \"times3\": [], \"times4\": [], \"times5\": [], \"times6\": [], \"isvideo\": 0, \"unlock1\": 2, \"unlock2\": 2, \"unlock3\": 2, \"unlock4\": 2, \"unlock5\": 2, \"unlock6\": 2, \"factory1\": [], \"factory2\": [], \"factory3\": [], \"factory4\": [], \"factory5\": [], \"factory6\": []}, \"guide\": {\"step\": 0, \"isGainGift\": false}, \"level\": 1, \"title\": [], \"adEcpm\": 0, \"adInfo\": {\"adId\": 0, \"firmId\": 0, \"status\": 1, \"startTime\": 0}, \"signIn\": {\"curDay\": 1, \"isActive\": true, \"curPeriod\": 1, \"isTodayGain\": false, \"isTodayWatch\": false}, \"tixian\": {\"tx\": [], \"yuan\": 0, \"records\": []}, \"account\": \"r1\", \"bigcrad\": {\"nums\": 0, \"bubred\": 0, \"isopen\": 0, \"time_s\": 0, \"catchexp\": 0, \"bubfreeexp\": 0, \"bubfreered\": 0, \"catchtimes\": 0, \"watchvido1\": 0, \"watchvido2\": 0, \"bubfreediamo\": 0, \"catchdiamond\": 0}, \"dayTask\": {\"box\": [], \"task\": [], \"stime\": 0, \"tatalscore\": 0, \"todaylogin\": 0}, \"dialROI\": 0, \"diamond\": 100, \"dropRed\": {\"gainCount\": 0, \"isCanGain\": false, \"isWatchAd\": false, \"lastRandCount\": 0}, \"headUrl\": \"\", \"isFloor\": false, \"mainRed\": [], \"regTime\": 1635390630, \"upLvExp\": 0, \"farmgame\": {\"gift\": [], \"hasgot\": 0, \"lvlist\": [], \"kanvido\": 0}, \"gainCash\": 0, \"loginDay\": 0, \"allAdInfo\": [], \"godwealth\": {\"alone\": 0, \"stime\": 0, \"ttime\": 0, \"txtime\": 0, \"currpkg\": \"111\", \"txmoney\": 0, \"evrdyone\": 0, \"evrdytwo\": 0, \"startpkg\": \"222\", \"currvideo\": 0, \"sevrdyone\": 0, \"sevrdytwo\": 0, \"staralone\": 0, \"teverdtwo\": 0, \"tixiantims\": 0, \"packagename\": [], \"oldstarttime\": 0, \"othervediotimes\": 0}, \"platCount\": 0, \"redPacket\": 0, \"todayGain\": {\"exp\": 0, \"cash\": 0, \"adRed\": 0, \"diamond\": 0, \"farmBuy\": 0, \"freeRed\": 0, \"watchAd\": [], \"gameTime\": 0, \"redCount\": [], \"buyDiamond\": 0, \"floorCount\": 0, \"plantCount\": 0, \"dropRedCount\": 0, \"creationValue\": 0, \"plantAccCount\": 0, \"shopMakeCount\": 0, \"plantRandReward\": [], \"shopUnlockCount\": 0, \"farmFeedAccCount\": 0, \"shopMakeAccCount\": 0}, \"buyDiamond\": {\"isWatchAd\": false}, \"dialReward\": {\"money\": 0, \"stime\": 0, \"times\": 0, \"bigred\": 0, \"isvido\": 0, \"diamond\": 0, \"hugered\": 0, \"todaylogin\": 0}, \"gainDouble\": {\"farmDouble\": false, \"shopDouble\": false, \"plantDouble\": false}, \"landUnlock\": {\"farm\": false, \"plat\": false, \"shop\": false, \"plant\": true}, \"loginCount\": 0, \"npcredpckt\": {\"pool\": 1, \"stime\": 0, \"awards\": [], \"kitten\": 1, \"todaydt\": [], \"littecrad\": 1, \"poolvideo\": 0, \"kittenvideo\": 0, \"litcradvideo\": 0, \"litcradvideo2\": 0, \"litcradvideo3\": 0}, \"saving_pot\": {\"money\": 0, \"time_s\": 0, \"isbaodi\": 0, \"txtimes\": 0, \"totalmoney\": 0, \"isoldplayer\": 0}, \"allDataStat\": {\"cash\": 0, \"adRed\": 0, \"diamond\": 0, \"fallRed\": 0, \"freeRed\": 0, \"creationValue\": 0}, \"annimalhome\": {\"cow\": [], \"pig\": [], \"stime\": 0, \"isopen\": 0, \"chicken\": [], \"isvideo\": 0, \"cowfodder\": 50, \"pigfodder\": 50, \"unlockhome1\": 1, \"unlockhome2\": 1, \"unlockhome3\": 1, \"chickenfodder\": 50}, \"lastAdValue\": 0, \"levelReward\": [], \"adDialProfit\": 0, \"adMainProfit\": 0, \"clickAdCount\": 0, \"everydaygift\": {\"stime\": 1, \"times\": 0, \"showmy\": 0, \"dangwei\": [], \"todaylogin\": 0, \"tixiantimes\": []}, \"isTodayFloor\": true, \"npcRedPacket\": 0, \"watchAdCount\": 0, \"watchAdAction\": {\"isAdJudge\": true, \"isGainCash\": true, \"unlockTime\": 0, \"seriesCount\": 0, \"nextLimitLevel\": 0}, \"lastLogintTime\": 0, \"loginSeriesDay\": 1, \"adDayGiftProfit\": 0, \"adSavingPotEcpm\": 0, \"lastDayGainCash\": 0, \"todayLoginCount\": 0, \"gameWatchAdCount\": 0, \"lastDayCreationValue\": 0}', 1635390630);
-- ----------------------------
-- Table structure for real_identity
-- ----------------------------
DROP TABLE IF EXISTS `real_identity`;
CREATE TABLE `real_identity` (
`ID` int(11) NOT NULL,
`Name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`IdentityCard` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of real_identity
-- ----------------------------
-- ----------------------------
-- Table structure for rewardcenter
-- ----------------------------
DROP TABLE IF EXISTS `rewardcenter`;
CREATE TABLE `rewardcenter` (
`itemId` int(10) NOT NULL COMMENT '道具id',
`type` int(10) NULL DEFAULT NULL COMMENT '1-种植园 2-养殖场 3-商店',
`curCount` int(10) NULL DEFAULT NULL COMMENT '今日当前数量',
`allCount` int(10) NULL DEFAULT NULL COMMENT '每日总的数量'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of rewardcenter
-- ----------------------------
-- ----------------------------
-- Table structure for serverinfo
-- ----------------------------
DROP TABLE IF EXISTS `serverinfo`;
CREATE TABLE `serverinfo` (
`userVersion` int(10) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '当前的用户数据版本' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of serverinfo
-- ----------------------------
-- ----------------------------
-- Table structure for statalluserdata
-- ----------------------------
DROP TABLE IF EXISTS `statalluserdata`;
CREATE TABLE `statalluserdata` (
`UserType` int(11) NULL DEFAULT NULL COMMENT '1-新玩家 2-老玩家',
`DataType` int(11) NULL DEFAULT NULL COMMENT '类型 1-创造价值 2-提现数量',
`AllCount` float(11, 2) NULL DEFAULT NULL COMMENT '数量',
`RecordTime` int(11) NULL DEFAULT NULL COMMENT '记录时间（时间戳）'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of statalluserdata
-- ----------------------------
-- ----------------------------
-- Table structure for statbaseinfo
-- ----------------------------
DROP TABLE IF EXISTS `statbaseinfo`;
CREATE TABLE `statbaseinfo` (
`ActiveUser` int(11) NULL DEFAULT NULL COMMENT '活跃人数',
`ActiveNewUser` int(11) NULL DEFAULT NULL COMMENT '新活跃人数',
`ActiveOldUser` int(11) NULL DEFAULT NULL COMMENT '老活跃人数',
`ActiveARPU` int(11) NULL DEFAULT NULL COMMENT '活跃ARPU',
`CashARPU` int(11) NULL DEFAULT NULL COMMENT '提现ARPU',
`ActiveNewARPU` int(11) NULL DEFAULT NULL COMMENT '新活跃ARPU',
`ActiveOldARPU` int(11) NULL DEFAULT NULL COMMENT '老活跃ARPU',
`CashNewARPU` int(11) NULL DEFAULT NULL COMMENT '新提现ARPU',
`CashOldARPU` int(11) NULL DEFAULT NULL COMMENT '老提现ARPU',
`RecordTime` int(11) NULL DEFAULT NULL COMMENT '记录时间（时间戳）'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of statbaseinfo
-- ----------------------------
-- ----------------------------
-- Table structure for statcash
-- ----------------------------
DROP TABLE IF EXISTS `statcash`;
CREATE TABLE `statcash` (
`UserType` int(11) NULL DEFAULT NULL COMMENT '1新增人数 2老玩家 3活跃玩家',
`CashCountType` int(11) NULL DEFAULT NULL COMMENT '提现次数类型\r\n1为1次\r\n2为2次\r\n3为3次\r\n4为4次\r\n5为5次\r\n6为6次\r\n7为7次\r\n8为8次\r\n9为9次\r\n10为10次',
`CashUser` int(11) NULL DEFAULT NULL COMMENT '提现人数',
`CashMoney` int(11) NULL DEFAULT NULL COMMENT '提现金额',
`CreationValue` float(11, 2) NULL DEFAULT NULL COMMENT '创造价值',
`AveCashMoney` float(11, 2) NULL DEFAULT NULL COMMENT '人均提现金额',
`AveCreationValue` float(11, 2) NULL DEFAULT NULL COMMENT '人均创造价值',
`AveGameTime` float(11, 2) NULL DEFAULT NULL COMMENT '人均在线时长',
`AveWatchCount` float(11, 2) NULL DEFAULT NULL COMMENT '人均视频次数',
`RecordTime` int(11) NULL DEFAULT NULL COMMENT '记录时间（时间戳秒）'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of statcash
-- ----------------------------
-- ----------------------------
-- Table structure for statdetailsinfo
-- ----------------------------
DROP TABLE IF EXISTS `statdetailsinfo`;
CREATE TABLE `statdetailsinfo` (
`UserType` int(11) NULL DEFAULT NULL COMMENT '1新增人数 2老玩家',
`UserCount` int(11) NULL DEFAULT NULL COMMENT '人数',
`AdRedCoin` bigint(16) NULL DEFAULT NULL COMMENT '付费红包币',
`FreeRedCoin` bigint(16) NULL DEFAULT NULL COMMENT '免费红包币',
`SaveCoin` bigint(16) NULL DEFAULT NULL COMMENT '存钱币',
`CreationValue` bigint(16) NULL DEFAULT NULL COMMENT '创造价值',
`GoldValue` bigint(16) NULL DEFAULT NULL COMMENT '财神价值',
`AllGainCash` bigint(16) NULL DEFAULT NULL COMMENT '所有提现金额',
`GoldGainCash` bigint(16) NULL DEFAULT NULL COMMENT '财神提现金额',
`SaveGainCash` bigint(16) NULL DEFAULT NULL COMMENT '存钱罐提现金额',
`DialGainCash` bigint(16) NULL DEFAULT NULL COMMENT '转盘提现金额',
`RecordTime` int(11) NULL DEFAULT NULL COMMENT '记录时间（时间戳秒）'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of statdetailsinfo
-- ----------------------------
-- ----------------------------
-- Table structure for statretention
-- ----------------------------
DROP TABLE IF EXISTS `statretention`;
CREATE TABLE `statretention` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`NewUser` int(11) NULL DEFAULT NULL COMMENT '新增人数',
`2DayCount` int(11) NULL DEFAULT NULL COMMENT '次留',
`3DayCount` int(11) NULL DEFAULT NULL,
`4DayCount` int(11) NULL DEFAULT NULL,
`5DayCount` int(11) NULL DEFAULT NULL COMMENT '5留',
`6DayCount` int(11) NULL DEFAULT NULL,
`7DayCount` int(11) NULL DEFAULT NULL,
`8DayCount` int(11) NULL DEFAULT NULL,
`9DayCount` int(11) NULL DEFAULT NULL COMMENT '9留',
`RecordTime` int(11) NULL DEFAULT NULL COMMENT '记录时间（时间戳）',
PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of statretention
-- ----------------------------
-- ----------------------------
-- Table structure for statwatchmovie
-- ----------------------------
DROP TABLE IF EXISTS `statwatchmovie`;
CREATE TABLE `statwatchmovie` (
`UserType` int(11) NOT NULL COMMENT '1新增人数 2老玩家 3活跃玩家',
`WatchCountType` int(11) NULL DEFAULT NULL COMMENT '观看视频次数类型\r\n0 为 0次\r\n1 为 1-4\r\n2 为 5-9\r\n3 为 10-19\r\n4 为 20-29\r\n5 为 30-39\r\n6 为 40-49\r\n7 为 50-59\r\n8 为 60-69\r\n9 为 70-79\r\n10 为 80-89\r\n11 为 90-99\r\n12 为 100-119\r\n13 为 120-149\r\n14 为 150-179\r\n15 为 180-199\r\n16 为 200以上',
`UserCount` int(11) NULL DEFAULT NULL COMMENT '当前用户类型观看视频人数',
`WatchUserRatio` int(11) NULL DEFAULT NULL COMMENT '当前用户类型观看视频人数占比',
`WatchMovieCount` int(11) NULL DEFAULT NULL COMMENT '当前用户类型观看视频次数',
`WatchMovieRatio` int(11) NULL DEFAULT NULL COMMENT '当前用户类型观看视频次数占比',
`AveWatchMovieCount` int(11) NULL DEFAULT NULL COMMENT '当前用户类型观看视频人均观看次数',
`RecordTime` int(11) NULL DEFAULT NULL COMMENT '记录时间（时间戳秒）'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of statwatchmovie
-- ----------------------------
-- ----------------------------
-- Table structure for uppoint
-- ----------------------------
DROP TABLE IF EXISTS `uppoint`;
CREATE TABLE `uppoint` (
`UDID` char(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
`PointType` int(10) NULL DEFAULT NULL,
`RecordTime` int(11) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of uppoint
-- ----------------------------
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`account` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`pwd` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`name` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`loginType` int(10) NULL DEFAULT NULL COMMENT '1-本地登陆 2-微信登陆',
`accountStatus` int(10) NULL DEFAULT NULL COMMENT '1-正常状态 2-白名单 3-灰名单 4-黑名单',
`token` char(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`regTime` int(11) NULL DEFAULT NULL COMMENT '注册时间',
`regIP` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '注册IP',
`lastLoginTime` int(11) NULL DEFAULT NULL COMMENT '最后一次登陆时间',
`lastLoginIP` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '最后一次登陆IP',
`lastServerId` int(11) NULL DEFAULT NULL COMMENT '最后一次登陆服务器的ID',
`RealName` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '真实姓名',
`IdentityCard` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '身份证\r\n',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 100026 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (100025, 'r1', NULL, '我是人', 1, 1, '', 1635390630, '172.16.1.245', 1635390630, '172.16.1.245', NULL, NULL, NULL);
-- ----------------------------
-- Table structure for user_device_info
-- ----------------------------
DROP TABLE IF EXISTS `user_device_info`;
CREATE TABLE `user_device_info` (
`id` int(11) NOT NULL COMMENT '玩家ID',
`adDeviceParam` json NULL COMMENT '广告平台的设备信息',
`aDeviceParam` json NULL COMMENT 'APP的设备信息',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user_device_info
-- ----------------------------
-- ----------------------------
-- Table structure for user_device_infor
-- ----------------------------
DROP TABLE IF EXISTS `user_device_infor`;
CREATE TABLE `user_device_infor` (
`id` int(11) NOT NULL,
`adDeviceInfor` json NULL COMMENT '广告过来的设备信息',
`appDeviceInfor` json NULL COMMENT 'APP过来的设备信息',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user_device_infor
-- ----------------------------
-- ----------------------------
-- Table structure for warning
-- ----------------------------
DROP TABLE IF EXISTS `warning`;
CREATE TABLE `warning` (
`id` int(10) NOT NULL,
`type` int(10) NULL DEFAULT NULL,
`recordTime` int(12) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of warning
-- ----------------------------
-- ----------------------------
-- Procedure structure for ADCount
-- ----------------------------
DROP PROCEDURE IF EXISTS `ADCount`;
delimiter ;;
CREATE PROCEDURE `ADCount`()
BEGIN
DECLARE adCount int default 0;
DECLARE i INT DEFAULT 0;
select json_length(JSON_EXTRACT(player.data,'$.allAdInfo')) from player where player.id = 100000 into adCount;
WHILE i < adCount DO
select JSON_EXTRACT(JSON_EXTRACT(player.data,'$.allAdInfo'), '$[1].count') as a1 from player where player.id = 100000;
set i = i + 1;
END WHILE;
END
;;
delimiter ;
SET FOREIGN_KEY_CHECKS = 1;
Reference in New Issue Copy Permalink