实现一个简单的服务端推送方案-实例篇Polling(服务端阻塞读,SLEEP方式读MYSQL) - 数据库编程

前面讲过一篇《实现一个简单的服务端推送方案-实例篇Polling(服务端阻塞读)》，那篇文章服务端利用Gearman队列来实现阻塞读，这篇服务器端用原生MySQL的Sleep函数来实现阻塞读，这篇文章也是另一篇文章《使用MySQL构建一个队列表》的实例实现。

客户端代码，JS库为prototype.js：





无标题文档
<script language="java script" type="text/java script" src="./prototype.js">




<script language="java script">
  var Class = {
    create: function() {
      return function() {
        this.initialize.apply(this, arguments);
      }  
    }
  }
  var Comet = Class.create();
  Comet.prototype = {
      maxvid: 0,
      url: './backend.php',
      noerror: true,
      initialize: function(){
      },
      connect: function(){
          this.ajax = new Ajax.Request(this.url, {
              method: 'get',
              parameters: {
                  'maxvid': this.maxvid
              },
              onSuccess: function(transport){
                  var response = transport.responseText.eva lJSON();
                  this.comet.maxvid = response['vid'];
                  this.comet.handleResponse(response);
                  this.comet.noerror = true;
              },
              onComplete: function(transport){
                  if (!this.comet.noerror) setTimeout(function(){
                                           comet.connect()
                                           }, 5000);
                  else
                  this.comet.connect();
                  this.comet.noerror = false;
              }
          });
          this.ajax.comet = this;
      },
      handleResponse: function(response){
          $('content').innerHTML += '' + response['msg'] + '';
      }
  }

  var comet = new Comet();
  comet.connect();

服务器端代码，backend.php，在这里当读不到新数据时，就调用"带注释的Sleep函数"来阻塞，阻塞直到超时或线程被KILL

< php

header("Cache-Control: no-cache, must-reva lidate");
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
flush();
//控制浏览器前端不要缓存结果，每次都要重新查询

$maxvid = $_GET["maxvid"];
//$maxvid = -1;
error_log(date("[Y-m-d H:i:s]")." > "."maxvid: ".$maxvid."\n", 3 , "/usr/local/apache2219/logs/php_log");

$dblnk = mysql_connect('localhost:3306', 'root', 'cpyf');
mysql_select_db('test', $dblnk);

if ( $maxvid <= 0 ) {
  $result = mysql_query("select max(vid) from vdooropen");
  $result = mysql_fetch_row($result);
  $maxvid = $result[0];
  error_log(date("[Y-m-d H:i:s]")." > "."maxvid: ".$maxvid."\n", 3 , "/usr/local/apache2219/logs/php_log");
}

while (1)
{
  $result = mysql_query("select * from vdooropen where vid > $maxvid order by vid limit 1");
  $num = mysql_num_rows($result);
  if ( $num > 0 ) break;
  mysql_query("/*!999999 wait queue msg */ SELECT SLEEP(300)");
  if ( mysql_errno() != 0 ) {
    mysql_close($dblnk);  //必须先关闭
    $dblnk = mysql_connect('localhost:3306', 'root', 'cpyf');
    mysql_select_db('test', $dblnk);
  }
}

$result = mysql_fetch_row($result);

// 返回 JSON 数组
$response = array();
$response['vid'] = $result[0];
$response['msg'] = $result[0].",".$result[1].",".$result[2].",".$result[3].",".$result[4];

$responseText = json_encode($response);
error_log(date("[Y-m-d H:i:s]")." < ".$responseText."\n", 3 , "/usr/local/apache2219/logs/php_log");
echo $responseText;

flush();

 >

当新增加数据后，通过show processlist找到带注释的Sleep线程，并KILL掉，阻塞就会退出。增加数据代码如下：

< php

$dblnk = mysql_connect('localhost:3306', 'root', 'cpyf');
mysql_select_db('test', $dblnk);

mysql_query('insert into vdooropen values(0, 2001, "0863030900020185","20130510-115152","D")');
mysql_query('insert into vdooropen values(0, 2001, "0863030900020185","20130510-115152","D")');
mysql_query('insert into vdooropen values(0, 2001, "0863030900020185","20130510-115152","D")');
mysql_query('insert into vdooropen values(0, 2001, "0863030900020185","20130510-115152","D")');
mysql_query('insert into vdooropen values(0, 2001, "0863030900020185","20130510-115152","D")');

$result = mysql_query("SHOW PROCESSLIST");
$num = mysql_num_rows($result);
for ($i = 0; $i < $num; $i++) {
  $row = mysql_fetch_row($result);
  if ( strstr($row[7], "wait queue msg") != NULL ) {
    echo "KILL ".$row[0] . "\n";
    mysql_query("KILL ".$row[0]);
  }
}

 >