Full library-specific string modification

Requirements: Website renaming

Formerly: Www.zhangsan.com

Renamed: www.lisi.cn

You need to change all the tables inside the database to include www.zhansan.com into www.lisi.cn.

Ideas:

Thinking through the dictionary table information_schema.table know all the tables and fields of the entire database

Each column and table matching query to the field containing www.zhangsan.com, the query to change to www.lisi.cn can be

Steps:

The first step:write a stored procedure

delimiter $ $CREATE     PROCEDURE  ' test '. ' Filter_data1 ' ()      Begindeclare v_table_schema varchar (255);D Eclare v_table_name varchar (255);D eclare v_ Column_name varchar (255);D Eclare v_content varchar (1024x768);D eclare done int  Default false;declare cur_table_col cursor for select table_schema,table_name, Column_name from information_schema. columns where table_schema not in  (  ' MySQL ', ' information_schema ', ' Performance_ Schema ', ' test2 '  )  ;D eclare continue handler for not found set  Done = true;  open cur_table_col;read_loop:loopfetch cur_table_col into  v_table_schema,v_table_name,v_column_name;IF done THEN LEAVE read_loop; End if;  select concat (' select * from   ', V_table_schema, '. ', V_tabLe_name, '  where  ', V_column_name, '  like   '%mxj% '  ; ')    INTO v_content;   INSERT INTO content  (content)   values  (v_content); end loop; close cur_table_col;    end$ $DELIMITER  ;

CREATE TABLE Content (content VARCHAR); call ' Test '. ' Filter_data11 ' ()

After the call is finished, a detailed statement is recorded in the content table of test.

Step Two:

execute all the query statements stored in the content table, there are empty, can get the query record www.zhangsan.com, will get Results of Replace all into www.lisi.cn

Of course, we can not manually one to determine which statement has the result that one does not need a loop of the judgment script

mkdir/opt/k/mkdir/opt/lcd/opt/l

Copy the results from the previous stored procedure into the file, that is, all the query statements inside the CONTETNT table are stored in the file/opt/l/sqlfile.txt

Vim Tiaoshi.sh#!/bin/bash sqlfile=/opt/l/sqlfile.txtresultfile=/tmp/result.txttmpfile=/tmp/tmpfile TMP=0 > $        Resultfilewhile Read Linedo echo $line echo ' $line ' |mysql-p123 > $TMPFILE If [-s $TMPFILE] Then echo $line >> $RESULTFILE tmp=$ (($TMP + 1) fi do </opt/l/sqlfi Le.txt

Sh.. /l/tiaoshi.sh must do so must be found under an empty folder by path to execute otherwise the name of the other file under the directory will be brought into

/tmp/tmpfile is where the results are stored

/tmp/result.txt it's a result. SQL statements can be used to determine which tables have www.zhangsan.com typeface

Note:sqlfile.txt The file with the results of the previous write stored procedure (test.content) you don't have to check.

And a statement like that will give an error.

SELECT * from NIHAO.A where ' key ' like '%www.zhangsan.com% ';

SELECT * from nihao.a where ' to ' like '%www.zhangsan.com% ';

SELECT * from NIHAO.A where ' from ' like '%www.zhangsan.com% ';

SELECT * from nihao.c where ' default ' like '%www.zhangsan.com% ';

SELECT * from nihao.c where ' desc ' like '%www.zhangsan.com% ';

This is because in where The following conditional columns have special meanings that should be added with single quotes

Step Three:

Determine table changes

Through the update

UPDATE tb1 SET f1=replace (F1, ' www.zhangsan.com ', ' www.lisi.cn ');

T B1 The name of the table you want to modify F1 the name of the column you want to modify

www.zhangsan.com for characters that you want to modify

Www.lisi.cn to what character to change

Use the Replace function to do

To ensure security, perform the production library backup on the test library and then go through the test line and migrate to the production library without error.

Think about why you're not directly replacing it when you write a stored procedure. Online database data is heavy as Taishan and the whole table and the new one cause a great resource problem so determine

To filter out after the update if the number of more can be a single statement update

This article from "Grinning Ah" blog, reproduced please contact the author!

Full library-specific string modification