ts-mysql-helper

mysql 帮助类，需要 Node.js 8.0 以上版本

Usage

初始化

import { MysqlHelper } from 'ts-mysql-helper';
const mysqlConfig = {
    host: 'localhost',
    port: 3306,
    database: 'test'
    user: 'root',
    password: 123456,
    charset: 'utf8mb4'
};
const helper = MysqlHelper.getInstance(mysqlConfig);

// 也可以再后期增加新的连接
helper.addPool(mysqlConfig1);
helper.addPool(mysqlConfig2);

多库连接

import { MysqlHelper } from 'ts-mysql-helper';
const helper = MysqlHelper.getInstance();
helper.addPool({
    name: 'db1',
    ...
});
helper.addPool({
    name: 'db2',
    ...
});
helper.addPool({
    name: 'db2',
    ...
});

// 查询时
const result = await helper.selectOne({
    table: 'my_table',
    id: 'db1',  // 指定用哪个库
});

查询一条数据

const tableName = 'my_table';
const fields = ['name', 'age'];
const where = { id: 1 };
const helper = MysqlHelper.getInstance();
async function main() {
    try {
        const values = await helper.selectOne(tableName, fields, where);
        console.log(`val = ${values}`);
    } catch (err) {
        console.log(err.stack);
    }
}

// 同时也支持以对象为参数的方式
// 在可选参数很多的时候，这样做很方便
const values = await helper.selectOne({
    table: tableName,
    fields,
    where,
});

查询多条数据

const tableName = 'my_table';
const fields = ['name', 'age'];
const where = { id: 1 };
const limit = 1;
const helper = MysqlHelper.getInstance();
async function main() {
    try {
        const values = await helper.select(tableName, fields, where, limit);
        console.log(`val = ${values}`);
    } catch (err) {
        console.log(err.stack);
    }
}

更复杂的查询

const tableName = 'my_table';
const fields = ['name', 'age'];
// 范围查询
const where = {id: {
    '>=': 1,
    '<': 10
}};
const limit = 1;
const helper = MysqlHelper.getInstance();
async function main() {
    try {
        const values = await helper.select(tableName, fields, where, limit);
        console.log(`val = ${values}`);
    } catch (err) {
        console.log(err.stack);
    }
}

const tableName = 'my_table';
const fields = ['name', 'age'];
// order by 语句
const where = { id:1 };
const limit = 1;
const order = { id: 'desc' };
const helper = MysqlHelper.getInstance();
async function main() {
    try {
        const values = await helper.select(tableName, fields, where, limit, order);
        console.log(`val = ${values}`);
    } catch (err) {
        console.log(err.stack);
    }
}

插入数据

const tableName = 'my_table';
const values = { name: 'bruce', age: 28 };
const helper = MysqlHelper.getInstance();
async function main() {
    try {
        const insertId = await helper.insertInto(tableName, values);
    } catch (err) {
        console.log(err.stack);
    }
}

批量插入数据

const tableName = 'my_table';
const fieldsArr = ['name', 'age'];
const valueArr = [
    ['bruce', 28],
    ['chris', 29],
    ['doris', 30]
];
const helper = MysqlHelper.getInstance();
async function main() {
    try {
        const results = await helper.batchInsertInto(tableName, fieldsArr, valueArr);
        for (let r of results) {
            console.log('inserted id =', r.insertId);
        }
    } catch (err) {
        console.log(err.stack);
    }
}

插入或更新数据

const tableName = 'my_table';
const values = { name: 'bruce', age: 28 };
const helper = MysqlHelper.getInstance();
async function main() {
    try {
        const insertId = await helper.replaceInto(tableName, values);
    } catch (err) {
        console.log(err.stack);
    }
}

更新数据 #1

const tableName = 'my_table';
const values = { name: 'bruce' };
const where = { id: 1 };
const helper = MysqlHelper.getInstance();
async function main() {
    try {
        const results = await helper.update(tableName, values, where);
        console.log(`res = ${results}`);
    } catch (err) {
        console.log(err.stack);
    }
}

更新数据 #2

const tableName = 'my_table';
// 设定自增
const values = { tagline: 'stay foolish', age: { increment: 1 } };
const where = { id: 1 };
const helper = MysqlHelper.getInstance();
async function main() {
    try {
        const results = await helper.update(tableName, values, where);
        console.log(`res = ${results}`);
    } catch (err) {
        console.log(err.stack);
    }
}

删除数据

const tableName = 'my_table';
const where = { id: 1 };
const helper = MysqlHelper.getInstance();
async function main() {
    try {
        const affectedRows = await helper.delete(tableName, where);
        console.log(`res = ${affectedRows}`);
    } catch (err) {
        console.log(err.stack);
    }
}

自增

const tableName = 'my_table';
const field = 'field1';
const value = 10;
const where = { id: 1 };
const helper = MysqlHelper.getInstance();
async function main() {
    try {
        const result = await helper.increment(tableName, field, value, where);
        console.log(`res = ${result}`);
    } catch (err) {
        console.log(err.stack);
    }
}

如果现有功能还无法满足需求，可以使用 query()

const helper = MysqlHelper.getInstance();
async function main() {
    try {
        const rows = await helper.query('select * from my_table', {id: 1});
    } catch (err) {
        console.log(err.stack);
    }
}