Laravel 5.2查询在列上构建JSON [英] Laravel 5.2 query building for JSON on columns
问题描述
我正在使用MySql 5.7.14和laravel 5.2.
我的表用户" JSON如下所示:
I am using MySql 5.7.14 and laravel 5.2.
My Table 'user' JSON look like this:
[{
"id": "1",
"options": "{\"religion\": \"R'hllor, the Lord of Light\", \"favorite_color\": \"red\"}"
}]
当我通过laravel查询为:
when i query through the laravel as:
$ii =\DB::table('user')->where('options->favorite_color', 'red')->get();
我正面临错误
SQLSTATE[HY000]: General error: 2036 (SQL: select * from `user` where `options`->"$.favorite_color" = red)
当我运行与
SELECT * FROM `user` WHERE options->"$.favorite_color"='red'
在phpmyadmin中它没有给出任何错误,它工作正常. 谁能告诉我可能是什么问题? 提前致谢.
in phpmyadmin its not giving any error it working fine. Can anyone tell me what may be the issue? Thanks in advance.
推荐答案
我找到了解决方案. 要使用laravel 5.2与MySQL 5.7.8及更高版本一起使用,需要将PDO选项添加到config/database.php中的连接中,如下所示.
I got the solution. To work with MySQL 5.7.8 and above using laravel 5.2 need to add the PDO options to the your connections in config/database.php as follows.
'mysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', 'localhost'),
'database' => env('DB_DATABASE', 'db_name'),
'username' => env('DB_USERNAME', 'root'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
'options' => array(
PDO::ATTR_CASE => PDO::CASE_LOWER,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,
PDO::ATTR_STRINGIFY_FETCHES => true,
PDO::ATTR_EMULATE_PREPARES => true,
),
],
,然后运行php artisan config:cache. 它运作良好. vendor/laravel/framework/src/illuminate/Database/Connectors/Connector.php PDO选项就像
and then run php artisan config:cache. Its works well. vendor/laravel/framework/src/illuminate/Database/Connectors/Connector.php PDO Options are like
protected $options = [
PDO::ATTR_CASE => PDO::CASE_NATURAL,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,
PDO::ATTR_STRINGIFY_FETCHES => false,
PDO::ATTR_EMULATE_PREPARES => false,
];
PDO :: ATTR_STRINGIFY_FETCHES& PDO :: ATTR_EMULATE_PREPARES需要在database.php中覆盖.
PDO::ATTR_STRINGIFY_FETCHES & PDO::ATTR_EMULATE_PREPARES Need To Be Overwritten in database.php.
这篇关于Laravel 5.2查询在列上构建JSON的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!