NCX SQL编程规范

注: 转载NC技术部文档, 并做部分修改

一、概述

本手册侧重于代码编写过程中SQL语句的编写规范问题,内容涉及书写风格、性能优化、多数据适配等方面。

文档中用标示的内容为必须遵守的条例,其余的可视为建议。

二、书写风格

  1. SQL语句全部使用小写。
  2. 引用字符时用单引号。如:update testable set idcol=’abcd’。
  3. 连接符或运算符orinand、=、<=>= +- 等前后加上一个空格。
  4. 严禁使用select * …….形式的语句,必须指出select的具体字段,即select col1, col2,… from tablea where ….
  5. 严禁使用 insert into table values(?,?,?),必须指出具体要赋值的字段,即 insert into tablea (col1, col2,…) values(?,?,…)
  6. SQL语句包含多表连接时,建议对每个表命名别名,对每个字段的使用都要带上表别名,即 select a.col1, a.col2, b.col3 from tablea a, tableb b where a.col4=b.col5
  7. 避免隐含的类型转换。例如在where子句中numeric 型和int型的列的比较或相加。
  8. 读取是指通过JDBC读到的数据格式,保存是指保存在VO中的数据格式,插入或者更新是指insert或者update语句中的数据格式。
    1. 整型字段:读取时根据字段设置保存为Integer或者Long。
    2. 数字型字段:读取为BigDecimal,并保存为UFDouble,插入或者更新时为BigDecimal。
    3. 字符型字段:读取为String,并保存为String,插入或者更新为String。
    4. 布尔型字段:读取为String(‘Y’ OR ‘N’),并保存为UFBoolean,插入或者更新时为String(‘Y’ OR ‘N’)。
    5. 时间字段:读取为String,并保存为UFDateTime,插入或者更新时的时间格式由中间件统一处理,有单独需求的要申请后才能决定。 
  9. 在子查询中前后必须加上括号, select col1, col2 from tablea where col3 in ( select col4 from tableb where col4>0)
  10. 避免在where使用'1=1','1=2'这种表达式作为部分条件,如 select col1, col2 from tablea where 1=1 and col1 >0。
  11. 禁止使用视图。

三、性能优化

  1. 尽量使用prepareStatement,利用预处理功能。
  2. 在进行多条记录的增加、修改、删除时,建议使用批处理功能,批处理的次数以整个SQL语句不超过相应数据库的SQL语句大小的限制为准。
  3. 建议每条SQL 语句中in中的元素个数在500以下,如果个数超过时,应拆分为多条SQL语句。禁止使用xx in(‘’,’’….) or xx in(‘’,’’,’’)。
  4. 禁止使用or 超过 500,如 xx =’123’ or xx=’456’。
  5. 尽量不使用外连接。
  6. 禁止使用not in 语句,建议用not exists。
  7. 禁止使用Union, 如果有业务需要,请拆分为两个查询。
  8. 禁止在一条SQL语句中使用3层以上的嵌套查询,如果有,请考虑使用临时表或中间结果集。
  9. 尽量避免在一条SQL语句中从>= 4个表中同时取数,对于仅是作为过滤条件关联,但不涉及取数的表,不参与表个数计算;如果必须关联4个或4个以上表,尽量采用子查询的方式。
  10. 尽量避免使用order by和group by排序操作,因为大量的排序操作影响系统性能。如必须使用排序操作,尽量建立在有索引的列上。
  11. 对索引列的比较,尽量避免使用NOT 或 !=,可以考虑拆分为几个条件。如col1 是索引列,条件col1 !=0 可以拆分为col1 >0 or col2 <0
  12. 任何对列的操作都将导致表扫描,所以应尽量将数据库函数、计算表达式写在逻辑操作符右边。
  13. 在对char类型比较时,建议不要使用rtrim()函数,应该在程序中将不足的长度补齐。
  14. 用多表连接代替EXISTS子句。
  15. 如果有多表连接时,应该有主从之分,并尽量从一个表取数, 如select a.col1, a.col2 from a join b on a.col3=b.col4 where b.col5 = ‘a’。
  16. 在where子句中,如果有多个过滤条件,应将索引列或过滤记录数最多的条件应该放在前面。
  17. 在使用Like时,建议Like 的一边是字符串,表列在一边出现。

四、多数据库的考虑

  1. 字符串连接必须用“||”符号,不使用“+”。注意:在Oracle中一个null值与非null值连接,结果为非null值,在DB2和SqlServer中相反。使用isnull()对null转换为’’ 。★
  2. 通配符不能使用‘[a-c]%’这种形式,应写成如:select col1, col2 from table_name where col1 like ‘[a]%’ OR col1 like ‘[b]%’ OR col1 like ‘[c]%’ 。
  3. 在Case when语句中只能出现 =、>=、<= 以及is null运算符,不能出现 <、>、<>、!=、以及is not null运算符。 否则在Oracle的decode函数无法表达。

当必须使用 <, >, != ,is not null时,建议采用如下变通方法:

1)使用 !=时:例如   case  when  A!=B  then  e,

可改为 :  case  A=b  then  e1  else  e   (间接实现 A!=B)

2)使用 < 时:例如   case  when  A<B  then  e,

可改为 :  case  A<=b  then  case A=B  then  e1  else  e  (间接实现 A<B)

或    case  A>=b  then  e1  else  e    (间接实现 A<B)

3)使用 > 时:例如   case  when  A>B  then  e,

可改为 :  case  A>=b  then  case A=B  then  e1  else  e   (间接实现 A>B)

或    case  A<=b  then  e1  else  e    (间接实现 A>B)

4)使用 is  not  null  时:例如  case  when  A  is  not  null  then  e,

可改为:  case  A   is  null  then  e1   else   e  (间接实现 A  is  not  null)

特别说明:当执行大数据量的操作时,sql  Server  对 case when 的执行效率极低,甚至可能会死机,因此希望大家尽量不要使用case when。

 

  1. 左连接的写法必须带“outer”关键字。例如: select f1 from t1 left outer t2 on t1.f1 = t2.f1;而不是: select f1 from t1 left t2 on t1.f1 = t2.f1。
  2. 只能使用以下函数,如要使用新的函数必须申报,审批后才能使用。函数:coalesce, cast, len, left, replace, right, substring, lower, upper, ltrim, rtrim, abs, acos, asin, atan, cos, ceiling, exp, floor, log, power, round, sign,sin, square, sqrt, tan, count, max, min, sum, avg。
  3. substring函数中起始位置为1,表示从头开始。
  4. 对于一些char/varchar的字段的值,即使是0,1,2…等值,也必须表达为’0’,’1’,’2’…
  5. 在cast和convert语句中只支持到字符型、日期型和数字型的转换,且日期型只支持UFDate的格式,即’YYYY’-‘MM’-‘DD’
  6. 不能通过来percent  n 限制查询结果集的记录数,也不能使用 select  top n 的语句
  7. join 与on 必须严格匹配,不允许出现没有on的join。
  8. join…on 后面慎用 or,如果用到,请把or的范围用( )括起来
  9. 不能使用select into 的格式
  10. 给某个字符型的列赋值时,如果其值含有单引号,例如:set  col=’fsdf’fdsfdsf’sdf’…, 有两种解决方案:
    1. 使用转意字符,将单引号写成’’,即  set  col=’fsdf’’fdsfdsf’’sdf’…
    2. 通过set的方式赋值,即:set  col= ? …

然后:setString(8,” ’fsdf’fdsfdsf’sdf’”)

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值