这是一个根据配置生成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 可以用#
分割成 3 段
- 例:
col1#function#foo
- 第一段是表的列名,可为空字符串
- 第二段是预留字,可为空字符串
- 第三段仅用于让相同 key 可共存在一个 js 对象中,可填任意字符
预留字 | 可用区域 | 说明 |
---|---|---|
function | select 的 where | 它的值为 SQL 函数,值可为字符串,或字符串数组 |
function | select 的 order | |
function | update 的 data | 它的值为 SQL 函数,值可为字符串,或字符串数组 |
sql |
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: []
// }
支持指定字段排序,也支持排序函数。排序字段支持表别名。
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' ]
// }
简单示例:
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 | 当值为数组时 |
NOT | IS NOT |
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 ],
// }
不同条件之间的关系默认是 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
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],
// }
当 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' ],
// }
select('test', ['`a` = ? AND `b` = ?', ['b', 'a']])
// {
// sql: 'SELECT * FROM `test` WHERE `a` = ? AND `b` = ?',
// values: ["b","a"]
// }
select('test', '`a` = 1 AND `b` = 2')
// {
// sql: 'SELECT * FROM `test` WHERE `a` = 1 AND `b` = 2'
// }
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']
// }
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: []
// }
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此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。