Laravel ORM where条件查询纯数字字符串类型传给整型字段查询不出数据
Laravel ORM where条件查询,数据表字段是bigint,其中一个传递字符串,导致查询不到数据,实际数据表存在。
CREATE TABLE `account_auth` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`account_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '本系统账号id',
`status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '授权状态,默认1',
`sys_id` int unsigned NOT NULL DEFAULT '1' COMMENT '授权系统ID,默认1,',
`sys_user_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '授权用户id',
`sys_user_name` varchar(45) NOT NULL DEFAULT '' COMMENT '授权用户名',
`created_at` int NOT NULL DEFAULT '0' COMMENT '创建时间',
`updated_at` int NOT NULL DEFAULT '0' COMMENT '更新时间',
`deleted_at` int NOT NULL DEFAULT '0' COMMENT '删除时间',
PRIMARY KEY (`id`),
KEY `idx_account_id` (`account_id`),
KEY `idx_sys_uid` (`sys_user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='授权表';
当sys_user_id传入的值为整型时候,account_id值传入字符串类型一起查询时候,返回结果为0,
#无返回数据
select count(*) as aggregate from fc_account_auth
where (account_id
= ‘50279273105330176’ and sys_user_id
= 4117019518842343);
#有返回数据
select count(*) as aggregate from fc_account_auth
where (account_id
= ‘50279273105330176’ and sys_user_id
= ‘4117019518842343’);
#有返回数据
select count(*) as aggregate from fc_account_auth
where (account_id
= 50279273105330176 and sys_user_id
= 4117019518842343);
#有返回数据
select count(*) as aggregate from fc_account_auth
where (account_id
= 50279273105330176 and sys_user_id
= ‘4117019518842343’);
###PHP代码
$account_id = '5027927310533017';
$sys_user_id = 4117019518842343;
$query = AccountAuthModel::query();
$query->where('sys_user_id', $sys_user_id);
$query->where('account_id', $account_id);
$count =$query->count();
var_dump($count);
以上问题在MySQL5.7中正常,在MySQL8.0.2版本就不行,找了下Laravel单元测试源码,需要设置PDO::ATTR_EMULATE_PREPARES => true,就可以正常查询出来了。
github.com/laravel/framework/blob/...
再贴一个blog供大家学习 www.jianshu.com/p/255f65a3ce35
ATTR_EMULATE_PREPARES 这个参数设置为 true 时存在安全问题,会导致预处理(参数化)失效。