1.MySQL模块

nodeJS中提供了mysql模块，方便我们操作Mysql数据库。 在使用mysql模块时，会比较常用到用到mysql里面的几种操作： 1. 查询数据 select * from 表名 where 查询条件； 2. 删除数据 delete from 表名 where 搜索条件； 3. 修改数据 update 表名 set 修改内容·where 数据编号； 4. 添加数据 insert into 表名 values（数据） ；

2.实例

1)安装mysql模块

npm install mysql -S
#或者
npm install mysql --save

2)编写test.js

//引入mysql模块
const mysql = require('mysql');
let TEST_DATABASE = 'test';
let TEST_TABLE = 'users';
//创建连接
let client = mysql.createConnection({
    user: 'root',
    password: 'root',
});
client.connect();
client.query("use " + TEST_DATABASE);
client.query(
    'SELECT * FROM ' + TEST_TABLE,
    function selectCb(err, results, fields) {
        if (err) {
            throw err;
        }
        if (results) {
            for (let i = 0; i < results.length; i++) {
                console.log("%d\t%s\t%s", results[i].uid, results[i].username, results[i].password);
            }
        }
        client.end();
    }
);

注意：对于MySQL8+以上的版本，由于更改了密码策略，必须通过以下SQL脚本更改为传统的密码策略后，nodejs才能正常连接。

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '自己的密码';

3.自定义mysql工具类实现

1)在common目录下，新建dbutils.js

var CRUD = {
    //新增
    _insert: function (client, insertSQLString, value) {
        client.query(insertSQLString, value, function (error, results) {
            if (error) {
                console.log("ClientReady Error:" + error.message);
                client.end();
                return;
            } else {
                console.log("Inserted:" + results.affectedRows + " row.");
                console.log("Insert success...");
            }
        });
    },
    //查询
    _select: function (client, selectSQLString) {

        client.query(selectSQLString, function (error, results, fields) {
            if (error) {
                console.log("GetData Error:" + error.message);
                client.end();
                return;
            }
            if(results){
                for (let i = 0; i < results.length; i++) {
                    console.log(results[i]);
                }
            }
        });

    },
    //更新
    _update: function (client, updateSQLString) {
        client.query(updateSQLString, function (error, results) {
            if (error) {
                console.log("ClientReady Error:" + error.message);
                client.end();
                return;
            }
            console.log("Update success...");
        });
    },
    //删除
    _delete: function (client, deleteSQLString) {
        client.query(deleteSQLString, function (error, results) {
            if (error) {
                console.log("ClientReady Error:" + error.message);
                client.end();
                return;
            }
            console.log("Delete success...");
        });
    }
};

exports._insert = CRUD._insert;
exports._select = CRUD._select;
exports._update = CRUD._update;
exports._delete = CRUD._delete;

2)编写测试类。 在src下，编写test.js

let mysql = require("mysql"),
    CRUD = require("./common/dbutils");

let client = mysql.createConnection({
    host: "localhost", //主机名
    user: "root", //数据库用户名
    password: "root", //数据库密码
    database: "test" //数据库
});

CRUD._insert(client, "insert into users set username=?,password=?,gender=?,birthday=?", ['张无忌2', '666666','男','1999-10-10']);
CRUD._update(client, "update users set password='777777' where uid=2");
CRUD._delete(client, "delete from users where uid=58");
CRUD._select(client, "select * from users");

client.end();