最近在學習swoole悴势，自己完成下論壇里留下的作業(yè)胸私，通過swoole_websocket實時展示mysql數(shù)據(jù)吹截，有個遺留問題瘦陈，如何判斷mysql是否有增刪改，我想到的方法有：
1波俄、在應用層中有增刪改時晨逝，發(fā)一個消息到消息隊列來監(jiān)聽。
2懦铺、解析mysql bin-log日志捉貌。
3、觸發(fā)器冬念。
現(xiàn)在的解決辦法是利用觸發(fā)器趁窃，例子中我只監(jiān)聽了一個表，當有多個表時急前，需要添加大量觸發(fā)器醒陆，觸發(fā)器本身占用很多資源，太多觸發(fā)器也不好管理裆针，之后會再想辦法解析下bin-log日志來監(jiān)聽Mysql數(shù)據(jù)變化刨摩。
服務端代碼
<?php
/**
* Created by PhpStorm.
* User: qyc
* Date: 2017/10/22
* Time: 下午4:40
*/
define('HOST', '0.0.0.0');
define('PORT', '9502');
define('WORKER_NUM', 8);
define('DISPATCH_MODE', 2);
define('DAEMONIZE', 0);
class swoole_ws
{
private $pdo;
private $server;
//這里寫死了數(shù)據(jù)表，之后完成數(shù)據(jù)庫監(jiān)聽后改寫這里邏輯
private $table
= [
'swoole_test',
];
public function __construct()
{
$this->server = new swoole_websocket_server(HOST, PORT);
$this->server->set(
[
//開啟woker進程數(shù)
'worker_num' => WORKER_NUM,
//請求分發(fā)策略世吨，
'dispatch_mode' => DISPATCH_MODE,
//是否守護進程
'daemonize' => DAEMONIZE,
]
);
$this->server->on('message', [$this, 'onMessage']);
$this->server->on('open', [$this, 'onOpen']);
$this->server->on('workerStart', [$this, 'onWorkerStart']);
$this->server->start();
}
//woker進程
public function onWorkerStart(swoole_websocket_server $server, $worker_id)
{
if ($worker_id == 0) {
// 0 worker進程開啟一個定時器去監(jiān)聽mysql數(shù)據(jù)變化
$this->server->tick(500, [$this, 'onTick']);
}
//每個worker進程維持一個pdo連接
$this->pdo = new PDO("mysql:host=localhost;dbname=swoole_ws", "root", "root");
}
//接受客戶端數(shù)據(jù)
public function onMessage(swoole_websocket_server $server, swoole_websocket_frame $frame)
{
// echo $frame->data; //客戶端發(fā)送的數(shù)據(jù)
// echo $server->worker_id;
}
//客戶端 服務端建立連接并完成握手后的回調
public function onOpen(swoole_websocket_server $server, swoole_http_request $request)
{
//第一次連接去獲取一下Mysql數(shù)據(jù)
$this->update();
}
private function update()
{
$res = [];
foreach ($this->table as $table) {
$res[$table] = $this->getTableData($table);
}
foreach ($this->server->connections as $connection) {
//向所有客戶端發(fā)送數(shù)據(jù)
$this->server->push($connection, json_encode($res));
}
}
//獲取表數(shù)據(jù)
private function getTableData($table)
{
$sql = 'select * from ' . $table;
try {
$stmt = $this->pdo->prepare($sql);
$stmt->execute();
$res = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $res ?: [];
} catch (Exception $e) {
return [];
}
}
//定時器回調
public function onTick()
{
/*
* is_update表記錄表是否更新過
* swoole_test表添加了3個觸發(fā)器澡刹， after insert、update耘婚、delete罢浇，
* 會向is_update表更新swoole_test是否有更新操作
*/
try {
$sql = 'select is_update from is_update where table_name = "swoole_test"';
$stmt = $this->pdo->prepare($sql);
$stmt->execute();
$res = $stmt->fetchAll(PDO::FETCH_ASSOC);
if ( ! $res) {
return [];
}
if ($res[0]['is_update'] == 1) {
//當is_update字段為1時表明數(shù)據(jù)有更新，向客戶端推送消息
$this->update();
//更新下表更新字段
$update = 'update is_update set is_update=0 where table_name = "swoole_test"';
$stmt = $this->pdo->prepare($update);
$stmt->execute();
}
} catch (Exception $e) {
$this->pdo = new PDO("mysql:host=localhost;dbname=swoole_ws", "root", "root");
}
}
}
new swoole_ws();
客戶端代碼
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Mysql實時數(shù)據(jù)</title>
<link rel="stylesheet"
integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
<!--[if lt IE 9]>
<script src="https://cdn.bootcss.com/html5shiv/3.7.3/html5shiv.min.js"></script>
<script src="https://cdn.bootcss.com/respond.js/1.4.2/respond.min.js"></script>
<![endif]-->
<script src="https://cdn.bootcss.com/jquery/1.12.4/jquery.min.js"></script>
<script src="https://cdn.bootcss.com/bootstrap/3.3.7/js/bootstrap.min.js"
integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa"
crossorigin="anonymous"></script>
</head>
<body>
<div class="container">
<div class="panel panel-default">
<div class="panel-heading">
<h3 class="panel-title">swoole_test</h3>
</div>
<div class="panel-body">
<table class="table table-bordered table-hover">
<thead id="thead">
</thead>
<tbody id="tbody">
</tbody>
</table>
</div>
</div>
</div>
<script>
var websocket = new WebSocket('ws://127.0.0.1:9502');
websocket.onopen = function (event) {
console.log('websocket connect');
websocket.send('hello swoole');
};
websocket.onclose = function (event) {
console.log('websocket close');
};
websocket.onerror = function (event, e) {
console.log('error occured:' + event.data);
};
websocket.onmessage = function (event) {
data = JSON.parse(event.data)['swoole_test'];
var th = '<tr>';
var width = 1 / json_length(data[0]) * 100;
for (var key in data[0]) {
th += "<th style='width:" + width + "%'>" + key + "</th>";
}
th += '</tr>';
$("#thead").html(th);
var tbody = '';
for (var line in data) {
tbody += '<tr>';
var td = '';
for (var column in data[line]) {
td += "<td>" + data[line][column] + "</td>";
}
tbody += td + '</tr>';
}
$("#tbody").html(tbody);
};
function json_length(json) {
var length = 0;
for (var item in json) {
length++;
}
return length;
}
</script>
</body>
</html>
觸發(fā)器
DELIMITER $$
/*更新觸發(fā)器*/
DROP TRIGGER IF EXISTS swoole_test_is_update;
$$
CREATE TRIGGER swoole_test_is_update AFTER UPDATE ON swoole_test FOR EACH ROW
BEGIN
UPDATE `is_update`
SET is_update = 1
WHERE TABLE_NAME = 'swoole_test';
END;
$$
/*添加觸發(fā)器*/
DROP TRIGGER IF EXISTS swoole_test_is_insert;
$$
CREATE TRIGGER swoole_test_is_insert AFTER UPDATE ON swoole_test FOR EACH ROW
BEGIN
UPDATE `is_update`
SET is_update = 1
WHERE TABLE_NAME = 'swoole_test';
END;
$$
/*刪除觸發(fā)器*/
DROP TRIGGER IF EXISTS swoole_test_is_delete;
$$
CREATE TRIGGER swoole_test_is_delete AFTER UPDATE ON swoole_test FOR EACH ROW
BEGIN
UPDATE `is_update`
SET is_update = 1
WHERE TABLE_NAME = 'swoole_test';
END;
$$
DELIMITER ;
開啟websocker服務端
php swoole_ws;
運行效果
現(xiàn)在訪問前端頁面沐祷，對數(shù)據(jù)庫進行增刪改操作測試效果嚷闭。
1.png
結語
這只是個swoole_websocket練習，熟悉一下戈轿，大家可以看看Swoole官方文檔進行學習凌受，會有不錯的提升，之后會做個即時聊天工具玩玩思杯，Just code for fun ~
