Laravel : Syntax error or access violation: 1055 Error 阿星小栈

 

 /**
     * @param $where
     * @return array
     */
    public static function getOrderTicketsByParamsWhere($where){
        if(empty($where)){
            return $where;
        }
        $orderTicket = OrderTicket::select(
            'th_order_ticket.id as order_ticket_id','th_order_ticket.order_num', 'th_order_ticket.sub_order_status',
            'th_order_ticket.ticket_id'
        )
            ->baseWhere($where)
            ->get()
            ->toArray();
        foreach ($orderTicket as $key=>$value){
            $value= OrderTicket::dealUrlForQuery($value);
            $orderTicket[$key] = $value;
        }
        return $orderTicket;
    }

上面这样一段代码, 测试服务器很好, 上线后报错了.
Syntax error or access violation: 1055 Error : MySQL : isn't in GROUP BY................................

后来Google了一下说是sql_mode设置导致的问题, 修改 config/database.php这个配置 strict => false

 

'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', 'localhost'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix' => '',
    'strict' => false,
    'engine' => null,
],

 

然后试了一下 果然问题解决了 ,然后仔细查阅了一下原因:

查询mysql 1055错误码发现问题为在mysql的配置中如果设置了sql_mode包含
ONLY_FULL_GROUP_BY值得话，在进行查询时需要将select的字段都包含在group by 中。
即 select x,y from xxx group by x,y
否则就会报错

总结:

laravel 5.3 是默认开启 mysql严格模式的.
mysql在严格模式下, 并且开启了ONLY_FULL_GROUP_BY的情况下,
group by 的字段没有出现在 select 的语句中会报错.关闭了严格模式就不会报错.

 

原文：https://segmentfault.com/a/1190000008383053