如何将表中的所有空字符串更改为 NULL?

2022-01-17 00:00:00 string sql-update sql mysql

我有一个包含大约 100 列的旧表(90% 可以为空).在这 90 列中，我想删除所有空字符串并将它们设置为 null.我知道我可以:

I have a legacy table with about 100 columns (90% nullable). In those 90 columns I want to remove all empty strings and set them to null. I know I can:

update table set column = NULL where column = '';
update table set column2 = NULL where column2 = '';

但这很乏味且容易出错.必须有一种方法可以在整个桌子上做到这一点?

But that is tedious and error prone. There has to be a way to do this on the whole table?

推荐答案

UPDATE
    TableName
SET
    column01 = CASE column01 WHEN '' THEN NULL ELSE column01 END,
    column02 = CASE column02 WHEN '' THEN NULL ELSE column02 END,
    column03 = CASE column03 WHEN '' THEN NULL ELSE column03 END,
    ...,
    column99 = CASE column99 WHEN '' THEN NULL ELSE column99 END

这仍然是手动执行的，但比您所拥有的痛苦要小一些，因为它不需要您为每一列发送查询.除非您想麻烦编写脚本，否则在执行此类操作时将不得不忍受一定程度的痛苦.

This is still doing it manually, but is slightly less painful than what you have because it doesn't require you to send a query for each and every column. Unless you want to go to the trouble of scripting it, you will have to put up with a certain amount of pain when doing something like this.

添加了 ENDs

相关文章