node入门（三）-- mysql

mysql

npm install mysql -S
// 涉及多表插入需要使用
beginTransaction()　// 打开事物
rollback() //事物回滚

首先说mysql的第一种连接方式：Connection

附带数据库表--不加密
mysql version :
5.6.4-m7 MySQL Community Server (GPL)
－－－－－－－－－－－－－－－－－－－－－－
DROP DATABASE IF EXISTS hibernate_1;

CREATE DATABASE hibernate_1 CHARSET=utf8 DEFAULT CHARACTER SET UTF8;

USE hibernate_1;

CREATE TABLE login(
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  loginName VARCHAR(20) BINARY UNIQUE NOT NULL ,
  password VARCHAR(20) BINARY  NOT NULL
)ENGINE=InnoDB;

INSERT INTO login(loginName,password) VALUES 
('admin','admin'),
('user','123123');

"use strict";

var mysql = require('mysql');

var db = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'hibernate_1',
    port:'3306'
});
//插入
db.query('insert into login(loginName,password) values(?,?)', ['zhang12san', '123112323'], function (err, res) {
    if(err){
        console.error(err);
        return;
    }
    console.log(res);
    /**
    * OkPacket {
     fieldCount: 0,
     affectedRows: 1,
     insertId: 3, //插入后的id 多表插入很有用
     serverStatus: 2,
     warningCount: 0,
     message: '',
     protocol41: true,
     changedRows: 0 }
    */
    db.end();
});
//查询
// console.log(db);
db.query('select password from login where loginName=?', ['zhang12san'], function (err, data) {
    if(err){
        console.error(err);
        return;
    }
    console.log(data[0].password); //123112323
    db.end();//关闭数据库连接
});

第二种连接方式：pool连接池,(推荐使用)

"use strict";

var mysql = require('mysql');
var pool =mysql.createPool({ //得到连接池
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'hibernate_1',
    port : '3306'
});
//查询
pool.getConnection(function (err,conn) { //打开连接
    if(err){
        console.error(err);
        return;
    }
    conn.query('select * from login', [], function (err,result) {
        console.log(result);
        /**
        * [ RowDataPacket { id: 1, loginName: 'admin', password: 'admin' },
         RowDataPacket { id: 2, loginName: 'user', password: '123123' },
         RowDataPacket { id: 3, loginName: 'zhang12san', password: '123112323' } ]
        * */
        conn.release();
    });
});