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全局遍歷替換特征字符串如何實現”這篇文章的內容，相信大家都有了一定的了解，希望小編分享的內容對大家有幫助，若想了解更多相關的知識內容，請關注億速云行業資訊頻道。