SQL编程规范.doc

SQL编程规范
一、概述
本手册侧重于代码编写过程中SQL语句的编写规范问题，内容涉及书写风格、性能优化、多数据适配等方面。
文档中用★标示的内容为必须遵守的条例，其余的可视为建议。
二、书写风格
SQL语句全部使用小写。★
引用字符时用单引号。如：updatetestablesetidcol=’abcd’。★
连接符或运算符or、in、and、＝、<=、>=，+，-等前后加上一个空格。
严禁使用select*…….形式的语句，必须指出select的具体字段，即selectcol1,col2,…fromtableawhere….★
严禁使用insertintotablevalues(?,?,?)，必须指出具体要赋值的字段，即insertintotablea(col1,col2,…)values(?,?,…)★
SQL语句包含多表连接时，建议对每个表命名别名，对每个字段的使用都要带上表别名，即selecta.col1,a.col2,b.col3fromtableaa,tablebbwherea.col4=b.col5
避免隐含的类型转换。例如在where子句中numeric型和int型的列的比较或相加。★
读取是指通过JDBC读到的数据格式，保存是指保存在VO中的数据格式，插入或者更新是指insert或者update语句中的数据格式。
整型字段：读取时根据字段设置保存为Integer或者Long。
数字型字段：读取为BigDecimal，并保存为UFDouble，插入或者更新时为BigDecimal。
字符型字段：读取为String，并保存为String，插入或者更新为String。
布尔型字段：读取为String(‘Y’OR‘N’)，并保存为UFBoolean，插入或者更新时为String(‘Y’OR‘N’)。
时间字段：读取为String，并保存为UFDateTime，插入或者更新时的时间格式由中间件统一处理，有单独需求的要申请后才能决定。
在子查询中前后必须加上括号,selectcol1,col2fromtableawherecol3in(selectcol4fromtablebwherecol4>0)★
避免在where使用'1=1','1=2'这种表达式作为部分条件，如selectcol1,col2fromtableawhere1=1andcol1>0。
禁止使用视图进行增删改。★
三、性能优化
尽量使用prepareStatement，利用预处理功能。
在进行多条记录的增加、修改、删除时，建议使用批处理功能，批处理的次数以整个SQL语句不超过相应数据库的SQL语句大小的限制为准。
建议每条SQL语句中in中的元素个数在500以下，如果个数超过时，应拆分为多条SQL语句。禁止使用xxin(‘’,’’….)orxxin(‘’,’’,’’)。★
禁止使用or超过500，如xx=’123’orxx=’456’。★
尽量不使用外连接。
禁止使用notin语句，建议用notexist。★
禁止使用Union,如果有业务需要，请拆分为两个查询。★
禁止在一条SQL语句中使用3层以上的嵌套查询，如果有，请考虑使用临时表或中间结果集。★
尽量避免在一条SQL语句中从>=4个表中同时取数，对于仅是作为过滤条件关联，但不涉及取数的表，不参与表个数计算；如果必须关联4个或4个以上表，尽量采用子查询的方式。
尽量避免使用orderby和groupby排序操作，因为大量的排序操作影响系统性能。如必须使用排序操作，尽量建立在有索引的列上。
对索引列的比较，尽量避免使用NOT或!=，可以考虑拆分为几个条件。如col1是索引列，条件col1!=0可以拆分为col1>0orcol2<0
任何对列的操作都将导致表扫描，所以应尽量将数据库函数、计算表达式写在逻辑操作符右边。
在对char类型比较时,建议不要使用rtrim()函数,应该在程序中将不足的长度补齐。
用多表连接代替EXISTS子句。
如果有多表连接时，应该有主从之分，并尽量从一个表取数，如selecta.col1,a.col2fromajoinbona.col3=b.col4whereb.col5=‘a’。
在where子句中，如果有多个过滤条件，应将索引列或过滤记录数最多的条件应该放在前面。
在使用Like时，建议Like的一边是字符串，表列在一边出现。
四、多数据库的考虑
字符串连接必须用“||”符号，不使用“+”。注意：在Oracle中一个null值与非null值连接，结果为非null值，在DB2和SqlServer中相反。使用isnull()对null转换为’’。★
通配符不能使用‘[a-c]%’这种形式，应写成如：selectcol1,col2fromtable_namewhereco