聊聊数据库表结构设计心得

快来打我* 2023-03-01 05:41 11阅读 0赞

本文讨论是一般表的设计，有一定的普遍性和通用性，当然对于特殊性的考量则不在本文讨论之列。

## 自增 id ##

Java 层的 CRUD 都是围绕自增 id 的，以这个 id 为依据的，所以自增 id 不可或缺，每张表都应该有。当然其他类型的 id，如 uuid、雪花 id 都可以并存。

还有分页、表与表的关联都离不开这个自增 id。

## createDate & updateDate ##

有了这两个字段，你可以追溯到数据的时间点，创建和修改的时间点，以方便查找问题。MySQL 下类型为 datetime，在 Service 层控制生成。一般精确到秒即可，如果并发量大可以考虑毫秒级别的。

## creator & updater ##

同理，创建人& 修改人也是为了出问题的时候可以追溯起因用的，相当于日志的作用。但是这两个字段可以斟酌使用，因为涉及表关联（外连接到用户表）比较麻烦。

## 唯一标识 ##

特点是全局唯一，在处理通用表的时候很好用，例如附件表，一个实体可以拥有多个附件，即一对多，那么在附件表里面有个 ownerId 对应那个实体即可，无须额外增加字段再说明哪种类型。当然于附件表本身而言，他并不知道对应的类型，要靠实体主动关联它的时候，才晓得。

唯一标识生成算法很多，我们架构采用 Twitter 的“雪花算法”。

## 状态 status ##

如果不是物理删除某行记录，那么就在数据库上面标记之。如设一 `isDeleted` 字段，不是不行，是每张表都要那么做。还有我们的表多数时候考虑一个“上线/下线”的状态，获取其他特定的状态。那么何不干脆将它们合并之？统一一个 status 字段好了。MySQL `status` 是关键字，你可改名 `stat` 或转义。

MySQL 下设置 `TinyInt(1)` 类型即可，对应 Java 设计好的常量，例如 0/null = 正常/上线状态，1=下线，2=已删除，……=不断扩充。

## 表关联 ##

尽量不要有冗余的信息数据，否则你需要更新同一份信息的时候，需要更新多个地方。数据库三大范式能遵守就遵守，实在不行也不必过于拘泥，例如权衡性能的话，有时需要冗余部分数据。

## 外键、触发器 ##

外键是老一代的东东，不要为自己添加枷锁。触发器看情况，虽然大部分可以用 Java 代替之，但有时妙用也可以。

## 存储过程、函数 ##

老一代很喜欢用，现在通通给我用 Java 写。但有时 SQL 没法实现，就应该请益于它们了，例如 MySQL 递归查询。

## 分库分表 ##

这方面本人经验不是很多，——另请看高人的——  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neGluMDk_size_16_color_FFFFFF_t_70]  
出处 https://developer.aliyun.com/article/756689

## 其他杂项 ##

### 数据库表名，应该用复数还是单数？ ###

用单数形式更佳，参考 http://www.cnblogs.com/jiqing9006/p/4999670.html

### varchar(255) 还是 varchar(256)？ ###

tinyint 类型存储的最大数字是 255，诱导我们设置 varchar 时也不要突破 255，实际上 tinyint 存储的是 0-255 一共 256 个数字。  
但是，实际上建议使用 varchar(256)，因为这是一个字节的内容，微软设计的数据库中使用的就是 256，而不是 255。

### 数据库版本号 ###

参见：《数据库模型设计：历史与版本设计》 https://blog.csdn.net/studyzy/article/details/11524649

## 通用结构 ##

综上所述，给出表结构的通用字段如下。

CREATE TABLE `template` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键 id，自增',
      `name` varchar(45) DEFAULT NULL COMMENT '名称',
      `content` text COMMENT '内容',
      `uid` bigint(20) NOT NULL COMMENT '唯一 id，通过 uuid 生成不重复 id',
      `createByUser` int(11) DEFAULT NULL COMMENT '创建者 id',
      `createDate` datetime NOT NULL COMMENT '创建日期',
      `updateDate` datetime NOT NULL COMMENT '修改日期',
      `stat` tinyint(2) DEFAULT NULL COMMENT '是否已删除 1=已删除；0/null；未删除',
      `catelogId` int(11) DEFAULT NULL COMMENT '分类 id',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='模版结构';

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5neGluMDk_size_16_color_FFFFFF_t_70]: /images/20230209/df51f4f8adb447edacb0feb2300e7d59.png