book_admin/test_cps_user.sql

/*
SQLyog Community v13.1.6 (64 bit)
MySQL - 5.7.15-log : Database - cps_user_256
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`cps_user_256` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `cps_user_256`;

/*Table structure for table `consume` */

DROP TABLE IF EXISTS `consume`;

CREATE TABLE `consume` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
  `type` enum('1','2') COLLATE utf8mb4_unicode_ci DEFAULT '1' COMMENT '消费类型:1=订购章节,2=打赏',
  `book_id` bigint(20) unsigned DEFAULT NULL COMMENT '书籍ID',
  `book_name` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '书籍名称',
  `chapter_id` bigint(20) unsigned DEFAULT NULL COMMENT '章节ID',
  `chapter_name` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '章节名称',
  `kandian` int(11) unsigned DEFAULT '0' COMMENT '充值看点数',
  `free_kandian` int(11) unsigned DEFAULT '0' COMMENT '赠送看点数',
  `createtime` int(10) unsigned DEFAULT NULL COMMENT '创建时间',
  `updatetime` int(10) unsigned DEFAULT NULL COMMENT '更新时间',
  `extend1` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT '',
  `extend2` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `consume_user_id` (`user_id`),
  KEY `consume_book_id` (`book_id`),
  KEY `consume_chapter_id` (`chapter_id`),
  KEY `consume_type` (`type`),
  KEY `consume_createtime` (`createtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='消费表';

/*Data for the table `consume` */

/*Table structure for table `openid` */

DROP TABLE IF EXISTS `openid`;

CREATE TABLE `openid` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键ID',
  `channel_openid` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '渠道商ID_openid',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
  `createtime` int(11) unsigned DEFAULT NULL COMMENT '创建时间',
  `updatetime` int(11) unsigned DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `channel_openid` (`channel_openid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='openid与user_id映射表';

/*Data for the table `openid` */

insert  into `openid`(`id`,`channel_openid`,`user_id`,`createtime`,`updatetime`) values 
(1,'1736_oK2-n1LJImgDcdAY_AMJZezXECeQ',23364,1596962218,1596962218);

/*Table structure for table `openid_all` */

DROP TABLE IF EXISTS `openid_all`;

CREATE TABLE `openid_all` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键ID',
  `channel_openid` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '渠道商ID_openid',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
  `createtime` int(11) unsigned DEFAULT NULL COMMENT '创建时间',
  `updatetime` int(11) unsigned DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `channel_openid` (`channel_openid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='openid与user_id映射表';

/*Data for the table `openid_all` */

/*Table structure for table `recharge` */

DROP TABLE IF EXISTS `recharge`;

CREATE TABLE `recharge` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
  `type` enum('1','2','3','4','5','6') COLLATE utf8mb4_unicode_ci DEFAULT '1' COMMENT '充值类型:1=看点充值,2=VIP充值,3=系统操作看点,4=系统操作vip,5=签到,6=活动赠送',
  `kandian` int(11) DEFAULT '0' COMMENT '充值看点数',
  `free_kandian` int(11) DEFAULT '0' COMMENT '赠送看点数',
  `remain_free_kandian` int(10) unsigned DEFAULT '0' COMMENT '剩余免费看点',
  `free_endtime` int(10) unsigned DEFAULT NULL COMMENT '赠送看点到期时间',
  `vip_starttime` int(10) unsigned DEFAULT NULL COMMENT 'VIP开始时间',
  `day` int(11) DEFAULT '0' COMMENT 'VIP天数',
  `hour` tinyint(2) DEFAULT NULL COMMENT 'vip小时',
  `book_id` bigint(20) unsigned DEFAULT NULL COMMENT '书籍ID',
  `createtime` int(10) unsigned DEFAULT NULL COMMENT '创建时间',
  `updatetime` int(10) unsigned DEFAULT NULL COMMENT '更新时间',
  `notes` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
  `edit_type` enum('1','2') COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '充值类型:1=活动奖励,2=系统操作',
  `remain_kandian` int(10) unsigned DEFAULT '0' COMMENT '剩余永久看点',
  `dd` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否扣量',
  `channel_vip_starttime` int(10) unsigned DEFAULT NULL COMMENT 'VIP开始时间qds',
  `business_line` enum('0','1') COLLATE utf8mb4_unicode_ci DEFAULT '0',
  `orders_id` int(11) NOT NULL DEFAULT '0' COMMENT '订单ID',
  `extend_type` tinyint(3) unsigned DEFAULT '0' COMMENT '扩展类型 1,消耗活动充值',
  PRIMARY KEY (`id`),
  KEY `recharge_user_id` (`user_id`),
  KEY `re_free_endtime` (`free_endtime`)
) ENGINE=InnoDB AUTO_INCREMENT=22696 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='充值表';

/*Data for the table `recharge` */

insert  into `recharge`(`id`,`user_id`,`type`,`kandian`,`free_kandian`,`remain_free_kandian`,`free_endtime`,`vip_starttime`,`day`,`hour`,`book_id`,`createtime`,`updatetime`,`notes`,`edit_type`,`remain_kandian`,`dd`,`channel_vip_starttime`,`business_line`,`orders_id`,`extend_type`) values 
(22695,22272,'5',0,20,20,1586500786,NULL,0,NULL,NULL,1586414386,1586414386,NULL,NULL,0,0,NULL,'0',0,0);

/*Table structure for table `share_source` */

DROP TABLE IF EXISTS `share_source`;

CREATE TABLE `share_source` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户id',
  `from_user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '来自与哪个用户分享的用户id',
  `share_time` int(10) unsigned zerofill DEFAULT NULL COMMENT '分享时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`,`from_user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户分享来源关系表';

/*Data for the table `share_source` */

/*Table structure for table `share_user` */

DROP TABLE IF EXISTS `share_user`;

CREATE TABLE `share_user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '分享者用户id',
  `to_user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '被分享者的用户id',
  `to_user_nickname` char(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '被分享者的昵称',
  `to_user_avatar` char(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '被分享者的头像',
  `is_fan` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否通过分享链接关注（不代表真实的关注情况)1，是',
  `kandian` smallint(6) NOT NULL DEFAULT '0' COMMENT '关 注后得到的看点',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `userid` (`user_id`,`to_user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户分享关系表';

/*Data for the table `share_user` */

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL,
  `openid` varchar(28) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '微信openID',
  `unionid` varchar(29) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '微信unionid',
  `visitor` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '访客id',
  `nickname` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '昵称',
  `sex` enum('0','1','2') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0' COMMENT '状态值:0=未知,1=男性,2=女性',
  `mobile` char(11) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '手机号',
  `avatar` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '头像',
  `is_subscribe` enum('1','0') COLLATE utf8mb4_unicode_ci DEFAULT '0' COMMENT '关注公众号状态:1=已关注,0=未关注',
  `is_white` tinyint(1) DEFAULT '0' COMMENT '是否是白名单:0=不是,1=是',
  `first_cancel_pay` tinyint(1) DEFAULT '0' COMMENT '首冲:0=结束,1=激活,2=未激活',
  `subscription_extend` enum('0','1') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0' COMMENT '关注引导公众号状态:1=已关注,0=未关注',
  `subscribe_time` int(10) unsigned DEFAULT '0' COMMENT '关注时间',
  `book_category_ids` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '喜好书籍分类',
  `operate_time` int(10) DEFAULT NULL COMMENT '微信交互时间戳',
  `is_pay` enum('1','0') COLLATE utf8mb4_unicode_ci DEFAULT '0' COMMENT '首充状态:1=已充值,0=未充值',
  `kandian` int(11) unsigned DEFAULT '0' COMMENT '充值看点数',
  `free_kandian` int(11) unsigned DEFAULT '0' COMMENT '赠送看点数',
  `vip_endtime` int(10) unsigned DEFAULT '0' COMMENT 'VIP到期时间',
  `register_ip` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '注册IP',
  `country` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '国家',
  `area` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '区域',
  `province` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '省份',
  `city` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '城市',
  `isp` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'isp服务商',
  `channel_id` int(11) unsigned NOT NULL COMMENT '关联渠道商ID',
  `state` enum('0','1') COLLATE utf8mb4_unicode_ci DEFAULT '1' COMMENT '状态值:0=禁用,1=正常',
  `createtime` int(11) unsigned DEFAULT NULL COMMENT '创建时间',
  `updatetime` int(11) unsigned DEFAULT NULL COMMENT '更新时间',
  `is_first_unfollow` enum('0','1','2') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '2' COMMENT '是否首次取消关注:0=否,1=是,2=未取关',
  `referral_id` int(10) NOT NULL DEFAULT '0' COMMENT '推广链接id',
  `referral_id_permanent` int(11) DEFAULT NULL COMMENT '永久referral_id',
  `agent_id` int(10) NOT NULL DEFAULT '0' COMMENT '代理商id',
  `follow_referral_id` int(11) DEFAULT '0' COMMENT '用户关注推广链接',
  `has_first_unpay` enum('0','1','2') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '2' COMMENT '是否首次取消支付:0=否,1=是,2=未取消支付',
  `city_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '所属城市',
  `ext` text COLLATE utf8mb4_unicode_ci COMMENT 'push_id,push_idx,mark,push_time...',
  `is_black` tinyint(1) DEFAULT '0' COMMENT '是不是黑名单，0=不是，1=黑名单，2=灰名单',
  `flush_state` tinyint(1) DEFAULT '0' COMMENT '0=待清理 1=脚本清理 2=访问清理',
  `extend` json DEFAULT NULL COMMENT '用户扩展信息',
  PRIMARY KEY (`id`),
  KEY `i_subscribe_state` (`operate_time`,`is_subscribe`,`state`),
  KEY `user_updatetime` (`updatetime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='前台用户表';

/*Data for the table `user` */

insert  into `user`(`id`,`openid`,`unionid`,`visitor`,`nickname`,`sex`,`mobile`,`avatar`,`is_subscribe`,`is_white`,`first_cancel_pay`,`subscription_extend`,`subscribe_time`,`book_category_ids`,`operate_time`,`is_pay`,`kandian`,`free_kandian`,`vip_endtime`,`register_ip`,`country`,`area`,`province`,`city`,`isp`,`channel_id`,`state`,`createtime`,`updatetime`,`is_first_unfollow`,`referral_id`,`referral_id_permanent`,`agent_id`,`follow_referral_id`,`has_first_unpay`,`city_code`,`ext`,`is_black`,`flush_state`,`extend`) values 
(22272,'22272',NULL,NULL,'是远辰啊?','2',NULL,'http://thirdwx.qlogo.cn/mmopen/RRo1phJsnNh5oFKVfUicfWdibdIQzYlcX9sEQ34O9h7Sh9PLP2ib6Uv20f2Z26mjglcfQpibEwU5XEqg58CcU4PPB5KVXsnTdv6O/132','1',0,2,'0',1586414371,NULL,1586414604,'0',0,0,0,NULL,'中国',NULL,'江苏','徐州',NULL,1807,'1',1586414372,1600932283,'2',0,NULL,0,0,'2','',NULL,0,2,'{\"a\": \"1\", \"kd\": 0, \"fkd\": 0, \"r_a\": \"0\", \"r_n\": 0, \"cs_kd\": 0, \"cs_fkd\": 0, \"klh_kd\": 0, \"klh_ra\": \"0\", \"klh_rn\": 0, \"klh_fkd\": 0, \"cs_klh_kd\": 0, \"cs_klh_fkd\": 0}');

/*Table structure for table `user_match` */

DROP TABLE IF EXISTS `user_match`;

CREATE TABLE `user_match` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `active_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '活动id',
  `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
  `match_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '场次id',
  `kandian` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '场次看点数',
  `match_date` char(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '场次时间',
  `period` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '打卡周期',
  `period_day` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '领奖周期',
  `num` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '打卡天数',
  `reward` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '领取看点数',
  `is_again` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '是否补签',
  `status` int(10) DEFAULT NULL COMMENT '1,报名，2打卡中，3完成可领奖，4已领奖，5失败，6完成未领奖',
  `updatetime` int(10) unsigned NOT NULL COMMENT '更新时间',
  `createtime` int(10) unsigned NOT NULL COMMENT '创建时间',
  `openid` char(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '微信openid',
  `channel_id` int(10) unsigned DEFAULT NULL COMMENT '渠道id',
  `endtime` int(10) unsigned DEFAULT NULL COMMENT '结束时间',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户报名记录表';

/*Data for the table `user_match` */

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;