MYSQL数据库 SQL开发设计规范

2018-12-02 本文已影响301人 Graddy

1 编写目的

本手册是为指导开发人员按照文档中的规范进行MYSQL数据库设计及SQL编码。

2 数据库对象定义规范

2.1 表的定义规则

2.1.1 表名的规定

使用英语

命名应该使用英文单词，避免使用拼音，特别不应该使用拼音简写;

命名不允许使用中文或者特殊字符，因为不是所有数据库都支持;

只使用字母(A-Za-z),数字(0-9)以及下划线()，不使用其他字符;

应该以字母开头;

采用单数

表名使用的单词，应是单数，而不是复数; 如工人表，选择WORKER,

而不是WORKERS;

除非一些约定俗成的单词，如SALES;

采用大写

Mysql 大小写转换需要一定的开销，要求数据库配置为大小写敏感，所以要求数据库对象名称建议一律大写，也方便不同数据库间的移植;

禁止使用大小写混合的方式

单词分隔

命名的各单词之间可以使用下划线(_) 进行分隔;

一般不要超过30个字符

表名的前缀

一个项目或一个模块的表名定义建议前缀最好统一，方便管理

2.1.2 表的设计规则

如果没有特殊要求，表的缺省引擎采用Innodb,支持事务，锁方式为行锁，并发性能高。
每个表要求定义主键
平衡范式与冗余关系，效率优先；原则上表设计必须遵守第三范式,如果因为合理的冗余字段将会给我们减少join的查询除外;
单行记录禁止超过8K
禁止使用外键
单表列数目建议小于30
如果有可能，把表设计成固定长度的，可以提高性能。
索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，索引最好不要超过5个

2.1.3 列的规定

列名建议大写
单词采用单数
是否采用下划线要统一
列名应避免二义性

关联表中的同义字段列名应相同，一个应用程序中的多个关联表，含有同样含义的字段，建议采用相同的列名，同时字段名的数据类型也要求相同。

如客户ID 一张表定义为CUSTOMER_ID 类型varchar
另一张表定义为CUSTOMER_NUM 类型int

如果两张表依赖客户ID字段关联查询时，可能因为隐式转换导致索引失效。

主键

一定要显式定义主键

采用与业务无关的单独列

建议采用自增列

数据类型采用int，并尽可能小，能用tinyint就不用int，能用int就不用bigint

将主键放在表的第一列

禁止使用varchar类型作为主键语句设计

不建议使用UUID类型设计主键，

首先UUID长度过长，另外InnoDB为聚集主键类型的引擎，数据会按照主键进行排序，由于UUID的无序性，InnoDB会产生巨大的IO压力

索引列

索引列必须定义为not null，并设置default值

复合索引，选择性高的字段排在前面。

字段

l 长度设计需要根据业务实际需要进行长度控制，禁止预留过长空间。例如status使用varchar(128)进行存储

l 用尽量少的存储空间来存数一个字段的数据;

例如：能使用int就不要使用varchar、char,能用varchar(16)就不要varchar(256);

l IP地址最好使用int类型;

l 固定长度的类型最好使用char,例如：邮编;

l 能使用tinyint就不要使用smallint,int;

l 尽可能不用TEXT、BLOB类型。

l 存储年使用YEAR类型，存储日期使用DATE类型，存储时间（精确到秒）建议使用TIMESTAMP类型，因为TIMESTAMP使用4字节，DATETIME使用8个字节。

总之：Mysql的字段选择原则，数据越小，性能越高，请选择合适字段类型,mysql数据类型及范围介绍见附录4.2

2.1.4 其它数据库对象定义规则

除表外，其他对象命名也建议使用不同的前缀来区别。建议如下：

视图 v_

序列 seq_

簇 c_

触发器 trg_

存储过程 sp_/p_

函数 f_/fn_

主键 pk_

外键 fk_

唯一索引 uk_

普通索引 idx_

说明：

1）为了避免不必要的冲突和麻烦，数据库对象中不使用数据库关键字和保留字，具体可阅附录4.1

2）用户自定义数据库对象：表，视图，主外键，索引，触发器，函数，存储过程等风格要保持一致。

2.1.5 建表格式规范

l 建表格式示范如下图：

建表格式示范.png

l 修改及查看表相关注释语句如下：

1）修改表的注释

