当MySQL表被修改（如添加行）时，如何在PHP中执行函数？

以下是三种常用的实现方案，你可以根据自己的场景选择：

1. 短轮询（最简单的快速实现） #

定时向服务器发送请求，检查是否有新数据，适合小流量场景。

前端代码（JavaScript） #

// 每2秒发起一次请求
setInterval(() => {
    // 获取页面最后一条数据的ID（假设每条数据有唯一id字段）
    const lastId = document.querySelector('.message-item:last-child')?.dataset.id || 0;

    fetch(`get_new_data.php?last_id=${lastId}`)
        .then(res => res.json())
        .then(data => {
            if (data.length === 0) return;
            
            const container = document.getElementById('messages-container');
            data.forEach(item => {
                // 创建新消息元素并添加到页面
                const div = document.createElement('div');
                div.className = 'message-item';
                div.dataset.id = item.id;
                div.innerHTML = `<strong>${item.username}</strong>: ${item.content}`;
                container.appendChild(div);
                // 自动滚动到最新消息
                container.scrollTop = container.scrollHeight;
            });
        });
}, 2000);

后端代码（get_new_data.php） #

<?php
// 替换为你的数据库连接信息
$conn = mysqli_connect('localhost', 'db_user', 'db_pass', 'db_name');
if (!$conn) exit(json_encode([]));

$lastId = isset($_GET['last_id']) ? intval($_GET['last_id']) : 0;

// 查询比lastId更大的新数据
$sql = "SELECT id, username, content FROM messages WHERE id > ? ORDER BY id ASC";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, 'i', $lastId);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

$newData = [];
while ($row = mysqli_fetch_assoc($result)) {
    $newData[] = $row;
}

echo json_encode($newData);
mysqli_close($conn);
?>

2. 长轮询（更高效的轮询方案） #

前端发起请求后，服务器挂起连接，直到有新数据或超时才返回，减少无效请求次数。

前端代码（JavaScript） #

function longPoll() {
    const lastId = document.querySelector('.message-item:last-child')?.dataset.id || 0;

    fetch(`long_poll.php?last_id=${lastId}`)
        .then(res => res.json())
        .then(data => {
            if (data.length > 0) {
                const container = document.getElementById('messages-container');
                data.forEach(item => {
                    const div = document.createElement('div');
                    div.className = 'message-item';
                    div.dataset.id = item.id;
                    div.innerHTML = `<strong>${item.username}</strong>: ${item.content}`;
                    container.appendChild(div);
                    container.scrollTop = container.scrollHeight;
                });
            }
            // 立即发起下一次请求
            longPoll();
        })
        .catch(() => {
            // 出错后延迟3秒重试
            setTimeout(longPoll, 3000);
        });
}

// 页面加载后启动长轮询
window.onload = longPoll;

后端代码（long_poll.php） #

<?php
set_time_limit(60); // 设置超时时间为60秒
$conn = mysqli_connect('localhost', 'db_user', 'db_pass', 'db_name');
if (!$conn) exit(json_encode([]));

$lastId = isset($_GET['last_id']) ? intval($_GET['last_id']) : 0;

// 循环检测新数据
while (true) {
    $sql = "SELECT id, username, content FROM messages WHERE id > ? ORDER BY id ASC";
    $stmt = mysqli_prepare($conn, $sql);
    mysqli_stmt_bind_param($stmt, 'i', $lastId);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);

    $newData = [];
    while ($row = mysqli_fetch_assoc($result)) {
        $newData[] = $row;
    }

    if (!empty($newData)) {
        echo json_encode($newData);
        mysqli_close($conn);
        exit;
    }

    // 无新数据时休眠2秒再检测
    sleep(2);
}
?>

3. WebSocket（实时性最高的方案） #

通过双向通信协议，服务器可以主动推送新数据给前端，适合高并发、低延迟场景，需要服务器支持WebSocket。

后端代码（基于Ratchet库） #

先安装依赖：

composer require cboden/ratchet

创建WebSocket服务器（server.php）：

<?php
use Ratchet\MessageComponentInterface;
use Ratchet\ConnectionInterface;
use Ratchet\Server\IoServer;
use Ratchet\Http\HttpServer;
use Ratchet\WebSocket\WsServer;
require 'vendor/autoload.php';

class MessagePusher implements MessageComponentInterface {
    protected $clients;
    protected $lastId;
    protected $dbConn;

    public function __construct() {
        $this->clients = new \SplObjectStorage;
        // 连接数据库
        $this->dbConn = mysqli_connect('localhost', 'db_user', 'db_pass', 'db_name');
        // 获取当前最大消息ID
        $result = mysqli_query($this->dbConn, "SELECT MAX(id) as max_id FROM messages");
        $row = mysqli_fetch_assoc($result);
        $this->lastId = $row['max_id'] ?: 0;

        // 启动后台线程检测数据库更新
        $this->watchDb();
    }

    public function onOpen(ConnectionInterface $conn) {
        $this->clients->attach($conn);
    }

    public function onMessage(ConnectionInterface $from, $msg) {
        // 处理前端发送的消息（比如新增消息到数据库）
        $data = json_decode($msg, true);
        if (isset($data['username'], $data['content'])) {
            $stmt = mysqli_prepare($this->dbConn, "INSERT INTO messages (username, content) VALUES (?, ?)");
            mysqli_stmt_bind_param($stmt, 'ss', $data['username'], $data['content']);
            mysqli_stmt_execute($stmt);
        }
    }

    public function onClose(ConnectionInterface $conn) {
        $this->clients->detach($conn);
    }

    public function onError(ConnectionInterface $conn, \Exception $e) {
        $conn->close();
    }

    protected function watchDb() {
        while (true) {
            $result = mysqli_query($this->dbConn, "SELECT id, username, content FROM messages WHERE id > {$this->lastId} ORDER BY id ASC");
            $newData = [];
            while ($row = mysqli_fetch_assoc($result)) {
                $newData[] = $row;
                $this->lastId = $row['id'];
            }

            if (!empty($newData)) {
                // 推送给所有在线客户端
                foreach ($this->clients as $client) {
                    $client->send(json_encode($newData));
                }
            }

            sleep(1);
        }
    }
}

// 启动WebSocket服务器，监听8080端口
$server = IoServer::factory(
    new HttpServer(
        new WsServer(
            new MessagePusher()
        )
    ),
    8080
);

$server->run();

前端代码（JavaScript） #

// 连接WebSocket服务器
const ws = new WebSocket('ws://localhost:8080');

ws.onopen = () => console.log('WebSocket连接成功');

ws.onmessage = (event) => {
    const data = JSON.parse(event.data);
    const container = document.getElementById('messages-container');
    data.forEach(item => {
        const div = document.createElement('div');
        div.className = 'message-item';
        div.dataset.id = item.id;
        div.innerHTML = `<strong>${item.username}</strong>: ${item.content}`;
        container.appendChild(div);
        container.scrollTop = container.scrollHeight;
    });
};

ws.onerror = (err) => console.error('WebSocket错误:', err);

ws.onclose = () => {
    console.log('连接断开，3秒后重试');
    setTimeout(() => window.location.reload(), 3000);
};

// 发送消息的示例函数
function sendMsg() {
    const username = document.getElementById('username').value;
    const content = document.getElementById('content').value;
    ws.send(JSON.stringify({username, content}));
    document.getElementById('content').value = '';
}

内容的提问来源于stack exchange，提问作者goteki