mysql表字段设计
最后更新于：2022-04-02 04:31:53
## mysql表字段设计
**VARCHAR与CHAR如何选择**
参考：[MySQL数据库中CHAR与VARCHAR之争 - 站长之家](http://www.chinaz.com/program/2011/0503/176896.shtml)
总结：
**使用VARCHAR理由**
1. 字段不经常更新
2. 字段比较长，且长度不均（比如用户留言，有的人长有的人短）
3. 不用再检索列
**使用CHAR的理由**
1. 字段不是很长，且长度都比较均匀（比如用户名）
2. ……
* * * * *
### 条目表 和 json字段 的使用
> 不会再更改的信息，可以放在json字段中，否则的话，还是要用条目表。和不被查询，即不与其它数据有关联的，也可以用json字段
```sql
-- ----------------------------
-- Table structure for `fxz_dinne_card_mould_item`
-- ----------------------------
DROP TABLE IF EXISTS `fxz_dinne_card_mould_item`;
CREATE TABLE `fxz_dinne_card_mould_item` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`card_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '对应套餐卡模板ID',
`service_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '对应服务ID',
`num` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '总次数',
`price` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '价值-用于和商家结算（指导价格）',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='套餐卡模板服务条目 表 （其实可以像订单表一样，无需这个表也可以）';
-- ----------------------------
-- Table structure for `fxz_dinne_card_order`
-- ----------------------------
DROP TABLE IF EXISTS `fxz_dinne_card_order`;
CREATE TABLE `fxz_dinne_card_order` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`user_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '用户ID',
-- 用这些信息为用户生成订单，这些信息，套餐卡模板可能会改变，但是改变不能够影响用户的订单，所以这些信息必须此时固化到订单信息中，仔细注意着点，这很重要！
-- 不会再更改的信息，可以放在json字段中，否则的话，还是要用条目表
`dinne_info_json` text NULL COMMENT '套餐卡模板信息',
`server_info_json` text NULL COMMENT '套餐卡模板服务条目信息',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '创建时间',
`pay_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '支付完成时间',
`amount` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '应付金额',
`status` tinyint(4) NOT NULL DEFAULT 0 COMMENT '状态，0-待支付，1-已支付',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='套餐卡订单 表';
```
* * * * *
### 冗余设计 与 业务逻辑
> 很多时候，冗余设计可以让业务逻辑更方便的实现
```php
/* START ######################## 用户可用卡信息 ######################## */
$cardServer = [];
// 使用时，我们并不关心，是用了哪一张套餐卡，我们只管里面的条目够用就行，换句话说，套餐卡是可以跨卡使用的
$on = 'dinne_card_item.service_id = platform_service.id';
$cardServer = Model()->table('dinne_card_item,platform_service')->join('left')->on($on)->field('platform_service.id,platform_service.name,SUM(dinne_card_item.surplus) AS surplus')->where(['dinne_card_item.status' => 0, 'dinne_card_item.user_id' => $userInfo['member_id']])->group('dinne_card_item.service_id')->select();
// 注意这里的 SUM(dinne_card_item.surplus) AS surplus
```
```sql
-- ----------------------------
-- Table structure for `fxz_dinne_card_item`
-- ----------------------------
DROP TABLE IF EXISTS `fxz_dinne_card_item`;
CREATE TABLE `fxz_dinne_card_item` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`card_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '对应套餐卡ID',
-- 卡就是服务
`service_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '对应服务ID',
`user_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '所属用户',
`num` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '总次数',
`use_num` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '已使用次数',
-- 很多时候，冗余设计可以让业务逻辑更方便的实现
`surplus` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '剩余次数',
`update_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '最后更新（使用）时间',
`price` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '价值-用于和商家结算（指导价格）',
`status` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '状态，0-正常，1-已用完，2-已过期',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='套餐卡服务条目 表 套餐卡内包含的服务（只用于标准服务）';
```
* * * * *
### 要注意的一些问题
**注意文本字段**
```
-- 文本的要设置为允许为空，因为它不能设置默认值（实际上默认值就是NULL），如果插入数据时没有值会报错的（没有值时就用缺省值，而缺省值就是默认值）
`content` longtext NULL COMMENT 'content',
`pic` text NULL COMMENT '相册，使用,分割多图',
```
**注意NULL和唯一索引问题**
> 唯一索引不约束 null
```
-- 这个用户邮箱和手机是唯一的，但是有的人开始并没有绑定邮箱或手机，所以也不能为空串，不然唯一冲突，所以只能允许为空null了，并且默认为null
`email` char(32) NULL DEFAULT NULL COMMENT '用户邮箱',
……
UNIQUE KEY `email` (`email`) USING BTREE,
```
**默认值问题**
一定要为字段设置默认值，始终要设置默认值，除非是想面提到的文本字段不能设置默认值的情况
**并且php中获取参数，也要有默认值：**
```
$request->param('status/d', 0)
```
不然获取到的值可能为null，而数据库字段NOT NULL那么就会出错。
请严格准守这些规则，任何时候，否则看这样虽然起来不会有什问题，但是在生产环境中就会出现很多很多的问题。
* * * * *
### 数据库名词解析
数据库是软件，功能是提供存储和查询的服务，一般称为存取服务。
数据库软件通常是以一个实例的方式呈现。
一个数据库就是一个实例。
一个实例就是一个数据库软件。
一个实例上可以创建多个数据库（这个数据库不是上面提高的软件的意思，而是业务数据库）。
一个数据库内有多张表。
一个实例可以有多个用户账号，这些账号就是实例的管理用户，也是连接账号。
* * * * *
### 边缘业务逻辑表
对于**边缘业务逻辑**（临时的需求），比如临时的新功能（不是系统主功能，没有也没关系），记录用户有没有查看过我们新的欢迎页面（用cookie是不行的，用户清除就又要看一次了），有没有打开某个功能页面等等这样临时的需求，可能只用那么一两次的业务。这种业务表可以使用临时边缘表来做。用 `edgetem_` 做表前缀。
* * * * *
### 业务与字段 表设计经验
订单条目表，存商品的名称，图片，规格，是防止商品更改了。当时数据和以后数据可能不统一。
而一些关联关系中，只存原始ID，这是说明，数据统一性比较强，数据从原始数据处获取，原始数据更改了，所有地方都不一样，统一了数据，这种有时候要求，原始数据不能随意经常更改，删除，易造成业务不稳定（如需改变，请直接新增，尽量避免删除和改动数据），这类数据往往是系统设置的一些信息。
用id统一性，如果不统一，那么就会出现，洗车卡可以扣减打蜡的服务了，因为我们判断用户是否有这个服务对应的卡是按照服务id来的，如果不统一，后来系统服务名称发生改变后，就会出现用户卡和系统卡两个地方名称不一致的问题。
* * * * *
### 扩展信息表
防止一个表字段太多，有时可以拆分到另一个扩展信息表里面去。
* * * * *
### 外键设计技巧与规范
为了实现灵活的sql，满足业务要求，以及后期发展，所以开始在设计表外键关系时就一定要遵守规范。
#### 一对一
一个用户只有一个身份证，用户表，身份证表，显然用户表为主，身份证表为从。
外键设计在身份证表中，user_id，而不要将外键身份证ID设计在用户表中。
所以遵循的规则就是，外键要设计在从表上。
#### 一对多
一个用户有多张银行卡，用户表，银行卡表。用户表为主，银行卡表为从。
外键设计在银行卡表中，user_id，这样就能轻易实现一对多的关系了。
规训规则还是外键设计在从表上，而不是在主表上设计逗号分隔的银行卡ID。
（其实和一对一的设计方式是一样的，一对一的关系我们不在外键中约束，而是在业务逻辑中约束。）
#### 多对多
一个用户属于多个用户组，一个用户组可以包含多个用户。
用户组表为主，用户表为从。（但此时主从关系就不在重要了。）
**有两种方案：**
1. 使用关联表，并且用户id和用户组id组成唯一联合索引。
2. 使用逗号分隔ID列表的方式。这个外键可以放在用户表中，也可以放在用户组表中。（通常取决于更短小的分隔串）
方案一是标准的方案，但是要多增加一张表。方案二更简单，但是有局限性，因为要保证短小的分隔串，比如一个用户所属的用户组数量应该有限，不能太多太多。如果满足这个条件，那么用这种方式也许是最方便的。但是检索时不太灵活，比如根据用户组检索下面的用户，只能使用like模糊查询了，并且是实现还是有很大不方便。所以如果对检索有要求的，最好是还是按照标准方案来。
**总结：**
方案一：标准，只需要多增加一张表。
方案二：对检索要求不高，关联数量逻辑上是有限的，并且不是很多，无需多增加一张表。
* * * * *
参考：
**多对多时一定要使用关联表吗**
非得用关联表时，才使用关联表。
如果明确知道关联数据不是很多，那就用一个大字段(text)，用`,`分割ID就可以了。
但是如果这种关联关系，是具有扩展性的，业务关系决定了关联数据可能是无限多的（比如员工和公司的关联，不能在员工或者公司行中使用一个大字段存一个员工的所有公司，或者一个公司所有的员工，这是不现实的），并且数据关联性查询比较频繁，那么就使用关联表。
>[danger] 有复杂的查询的，要用关联表，不然很麻烦，in是 多个查单个，FIND_IN_SET是单个查 多个，但是遇到多个查多个就不好办了（比如根据多个年级查课程，而课程关联的也是多个年级），所以有复杂的查询的一定要用关联表。
* * * * *
### 其它
[ASCII 、UTF-8、Unicode都是个啥啊，为啥会乱码啊？](https://www.toutiao.com/a6508698036997194253/)
> 整形不考虑编码
[58赶集的mysql军规](http://mp.weixin.qq.com/s/tMKlpUOP0UX6cYmRMUlSBw)
> 不用外键，因为由程序业务保证约束更合理。这样整个程序逻辑更清晰，而不是把逻辑分散到数据库中了。
[think-orm](https://github.com/top-think/think-orm) 数据库操作还是都保持统一比较好。
[58到家MySQL军规升级版](http://mp.weixin.qq.com/s/YfCORbcCX1hymXBCrZbAZg)
[MySql的索引原理](https://mp.weixin.qq.com/s/ssyg8zd2pMgEhLYPj5YrcQ)
[MySQL不为人知的主键与唯一索引约束](https://mp.weixin.qq.com/s/IE31GSDP0Ndjzc8kFj4Ukw)
[mysql36条军规.pdf_微盘下载](http://vdisk.weibo.com/s/muWOT)
[mysql中utf8和utf8mb4区别 - 彼扬 - 博客园](https://www.cnblogs.com/beyang/p/7580814.html)
[mysql中utf8不是真正的utf-8, utf8mb4才是真正的utf-8 - 不忘初心，方得始终 - CSDN博客](https://blog.csdn.net/cn_yefeng/article/details/80825335)
[(译)用MySQL的朋友们请不要使用"utf8"，请使用"utf8mb4" - 简书](https://www.jianshu.com/p/ab9aa8d4df7d?openInApp=1)
[译 | 永远不要在MySQL中使用utf8，改用utf8mb4 - 为程序员服务](http://ju.outofmemory.cn/entry/359647)
* * * * *
update time：2018-10-26 23:51:47
';