alter table tablename comment '修改后的表的注释';

如：alter table TB_EXAMPLE comment '示例表1';

修改表的注释.png

2）修改字段的注释

alter table tablename modify column field_name int comment '修改后的字段注释';

注意：字段名和字段类型照写就行

如：alter table TB_EXAMPLE modify APP_NAME varchar(255) not null default '' comment '应用名1';

字段名和字段类型修改.png

3）查看表注释的方法

show create table tablename;

如：show create table TB_EXAMPLE;

查看表注释.png

4）查看字段注释的方法

show full columns from tablename;

如：show full columns from TB_EXAMPLE;

查看字段注释.png

3 MYSQL SQL开发规范

3.1 SQL书写规范

1、sql编写时，大小写一致

2、关键字单占一行，如select、from、where、and、group by、order by等

3、注意行缩进和对齐，建议语句中的关键字右对齐

4、使用空格，SQL语句内的算术运算符、逻辑运算符（AND、OR、NOT）、比较运算符（=、<=、>=、>、<、<>、BETWEEN AND）、IN、LIKE等运算符前后都应加一空格。

5、对较为复杂的sql语句加上注释，说明算法、功能。

注释风格：注释单独成行、放在语句前面。

单行注释：--

多行注释：/* */

6、select后面的每一列（列数目大于1）单独占一行，where后面的每个条件（条件数大于1）单独占一行。

7、update set子句内容每一项单独占一行，无缩进。

8****、insert子句内容每个表字段单独占一行，无缩进；values每一项单独占一行，无缩进。

3.2 SQL开发技巧规范

Sql 语句尽可能得简单
select * 尽量少用，仅select出需要的字段，只要一行数据时尽量使用limit 1
尽量避免在where子句中使用in,not in或者having，使用exists,not exists代替
尽量避免两端模糊匹配 like %***%
尽量用union all代替union
拒绝大sql语句，大事物，大批量，避免锁表，拆分大的 DELETE 或 INSERT 语句

如果你有一个大的处理，一定把其拆分，使用 LIMIT 条件是一个好的方法。参考下面是一个示例：

代码如下:

while (1) {

//每次只做1000条

mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");

if (mysql_affected_rows() == 0) {

// 没得可删了，退出！

break;

}

// 每次都要休息一会儿

usleep(50000);

}

数据表最好起别名，便于sql优化器快速分析。
除非业务需要否则不允许在sql语句中使用order by 子句，禁止使用order by rand()
多表连接查询

关联表个数限制的基本原则

在报表数据库或批处理数据库中经常会有需要关联多张表做查询的操作，而这些表有的可能会是大表。过多的表做关联可能给性能带来严重的影响，请慎用关联查询。

如果不能满足这个限定条件，可以考虑如下的两种处理方式:

第一，增加冗余字段，对于经常被关联使用的个别字段，可以考虑在一边增加冗余字段的方式来减少关联，这是一种反范式化的处理方式，但经常被用于报表查询类型的系统中。

第二，采用中间结果表方式。这种方式就是将原来一个SQL完成的操作拆开成多个SQL进行，将某两张或三张表的关联结果先取出，然后再拿结果集与剩下的表继续做关联，得到最终完整的结果。

2）连接语法

表连接分以下几种方式

•等价连接(两边的表严格相等才返回)

A join B on 条件

•左外连接(左边的表全返回，右边表关联不上就用null返回)

A left join B on条件

•右外连接(右边的表全返回，左边表关联不上就用null返回)

A right join B on　条件

•全外连接(左右两表都不加限制，全部返回)

Afull join B and 条件

3）连接查询的表字段前必须用表的别名标识。

如　select a.col1,b.col2….

4）避免笛卡尔出现

产生笛卡尔连接的原因就是在多张表进行关联的操作中缺少了表间的连接条件。由于笛卡尔积产生的结果集将是多表记录的乘积关系，因此当哪怕只有一张表的记录数比较大时，其结果集都将被数倍以上地放大，这势必给数据库性能带来严重影响。所以在写关联语句时要严格检查连接条件是否有遗漏。

5）join连接的字段，字段类型必须相同，防止隐式转换导致的索引失效。

避免嵌套查询

如：select a.name,(select b.address from table2 b where a.id=b.id) from a table1

用join代替：

Select a.name ,b.address

From table1 a

left join table2 b on a.id = b.id

