Find and Replace text in all table using mysql query

Question

I have a database with name test having two tables

1. sample1(name, quote)
2. sample2(name, quote)

I need a replace function or procedure that does the following

1. Find columns with String data type (Text,varchar,char,etc) in all tables within test database
2. Look for a value in that column and replace it with a new value

I have found an stored procedure in this post but I don't understand how it works.

is there anyway to achieve this or adopt the stored procedure in the linked post for my use?

• MySQL DBA: can you elaborate your questions little bit more in detail. as I can see you have two questions you want to replace something in db and how it is being related to table_schema???
• MySQL DBA: with respect to replacing a specific text in entire databases. You can refer to solution provided here :stackoverflow.com/questions/4822638/…
• Developer: Regarding table_scheme I don't know what is this and how I can get it from phpmyadmin. can you write a sample query for it.
• MySQL DBA: table_schema is nothing but your database name. If you query informaiton_schema.tables you get the details.
• miracle173: You searched a lot and found `table_schema'? where did you find it? How is it related to the remaining text and the title of your post?

Answer 1

MySQL's replace function should come handy to replace the text you need:

update $table set column = replace(column, 'old_text', new_text') where column='old_text' and <other_conditions>;

Now, as you talk of replace in all tables, that's something not possible in single query. Though you can create a routine to do so! For the sample you can refer a procedure which is finding a string in all tables of all databases.

• (Though been already mentioned in comments) table_schema is a column name of information_schema.tables table which represents the database name.