在Node.js中操作MySQL数据库

要在 Node.js 中操作 MySQL 数据库，就需要使用 mysql 模块，本节将对如何在 Node.js 中操作 MySQL 数据库进行讲解。

Node.js中的mysql模块

要使用 mysql 模块，需要先进行安装，命令如下：

npm install mysql

安装 mysql 模块后，如果要使用它，需要用 require() 方法引入，代码如下：

var mysql=require('mysql')

mysql 模块中提供了 createConnection(option) 方法，该方法用来创建数据库连接对象，其 option 参数用来设置要连接的数据库的相关信息，该参数中可以指定的属性及说明如表 15.3 所示。

Figure 1. 表15.3 option 参数中可以指定的属性及说明

使用 createConnection() 方法创建的数据库连接对象主要有以下 3 个方法。

connect() 方法：连接数据库。
end() 方法：关闭数据库。
query() 方法：执行 SQL 语句。

connect() 方法和 end() 方法没有参数，使用比较简单，而 query() 方法使用时，需要提供相应的参数，其语法格式如下：

connection.query(sql,add,callback);
connection.query(sql,callback);

sql：SQL 语句，执行添加、修改、删除或者查询等操作。
add：指定 SQL 语句中的占位符内容，如果 SQL 语句中没有占位符，则省略。
callback：回调函数，操作完成后返回的数据，其中可以对可能产生的错误进行处理。

【例15.1】连接 MySQL 数据库并查询数据。（实例位置：资源包\源码\15\01）

本实例使用 mysql 模块的 createConnection() 方法创建数据库连接，并使用 query() 方法查询 Library 数据库的 books 数据表中的数据。在实现之前，首先需要准备数据库及数据表。创建 Library 数据库、在数据库中创建 books 数据表并向数据表中添加数据的 SQL 语句如下：

CREATE DATABASE Library;                                 /*创建数据库*/
USE Library;                                             /*选择数据库*/
/*创建数据表*/
CREATE  TABLE  books(
     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
     bookname VARCHAR(50) NOT NULL,
     author VARCHAR(15) NOT NULL,
     press VARCHAR(30) NOT NULL
);
/*向数据表中添加数据*/
INSERT  INTO  books(bookname,author,press) VALUES
('《Java从入门到精通》','明日科技','清华大学出版社'),
('《Node.js从入门到精通》','王小科','吉林大学出版社'),
('《Python从入门到精通》','明日科技','清华大学出版社'),
('《C#从入门到精通》','明日科技','清华大学出版社'),
('《C#开发实例大全》','明日科技','清华大学出版社'),
('《C语言从入门到精通》','李磊','清华大学出版社');

上面的 SQL 语句在 MySQL 的 “命令提示符” 对话框中执行，效果如图 15.47 所示。

Figure 2. 图15.47 准备数据库及数据表

准备完要操作的数据之后，在 WebStorm 中创建一个 index.js 文件，该文件中使用 mysql 模块的 createConnection() 方法生成数据库连接对象，然后使用数据库连接对象的 query() 方法查询 books 数据表中的所有信息，并输出。代码如下：

//引入模块
var mysql = require('mysql');
//连接数据库
var connection = mysql.createConnection({
     host: 'localhost',
     port:"3306",
     user: 'root',
     password: 'root',
     database: 'Library'
});
//判断数据库是否连接成功
connection.connect(function(err){
     if(err){
          console.log('[query] - :'+err);
          return;
     }
     console.log('[connection connect]  MySQL数据库连接成功!');
});
//使用SQL查询语句
connection.query(use Library);
connection.query(select * from books', function(error, result, fields) {
     if(error) {
          console.log('查询语句有误！');
     } else {
          console.log(result);
     }
});
//关闭连接
connection.end(function (err) {
     if (err) {
          return;
     }
     console.log('[connection end] 关闭数据库连接!');
});

运行程序，效果如图 15.48 所示。

运行上面代码时，可能会出现如图 15.49 所示的错误提示，这是由于 MySQL 8.0 之前的版本中的加密规则是 mysql_native_password，而在 MySQL 8.0 之后，加密规则变成 caching_sha2_password。

要解决上面的错误，需要使用 SQL 语句修改 MySQL 数据库的加密规则，具体如下：

/*修改加密规则*/
ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘password’ PASSWORD EXPIRE NEVER;
/*修改密码*/
ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;
/*刷新权限，使修改生效*/
FLUSH PRIVILEGES;

具体的执行效果如图 15.50 所示。

Figure 3. 图15.48 连接数据库并查询数据

Figure 4. 图15.49 Node.js连接数据库时可能的错误

Figure 5. 图15.50 连接产生错误时修改MySQL数据库加密规则

