SQL UPDATE时向已连接客户端返回数据的实现方案

Hey there! I get that constant polling is a total drag—inefficient, resource-heavy, and just plain clunky. Let’s break down practical, Google Cloud-native solutions to build a "push" system where your PHP server gets notified only when your Cloud SQL (SQL Server) table updates, then expose that via a REST API for easy cross-platform integration.

Option 1: SQL Server Service Broker (Native Database Messaging) #

SQL Server’s Service Broker is built for asynchronous internal messaging—perfect for triggering notifications when tables change. Triggers can’t send data directly to your PHP server, but they can drop messages into a broker queue that your PHP script can listen to 24/7.

Step 1: Configure Service Broker in Cloud SQL #

First, enable Service Broker on your Cloud SQL instance (do this via the Cloud Console or T-SQL):

ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;
GO

Next, set up the queue, service, and trigger to send messages on table updates:

-- Create a simple message type
CREATE MESSAGE TYPE [UpdateNotificationMessage] VALIDATION = NONE;
GO

-- Define a contract for message exchange
CREATE CONTRACT [UpdateNotificationContract] ([UpdateNotificationMessage] SENT BY INITIATOR);
GO

-- Create a queue to hold incoming update notifications
CREATE QUEUE [UpdateNotificationQueue];
GO

-- Link the queue to a service using the contract
CREATE SERVICE [UpdateNotificationService] ON QUEUE [UpdateNotificationQueue] ([UpdateNotificationContract]);
GO

-- Trigger that sends a message whenever your target table changes
CREATE TRIGGER [Trigger_OnTableUpdate] ON [YourTargetTable]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- Start a conversation with the broker
    DECLARE @conversationHandle UNIQUEIDENTIFIER;
    BEGIN DIALOG CONVERSATION @conversationHandle
        FROM SERVICE [UpdateNotificationService]
        TO SERVICE 'UpdateNotificationService'
        ON CONTRACT [UpdateNotificationContract]
        WITH ENCRYPTION = OFF;

    -- Send the update notification
    SEND ON CONVERSATION @conversationHandle
        MESSAGE TYPE [UpdateNotificationMessage] ('Table updated at ' + CONVERT(VARCHAR, GETDATE()));

    -- Close the conversation
    END CONVERSATION @conversationHandle;
END
GO

Step 2: Persistent PHP Listener Script #

Write a PHP script that stays connected to Cloud SQL and listens for new messages. Run this as a background process on your Google Cloud VM (use systemd or supervisord to keep it running if it crashes):

<?php
// Persistent connection to Cloud SQL SQL Server
$serverName = "tcp:your-cloud-sql-ip,1433";
$connectionOptions = [
    "Database" => "YourDatabaseName",
    "Uid" => "YourUsername",
    "PWD" => "YourPassword",
    "ConnectionPooling" => true, // Keep connection alive
    "LoginTimeout" => 30
];

$conn = sqlsrv_connect($serverName, $connectionOptions);
if (!$conn) {
    die("Connection failed: " . print_r(sqlsrv_errors(), true));
}

// Listen for updates indefinitely
while (true) {
    // Fetch new messages from the queue
    $sql = "
        RECEIVE TOP(1)
            message_body, conversation_handle
        FROM UpdateNotificationQueue;
    ";
    $stmt = sqlsrv_query($conn, $sql);
    
    if ($stmt && sqlsrv_has_rows($stmt)) {
        $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
        $message = $row['message_body'];
        $conversationHandle = $row['conversation_handle'];

        // Process the update here: update a cache, log the event, etc.
        file_put_contents('/var/log/sql_updates.log', "Received update: " . $message . "\n", FILE_APPEND);

        // Clean up the conversation
        sqlsrv_query($conn, "END CONVERSATION ?", [$conversationHandle]);
    }

    // Small delay to reduce CPU load (adjust as needed)
    sleep(1);
}

sqlsrv_close($conn);
?>

Step 3: REST API Integration #

Your REST API can now serve fresh data by reading from a cache (updated by the listener) or directly querying the database. Here’s a simple endpoint example:

