SQL

本章节介绍脚本中SQL的规则。

• 不推荐用拼接SQL方法，避免注入风险。

  let sql = "select id,name,ExternalCode,FeeType,FeeItemName,FeeItemDescription,Remark,Status from CA_PC_FeeItemDef where 1=1";
  if (!InputParams.id && !InputParams.name && !InputParams.feeType && !InputParams.feeItemName && !InputParams.status) {
         context.setI18nError("ca_pc__001013");
         return;
  }
  if (InputParams.id) {
         sql += "and id ='" + InputParams.id + "'"
  }

• 多表复杂查询建议用“sql.exec()”或“sql.excute()”方法，“excute()”方法比“exec()”多返回字段集和操作成功数。

  let result = execsql.exec("select id,name,OfferingId,ParentId,SkuCode,ChannelId,Status,ProductLabel,PriceCode,DefaultChoose,PaymentType from CA_PC_Product where id in (" + str + ")",
  {
         params: productId
  });

  多表复杂查询只能使用拼接SQL方法，但是有限制，例如示例中的“str”要求如下：

  • str如果来源于入参，则入参在拼接SQL之前需要进行校验，以免引入SQL注入攻击。如果来源于内部数据，可以不进行校验。
  • 不推荐直接在SQL中拼接入参，应该采用在SQL中拼接占位符，然后把入参放入参数的数组中，例如：

    attriSql = "select AttrDef from DE_DeviceDefAttri where DeviceDef=? and ExternalCode=? and AttrType='DYNAMIC' and ValueType='1'";
    attriRecords = db.sql().exec(attriSql, {
    		params: [deviceDefId, defExternalCode]
    	});

• 对于单表查询和增删改SQL，推荐使用Orm接口方法。

  Options选项可以选择返回字段、排序、聚合运算和分页等功能。当value不存在时，默认为null类型。

  let CA_PC_Stock = db.object('CA_PC_Stock');
  let amountCount = 0;
  if (UpdateStock.amount) {
         let record = { Amount: UpdateStock.amount };
         amountCount = CA_PC_Stock.updateByCondition({
                "conjunction": "AND",
                "conditions": [{
                       "field": "SkuCode",
                       "operator": "eq",
                       "value": UpdateStock.skuCode
                }]
         }, record);
  }

• 避免在循环中调用方法和操作数据库，可以用“in”来查询在集合中的结果。

  let productIdList = input.productId;
  let str = "";
  let arr = [];
  for (let i = 0; i < productIdList.length; i++) {
         arr[i] = "?";
  }
  str = arr.toString();
  let result = execsql.exec("select id,name,OfferingId,ParentId,SkuCode,ChannelId,Status,ProductLabel,PriceCode,DefaultChoose,PaymentType from CA_PC_Product where id in (" + str + ")",
  {
         params: productIdList
  });

• 对sql进行优化时，尽量使用有索引的字段，避免使用没有索引的字段。
• 批量操作数据库时，尽量使用已封装好的批量操作接口。

  例如，Orm.batchInsert、Orm.batchUpdate接口和Orm.deleteByCondition批量删除接口。如果批量创建父子对象记录，且批量创建的记录在一个完整的事务中，全部成功或全部失败，建议使用Orm.compositeInsert接口。

  var s = db.object('Customer__CST');
  var records = [];
  var record = {
      "name": "hello",
      "count__CST": 123,
      "Contacts": {
          "records": [
              {
                  "name": "hello_contact1"
              },
              {
                  "name": "hello_contact2"
              },
          ]
      }
  };
  records.push(record);
  var ids = s.compositeInsert(records);
  console.log("id list = ", ids);
  
  count = s.count();
  console.log("record count = ", count);

• 匹配查询推荐用like，日期比较推荐用“<”、“>”。

  【推荐】：

  select id from t where name like ‘abc%’
  select id from t where createdate>=’2005-11-30’ and createdate<’2005-12-1’

  【不推荐】：

  select id from t where substring(name,1,3)=’abc’
  select id from t where datediff(day,createdate, ‘2005-11-30’)

• 使用exists替代in，使用not exists替代not in。

  【推荐】：

  SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X’FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)

  【不推荐】：

  SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC =’MELB’)

• 避免在索引列上使用is null和is not null，会造成索引失效。

  【推荐】：

  SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0

  【不推荐】：

  SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL

• 注意事项
  • 尽量避免Select字句中，使用“*”。
  • 尽量避免在where子句中，使用!=或<>操作符。
  • 尽量避免在where子句中，对字段进行函数操作。
  • 尽量避免在SQL中，使用 “!=” 、“||”、“+”符号。
  • 尽量避免在where条件中，做数据筛选。
  • 尽量避免查询中，按字段排序。
  • 尽量避免多表关联查询和嵌套查询，不要使用超过2表的关联查询。
  • 不要在循环内重复使用同一条件查询，应该在循环外处理。例如，公共数据仅在循环外查询一次。
  • 不要在同一个脚本的多个方法内，使用同一条件多次查询，可以定义类的成员变量。
  • 避免关联查询

    关联的条件很多情况下都是唯一的，可以提前做单独查询。例如，使用公共数据作为后续条件，避免关联查询。

  • 避免频繁的数据库交互

    例如，查询5000条数据，查询补充数据的时候，不要在循环内多次交互数据库，把可以合并的条件在循环外拼接并进行一次性查询，在循环内只需要从结果集中获取数据，可以极大提升查询性能。

  • 尽量利用对象做临时缓存

    例如查询到DeviceDef后，按照“id:Object”的方式存起来，后续查询时先判断缓存对象中是否已经存在，如果存在则直接获取不再查询。