Node.js中对MySQL实现增删改查操作

本节将在 Node.js 中使用 mysql 模块来操作 MySQL 数据库，以实现一个简单的小型图书管理系统，主要实现的功能有图书的查询、添加、修改和删除等操作，前端页面展示使用的是 ejs 模板。

【例15.2】小型图书管理系统。（实例位置：资源包\源码\15\02）

实现本实例需要完成的步骤如下。

显示图书列表

实现小型图书管理系统之前，首先需要准备该系统将用到的 Node.js 第三方模块，打开系统的 “命令提示符” 对话框，或者 WebStorm 的命令终端，使用下面命令安装所需的模块：

npm install express@4
npm install ejs
npm install mysql
npm install body-parser

在 WebStorm 中创建一个 index.js 文件，在该文件中使用 mysql 模块的 createConnection(option) 方法创建数据库连接对象；然后使用 express 模块创建服务器并启动，在创建的服务器中使用数据库连接对象的 query() 方法执行 SQL 查询语句，获取所有的图书信息，并通过 ejs.render() 方法发送到客户端。index.js 文件中的代码如下：

//引入模块
var fs = require('fs');
var ejs = require('ejs');
var mysql = require('mysql');
var express = require('express');
var bodyParser = require('body-parser');
//连接MySQL数据库
var client = mysql.createConnection({
     host: 'localhost',
     port:"3306",
     user: 'root',
     password: root,
     database: 'Library'
});
//判断数据库是否连接成功
client.connect(function(err){
     if(err){
          console.log('[query] - :'+err);
          return;
     }
     console.log('[connection connect]  MySQL数据库连接成功!');
});
//创建服务器
var app = express();
app.use(bodyParser.urlencoded({
     extended: false
}));
//启动服务器
app.listen(52273, function () {
     console.log('服务器运行在 http://127.0.0.1:52273');
});
//显示图书列表
app.get('/', function (request, response) {
     //读取模板文件
     fs.readFile('book-list.html', 'utf8', function (error, data) {
          //执行SQL语句
          client.query(select * from books', function (error, results) {
               //响应信息
               response.send(ejs.render(data, {
                     data: results
               }));
          });
     });
});

显示所有图书信息是在 book-list.html 页面中实现的，该页面使用 ejs 渲染标识，并将 index.js 文件中获取到的图书数据分别放到指定的 HTML 标签中进行显示。代码如下：

<!DOCTYPE html>
<html>
<head>
     <meta charset="UTF-8">
     <title>图书列表</title>
     <style>
          table{
               padding: 0;
               position: relative;
               margin: 0 auto;
          }
          table tbody tr th {
               background: #044599 no-repeat;
               text-align: center;
               border-left: 1px solid #02397F;
               border-right: 1px solid #02397F;
               border-bottom: 1px solid #02397F;
               border-top: 1px solid #02397F;
               letter-spacing: 2px;
               text-transform: uppercase;
               font-size: 14px;
               color: #fff;
               height: 37px;
          }
          table tbody tr td {
               text-align: center;
               border-left: 1px solid #ECECEC;
               border-right: 1px solid #ECECEC;
               border-bottom: 1px solid #ECECEC;
               font-size: 15px;
               color: #909090;
               height: 37px;
          }
     </style>
</head>
<body>
     <h1 style="text-align: center">图书列表</h1>
     <a href="/insert">添加数据</a>
     <br/>
     <table width="100%">
          <tr>
               <th>ID</th>
               <th>书名</th>
               <th>作者</th>
               <th>出版社</th>
               <th>删除</th>
               <th>编辑</th>
          </tr>
          <%data.forEach(function (item, index) { %>
          <tr>
               <td><%= item.id %></td>
               <td><%= item.bookname %></td>
               <td><%= item.author %></td>
               <td><%= item.press %></td>
               <td><a href="/delete/<%= item.id %>">删除</a></td>
               <td><a href="/edit/<%= item.id %>">编辑</a></td>
          </tr>
          <% }); %>
     </table>
</body>
</html>

运行程序，启动 Node.js 服务器，然后在浏览器中打开 http://127.0.0.1:52273/ ，效果如图15.51所示。

Figure 6. 图15.51 显示图书列表信息

添加图书信息

添加图书信息是在 book-insert.html 页面中实现的，在该页面中，通过 <form> 标签，将输入的图书信息通过表单的方式提交。代码如下：

<!DOCTYPE html>
<html>
<head>
     <meta charset="UTF-8">
     <title>添加图书</title>
