node-mysql-promise

Install

$ npm install uh-node-mysql-promise

Introduction

node mysql操作封装类，基于promise，借鉴75team开源项目thinkjs中model操作，数据库连接使用node-mysql的连接池。

使用示例

1var Mysql = require('uh-node-mysql-promise');
2var mysql = Mysql.createConnection({
3	host        : 'localhost',
4	user        : 'username',
5	password    : 'password'
6});
7//SELECT * FROM table;
8mysql.table('table').select().then(function (data) {
9	console.log(data);
10}).catch(function (e) {
11	console.log(e);
12});

##API

配置

• host: 连接的host（默认: localhost）
• port: 连接端口
• user: 用户名
• password: 密码
• database: 数据库名
• tablePrefix: 数据表前缀
• charset: 编码（默认: UTF8_GENERAL_CI）
• timezone: 时区（默认: 'local'）
• connectTimeout: 连接超时时间（默认: 10000）
• connectionLimit: 最大连接数（默认: 10）
• logSql: 控制台输出sql（默认: false）

方法

table(tableName)

设置要查询的表（必需）

• tableName String 要查询的表
• return this

1//SELECT * FROM `table`
2mysql.table('table').select()

field(field, reverse)

设置要查询的字段

• field String|Array 要查询的字段，可以是字符串，也可以是数组
• reverse Boolean 是否反选字段
• return this

1//SELECT * FROM `table`
2mysql.table('table').field().select();
3//SELECT `id`, `title` FROM `table`
4mysql.table('table').field('id, title').select();
5//SELECT `id`, `title` FROM `table`
6mysql.table(['id', 'title']).select();
7//SELECT `author`, `date` FROM `table`
8mysql.table('table').field(['id', 'title'], true).select();

limit(offset, length)

设置查询的数量

• offset Number 起始位置
• length Number 查询的数目
• return this

1//SELECT * FROM `table` LIMIT 10
2mysql.table('table').limit(10).select();
3//SELECT * FROM `table` LIMIT 10, 20
4mysql.table('table').limit(10, 20).select();

page(page, listRows)

设置当前查询的页数，页数从1开始

• page Number 当前的页数
• listRows Number 一页记录条数，默认20条
• return this

1//SELECT * FROM `table`
2mysql.table('table').page().select();
3//SELECT * FROM `table` LIMIT 0,20
4mysql.table('table').page(1).select();
5//SELECT * FROM `table` LIMIT 10, 20
6mysql.table('table').page(2, 10).select();

union(union, all)

联合查询

• union String 联合查询的字符串
• all 是否为UNION ALL模式
• return this

