平台不仅仅要控制设备，还需要管理用户的信息、设备的信息等（废话...不然怎么知道用户有哪些设备可以控制），因此需要数据库来存储这些信息。由于之前使用的是MySql，因此此次还是选用MySql作为我们的数据库。先简单介绍下如何在node环境中操作MySql数据库。（重新学习于coderwhy老师）

myqsl2的安装与使用

那么如何可以在Node的代码中执行SQL语句来，这里我们可以借助于两个库：

mysql：最早的Node连接MySQL的数据库驱动；
mysql2：在mysql的基础之上，进行了很多的优化、改进；

mysql2相对于mysql来说，mysql2兼容mysql的API，并且提供了一些附加功能，例如Prepared Statement（预编译语句）；支持Promise(可以使用async和await语法)

首先npm install mysql2

使用

1、导入并创建连接

const mysql = require('mysql2')

// 1.创建一个连接(连接上数据库)
const connection = mysql.createConnection({
  host: 'localhost',
  port: 3306,
  database: '自己创建的数据库名',
  user: 'root', //替换为搭建mysql的时候自己定义的用户名与密码，默认为root
  password: 'password.'
})


// 2.执行操作语句, 操作数据库
const statement = 'SELECT * FROM `students`;'
// 这里的query 是指结构化查询语言。例如增删改查
connection.query(statement, (err, values, fields) => {
  if (err) {
    console.log('查询失败:', err)
    return
  }

  // 查看结果
  console.log(values)
  console.log(fields)
})

Prepared Statement

为了防止依赖注入，我们可以使用预编译语句。在普通查询的基础上就将方法修改为execute,第二个参数需要传入数组，内容和顺序就是sql 语句需要填充的(?)

// 2.执行一个SQL语句: 预处理语句
const statement = 'SELECT * FROM `products` WHERE price > ? AND score > ?;'
connection.execute(statement, [1000, 8], (err, values) => {
  console.log(values)
})

connection.destroy() // 销毁连接

Connection Pools

前面我们是创建了一个连接（connection），但是如果我们有多个请求的话，该连接很有可能正在被占用，那么我们是否需要每次一个请求都去创建一个新的连接呢？

事实上，mysql2给我们提供了连接池（connection pools）；
连接池可以在需要的时候自动创建连接，并且创建的连接不会被销毁，会放到连接池中，后续可以继续使用；
我们可以在创建连接池的时候通过connectionLimit设置最大创建个数

const mysql = require('mysql2')

// 1.创建一个连接
const connectionPool = mysql.createPool({
  host: 'localhost',
  port: 3306,
  database: '自己创建的数据库名',
  user: 'root', //替换为搭建mysql的时候自己定义的用户名与密码，默认为root
  password: 'password.'
  connectionLimit: 5
})

// 2.执行一个SQL语句: 预处理语句
const statement = 'SELECT * FROM `products` WHERE price > ? AND score > ?;'
connectionPool.execute(statement, [2000, 8], (err, values) => {
  console.log(values)
})

Promise方式

我们可以使用 .promise() 方法（在execute()前调用下）然后再执行我们的sql语句，这样sql查询就是异步的了，我们在其他地方的时候就可以使用async await 来获取我们查询数据库的信息。

const mysql = require('mysql2')
// 1.创建一个连接
const connectionPool = mysql.createPool({
  host: 'localhost',
  port: 3306,
  database: '自己创建的数据库名',
  user: 'root', //替换为搭建mysql的时候自己定义的用户名与密码，默认为root
  password: 'password.',
  connectionLimit: 5
})

// 2.执行一个SQL语句: 预处理语句
const statement = 'SELECT * FROM `products` WHERE price > ? AND score > ?;'
// 返回的结果是一个数组 [values, fields]
connectionPool.promise().execute(statement, [1000, 9]).then((res) => {
  const [values, fields] = res //返回的是一个数组。我们查询到的数据在第0位上
  console.log('-------------------values↓------------------')
  console.log(values)
  console.log('-------------------fields↓------------------')
  console.log(fields)
}).catch(err => {
  console.log('-------------------err↓------------------')
  console.log(err)
})

