dj-node-mysql
Advanced tools
$ npm install dj-node-mysql
var Mysql = require('node-mysql-promise');
var mysql = Mysql.createConnection({
host : 'localhost',
user : 'username',
password : 'password'
});
//SELECT * FROM table;
mysql.table('table').select().then(function (data) {
console.log(data);
}).catch(function (e) {
console.log(e);
});
##API
host: 连接的host（默认: localhost）port: 连接端口user: 用户名password: 密码database: 数据库名tablePrefix: 数据表前缀charset: 编码（默认: UTF8_GENERAL_CI）timezone: 时区（默认: 'local'）connectTimeout: 连接超时时间（默认: 10000）connectionLimit: 最大连接数（默认: 10）logSql: 控制台输出sql（默认: false）设置要查询的表（必需）
tableName String 要查询的表return this//SELECT * FROM `table`
mysql.table('table').select()
设置要查询的字段
field String|Array 要查询的字段，可以是字符串，也可以是数组reverse Boolean 是否反选字段return this//SELECT * FROM `table`
mysql.table('table').field().select();
//SELECT `id`, `title` FROM `table`
mysql.table(['id', 'title']).select();
//SELECT `id`, `title` as kk FROM `table`
mysql.table('table').field(['id', ['title', 'kk']], true).select();
设置查询的数量
offset Number 起始位置length Number 查询的数目return this//SELECT * FROM `table` LIMIT 10
mysql.table('table').limit(10).select();
//SELECT * FROM `table` LIMIT 10, 20
mysql.table('table').limit(10, 20).select();
设置当前查询的页数，页数从1开始
page Number 当前的页数listRows Number 一页记录条数，默认20条return this//SELECT * FROM `table`
mysql.table('table').page().select();
//SELECT * FROM `table` LIMIT 0,20
mysql.table('table').page(1).select();
//SELECT * FROM `table` LIMIT 10, 20
mysql.table('table').page(2, 10).select();
联合查询
union String 联合查询的字符串all 是否为UNION ALL模式return this//SELECT * FROM `table` UNION (SELECT * FROM `table2`)
mysql.table('table').union('SELECT * FROM `table2`').select();
//SELECT * FROM `table` UNION ALL (SELECT * FROM `table2`)
mysql.table('table').union('SELECT * FROM `table2`', true).select();
//SELECT * FROM `table` UNION ALL (SELECT * FROM `table2`)
mysql.table('table').union({table: 'table2'}, true);
//SELECT * FROM `table` UNION ALL (SELECT * FROM `table2`) UNION (SELECT * FROM `table3`)
mysql.table('table').UNION({table: 'table2`}, true).union({table: 'table3'});
组合查询
join String|Array|Objectreturn this//SELECT * FROM `table` LEFT JOIN `table2` ON table.id = table2.id
mysql.table('table').join('table2 on table.id = table2.id').select();
//SELECT * FROM `table` LEFT JOIN `table2` ON table.id = table2.id RIGHT JOIN `table3` ON table.sid = table3.sid
mysql.table('table').join('table2 ON table.id = table2.id', 'RIGHT JOIN table3 ON table.sid = table3.sid').select();
//SELECT * FROM `table` INNER JOIN `table2` on table.id = table2.id
mysql.table('table').join({
table: 'table2',
join: 'inner',//left, right, inner三种方式
as: 'c' //表别名
on: ['id', 'id'] //ON 条件
}).select();
//SELECT * FROM `table` AS a LEFT JOIN `table2` AS b ON a.id = b.id LEFT JOIN `table3` AS c ON a.sid = c.sid
mysql.table('table').alias('a').join({
table: 'table2',
join: 'left',
as: 'b'
on: ['id', 'id']
}).join({
table: 'table3',
join: 'left',
as: 'c',
on: ['sid', 'sid']
}).select();
//SELECT * FROM `table` AS a LEFT JOIN `table2` AS b ON a.id = b.id LEFT JOIN `table3` AS c ON a.sid = c.sid
mysql.table('table').join({
table2: {
join: 'left',
as: 'b',
on: ['id', 'id']
},
table3: {
join: 'left',
as: 'c',
on: ['sid', 'sid']
}
}).select();
//SELECT * FROM `table` LEFT JOIN `table2` ON table.id = table2.id LEFT JOIN `table3` ON (table.sid = table3.sid AND table.name = table3.title);
mysql.table('table').join({
table2: {
on: ['id', 'id']
},
table3: {
on: {
id: 'id',
title: 'name'
}
}
}).select();
设置排序方式
order String|Array|Obeject 排序方式return this//SELECT * FROM `table` ORDER BY `id`
mysql.table('table').order('id').select();
//SELECT * FROM `table` ORDER BY `id` DESC
mysql.table('table').order('id DESC').select();
//SELECT * FROM `table` ORDER BY `id` DESC, `title` ASC
mysql.table('table').order('id DESC, title ASC').select();
//SELECT * FROM `table` ORDER BY `id` DESC, `title` ASC
mysql.table('table').order(['id DESC', 'title ASC']).select();
//SELECT * FROM `table` ORDER BY `id` DESC `title` ASC
mysql.table('table').order({id: 'DESC', title: 'ASC'}).select();
设置表别名
alias String 表别名return this//SELECT * FROM `table` AS t
mysql.table('table').alias('t').select();
having查询
str String having查询的字符串return this//SELECT * FROM `table` HAVING `id` > 1 AND `id` < 100
mysql.table('table').having('id > 1 AND id < 100').select();
分组查询
field String 设定分组查询的字段return this//SELECT * FROM `table` GROUP BY `date`
mysql.table('table').group('date').select();
去重查询
field String 去重的字段return this//SELECT DISTINCT `title` FROM `table`
mysql.table('table').distinct('title').select();
设置where条件
where Sting|Object 查询条件return this//SELECT * FROM `table` WHERE `id` = 100;
mysql.table('table').where('id = 100').select();
//SELECT * FROM `table` WHERE `id` = 100;
mysql.table('table').where({id: 100}).select();
//SELECT * FROM `table` WHERE `id` = 100 OR `id` < 2
mysql.table('table').where('id = 100 OR id < 2').select();
//SELECT * FROM `table` WHERE `id` != 100
mysql.table('table').where({id: ['!=', 100]})
默认会对字段和值进行转义，如果不希望被转义，可是使用EXP的方式
//SELECT * FROM `table` WHERE `name` = 'name'
mysql.table('table').where({name: ['EXP', "='name'"]}).select();
//UPDATE `table` SET `num' = `num`+1
mysql.table('table').update({num: ['EXP', 'num+1']});
//SELECT * FROM `table` WHERE (`title` NOT LIKE 'title')
mysql.table('table').where({title: ['NOT LIKE', 'title']}).select();
//SELECT * FROM `table` WHERE (`title` LIKE '%title%')
mysql.table('table').where({title: ['LIKE', '%title%']}).select();
//LIKE多个值
//SELECT * FROM `table` WHERE (`title` LIKE 'title' OR `title` LIKE 'name')
mysql.table('table').where({title: ['LIKE', ['title', 'name']]}).select();
//多个字段LIKE同一个值，OR的关系
//SELECT * FROM `table` WHERE ((`title` LIKE '%title%') OR (`content` LIKE '%title%'))
mysql.table('table').where({'title|content': ['LIKE', '%title%']}).select();
//多个字段LIKE同一个值，AND的关系
//SELECT * FROM `table` WHERE ((`title` LIKE '%title%') AND (`content` LIKE '%title%'))
mysql.table('table').where({'title&content': ['LIKE', '%title%']}).select();
//SELECT * FROM `table` WHERE (`id` IN (1,2,3))
mysql.table('table').where({id: ['IN', '1, 2, 3']}).select();
//SELECT * FROM `table` WHERE (`id` IN (1, 2, 3))
mysql.table('table').where({id: ['IN', [1, 2, 3]]}).select();
//SELECT * FROM `table` WHERE (`id` NOT IN (1, 2, 3))
mysql.table('table').where({id: ['NOT IN', [1, 2, 3]]}).select();
//SELECT * FROM `table` WHERE (`id` = 10) AND (`title` = 'title')
mysql.table('table').where({id: 10, title: 'title'}).select();
//OR
//SELECT * FROM `table` WHERE (`id` = 10) OR (`title` = 'title')
mysql.table('table').where({id: 10, title: 'title', _logic: 'OR'}).select();
//XOR
//SELECT * FROM `table` WHERE (`id` = 10) XOR (`title` = 'title')
mysql.table('table').where({id: 10, title: 'title', _logic: 'XOR'}).select();
//SELECT * FROM `table` WHERE (`id` BETWEEN 1 AND 2)
mysql.table('table').where({id: ['BETWEEN', 1, 2]}).select();
//SELECT * FROM `table` WHERE (`id` BETWEEN 1 AND 2)
mysql.table('table').where({id: ['BETWEEN', '1,2']}).select();
//SELECT * FROM `table` WHERE `id` > 10 AND `id` < 20
mysql.table('table').where({id: {
'>': 10,
'<': 20
}}).select();
//SELECT * FROM `table` WHERE `id` < 10 OR `id` > 20
mysql.table('table').where({id: {
'<': 10,
'>': 20,
_logic: 'OR'
}}).select();
//SELECT * FROM `table` WHERE (`id` > 10 AND `id` < 20) OR (`title` LIKE '%title%')
mysql.table('table').where({id: {
'>': 10,
'<': 20
}, title: ['LIKE', '%title%']}).select();
//SELECT * FROM `table` WHERE (`title` = 'title') AND ((`id` IN (1, 2, 3)) OR (`content` = 'content'))
mysql.table('table').where({
title: 'title',
_complex: {
id: ['IN', [1, 2, 3]],
content: 'content',
_logic: 'OR'
}
}).select();
查询符合条件的数目
field String count的字段return promise//SELECT COUNT(`id`) FROM `table` LIMIT 1
mysql.table('table').count('id').then(function (count) {
//count为符合条件的数目
})
求和
field String 要求和的字段return promise//SELECT SUM(`num`) FROM `table` LIMIT 1
mysql.table('table').sum('num').then(function (sum) {
//sum为求和的值
});
求字段的最大值
field String 要求最大值的字段return promise//SELECT MAX(`num`) FROM `table` LIMIT 1
mysql.table('table').max('num').then(function (max) { //max为num的最大值
});
求字段的最小值
field String 要求最小值的字段return promise//SELECT MIN(`num`) FROM `table` LIMIT 1
mysql.table('table').min('num').then(function (min) {
//min为num的最小值
})
求字段的平均值
field Sting 要求平均值的字段return promise//SELECT AVG(`num`) FROM `table` LIMIT 1;
mysql.table('table').avg('num').then(function (avg) {
//avg为num的平均值
})
插入数据
data Object 要插入的数据return promisevar data = {
title: 'title',
content: 'content'
};
mysql.table('table').insert(data).then(function (insertId) {
//如果插入成功，返回插入的id
}).catch(function (err) {
//插入失败，err为具体的错误信息
})
当数据表中不存在where条件对应的数据时才进行插入
data Object 要插入的数据where String|Array|Object 检测的条件returnDetail Boolean 是否返回详细的信息//假设字段title为UNIQUE
var data = {
title: 'title',
content: 'content'
};
var where = {
title: 'title'
}
mysql.table('table').thenInsert(data, where).then(function (id) {
//返回已经存在或者刚插入的id
})
//返回详细信息
mysql.table('table').thenInsert(data, where, true).then(function (data) {
/*
data数据结构为
{
type: 'exist' || 'add', //exist表示已存在，add新增
id: 1
}
*/
})
一次添加多条数据
data Arrayreturn promisevar data = [{title: 'xxx'}, {title: 'yyy'}];
mysql.table('table').InsertAll(data).then(function (insertId) {
//插入成功
}).catch(function (err) {
//插入失败
})
删除数据
return promise//删除所有数据
mysql.table('table').delete().then(function (affectRows) {
//返回影响行数
})
//删除符合条件的数据
mysql.table('table').where(where).delete().then(functino (affectRows) {
//返回影响的行数
})
更新数据，需要条件
data Object 要更新的数据return promisemysql.table('table').where(where).update(data).then(function (affectRows) {
//返回影响行数
})
查询符合条件的数据
return promisemysql.table('table').where(where).select().then(function (data) {
//返回结果 Array
})
查找一条符合条件的数据
return promisemysql.table('table').where(where).find().then(function (data) {
//返回结果 Object
})
字段值增加
field String 要增加的字段step Number 增加的数值，默认为1return promise//将id为1的num字段加10
mysql.table('table').where({id: 1}).updateInc('num', 10).then(function () {
})
字段值减少
field String 要减少的字段step Number 减少的数字，默认为1return promise//将id为1的num字段值减10
mysql.table('table').where({id: 1}).updateDec('num', 10).then(function () {
})
获取某个字段的值
field String 要获取的字段，可以是多个字段（用,隔开）onlyOne Boolean|Array 是否只需要一个值，或者是需要几个值//取id>100的id集合
mysql.table('table').where({id: ['>', 100]}).getField('id').then(function (data) {
//data为Array，是符合结果的所有集合
//data = [101, 102, 103, 104]
})
//只需要id>100的一个值
mysql.table('table').where({id: ['>': 100]}).getField('id', true).then(function (data) {
//data为数字，符合条件的第一个值
//data = 101
})
//只需要id>100的3个值
mysql.table('table').where({id: ['>' 100]}).getField('id', 3).then(function (data) {
//data为Array
//data = [101, 102, 103]
})
//需要id和title两个字段的值
mysql.table('table').getField('id, title').then(function (data) {
//data为对象
/*
data = {
id: [101, 102, 103, 104],
title: ['aaaa', 'bbbb', 'cccc', 'dddd']
}
*/
})
options 查询参数flag Boolean 当分页值不合法的时候，处理情况。true为修正到第一页，false为修正到最后一页，默认不进行修正return promise//查询1-20条数据
mysql.table('table').page(1, 20).countSelect().then(function (data) {
//data数据格式
data = {
count: 123, //总条数
total: 7 //总页数
page: 1 //当前页
num: 20 //每页显示数量
data： [{}, {}] //详细数据
}
});
自定义sql语句进行查询
sql String 要执行的sql语句parse 格式参数的数据return promisevar data = [
'*',
'table',
'id > 100'
]
mysql.query('SELECT %s FROM %s WHERE %s', data).then(function (data) {
})
自定义sql语句执行，使用与query相同，返回数据不同，execute返回影响行数
关闭连接池连接，非特殊情况，不建议使用
FAQs
egg mysql
We found that dj-node-mysql demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Product
Socket Fix 2.0 brings targeted CVE remediation, smarter upgrade planning, and broader ecosystem support to help developers get to zero alerts.
Security News
Socket CEO Feross Aboukhadijeh joins Risky Business Weekly to unpack recent npm phishing attacks, their limited impact, and the risks if attackers get smarter.
Product
Socket’s new Tier 1 Reachability filters out up to 80% of irrelevant CVEs, so security teams can focus on the vulnerabilities that matter.