com.zhuang.mica.mqtt.db.create.mysql.sql Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of my-mica-mqtt Show documentation
my-mica-mqtt
The newest version!
DROP TABLE IF EXISTS `sys_device`;
CREATE TABLE `sys_device`
(
    `device_id`     varchar(50) NOT NULL COMMENT '设备ID',
    `device_code`   varchar(50) NULL COMMENT '设备编号',
    `device_name`   varchar(50) NOT NULL COMMENT '设备名称',
    `device_remark` varchar(500)         DEFAULT NULL COMMENT '设备备注',
    `username`      varchar(50) NOT NULL COMMENT '登录用户',
    `password`      varchar(50) NOT NULL COMMENT '登录密码',
    `online`        tinyint(4)           DEFAULT NULL COMMENT '是否在线',
    `status`        tinyint(4)  NOT NULL DEFAULT '1' COMMENT '状态：0=禁用；1=启用',
    `create_by`     varchar(50)          DEFAULT NULL COMMENT '创建人ID',
    `create_time`   datetime             DEFAULT NULL COMMENT '创建时间',
    `modify_by`     varchar(50)          DEFAULT NULL COMMENT '修改人ID',
    `modify_time`   datetime             DEFAULT NULL COMMENT '修改时间',
    PRIMARY KEY (`device_id`),
    KEY `idx_device_code` (`device_code`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8 COMMENT ='设备资料';


DROP TABLE IF EXISTS `sys_device_function_log`;
CREATE TABLE `sys_device_function_log`
(
    `id`             int(11)    NOT NULL AUTO_INCREMENT,
    `device_id`      varchar(50) NOT NULL COMMENT '设备ID',
    `function_id`     varchar(50) DEFAULT NULL COMMENT '命令Id',
    `function_name`   varchar(50) DEFAULT NULL COMMENT '命令名字',
    `function_type`   varchar(50) DEFAULT NULL COMMENT '命令类型',
    `send_value`     text COMMENT '命名下发值',
    `send_time`      datetime    DEFAULT NULL COMMENT '下发时间',
    `reply_value` text COMMENT '命令响应值',
    `reply_result`  int(11)     DEFAULT NULL COMMENT '命令响应结果：0=失败；1=成功；',
    `reply_time`  datetime    DEFAULT NULL COMMENT '响应时间',
    `create_by`      varchar(64) DEFAULT NULL COMMENT '创建人',
    `create_time`    datetime    DEFAULT NULL COMMENT '创建时间',
    PRIMARY KEY (`id`) USING BTREE,
    KEY `idx_device_id` (`device_id`),
    KEY `idx_function_id` (`function_id`),
    KEY `idx_send_time` (`send_time`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8 COMMENT ='设备指令日志';