92 lines
5.5 KiB
SQL
92 lines
5.5 KiB
SQL
-- wxshop.ab98_user definition
|
||
|
||
CREATE TABLE `ab98_user` (
|
||
`ab98_user_id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`openid` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'openid',
|
||
`userid` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '汇邦云用户唯一ID',
|
||
`qy_user_id` bigint DEFAULT NULL COMMENT '企业用户id',
|
||
`name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '真实姓名',
|
||
`tel` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '手机号码',
|
||
`idnum` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '身份证号码',
|
||
`sex` char(8) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '性别（男 女）',
|
||
`face_img` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '人脸照片地址',
|
||
`idcard_front` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '身份证正面地址',
|
||
`idcard_back` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '身份证背面地址',
|
||
`address` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '身份证登记地址',
|
||
`registered` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已注册（0未注册 1已注册）',
|
||
`creator_id` bigint DEFAULT '0' COMMENT '创建者ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updater_id` bigint DEFAULT '0' COMMENT '更新者ID',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志（0存在 1删除）',
|
||
`ab98_balance` int NOT NULL DEFAULT '0' COMMENT '用户余额（单位：分）',
|
||
PRIMARY KEY (`ab98_user_id`),
|
||
UNIQUE KEY `uk_idnum` (`idnum`),
|
||
KEY `idx_openid` (`openid`),
|
||
KEY `idx_tel` (`tel`),
|
||
KEY `idx_name` (`name`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=87 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='汇邦云用户信息表';
|
||
|
||
|
||
-- wxshop.user_balance definition
|
||
|
||
CREATE TABLE `user_balance` (
|
||
`user_balance_id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`corpid` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '企业微信id',
|
||
`openid` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'openid',
|
||
`ab98_user_id` bigint NOT NULL COMMENT '汇邦云用户ID',
|
||
`qy_user_id` bigint DEFAULT NULL COMMENT '企业用户id',
|
||
`balance` bigint NOT NULL DEFAULT '0' COMMENT '用户余额（单位：分）',
|
||
`use_balance` bigint NOT NULL DEFAULT '0' COMMENT '用户余额（单位：分）',
|
||
`balance_limit` bigint NOT NULL DEFAULT '0' COMMENT '用户余额（单位：分）',
|
||
`creator_id` bigint DEFAULT '0' COMMENT '创建者ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updater_id` bigint DEFAULT '0' COMMENT '更新者ID',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志（0存在 1删除）',
|
||
PRIMARY KEY (`user_balance_id`),
|
||
UNIQUE KEY `uk_corpid_user` (`corpid`,`ab98_user_id`),
|
||
KEY `idx_openid` (`openid`),
|
||
KEY `idx_ab98_user_id` (`ab98_user_id`),
|
||
KEY `idx_corpid` (`corpid`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=137 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户余额表';
|
||
|
||
|
||
-- wxshop.wx_user definition
|
||
|
||
CREATE TABLE `wx_user` (
|
||
`wx_user_id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`openid` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'openid',
|
||
`ab98_user_id` bigint DEFAULT NULL COMMENT '汇邦云用户ID',
|
||
`qy_user_id` bigint DEFAULT NULL COMMENT '企业用户id',
|
||
`nick_name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '昵称',
|
||
`tel` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '手机号码',
|
||
`wx_balance` int NOT NULL DEFAULT '0' COMMENT '用户余额（单位：分）',
|
||
`creator_id` bigint DEFAULT '0' COMMENT '创建者ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updater_id` bigint DEFAULT '0' COMMENT '更新者ID',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志（0存在 1删除）',
|
||
`avatar` tinytext COLLATE utf8mb4_unicode_ci COMMENT '用户头像',
|
||
PRIMARY KEY (`wx_user_id`),
|
||
KEY `idx_openid` (`openid`),
|
||
KEY `idx_tel` (`tel`),
|
||
KEY `idx_nick_name` (`nick_name`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=151 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='微信用户信息表';
|
||
|
||
|
||
-- wxshop.ab98_user_tag definition
|
||
|
||
CREATE TABLE `ab98_user_tag` (
|
||
`tag_id` bigint NOT NULL AUTO_INCREMENT COMMENT '标签ID',
|
||
`ab98_user_id` bigint NOT NULL COMMENT '关联用户ID',
|
||
`tag_name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '标签名称',
|
||
`creator_id` bigint DEFAULT '0' COMMENT '创建者ID',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updater_id` bigint DEFAULT '0' COMMENT '更新者ID',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标志（0存在 1删除）',
|
||
PRIMARY KEY (`tag_id`),
|
||
KEY `idx_user` (`ab98_user_id`),
|
||
CONSTRAINT `fk_tag_user` FOREIGN KEY (`ab98_user_id`) REFERENCES `ab98_user` (`ab98_user_id`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户标签表'; |