mysqlバッチあるテーブルの複数のフィールドをnullに置換する空白列'''
6578 ワード
1.問題の説明
特別な要件のため、テーブルのすべてのフィールドをnullに置き換える必要があります.
2.解決方法
例は次のとおりです.
OK, Enjoy it~
特別な要件のため、テーブルのすべてのフィールドを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~