Change URL of all Mysql DBMS tables with PHP

Question

I have a Mysql DBMS with several tables, I searched unsuccessfully how to do a routine, I need to change all URL’s of all tables to another URL.

Ex: https://www.site1.com.br/ for https://www.site2.com.br/

Answer 1

You can use the information_schema.Columns table to extract the list of columns and tables and assemble the update instructions to run individually. Example:

SELECT
    CONCAT('UPDATE ', table_name, ' SET ', column_name, ' = REPLACE(', column_name, ', ''xxx'', ''yyy'');')
FROM
    information_schema.columns
WHERE
    table_schema = '<SEU BANCO AQUI>'
    AND data_type IN ('char, ''varchar', 'text')
ORDER BY
    table_name,
    ordinal_position;