MySQL集成 #

一、MySQL简介 #

1.1 什么是MySQL？ #

MySQL是最流行的关系型数据库管理系统，具有高性能、高可靠性和易用性。

1.2 特点 #

成熟稳定，广泛使用
支持事务，ACID特性
丰富的索引类型
强大的查询优化器

1.3 安装 #

bash

npm install mysql2

二、连接MySQL #

2.1 基本连接 #

javascript

const mysql = require('mysql2');

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'myapp'
});

connection.connect((err) => {
    if (err) {
        console.error('连接失败:', err);
        return;
    }
    console.log('连接成功');
});

2.2 连接池 #

javascript

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'myapp',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

module.exports = pool;

2.3 配置文件 #

config/database.js：

javascript

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
    database: process.env.DB_NAME || 'myapp',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

const testConnection = async () => {
    try {
        const connection = await pool.getConnection();
        console.log('数据库连接成功');
        connection.release();
    } catch (error) {
        console.error('数据库连接失败:', error.message);
    }
};

module.exports = { pool, testConnection };

三、基本查询 #

3.1 查询数据 #

javascript

const [rows] = await pool.query('SELECT * FROM users');
const [rows] = await pool.query('SELECT * FROM users WHERE id = ?', [id]);
const [rows] = await pool.query('SELECT * FROM users WHERE role = ? AND active = ?', ['admin', true]);

3.2 插入数据 #

javascript

const [result] = await pool.query(
    'INSERT INTO users (name, email, password) VALUES (?, ?, ?)',
    [name, email, password]
);
console.log('插入ID:', result.insertId);

3.3 更新数据 #

javascript

const [result] = await pool.query(
    'UPDATE users SET name = ?, email = ? WHERE id = ?',
    [name, email, id]
);
console.log('影响行数:', result.affectedRows);

3.4 删除数据 #

javascript

const [result] = await pool.query('DELETE FROM users WHERE id = ?', [id]);
console.log('影响行数:', result.affectedRows);

四、模型封装 #

4.1 基础模型 #

models/Model.js：

javascript

const pool = require('../config/database').pool;

class Model {
    constructor(tableName) {
        this.tableName = tableName;
    }
    
    async findAll(options = {}) {
        let sql = `SELECT * FROM ${this.tableName}`;
        const params = [];
        
        if (options.where) {
            const conditions = Object.keys(options.where)
                .map(key => `${key} = ?`)
                .join(' AND ');
            sql += ` WHERE ${conditions}`;
            params.push(...Object.values(options.where));
        }
        
        if (options.order) {
            sql += ` ORDER BY ${options.order}`;
        }
        
        if (options.limit) {
            sql += ` LIMIT ?`;
            params.push(options.limit);
        }
        
        const [rows] = await pool.query(sql, params);
        return rows;
    }
    
    async findById(id) {
        const [rows] = await pool.query(
            `SELECT * FROM ${this.tableName} WHERE id = ?`,
            [id]
        );
        return rows[0];
    }
    
    async create(data) {
        const keys = Object.keys(data);
        const values = Object.values(data);
        const placeholders = keys.map(() => '?').join(', ');
        
        const [result] = await pool.query(
            `INSERT INTO ${this.tableName} (${keys.join(', ')}) VALUES (${placeholders})`,
            values
        );
        return { id: result.insertId, ...data };
    }
    
    async update(id, data) {
        const keys = Object.keys(data);
        const values = Object.values(data);
        const setClause = keys.map(key => `${key} = ?`).join(', ');
        
        const [result] = await pool.query(
            `UPDATE ${this.tableName} SET ${setClause} WHERE id = ?`,
            [...values, id]
        );
        return result.affectedRows > 0;
    }
    
    async delete(id) {
        const [result] = await pool.query(
            `DELETE FROM ${this.tableName} WHERE id = ?`,
            [id]
        );
        return result.affectedRows > 0;
    }
}

module.exports = Model;

4.2 用户模型 #

models/User.js：

javascript

const Model = require('./Model');
const bcrypt = require('bcryptjs');

class User extends Model {
    constructor() {
        super('users');
    }
    
    async findByEmail(email) {
        const [rows] = await pool.query(
            'SELECT * FROM users WHERE email = ?',
            [email]
        );
        return rows[0];
    }
    
    async create(userData) {
        const hashedPassword = await bcrypt.hash(userData.password, 10);
        return await super.create({
            ...userData,
            password: hashedPassword
        });
    }
    
    async comparePassword(plainPassword, hashedPassword) {
        return await bcrypt.compare(plainPassword, hashedPassword);
    }
    