项目中对数据库连接与CRUD操作的简单封装

在项目中，我们可以将数据库操作的代码根据其功能（业务）需求封装为一个个函数（像coderwhy老师那样封装成一个类也可）。对于数据库的连接，单独放在一个databaseConnect.js文件中例如：

// databaseConnect
const mysql = require("mysql2");

// 1.创建连接池
const connectionPool = mysql.createPool({
  host: "localhost",
  port: 3306,
  database: "iot platform",
  user: "root",
  password: "123456",
  connectionLimit: 5,
});

// 2.获取连接是否成功
connectionPool.getConnection((err, connection) => {
  // 1.判断是否有错误信息
  if (err) {
    console.log("获取连接失败~", err);
    return;
  }

  // 2.获取connection, 尝试和数据库建立一下连接
  connection.connect((err) => {
    if (err) {
      console.log("和数据库交互失败", err);
    } else {
      console.log("数据库连接成功, 可以操作数据库~");
    }
  });
});

// 3.获取连接池中连接对象(先调用promise方法再导出，这样后面不用每次都加promise了)
const connection = connectionPool.promise();
module.exports = connection;

对用户表相关的操作，我们可以单独创建一个userService.js文件来存放所有和用户有关的操作数据库的函数。

//userService
// 导入我们的数据库连接
const connection = require("../utils/databaseConnect");

// 添加用户的函数（具体传入的值根据数据表和需要来调整）
async function addUser(username, password, phone, email) {
  const statement =
    "INSERT INTO user (username, password, phone, email) VALUES (?, ?, ?, ?)";
  try { //由于查询可能会出错从而导致程序崩溃，因此我们需要用try-catch包裹
    // 执行预处理语句添加用户
    const [result] = await connection.execute(statement, [
      username,
      password,
      phone,
      email,
    ]);

    // 返回插入的用户ID
    return result.insertId;
  } catch (error) {
    // 处理错误
    console.error("Error adding user:", error);
    throw error;
  }
}
// 根据用户名查询用户的函数
async function getUserByUsername(username) {
  const statement = "SELECT * FROM user WHERE username = ?";
  try {
    // 执行预处理语句查询用户
    const [result] = await connection.execute(statement, [username]);

    // 返回查询结果（可能为空）
    return result;
  } catch (error) {
    // 处理错误
    console.error("Error fetching user:", error);
    throw error;
  }
}

// 修改用户信息的函数
async function updateUserInfo(username, password, phone, email) {
  const statement =
    "UPDATE user SET password = ?, phone = ?, email = ? WHERE username = ?";
  try {
    // 执行预处理语句修改用户信息
    const [result] = await connection.execute(statement, [
      password,
      phone,
      email,
      username,
    ]);

    // 返回修改的用户ID
    return result.insertId;
  } catch (error) {
    // 处理错误
    console.error("Error updating user:", error);
    throw error;
  }
}

// 删除用户的函数
async function deleteUser(username) {
  const statement = "DELETE FROM user WHERE username = ?";
  try {
    // 执行预处理语句删除用户
    const [result] = await pool.execute(statement, [username]);

    // 返回删除的用户ID
    return result.insertId;
  } catch (error) {
    // 处理错误
    console.error("Error deleting user:", error);
    throw error;
  }
}

module.exports = {
  addUser,
  getUserByUsername,
  updateUserInfo,
  deleteUser,
};
//这样后续我们在需要操作数据库的时候，就可以直接
const res = await getUserByUsername(xxx,xxx,xxx,xxx)

同理，其他表的操作也可以这样封装一下。

搭建一个简单的MQTT物联网平台笔记(四): 数据库连接与CRUD

myqsl2的安装与使用

使用

Prepared Statement

Connection Pools

Promise方式

项目中对数据库连接与CRUD操作的简单封装