How do I delete blank rows in Mysql?

问题:

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.

回答1:

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.

回答2:

I am doing the mysql operation in command prompt in windows. And the basic queries:

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:

delete * from table_name where column is NULL 

works fine.

回答3:

I have a PHP script that automatically removes empty rows based on column data types.

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.

回答4:

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.

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:

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.