1//SELECT * FROM `table` UNION (SELECT * FROM `table2`)
2mysql.table('table').union('SELECT * FROM `table2`').select();
3//SELECT * FROM `table` UNION ALL (SELECT * FROM `table2`)
4mysql.table('table').union('SELECT * FROM `table2`', true).select();
5//SELECT * FROM `table` UNION ALL (SELECT * FROM `table2`)
6mysql.table('table').union({table: 'table2'}, true);
7//SELECT * FROM `table` UNION ALL (SELECT * FROM `table2`) UNION (SELECT * FROM `table3`)
8mysql.table('table').UNION({table: 'table2`}, true).union({table: 'table3'});

join(join)

组合查询

• join String|Array|Object
• return this

1//SELECT * FROM `table` LEFT JOIN `table2` ON table.id = table2.id
2mysql.table('table').join('table2 on table.id = table2.id').select();
3//SELECT * FROM `table` LEFT JOIN `table2` ON table.id = table2.id RIGHT JOIN `table3` ON table.sid = table3.sid
4mysql.table('table').join('table2 ON table.id = table2.id', 'RIGHT JOIN table3 ON table.sid = table3.sid').select();
5//SELECT * FROM `table` INNER JOIN `table2` on table.id = table2.id
6mysql.table('table').join({
7	table: 'table2',
8	join: 'inner',//left, right, inner三种方式
9	as: 'c' //表别名
10	on: ['id', 'id'] //ON 条件
11}).select();
12//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
13mysql.table('table').alias('a').join({
14	table: 'table2',
15	join: 'left',
16	as: 'b'
17	on: ['id', 'id']
18}).join({
19	table: 'table3',
20	join: 'left',
21	as: 'c',
22	on: ['sid', 'sid']
23}).select();		
24//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
25mysql.table('table').join({
26	table2: {
27		join: 'left',
28		as: 'b',
29		on: ['id', 'id']
30	},
31	table3: {
32		join: 'left',
33		as: 'c',
34		on: ['sid', 'sid']
35	}
36}).select();		
37//SELECT * FROM `table` LEFT JOIN `table2` ON table.id = table2.id LEFT JOIN `table3` ON (table.sid = table3.sid AND table.name = table3.title);
38mysql.table('table').join({
39	table2: {
40		on: ['id', 'id']
41	},
42	table3: {
43		on: {
44			id: 'id',
45			title: 'name'
46		}
47	}
48}).select();

order(order)

设置排序方式

• order String|Array|Obeject 排序方式
• return this

1//SELECT * FROM `table` ORDER BY `id`
2mysql.table('table').order('id').select();		
3//SELECT * FROM `table` ORDER BY `id` DESC
4mysql.table('table').order('id DESC').select();
5//SELECT * FROM `table` ORDER BY `id` DESC, `title` ASC
6mysql.table('table').order('id DESC, title ASC').select();
7//SELECT * FROM `table` ORDER BY `id` DESC, `title` ASC
8mysql.table('table').order(['id DESC', 'title ASC']).select();
9//SELECT * FROM `table` ORDER BY `id` DESC `title` ASC
10mysql.table('table').order({id: 'DESC', title: 'ASC'}).select();

alias(alias)

设置表别名

• alias String 表别名
• return this

1//SELECT * FROM `table` AS t
2mysql.table('table').alias('t').select();

having(str)

having查询

• str String having查询的字符串
• return this

1//SELECT * FROM `table` HAVING `id` > 1 AND `id` < 100
2mysql.table('table').having('id > 1 AND id < 100').select();

group(field)

分组查询

• field String 设定分组查询的字段
• return this

1//SELECT * FROM `table` GROUP BY `date`
2mysql.table('table').group('date').select();

distinct(field)

去重查询

• field String 去重的字段
• return this

1//SELECT DISTINCT `title` FROM `table`
2mysql.table('table').distinct('title').select();

where(where)

设置where条件

• where Sting|Object 查询条件
• return this

普通条件

1//SELECT * FROM `table` WHERE `id` = 100;
2mysql.table('table').where('id = 100').select();
3//SELECT * FROM `table` WHERE `id` = 100;
4mysql.table('table').where({id: 100}).select();
5//SELECT * FROM `table` WHERE `id` = 100 OR `id` < 2
6mysql.table('table').where('id = 100 OR id < 2').select();
7//SELECT * FROM `table` WHERE `id` != 100
8mysql.table('table').where({id: ['!=', 100]})

EXP条件

默认会对字段和值进行转义，如果不希望被转义，可是使用EXP的方式

1//SELECT * FROM `table` WHERE `name` = 'name'
2mysql.table('table').where({name: ['EXP', "='name'"]}).select();
3//UPDATE `table` SET `num' = `num`+1
4mysql.table('table').update({num: ['EXP', 'num+1']});

LIKE条件

1//SELECT * FROM `table` WHERE (`title` NOT LIKE 'title')
2mysql.table('table').where({title: ['NOT LIKE', 'title']}).select();
3//SELECT * FROM `table` WHERE (`title` LIKE '%title%')
4mysql.table('table').where({title: ['LIKE', '%title%']}).select();
5//LIKE多个值
6//SELECT * FROM `table` WHERE (`title` LIKE 'title' OR `title` LIKE 'name')
7mysql.table('table').where({title: ['LIKE', ['title', 'name']]}).select();
8//多个字段LIKE同一个值，OR的关系
9//SELECT * FROM `table` WHERE ((`title` LIKE '%title%') OR (`content` LIKE '%title%'))
10mysql.table('table').where({'title|content': ['LIKE', '%title%']}).select();
11//多个字段LIKE同一个值，AND的关系
12//SELECT * FROM `table` WHERE ((`title` LIKE '%title%') AND (`content` LIKE '%title%'))
13mysql.table('table').where({'title&content': ['LIKE', '%title%']}).select();

IN条件

1//SELECT * FROM `table` WHERE (`id` IN (1,2,3))
2mysql.table('table').where({id: ['IN', '1, 2, 3']}).select();
3//SELECT * FROM `table` WHERE (`id` IN (1, 2, 3))
4mysql.table('table').where({id: ['IN', [1, 2, 3]]}).select();
5//SELECT * FROM `table` WHERE (`id` NOT IN (1, 2, 3))
6mysql.table('table').where({id: ['NOT IN', [1, 2, 3]]}).select();

多字段查询

1//SELECT * FROM `table` WHERE (`id` = 10) AND (`title` = 'title')
2mysql.table('table').where({id: 10, title: 'title'}).select();
3//OR
4//SELECT * FROM `table` WHERE (`id` = 10) OR (`title` = 'title')
5mysql.table('table').where({id: 10, title: 'title', _logic: 'OR'}).select();
6//XOR
7//SELECT * FROM `table` WHERE (`id` = 10) XOR (`title` = 'title')
8mysql.table('table').where({id: 10, title: 'title', _logic: 'XOR'}).select();

BETWEEN

1//SELECT * FROM `table` WHERE (`id` BETWEEN 1 AND 2)
2mysql.table('table').where({id: ['BETWEEN', 1, 2]}).select();
3//SELECT * FROM `table` WHERE (`id` BETWEEN 1 AND 2)
4mysql.table('table').where({id: ['BETWEEN', '1,2']}).select();

复合查询

1//SELECT * FROM `table` WHERE `id` > 10 AND `id` < 20
2mysql.table('table').where({id: {
3	'>': 10,
4	'<': 20
5}}).select();
6//SELECT * FROM `table` WHERE `id` < 10 OR `id` > 20
7mysql.table('table').where({id: {
8	'<': 10,
9	'>': 20,
10	_logic: 'OR'
11}}).select();
12//SELECT * FROM `table` WHERE (`id` > 10 AND `id` < 20) OR (`title` LIKE '%title%')
13mysql.table('table').where({id: {
14	'>': 10,
15	'<': 20
16}, title: ['LIKE', '%title%']}).select();
17//SELECT * FROM `table` WHERE (`title` = 'title') AND ((`id` IN (1, 2, 3)) OR (`content` = 'content'))
18mysql.table('table').where({
19	title: 'title',
20	_complex: {
21		id: ['IN', [1, 2, 3]],
22		content: 'content',
23		_logic: 'OR'
24	}
25}).select();

count(field)

查询符合条件的数目

• field String count的字段
• return promise

1//SELECT COUNT(`id`) FROM `table` LIMIT 1
2mysql.table('table').count('id').then(function (count) {
3	//count为符合条件的数目		
4})

sum(field)

求和

• field String 要求和的字段
• return promise

1//SELECT SUM(`num`) FROM `table` LIMIT 1
2mysql.table('table').sum('num').then(function (sum) {
3	//sum为求和的值	
4});

max(field)

求字段的最大值

• field String 要求最大值的字段
• return promise

1//SELECT MAX(`num`) FROM `table` LIMIT 1
2mysql.table('table').max('num').then(function (max) {	//max为num的最大值
3});

min(field)

求字段的最小值

• field String 要求最小值的字段
• return promise

1//SELECT MIN(`num`) FROM `table` LIMIT 1
2mysql.table('table').min('num').then(function (min) {
3	//min为num的最小值	
4})

avg(field)

求字段的平均值

• field Sting 要求平均值的字段
• return promise

1//SELECT AVG(`num`) FROM `table` LIMIT 1;
2mysql.table('table').avg('num').then(function (avg) {
3	//avg为num的平均值	
4})

add(data)

插入数据

• data Object 要插入的数据
• return promise

1var data  = {
2	title: 'title',
3	content: 'content'
4};
5mysql.table('table').add(data).then(function (insertId) {
6	//如果插入成功，返回插入的id
7	}).catch(function (err) {
8		//插入失败，err为具体的错误信息
9	})

thenAdd(data, where, returnDetail)

当数据表中不存在where条件对应的数据时才进行插入

• data Object 要插入的数据
• where String|Array|Object 检测的条件
• returnDetail Boolean 是否返回详细的信息

1//假设字段title为UNIQUE
2var data = {
3	title: 'title',
4	content: 'content'
5};
6var where = {
7	title: 'title'
8}
9mysql.table('table').thenAdd(data, where).then(function (id) {
10	//返回已经存在或者刚插入的id
11})		
12//返回详细信息
13mysql.table('table').thenAdd(data, where, true).then(function (data) {
14	/*
15	data数据结构为
16	{
17		type: 'exist' || 'add',  //exist表示已存在，add新增
18		id: 1
19	}
20	*/	
21})

addAll(data)

一次添加多条数据

• data Array
• return promise

1var data = [{title: 'xxx'}, {title: 'yyy'}];
2mysql.table('table').addAll(data).then(function (insertId) {
3	//插入成功
4}).catch(function (err) {
5	//插入失败
6})

delete()

删除数据

• return promise

1//删除所有数据
2mysql.table('table').delete().then(function (affectRows) {
3	//返回影响行数
4})		
5//删除符合条件的数据
6mysql.table('table').where(where).delete().then(functino (affectRows) {
7	//返回影响的行数
8})

update(data)

更新数据，需要条件

• data Object 要更新的数据
• return promise

1mysql.table('table').where(where).update(data).then(function (affectRows) {
2	//返回影响行数
3})

select()

查询符合条件的数据

• return promise

1mysql.table('table').where(where).select().then(function (data) {
2	//返回结果 Array
3})

find()

查找一条符合条件的数据

• return promise

1mysql.table('table').where(where).find().then(function (data) {
2	//返回结果 Object
3})

updateInc(field, step)

字段值增加

• field String 要增加的字段
• step Number 增加的数值，默认为1
• return promise

1//将id为1的num字段加10
2mysql.table('table').where({id: 1}).updateInc('num', 10).then(function () {
3})

updateDec(field, step)

字段值减少

• field String 要减少的字段
• step Number 减少的数字，默认为1
• return promise

1//将id为1的num字段值减10
2mysql.table('table').where({id: 1}).updateDec('num', 10).then(function () {
3})

getField(field, onlyOne)

获取某个字段的值

• field String 要获取的字段，可以是多个字段（用,隔开）
• onlyOne Boolean|Array 是否只需要一个值，或者是需要几个值

1//取id>100的id集合
2mysql.table('table').where({id: ['>', 100]}).getField('id').then(function (data) {
3	//data为Array，是符合结果的所有集合
4	//data = [101, 102, 103, 104]
5})		
6//只需要id>100的一个值
7mysql.table('table').where({id: ['>': 100]}).getField('id', true).then(function (data) {
8	//data为数字，符合条件的第一个值
9	//data = 101
10})
11//只需要id>100的3个值
12mysql.table('table').where({id: ['>' 100]}).getField('id', 3).then(function (data) {
13	//data为Array
14	//data = [101, 102, 103]
15})
16//需要id和title两个字段的值
17mysql.table('table').getField('id, title').then(function (data) {
18	//data为对象
19	/*
20	data = {
21		id: [101, 102, 103, 104],
22		title: ['aaaa', 'bbbb', 'cccc', 'dddd']
23	}
24	*/
25})

countSelect(options, flag)

• options 查询参数
• flag Boolean 当分页值不合法的时候，处理情况。true为修正到第一页，false为修正到最后一页，默认不进行修正
• return promise

1//查询1-20条数据
2mysql.table('table').page(1, 20).countSelect().then(function (data) {
3	//data数据格式
4	data = {
5		count: 123, //总条数
6		total: 7    //总页数
7		page: 1     //当前页
8		num: 20     //每页显示数量
9		data： [{}, {}] //详细数据
10	}
11});

query(sql, parse)

自定义sql语句进行查询

• sql String 要执行的sql语句
• parse 格式参数的数据
• return promise

1var data = [
2	'*',
3	'table',
4	'id > 100'
5]	
6mysql.query('SELECT %s FROM %s WHERE %s', data).then(function (data) {
7})

execute(sql, parse)

自定义sql语句执行，使用与query相同，返回数据不同，execute返回影响行数

close()

关闭连接池连接，非特殊情况，不建议使用