Laravel使用遇到问题记录二

1、更新Cache值

直接用存储缓存数据的方法add(), put(), forever()即可

2、sql参数绑定

• ?方式绑定

$sql = <<<SQL
  UPDATE `unit` SET upunitcode=?, `unitcode` = CONCAT(?, RIGHT(unitcode, char_length(unitcode)-char_length(?))) WHERE FIND_IN_SET(unitcode, getChildList(?, 0)) AND LEFT(unitcode, char_length(?)) = ?
SQL;
DB::update($sql, [
    $newunitcode,
    $newunitcode,
    $oldunitcode,
    $oldunitcode,
    $oldunitcode,
    $oldunitcode,
]);

• :param方式绑定

该方式必须每个参数对应一个，即使某些参数指向的值是一样的，下面正确的代码中，如果将:newunitcode*改为:newunitcode，:oldunitcode*改为:oldunitcode，然后参数绑定的时候

DB::update($sql, [
    'newunitcode' => $newunitcode,
    'oldunitcode' => $oldunitcode,
]);

将会报错,下面是正确的代码：

$sql = <<<SQL
  UPDATE `unit` SET upunitcode=:newunitcodeA, `unitcode` = CONCAT(:newunitcodeB, RIGHT(unitcode, char_length(unitcode)-char_length(:oldunitcodeA))) WHERE FIND_IN_SET(unitcode, getChildList(:oldunitcodeB, 0)) AND LEFT(unitcode, char_length(:oldunitcodeC)) = :oldunitcodeD
SQL;
DB::update($sql, [
    'newunitcodeA' => $newunitcode,
    'newunitcodeB' => $newunitcode,
    'oldunitcodeA' => $oldunitcode,
    'oldunitcodeB' => $oldunitcode,
    'oldunitcodeC' => $oldunitcode,
    'oldunitcodeD' => $oldunitcode,
]);

3、group_by错误

---视图代码
select `p`.`uuid` AS `uuid`,`p`.`name1` AS `name1`,`p`.`e_date` AS `e_date`,`p`.`childnum` AS `childnum`,`p`.`unitcode` AS `unitcode`,`m`.`puuid` AS `puuid`,`m`.`marrow` AS `marrow`,`m`.`marrowdate` AS `marrowdate`,`m`.`marrowunit` AS `marrowunit`,`j`.`f_date` AS `f_date` from (((select `persons`.`uuid` AS `uuid`,`persons`.`name1` AS `name1`,`persons`.`e_date` AS `e_date`,`persons`.`childnum` AS `childnum`,`persons`.`unitcode` AS `unitcode` from `persons` where ((`persons`.`sex` = '01') and (`persons`.`childnum` >= 1) and (`persons`.`marry` between '21' and '23') and isnull(`persons`.`deleted_at`))) `p` join (select `marrows`.`puuid` AS `puuid`,`marrows`.`marrow` AS `marrow`,`marrows`.`marrowdate` AS `marrowdate`,`marrows`.`marrowunit` AS `marrowunit` from `marrows` where ((`marrows`.`because` between '21' and '23') and isnull(`marrows`.`deleted_at`))) `m` on((`p`.`uuid` = `m`.`puuid`))) left join (select `j`.`f_date` AS `f_date`,`j`.`uuid` AS `uuid`,`j`.`puuid` AS `puuid` from (`jshds` `j` join (select max(`jshds`.`f_date`) AS `fdate`,`jshds`.`uuid` AS `uuid`,`jshds`.`puuid` AS `puuid` from `jshds` where isnull(`jshds`.`deleted_at`) group by `jshds`.`puuid`) `js` on((`j`.`uuid` = `js`.`uuid`)))) `j` on((`p`.`uuid` = `j`.`puuid`)))

Laravel5.6执行上面代码的时候发生错误：

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'populac.jshds.uuid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

错误原因是ONLY_FULL_GROUP_BY模式是不允许select max(a), b, c from tb group by b,只允许select max(a), b from tb group by b

解决方式，修改config/database.php中mysql配置项modes

//...
    'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_0900_ai_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
        'modes' => [
            //'ONLY_FULL_GROUP_BY',
            'STRICT_TRANS_TABLES',
            'NO_ZERO_IN_DATE',
            'NO_ZERO_DATE',
            'ERROR_FOR_DIVISION_BY_ZERO',
            'NO_ENGINE_SUBSTITUTION',
        ],
    ],

• laracasts.com/discuss/cha…