建表规约
说起建表规约前,先提一下数据库三大范式:
第一范式(确保每列保持原子性)
第二范式(确保表中的每列都和主键相关)
第三范式(确保每列都和主键列直接相关,而不是间接相关)
仅供参考,需要根据实际情况而定,并非一定需要完全遵循
- 命名规范
1.1 表达是与否概念的字段,必须使用 is_xxx 的方式命名,数据类型是 tinyint(1 表示是,0 表示否)。
1.2 表名、字段名必须使用小写字母或数字,禁止出现数字开头,禁止两个下划线中间出现数字。
1.3 表名不使用复数名词。
1.4 禁用保留字,如 desc、range、match、delayed 等。详见表格:1.0
1.5 主键索引名为 pk_字段名;唯一索引名为 uk_字段名;普通索引名则为 idx_字段名。
说明:pk_ 即 primary key;uk_ 即 unique key;idx_ 即 index 的简称
- 字段定义
2.1小数类型为 decimal,禁止使用 float 和 double
2.2如果存储的字符串长度几乎相等,使用 char 定长字符串类型
2.3 varchar 是可变长字符串,不预先分配存储空间,长度不要超过 5000,如果存储长度
大于此值,定义字段类型为 text,独立出来一张表,用主键来对应,避免影响其它字段索引效率
2.4 字段定义尽量非null,给一个默认值
- 通用建议
3.1字段允许适当冗余,以提高查询性能,但必须考虑数据一致。
冗余字段应遵循:
3.1.1不是频繁修改的字段。
3.1.2不是唯一索引的字段。
3.1.3不是 varchar 超长字段,更不能是 text 字段
3.2 单表行数超过 500 万行或者单表容量超过 2GB,推荐进行分库分表:需要提出来大家讨论过一遍
3.3 对于可能需要扩展的表,可以提前增加预留字段
3.4 表注释一定要写
3.5 创建表选择字段的时候,在符合业务需求的情况下尽量小,数据类型尽量简单
表格:1.0
ADD | ALL | ALTER | |
ANALYZE | AND | AS | |
ASC | ASENSITIVE | BEFORE | |
BETWEEN | BIGINT | BINARY | |
BLOB | BOTH | BY | |
CALL | CASCADE | CASE | |
CHANGE | CHAR | CHARACTER | |
CHECK | COLLATE | COLUMN | |
CONDITION | CONNECTION | CONSTRAINT | |
CONTINUE | CONVERT | CREATE | |
CROSS | CURRENT_DATE | CURRENT_TIME | |
CURRENT_TIMESTAMP | CURRENT_USER | CURSOR | |
DATABASE | DATABASES | DAY_HOUR | |
DAY_MICROSECOND | DAY_MINUTE | DAY_SECOND | |
DEC | DECIMAL | DECLARE | |
DEFAULT | DELAYED | DELETE | |
DESC | DESCRIBE | DETERMINISTIC | |
DISTINCT | DISTINCTROW | DIV | |
DOUBLE | DROP | DUAL | |
EACH | ELSE | ELSEIF | |
ENCLOSED | ESCAPED | EXISTS | |
EXIT | EXPLAIN | FALSE | |
FETCH | FLOAT | FLOAT4 | |
FLOAT8 | FOR | FORCE | |
FOREIGN | FROM | FULLTEXT | |
GOTO | GRANT | GROUP | |
HAVING | HIGH_PRIORITY | HOUR_MICROSECOND | |
HOUR_MINUTE | HOUR_SECOND | IF | |
IGNORE | IN | INDEX | |
INFILE | INNER | INOUT | |
INSENSITIVE | INSERT | INT | |
INT1 | INT2 | INT3 | |
INT4 | INT8 | INTEGER | |
INTERVAL | INTO | IS | |
ITERATE | JOIN | KEY | |
KEYS | KILL | LABEL | |
LEADING | LEAVE | LEFT | |
LIKE | LIMIT | LINEAR | |
LINES | LOAD | LOCALTIME | |
LOCALTIMESTAMP | LOCK | LONG | |
LONGBLOB | LONGTEXT | LOOP | |
LOW_PRIORITY | MATCH | MEDIUMBLOB | |
MEDIUMINT | MEDIUMTEXT | MIDDLEINT | |
MINUTE_MICROSECOND | MINUTE_SECOND | MOD | |
MODIFIES | NATURAL | NOT | |
NO_WRITE_TO_BINLOG | NULL | NUMERIC | |
ON | OPTIMIZE | OPTION | |
OPTIONALLY | OR | ORDER | |
OUT | OUTER | OUTFILE | |
PRECISION | PRIMARY | PROCEDURE | |
PURGE | RAID0 | RANGE | |
READ | READS | REAL | |
REFERENCES | REGEXP | RELEASE | |
RENAME | REPEAT | REPLACE | |
REQUIRE | RESTRICT | RETURN | |
REVOKE | RIGHT | RLIKE | |
SCHEMA | SCHEMAS | SECOND_MICROSECOND | |
SELECT | SENSITIVE | SEPARATOR | |
SET | SHOW | SMALLINT | |
SPATIAL | SPECIFIC | SQL | |
SQLEXCEPTION | SQLSTATE | SQLWARNING | |
SQL_BIG_RESULT | SQL_CALC_FOUND_ROWS | SQL_SMALL_RESULT | |
SSL | STARTING | STRAIGHT_JOIN | |
TABLE | TERMINATED | THEN | |
TINYBLOB | TINYINT | TINYTEXT | |
TO | TRAILING | TRIGGER | |
TRUE | UNDO | UNION | |
UNIQUE | UNLOCK | UNSIGNED | |
UPDATE | USAGE | USE | |
USING | UTC_DATE | UTC_TIME | |
UTC_TIMESTAMP | VALUES | VARBINARY | |
VARCHAR | VARCHARACTER | VARYING | |
WHEN | WHERE | WHILE | |
WITH | WRITE | X509 | |
XOR | YEAR_MONTH | ZEROFILL |