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 -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）执行查询并生成结果文件
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）导入库表字段数据
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语句
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）执行查询并生成结果文件
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语句
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
（2）执行更新
mysql -uroot -p123456 -h10.10.10.1 -P3306 < update.sql
到此这篇关于MySQL全局遍历替换特征字符串的实现方法的文章就介绍到这了,更多相关MySQL全局遍历替换特征字符串内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/43.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
