yzncms_console/addons/member/install.sql

CREATE TABLE IF NOT EXISTS `__PREFIX__member` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `username` varchar(16) NOT NULL COMMENT '用户名',
  `nickname` varchar(16) NOT NULL DEFAULT '' COMMENT '昵称',
  `password` varchar(32) NOT NULL COMMENT '密码',
  `encrypt` varchar(6) DEFAULT NULL COMMENT '加密因子',
  `point` mediumint(8) NOT NULL DEFAULT '0' COMMENT '用户积分',
  `amount` decimal(8,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '钱金总额',
  `login` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '登录次数',
  `email` varchar(32) NOT NULL DEFAULT '' COMMENT '电子邮箱',
  `mobile` varchar(11) NOT NULL DEFAULT '' COMMENT '手机号',
  `gender` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '性别:0=未知,1=男,2=女',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `motto` varchar(255) DEFAULT '' COMMENT '签名',
  `avatar` varchar(255) NOT NULL DEFAULT '' COMMENT '头像',
  `groupid` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '用户组ID',
  `modelid` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '用户模型ID',
  `vip` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'VIP会员',
  `overduedate` int(10) unsigned DEFAULT NULL COMMENT '过期时间',
  `reg_ip` varchar(50) NOT NULL DEFAULT '' COMMENT '注册IP',
  `reg_time` int(10) unsigned DEFAULT NULL COMMENT '注册时间',
  `last_login_ip` varchar(50) NOT NULL DEFAULT '' COMMENT '最后登录IP',
  `last_login_time` int(10) unsigned DEFAULT NULL COMMENT '最后登录时间',
  `ischeck_email` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否验证过邮箱',
  `ischeck_mobile` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否验证过手机',
  `token` varchar(50) DEFAULT '' COMMENT 'Token',
  `status` tinyint(2) NOT NULL DEFAULT '0' COMMENT '状态',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `email` (`email`),
  KEY `mobile` (`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT='会员表';

CREATE TABLE IF NOT EXISTS `__PREFIX__member_group` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT COMMENT '会员组id',
  `name` varchar(15) NOT NULL COMMENT '用户组名称',
  `issystem` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否是系统组',
  `starnum` tinyint(2) unsigned NOT NULL COMMENT '会员组星星数',
  `point` smallint(6) unsigned NOT NULL COMMENT '积分范围',
  `allowmessage` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '许允发短消息数量',
  `allowvisit` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否允许访问',
  `allowpost` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否允许发稿',
  `allowpostverify` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否投稿不需审核',
  `allowsearch` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否允许搜索',
  `allowupgrade` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否允许自主升级',
  `allowsendmessage` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '允许发送短消息',
  `allowpostnum` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '每天允许发文章数',
  `allowattachment` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否允许上传附件',
  `price_y` decimal(8,2) unsigned NOT NULL DEFAULT '0.00',
  `price_m` decimal(8,2) unsigned NOT NULL DEFAULT '0.00',
  `price_d` decimal(8,2) unsigned NOT NULL DEFAULT '0.00',
  `icon` varchar(255) NOT NULL DEFAULT '' COMMENT '用户组图标',
  `usernamecolor` varchar(7) NOT NULL DEFAULT '' COMMENT '用户名颜色',
  `description` varchar(100) NOT NULL DEFAULT '' COMMENT '描述',
  `listorder` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
  `status` tinyint(2) NOT NULL DEFAULT '0' COMMENT '状态',
  `expand` mediumtext COMMENT '拓展',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT='会员组';

CREATE TABLE IF NOT EXISTS `__PREFIX__member_token` (
  `token` varchar(50) NOT NULL COMMENT 'Token',
  `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '会员ID',
  `create_time` int(10) unsigned DEFAULT NULL COMMENT '创建时间',
  `expire_time` int(10) unsigned DEFAULT NULL COMMENT '过期时间',
  PRIMARY KEY (`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT='会员Token表';

CREATE TABLE IF NOT EXISTS `__PREFIX__member_amount_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '会员ID',
  `amount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '变更余额',
  `before` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '变更前余额',
  `after` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '变更后余额',
  `remark` varchar(255) DEFAULT '' COMMENT '备注',
  `create_time` int(10) unsigned DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT='会员余额变动表';

CREATE TABLE IF NOT EXISTS `__PREFIX__member_point_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '会员ID',
  `point` int(10) NOT NULL DEFAULT '0' COMMENT '变更积分',
  `before` int(10) NOT NULL DEFAULT '0' COMMENT '变更前积分',
  `after` int(10) NOT NULL DEFAULT '0' COMMENT '变更后积分',
  `remark` varchar(255) DEFAULT '' COMMENT '备注',
  `create_time` int(10) unsigned DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT='会员积分变动表';

INSERT INTO `__PREFIX__member_group` VALUES ('1', '禁止访问', '1', '0', '0', '0', '1', '1', '0', '1', '0', '0', '0', '0','0.00','0.00', '0.00', '', '', '0', '10', '1', '');
INSERT INTO `__PREFIX__member_group` VALUES ('2', '新手上路', '1', '1', '50', '100', '1', '1', '0', '0', '1', '1', '0', '0','50.00', '10.00', '1.00' ,'', '', '', '8', '1', '');
INSERT INTO `__PREFIX__member_group` VALUES ('6', '注册会员', '1', '2', '100', '150', '0', '1', '0', '0', '1', '1', '0', '0','300.00', '30.00', '1.00', '', '', '', '7', '1', '');
INSERT INTO `__PREFIX__member_group` VALUES ('4', '中级会员', '1', '3', '150', '500', '1', '1', '0', '1', '1', '1', '0', '0','360.00', '60.00', '1.00', '', '', '', '6', '1', '');
INSERT INTO `__PREFIX__member_group` VALUES ('5', '高级会员', '1', '5', '300', '999', '1', '1', '0', '1', '1', '1', '0', '0','500.00', '90.00', '1.00', '', '', '', '5', '1', '');
INSERT INTO `__PREFIX__member_group` VALUES ('7', '邮件认证', '1', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0.00', '0.00', '0.00','', '#000000', '', '4', '1', '');
INSERT INTO `__PREFIX__member_group` VALUES ('8', '游客', '1', '0', '0', '0', '0', '0', '0', '1', '0', '0', '0', '0','0.00', '0.00', '0.00', '', '', '', '9', '1', '');

-- 1.1.0 --
ALTER TABLE `__PREFIX__member` ENGINE = InnoDB;
ALTER TABLE `__PREFIX__member_group` ENGINE = InnoDB;
ALTER TABLE `__PREFIX__member_token` ENGINE = InnoDB;
ALTER TABLE `__PREFIX__member_amount_log` ENGINE = InnoDB;
ALTER TABLE `__PREFIX__member_point_log` ENGINE = InnoDB;

-- 1.1.1 --
ALTER TABLE `__PREFIX__member` ADD COLUMN `gender` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '性别:0=未知,1=男,2=女' AFTER `mobile`,
ADD COLUMN `birthday` date DEFAULT NULL COMMENT '生日' AFTER `gender`,
ADD COLUMN `motto` varchar(255) DEFAULT '' COMMENT '签名' AFTER `birthday`;