table definition

table.sql

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `member_id` bigint(20) DEFAULT NULL COMMENT '用户号以及推荐码,对外都应该只暴露本字段而不是id',
  `phone` varchar(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `is_valid` tinyint(1) NOT NULL DEFAULT '0',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_member_id` (`member_id`),
  UNIQUE KEY `idx_phone` (`phone`),
  UNIQUE KEY `idx_email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=10153699 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_profile` (
  `user_id` bigint(20) NOT NULL,
  `nickname` varchar(50) DEFAULT NULL,
  `avatar` varchar(200) DEFAULT NULL,
  `background_image_path` varchar(200) DEFAULT NULL,
  `self_introduction` varchar(200) DEFAULT NULL,
  `renamed` tinyint(1) NOT NULL DEFAULT '0',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `idx_nickname` (`nickname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_status` (
  `user_id` bigint(20) NOT NULL,
  `id_verify` varchar(20) NOT NULL DEFAULT 'NOT_VERIFIED',
  `redo_id_verify` varchar(20) NOT NULL DEFAULT 'NORMAL',
  `has_pay_password` tinyint(1) NOT NULL DEFAULT '0',
  `has_password` tinyint(1) NOT NULL DEFAULT '0',
  `has_phone` tinyint(1) NOT NULL DEFAULT '0',
  `has_email` tinyint(1) NOT NULL DEFAULT '0',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_stat` (
  `user_id` bigint(20) NOT NULL,
  `follows` int(11) NOT NULL DEFAULT '0' COMMENT '关注数',
  `fans` int(11) NOT NULL DEFAULT '0' COMMENT '粉丝数',
  `subscribes` int(11) NOT NULL DEFAULT '0' COMMENT '收藏板块数',
  `articles` int(11) NOT NULL DEFAULT '0' COMMENT '收藏文章数',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_recommend` (
  `user_id` bigint(20) NOT NULL,
  `recommend_user_id` bigint(20) NOT NULL,
  `activity_id` bigint(20) DEFAULT NULL,
  `rewarded` tinyint(1) NOT NULL DEFAULT '0',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  KEY `idx_recommend_user_id` (`recommend_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_id_verify` (
  `user_id` bigint(20) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `id_number` varchar(50) DEFAULT NULL,
  `id_pic_front` varchar(200) DEFAULT NULL,
  `id_pic_back` varchar(200) DEFAULT NULL,
  `id_pic_face` varchar(200) DEFAULT NULL,
  `status` varchar(20) NOT NULL,
  `message` varchar(300) DEFAULT NULL,
  `ticket_id` varchar(40) DEFAULT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  KEY `idx_id_number` (`id_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_password` (
  `user_id` bigint(20) NOT NULL,
  `salt` varchar(32) DEFAULT NULL,
  `password` varchar(128) DEFAULT NULL COMMENT '登录密码',
  `pay_password` varchar(128) DEFAULT NULL COMMENT '支付密码',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_verify` (
  `user_id` bigint(20) NOT NULL,
  `verify_type` varchar(30) NOT NULL,
  `verify_title` varchar(100) DEFAULT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;