1-1.mysql2 之 mysql2 初识（mysql2 初识案例、初识案例挖掘）
一、mysql2 概述
-
mysql2 是一个用于
Node.js的 MySQL 客户端库 -
mysql2 是 mysql 库的一个改进版本，提供了更好的性能和更多的功能
-
使用 mysql2 之前，需要先安装它
npm install mysql2
二、mysql2 初识案例
1、数据库准备
- 创建数据库 testdb
CREATE DATABASE testdb;
- 创建数据表 employees
CREATE TABLE IF NOT EXISTS employees (
id INT AUTO_INCREMENT PRIMARY KEY,
firstName VARCHAR(50) NOT NULL,
lastName VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
phone VARCHAR(20),
hireDate DATE,
jobTitle VARCHAR(50)
);
- 添加一些测试数据
INSERT INTO employees (firstName, lastName, email, phone, hireDate, jobTitle) VALUES
('John', 'Doe', 'john.doe@example.com', '123-456-7890', '2023-01-15', 'Software Engineer'),
('Jane', 'Smith', 'jane.smith@example.com', '098-765-4321', '2022-07-22', 'Product Manager'),
('Alice', 'Johnson', 'alice.johnson@example.com', '555-123-4567', '2021-11-30', 'Designer'),
('Bob', 'Brown', 'bob.brown@example.com', '555-987-6543', '2020-03-15', 'Data Analyst');
2、测试
const mysql = require("mysql2/promise");
const pool = mysql.createPool({
host: "localhost",
database: "testdb",
user: "root",
password: "1234",
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0,
});
const queryAllTest = async () => {
const connection = await pool.getConnection();
const result = await connection.execute("SELECT * FROM employees");
connection.release();
console.log(result.rows);
};
queryAllTest();
- 输出结果
[
{
id: 1,
firstName: 'John',
lastName: 'Doe',
email: 'john.doe@example.com',
phone: '123-456-7890',
hireDate: 2023-01-14T16:00:00.000Z,
jobTitle: 'Software Engineer'
},
{
id: 2,
firstName: 'Jane',
lastName: 'Smith',
email: 'jane.smith@example.com',
phone: '098-765-4321',
hireDate: 2022-07-21T16:00:00.000Z,
jobTitle: 'Product Manager'
},
{
id: 3,
firstName: 'Alice',
lastName: 'Johnson',
email: 'alice.johnson@example.com',
phone: '555-123-4567',
hireDate: 2021-11-29T16:00:00.000Z,
jobTitle: 'Designer'
},
{
id: 4,
firstName: 'Bob',
lastName: 'Brown',
email: 'bob.brown@example.com',
phone: '555-987-6543',
hireDate: 2020-03-14T16:00:00.000Z,
jobTitle: 'Data Analyst'
}
]
三、初识案例挖掘
1、案例解析
- 引入 mysql2
const mysql = require("mysql2/promise");
- 创建数据库连接池
const pool = mysql.createPool({
host: "localhost",
database: "testdb",
user: "root",
password: "1234",
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0,
});
| 参数 | 类型 | 说明 |
|---|---|---|
| host | string | 数据库服务器的地址 |
| database | string | 要连接的数据库名称 |
| user | string | 数据库用户名 |
| password | string | 数据库密码 |
| waitForConnections | boolean | 当没有可用连接时，是否等待连接释放（默认为 true） |
| connectionLimit | number | 连接池中允许的最大连接数 |
| queueLimit | number | 当连接池中没有可用连接且达到最大等待队列长度时，新请求的行为（0 表示无限制） |
- 从连接池中获取一个连接
const connection = await pool.getConnection();
- 执行一条 SQL 语句并获取结果
const result = await connection.execute("SELECT * FROM employees");
- 释放一个连接
connection.release();
2、要点复习
- 复习一下数组的解构赋值
const nums = [1,2,3,4,5];
const [num1, num2] = nums;
console.log(num1, num2);
- 输出结果
1 2
- 对 execute 方法返回的数组的解构赋值，就是获取该数组的第一个元素，完整的数组是这样的
const queryAllResultTest = async () => {
const connection = await pool.getConnection();
const result = await connection.execute("SELECT * FROM employees");
connection.release();
console.log(result);
};
queryAllResultTest();
- 输出结果
[
[
{
id: 1,
firstName: 'John',
lastName: 'Doe',
email: 'john.doe@example.com',
phone: '123-456-7890',
hireDate: 2023-01-14T16:00:00.000Z,
jobTitle: 'Software Engineer'
},
{
id: 2,
firstName: 'Jane',
lastName: 'Smith',
email: 'jane.smith@example.com',
phone: '098-765-4321',
hireDate: 2022-07-21T16:00:00.000Z,
jobTitle: 'Product Manager'
},
{
id: 3,
firstName: 'Alice',
lastName: 'Johnson',
email: 'alice.johnson@example.com',
phone: '555-123-4567',
hireDate: 2021-11-29T16:00:00.000Z,
jobTitle: 'Designer'
},
{
id: 4,
firstName: 'Bob',
lastName: 'Brown',
email: 'bob.brown@example.com',
phone: '555-987-6543',
hireDate: 2020-03-14T16:00:00.000Z,
jobTitle: 'Data Analyst'
}
],
[
`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`firstName` VARCHAR(50) NOT NULL,
`lastName` VARCHAR(50) NOT NULL,
`email` VARCHAR(100) NOT NULL UNIQUE_KEY,
`phone` VARCHAR(20),
`hireDate` DATE(10),
`jobTitle` VARCHAR(50)
]
]
3、案例优化
- 添加
try catch finally能保证在 execute 方法执行 SQL 语句发送异常时连接也能释放
const queryAllErrorTest = async () => {
const connection = await pool.getConnection();
try {
const [rows] = await connection.execute("SELECT * FROM error_employees");
console.log(rows);
} catch (error) {
console.error(error);
} finally {
connection.release();
}
};
queryAllErrorTest();
- 输出结果
Error: Table 'testdb.error_employees' doesn't exist