MYSQL全局遍历替换特征字符串的方法

柔光的暖阳◎ 2024-03-23 20:20 13阅读 0赞

![1795c9daa62dd9ae24218d9c89d64e95.jpeg][]

本文主要介绍了MySQL全局遍历替换特征字符串的实现方法，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着[微点阅读][Link 1]小编来一起学习学习吧

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

实现：

### 1. 确定替换规则 ###

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>replace</code> <code>.letssing.net/ -&gt; .kaixinvv.com/&nbsp; </code><code>where</code> <code>column</code> <code>like</code> <code>(%http://%.letssing.net/% </code><code>or</code> <code>%https://%.letssing.net/%)</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

（1）生成查询SQL语句

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

说明：

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

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

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>mysql -uroot -p123456 -h10.10.10.1 -P3306 -N &lt; query.sql &gt; result.txt</code></p> </td> 
  </tr> 
 </tbody> 
</table>

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

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

（1）导入库表字段数据

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

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

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

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

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

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

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>mysql -uroot -p123456 -h10.10.10.1 -P3306 &lt; query_domain.sql &gt; result_domain.txt</code></p> </td> 
  </tr> 
 </tbody> 
</table>

> 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语句

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

（2）执行更新

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>mysql -uroot -p123456 -h10.10.10.1 -P3306 &lt; </code><code>update</code><code>.sql</code></p> </td> 
  </tr> 
 </tbody> 
</table>

到此这篇关于MySQL全局遍历替换特征字符串的实现方法的文章就介绍到这了,希望可以帮到你！

转自：[微点阅读][Link 1] [https://www.weidianyuedu.com][Link 1]

[1795c9daa62dd9ae24218d9c89d64e95.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/28c7db923a4340fda6fa865652e25a88.png
[Link 1]: https://www.weidianyuedu.com