更新时间:2022-05-07 GMT+08:00
分享

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的方式存起来,后续查询时先判断缓存对象中是否已经存在,如果存在则直接获取不再查询。

分享:

    相关文档

    相关产品

close