在 Spatie\QueryBuilder ，请求参数中包含 “,” 报错：SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
1、在 Spatie\QueryBuilder ，请求参数中包含 “,” 报错：SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens (SQL: select count(*) as aggregate from `table` where `table`.`shipping_type` = 2 and `shipping_at_gmt` >= 2024-05-21 08:54:14 and `shipping_at_gmt` <= 2024-08-21 08:54:14 and `table`.`shipping_error_message` in (aaa))。如图1
![在 Spatie\QueryBuilder ，请求参数中包含 "," 报错：SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens (SQL: select count(*) as aggregate from `table` where `table`.`shipping_type` = 2 and `shipping_at_gmt` >= 2024-05-21 08:54:14 and `shipping_at_gmt` <= 2024-08-21 08:54:14 and `table`.`shipping_error_message` in (aaa))。](https://www.shuijingwanwq.com/wp-content/uploads/2024/09/1.png)
图1
图2
3、查看生成的 SQL
1 2 3 4 5 6 7 8 9 | select count(*) as aggregatefrom `table`where `table`.`shipping_type` = 2 and `shipping_at_gmt` >= '2024-05-21 09:00:21' and `shipping_at_gmt` <= '2024-08-21 09:00:21' and `table`.`shipping_error_message` in ('aaa') |
4、打印出处理后的查询和绑定的参数，检查是否所有参数都匹配 SQL 中的占位符。确认占位符 ? 有 3 个，绑定的参数有 4 个。根源在于 “aaa,bbbb” 因为是逗号间隔的缘故，被拆分为了 2 个绑定参数。如图3
图3
1 2 3 | print_r($builder->toSql());print_r($builder->getBindings());exit; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 | select `table`.* from `table` where `shipping_at_gmt` >= ? and `shipping_at_gmt` <= ? and `table`.`shipping_error_message` in (?) order by `operated_at_gmt` descArray( [0] => Illuminate\Support\Carbon Object ( [endOfTime:protected] => [startOfTime:protected] => [constructedObjectId:protected] => 000000000000115d0000000000000000 [localMonthsOverflow:protected] => [localYearsOverflow:protected] => [localStrictModeEnabled:protected] => [localHumanDiffOptions:protected] => [localToStringFormat:protected] => [localSerializer:protected] => [localMacros:protected] => [localGenericMacros:protected] => [localFormatFunction:protected] => [localTranslator:protected] => [dumpProperties:protected] => Array ( [0] => date [1] => timezone_type [2] => timezone ) [dumpLocale:protected] => [dumpDateProperties:protected] => [date] => 2024-05-29 03:03:53.000000 [timezone_type] => 3 [timezone] => UTC ) [1] => Illuminate\Support\Carbon Object ( [endOfTime:protected] => [startOfTime:protected] => [constructedObjectId:protected] => 000000000000115e0000000000000000 [localMonthsOverflow:protected] => [localYearsOverflow:protected] => [localStrictModeEnabled:protected] => [localHumanDiffOptions:protected] => [localToStringFormat:protected] => [localSerializer:protected] => [localMacros:protected] => [localGenericMacros:protected] => [localFormatFunction:protected] => [localTranslator:protected] => [dumpProperties:protected] => Array ( [0] => date [1] => timezone_type [2] => timezone ) [dumpLocale:protected] => [dumpDateProperties:protected] => [date] => 2024-08-29 03:03:53.000000 [timezone_type] => 3 [timezone] => UTC ) [2] => aaa [3] => bbbb) |
5、决定在 Spatie\QueryBuilder 中禁用逗号（,）自动转换为数组的行为。参考：Is it possible to disable a delimiter for an allowed filter? 打印出处理后的查询和绑定的参数，检查是否所有参数都匹配 SQL 中的占位符。确认占位符 ? 有 3 个，绑定的参数有 3 个。符合预期。如图4
图4
1 | $allowedFilters[] = AllowedFilter::exact($field, null, true, false); |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | select `table`.* from `table` where `shipping_at_gmt` >= ? and `shipping_at_gmt` <= ? and `table`.`shipping_error_message` in (?) order by `operated_at_gmt` descArray( [0] => Illuminate\Support\Carbon Object ( [endOfTime:protected] => [startOfTime:protected] => [constructedObjectId:protected] => 000000000000115d0000000000000000 [localMonthsOverflow:protected] => [localYearsOverflow:protected] => [localStrictModeEnabled:protected] => [localHumanDiffOptions:protected] => [localToStringFormat:protected] => [localSerializer:protected] => [localMacros:protected] => [localGenericMacros:protected] => [localFormatFunction:protected] => [localTranslator:protected] => [dumpProperties:protected] => Array ( [0] => date [1] => timezone_type [2] => timezone ) [dumpLocale:protected] => [dumpDateProperties:protected] => [date] => 2024-05-29 05:54:56.000000 [timezone_type] => 3 [timezone] => UTC ) [1] => Illuminate\Support\Carbon Object ( [endOfTime:protected] => [startOfTime:protected] => [constructedObjectId:protected] => 000000000000115e0000000000000000 [localMonthsOverflow:protected] => [localYearsOverflow:protected] => [localStrictModeEnabled:protected] => [localHumanDiffOptions:protected] => [localToStringFormat:protected] => [localSerializer:protected] => [localMacros:protected] => [localGenericMacros:protected] => [localFormatFunction:protected] => [localTranslator:protected] => [dumpProperties:protected] => Array ( [0] => date [1] => timezone_type [2] => timezone ) [dumpLocale:protected] => [dumpDateProperties:protected] => [date] => 2024-08-29 05:54:56.000000 [timezone_type] => 3 [timezone] => UTC ) [2] => aaa,bbbb) |
6、当请求参数 是 “aaa,bbbb”，SQL 不再报错，符合预期。查看生成的 SQL。如图5
图5
1 2 3 4 5 6 7 8 | select count(*) as aggregatefrom `table`where `shipping_at_gmt` >= '2024-05-29 05:57:18' and `shipping_at_gmt` <= '2024-08-29 05:57:18' and `table`.`shipping_error_message` in ('aaa,bbbb') |
近期评论