MySQL全局遍历替换特征字符串的实现方法

吾爱主题阅读：194 2023-03-27 14:02:00 评论：0

需求：将一个MySQL实例（如10.10.10.1:3306）范围内所有字段数据中的 .letssing.net 替换为 .kaixinvv.com。

实现：

1. 确定替换规则

1	`replace` `.letssing.net/ -> .kaixinvv.com/` `where` `column` `like` `(%http://%.letssing.net/%` `or` `%https://%.letssing.net/%)`

2. 找出实例中所有符合特征的库表字段

（1）生成查询SQL语句

1 2 3 4 5 6 7 8 9 10 11 12 $ cat find_db_table_column.sh mysql -uroot -p123456 -h10.10.10.1 -P3306 -e " select concat( 'select ' , '' '' ,t1.TABLE_SCHEMA, '' '' , ', ' , '' '' ,t1.TABLE_NAME, '' '' , ', ' , '' '' ,t1.COLUMN_NAME, '' '' ,' from ', t1.TABLE_SCHEMA,' . ',t1.TABLE_NAME,' where \` ',t1.COLUMN_NAME,' \` like \ '%://%.letssing.net/%\' limit 1;' ) from information_schema.columns t1, information_schema.tables t2 where t1.DATA_TYPE in ( 'varchar' , 'longtext' , 'text' , 'mediumtext' , 'char' ) and t1.TABLE_SCHEMA not in ( 'information_schema' , 'mysql' , 'performance_schema' , 'sys' ) and (t2.data_length+t2.index_length) /1024/1024/1024 < 1 and t2.table_name not like '%log%' and t2.table_name not like '%idempotent%' and t1.table_schema= t2.table_schema and t1.table_name= t2.table_name order by t1.TABLE_SCHEMA, t1.TABLE_name, t1.column_name;" -N > query.sql

说明：

mysql命令行执行查询，将查询结果输出到文件，-N参数用于去掉表头。
通过查询数据字典视图 information_schema.columns 和 information_schema.tables 生成查询所有包含特征字符串的库表字段的SQL语句。查询条件为：只查询字符串类型的字段；不查询系统库表；只查询1G以下的小表；不查询某些特殊用途（日志、幂等性）的超大表。

（2）执行查询并生成结果文件

1	`mysql -uroot -p123456 -h10.10.10.1 -P3306 -N < query.sql > result.txt`

result.txt文件内容示例：
db1 table1 column1
db1 table1 column2
db2 table2 column1
db2 table2 column2

3. 对上一步每个库表字段，查询n条特征数据，用以人工采样确认

（1）导入库表字段数据

1 2 3 mysql -uwxy -p -h127.0.0.1 -p123456 -P3306 --local-infile -Ddomain -e " truncate table t1; load data local infile '/home/mysql/domain_name/rule/result.txt' into table t1(dbname,tablename,columnname);"

说明：将前一步生成的结果文件导入一个表中，用于下一步生成查询SQL语句。

（2）生成查询数据的SQL语句

1 2 mysql -uwxy -p -h127.0.0.1 -p123456 -P3306 -Ddomain -e " select concat('select ',instance,',''',dbname,''',''',tablename,''',\`',columnname,'\` from ',dbname,'.',tablename, ' where \`',columnname,'\` like \'%://%.letssing.net/%\' limit 5;') from t1 order by instance,dbname,tablename;" -N > query_domain.sql

说明：这里对于每个符合条件的库表字段，查询出5条数据用于人工确认。

（3）执行查询并生成结果文件

1	`mysql -uroot -p123456 -h10.10.10.1 -P3306 < query_domain.sql > result_domain.txt`

result_domain.txt文件内容示例：
db1 table1 column1
db1 table1 http://txcdn-song-mvbox-cn.letssing.net/mka/16/90461116-0.mka
db1 table1 http://txcdn-song-mvbox-cn.letssing.net/mka/16/90461116-0.mka
db1 table1 column2
db1 table1 http://txcdn-song-mvbox-cn.letssing.net/ksc/90/16/90461116-0.ksc
db1 table1 http://txcdn-song-mvbox-cn.letssing.net/ksc/90/16/90461116-0.ksc
db2 table2 column1
db2 table2 http://txcdn-song-mvbox-cn.letssing.net/mka/16/90461116-0.mka
db2 table2 http://txcdn-song-mvbox-cn.letssing.net/mka/16/90461116-0.mka
db2 table2 column2
db2 table2 http://txcdn-song-mvbox-cn.letssing.net/ksc/90/16/90461116-0.ksc
db2 table1 http://txcdn-song-mvbox-cn.letssing.net/ksc/90/16/90461116-0.ksc

4. 更新特征域名数据

（1）生成字符串替换的更新SQL语句

1 2 3 4 mysql -uwxy -p -h127.0.0.1 -p123456 -P3306 --local-infile -Ddomain -e " select concat( 'update ' ,dbname, '.' ,tablename, ' set \`' ,columnname, '\` = ' , 'replace(\`' ,columnname, '\`,' , '\'.letssing.net/\',\'.kaixinvv.com/\')' , ' where \`' ,columnname, '\` like \'%http://%.letssing.net/%\' or \`' , columnname, '\` like \'%https://%.letssing.net/%\';' ) from t1 where instance = 1 order by instance,dbname,tablename;" -N > update .sql