数据库SQL开发的一些要点

前段时间做一个项目，其中涉及到报表部分编写了大量复杂的SQL，比如其中的一个存储过程就有700多行。项目上线过后，进入维护阶段，发现大量的SQL很难维护。于是总结点经验教训：

设计

一、数据库命名遵循一些通用规范。

数据库命名规范是个基本的命名标准，每个团队都有自己的命名规范，我们做项目中以全大写下划线分割作为标准。表名或字段名要准确表达其业务含义。以DATE结尾的数据类型都是date类型，以TIME结尾的数据类型是datetime类型。以IS开头的都是bool类型。

二、大数据对象列应该独立成表。

比如员工照片是一个blob对象，按照范式来说，这个字段完全可以放在Employee表中，但是出于性能的考虑，最好单独出一张EmployeePhoto表，与Employee是一对一的关系，这样使用ORM的时候，平时取Employee对象就不会取到照片，只有需要显示照片时才取EmployeePhoto对象。

三、数据库字段尽量不要为null。

一个字段允许为空，那么在SQL查询时就需要进行一些特殊处理，比如在WHERE条件中用上 t1.COLUMN1 IS NULL或者在SELECT时用上ISNULL()函数。而在ORM时，对应的对象的数据类型如果是不允许为空的，还必须加上?表示允许为空。在编程时也要进行判断该值是否为空。一不小心就容易漏掉空的判断，造成计算结果不正确。所以在数据库设计时，尽量将每个字段设计为not null。

四、带小数的字段使用Decimal数据类型而不要使用Float数据类型。

因为Float类型是用于表示浮点数据的近似数据类型，所以存储后可能会造成一点误差，如果在程序中传入2.4进行保存，可能读取到的值却是2.4000001或者2.399999999。

五、使用配置表来存储可能经常变化的配置项，而不是写死在代码中。

在编写查询语句，写存储过程或者出报表时，经常会对某些字段进行过滤。比如ProjectAssignment表中有个RoleCode字段，表示在往项目上分配人时，该人的角色。在查询时经常会把角色A、B、C放在一起作为管理层角色，那么在关于管理层分配的各种报表中，就充斥着where pa.ROLE_CODE in ('A','B','C')这样的条件。但是有一天，用户说现在角色D也算是管理层角色了，那么之前做的所有报表，都要将这段代码进行修改。

所以对于这种可能修改的查询条件，那么最好是建立一个配置表，然后所有查询都是从这个配置表中读取数据进行查询。那么前面是SQL可以改为：

where pa.ROLE_CODE in (select CODE from CONFIG where CODE_TYPE='Management')

虽然这样要牺牲一点点的性能，但是由于本身配置表数据量不会很大，而且可以以CODE_TYPE建立聚集索引，那么性能不是很大的问题。

六、不要使用ORM工具通过对象模型生成数据库。

数据库的创建和修改都应该以脚本来完成，而每个字段的数据类型、长度、表的各种约束（主键约束、外键约束、唯一约束、非空约束、CHECK约束等）、索引都应该是需要根据实际需求进行设计的，而使用ORM工具通过对象模型只能生成一个大概的表和列，无法生成准确的Schema。推荐使用专业的数据库建模工具PowerDesigner或者ERWin进行数据库建模，然后生成数据库脚本。

 

开发

一、使用有意义的表别名。

在进行查询时经常会JOIN很多表，那么就经常用到表别名，表别名使得SQL开发更简单，查看起来也更简洁。表别名一般就1个字母，或者2个字母，采用表的单词首字母作为别名即可。

select 'ProjectAssignAuth', p.PROJECT_ID , 0 , 0 , gs.EMPLOYEE_ID,'STAFF' 
from GROUP_STAFF gs 
join GROUP_PROJECT gp on gs.GROUP_ID = gp.GROUP_ID 
join PROJECT p on gp.PROJECT_ID = p.PROJECT_ID

二、SQL语句中应该写上详细注释。

这个算是老生常谈了，SQL也是一种语言，对于复杂的逻辑，一不小心存储过程就写出了几百行，如果没有注释，那么看一个几百行的SQL那真是无比痛苦的事情，即使这个SQL是自己写的，那么一个月以后，没有注释连自己都看不懂自己在写什么。

