Node.js与MySQL数据库连接完全指南从环境配置到连接建立再到数据增删改查操作全方位解析助你成为全栈开发高手

威震华夏关云长

引言

Node.js作为当今流行的后端JavaScript运行环境，与MySQL这一广泛应用的关系型数据库相结合，为全栈开发提供了强大而灵活的解决方案。本指南将带你从零开始，逐步掌握Node.js与MySQL的连接技术，从基础的环境配置到复杂的数据库操作，帮助你构建高效、安全的数据驱动应用。

一、环境配置

1. Node.js安装与配置

首先，确保你的系统已安装Node.js。如果尚未安装，请访问Node.js官网下载并安装最新的LTS版本。

安装完成后，在终端中运行以下命令验证安装：

2. node -v
3. npm -v

复制代码

你将看到类似以下的输出，显示安装的Node.js和npm版本：

2. v18.17.0
3. 9.6.7

复制代码

2. MySQL安装与配置

1. 访问MySQL官网下载MySQL Installer
2. 运行安装程序，选择”Full”安装类型以包含所有组件
3. 在配置过程中，设置root用户密码并记住它
4. 完成安装后，确保MySQL服务已启动

使用Homebrew安装MySQL：

2. brew update
3. brew install mysql

复制代码

安装完成后，启动MySQL服务：

2. brew services start mysql

复制代码

对于Ubuntu/Debian系统：

2. sudo apt update
3. sudo apt install mysql-server
4. sudo mysql_secure_installation

复制代码

对于CentOS/RHEL系统：

2. sudo yum update
3. sudo yum install mysql-server
4. sudo systemctl start mysqld
5. sudo systemctl enable mysqld

复制代码

在终端中运行以下命令登录MySQL：

2. mysql -u root -p

复制代码

输入你设置的root密码，成功登录后将看到MySQL命令行提示符：

2. mysql>

复制代码

二、连接建立

1. 创建Node.js项目

首先，创建一个新的项目目录并初始化npm项目：

2. mkdir node-mysql-guide
3. cd node-mysql-guide
4. npm init -y

复制代码

2. 安装MySQL驱动

Node.js有多种MySQL驱动可供选择，最常用的是mysql和mysql2。mysql2是mysql的增强版，提供了更好的性能和Promise支持。

2. npm install mysql2

复制代码

3. 基本连接示例

创建一个名为app.js的文件，添加以下代码：

2. const mysql = require('mysql2');
4. // 创建连接配置
5. const connection = mysql.createConnection({
6.   host: 'localhost',
7.   user: 'root',
8.   password: 'your_password', // 替换为你的MySQL密码
9.   database: 'my_database'    // 可选，指定要连接的数据库
10. });
12. // 连接到MySQL
13. connection.connect(error => {
14.   if (error) {
15.     console.error('Error connecting to the database: ' + error.stack);
16.     return;
17.   }
18.   console.log('Connected to MySQL with id ' + connection.threadId);
19.   
20.   // 在这里执行数据库操作
21.   
22.   // 关闭连接
23.   connection.end();
24. });

复制代码

运行这个文件：

2. node app.js

复制代码

如果一切正常，你将看到连接成功的消息。

4. 使用连接池

在生产环境中，推荐使用连接池来管理数据库连接，以提高性能和资源利用率。

2. const mysql = require('mysql2');
4. // 创建连接池
5. const pool = mysql.createPool({
6.   host: 'localhost',
7.   user: 'root',
8.   password: 'your_password',
9.   database: 'my_database',
10.   waitForConnections: true,
11.   connectionLimit: 10,  // 连接池大小
12.   queueLimit: 0
13. });
15. // 将连接池转换为Promise支持
16. const promisePool = pool.promise();
18. // 使用连接池查询数据库
19. async function queryDatabase() {
20.   try {
21.     const [rows, fields] = await promisePool.query('SELECT * FROM users');
22.     console.log(rows);
23.   } catch (error) {
24.     console.error('Error querying database:', error);
25.   }
26. }
28. queryDatabase();

复制代码

5. 使用环境变量管理敏感信息

在实际项目中，不应将数据库凭据硬编码在代码中。使用dotenv包从环境变量加载配置：

2. npm install dotenv

复制代码

在项目根目录创建.env文件：

