php - 奇怪的 mySQL 错误代码 1271

Question

我想做一个数据库查询，根据一堆标准计算条目数。这是我到目前为止所拥有的:

MySQL Error
Message: MySQL Query Error
SQL: SELECT COUNT(distinct p0.placeid) FROM uchome_place p0 INNER JOIN uchome_place_hours ph ON (p0.placeid=ph.placeid AND ph.weekdays=DATE_FORMAT(NOW(), "%w") AND ph.start<=TIME_FORMAT(TIMESTAMPADD(HOUR,2,NOW()), "%H:%i:%s") AND ph.end>=TIME_FORMAT( TIMESTAMPADD( MINUTE,(CASE p0.book_period WHEN 1 THEN 60 WHEN 2 THEN 120 WHEN 6 THEN 30 ELSE 15 END) ,NOW() ), "%H:%i:%s") )
Error: Illegal mix of collations for operation '>='
Errno.: 1271

此消息是对我尝试通过 PHP 向 mySQL 服务器提供此查询的响应。然而，当我尝试通过 phpMyAdmin 向同一个 mySQL 服务器提供相同的查询时，查询运行没有错误 as shown in this linked picture .

我做了一些进一步的测试，发现 Illegal mix of collat​​ions 来自:

ph.end>=TIME_FORMAT( TIMESTAMPADD( MINUTE,(CASE book_period WHEN 1 THEN 60 WHEN 2 THEN 120 WHEN 6 THEN 30 ELSE 15 END) ,NOW() ), "%H:%i:%s")

在 phpMyAdmin 查询被视为可运行时，PHP 查询究竟如何被视为非法混合排序规则？我需要 ph.end>=TIME_FORMAT( TIMESTAMPADD( MINUTE,(CASE book_period WHEN 1 THEN 60 WHEN 2 THEN 120 WHEN 6 THEN 30 ELSE 15 END) ,NOW() ), "%H:%i:% s") 查询由 PHP 而不是 phpMyAdmin 提供，所以我需要解释错误是如何在 PHP 而不是在 phpMyAdmin 中发生的，以及如何解决这个问题。

PS:代码块中的查询已根据 Tim Biegeleisen 缩短的请求。原始查询是way longer, as shown in this linked picture .

PPS:添加了 lbu 要求的额外 Material .但是，我不明白他的意思。

<?php
class dbstuff {
    var $querynum = 0;
    var $link;
    var $charset;
    function connect($dbhost, $dbuser, $dbpw, $dbname = '', $pconnect = 0, $halt = TRUE) {
        if($pconnect) {
            if(!$this->link = @mysql_pconnect($dbhost, $dbuser, $dbpw)) {
                $halt && $this->halt('Can not connect to MySQL server');
            }
        }
        else
        {
            if(!$this->link = @mysql_connect($dbhost, $dbuser, $dbpw, 1)) {
                $halt && $this->halt('Can not connect to MySQL server');
            }
        }
        if($this->version() > '4.1')
        {
            if($this->charset) {
                @mysql_query("SET character_set_connection=".$this->charset.", character_set_results=".$this->charset.", character_set_client=binary", $this->link);
            }
            if($this->version() > '5.0.1') {
                @mysql_query("SET sql_mode=''", $this->link);
            }
        }
        if($dbname) {
            @mysql_select_db($dbname, $this->link);
        }
    }
    function query($sql, $type = '') {
        $func = ($type=='UNBUFFERED' && function_exists('mysql_unbuffered_query')) ?'mysql_unbuffered_query':'mysql_query';
        if(!($query = $func($sql, $this->link)) && $type != 'SILENT') {
            $this->halt('MySQL Query Error', $sql);
        }
        $this->querynum++;
        return $query;
    }
    function error() {
        return (($this->link) ? mysql_error($this->link) : mysql_error());
    }
    function errno() {
        return intval(($this->link) ? mysql_errno($this->link) : mysql_errno());
    }
    function fetch_row($query) {
        $query = mysql_fetch_row($query);
        return $query;
    }
    function version() {
        return mysql_get_server_info($this->link);
    }
    function close() {
        return mysql_close($this->link);
    }
    function halt($message = '', $sql = '') {
        $dberror = $this->error();
        $dberrno = $this->errno();
        echo "<div style=\"position:absolute;font-size:11px;font-family:verdana,arial;background:#EBEBEB;padding:0.5em;\">
            <b>MySQL Error</b><br>
            <b>Message</b>: $message<br>
            <b>SQL</b>: $sql<br>
            <b>Error</b>: $dberror<br>
            <b>Errno.</b>: $dberrno<br>
            </div>";
        exit();
    }
}
error_reporting(7);
set_magic_quotes_runtime(0);
$_SGLOBAL=array();
if(empty($_SGLOBAL['db'])){
    $_SGLOBAL['db']=new dbstuff;
    $_SGLOBAL['db']->charset='utf8mb4';
    $_SGLOBAL['db']->connect('localhost',$username,$password,$dbname);
}
$fullsql="SELECT COUNT(distinct p0.placeid) FROM uchome_place p0 INNER JOIN uchome_place_hours ph ON (p0.placeid=ph.placeid AND ph.weekdays=DATE_FORMAT(NOW(), \"%w\") AND ph.start<=TIME_FORMAT(TIMESTAMPADD(HOUR,2,NOW()), \"%H:%i:%s\") AND ph.end>=TIME_FORMAT( TIMESTAMPADD( MINUTE,(CASE book_period WHEN 1 THEN 60 WHEN 2 THEN 120 WHEN 6 THEN 30 ELSE 15 END) ,NOW() ), \"%H:%i:%s\") )";
$res=$_SGLOBAL['db']->query($fullsql);
if(false===$res)
{
    $place_list=false;
}
else
{
    $row=$_SGLOBAL['db']->fetch_row($res);
    if(false===$row)
    {
        $place_list='';
    }
    else
    {
        $place_list=$row[0];
    }
}
$_SGLOBAL['db']->close();
die(json_encode($place_list));
?>

Answer 1

最后，我自己解决了这个问题。感谢rkosegi获取提示。

具体来说，我的代码中的问题是我的数据库的 collat​​ion_connection 没有明确定义。 Which means the DB uses its default value, latin1, in the CASE statement's numbers.由于 CASEd 到的列 book_period 在 utf8_general_ci 中整理，这会导致 PHP-fed MySQL 语句出现错误 1271。

解决方案代码:

<?php
class dbstuff {
    var $querynum = 0;
    var $kink;
    var $charset;
    function connect($dbhost, $dbuser, $dbpw, $dbname, $halt=true){
        if(!$this->kink = @mysqli_connect($dbhost, $dbuser, $dbpw, $dbname)){
            $halt && $this->halt('Can not connect to MySQL server');
        }
        if($this->version() > '4.1')
        {
            if($this->charset) {
                @mysqli_query($this->kink,"SET character_set_connection=".$this->charset.",collation_connection=".$this->charset.", character_set_results=".$this->charset.", character_set_client=binary");
                #Question's faulty SQL below. Notice the missing collation_connection
                #mysqli_query($this->kink,"SET character_set_connection=".$this->charset.", character_set_results=".$this->charset.", character_set_client=binary");
            }
            if($this->version() > '5.0.1') {
                @mysql_query($this->kink,"SET sql_mode=''");
            }
        }
    }
...#rest of the code are unchanged
}
...
?>