我想用Node.js处理promise-mysql，因此用自己的观点进行了总结

1 年 ago

逸, 科

7 minutes

首先

你好，又是我。
这次我想要尝试处理promise-mysql。
像往常一样，我是摸索着。

为了在Node.js中使用MySQL，我用自己的角度整理了到达这里的经过。

简单的操作确认

设定

const mysql = require('/usr/local/lib/node_modules/promise-mysql');

mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs'
}).then(function(conn) {
    // do stuff with conn
    console.log('promise-mysql createConnection.');
    console.log(conn);
    conn.end();
});

选择

const mysql = require('/usr/local/lib/node_modules/promise-mysql');

mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs'
}).then(function(conn) {
    // do stuff with conn
    console.log('promise-mysql createConnection.');
    // console.log(conn);

    // select
    const result = conn.query('SELECT * FROM node_mysql_test');
    conn.end();
    return result;
}).then(function(rows) {
    console.log(rows);
});

const mysql = require('/usr/local/lib/node_modules/promise-mysql');
let connection;

mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs'
}).then(function(conn) {
    // do stuff with conn
    console.log('promise-mysql createConnection.');
    // console.log(conn);
    connection = conn;
    // select
    const result = connection.query('SELECT * FROM node_mysql_test');
    connection.end();
    console.log(result);
    return result;
}).then(function(rows) {
    // result
    console.log('SELECT is Success!');
    console.log(rows);
}).catch(function(error) {
    if (connection && connection.end) connection.end();
    //logs out the error
    console.log('catch error.');
    console.log(error);
});

请插入

/**
 * promise-mysql module
 * @type       {Object}
 */
const mysql = require('/usr/local/lib/node_modules/promise-mysql');
/**
 * table name
 * @type       {string}
 */
const TABLE_NAME = 'node_mysql_test';
/**
 * connection
 * @type       {Object}
 */
let conn;
let connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs'
}).then(function(connect) {
    console.log('promise-mysql connect.');
    conn = connect;
    // insert
    const sql = 'INSERT INTO ' + TABLE_NAME + ' SET ?';
    const inserts = {name: '鷲尾雷'};
    const result = conn.query(sql, inserts);
    console.log(result);
    return result;
}).then(function(res) {
    console.log(res);
    // select
    const result = conn.query('SELECT * FROM node_mysql_test');
    conn.end();
    return result;
}).then(function(rows) {
    // select is success
    console.log('SELECT is Success!');
    console.log(rows);
}).catch(function(error) {
    if (conn && conn.end) conn.end();
    //logs out the error
    console.log('catch error.');
    console.log(error);
});

更新

/**
 * promise-mysql module
 * @type       {Object}
 */
const mysql = require('/usr/local/lib/node_modules/promise-mysql');
/**
 * table name
 * @type       {string}
 */
const TABLE_NAME = 'node_mysql_test';
/**
 * connection
 * @type       {Object}
 */
let conn;
let connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs'
}).then(function(connect) {
    console.log('promise-mysql connect.');
    conn = connect;
    // insert
    const sql = 'UPDATE ' + TABLE_NAME + ' SET age = ? WHERE name = ?';
    const inserts = [35, '氷室'];
    const result = conn.query(sql, inserts);
    console.log(result);
    return result;
}).then(function(res) {
    console.log(res);
    // select
    const result = conn.query('SELECT * FROM node_mysql_test');
    conn.end();
    return result;
}).then(function(rows) {
    // select is success
    console.log('SELECT is Success!');
    console.log(rows);
}).catch(function(error) {
    if (conn && conn.end) conn.end();
    //logs out the error
    console.log('catch error.');
    console.log(error);
});

恭喜！冰室先生的正确年龄已被记录下来！

刪除

/**
 * promise-mysql module
 * @type       {Object}
 */
const mysql = require('/usr/local/lib/node_modules/promise-mysql');
/**
 * table name
 * @type       {string}
 */
const TABLE_NAME = 'node_mysql_test';
/**
 * connection
 * @type       {Object}
 */
let conn;
let connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs'
}).then(function(connect) {
    console.log('promise-mysql connect.');
    conn = connect;
    // delete
    const sql = 'DELETE FROM ' + TABLE_NAME + ' WHERE name = ?';
    const inserts = ['葛城'];
    const result = conn.query(sql, inserts);
    console.log(result);
    return result;
}).then(function(res) {
    console.log(res);
    // select
    const result = conn.query('SELECT * FROM node_mysql_test');
    conn.end();
    return result;
}).then(function(rows) {
    // select is success
    console.log('SELECT is Success!');
    console.log(rows);
}).catch(function(error) {
    if (conn && conn.end) conn.end();
    //logs out the error
    console.log('catch error.');
    console.log(error);
});

还有其他SELECT的方法

/**
 * promise-mysql module
 * @type       {Object}
 */
const mysql = require('/usr/local/lib/node_modules/promise-mysql');
/**
 * table name
 * @type       {string}
 */
const TABLE_NAME = 'node_mysql_test';
/**
 * pool
 * @type       {Object}
 */
let conn;
let pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs'
});

