MySQL表的设计一些基本规则

向右看齐 2022-05-08 02:16 503阅读 0赞

## 1.主键 ##

主键可以使用bigint(20) unsigned也可以使用varchar，使用bigint，可以设置为自增主键auto\_increment。使用varchar，要生成主键。

## 2.gmt\_create、gmt\_modified ##

在TB所有表中都添加gmt\_create、gmt\_modified字段，都是datetime类型。gmt\_create表示记录创建时间，gmt\_modified表示最近修改时间，如果记录没有修改，gmt\_create和gmt\_modified一致。

那么，这两个字段可以做什么用呢？这两个字段可以方便统计每天对某个表做了多少次的DML。另一种统计方式可以通过binlog。

### 查看昨天insert： ###

select \* FROM  test\_table  WHERE   
gmt\_create < date\_format(DATE(now()), '%Y-%m-%e %H-%i-%s') AND   
gmt\_create >= date\_format(DATE(date\_add(now(), INTERVAL - 1 DAY)),'%Y-%m-%e %H-%i-%s');

### 查看昨天update： ###

select \* FROM test\_table WHERE gmt\_create < date\_format(DATE(now()), '%Y-%m-%e %H-%i-%s')  
AND gmt\_create >= date\_format(DATE(date\_add(now(), INTERVAL - 1 DAY)),'%Y-%m-%e %H-%i-%s')  
AND date\_add(gmt\_create, INTERVAL - 1 DAY) != date\_add(gmt\_modified, INTERVAL - 1 DAY)

## 3.逻辑删除 is\_deleted ##

数据库不做物理删除，只做逻辑删除，用is\_deleted做逻辑删除，如果删除，则为1，不删除则为0.is\_deleted字段可以使用tinyint型。

## 4.禁止使用物理外键，使用逻辑外键 ##

由于mysql性能不如oracle，外键还是一个比较消耗性能的东西，所以我们不要使用物理外键，就是我们在建表的时候，禁止使用foreign key。

例如表b记录了表a的id，我们只需在表b中添加一列：aid就可以了，然后通过程序来控制外键关系。

## 5.命名规范 ##

1.库名，表名，字段名必须都用小写字母，并用下划线\_分隔，并且见名知意，使用名词。

原因如下：

a) MySQL有配置参数lower\_case\_table\_names，不可动态更改，linux系统默认为0，即库表名以实际情况存储，大小写敏感。如果是1，以小写存储，大小写不敏感。如果是2，以实际情况存储，但以小写比较。  
b) 如果大小写混合用，可能存在abc,Abc,ABC等多个表共存，容易导致混乱。  
c) 字段名显示区分大小写，但实际使用不区分，即不可以建立两个名字一样但大小写不一样的字段。  
d) 为了统一规范， 库名、表名、字段名使用小写字母。

## 6.使用innoDB存储引擎 ##

5.5以后的默认引擘，支持事务，行级锁，更好的恢复性，高并发下性能更好，对多核，大内存，ssd等硬件支持更好。

## 7.关于数据类型 ##

a.存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE。

例如：对于金钱的存储，使用decimal，或者以分为单位，用bigint(20) unsigned。  
b.使用UNSIGNED存储非负数值。同样的字节数，存储的数值范围更大。如tinyint 有符号为 -128-127，无符号为0-255  
c.建议使用INT UNSIGNED存储IPV4。  
使用INT UNSIGNED而不是char(15)来存储ipv4地址，通过MySQL函数inet\_ntoa和inet\_aton来进行转化。Ipv6地址目前没有转化函数，需要使用DECIMAL或者两个bigINT来存储。例如：  
SELECT INET\_ATON('209.207.224.40');  
3520061480  
SELECT INET\_NTOA(3520061480);  
209.207.224.40

d.整形定义中不添加（4），比如使用INT，而不是INT(4)

注意数值类型括号后面的数字只是表示宽度而跟存储范围没有关系，比如INT(3)默认显示3位，空格补齐，超出时正常显示，python、java客户端等不具备这个功能。

e.使用短数据类型，比如取值范围为0-80时，使用TINYINT UNSIGNED。

f.不建议使用ENUM类型，使用TINYINT来代替。

ENUM，有三个问题：添加新的值要做DDL，默认值问题(将一个非法值插入ENUM(也就是说，允许的值列之外的字符串)，将插入空字符串以作为特殊错误值)，索引值问题（插入数字实际是插入索引对应的值）  
实例：  
drop table if exists t;  
create table t(sex enum('0','1'));  
insert into t values(1);  
insert into t values('3');  
select \* from t;  
\+------+  
| sex  |  
\+------+  
| 0    |  
|      |  
\+------+  
2 rows in set (0.00 sec)  
g.尽可能不使用TEXT、BLOB类型。  
h.VARCHAR(N)，N表示存的个数，比如：VARCHAR（10） 下面语句都可以成功：  
update test set testvarchar='一二三四五六七八九十' where id=1  
update test set testvarchar='1234567890' where id=1  
update test set testvarchar='abcdefghij' where id=1  
i.存储年使用YEAR类型。  
j.存储日期使用DATE类型。  
k.存储时间（精确到秒）建议使用TIMESTAMP类型，因为TIMESTAMP使用4字节，DATETIME使用8个字节。  
l.将过大字段拆分到其他表中。  
m.禁止在数据库中使用VARBINARY、BLOB存储图片、文件等

## 8.适当建立索引 ##

非唯一索引必须按照“idx\_字段名称\_字段名称\[\_字段名\]”进行命名。  
唯一索引必须按照“uniq\_字段名称\_字段名称\[\_字段名\]”进行命名。  
索引名称必须使用小写。  
索引中的字段数建议不超过5个。  
单张表的索引数量控制在5个以内。  
不建议使用%前缀模糊查询，例如LIKE “%weibo”。  
合理创建联合索引（避免冗余），(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。  
唯一键由3个以下字段组成，并且字段都是整形时，使用唯一键作为主键。  
没有唯一键或者唯一键不符合5中的条件时，使用自增id作为主键。  
唯一键不和主键重复。  
索引字段的顺序需要考虑字段值去重之后的个数，个数多的放在前面。  
ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面。