NodeJS操作mysql

背景

在 KOA2 中操作mysql 数据库，行程本文。

安装包

yarn add mysql  --save

导入

var mysql = require('mysql');

创建 mysql 数据连接

  var connection = mysql.createConnection(option);
  connection.connect();
  console.log(`打开数据库成功`);
  return connection;

操作数据库

  let promise = new Promise((resolve, reject) => {
    let sql2 = mysql.format(sql, paraArray);
    console.log(`  #sql: ${sql2}`);
    db.query(sql2,paraArray,(error, results, fields)=>{
      let str ='  #result:';
      str+=' error:'+error;
      str+=' results:'+JSON.stringify(results);
      str+=' fields:'+fields;
      console.log(str+'\n');
      if (error) {
        reject(error);
      };
      resolve(results,fields);
    })
  });

其他

由于我在koa 中使用 mysql, 我期望类似 同步的形式访问数据库。我的 MysqlDbHelper 类如下：

    # 概述

  # 安装包

      yarn add mysql


  # 导入

    var mysql = require('mysql'var mysql = require('mysql');
    var env = require('./env.js');
    var fs = require("fs");
    var path = require('path');

    const option = {
      host     : env.db_host,
      user     : env.db_user,
      password : env.db_password,
      database : env.db_name,
    };

    function querySync(db,sql,paraArray){
      // console.log('para is'+JSON.stringify(paraArray));
      let promise = new Promise((resolve, reject) => {
        let sql2 = mysql.format(sql, paraArray);
        console.log(`  #sql: ${sql2}`);
        db.query(sql2,paraArray,(error, results, fields)=>{
          let str ='  #result:';
          str+=' error:'+error;
          str+=' results:'+JSON.stringify(results);
          str+=' fields:'+fields;
          console.log(str+'\n');
          if (error) {
            reject(error);
          };
          resolve(results,fields);
        })
      });
      return promise;
    }

    let MysqlDbHelper = {

      createConnection: function() {
        var connection = mysql.createConnection(option);
        connection.connect();
        console.log(`打开数据库成功`);
        return connection;
      },

      querySync: function(sql,paraArray){
        let connection = this.createConnection();
        let pro = querySync(connection,sql,paraArray);
        pro.catch((err)=>{
          console.error(' # on querySync: '+err);
        }).finally((some)=>{
          // if(conn){
          //
          // }
        });
        connection.end();
        return pro;
      }
    };


    global.db = MysqlDbHelper;

    module.exports = MysqlDbHelper;
  );

  # 创建 mysql 数据连接

        var connection = mysql.createConnection(option);
        connection.connect();
        console.log(`打开数据库成功`);
        return connection;


  #  操作数据库

        let promise = new Promise((resolve, reject) => {
          let sql2 = mysql.format(sql, paraArray);
          console.log(`  #sql: ${sql2}`);
          db.query(sql2,paraArray,(error, results, fields)=>{
            let str ='  #result:';
            str+=' error:'+error;
            str+=' results:'+JSON.stringify(results);
            str+=' fields:'+fields;
            console.log(str+'\n');
            if (error) {
              reject(error);
            };
            resolve(results,fields);
          })
        });

参考

https://github.com/mapbox/node-sqlite3/wiki/API#databasegetsql-param--callback