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); 
           
         
           
           关于 LearnKu
                关于 LearnKu
               
                     
                     
                     粤公网安备 44030502004330号
 粤公网安备 44030502004330号 
 
以上问题在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 时存在安全问题,会导致预处理(参数化)失效。