<?php
// REST API endpoint: /api/latest-data
function getLatestCachedData() {
    // Logic to fetch updated data from cache/database
    $conn = sqlsrv_connect("tcp:your-cloud-sql-ip,1433", $connectionOptions);
    $sql = "SELECT * FROM YourTargetTable ORDER BY UpdatedAt DESC LIMIT 10";
    $stmt = sqlsrv_query($conn, $sql);
    $data = [];
    while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
        $data[] = $row;
    }
    sqlsrv_close($conn);
    return $data;
}

header('Content-Type: application/json');
echo json_encode(getLatestCachedData());
?>

Option 2: Google Cloud Pub/Sub (Cloud-Native Messaging) #

If you prefer leveraging Google’s managed services over database-level setup, Pub/Sub is a great choice. It decouples your SQL Server from the PHP server, making scaling and maintenance easier.

Step 1: Set Up Pub/Sub Topic & Subscription #

1. Create a Pub/Sub topic (e.g., sql-table-updates) via the Cloud Console or gcloud CLI.
2. Create a subscription for your PHP server to listen to this topic.

Step 2: SQL Trigger + Pub/Sub Publisher #

SQL Server triggers can’t call external APIs directly, so we’ll log updates to a staging table, then use a cron job to publish those logs to Pub/Sub:

-- Staging table to log updates
CREATE TABLE [UpdateLog] (
    LogId INT IDENTITY(1,1) PRIMARY KEY,
    UpdateTime DATETIME DEFAULT GETDATE(),
    TableName VARCHAR(50),
    Processed BIT DEFAULT 0
);
GO

-- Trigger to log table changes
CREATE TRIGGER [Trigger_LogTableUpdate] ON [YourTargetTable]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO [UpdateLog] (TableName) VALUES ('YourTargetTable');
END
GO

Run a cron job on your VM to read the staging table and publish to Pub/Sub:

<?php
require __DIR__ . '/vendor/autoload.php';
use Google\Cloud\PubSub\PubSubClient;

// Initialize Pub/Sub client
$pubsub = new PubSubClient([
    'projectId' => 'your-gcp-project-id',
]);
$topic = $pubsub->topic('sql-table-updates');

// Connect to Cloud SQL
$conn = sqlsrv_connect("tcp:your-cloud-sql-ip,1433", $connectionOptions);
$sql = "SELECT * FROM UpdateLog WHERE Processed = 0";
$stmt = sqlsrv_query($conn, $sql);

// Publish unprocessed logs to Pub/Sub
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
    $topic->publish([
        'data' => json_encode([
            'table' => $row['TableName'],
            'time' => $row['UpdateTime']->format('Y-m-d H:i:s')
        ])
    ]);

    // Mark log as processed
    sqlsrv_query($conn, "UPDATE UpdateLog SET Processed = 1 WHERE LogId = ?", [$row['LogId']]);
}

sqlsrv_close($conn);
?>

Step 3: PHP Pub/Sub Subscriber #

Run a persistent subscriber script on your VM to receive updates and refresh your API’s data:

<?php
require __DIR__ . '/vendor/autoload.php';
use Google\Cloud\PubSub\PubSubClient;

$pubsub = new PubSubClient([
    'projectId' => 'your-gcp-project-id',
]);
$subscription = $pubsub->subscription('sql-table-updates-sub');

// Listen for messages indefinitely
$subscription->listen(function ($message) {
    // Process the update: clear cache, refresh data, etc.
    $data = json_decode($message->data(), true);
    file_put_contents('/var/log/sql_updates.log', "Table updated: " . $data['table'] . " at " . $data['time'] . "\n", FILE_APPEND);

    // Acknowledge the message to remove it from the subscription
    $message->ack();
});
?>

Which Option Should You Pick? #

• Service Broker: Best for a self-contained, low-latency solution with no external dependencies. Great if you want to keep everything within the database ecosystem.
• Pub/Sub: Ideal for scaling and flexibility. It’s managed by Google, so you don’t have to maintain messaging infrastructure, and it works seamlessly with other GCP services.

Both solutions eliminate client-side polling entirely—your PHP server gets notified the moment data changes, and your REST API can serve fresh data to clients via simple http.get requests.

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