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

最新推荐文章于 2023-04-26 11:18:11 发布

zhuxiongxian

最新推荐文章于 2023-04-26 11:18:11 发布

阅读量5.4k

点赞数

分类专栏： mysql 文章标签： mysql null 空串

本文链接：https://blog.csdn.net/cryhelyxx/article/details/70255975

版权

mysql 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

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)