3. module.exports = {
    4.     /**
    5.      * wen
    6.      * sql 拼接
    7.      * @param {*} object 数据对象需要是map对象
    8.      * @param {*} sqlMap map对象对于的sql对象
    9.      * @param {*} sql sql查询语句用来判断是否加where可不传，不传默认需拼接的sql带where，所有条件都会加 and
    10.      * 拼接时变量会用?进行站位 返回一个sql 字符串，和param数组
    11.      * object 需要家$符，$前部分为key和sqlMapkey要一样，$后半部分为对比条件，比如
    12.      * = :=
    13.      * !=: !=
    14.      * l : like
    15.      * nl : not like
    16.      * i : in
    17.      * ni : not in
    18.      * b : between
    19.      * nb : not between
    20.      * f : FIND_IN_SET
    21.      * nf : not FIND_IN_SET
    22.      * >:?
    23.      * <:<
    24.      * >=:>=
    25.      * <=:<=
    26.      */
    27.     sqlAppend(object, sqlMap, sql) {
    28.         const result = {
    29.             sql: '',
    30.             param: []
    31.         };
    32.         let check = true;
    33.         if (!sql) {
    34.             check = false;
    35.         }
    36.         let addSql = '';
    37.         try {
    38.             if (object && sqlMap && typeof object === 'object' && typeof sqlMap === 'object') {
    39.                 const keys = Object.keys(object);
    40.                 keys.forEach(item => {
    41.                     // 有包含$才进行处理
    42.                     if (item.includes('$')) {
    43.                         const mapKeys = item.split('$');
    44.                         // $拆分后有两个数据，并且sqlmap有对应的数据，boject的数据不为空
    45.                         if (mapKeys.length == 2 && object[item] && sqlMap[mapKeys[0]]) {
    46.                             switch (mapKeys[1]) {
    47.                             case '=':
    48.                                 addSql += `  ${sqlMap[mapKeys[0]]} = ? `;
    49.                                 result.param.push(object[item]);
    50.                                 break;
    51.                             case '!=':
    52.                                 addSql += `  ${sqlMap[mapKeys[0]]} != ? `;
    53.                                 result.param.push(object[item]);
    54.                                 break;
    55.                             case '<':
    56.                                 addSql += `  ${sqlMap[mapKeys[0]]} < ? `;
    57.                                 result.param.push(object[item]);
    58.                                 break;
    59.                             case '>':
    60.                                 addSql += `  ${sqlMap[mapKeys[0]]} > ? `;
    61.                                 result.param.push(object[item]);
    62.                                 break;
    63.                             case '<=':
    64.                                 addSql += `  ${sqlMap[mapKeys[0]]} <= ? `;
    65.                                 result.param.push(object[item]);
    66.                                 break;
    67.                             case '>=':
    68.                                 addSql += `  ${sqlMap[mapKeys[0]]} >= ? `;
    69.                                 result.param.push(object[item]);
    70.                                 break;
    71.                             case 'l':
    72.                                 addSql += `  ${sqlMap[mapKeys[0]]}  like  ?  `;
    73.                                 result.param.push(`%${object[item]}%`);
    74.                                 break;
    75.                             case 'nl':
    76.                                 addSql += `  ${sqlMap[mapKeys[0]]}  not like  ?  `;
    77.                                 result.param.push(`%${object[item]}%`);
    78.                                 break;
    79.                             case 'i':
    80.                                 addSql += `  ${sqlMap[mapKeys[0]]}  in (?) `;
    81.                                 result.param.push(object[item]);
    82.                                 break;
    83.                             case 'ni':
    84.                                 addSql += `  ${sqlMap[mapKeys[0]]}  not  in (?) `;
    85.                                 result.param.push(object[item]);
    86.                                 break;
    87.                             case 'b':
    88.                                 if (object[item].length > 1 && object[item][0] && object[item][1]) {
    89.                                     addSql += `  ${sqlMap[mapKeys[0]]}  between ? and ? `;
    90.                                     result.param.push(object[item][0], object[item][1]);
    91.                                 }
    92.                                 break;
    93.                             case 'nb':
    94.                                 if (object[item].length > 1 && object[item][0] && object[item][1]) {
    95.                                     addSql += `  ${sqlMap[mapKeys[0]]} not  between ? and ? `;
    96.                                     result.param.push(object[item][0], object[item][1]);
    97.                                 }
    98.                                 break;
    99.                             case 'f':
    100.                                 addSql += ` find_in_set(${sqlMap[mapKeys[0]]} ?) `;
    101.                                 result.param.push(object[item]);
    102.                                 break;
    103.                             case 'nf':
    104.                                 addSql += ` not find_in_set(${sqlMap[mapKeys[0]]} ?) `;
    105.                                 result.param.push(object[item]);
    106.                                 break;
    107.                             default:
    108.                                 break;
    109.                             }
    110.                             if (check && addSql && !sql.includes('where') && !sql.includes('WHERE')) {
    111.                                 check = false;
    112.                                 result.sql += ` where ${addSql}`;
    113.                             } else if (addSql) {
    114.                                 result.sql += ` and ${addSql}`;
    115.                             }
    116.                             addSql = '';
    117.                         }
    118.                     }
    119.                 });
    120.             }
    121.             return result;
    122.         } catch (error) {
    123.             console.log('拼接sql产生错误', error);
    124.             return {
    125.                 sql: '',
    126.                 param: []
    127.             };
    128.         }
    129.     },
    130.     /**
    131.      * 分页
    132.      * @param {*} page
    133.      * @param {*} pageSize
    134.      */
    135.     sqlLimit(page, pageSize) {
    136.         let sql = '';
    137.         if (page && pageSize && !isNaN(page) && !isNaN(pageSize)) {
    138.             sql += ` limit ${(Number(page) - 1) * pageSize},${pageSize}`;
    139.         }
    140.         return sql;
    141.     }
    142. };

    调用例子

    1.  let sql =
    2.         ' SELECT t1.*,t1.start_time*1000 as start_time,t1.end_time*1000 as end_time  FROM task t1  ';
    3.     let object = {
    4.             'product_id$=': param.product_id,
    5.             'group_id$=': param.group_id,
    6.             'executors$=': param.executors,
    7.             start_time$b: param.time,
    8.             ' status$i': [1, 2]
    9.         },
    10.         sqlMap = {
    11.             product_id: 't1.product_id',
    12.             group_id: 't1.group_id',
    13.             executors: 't1.executors',
    14.             start_time: 't1.start_time',
    15.             status: 't1.status'
    16.         };
    17.     const sqlResuld = sqlAppend(object, sqlMap, sql);
    18.  sql += sqlResult.sql
    19. await models.sequelize.query(sql, {
    20.         replacements: sqlResult.param,
    21.         type: models.SELECT
    22.     });