node.js(koa2)+MySQL简易入门

3,312

安装Mysql

具体的安装MySQL省略。。。

  1. 配置密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'your password'; 
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your password';

使用mysql模块或者node.js的orm时需要在mysql命令行中配置一下密码,才可以正常启动node.js连接

配置MySQL相关

1.安装mysql引擎

npm install mysql --save

mysql模块是node.js操作MySQL的驱动(引擎),可以在node.js环境下对MySQL数据库进行建表,增、删、改、查等操作。

2.创建MySQL数据库

CREATE DATABASE login_test DEFAULT CHARSET utf8;

使用node.js连接MySQL

  1. 引入mysql模块
 const mysql = require('mysql');
  1. 创建connection实例

使用createConnection方法,该方法接受一个OBject参数,提供连接数据库的主机,用户名,密码,数据库名,创建一个connection实例连接对象。

const connection = mysql.createConnection({
 host: 'localhost',
 user: 'root',
 password: 'password',
 database: 'login_test'
});
  1. 调用连接方法,连接数据库

调用connection中的connect方法连接数据库,该方法接受一个含有err的函数,提供了错误处理办法。

connection.connect(function (err) {
  if (err) {
    console.error('error connecting: ' + err.stack);
    return;
  }

  console.log('connected as id ' + connection.threadId);
});

  1. 关闭数据库连接 提供了enddestroy两种方法,区别是destroy会马上停止数据库连接,而end方法会在处理完数据库请求后停止连接。
connection.end(function (err) {
  if (err) {
    return console.log('error:' + err.message);
  }
  console.log('Close the database connection.');
});

完整的数据库连接

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'login_test'
});

connection.connect(function (err) {
  if (err) {
    console.error('error connecting: ' + err.stack);
    return;
  }

  console.log('connected as id ' + connection.threadId);
});

connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

connection.end(function (err) {
  if (err) {
    return console.log('error:' + err.message);
  }
  console.log('Close the database connection.');
});

MySQL连接池

  1. 创建连接池

通过connectioncreatePool方法可以创建连接池。

const pool = mysql.createPool({
   connectionLimit: 10,
   host: 'localhost',
   user: 'root',
   password: 'password', 
   database: 'login_test'
});

这样将创建一个具有10个连接的连接池。需要注意的是,连接池的创建是惰性的,如果仅仅使用2个连接,将仅创立2个连接。

2.使用连接池

首先,连接池是这样的调用顺序pool.getConnection() -> connection.query() -> connection.release()

pool.getConnection(function (err, connection) {
 if (err) throw err; // not connected!
 // Use the connection
 connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
   // When done with the connection, release it.
   connection.release();
   // Handle error after the release.
   if (error) throw error;
   console.log('The solution is: ', results[0].solution);
   // Don't use the connection here, it has been returned to the pool.
 });
});

使用完连接后,可以通过connection.release方法释放连接,可以被其他请求使用。

使用connection.destroy方法,可以摧毁当前连接,连接池可以在需要时,创建一个新的连接。

使用end方法可以关闭连接池的所有连接

  pool.end(function (err) {
   // all connections in the pool have ended
 });

需要注意的是,执行pool.end会调用每一个连接的connction.end方法,已经在event loop执行栈中的任务会继续执行,未进入的将不会执行。

完整例子

const mysql = require('mysql');

const pool = mysql.createPool({
 connectionLimit: 10,
 host: 'localhost',
 user: 'root',
 password: 'password',
 database: 'login_test'
});

pool.getConnection(function (err, connection) {
 if (err) throw err; // not connected!
 // Use the connection
 connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
   // When done with the connection, release it.
   connection.release();
   // Handle error after the release.
   if (error) throw error;
   console.log('The solution is: ', results[0].solution);
   // Don't use the connection here, it has been returned to the pool.
 });
});

setTimeout(function () {
 pool.end(function (err) {
   // all connections in the pool have ended
 });
}, 1000)

在MySQL中创建表

query方法

mysql模块提供了一个query方法,可以通过实例化后的Connection,Pool,PoolNamespace调用。query方法有两种使用方法:

  1. .query(sqlString, callback)
  2. .query(sqlString, values, callback)

values为sql语句中占位符的替换变量,需要以Array的形式提供,如果为多个变量则需要以Array中嵌套Array的方式使用。(在MySQL写入中有具体例子)

