MySQL 如何删除Mysql中的空白行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4633321/
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 delete blank rows in Mysql?
提问by Jose H d
I do have a table with more than 100000 data elements, but there are almost 350 blank rows within. How do I delete this blank rows using phpmyadmin? Manually deleting is a tedious task.
我确实有一个包含超过 100000 个数据元素的表格，但其中有近 350 个空白行。如何使用 phpmyadmin 删除此空白行？手动删除是一项繁琐的任务。
回答by Spiny Norman
The general answer is:
一般的答案是：
DELETE FROM table_name WHERE some_column = '';
or
或者
DELETE FROM table_name WHERE some_column IS NULL;
See: http://dev.mysql.com/doc/refman/5.0/en/delete.html
见：http: //dev.mysql.com/doc/refman/5.0/en/delete.html
More info when you post your tables!~
发布表格时会提供更多信息！~
Also, be sure to do:
另外，一定要做到：
SELECT * FROM table_name WHERE some_column = '';
before you delete, so you can see which rows you are deleting! I think in phpMyAdmin you can even just do the select and then "select all" and delete, but I'm not sure. This would be pretty fast, and very safe.
在删除之前，这样您就可以看到要删除的行！我认为在 phpMyAdmin 中，您甚至可以只进行选择，然后“全选”并删除，但我不确定。这会非常快，而且非常安全。
回答by NCA
I am doing the mysqloperation in command prompt in windows. And the basic queries:
我mysql在 Windows 的命令提示符下进行操作。以及基本查询：
delete * from table_name where column=''
and
和
delete * from table_name where column='NULL'
doesn't work. I don't know whether it works in phpmyadminsqlcommand builder. Anyway:
不起作用。我不知道它是否适用于phpmyadminsqlcommand builder。反正：
delete * from table_name where column is NULL
works fine.
工作正常。
回答by Buttle Butkus
I have a PHP script that automatically removes empty rows based on column data types.
我有一个 PHP 脚本，可以根据列数据类型自动删除空行。
That allows me to define "emptiness" differently for different column types.
这允许我为不同的列类型以不同的方式定义“空”。
e.g.
例如
table
first_name (varchar) | last_name (varchar) | some_qty ( int ) | other_qty (decimal)
DELETE FROM `table` WHERE
(`first_name` IS NULL OR `first_name` = '')
AND
(`last_name` IS NULL OR `last_name` = '')
AND
(`some_qty` IS NULL OR `some_qty` = 0)
AND
(`other_qty` IS NULL OR `other_qty` = 0)
Since "0" values are meaningless in my system, I count them as empty. But I found out that if you do (first_name= 0) then you will always get true, because strings always == 0 in MySQL. So I tailor the definition of "empty" to the data type.
由于“0”值在我的系统中毫无意义，因此我将它们视为空值。但我发现如果你这样做 ( first_name= 0) 那么你总是会得到正确的，因为在 MySQL 中字符串总是 == 0。所以我根据数据类型定制了“空”的定义。
回答by STWilson
This procedure will delete any row for all columns that are null ignoring the primary column that may be set as an ID. I hope it helps you.
此过程将删除所有空列的任何行，忽略可能设置为 ID 的主列。我希望它能帮助你。
DELIMITER //
CREATE PROCEDURE DeleteRowsAllColNull(IN tbl VARCHAR(64))
BEGIN
SET @tbl = tbl;
SET SESSION group_concat_max_len = 1000000;
SELECT CONCAT('DELETE FROM `',@tbl,'` WHERE ',(REPLACE(group_concat(concat('`',COLUMN_NAME, '` is NULL')),',',' AND ')),';') FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = @tbl AND COLUMN_KEY NOT LIKE 'PRI' into @delete_all;
PREPARE delete_all FROM @delete_all;
EXECUTE delete_all;
DEALLOCATE PREPARE delete_all;
END //
DELIMITER ;
Execute the procedure like this.
执行这样的程序。
CALL DeleteRowsAllColNull('your table');
回答by Thomas Williams
I know this has already been answered and has got a tick, but I wrote a small function for doing this, and thought it might be useful to other people.
我知道这已经得到了回答并得到了一个勾号，但我为此编写了一个小函数，并认为它可能对其他人有用。
I call my function with an array so that I can use the same function for different tables.
我用数组调用我的函数，以便我可以对不同的表使用相同的函数。
$tableArray=array("Address", "Email", "Phone"); //This is the column names
$this->deleteBlankLines("tableName",$tableArray);
and here is the function which takes the array and builds the delete string
这是获取数组并构建删除字符串的函数
private function deleteBlankLines($tablename,$columnArray){
$Where="";
foreach($columnArray as $line):
$Where.="(`".$line."`=''||`".$line."` IS NULL) && ";
endforeach;
$Where = rtrim($Where, '&& ');
$query="DELETE FROM `{$tablename}` WHERE ".$Where;
$stmt = $this->db->prepare($query);
$stmt->execute();
}
You can use this function for multiple tables. You just need to send in a different table name and array and it will work.
您可以将此功能用于多个表。您只需要发送不同的表名和数组，它就会起作用。
My function will check for a whole row of empty columns or NULL columns at the same time. If you don't need it to check for NULL then you can remove that part.
我的函数将同时检查一整行空列或 NULL 列。如果您不需要它来检查 NULL，那么您可以删除该部分。
