0.1.8 • Published 3 years ago

@haua/sql v0.1.8

Weekly downloads
4
License
ISC
Repository
-
Last release
3 years ago

简介

覆盖率 typescript

更新速度最快的接口文档地址（gitee）

这是一个根据配置生成sql的工具，仅生成sql语句，不会执行。

生成的 sql 中参数均为?，并且在 values 中返回这些问号对应的值。

该工具返回的值类型：

export interface HSqlObjectT {
  // 例 SELECT * FROM `test` WHERE `col` = ?
  // ps. 该值可能为空字符串，通常是因为入参不符合要求，或触发安全检查
  sql: string

  // 以上sql中问号依次对应的值
  values: (string | number)[]

  // 用于本工具判断该sql对象是否由本工具生成，建议不要加工或去除，mysql库的query函数会忽略它
  symbol: string[]
}

它可以直接用于mysql库的query函数第一个入参，参考文档： https://www.npmjs.com/package/mysql#performing-queries

安装

npm i -S @haua/sql
# or
yarn add @haua/sql

使用

import {select, del, insert, update} from '@haua/sql'
const sql = select('test')
console.log(sql)
// {
//     sql: 'SELECT * FROM `test`',
//     values: []
// }

对象 key 的写法

key 可以用#分割成 3 段

  • 例：col1#function#foo
  • 第一段是表的列名，可为空字符串
  • 第二段是预留字，可为空字符串
  • 第三段仅用于让相同 key 可共存在一个 js 对象中，可填任意字符
预留字可用区域说明
functionselect 的 where它的值为 SQL 函数，值可为字符串，或字符串数组
functionselect 的 order
functionupdate 的 data它的值为 SQL 函数，值可为字符串，或字符串数组
sql

select 用法

export declare function select(
  // 表名
  table: string,
  // where 内的语句，还有部分特殊语句也是在这写，如`LEFT JOIN`
  where: Record<string, any> | string | [string, (number | string)[]],
  // 查询的列名
  col?: string | string[],
  // ORDER BY
  order?: null | {
    [k: string]: 'DESC' | 'ASC' | string
  },
  // LIMIT 不建议和OFFSET合写
  limit?: number,
  // OFFSET 一般是分页时使用
  offset?: number,
  // 排序
  groupBy?: string
): HSqlObjectT

返回指定字段

可以给查询出来的字段设置别名

select('test', {}, ['title', 'count(*) as total', 'id as testId'])
// {
//     sql: 'SELECT `title`,count(*) as `total`,`id` as `testId` FROM `test`',
//     values: []
// }

select('test', {}, 'title')
// {
//     sql: 'SELECT `title` FROM `test`',
//     values: []
// }

select('test', {}, 'count(*) as total')
// {
//     sql: 'SELECT count(*) as `total` FROM `test`',
//     values: []
// }

select('test', {}, '*')
// {
//     sql: 'SELECT * FROM `test`',
//     values: []
// }

排序 & GROUP BY

支持指定字段排序，也支持排序函数。排序字段支持表别名。

select(
  'test',
  {
    AS: 't1',
    a: 'b',
    b: 'a',
  },
  ['count(*) as num', 'id as testId'],
  {
    't1.col1': 'DESC',
    num: 'DESC',
    b: 'ASC',
    '#function': 'RAND()',
    '#function#': 'FIELD(id,1,2,3)',
  },
  10,
  20,
  'col1'
)

// {
//     sql: 'SELECT count(*) as `num`,`id` as `testId` FROM `test` AS t1 WHERE `a` = ? AND `b` = ? GROUP BY col1 ORDER BY `t1`.`col1` DESC,`num` DESC,`b` ASC,RAND(),FIELD(id,1,2,3) LIMIT 10 OFFSET 20',
//     values: [ 'b', 'a' ]
// }

当 where 是对象（推荐）

简单示例：

select('test', {
  a: 'b',
  b: 1,
  c: undefined,
  d: null,
  e: {
    '>=': new Date(1606472694938),
  },
  f: true,
  g: false,
})

// {
//     sql: 'SELECT * FROM `test` WHERE `a` = ? AND `b` = ? AND `d` IS NULL AND `e` >= "2020-11-27 18:24:54" AND `f` = ? AND `g` = ?',
//     values: ["b",1,1,0]
// }

对比符号的使用

=与IN可省略，其他符号需要显式注明（如 >,LIKE,IS NOT,NOT IN ），支持所有 MySQL 的符号

select('test', {
  a: 1, // = 号可简写
  b: {
    // 同一个字段的可合并写
    '<': 10,
    '>': 1,
    '!': [5],
  },
  c: {
    '!': 5,
  },
  d: [1], // IN 号可简写
})

