51 lines
2.5 KiB
SQL
51 lines
2.5 KiB
SQL
DROP TABLE IF EXISTS `ab98_user`;
|
||
|
||
CREATE TABLE `ab98_user` (
|
||
`ab98_user_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`openid` VARCHAR(32) NOT NULL COMMENT 'openid',
|
||
`userid` VARCHAR(100) NOT NULL COMMENT '汇邦云用户唯一ID',
|
||
`name` VARCHAR(50) NOT NULL COMMENT '真实姓名',
|
||
`tel` VARCHAR(20) NOT NULL COMMENT '手机号码',
|
||
`idnum` VARCHAR(20) NOT NULL COMMENT '身份证号码',
|
||
`sex` CHAR(8) NOT NULL COMMENT '性别（男 女）',
|
||
`face_img` VARCHAR(500) NOT NULL COMMENT '人脸照片地址',
|
||
`idcard_front` VARCHAR(500) COMMENT '身份证正面地址',
|
||
`idcard_back` VARCHAR(500) COMMENT '身份证背面地址',
|
||
`address` VARCHAR(200) 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删除）',
|
||
PRIMARY KEY (`ab98_user_id`),
|
||
KEY `idx_openid` (`openid`),
|
||
KEY `idx_tel` (`tel`),
|
||
KEY `idx_name` (`name`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='汇邦云用户信息表';
|
||
|
||
|
||
ALTER TABLE `smart_cabinet`
|
||
ADD COLUMN `main_cabinet` BIGINT NULL COMMENT '归属主柜ID'
|
||
AFTER `cabinet_type`;
|
||
|
||
DROP TABLE IF EXISTS `cabinet_mainboard`;
|
||
|
||
CREATE TABLE `cabinet_mainboard` (
|
||
`mainboard_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主板唯一ID',
|
||
`cabinet_id` BIGINT NOT NULL COMMENT '关联柜机ID',
|
||
`lock_control_no` INT NOT NULL COMMENT '锁控板序号',
|
||
`creator_id` BIGINT NULL DEFAULT 0 COMMENT '创建者ID',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updater_id` BIGINT NULL 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 (`mainboard_id`),
|
||
KEY `idx_cabinet` (`cabinet_id`),
|
||
CONSTRAINT `fk_mainboard_cabinet` FOREIGN KEY (`cabinet_id`) REFERENCES `smart_cabinet` (`cabinet_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='机柜主板信息表';
|
||
|
||
ALTER TABLE `cabinet_cell`
|
||
ADD COLUMN `mainboard_id` BIGINT NULL COMMENT '归属主板ID'
|
||
AFTER `cabinet_id`;
|