2. DB_HOST=localhost
3. DB_USER=root
4. DB_PASSWORD=your_password
5. DB_DATABASE=my_database

复制代码

修改连接代码：

2. require('dotenv').config();
3. const mysql = require('mysql2');
5. const pool = mysql.createPool({
6.   host: process.env.DB_HOST,
7.   user: process.env.DB_USER,
8.   password: process.env.DB_PASSWORD,
9.   database: process.env.DB_DATABASE,
10.   waitForConnections: true,
11.   connectionLimit: 10,
12.   queueLimit: 0
13. });
15. const promisePool = pool.promise();
17. module.exports = promisePool;

复制代码

三、数据库操作

1. 创建数据库和表

首先，让我们创建一个示例数据库和表：

2. const promisePool = require('./db'); // 假设上面的连接池代码保存在db.js中
4. async function setupDatabase() {
5.   try {
6.     // 创建数据库（如果不存在）
7.     await promisePool.query('CREATE DATABASE IF NOT EXISTS my_app_db');
8.     console.log('Database created or already exists');
9.    
10.     // 使用数据库
11.     await promisePool.query('USE my_app_db');
12.    
13.     // 创建用户表
14.     const createUsersTable = `
15.       CREATE TABLE IF NOT EXISTS users (
16.         id INT AUTO_INCREMENT PRIMARY KEY,
17.         name VARCHAR(255) NOT NULL,
18.         email VARCHAR(255) NOT NULL UNIQUE,
19.         created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
20.       )
21.     `;
22.     await promisePool.query(createUsersTable);
23.     console.log('Users table created or already exists');
24.    
25.     // 创建产品表
26.     const createProductsTable = `
27.       CREATE TABLE IF NOT EXISTS products (
28.         id INT AUTO_INCREMENT PRIMARY KEY,
29.         name VARCHAR(255) NOT NULL,
30.         price DECIMAL(10, 2) NOT NULL,
31.         stock_quantity INT NOT NULL DEFAULT 0,
32.         created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
33.       )
34.     `;
35.     await promisePool.query(createProductsTable);
36.     console.log('Products table created or already exists');
37.    
38.     // 创建订单表
39.     const createOrdersTable = `
40.       CREATE TABLE IF NOT EXISTS orders (
41.         id INT AUTO_INCREMENT PRIMARY KEY,
42.         user_id INT NOT NULL,
43.         total_amount DECIMAL(10, 2) NOT NULL,
44.         order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
45.         FOREIGN KEY (user_id) REFERENCES users(id)
46.       )
47.     `;
48.     await promisePool.query(createOrdersTable);
49.     console.log('Orders table created or already exists');
50.    
51.   } catch (error) {
52.     console.error('Error setting up database:', error);
53.   }
54. }
56. setupDatabase();

复制代码

2. 插入数据（Create）

2. async function insertUser(name, email) {
3.   try {
4.     const sql = 'INSERT INTO users (name, email) VALUES (?, ?)';
5.     const [result] = await promisePool.query(sql, [name, email]);
6.     console.log('User inserted with ID:', result.insertId);
7.     return result.insertId;
8.   } catch (error) {
9.     console.error('Error inserting user:', error);
10.     throw error;
11.   }
12. }
14. // 使用示例
15. insertUser('John Doe', 'john@example.com')
16.   .then(id => console.log('New user ID:', id))
17.   .catch(error => console.error('Failed to insert user:', error));

复制代码

2. async function insertMultipleUsers(users) {
3.   try {
4.     const sql = 'INSERT INTO users (name, email) VALUES ?';
5.     const values = users.map(user => [user.name, user.email]);
6.     const [result] = await promisePool.query(sql, [values]);
7.     console.log(`${result.affectedRows} users inserted`);
8.     return result.affectedRows;
9.   } catch (error) {
10.     console.error('Error inserting multiple users:', error);
11.     throw error;
12.   }
13. }
15. // 使用示例
16. const newUsers = [
17.   { name: 'Alice Johnson', email: 'alice@example.com' },
18.   { name: 'Bob Smith', email: 'bob@example.com' },
19.   { name: 'Charlie Brown', email: 'charlie@example.com' }
20. ];
22. insertMultipleUsers(newUsers)
23.   .then(count => console.log(`${count} users inserted`))
24.   .catch(error => console.error('Failed to insert users:', error));

