Common_schema: repetitive tasks with foreach

Posted by in MariaDB, MySQL

Although we have spoken several times of this framework MariaDB MySQL-today a new feature, I bring very useful when you need to perform repetitive tasks based on consultations. It is used foreach call. Here I present a practical example of how you’ve used to perform a specific task: to replace all null to zero in a table with a lot of fields values.

Locating the table fields

The best way of locating the fields that will be used in our task is by using the information_schema, an internal database needed forMySQL.

Knowing that the database is called inst7 and the table datos, the query to be used is this one:

SELECT c.column_name FROM information_schema.`COLUMNS` c
WHERE TABLE_SCHEMA = “inst7” and TABLE_NAME = “datos” ORDER BY c.column_name

And this is the outcome of the sentence:

common_schema foreach

common_schema foreach

Now we have all the fields ready to be modified from nulls values to zero. The SQL sentence will be something like this:

UPDATE datos SET campo = 0 WHERE campo is null

Using foreach

The documentation for foreach can be found here: http://code.openark.org/blog/mysql/mysql-foreach, so, let’s go to action.

Foreach has a really cool feature: the sintax is really easy to use, because we can use {1} and {2} for using the data of the first field and the second field. In our case, we only need the first one only.

The SQL sentence will look like this:

UPDATE datos SET ${1} = 0 WHERE ${1} is null

The the full SQL sentence to use is this:

call common_schema.foreach(‘SELECT c.column_name FROM information_schema.`COLUMNS` c
WHERE TABLE_SCHEMA = “inst7” and TABLE_NAME = “datos”‘,’UPDATE inst7.datos SET$ {1} = 0 WHERE ${1} is nul’);

Here you can see the process in action:

common_schema foreach

common_schema foreach

To end with, I would say that foreach is a very practical function when you need to execute repetitive task. Also, you should know that the CONCAT function (for joining strings) are not needed!!

I hope this can help you!