三、使用print打印出过程信息。

在编写复杂的存储过程时，不可避免的就是要调试存储过程的正确性，虽然SQL Server支持调试SQL语句的功能，但是在对于几百行的SQL来说，还是很麻烦的。所以在编写SQL时加入print过程信息的功能，这个相当于写程序时的Debug.WriteLine()，打印的信息对外部程序并没有影响，只是在SSMS调用存储过程时能够打印一些有用的信息。

四、增加调试参数帮助输出更多的调试信息。

在编写C#代码的时候，我们在VS中可以设置Debug或者Release模式，同样我们可以在存储过程中增加一个带有默认值的参数，比如我们有一个计算项目金额的存储过程，计算逻辑复杂，我们可以增加一个@debug参数，默认情况下是关闭的，输入一些调试信息。

create proc PROC_CALC_PROJECT_AMOUNT 
@pid int,--项目ID 
@debug bit=0 
as

…SQL

if(@debug=1) 
begin 
--输出一下调试信息

end

这样我们平时调用时只传入一个参数，在SSMS中想打开调试信息时，只需要增加第二个参数1即可：

exec PROC_CALC_PROJECT_AMOUNT 100,1

五、尽量避免在WHERE条件中对字段使用函数。

这个是编程人员容易犯的错误。因为对字段使用函数后将无法使用到字段中的索引，降低了执行效率。比如查询所有2012年新建的项目，那么应该写成：

select *
from Project p
where p.CreateDate between ‘2012-1-1’ and ‘2012-12-31’;

而不要写成

select *
from Project p
where Year(p.CreateDate)=2013;

 

六、使用视图来抽象公共的查询部分。

在设计中提到使用配置表来把一些可能变化的查询条件放在数据库中，这样在需求更改时只修改数据库中的配置，而不用一个一个的改存储过程和SQL语句。另外还有一种方法就是使用视图来抽取公共查询的部分，将一些逻辑和条件放在视图中，然后其他存储过程和SQL直接使用视图，在需求发生变化时，我们只需要修改视图，其他的存储过程和SQL都不用修改。

 

 

 

七、小心查询时数据类型不匹配隐式转换导致的性能问题。

对于数据库中每个字段的类型不一定完全和其存储的值匹配。比如我们在设计员工表的员工号字段时，考虑到员工号不一定是个整数，所以设计成了varchar(10)，但是在实际应用中所有员工号都是5位数的整数，那么我们可能在写查询时可能就直接把int类型的员工号传入进行查询。

 

八、公用表表达式CTE、临时表和表变量的使用。

CTE 可用于：

• 创建递归查询。这个在树结构查询中常用。

• 在不需要常规使用视图时替换视图，也就是说，不必将定义存储在元数据中。

• 启用按从标量嵌套 select 语句派生的列进行分组，或者按不确定性函数或有外部访问的函数进行分组。

• 在同一语句中多次引用生成的表。

临时表分为局部临时表#开头和全局临时表##开头。临时表可以建立索引，对于大数据量的临时存储时就使用临时表。

表变量适用于存储数据量不大的临时数据。表变量不可用创建索引。

运维

一、数据库操作必须脚本化并进行版本控制。

所有数据库的操作，包括前期的建表、初始化数据、建索引后期的增量修改和数据维护，都必须以SQL脚本来执行。这些脚本都保存到源代码管理中。这样方便于测试和部署。

二、数据库脚本应该能够重复执行。

在创建或者修改数据库对象时，先判断现有数据库中是否已经有这个对象，有的话就不再创建或者改为更新对象或者将原对象删除，重新创建。这样脚本可以重复执行，避免了环境不一致导致脚本在这个环境可以正常运行，在另外一个数据库却报错的情况。

三、在修改或删除数据时，先把原有的数据值SELECT出来并将结果保存在Log中。

系统上线后有可能因为用户操作的原因，也可能是系统的Bug，导致了错误数据的产生，那么就需要出维护脚本将这些错误的数据删除或者更新回来。对于delete和update类的维护脚本，需要在删除和修改之前先select出要修改的数据，维护人员将查询的结果保存到维护日志中，这样如果编写的维护脚本有问题，那么还可以根据维护日志看到原来的数据，将数据修复回来。