环境WAMP
thinkphp6
mysql5.6升级到5.7之后group会报错
一、更改my.ini的sql_model(不推荐)
二、临时更改
$sql="set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'";
Db::query($sql);
在查询完数据后要改回来
其实不改也行,不改的话相当于在这次链接中全局更改了sql_model,重启后还是会变回来
$sql="set @@global.sql_mode ='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'";
Db::query($sql);
三、使用any_value
->field(any_value('字段1'),any_value('字段2'),'group字段不需要')
补充分组:
mysql的group by 查询不能把分组的全部数据展示出来,用group_concat的话个人感觉数据会相对变的更好处理
mysql> select merchants_province,merchants_id from admin_merchants;
+--------------------+--------------+
| merchants_province | merchants_id |
+--------------------+--------------+
| hn | 4 |
| hn | 5 |
| hn | 8 |
| hn | 9 |
| hn | 12 |
| bj | 13 |
| hn | 14 |
| hn | 15 |
| hn | 16 |
| hn | 17 |
| hn | 18 |
| hn | 19 |
+--------------------+--------------+
12 rows in set (0.04 sec)
mysql> select merchants_province,group_concat(merchants_id) from admin_merchants group by merchants_province;
+--------------------+------------------------------+
| merchants_province | group_concat(merchants_id) |
+--------------------+------------------------------+
| bj | 13 |
| hn | 4,5,8,9,12,14,15,16,17,18,19 |
+--------------------+------------------------------+
2 rows in set (0.03 sec)
group_concat可以多个字段
mysql> select merchants_province,group_concat(merchants_id,':',merchants_number) from admin_merchants group by merchants_province;
+--------------------+---------------------------------------------------------------------------------------------------------------------------------+
| merchants_province | group_concat(merchants_id,':',merchants_number) |
+--------------------+---------------------------------------------------------------------------------------------------------------------------------+
| bj | 13:15666253 |
| hn | 4:11111111,5:21212121,8:21888811,9:55668811,12:15586253,14:55943354,15:55943355,16:55943356,17:55943357,18:55943358,19:55943359 |
+--------------------+---------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.04 sec)
个人感觉数据会相对好处理一些,只做记录使用(我记得我之前写过可以直接查出数据的,但是时间长给忘了,有大佬知道的话希望点醒一下)