mysql 批量将某表的多个字段为null的替换为空串''

来源：互联网发布：网络情报收集编辑：程序博客网时间：2024/05/18 09:50

1. 问题描述

由于特殊需求，需要将表的所有字段为null的替换为空串''。

2. 解决方法

示例如下：

UPDATE `tb_blacklist_p2p_fix` SET `name` = (CASE WHEN IFNULL(`name`, '') = '' THEN '' ELSE `name` END),card_num = (CASE WHEN IFNULL(card_num, '') = '' THEN '' ELSE `card_num` END),phone_num = (CASE WHEN IFNULL(phone_num, '') = '' THEN '' ELSE `phone_num` END),email = (CASE WHEN IFNULL(email, '') = '' THEN '' ELSE `email` END),qq_num = (CASE WHEN IFNULL(qq_num, '') = '' THEN '' ELSE `qq_num` END),household_register = (CASE WHEN IFNULL(household_register, '') = '' THEN '' ELSE `household_register` END),company = (CASE WHEN IFNULL(company, '') = '' THEN '' ELSE `company` END),`position` = (CASE WHEN IFNULL(`position`, '') = '' THEN '' ELSE `position` END),loan_amount = (CASE WHEN IFNULL(loan_amount, '') = '' THEN '' ELSE `loan_amount` END),overdue_amount = (CASE WHEN IFNULL(overdue_amount, '') = '' THEN '' ELSE `overdue_amount` END),overdue_day = (CASE WHEN IFNULL(overdue_day, '') = '' THEN '' ELSE `overdue_day` END),paid_amount = (CASE WHEN IFNULL(paid_amount, '') = '' THEN '' ELSE `paid_amount` END),loan_period = (CASE WHEN IFNULL(loan_period, '') = '' THEN '' ELSE `loan_period` END),borrowing_time = (CASE WHEN IFNULL(borrowing_time, '') = '' THEN '' ELSE `borrowing_time` END),info_source = (CASE WHEN IFNULL(info_source, '') = '' THEN '' ELSE `info_source` END)

OK, Enjoy it~

0 0