// {
//   sql: 'SELECT * FROM `test` WHERE `a` = ? AND (`b` < ? AND `b` > ? AND `b` NOT IN (?)) AND `c` != ? AND `d` IN (?)',
//   values: [ 1, 10, 1, 5, 5, 1 ],
// }

有些符号可缩写：

缩写的符号转为 SQL 后的符号备注
!!=当值不为数组时
!NOT IN当值为数组时
NOTIS NOT

相同 key 可重复出现

js 对象不允许出现多个相同的 key，可增加#号，#号后面的内容可以随便写，但以后可能会增加预留字，尽量使用双#号

select('test', {
  a: {
    '>': 1,
  },
  'a##': {
    '<': 10,
  },
  'a##666': {
    '!': 5,
  },
})

// {
//   sql: 'SELECT * FROM `test` WHERE `a` > ? AND `a` < ? AND `a` != ?',
//   values: [ 1, 10, 5 ],
// }

OR/AND 的使用

不同条件之间的关系默认是 AND，可通过以下方法修改为OR

select('test', {
  g: 1,
  AND: {
    a: 2,
    b: 3,
  },
  f: 4,
  OR: {
    a: 5,
    b: 6,
    AND: {
      a: 7,
      b: 8,
      OR: {
        a: 9,
        b: 10,
      },
    },
  },
  e: 11,
  'OR##': {
    a: 12,
    b: 13,
    c: [14],
  },
  d: 15,
})

// {
//   sql: 'SELECT * FROM `test` WHERE `g` = ? AND (`a` = ? AND `b` = ?) AND `f` = ? AND (`a` = ? OR `b` = ? OR (`a` = ? AND `b` = ? AND (`a` = ? OR `b` = ?))) AND `e` = ? AND (`a` = ? OR `b` = ? OR `c` IN (?)) AND `d` = ?',
//   values: [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15],
// }

根条件之间的关系默认是 AND，如果想要 OR，则在根写一个 OR，所有条件都写在这个 OR 内：

select('test', {
  OR: {
    a: 'b',
    b: 'A',
    c: 'A',
    d: 'A',
  },
})

// {
//   sql: 'SELECT * FROM `test` WHERE (`a` = ? OR `b` = ? OR `c` = ? OR `d` = ?)',
//   values: ["b","A","A","A"],
// }

ps. 根部如果有两个 OR，则两个 OR 之间的关系是 AND

left join 用法

select(
  'test',
  {
    'LEFT JOIN': 'test2 as t2',
    't2.a': 'text1',
    b: 1,
    c: undefined,
    d: null,
    e: {
      '>=': new Date(1606472694938),
    },
  },
  '*'
)

// {
//   sql: 'SELECT * FROM `test` LEFT JOIN test2 as t2 WHERE `t2`.`a` = ? AND `test`.`b` = ? AND `test`.`d` IS NULL AND `test`.`e` >= "2020-11-27 18:24:54"',
//   values: ["text1",1],
// }

设置表别名

常与 left join 一起使用

select(
  'test',
  {
    AS: 't1',
  },
  ['count(*) as num', 'id as testId'],
  {
    't1.col1': 'DESC',
  }
)

// {
//   sql: 'SELECT * FROM `test` LEFT JOIN test2 as t2 WHERE `t2`.`a` = ? AND `test`.`b` = ? AND `test`.`d` IS NULL AND `test`.`e` >= "2020-11-27 18:24:54"',
//   values: ["text1",1],
// }

where 中含 function

当 key 为 #function 或其后是#+任意字符 时，该行被认为是函数，且不做任何检查。

为防止 sql 注入，推荐 value 为(string|number)[]，数组第一个字符串中的?将被依次替换为数组后面的参数

select('test', {
  '#function': "find_in_set('4',col1)",
  '#function#2': ['find_in_set(?,col2)', '666'], // 推荐传入此格式的 value，以防止sql注入
  b: 'A',
})

// {
//   sql: 'SELECT * FROM `test` WHERE find_in_set('4',col1) AND find_in_set(?,col2) AND `b` = ?',
//   values: [ '666', 'A' ],
// }

子查询

在一条 sql 语句中包含另一条或多条 sql 语句

只需要把 select 的返回值，直接作为 where 中的 value 即可，注意，如果 key 与 value 之间的关系是 IN，则必须显式声明，因为 value 是另一条 sql 语句，无法判断它的返回值只有一个还是多个

ps. select 返回值中有个字段叫 symbol，请勿修改其值，否则 hsql 无法判断该 value 是不是 sql。

