thinkphp6 mysql查詢語句對于為null和為空字符串給出特定值處理 · php開發筆記

一、mysql查詢語句對于為null和為空字符串給出特定值處理 * **SELECT if(IFNULL(filedName,"指定字符串")="","指定字符串",filedName) '重命名的字符名' FROM tableName;** * 在mysql中： ~~~jsx 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中： ~~~php $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(); ~~~