复制代码

3. 查询数据（Read）

2. async function getAllUsers() {
3.   try {
4.     const [rows] = await promisePool.query('SELECT * FROM users');
5.     return rows;
6.   } catch (error) {
7.     console.error('Error fetching users:', error);
8.     throw error;
9.   }
10. }
12. // 使用示例
13. getAllUsers()
14.   .then(users => console.log('All users:', users))
15.   .catch(error => console.error('Failed to fetch users:', error));

复制代码

2. async function getUserById(id) {
3.   try {
4.     const [rows] = await promisePool.query('SELECT * FROM users WHERE id = ?', [id]);
5.     if (rows.length > 0) {
6.       return rows[0];
7.     }
8.     return null; // 用户不存在
9.   } catch (error) {
10.     console.error('Error fetching user by ID:', error);
11.     throw error;
12.   }
13. }
15. // 使用示例
16. getUserById(1)
17.   .then(user => {
18.     if (user) {
19.       console.log('Found user:', user);
20.     } else {
21.       console.log('User not found');
22.     }
23.   })
24.   .catch(error => console.error('Failed to fetch user:', error));

复制代码

2. async function searchUsersByName(searchTerm) {
3.   try {
4.     const [rows] = await promisePool.query(
5.       'SELECT * FROM users WHERE name LIKE ?',
6.       [`%${searchTerm}%`]
7.     );
8.     return rows;
9.   } catch (error) {
10.     console.error('Error searching users by name:', error);
11.     throw error;
12.   }
13. }
15. // 使用示例
16. searchUsersByName('John')
17.   .then(users => console.log('Users matching "John":', users))
18.   .catch(error => console.error('Failed to search users:', error));

复制代码

2. async function getUsersPaginated(page = 1, pageSize = 10) {
3.   try {
4.     const offset = (page - 1) * pageSize;
5.     const [rows] = await promisePool.query(
6.       'SELECT * FROM users LIMIT ? OFFSET ?',
7.       [pageSize, offset]
8.     );
9.    
10.     // 获取总记录数
11.     const [countResult] = await promisePool.query('SELECT COUNT(*) as total FROM users');
12.     const total = countResult[0].total;
13.    
14.     return {
15.       data: rows,
16.       pagination: {
17.         page,
18.         pageSize,
19.         total,
20.         totalPages: Math.ceil(total / pageSize)
21.       }
22.     };
23.   } catch (error) {
24.     console.error('Error fetching paginated users:', error);
25.     throw error;
26.   }
27. }
29. // 使用示例
30. getUsersPaginated(1, 5)
31.   .then(result => {
32.     console.log('Page data:', result.data);
33.     console.log('Pagination info:', result.pagination);
34.   })
35.   .catch(error => console.error('Failed to fetch paginated users:', error));

复制代码

4. 更新数据（Update）

2. async function updateUserEmail(id, newEmail) {
3.   try {
4.     const [result] = await promisePool.query(
5.       'UPDATE users SET email = ? WHERE id = ?',
6.       [newEmail, id]
7.     );
8.    
9.     if (result.affectedRows === 0) {
10.       return false; // 用户不存在
11.     }
12.    
13.     console.log('User email updated');
14.     return true;
15.   } catch (error) {
16.     console.error('Error updating user email:', error);
17.     throw error;
18.   }
19. }
21. // 使用示例
22. updateUserEmail(1, 'new.email@example.com')
23.   .then(success => {
24.     if (success) {
25.       console.log('Email updated successfully');
26.     } else {
27.       console.log('User not found');
28.     }
29.   })
30.   .catch(error => console.error('Failed to update email:', error));

复制代码

