网站域名解析页面热门推广软件
一、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