如何使用MySQL和JavaScript实现一个简单的即时聊天功能

CREATE TABLE messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sender VARCHAR(255) NOT NULL,
    receiver VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

npm install mysql

const mysql = require('mysql');

// 创建与数据库的连接
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'chat'
});

// 连接到数据库
connection.connect((err) => {
    if (err) throw err;
    console.log('Connected to the database');
});

// 发送消息
function sendMessage(sender, receiver, message) {
    const sql = 'INSERT INTO messages (sender, receiver, message) VALUES (?, ?, ?)';
    const values = [sender, receiver, message];
    connection.query(sql, values, (err) => {
        if (err) throw err;
        console.log('Message sent');
    });
}

// 获取聊天记录
function getMessages(sender, receiver) {
    const sql = 'SELECT * FROM messages WHERE (sender = ? AND receiver = ?) OR (sender = ? AND receiver = ?) ORDER BY timestamp';
    const values = [sender, receiver, receiver, sender];
    connection.query(sql, values, (err, results) => {
        if (err) throw err;
        console.log('Chat history:');
        results.forEach((row) => {
            console.log(`${row.sender} -> ${row.receiver}: ${row.message}`);
        });
    });
}

// 使用示例
sendMessage('Alice', 'Bob', 'Hello Bob');
sendMessage('Bob', 'Alice', 'Hi Alice');
getMessages('Alice', 'Bob');