反范式设计博客表结构 - RebuildBlog - Day2

今日代码，可以 checkout ff5e9bb

在这次重写博客时，设计数据库部分将考虑反范式设计，最关键一点就是舍弃所有外键，通过代码逻辑来保证表之间的联系。虽然我的博客可能没有多大访问量，但这个设计早已被验证。

关于 MySQL 表结构的设计和使用上的一些建议可参见书籍《高性能 MySQL(High Performance MySQL)》以及文档 启发式规则建议-By Xiaomi，最基本的一点就是，设计列名之前，先搜下名称是否在 MySQL 保留关键字里（不使用文档中标有 R 的词，慎重使用其他关键词）。

表设计构思

通用表设置

这里设置两个主要参数，一个是默认为 innoDB 引擎，另一个是设置字符集为utf8mb4，避免一些编码问题。

这两个设置放置在一个字典中，作为模型的表参数（__table_args__）

# Detail MySQL on SQLAlchemy, ref: https://docs.sqlalchemy.org/en/13/dialects/mysql.html
COMMON_TABLE_SETTINGS = {
    'mysql_charset': 'utf8mb4',
    'mysql_engine': 'InnoDB'
}

通用字段和抽象类

共有 5 张表，每张表都有 自增 id (unsigned bigint)、创建时间 (create_time)、更新时间 (update_time) 三个字段。其中更新时间将会自动随着记录更新而更新。

虽然 SQLAlchemy 的模型类可以对列设置 onupdate=func.now()，但根据其官方文档以及 db.create_all() 生成的表结构来看，这只是通过客户端生成的 SQL 来保证，表结构上的 update_time 并没有 ON UPDATE CURRENT_TIMESTAMP，这即是文档上所说的 Client-Invoked SQL Expressions，只是 DML，不会生成 DDL。 server_default nullable
这些参数就会生成 DDL。

既然是通用字段，自然而然地想到用一个基类来定义这些字段，其他模型继承即可。SQLAlchemy 设置抽象模型只需要简单地加上 __abstract__，在博客中用的是最简单的抽象方式，如果你想了解更高级的用法，可参见 Mixin and Custom Base Classes

三个通用字段的 DDL 表示：

-- 三个通用字段
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

对应的类设计：

class BaseModel(db.Model):
    __abstract__ = True
    __tablename__ = ''
    # Configuration Document: https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/table_config.html
    __table_args__ = ''
    id = db.Column(BIGINT(unsigned=True), primary_key=True, autoincrement=True)
    create_time = db.Column(db.DateTime, server_default=func.now())
    # This is a client-side update trigger.
    # Will not be reflect on db.
    update_time = db.Column(db.DateTime, server_default=func.now(), onupdate=func.now())

    def __repr__(self):
        pass

变更为默认自动更新的语句如下, 单独放在 setup.py 中与创建表一起执行

ALTER TABLE table_name CHANGE `update_time` `update_time` datetime 
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'

尽量选择无符号整型——包括 IP 地址

博客访问中暂时没有想到与计算相关且有意义的负值，因此整型都设置为无符号，扩大数值范围（指正数），而 IP 地址实际上可以通过移位操作来进行转化，MySQL、Python等也有内置的函数或库来进行 IP 地址和整数的转化。因此，在设计时，也把原先的 VARCHAR 类型改为 unsigned int，节省存储空间。

为所有字段设置默认值

除了文章表 body 字段为 TEXT 不能设置默认值外，其他表字段均设置默认值。需要注意的是，TEXT 不能设置默认值，因此也不设置 NOT NULL 限制。

逻辑上定长，物理上变长的密码字段

密码字段为了安全性考虑，存储的是哈希值，用了 VARCHAR(128)，通过类的设置密码方法使保存的密码都是定长的哈希值。

表设计落地

用户认证表——用于登录

CREATE TABLE `tbl_user_auth` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `password_hash` varchar(128) NOT NULL DEFAULT '',
  `email` varchar(128) NOT NULL DEFAULT '',
  `username` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `ix_user_auth_email` (`email`),
  KEY `ix_user_auth_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

用户信息表——公开展示的信息

不使用外键，通过代码逻辑来实现关联，下同。

CREATE TABLE `tbl_user_info` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `username` varchar(64) NOT NULL DEFAULT '',
  `country` varchar(8) DEFAULT '',
  `city` varchar(32) DEFAULT '',
  `avatar` varchar(128) DEFAULT '',
  `intro` varchar(128) DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `ix_user_info_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

文章表

由于 column 是 MySQL 的关键字，这里改用 section，由于专栏与文章是一对一，因此暂不对这个字段做冗余，只进行索引。

CREATE TABLE `tbl_article` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `username` varchar(64) NOT NULL DEFAULT '',
  `title` varchar(128) NOT NULL DEFAULT '',
  `body` text,
  `slug` varchar(256) NOT NULL DEFAULT '',
  `section` varchar(64) DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `ix_article_title` (`title`),
  KEY `ix_article_username` (`username`),
  KEY `ix_article_section` (`section`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

评论表

暂时保留评论体作为索引

CREATE TABLE `tbl_comment` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `username` varchar(64) NOT NULL DEFAULT '',
  `body` varchar(256) NOT NULL DEFAULT '',
  `ip` int(10) unsigned DEFAULT '0',
  `country_code` varchar(8) DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `ix_comment` (`body`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

标签表

与文章是多对多的关系，单独设置一张表来保存

CREATE TABLE `tbl_tag` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `tag` varchar(32) NOT NULL DEFAULT '',
  `username` varchar(64) DEFAULT '',
  `title` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `ix_tag_tag` (`tag`),
  KEY `ix_tag_title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

---

公众号：程序员的碎碎念