2. async function updateMultipleUsers(updates) {
3.   try {
4.     // 使用CASE语句进行批量更新
5.     let sql = 'UPDATE users SET email = CASE id ';
6.     const ids = [];
7.     const emails = [];
8.    
9.     updates.forEach(update => {
10.       sql += 'WHEN ? THEN ? ';
11.       ids.push(update.id);
12.       emails.push(update.email);
13.     });
14.    
15.     sql += 'END WHERE id IN (';
16.     sql += ids.map(() => '?').join(',');
17.     sql += ')';
18.    
19.     const params = [...ids, ...emails, ...ids];
20.     const [result] = await promisePool.query(sql, params);
21.    
22.     console.log(`${result.affectedRows} users updated`);
23.     return result.affectedRows;
24.   } catch (error) {
25.     console.error('Error updating multiple users:', error);
26.     throw error;
27.   }
28. }
30. // 使用示例
31. const updates = [
32.   { id: 1, email: 'updated1@example.com' },
33.   { id: 2, email: 'updated2@example.com' },
34.   { id: 3, email: 'updated3@example.com' }
35. ];
37. updateMultipleUsers(updates)
38.   .then(count => console.log(`${count} users updated`))
39.   .catch(error => console.error('Failed to update users:', error));

复制代码

5. 删除数据（Delete）

2. async function deleteUser(id) {
3.   try {
4.     const [result] = await promisePool.query('DELETE FROM users WHERE id = ?', [id]);
5.    
6.     if (result.affectedRows === 0) {
7.       return false; // 用户不存在
8.     }
9.    
10.     console.log('User deleted');
11.     return true;
12.   } catch (error) {
13.     console.error('Error deleting user:', error);
14.     throw error;
15.   }
16. }
18. // 使用示例
19. deleteUser(1)
20.   .then(success => {
21.     if (success) {
22.       console.log('User deleted successfully');
23.     } else {
24.       console.log('User not found');
25.     }
26.   })
27.   .catch(error => console.error('Failed to delete user:', error));

复制代码

2. async function deleteMultipleUsers(ids) {
3.   try {
4.     const [result] = await promisePool.query(
5.       'DELETE FROM users WHERE id IN (?)',
6.       [ids]
7.     );
8.    
9.     console.log(`${result.affectedRows} users deleted`);
10.     return result.affectedRows;
11.   } catch (error) {
12.     console.error('Error deleting multiple users:', error);
13.     throw error;
14.   }
15. }
17. // 使用示例
18. deleteMultipleUsers([2, 3, 4])
19.   .then(count => console.log(`${count} users deleted`))
20.   .catch(error => console.error('Failed to delete users:', error));

复制代码

四、高级操作

1. 事务处理

事务是一组SQL语句的集合，这些语句要么全部执行成功，要么全部失败回滚。在处理需要多个步骤的操作时，事务非常重要。

2. async function placeOrder(userId, productIds) {
3.   const connection = await promisePool.getConnection();
4.   
5.   try {
6.     // 开始事务
7.     await connection.beginTransaction();
8.    
9.     // 获取用户信息
10.     const [users] = await connection.query('SELECT * FROM users WHERE id = ?', [userId]);
11.     if (users.length === 0) {
12.       throw new Error('User not found');
13.     }
14.    
15.     // 计算订单总额
16.     let totalAmount = 0;
17.     for (const productId of productIds) {
18.       const [products] = await connection.query('SELECT * FROM products WHERE id = ?', [productId]);
19.       if (products.length === 0) {
20.         throw new Error(`Product with ID ${productId} not found`);
21.       }
22.       totalAmount += parseFloat(products[0].price);
23.     }
24.    
25.     // 创建订单
26.     const [orderResult] = await connection.query(
27.       'INSERT INTO orders (user_id, total_amount) VALUES (?, ?)',
28.       [userId, totalAmount]
29.     );
30.     const orderId = orderResult.insertId;
31.    
32.     // 更新产品库存
33.     for (const productId of productIds) {
34.       await connection.query(
35.         'UPDATE products SET stock_quantity = stock_quantity - 1 WHERE id = ?',
36.         [productId]
37.       );
38.     }
39.    
40.     // 提交事务
41.     await connection.commit();
42.     console.log('Order placed successfully');
43.     return orderId;
44.    
45.   } catch (error) {
46.     // 发生错误，回滚事务
47.     await connection.rollback();
48.     console.error('Error placing order:', error);
49.     throw error;
50.   } finally {
51.     // 释放连接回连接池
52.     connection.release();
53.   }
54. }
56. // 使用示例
57. placeOrder(1, [1, 2, 3])
58.   .then(orderId => console.log('Order placed with ID:', orderId))
59.   .catch(error => console.error('Failed to place order:', error));

复制代码

2. 预处理语句防止SQL注入

