Node.js，Express :MySQL操作超好用的包，node-mysql-promise

node-mysql-promise

请在已经搭好express项目前提下，安装

安装

$ npm install node-mysql-promise

说明
node mysql操作封装类，基于promise，借鉴75team开源项目thinkjs中model操作，数据库连接使用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）

方法

table(tableName)

设置要查询的表（必需）

• tableName String 要查询的表
• return this

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

field(field, reverse)

设置要查询的字段

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

//SELECT * FROM `table`
mysql.table('table').field().select();

//SELECT `id`, `title` FROM `table`
mysql.table('table').field('id, title').select();

//SELECT `id`, `title` FROM `table`
mysql.table(['id', 'title']).select();

//SELECT `author`, `date` FROM `table`
mysql.table('table').field(['id', 'title'], true).select();

limit(offset, length)

设置查询的数量

• 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();

page(page, listRows)

设置当前查询的页数，页数从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(union, all)

联合查询

• 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(join)

组合查询

• join String|Array|Object
• return 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'</