    async paginate(page = 1, limit = 10) {
        const offset = (page - 1) * limit;
        
        const [rows] = await pool.query(
            'SELECT id, name, email, role, created_at FROM users LIMIT ? OFFSET ?',
            [limit, offset]
        );
        
        const [countRows] = await pool.query('SELECT COUNT(*) as total FROM users');
        const total = countRows[0].total;
        
        return {
            users: rows,
            pagination: {
                page,
                limit,
                total,
                totalPages: Math.ceil(total / limit)
            }
        };
    }
}

module.exports = new User();

五、事务处理 #

5.1 基本事务 #

javascript

const connection = await pool.getConnection();

try {
    await connection.beginTransaction();
    
    await connection.query('INSERT INTO orders (user_id, total) VALUES (?, ?)', [userId, total]);
    const [result] = await connection.query('SELECT LAST_INSERT_ID() as id');
    const orderId = result[0].id;
    
    for (const item of items) {
        await connection.query(
            'INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?)',
            [orderId, item.productId, item.quantity]
        );
    }
    
    await connection.commit();
    return orderId;
} catch (error) {
    await connection.rollback();
    throw error;
} finally {
    connection.release();
}

5.2 事务封装 #

javascript

const transaction = async (callback) => {
    const connection = await pool.getConnection();
    
    try {
        await connection.beginTransaction();
        const result = await callback(connection);
        await connection.commit();
        return result;
    } catch (error) {
        await connection.rollback();
        throw error;
    } finally {
        connection.release();
    }
};

await transaction(async (conn) => {
    await conn.query('INSERT INTO users SET ?', [user]);
    await conn.query('INSERT INTO profiles SET ?', [profile]);
});

六、JOIN查询 #

6.1 内连接 #

javascript

const [rows] = await pool.query(`
    SELECT users.name, posts.title, posts.content
    FROM users
    INNER JOIN posts ON users.id = posts.user_id
`);

6.2 左连接 #

javascript

const [rows] = await pool.query(`
    SELECT users.name, COUNT(posts.id) as post_count
    FROM users
    LEFT JOIN posts ON users.id = posts.user_id
    GROUP BY users.id
`);

6.3 多表连接 #

javascript

const [rows] = await pool.query(`
    SELECT 
        orders.id,
        users.name as customer,
        products.name as product,
        order_items.quantity
    FROM orders
    INNER JOIN users ON orders.user_id = users.id
    INNER JOIN order_items ON orders.id = order_items.order_id
    INNER JOIN products ON order_items.product_id = products.id
`);

七、存储过程 #

7.1 调用存储过程 #

javascript

const [rows] = await pool.query('CALL GetUserOrders(?)', [userId]);

7.2 创建存储过程 #

javascript

await pool.query(`
    CREATE PROCEDURE GetUserOrders(IN userId INT)
    BEGIN
        SELECT * FROM orders WHERE user_id = userId;
    END
`);

八、完整示例 #

8.1 用户控制器 #

javascript

const User = require('../models/User');
const { pool } = require('../config/database');

const userController = {
    async index(req, res, next) {
        try {
            const page = parseInt(req.query.page) || 1;
            const limit = parseInt(req.query.limit) || 10;
            
            const { users, pagination } = await User.paginate(page, limit);
            
            res.json({ users, pagination });
        } catch (error) {
            next(error);
        }
    },
    
    async show(req, res, next) {
        try {
            const user = await User.findById(req.params.id);
            
            if (!user) {
                return res.status(404).json({ error: '用户不存在' });
            }
            
            delete user.password;
            res.json(user);
        } catch (error) {
            next(error);
        }
    },
    
    async store(req, res, next) {
        try {
            const { name, email, password, role } = req.body;
            
            const existingUser = await User.findByEmail(email);
            if (existingUser) {
                return res.status(400).json({ error: '邮箱已被注册' });
            }
            
            const user = await User.create({ name, email, password, role });
            delete user.password;
            
            res.status(201).json(user);
        } catch (error) {
            next(error);
        }
    },
    
    async update(req, res, next) {
        try {
            const { name, email, role } = req.body;
            
            const success = await User.update(req.params.id, { name, email, role });
            
            if (!success) {
                return res.status(404).json({ error: '用户不存在' });
            }
            
            res.json({ message: '更新成功' });
        } catch (error) {
            next(error);
        }
    },
    
    async destroy(req, res, next) {
        try {
            const success = await User.delete(req.params.id);
            
            if (!success) {
                return res.status(404).json({ error: '用户不存在' });
            }
            
            res.status(204).send();
        } catch (error) {
            next(error);
        }
    }
};

module.exports = userController;

九、总结 #

MySQL集成要点：

概念	说明
连接池	mysql2/promise
查询	pool.query()
参数化	防止SQL注入
事务	beginTransaction/commit/rollback
JOIN	多表关联查询

下一步，让我们学习ORM使用！