SQL注入是一种常见的网络安全漏洞，通过使用预处理语句（参数化查询），可以有效防止SQL注入攻击。

2. async function safeUserLogin(email, password) {
3.   try {
4.     // 使用预处理语句防止SQL注入
5.     const [rows] = await promisePool.query(
6.       'SELECT * FROM users WHERE email = ? AND password = ?',
7.       [email, password]
8.     );
9.    
10.     if (rows.length > 0) {
11.       // 不返回密码等敏感信息
12.       const { password, ...userWithoutPassword } = rows[0];
13.       return userWithoutPassword;
14.     }
15.    
16.     return null; // 认证失败
17.   } catch (error) {
18.     console.error('Error during user login:', error);
19.     throw error;
20.   }
21. }
23. // 使用示例
24. safeUserLogin('user@example.com', 'securepassword123')
25.   .then(user => {
26.     if (user) {
27.       console.log('Login successful. User:', user);
28.     } else {
29.       console.log('Login failed. Invalid email or password.');
30.     }
31.   })
32.   .catch(error => console.error('Login error:', error));

复制代码

3. 异步操作处理

Node.js的异步特性使得处理数据库操作时需要特别注意。以下是使用async/await处理异步操作的示例：

2. class UserService {
3.   constructor(dbPool) {
4.     this.dbPool = dbPool;
5.   }
6.   
7.   async createUser(userData) {
8.     try {
9.       const { name, email } = userData;
10.       const [result] = await this.dbPool.query(
11.         'INSERT INTO users (name, email) VALUES (?, ?)',
12.         [name, email]
13.       );
14.       
15.       // 获取新创建的用户
16.       const [newUser] = await this.dbPool.query(
17.         'SELECT * FROM users WHERE id = ?',
18.         [result.insertId]
19.       );
20.       
21.       return newUser[0];
22.     } catch (error) {
23.       if (error.code === 'ER_DUP_ENTRY') {
24.         throw new Error('Email already exists');
25.       }
26.       console.error('Error creating user:', error);
27.       throw error;
28.     }
29.   }
30.   
31.   async getUserWithOrders(userId) {
32.     try {
33.       // 并行执行多个查询
34.       const [userResult, ordersResult] = await Promise.all([
35.         this.dbPool.query('SELECT * FROM users WHERE id = ?', [userId]),
36.         this.dbPool.query('SELECT * FROM orders WHERE user_id = ?', [userId])
37.       ]);
38.       
39.       const user = userResult[0][0];
40.       const orders = ordersResult[0];
41.       
42.       if (!user) {
43.         return null;
44.       }
45.       
46.       // 返回用户及其订单
47.       return {
48.         ...user,
49.         orders
50.       };
51.     } catch (error) {
52.       console.error('Error fetching user with orders:', error);
53.       throw error;
54.     }
55.   }
56. }
58. // 使用示例
59. const userService = new UserService(promisePool);
61. // 创建用户
62. userService.createUser({ name: 'Jane Doe', email: 'jane@example.com' })
63.   .then(user => {
64.     console.log('Created user:', user);
65.     // 获取用户及其订单
66.     return userService.getUserWithOrders(user.id);
67.   })
68.   .then(userWithOrders => {
69.     if (userWithOrders) {
70.       console.log('User with orders:', userWithOrders);
71.     } else {
72.       console.log('User not found');
73.     }
74.   })
75.   .catch(error => console.error('Error:', error));

复制代码

五、实战案例：构建一个简单的RESTful API

让我们使用Express框架和MySQL构建一个简单的用户管理API。

1. 安装必要的依赖

2. npm install express body-parser cors

复制代码

2. 创建API服务器

创建一个名为server.js的文件：

