text: create database mqtt;
  use mqtt;
  
  CREATE TABLE `mqtt_user` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `username` varchar(100) DEFAULT NULL,
    `password` varchar(100) DEFAULT NULL,
    `salt` varchar(35) DEFAULT NULL,
    `is_superuser` tinyint(1) DEFAULT 0,
    `created` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `mqtt_username` (`username`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  
  -- 客户端信息
  INSERT INTO `mqtt_user` ( `username`, `password`, `salt`, `is_superuser`)
  VALUES
      ('emqx', 'efa1f375d76194fa51a3556a97e641e61685f914d446979da50a551a4333ffd7', NULL, 1);
  
  CREATE TABLE `mqtt_acl` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `allow` int(1) DEFAULT 1 COMMENT '0: deny, 1: allow',
    `ipaddr` varchar(60) DEFAULT NULL COMMENT 'IpAddress',
    `username` varchar(100) DEFAULT NULL COMMENT 'Username',
    `clientid` varchar(100) DEFAULT NULL COMMENT 'ClientId',
    `access` int(2) NOT NULL COMMENT '1: subscribe, 2: publish, 3: pubsub',
    `topic` varchar(100) NOT NULL DEFAULT '' COMMENT 'Topic Filter',
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  
  DROP TABLE IF EXISTS `mqtt_client`;
  CREATE TABLE `mqtt_client` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `clientid` varchar(64) DEFAULT NULL,
    `state` varchar(3) DEFAULT NULL,
    `node` varchar(64) DEFAULT NULL,
    `online_at` datetime DEFAULT NULL,
    `offline_at` datetime DEFAULT NULL,
    `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `mqtt_client_idx` (`clientid`),
    UNIQUE KEY `mqtt_client_key` (`clientid`),
    INDEX topic_index(`id`, `clientid`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8MB4;
  
  DROP TABLE IF EXISTS `mqtt_sub`;
  CREATE TABLE `mqtt_sub` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `clientid` varchar(64) DEFAULT NULL,
    `topic` varchar(180) DEFAULT NULL,
    `qos` tinyint(1) DEFAULT NULL,
    `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `mqtt_sub_idx` (`clientid`,`topic`,`qos`),
    UNIQUE KEY `mqtt_sub_key` (`clientid`,`topic`),
    INDEX topic_index(`id`, `topic`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8MB4;
  
  DROP TABLE IF EXISTS `mqtt_msg`;
  CREATE TABLE `mqtt_msg` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `msgid` varchar(64) DEFAULT NULL,
    `topic` varchar(180) NOT NULL,
    `sender` varchar(64) DEFAULT NULL,
    `node` varchar(64) DEFAULT NULL,
    `qos` tinyint(1) NOT NULL DEFAULT '0',
    `retain` tinyint(1) DEFAULT NULL,
    `payload` blob,
    `arrived` datetime NOT NULL,
    PRIMARY KEY (`id`),
    INDEX topic_index(`id`, `topic`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8MB4;
  
  DROP TABLE IF EXISTS `mqtt_retain`;
  CREATE TABLE `mqtt_retain` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `topic` varchar(180) DEFAULT NULL,
    `msgid` varchar(64) DEFAULT NULL,
    `sender` varchar(64) DEFAULT NULL,
    `node` varchar(64) DEFAULT NULL,
    `qos` tinyint(1) DEFAULT NULL,
    `payload` blob,
    `arrived` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `mqtt_retain_key` (`topic`),
    INDEX topic_index(`id`, `topic`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8MB4;
  
  DROP TABLE IF EXISTS `mqtt_acked`;
  CREATE TABLE `mqtt_acked` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `clientid` varchar(64) DEFAULT NULL,
    `topic` varchar(180) DEFAULT NULL,
    `mid` int(11) unsigned DEFAULT NULL,
    `created` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `mqtt_acked_key` (`clientid`,`topic`),
    INDEX topic_index(`id`, `topic`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8MB4;