</head>
<body>
     <h3>添加图书</h3>
     <hr />
     <form method="post">
          <fieldset>
               <legend>添加数据</legend>
               <table>
                     <tr>
                          <td><label>图书名称</label></td>
                          <td><input type="text" name="bookname" /></td>
                     </tr>
                     <tr>
                          <td><label>作者</label></td>
                          <td><input type="text" name="author" /></td>
                     </tr>
                     <tr>
                          <td><label>出版社</label></td>
                          <td><input type="text" name="press" /></td>
                     </tr>
               </table>
               <input type="submit" />
          </fieldset>
     </form>
</body>
</html>

在 index.js 文件中，首先读取 boot-insert.html 模板文件，然后获取 POST 请求中提交的要添加的图书信息，使用数据库连接对象的 query() 方法执行 SQL 添加语句。代码如下：

app.get('/insert', function (request, response) {
     //读取模板文件
     fs.readFile('book-insert.html', 'utf8', function (error, data) {
          //响应信息
          response.send(data);
     });
});
app.post('/insert', function (request, response) {
     //声明body
     var body = request.body;
     //执行SQL语句
     client.query(insert into books (bookname, author, press) VALUES (?, ?, ?)', [
          body.bookname, body.author, body.press
     ], function () {
          //响应信息
          response.redirect('/');
     });
});

运行程序，启动 Node.js 服务器，然后在浏览器中打开 http://127.0.0.1:52273/ ，在图书列表页面单击 “添加图书” 超链接，打开 book-insert.html 页面，在该页面中输入要添加的图书信息后，单击 “提交” 按钮，即可完成图书的添加操作，如图15.52所示。

Figure 7. 图15.52 添加图书信息

修改图书信息

修改图书信息是在 book-edit.html 页面中实现的，在该页面中，通过 <form> 标签，将要修改的图书信息通过表单的方式提交。代码如下：

<!DOCTYPE html>
<html>
<head>
     <meta charset="UTF-8">
     <title>修改图书</title>
</head>
<body>
     <h1>修改图书信息</h1>
     <hr />
     <form method="post">
          <fieldset>
               <legend>修改图书信息</legend>
               <table>
                     <tr>
                          <td><label>Id</label></td>
                          <td><input type="text" name="id" value="<%= data.id %>" disabled /></td>
                     </tr>
                     <tr>
                          <td><label>书名</label></td>
                          <td><input type="text" name="bookname" value="<%= data.bookname %>" /></td>
                     </tr>
                     <tr>
                          <td><label>作者</label></td>
                          <td>
                               <input type="text" name="author" value="<%= data.author %>" />
                          </td>
                     </tr>
                     <tr>
                          <td><label>出版社</label></td>
                          <td><input type="text" name="press" value="<%= data.press %>" /></td>
                     </tr>
               </table>
               <input type="submit" />
          </fieldset>
     </form>
</body>
</html>

在 index.js 文件中，首先读取 boot-edit.html 模板文件，并根据id将要修改的图书的信息显示出来，然后获取 POST 请求中提交的要修改的图书信息，使用数据库连接对象的 query() 方法执行 SQL 修改语句，实现根据 id 修改图书信息的功能。代码如下：

app.get('/edit/:id', function (request, response) {
     //读取模板文件
     fs.readFile('book-edit.html', 'utf8', function (error, data) {
          //执行SQL语句
          client.query(select * from books where id = ?', [
               request.params.id
          ], function (error, result) {
               //响应信息
               response.send(ejs.render(data, {
                     data: result[0]
               }));
          });
     });
});
app.post('/edit/:id', function (request, response) {
     //声明body
     var body = request.body;
     //执行SQL语句
     client.query('update books set bookname=?, author=?, press=? where id=?',
        [body.bookname, body.author, body.press, request.params.id], function () {
          //响应信息
          response.redirect('/');
     });
});

运行程序，启动 Node.js 服务器，然后在浏览器中打开 http://127.0.0.1:52273/ ，在图书列表页面单击指定图书后的 “编辑” 超链接，打开 book-edit.html 页面，在该页面中即可对指定图书的相关信息进行修改，修改完成后，单击 “提交” 按钮即可，如图15.53所示。

Figure 8. 图15.53 修改图书信息

删除图书信息

在 index.js 文件中，定义根据 id 删除图书信息的方法，代码如下：

app.get('/delete/:id', function (request, response) {
     //执行SQL语句
     client.query(delete from books where id=?', [request.params.id], function () {
          //响应信息
          response.redirect('/');
     });
});

运行程序，启动 Node.js 服务器，然后在浏览器中打开 http://127.0.0.1:52273/ ，在图书列表页面单击指定图书后的 “删除” 超链接，即可删除指定的图书信息，如图 15.54 所示。

Figure 9. 图15.54 删除图书信息