2. require('dotenv').config();
3. const express = require('express');
4. const bodyParser = require('body-parser');
5. const cors = require('cors');
6. const mysql = require('mysql2');
8. const app = express();
9. const PORT = process.env.PORT || 3000;
11. // 中间件
12. app.use(bodyParser.json());
13. app.use(cors());
15. // MySQL连接池
16. const pool = mysql.createPool({
17.   host: process.env.DB_HOST,
18.   user: process.env.DB_USER,
19.   password: process.env.DB_PASSWORD,
20.   database: process.env.DB_DATABASE,
21.   waitForConnections: true,
22.   connectionLimit: 10,
23.   queueLimit: 0
24. });
26. const promisePool = pool.promise();
28. // 确保数据库和表存在
29. async function initializeDatabase() {
30.   try {
31.     await promisePool.query('CREATE DATABASE IF NOT EXISTS my_app_db');
32.     await promisePool.query('USE my_app_db');
33.    
34.     const createUsersTable = `
35.       CREATE TABLE IF NOT EXISTS users (
36.         id INT AUTO_INCREMENT PRIMARY KEY,
37.         name VARCHAR(255) NOT NULL,
38.         email VARCHAR(255) NOT NULL UNIQUE,
39.         created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
40.       )
41.     `;
42.     await promisePool.query(createUsersTable);
43.     console.log('Database initialized');
44.   } catch (error) {
45.     console.error('Error initializing database:', error);
46.   }
47. }
49. // API路由
51. // 获取所有用户
52. app.get('/api/users', async (req, res) => {
53.   try {
54.     const [rows] = await promisePool.query('SELECT * FROM users');
55.     res.json({
56.       success: true,
57.       data: rows
58.     });
59.   } catch (error) {
60.     console.error('Error fetching users:', error);
61.     res.status(500).json({
62.       success: false,
63.       message: 'Error fetching users',
64.       error: error.message
65.     });
66.   }
67. });
69. // 获取单个用户
70. app.get('/api/users/:id', async (req, res) => {
71.   try {
72.     const { id } = req.params;
73.     const [rows] = await promisePool.query('SELECT * FROM users WHERE id = ?', [id]);
74.    
75.     if (rows.length === 0) {
76.       return res.status(404).json({
77.         success: false,
78.         message: 'User not found'
79.       });
80.     }
81.    
82.     res.json({
83.       success: true,
84.       data: rows[0]
85.     });
86.   } catch (error) {
87.     console.error('Error fetching user:', error);
88.     res.status(500).json({
89.       success: false,
90.       message: 'Error fetching user',
91.       error: error.message
92.     });
93.   }
94. });
96. // 创建用户
97. app.post('/api/users', async (req, res) => {
98.   try {
99.     const { name, email } = req.body;
100.    
101.     if (!name || !email) {
102.       return res.status(400).json({
103.         success: false,
104.         message: 'Name and email are required'
105.       });
106.     }
107.    
108.     const [result] = await promisePool.query(
109.       'INSERT INTO users (name, email) VALUES (?, ?)',
110.       [name, email]
111.     );
112.    
113.     const [newUser] = await promisePool.query(
114.       'SELECT * FROM users WHERE id = ?',
115.       [result.insertId]
116.     );
117.    
118.     res.status(201).json({
119.       success: true,
120.       data: newUser[0]
121.     });
122.   } catch (error) {
123.     console.error('Error creating user:', error);
124.    
125.     if (error.code === 'ER_DUP_ENTRY') {
126.       return res.status(400).json({
127.         success: false,
128.         message: 'Email already exists'
129.       });
130.     }
131.    
132.     res.status(500).json({
133.       success: false,
134.       message: 'Error creating user',
135.       error: error.message
136.     });
137.   }
138. });
140. // 更新用户
141. app.put('/api/users/:id', async (req, res) => {
142.   try {
143.     const { id } = req.params;
144.     const { name, email } = req.body;
145.    
146.     if (!name || !email) {
147.       return res.status(400).json({
148.         success: false,
149.         message: 'Name and email are required'
150.       });
151.     }
152.    
153.     const [result] = await promisePool.query(
154.       'UPDATE users SET name = ?, email = ? WHERE id = ?',
155.       [name, email, id]
156.     );
157.    
158.     if (result.affectedRows === 0) {
159.       return res.status(404).json({
160.         success: false,
161.         message: 'User not found'
162.       });
163.     }
164.    
165.     const [updatedUser] = await promisePool.query(
166.       'SELECT * FROM users WHERE id = ?',
167.       [id]
168.     );
169.    
170.     res.json({
171.       success: true,
172.       data: updatedUser[0]
173.     });
174.   } catch (error) {
175.     console.error('Error updating user:', error);
176.    
177.     if (error.code === 'ER_DUP_ENTRY') {
178.       return res.status(400).json({
179.         success: false,
180.         message: 'Email already exists'
181.       });
182.     }
183.    
184.     res.status(500).json({
185.       success: false,
186.       message: 'Error updating user',
187.       error: error.message
188.     });
189.   }
190. });
192. // 删除用户
193. app.delete('/api/users/:id', async (req, res) => {
194.   try {
195.     const { id } = req.params;
196.     const [result] = await promisePool.query('DELETE FROM users WHERE id = ?', [id]);
197.    
198.     if (result.affectedRows === 0) {
199.       return res.status(404).json({
200.         success: false,
201.         message: 'User not found'
202.       });
203.     }
204.    
205.     res.json({
206.       success: true,
207.       message: 'User deleted successfully'
208.     });
209.   } catch (error) {
210.     console.error('Error deleting user:', error);
211.     res.status(500).json({
212.       success: false,
213.       message: 'Error deleting user',
214.       error: error.message
215.     });
216.   }
217. });
219. // 启动服务器
220. app.listen(PORT, async () => {
221.   console.log(`Server running on port ${PORT}`);
222.   await initializeDatabase();
223. });