select('test', {
  col1: {
    IN: select(
      'test2',
      {
        col2: {
          IN: select(
            'test3',
            {
              type: [1, 2],
              name: {
                like: `%666%`,
              },
            },
            'foo_id'
          ),
        },
      },
      'test_id'
    ),
  },
})

// {
//   sql: 'SELECT * FROM `test` WHERE `col1` IN (SELECT `test_id` FROM `test2` WHERE `col2` IN (SELECT `foo_id` FROM `test3` WHERE `type` IN (?,?) AND `name` like ?))',
//   values: [ '666', 'A' ],
// }

当 where 是数组

select('test', ['`a` = ? AND `b` = ?', ['b', 'a']])

// {
//     sql: 'SELECT * FROM `test` WHERE `a` = ? AND `b` = ?',
//     values: ["b","a"]
// }

当 where 是字符串（不推荐）

select('test', '`a` = 1 AND `b` = 2')

// {
//     sql: 'SELECT * FROM `test` WHERE `a` = 1 AND `b` = 2'
// }

insert 用法

export declare function insert(
  // 表名
  table: string,
  // 要插入的数据，可为复数
  data: ObjectT | ObjectT[]
): HSqlObjectT

基础用法

insert('table1', {
  col1: '列1',
  col2: '列2',
  col3: null,
})

// {
//   sql: 'INSERT INTO `table1` (`col1`,`col2`,`col3`) VALUES (?,?,DEFAULT)',
//   values: ['列1', '列2']
// }

批量插入

insert('table1', [
  {
    col1: '列1',
    col2: '列2',
  },
  {
    col1: '列3',
    col2: '列4',
  },
])

// {
//   sql: 'INSERT INTO `table1` (`col1`,`col2`) VALUES (?,?),(?,?)',
//   values: ['列1', '列2', '列3', '列4']
// }

del 用法

export declare function del(
  // 表名
  table: string,
  // 与 select 方法的同名入参完全相同
  where: Record<string, any> | string | [string, (number | string)[]],
  // 可设定最多删除行数，默认为1，设为0则不限制条数
  limit?: number,
  // 安全检查项：是否允许where条件为空，默认为false不允许。如果不允许where条件为空，而where条件又是空，则返回的sql为空字符串
  allowEmpty?: boolean
): HSqlObjectT

基础用法

del('table1', {
  col1: '列1',
  col2: '列2',
})

// {
//   sql: 'DELETE FROM `table1` WHERE `col1` = ? AND `col2` = ? LIMIT 1'
//   values: ['列1', '列2']
// }

无效用法

del('table1', {}, 0)

// {
//   sql: ''
//   values: []
// }

update 用法

export declare function update(
  // 表名
  table: string,
  // 与 select 方法的同名入参完全相同
  where: Record<string, any> | string | [string, (number | string)[]],
  // 要修改的字段
  data: any,
  // 可设定最多删除行数，默认为1，设为0则不限制条数
  limit?: number,
  // 安全检查项：是否允许where条件为空，默认为false不允许。如果不允许where条件为空，而where条件又是空，则返回的sql为空字符串
  allowEmpty?: boolean
): HSqlObjectT

基础用法

update(
  'test',
  {
    a: '1',
  },
  {
    b: '666',
    c: {
      '+': 6,
    },
    'expire_time#function': 'DATE_ADD(expire_time, INTERVAL 1 MONTH)',
    '#sql#666': ['expire_time = DATE_ADD(expire_time, INTERVAL ? MONTH)', [1]],
  }
)

// {
//   sql: 'UPDATE `test` SET `b` = ?,`c` = `c` + ?,`expire_time` = DATE_ADD(expire_time, INTERVAL 1 MONTH),expire_time = DATE_ADD(expire_time, INTERVAL ? MONTH) where `a` = ? LIMIT 1',
//   values: ["666",6,1,"1"]
// }

下一版本

  • add where 的对比符号可以直接在 key 中第二段声明
  • add update data 的操作符号可以直接在 key 中第二段声明
  • add sort 的 function 可以为数组，提升 sql 注入防御力
  • add update data 的 function 可以为数组，提升 sql 注入防御力
  • add ts 中支持设置表模型的 type/interface
sqlsql-buildermysql
@everything-registry/sub-chunk-388@infinitebrahmanuniverse/nolb-_hau
0.1.8

3 years ago

0.1.7

3 years ago

0.1.6

3 years ago

0.1.4

3 years ago

0.1.3

3 years ago

0.1.2

3 years ago

0.1.1

3 years ago

0.0.1

3 years ago