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开发者_Go百科 phpmyadmin? Manually deleting is a tedious task.

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.

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.

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.

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');

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.