复制代码

3. 测试API

你可以使用Postman、curl或任何其他API测试工具来测试这个API。

2. curl -X POST http://localhost:3000/api/users \
3.   -H "Content-Type: application/json" \
4.   -d '{"name": "John Doe", "email": "john@example.com"}'

复制代码

2. curl http://localhost:3000/api/users

复制代码

2. curl http://localhost:3000/api/users/1

复制代码

2. curl -X PUT http://localhost:3000/api/users/1 \
3.   -H "Content-Type: application/json" \
4.   -d '{"name": "John Updated", "email": "john.updated@example.com"}'

复制代码

2. curl -X DELETE http://localhost:3000/api/users/1

复制代码

六、最佳实践与性能优化

1. 使用连接池

如前所述，使用连接池可以显著提高应用性能，避免频繁创建和销毁连接的开销。

2. 实施适当的错误处理

始终为数据库操作实施适当的错误处理，以防止未捕获的异常导致应用崩溃。

2. async function safeDatabaseOperation() {
3.   try {
4.     // 数据库操作
5.     const [rows] = await promisePool.query('SELECT * FROM users');
6.     return rows;
7.   } catch (error) {
8.     // 根据错误类型进行适当处理
9.     if (error.code === 'PROTOCOL_CONNECTION_LOST') {
10.       console.error('Database connection was lost.');
11.       // 尝试重新连接或通知用户
12.     } else if (error.code === 'ER_DUP_ENTRY') {
13.       console.error('Duplicate entry error.');
14.       // 处理重复条目错误
15.     } else {
16.       console.error('Database error:', error);
17.     }
18.     throw error; // 重新抛出错误以便上层处理
19.   }
20. }

复制代码

3. 使用索引优化查询性能

为经常查询的列创建索引可以显著提高查询性能。

2. async function createIndexes() {
3.   try {
4.     // 为email列创建唯一索引（如果已存在则忽略）
5.     await promisePool.query(
6.       'CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email ON users(email)'
7.     );
8.    
9.     // 为name列创建普通索引
10.     await promisePool.query(
11.       'CREATE INDEX IF NOT EXISTS idx_users_name ON users(name)'
12.     );
13.    
14.     console.log('Indexes created successfully');
15.   } catch (error) {
16.     console.error('Error creating indexes:', error);
17.     throw error;
18.   }
19. }

复制代码

4. 实施数据验证

在将数据发送到数据库之前，始终在应用层实施数据验证。

2. const Joi = require('joi'); // 需要安装: npm install joi
4. const userSchema = Joi.object({
5.   name: Joi.string().min(3).max(255).required(),
6.   email: Joi.string().email().required()
7. });
9. function validateUser(user) {
10.   return userSchema.validate(user);
11. }
13. // 在创建用户前使用
14. app.post('/api/users', async (req, res) => {
15.   const { error } = validateUser(req.body);
16.   if (error) {
17.     return res.status(400).json({
18.       success: false,
19.       message: error.details[0].message
20.     });
21.   }
22.   
23.   // 继续处理用户创建...
24. });

复制代码

5. 实施缓存策略

对于频繁访问但不经常更改的数据，实施缓存策略可以减少数据库负载。

