代码之家 › 专栏 › 技术社区 › Kyle West

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

sql-update string sql mysql

Kyle West · 技术社区 · 16 年前

我有一个具有大约100列(90%可以为空)的遗留表。在这90列中,我希望删除所有空字符串并将其设置为空。我知道我可以:

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

但这很乏味而且容易出错。在整张桌子上都必须有这样的方法吗?

6 回复 | 直到 7 年前

Hammerite 16 年前

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

这仍然是手动执行的,但比您所拥有的要简单一些,因为它不要求您为每一列发送查询。除非你想麻烦编写脚本,否则在做类似的事情时,你必须忍受一定的痛苦。

编辑:添加 END S

Matthew Flaschen 16 年前

一个可能的脚本:

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

tomriddle_1234 13 年前

对于新手,在看到上述答案后,您可能仍然需要更多的工作。打几千行是不现实的。所以在这里我提供了一个完整的工作代码,让您避免语法错误等。

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;

Jonathan Leffler 16 年前

没有标准的方法-但是您可以询问系统目录,以获取相关表的相关列名称,并生成SQL来执行此操作。您也可以使用case表达式来处理一次传递中的所有列——一个更大的SQL语句。

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

注意,一旦生成了大更新语句,所有的工作都将在服务器中完成。这比将数据选择到客户机应用程序、在那里更改数据以及将结果写回数据库要高效得多。

Nate CSS Guy 16 年前

我认为您需要将每一行都转换成C、PHP等语言。

类似:

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

Nae 7 年前

您可以编写一个简单的函数并将列传递给它:

用途:

SELECT
  fn_nullify_if_empty(PotentiallyEmptyString)
FROM
  table_name
;

实施:

DELIMITER $$
CREATE FUNCTION fn_nullify_if_empty(in_string VARCHAR(255))
  RETURNS VARCHAR(255)
  BEGIN
    IF in_string = ''
      THEN RETURN NULL;
      ELSE RETURN in_string;
    END IF;
  END $$
DELIMITER ;