#####日期:2020-01-08
#####提交人:黄怀平
#####sql解决问题: 添加member_member中若干字段(思拓用户同步),新增思拓站点表,积分表,认证表;
#####sql语句

ALTER TABLE `member_member` add COLUMN `thistimeLoginTime` timestamp DEFAULT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '本次登录时间' ;
ALTER TABLE `member_member` add COLUMN `thistimeLoginIp` int(11) DEFAULT NULL COMMENT '本次登录IP' ;
ALTER TABLE `member_member` add COLUMN `lasttimeLoginIp` int(11) DEFAULT NULL COMMENT '上次登录IP' ;
ALTER TABLE `member_member` add COLUMN `salt` char(16) DEFAULT NULL COMMENT '盐值' ;
ALTER TABLE `member_member` add COLUMN `certState` smallint(6) DEFAULT NULL COMMENT '1 已认证、0 未认证、2 等待认证、-1 未通过' ;
ALTER TABLE `member_member` add COLUMN `memberRejectId` int(10) DEFAULT NULL COMMENT '驳回理由id' ;
ALTER TABLE `member_member` add COLUMN `type` VARCHAR(100) DEFAULT NULL COMMENT '用户类型，注册用户还是第三方账户(regist,social,api)' ;

#####sql解决问题: 新增member_cert表(思拓用户认证资料)
#####sql语句
CREATE TABLE `member_cert` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `memberid` int(10) unsigned NOT NULL COMMENT 'member的id',
  `idCardNumber` varchar(100) NOT NULL COMMENT '身份证号',
  `idCardImages` text NOT NULL COMMENT '身份证图片 json, zm:正面,fm:反面,sc:手持',
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `memberid` (`memberid`),
  KEY `index_id_card_number` (`idCardNumber`)
) ENGINE=InnoDB AUTO_INCREMENT=277275 DEFAULT CHARSET=utf8mb4 COMMENT='member_cert 用户认证资料';


#####sql解决问题: 新增member_detail表(用户详细信息表,记录用户性别,生日等)
#####sql语句
CREATE TABLE `member_detail` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增',
  `memberid` mediumint(8) unsigned NOT NULL COMMENT '关联用户member_basic表ID',
  `truename` varchar(128) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '用户真实名',
  `gender` varchar(20) CHARACTER SET utf8 NOT NULL DEFAULT '未知' COMMENT '性别',
  `address` varchar(256) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '用户地址',
  `province` varchar(100) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '省',
  `city` varchar(100) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '市',
  `area` varchar(100) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '区',
  `year` int(4) unsigned NOT NULL DEFAULT '1900' COMMENT '出生年份',
  `month` tinyint(2) unsigned zerofill NOT NULL DEFAULT '01' COMMENT '出生月份',
  `day` tinyint(2) unsigned zerofill NOT NULL DEFAULT '01' COMMENT '出生日',
  `credits` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '会员积分',
  `registSiteid` int(11) NOT NULL DEFAULT '0' COMMENT '注册时的站点ID',
  `registIp` varchar(30) NOT NULL DEFAULT '0' COMMENT '用户注册时的IP',
  `registTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '用户注册时间',
  `registDevice` varchar(30) NOT NULL DEFAULT 'pc' COMMENT '用户注册账号平台 ',
  PRIMARY KEY (`id`),
  UNIQUE KEY `INDEX_memberid` (`memberid`)
) ENGINE=InnoDB AUTO_INCREMENT=4289661 DEFAULT CHARSET=utf8mb4 COMMENT='用户详细信息表，记录用户性别、生日等。';


#####sql解决问题: 新增member_site表(记录用户和站点的对应关系,用户登录过哪些站点,则为该站点的用户)
#####sql语句
CREATE TABLE `member_site` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `memberid` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
  `siteid` mediumint(8) NOT NULL DEFAULT '0' COMMENT '站点id',
  `accessTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '用户首次访问该站点的时间',
  `integral` int(10) DEFAULT '0' COMMENT '站点积分',
  `status` tinyint(2) DEFAULT '1' COMMENT '站点用户状态',
  PRIMARY KEY (`id`),
  UNIQUE KEY `U_INDEX_m_s` (`memberid`,`siteid`),
  KEY `memberid` (`memberid`),
  KEY `siteid` (`siteid`)
) ENGINE=InnoDB AUTO_INCREMENT=4635788 DEFAULT CHARSET=utf8mb4 COMMENT='用户和站点的对应关系，用户登录过哪些站点，则为该站点的用户。';


#####sql解决问题: 新增integral_log表(记录用户积分消费过程)
#####sql语句
CREATE TABLE `integral_log` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `memberid` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
  `siteid` mediumint(8) NOT NULL DEFAULT '0' COMMENT '站点id',
  `operationTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
  `integral` int(10) NOT NULL DEFAULT '0' COMMENT '操作积分（正数为增，负数为减）',
  `operationType` varchar(11) NOT NULL COMMENT '操作类型（1为加，0为减）',
  `type` varchar(11) NOT NULL COMMENT '类型',
  PRIMARY KEY (`id`),
  KEY `memberid` (`memberid`),
  KEY `siteid` (`siteid`)
) ENGINE=InnoDB AUTO_INCREMENT=4635788 DEFAULT CHARSET=utf8mb4 COMMENT='用户和站点的对应关系，用户登录过哪些站点，则为该站点的用户。';



#####日期:2020-01-15
#####提交人:黄怀平
#####sql解决问题: 设置member_member中性别默认值;
#####sql语句

ALTER TABLE member_member  CHANGE `sex` `sex` INT(1) DEFAULT 0  COMMENT '性别（0：未知；1：男； 2：女）';

INSERT INTO `member_version` (`version`, `update_time`) VALUES ('2.5.2.2.2', NOW());



 #####日期:2020-02-18
#####提交人:黄怀平
#####sql解决问题: 设置member_member中thistimeLoginTime默认值;
#####sql语句
UPDATE member_member set thistimeLoginTime = "2020-02-02 00:00:00";
alter table member_member alter column thistimeLoginTime set default  "2020-02-02 00:00:01";


#####日期:2020-02-24
#####提交人:黄怀平
#####sql解决问题: 针对思拓更改ip字段类型;
#####sql语句
alter table member_member drop column thistimeLoginIp;
alter table member_member drop column lasttimeLoginIp;
alter table member_member add  thistimeLoginIp  bigint;
alter table member_member add  lasttimeLoginIp  bigint;

INSERT INTO `member_version` (`version`, `update_time`) VALUES ('2.5.2.2.3', NOW());