NodeJS 后端开发 06 连接MySQL

const mysql =  require('mysql');

//创建一个数据库连接
const db =  mysql.createConnection({
    host: "localhost",
    port: 3306,
    user: "root",
    password: "12345678"
});

const logDbStat = function(){
    console.log("db state %s and threadID %s", db.state, db.threadId);
    // console.log("db detail:", db);
}

//在此时，仍旧未有实际连接到数据库
logDbStat();

console.log('start to connect mysql');
db.connect(function(err){
    if(err){
        console.log('fail to connect db',err.stack);
        throw err;
    }
    // 这里正真连接上数据库了。
    logDbStat();
});

const close = function(){
    db.destroy();
    console.log('db disconnected');
    logDbStat();
};

//定义一个计数器和runSql函数
//作用是跟踪输入的SQL与输出结果，也对数据库操作做一个简单包装。
var counter = 0;
const runSql = function(sql, callback){
    counter++;
    var cv = counter;
    console.log('run sql[%s] :[%s]',cv, sql);
    db.query(sql, function(error, result, fields){
        try{
            if(error) throw error;
            console.log('[%s] result:', cv, result); // use cv instead of counter, as counter is part of 'global', it will be polluted
        }finally{
            if(callback) callback(result, fields);
        }
    });
}

// 执行SQL语句
const myDb = 'demo20210330';

runSql("CREATE DATABASE IF NOT EXISTS " + myDb 
	+ " DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ; ");


runSql("USE " + myDb);

//创建公司数据表
runSql("CREATE TABLE IF NOT EXISTS COMPANY ( "
	+ " ID int(11) NOT NULL AUTO_INCREMENT,    "
	+ " NAME varchar(64) NOT NULL UNIQUE, "
	+ " AGE int(8) NOT NULL, "
	+ " ADDRESS text DEFAULT NULL, "
	+ " STOCK_PRICE decimal(10,3) DEFAULT NULL, "
    + "  PRIMARY KEY (ID) "
    + " ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8; ");


runSql("TRUNCATE TABLE COMPANY ;");

//插入三条公司股价信息
runSql(" INSERT INTO COMPANY "
	+ "(NAME, AGE, ADDRESS, STOCK_PRICE ) VALUES "
    + "('Alibaba', 21, 'Hangzhou', 230.35), "
    + "('Tencent', 31, 'Shenzhen', 610.00), "
    + "('XiaoMi', 10, 'Beijing', 25.05) ; ");

// 查询所有公司股票信息和地址等
const querySql = "SELECT * FROM COMPANY ;";

runSql(querySql, function(result,fields){
    console.log('query result:', result);
});


/**
db.query(querySql, function(err, rows){
    if(err){
        console.log('error ' + err);
        throw err;
    }
    console.log('query output=');
    console.log(rows);
});
*/

// close(); // don't close here as the query function may not completed.
runSql("SELECT 1 ; ", function(result, fields){
    close();
});

// runSql(" DELETE FROM COMPANY ;");