thinkphp6 mysql查询语句对于为null和为空字符串给出特定值处理

一、mysql查询语句对于为null和为空字符串给出特定值处理

• SELECT if(IFNULL(filedName,"指定字符串")="","指定字符串",filedName) '重命名的字符名' FROM tableName;
• 在mysql中：

SELECT `s`.`id`,`s`.`sku`,`s`.`barcode`,`s`.`goods_name`,
     `s`.`short_name`,`unit`,u.serial_number unitNumber,
     gb.serial_number brandNumber,`kind`,c.serial_number 
     cateNumber,`type_name`,`prop_name`,`s`.`status`,`s`.`sale_price`,
     IF (ifnull (brand_name,'') = '', '', brand_name) brand_name 
FROM `t_goods_sku` `s` 
LEFT JOIN `t_goods` `g` ON `s`.`goods_id`=`g`.`id` 
LEFT JOIN `t_goods_category` `c` ON `c`.`id`=`g`.`cate_id` 
LEFT JOIN `t_goods_unit` `u` ON `u`.`id`=`g`.`unit_id` 
LEFT JOIN `t_goods_brand` `gb` ON `gb`.`id`=`g`.`brand_id` 
WHERE ( `s`.`id` IN ('52','341','347') ) AND `s`.`delete_time` = 0

• thinphp6中：

      $info['goodsList'] = $skuObj->alias('s')
        ->field("s.id,s.sku,s.barcode,s.goods_name,s.short_name,unit,
        u.serial_number unitNumber,gb.serial_number brandNumber,
        kind,c.serial_number cateNumber,type_name,prop_name,s.status,s.sale_price")
                ->fieldRaw("IF (ifnull (brand_name,'') = '', '', brand_name) brand_name") // 处理值为null的情况
                ->leftJoin('goods g', "s.goods_id = g.id")
                ->leftJoin("goods_category c", "`c`.`id` = `g`.`cate_id`")
                ->leftJoin("goods_unit u", "`u`.`id` = `g`.`unit_id`")
                ->leftJoin("goods_brand gb", "`gb`.`id` = `g`.`brand_id`")
                ->where('s.id','in',$info['sku_ids'])
                ->select();