javascript - MySQL 首次运行时仅收到 30 条消息

Question

我有一个 php 聊天室代码，适用于长轮询解决方案:

这是一些代码，我 trim 了一些界面脚本:

客户端:

update_messages: function(){
    jQuery.post(quick_chat.ajaxurl, {
            action: 'quick-chat-ajax-update-messages',
            last_timestamp: quick_chat.last_timestamp,
            rooms: quick_chat.rooms,
            counter: quick_chat.updateMessageCounter
        },
        function(data) {
           if(data.success == 1) {
                var updates = data.messages;
                var main_container = jQuery('#dChatContainer > div.quick-chat-container');
                var chat_id = main_container.attr('data-quick-chat-id');
                var room_name = quick_chat.data[chat_id]['room_name'];
                var history_container = main_container.find('.quick-chat-history-container');

                for(var i=0;typeof(updates[i])!='undefined';i++){
                    if(room_name == updates[i].room) {
                       jQuery(history_container).prepend(quick_chat.single_message_html(updates[i], false));
                    }
                }


                quick_chat.last_timestamp = updates[updates.length-1].unix_timestamp;
            }
        }, 'json'
    );
}

和服务器端:

public function update_messages_ajax_handler(){
    global $wpdb;
    $quick_chat_messages_table_name = $wpdb->prefix . 'quick_chat_messages';

    ob_start();
    header("Content-Type: application/json");
    header("Cache-Control: no-cache, must-revalidate");
    header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");

    $rooms = implode('", "', esc_sql((array)$_POST['rooms']));
    $counter = $_POST['counter'];
    $startTime = time();
    while((time()-$startTime)<=20){
        $sql = "
SELECT id
     , wpid
     , room
     , timestamp
     , UNIX_TIMESTAMP(timestamp) unix_timestamp
     , alias
     , status
     , message
     , color 
  FROM $quick_chat_messages_table_name
 WHERE room IN ('$rooms') 
   AND timestamp > FROM_UNIXTIME(".esc_sql($_POST['last_timestamp']).")
 ORDER 
    BY unix_timestamp ASC;
    ";



        $messages = $wpdb->get_results($sql);
        if($messages) {
            foreach($messages as $v){
                $v->timestring = date_i18n($this->date_format.' - '.$this->time_format, $v->unix_timestamp+$this->gmt_offset);
                $v->message = convert_smilies( $v->message );
            }
            $response = json_encode(array('no_participation' => $this->no_participation, 'success'=> 1, 'messages'=>$messages, 'counter'=>$counter));

            echo $response;
            ob_flush(); flush();
            exit;
        } else {
            sleep($this->options['timeout_refresh_messages']);
        }
    }

    $response = json_encode(array('no_participation' => $this->no_participation, 'success'=> 0, 'counter'=>$counter));

    echo $response;
    ob_flush(); flush();
    exit;
}

现在，在第一次运行(加载时)时，我只想获取最后 30 条消息，然后开始轮询。我尝试限制查询，但代码只是交互。

如何重新创建此内容，仅获取 30 条消息，然后获取所有新消息(无限制)

Answer 1

考虑以下示例...

SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

假设我们想要获取前 3 个值，以及所有剩余的偶数...

SELECT *  
  FROM 
     ( SELECT i FROM ints ORDER BY i LIMIT 3 )a 
 UNION 
     ( SELECT i FROM ints WHERE MOD(i,2) = 0);
 +---+
 | i |
 +---+
 | 0 |
 | 1 |
 | 2 |
 | 4 |
 | 6 |
 | 8 |
 +---+

注意别名是否存在。这很重要，但(我认为)违反直觉。