let sql = 'SELECT * FROM ' + TABLE_NAME;
pool.query(sql).then(function(rows) {
    // select is success
    console.log('SELECT is Success!');
    console.log(rows);
}).catch(function(error) {
    //logs out the error
    console.log('catch error.');
    console.log(error);
});

/**
 * promise-mysql module
 * @type       {Object}
 */
const mysql = require('/usr/local/lib/node_modules/promise-mysql');
/**
 * table name
 * @type       {string}
 */
const TABLE_NAME = 'node_mysql_test';
/**
 * pool
 * @type       {Object}
 */
let conn;
let pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs'
});

pool.getConnection().then(function(connection) {
    let sql = 'SELECT * FROM ' + TABLE_NAME;
    connection.query(sql).then(function(rows) {
        // select is success
        console.log('SELECT is Success!');
        console.log(rows);
    });
}).catch(function(error) {
    //logs out the error
    console.log('catch error.');
    console.log(error);
    done(error);
});

我想要实现交易.

现在比起使用node-mysql的时候，可读性好多了。

/**
 * promise-mysql module
 * @type       {Object}
 */
const mysql = require('/usr/local/lib/node_modules/promise-mysql');
/**
 * table name
 * @type       {string}
 */
const TABLE_NAME = 'node_mysql_test';
/**
 * connection
 * @type       {Object}
 */
let conn;
mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs'
}).then(function(connect) {
    console.log('promise-mysql connect.');
    conn = connect;
    // console.log(conn);
    const result = conn.beginTransaction();
    return result;
}).then(function(res) {
    console.log(res);
    let sql = 'SELECT * FROM ' + TABLE_NAME;
    return conn.query(sql);
}).then(function(rows) {
    // select is success
    console.log('SELECT is Success!');
    console.log(rows);
    // insert data
    let sql = 'INSERT INTO ' + TABLE_NAME + ' SET ?';
    let inserts = {name: '桐生', age: 26};
    const result = conn.query(sql, inserts);
    return result;
}).then(function(res) {
    console.log('INSERT is Success!');
    console.log(res);
    // const result = conn.rollback();    // ここでロールバック発動すれば実行されずに終わる
    const result = conn.commit();
    return result;
}).then(function(res) {
    console.log('COMMIT Success!');
    console.log(res);
    // select data
    let sql = 'SELECT * FROM ' + TABLE_NAME;
    const result = conn.query(sql);
    return result;
}).then(function(rows) {
    // select is success
    console.log('SELECT is Success!');
    console.log(rows);
    return Promise.resolve(1);    // 次のFunctionに行く引数
}).then(function(res) {
    console.log(res);
    // 最後に必ずコネクションを開放する
    if (conn)
    {
        conn.end();
    }
});

关于连接池的内容

/**
 * promise-mysql module
 * @type       {Object}
 */
const mysql = require('/usr/local/lib/node_modules/promise-mysql');
/**
 * table name
 * @type       {string}
 */
const TABLE_NAME = 'node_mysql_test';
/**
 * connection
 * @type       {Object}
 */
let conn;
/**
 * pool
 * @type       {Object}
 */
let pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs',
    connectionLimit: 10,
})

pool.getConnection().then(function(connect) {
    console.log('promise-mysql connect.');
    conn = connect;
    // console.log(conn);
    const result = conn.beginTransaction();
    return result;
}).then(function(res) {
    console.log(res);
    let sql = 'SELECT * FROM ' + TABLE_NAME;
    return conn.query(sql);
}).then(function(rows) {
    // select is success
    console.log('SELECT is Success!');
    console.log(rows);
    // insert data
    let sql = 'INSERT INTO ' + TABLE_NAME + ' SET ?';
    let inserts = {name: '最上', age: 52};
    const result = conn.query(sql, inserts);
    return result;
}).then(function(res) {
    console.log('INSERT is Success!');
    console.log(res);
    // const result = conn.rollback();    // ここでロールバック発動すれば実行されずに終わる
    const result = conn.commit();
    return result;
}).then(function(res) {
    console.log('COMMIT Success!');
    console.log(res);
    // select data
    let sql = 'SELECT * FROM ' + TABLE_NAME;
    const result = conn.query(sql);
    return result;
}).then(function(rows) {
    // select is success
    console.log('SELECT is Success!');
    console.log(rows);
    return Promise.resolve(1);    // 次のFunctionに行く引数
}).then(function(res) {
    console.log(res);
    // 最後に必ずコネクションを開放する
    // console.log(conn);
    // if (conn && conn.end)
    // {
    //     conn.end();
    // }
    pool.releaseConnection(conn);
}).catch(function(err) {
    console.log('catch error.');
    done(err);
});

在使用pool的情况下，开始的时候使用pool.getConnection()获取连接，结束的时候使用pool.releaseConnection(connection)释放连接。

你不能使用async/await吗？

可以写。实际上这边更聪明。

async mysqlSelect()
{
    let conn = await mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: '',
        database: 'nodejs'
    });

    let sql = 'SELECT * FROM ' + TABLE_NAME;
    const result = await conn.query(sql);
    console.log(result);
}

最后

這篇文章還沒有預定的更新。
另外，如果有資訊的話，請在評論中提供，將不勝感激。
謝謝。