能用连接查询实现的子查询，应避免使用子查询

如：Select b.address from table1 b where a.id in(select id from table2 a where sex=1)

应改为：select b.address from table1 b , table2 a where b.id=a.id and a.sex=1

或者：

Select b.address

From table1 b

Inner join table2 a on b.id = a.id

Where a.sex=1

Insert语句也要写出字段名称，避免因表结构发生改变时发生编译错误

如：insert into table (col1,col2,col3…) values(?,?,?...)

更新语句

查询、更新语句中严格选择所操作的字段。

尽量减少表被访问的次数，看下面这个SQL:

UPDATE tb_target a

SET a.col1=

(SELECT b.col1FROM tb_source b WHERE b.id = a.id),

a.col2 =

(SELECT b.col2 FROM tb_source b WHERE b.id = a.id),

a.col3 =

(SELECT b.col3 FROM tb_source b WHERE bid = a.id),

a.col4 =

(SELECT b.col4 FROM tb_source b WHERE b.id = a.id)

WHERE a.id IN (SELECT b.id FROM tb_source b)

该语句作用其实就是用更新一个表的多个列，但这样写会多次扫描源表。

如果改写成如下的方式，则只需要扫描一次了:

UPDATE tb_target a

SET (a.col1,a.col2,a.col3,a.col4) =

(SELECT b.col1,b.col2,b.col3,b.col4

FROM tb_ source b

WHERE b.id = a.id)

WHERE EXISTS (SELECT 1 FROM tb_source b WHERE b.id = a.id)

减少控制语句的检查次数

如在if…else语句中，尽可能最常用的，数量多的符合条件前置以被先检查到，减少条件筛选的次数。

比如在人口清洗中，需要按民族分批处理时，最大数量符合条件的就是汉族，第一个处理

4 附录

4.1 常用保留字列表

ACTION ADD ALL ALTER ANALYZE AND AS ASC ASENSITIVE

BEFORE BETWEEN BIGINT BINARY BIT BLOB BOTH BY

CALL CASCADE CASE CHANGE CHARCHARACTER COLLATE COLUMN CONDITION CONNECTION CONSTRAINT CONTINUE CONVERT CREATE CROSS CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CHECK

DATABASE DATABASES DATE 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 ENUM ESCAPED EXISTS EXIT EXPLAIN

FALSE FETCH FLOAT FOR FORCE FOREIGN FORM FULLTEXT

GOTO GRANTGROUP

HAVING HIGH_PRIORITY HOUR_MICROSECOND HOUR_MINUTE HOUR_SECOND

IF IGNORE

INDEX INFILE INNER INOUT INSENSITIVE INSERT INT INTEGER INTERVAL INTO IS ITERATE

JOIN

KEY KYES KILL

LEADING LEAVE LEFT LIKE LIMIT LINES LOAD LOCALTIME LOCALTIMESTAMP LOCK

LONG LONGBLOB LONGTEXT LOOP LOW_PRIORITY

MATCH MEDIUMBLOB MEDIUMINT MEDIUMTEXT MIDDLEINT MINUTE_MICROSECOND MINUTE_SECOND MOD MODIFIES

NATURAL NO NO_WRITE_TO_BINLOG NOT NULL NUMERIC

ON OPTIMIZE OPTION OPTIONALLY OR ORDER OUT OUTER OUTFILE

PRECISION PRIMARY PROCEDURE PURGE

READ READS REFERENCES REGEXP RELEASE RENAME REPEAT REPLACE REQUIRE RESTRICT

RETURN REVOKE RIGHT RLIKE REAL

SCHEMA SCHEMAS SECOND_MICROSECOND SELECT SENSITIVESEPARATOR SET SHOW SMALLINT SONAME SPATIAL SPECIFIC SQL SQL_BIG_RESULT SQL_CALC_FOUND_ROWS

SQL_SMALL_RESULT SQLEXCEPTION SQLSTATE SQLWARNING SSL STARTING STRAIGHT_JOIN

TABLE TERMINATED TEXT THEN TIME TIMESTAMP 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

XOR

YEAR_MONTH

ZEROFILL

4.2 Mysql数据类型介绍

4.2.1 数字类型列表

数字类型列表.png

4.2.2 日期类型列表

日期类型列表.png

4.2.3 字符类型列表

字符类型列表.png