MySQL: Whole database, convert [empty] values to NULL

I'm working on a database where there is a mix of NULL and '' (empty) values. I would like to co开发者_运维问答nvert all empty values to NULL.

Something like

UPDATE table SET col=NULL WHERE col=''

but for the whole database

---

notes:

• I've changed default values to NULL

• I'm well aware of the NULL vs '' debate, and this isn't something i really want to go into (plenty of other questions on SO for that)

---

There probably is a better solution, but you could always do a mysql dump of the base, and then parse the .sql file, replacing '' with NULL. For parsing, you could use custom made php script, or some other way - that part shouldn't be hard :)

---

This worked for me:

$tables = {array of table names};

foreach($tables as $t){
    $cols = FALSE;
    $q = "SELECT * FROM $t LIMIT 1";
    $res = mysql_query($q);
    while ($r = mysql_fetch_array($res, MYSQL_ASSOC)){    
        $cols = array_keys($r);
    }
    if($cols){
        foreach($cols as $c){
            $q2 = "UPDATE $t SET $c=NULL WHERE $c = ''";
            mysql_query($q2);
            echo mysql_error();
        }
    } 
}

---

The quickest way might just be to dump, search/replace using a text editor, then load.