2. const NodeCache = require('node-cache'); // 需要安装: npm install node-cache
3. const userCache = new NodeCache({ stdTTL: 300 }); // 缓存5分钟
5. async function getUserWithCache(id) {
6.   // 首先检查缓存
7.   const cachedUser = userCache.get(id);
8.   if (cachedUser) {
9.     return cachedUser;
10.   }
11.   
12.   // 如果不在缓存中，查询数据库
13.   try {
14.     const [rows] = await promisePool.query('SELECT * FROM users WHERE id = ?', [id]);
15.     if (rows.length > 0) {
16.       // 将结果存入缓存
17.       userCache.set(id, rows[0]);
18.       return rows[0];
19.     }
20.     return null;
21.   } catch (error) {
22.     console.error('Error fetching user:', error);
23.     throw error;
24.   }
25. }
27. // 当用户更新时，清除缓存
28. async function updateUser(id, userData) {
29.   try {
30.     // 更新数据库
31.     await promisePool.query(
32.       'UPDATE users SET name = ?, email = ? WHERE id = ?',
33.       [userData.name, userData.email, id]
34.     );
35.    
36.     // 清除缓存
37.     userCache.del(id);
38.    
39.     // 返回更新后的用户
40.     return getUserWithCache(id);
41.   } catch (error) {
42.     console.error('Error updating user:', error);
43.     throw error;
44.   }
45. }

复制代码

6. 使用ORM简化数据库操作

对于复杂项目，考虑使用ORM（Object-Relational Mapping）库如Sequelize或TypeORM，它们可以简化数据库操作并提供额外功能如数据验证、关联管理等。

2. npm install sequelize

复制代码

2. const { Sequelize, DataTypes } = require('sequelize');
4. // 初始化Sequelize
5. const sequelize = new Sequelize(
6.   process.env.DB_DATABASE,
7.   process.env.DB_USER,
8.   process.env.DB_PASSWORD,
9.   {
10.     host: process.env.DB_HOST,
11.     dialect: 'mysql'
12.   }
13. );
15. // 定义用户模型
16. const User = sequelize.define('User', {
17.   name: {
18.     type: DataTypes.STRING,
19.     allowNull: false
20.   },
21.   email: {
22.     type: DataTypes.STRING,
23.     allowNull: false,
24.     unique: true
25.   }
26. });
28. // 同步模型到数据库
29. sequelize.sync();
31. // 使用模型进行CRUD操作
32. async function sequelizeExample() {
33.   try {
34.     // 创建用户
35.     const newUser = await User.create({
36.       name: 'Jane Smith',
37.       email: 'jane@example.com'
38.     });
39.     console.log('Created user:', newUser.toJSON());
40.    
41.     // 查询用户
42.     const users = await User.findAll();
43.     console.log('All users:', users.map(u => u.toJSON()));
44.    
45.     // 更新用户
46.     await User.update(
47.       { name: 'Jane Updated' },
48.       { where: { id: newUser.id } }
49.     );
50.    
51.     // 删除用户
52.     await User.destroy({
53.       where: { id: newUser.id }
54.     });
55.   } catch (error) {
56.     console.error('Sequelize error:', error);
57.   }
58. }
60. sequelizeExample();

复制代码

七、总结

通过本指南，我们全面了解了如何在Node.js应用中连接和操作MySQL数据库。从基础的环境配置到复杂的数据库操作，再到构建完整的RESTful API，我们涵盖了全栈开发中与数据库交互的各个方面。

关键要点包括：

1. 环境配置：正确安装和配置Node.js与MySQL是开发的基础。
2. 连接管理：使用连接池可以提高应用性能和资源利用率。
3. 基本CRUD操作：掌握创建、读取、更新和删除数据的基本操作。
4. 高级功能：事务处理、预处理语句和异步操作处理是构建健壮应用的关键。
5. 实战应用：通过构建RESTful API，将理论知识应用到实际项目中。
6. 最佳实践：实施适当的错误处理、数据验证、缓存策略和使用ORM等最佳实践，可以提高应用的性能和可维护性。

随着你对Node.js和MySQL的深入理解，你将能够构建更加复杂和高效的全栈应用。不断实践和探索新的技术，你将成为一名真正的全栈开发高手。

希望本指南能够帮助你在Node.js与MySQL开发的道路上取得成功！