第18章-数据库创建

• 表RouteData: 保存了所有mod路由信息. | 字段 | 数据类型 | 是否可以为空 | 主键 | 默认 | 附加 | 说明 | | —- | —- | —- | —- | —- | —- | —- | | id | int(10) unsigned | No | 是 | NULL | 自增长 | 该条数据ID | | modid | int(10) unsigned | No | | NULL | | 模块ID | | cmdid | int(10) unsigned | No | | NULL | | 指令ID | | serverip | int(10) unsigned | No | | NULL | | 服务器IP地址 | | serverport | int(10) unsigned | No | | NULL | | 服务器端口 |

• 表RouteVersion: 当前RouteData路由版本号，每次管理端修改某mod的路由，RouteVersion表中的版本号都被更新为当前时间戳 | 字段 | 数据类型 | 是否可以为空 | 主键 | 默认 | 附加 | | —- | —- | —- | —- | —- | —- | | id | int(10) unsigned | No | 是 | NULL | 自增长 | | version | int(10) unsigned | No | | NULL | |

• 表RouteChange: 每次管理端修改某mod的路由，会记录本次对哪个mod进行修改（增、删、改），以便指示最新的RouteData路由有哪些mod变更了。 | 字段 | 数据类型 | 是否可以为空 | 主键 | 默认 | 附加 | | —- | —- | —- | —- | —- | —- | | id | int(10) unsigned | No | 是 | NULL | 自增长 | | modid | int(10) unsigned | No | | NULL | | | cmdid | int(10) unsigned | No | | NULL | | | version | bigint(20) unsigned | No | | NULL | |

相关创建表格的sql语句如下lars_dns.sql

DROP DATABASE if exists lars_dns;
CREATE DATABASE lars_dns;
USE lars_dns;
DROP TABLE IF EXISTS `RouteData`;
CREATE TABLE `RouteData` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `modid` int(10) unsigned NOT NULL,
    `cmdid` int(10) unsigned NOT NULL,
    `serverip` int(10) unsigned NOT NULL,
    `serverport` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=116064 DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `RouteVersion`;
CREATE TABLE RouteVersion (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `version` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`)
);
INSERT INTO RouteVersion(version) VALUES(0);
DROP TABLE IF EXISTS `RouteChange`;
CREATE TABLE RouteChange (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `modid` int(10) unsigned NOT NULL,
    `cmdid` int(10) unsigned NOT NULL,
    `version` bigint(20) unsigned NOT NULL,
    PRIMARY KEY (`id`)
);

    我们创建一个基础目录`Lars/base`来存放一些公共的工具和资源.

cd到Lars/base, 我们mkdir sql， 然后将lars_dns.sql拷贝到sql/文件夹下。

然后执行创建表格

$mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.7.27-0ubuntu0.18.04.1 (Ubuntu)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> \. lars_dns.sql
Query OK, 0 rows affected, 1 warning (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Database changed
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.08 sec)
Query OK, 0 rows affected, 1 warning (0.01 sec)
Query OK, 0 rows affected (0.06 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 0 rows affected, 1 warning (0.01 sec)
Query OK, 0 rows affected (0.13 sec)