MySQL loop through every field of every table

I'm working on a database migration and I need a procedure or code to loop through every field of every table in a database.

Basically the old database has values as 'NULL' in millions of fields, and I need to update those values with NULL.

The basic query I need is:

UPDATE table SET field = NULL WHERE TRIM(field) = 'NULL';

The problem is that there are hundreds of tables and thousands of columns and manually querying them would take far too long. I need to loop through the tables and run the query on each row in each table.

Does anyone know how to write a query or SP that will do this? The DB is too big to perform this on an application level, so it must be done directly on the database.

 

Try this:

SELECT CONCAT ( 'UPDATE ', TABLE_NAME, ' SET ', COLUMN_NAME, ' = NULL WHERE TRIM(', COLUMN_NAME, ') = \'NULL\';' ) FROM INFORMATION_SCHEMA.COLUMNS

Should give you a list of commands which you can capture in a text file and then play back to the MySQL console.

 

Looks ok. Still a little manual step but with a good GUI, this works great.
For anyone needing to do this, here's exactly what worked.

//WHILE CONNECTED TO INFORMATION_SCHEMA...

SELECT(
CONCAT('UPDATE `', TABLE_NAME, '` SET `', COLUMN_NAME, '` = NULL WHERE TRIM(`', COLUMN_NAME, '`) = \'NULL\';' ))
FROM `COLUMNS` 
WHERE TABLE_SCHEMA = 'your_db_name'

Code (markup):

This will then generate a list of all the actual queries. You will then need to copy all of those queries, and apply them to the actual database you need to strip from. Using a good GUI makes this really easy. A SSH terminal would work fine as well.

 

Can anyone tell me what is the best database in the world
MySQL or what?

And also tell me what is the nested tables and can we make those in mysql?

 

Horses for courses. MySQL is pretty much the first choice for web applications, being the 'M' in LAMP (Linux, Apache, MySQL and PHP). And being free helps its popularity, of course. But it's not normally considered suitable for enterprise-level applications, as its functionality is limited compared to Microsoft SQL Server or the '800-pound gorilla,' Oracle.