toktok_stores/sp-parent/sp-module-account-core/doc/db.sql

CREATE TABLE `account` (
  `account_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '账户编号',
  `phone` varchar(11) NOT NULL COMMENT '手机号码',
  `register_type` tinyint(4) NOT NULL COMMENT '注册房事, 见RegisterTypeEnum',
  `os` varchar(32) NOT NULL COMMENT '操作系统Android/iOS',
  `market` varchar(32) NOT NULL COMMENT '应用市场',
  `source` varchar(32) DEFAULT NULL COMMENT '注册来源',
  `state` tinyint(4) NOT NULL COMMENT '帐号状态, 见AccountStateEnum',
  `gmt_block_begin` DATETIME DEFAULT NULL COMMENT '帐号封禁开始时间',
  `gmt_block_end` DATETIME DEFAULT NULL COMMENT '帐号封禁截止时间',
  `gmt_create` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `gmt_modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`account_id`),
  UNIQUE KEY `uq_phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8mb4 COMMENT='帐号信息表';


CREATE TABLE `account_third_wechat` (
   `openid` varchar(48) NOT NULL COMMENT '微信openid',
   `nickname` varchar(32) DEFAULT NULL COMMENT '微信昵称',
   `sex` tinyint(4) DEFAULT NULL COMMENT '性别 1-男  2-女 其他-未知',
   `headimgurl` varchar(512) DEFAULT NULL COMMENT '微信头像',
   `city` varchar(32) DEFAULT NULL COMMENT '城市',
   `province` varchar(32) DEFAULT NULL COMMENT '省份',
   `country` varchar(32) DEFAULT NULL COMMENT '国家',
   `unionid` varchar(48) DEFAULT NULL COMMENT '微信unionid',
   `account_id` bigint(20) unsigned NOT NULL COMMENT '账户编号',
   `gmt_create` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
   `gmt_modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
   PRIMARY KEY (`openid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='帐号微信授权表';


CREATE TABLE `account_third_apple` (
    `sub` varchar(64) NOT NULL COMMENT '苹果身份标识',
    `user_id` varchar(64) DEFAULT NULL COMMENT '苹果用户id',
    `email` varchar(64) DEFAULT NULL COMMENT '邮箱',
    `account_id` bigint(20) unsigned NOT NULL COMMENT '账户编号',
    `gmt_create` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `gmt_modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`sub`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='帐号苹果授权表';


CREATE TABLE `account_login` (
   `login_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '登录编号',
   `account_id` bigint(20) unsigned NOT NULL COMMENT '账户编号',
   `date` varchar(10) NOT NULL COMMENT '登录日期yyyy-MM-dd',
   `os` varchar(32) NOT NULL COMMENT '操作系统Android/iOS',
   `market` varchar(32) NOT NULL COMMENT '应用市场',
   `isp` varchar(16) NOT NULL COMMENT '运营商',
   `client` varchar(32) NOT NULL COMMENT '客户端类型',
   `app_version` int(4) unsigned NOT NULL COMMENT 'APP版本号',
   `ip` int(4) NOT NULL DEFAULT 0 COMMENT '登录IP',
   `gmt_create` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
   `gmt_modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
   PRIMARY KEY (`login_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000001 DEFAULT CHARSET=utf8mb4 COMMENT='帐号登录表';


CREATE TABLE `account_device` (
     `device_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '设备编号',
     `device_no` varchar(64) NOT NULL COMMENT '客户端生成唯一码(UUID32位大写)',
     `idfa` varchar(64) DEFAULT NULL COMMENT '苹果IDFA(广告标识符)',
     `imei` varchar(64) DEFAULT NULL COMMENT '登录渠道',
     `oaid` varchar(64) DEFAULT NULL COMMENT '运营商',
     `androidid` varchar(64) DEFAULT NULL COMMENT '网络类型',
     `os` varchar(32) NOT NULL COMMENT '操作系统Android/iOS',
     `market` varchar(32) NOT NULL COMMENT '应用市场',
     `isp` varchar(16) NOT NULL COMMENT '运营商',
     `client` varchar(32) NOT NULL COMMENT '客户端类型',
     `app_version` int(4) unsigned NOT NULL COMMENT 'APP版本号',
     `ip` int(4) NOT NULL DEFAULT 0 COMMENT '登录IP',
     `account_id` bigint(20) DEFAULT NULL COMMENT '账户编号',
     `source` varchar(32) DEFAULT NULL COMMENT '注册来源',
     `gmt_create` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
     `gmt_modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`device_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000001 DEFAULT CHARSET=utf8mb4 COMMENT='帐号设备表';