callback回调函数有三个参数:

error:错误信息

results:查询结果

fields:结果字段信息

使用mysql创建表

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'login_test'
});

// connect to the MySQL server
connection.connect(function (err) {
  if (err) {
    return console.error('error: ' + err.message);
  }
});

let createTodos = `create table if not exists user_table(
                          id int primary key AUTO_INCREMENT,
                          name varchar(255) NOT NULL,
                          password varchar(255) NOT NULL,
                          email varchar(255) DEFAULT '',
                          create_time datetime NOT NULL,
                          update_time datetime DEFAULT NOW()
                      ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;`;
connection.query(createTodos, function (err, results, fields) {
  if (err) {
    console.log(err.message);
  }
});

connection.end(function (err) {
  if (err) {
    return console.log(err.message);
  }
});

这里使用了一个简单的user表,如果实际项目中,应该将ProfileAuthorization分离

MySQL命令行中输入 use login_test;show tables;查看建好的数据表

在MySQL中写入数据

单行写入

const mysql = require('mysql');

const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'login_test'
});

const sql = `INSERT INTO user_table (name,password,create_time) 
             VALUES ('test','123','2019-08-25 00:00:00');
`

pool.getConnection(function (err, connection) {
  if (err) throw err;
  connection.query(sql, function (error, results, fields) {
    connection.release();
    if (error) throw error;
    console.log(results)
  });
});

MySQL命令行查看结果select * from user_table;

使用占位符?动态写入

const mysql = require('mysql');

const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'login_test'
});

const values = ['test', '123', '2019-08-25 00:00:00']
const sql = `INSERT INTO user_table (name,password,create_time) 
             VALUES ( ? , ? , ? );
`

pool.getConnection(function (err, connection) {
  if (err) throw err;
  connection.query(sql, values, function (error, results, fields) {
    connection.release();
    if (error) throw error;
    console.log(results)
  });
});

这里通过query语句的第二种使用方法(三个参数)来实现动态生成SQL语句然后插入。

一次插入多行

const mysql = require('mysql');

const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'login_test'
});

const values = [[
  ['test1', '1', '2019-08-25 00:00:00'],
  ['test2', '2', '2019-08-25 00:00:00'],
  ['test3', '3', '2019-08-25 00:00:00']
]]
const sql = `INSERT INTO user_table (name,password,create_time) 
             VALUES ?
`;

pool.getConnection(function (err, connection) {
  if (err) throw err;
  connection.query(sql, values, function (error, results, fields) {
    connection.release();
    if (error) throw error;
    console.log(results)
  });
});

将SQL语句中(?,?,?)换成?values换成三层嵌套的数组形式。

MySQL查询

简单查询

const mysql = require('mysql');

const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'login_test'
});


const sql = `SELECT * from user_table`;

pool.getConnection(function (err, connection) {
  if (err) throw err;
  connection.query(sql, function (error, results, fields) {
    connection.release();
    if (error) throw error;
    console.log(results)
  });
});

传值查询

const mysql = require('mysql');

const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'login_test'
});

const values = `test`;
const sql = `SELECT * from user_table WHERE name=?`;

pool.getConnection(function (err, connection) {
  if (err) throw err;
  connection.query(sql, values, function (error, results, fields) {
    connection.release();
    if (error) throw error;
    console.log(results)
  });
});

注意:这个?占位符号的写法与escape方法执行逻辑是一致的,同样可以抵御简单SQL注入。

MySQL更新

const mysql = require('mysql');

const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'login_test'
});

const values = ['test_pass', 'test1'];
const sql = `UPDATE user_table SET password=?  WHERE name=?`;

pool.getConnection(function (err, connection) {
  if (err) throw err;
  connection.query(sql, values, function (error, results, fields) {
    connection.release();
    if (error) throw error;
    console.log(results)
  });
});

依然使用?占位符还有value的Array参数。

MySQL删除数据

删除单个查询结果

const mysql = require('mysql');

const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'login_test'
});

const values = ['test', '4'];
const sql = `DELETE from user_table WHERE name=? AND id=?`;

pool.getConnection(function (err, connection) {
  if (err) throw err;
  connection.query(sql, values, function (error, results, fields) {
    connection.release();
    if (error) throw error;
    console.log(results)
  });
});