如何将所有空字符串转换为空值?

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:

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

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?

但这很乏味而且容易出错。必须有办法在整个桌子上做这个?

5 个解决方案

#1

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.

这仍然是手工完成的，但是比您所拥有的要简单一些，因为它不需要您为每个列发送查询。除非您想要解决编写脚本的麻烦，否则您将不得不忍受一定程度的痛苦。

Edit: Added the ENDs

编辑:添加了结束

#2

One possible script:

一个可能的脚本:

for col in $(echo "select column_name from information_schema.columns
where table_name='$TABLE'"|mysql --skip-column-names $DB)
do
echo update $TABLE set $col = NULL where $col = \'\'\;
done|mysql $DB

#3

For newbies, you may still need more work after seeing the above answers. And it's not realistic to type thousands lines. So here I provide a complete working code to let you avoid syntax errors etc.

对于新手来说，在看到上面的答案之后，你可能还需要更多的工作。输入数千行是不现实的。这里我提供了一个完整的工作代码，可以让您避免语法错误等等。

DROP PROCEDURE IF EXISTS processallcolumns;

DELIMITER $$

CREATE PROCEDURE processallcolumns ()
BEGIN

  DECLARE i,num_rows INT ;
  DECLARE col_name char(250);

  DECLARE col_names CURSOR FOR
  SELECT column_name
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'PROCESSINGTABLE'
  ORDER BY ordinal_position;

  OPEN col_names ;
  select FOUND_ROWS() into num_rows;

  SET i = 1;
  the_loop: LOOP

     IF i > num_rows THEN
          CLOSE col_names;
          LEAVE the_loop;
      END IF;


      FETCH col_names 
      INTO col_name;     


      SET @command_text = CONCAT('UPDATE `PROCESSINGTABLE` SET ', col_name, '= IF(LENGTH(', col_name, ')=0, NULL,', col_name, ') WHERE 1 ;' ) ;

--      UPDATE `PROCESSINGTABLE` SET col_name=IF(LENGTH(col_name)=0,NULL,col_name) WHERE 1;
--      This won't work, because MySQL doesn't take varibles as column name.

      PREPARE stmt FROM @command_text ;
      EXECUTE stmt ;

      SET i = i + 1;  
  END LOOP the_loop ;



END$$
DELIMITER ;

call processallcolumns ();
DROP PROCEDURE processallcolumns;

#4

There isn't a standard way - but you can interrogate the system catalog to get the relevant column names for the relevant table and generate the SQL to do it. You can also probably use a CASE expression to handle all the columns in a single pass - a bigger SQL statement.

没有一种标准的方法——但是您可以查询系统编目，以获取相关表的相关列名，并生成SQL来实现这一点。您也可以使用CASE表达式来处理一次传递中的所有列——一个更大的SQL语句。

UPDATE Table
   SET Column1 = CASE Column1 = ' ' THEN NULL ELSE Column1 END,
       ...

Note that once you've generated the big UPDATE statement, all the work is done down in the server. This is much more efficient than selecting data to the client application, changing it there, and writing the result back to the database.

注意，一旦您生成了大的UPDATE语句，所有的工作都将在服务器中完成。这比向客户端应用程序选择数据、更改数据并将结果写入数据库要高效得多。

#5

I think you'll need to pull each row into a language like C#, php, etc.

我认为您需要将每一行都转换为c#、php等语言。

Something like:

喜欢的东西:

rows = get-data()
foreach row in rows
    foreach col in row.cols
        if col == ''
            col = null
        end if
    next
next
save-data()

#1

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