关于Mysql查询json数据时的一个问题

最新推荐文章于 2022-10-10 17:58:01 发布

youcijibi

最新推荐文章于 2022-10-10 17:58:01 发布

阅读量480

点赞数

分类专栏：每日总结 php

本文链接：https://blog.csdn.net/youcijibi/article/details/106717650

版权

每日总结同时被 2 个专栏收录

412 篇文章 5 订阅

订阅专栏

php

232 篇文章 0 订阅

订阅专栏

注意数字也是用引号引起来的，也就是说是字符串类型，所以在查询时的语句中必须也是字符串类型，不然会出现时而能查到时而不能查到的情况。

sql:

SELECT * FROM `offcn_jdexpress_price` WHERE (json_extract(receive_address,"$.province")='21') AND (json_extract(receive_address,"$.city")='23') AND (json_extract(receive_address,"$.district")='0') AND (json_extract(send_address,"$.province")='1') AND (json_extract(send_address,"$.city")='4') AND (json_extract(send_address,"$.district")='0')

Yii2的查询：

      $jdPrice = JdpriceModel::find()->where([
                    'json_extract(receive_address,"$.province")' => (string)$receiveAddress['province'],
                    'json_extract(receive_address,"$.city")' => (string)$receiveAddress['city'],
                    'json_extract(receive_address,"$.district")' => (string)$receiveAddress['district'],
                    'json_extract(send_address,"$.province")' => (string)$sendAddress['province'],
                    'json_extract(send_address,"$.city")' => (string)$sendAddress['city'],
                    'json_extract(send_address,"$.district")' => (string)$sendAddress['district'],
                ])->one();

规避这个就是需要存储的时候记得数字转为int。