MySQL 如何将表中的所有空字符串更改为 NULL？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3238319/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I change all empty strings to NULL in a table?
提问by Kyle West
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?
但这既乏味又容易出错。必须有一种方法可以在整个桌子上做到这一点？
回答by Hammerite
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
编辑：添加了ENDs
回答by Matthew Flaschen
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
回答by tomriddle_1234
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;
回答by Jonathan Leffler
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 语句，所有工作都在服务器中完成。这比向客户端应用程序选择数据、在那里更改数据并将结果写回数据库要高效得多。
回答by Nate
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()
回答by Nae
You could write a simple function and pass your columns to it:
您可以编写一个简单的函数并将您的列传递给它：
Usage:
用法：
SELECT
fn_nullify_if_empty(PotentiallyEmptyString)
FROM
table_name
;
Implementation:
执行：
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 ;
