«

MySQL全局遍历替换特征字符串如何实现

时间:2024-7-25 09:07     作者:韩俊     分类: Mysql


这篇“MySQL全局遍历替换特征字符串如何实现”文章的知识点大部分人都不太理解,所以小编给大家总结了以下内容,内容详细,步骤清晰,具有一定的借鉴价值,希望大家阅读完这篇文章能有所收获,下面我们一起来看看这篇“MySQL全局遍历替换特征字符串如何实现”文章吧。

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

实现:

1. 确定替换规则

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

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

(1)生成查询SQL语句

$cat find_db_table_column.sh
mysql -uroot -p123456 -h20.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)执行查询并生成结果文件

mysql -uroot -p123456 -h20.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)导入库表字段数据

mysql -uwxy -p -h227.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语句

mysql -uwxy -p -h227.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)执行查询并生成结果文件

mysql -uroot -p123456 -h20.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语句

mysql -uwxy -p -h227.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

(2)执行更新

mysql -uroot -p123456 -h20.10.10.1 -P3306 < update.sql

标签: mysql

热门推荐