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删除此空白行?手动删除是一项繁琐的工作。
5 个解决方案
#1
25
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
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你甚至可以选择然后“全选”并删除,但我不确定。这将非常快,而且非常安全。
#2
4
I am doing the mysql
operation in command prompt in windows. And the basic queries:
我正在windows中的命令提示符下进行mysql操作。而基本的查询:
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 phpmyadmin
sqlcommand builder. Anyway:
不起作用。我不知道它是否在phpmyadmin sqlcommand builder中有效。无论如何:
delete * from table_name where column is NULL
works fine.
#3
3
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。所以我为数据类型定制了“empty”的定义。
#4
2
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.
此过程将删除所有列为null的行,而忽略可能设置为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');
#5
1
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,那么您可以删除该部分。
#1
25
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
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你甚至可以选择然后“全选”并删除,但我不确定。这将非常快,而且非常安全。
#2
4
I am doing the mysql
operation in command prompt in windows. And the basic queries:
我正在windows中的命令提示符下进行mysql操作。而基本的查询:
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 phpmyadmin
sqlcommand builder. Anyway:
不起作用。我不知道它是否在phpmyadmin sqlcommand builder中有效。无论如何:
delete * from table_name where column is NULL
works fine.
#3
3
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。所以我为数据类型定制了“empty”的定义。
#4
2
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.
此过程将删除所有列为null的行,而忽略可能设置为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');
#5
1
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,那么您可以删除该部分。