GZGame 发表于 2013-3-18 10:43:30

[转帖]MYSQL查询字符替换sql语句

<p><span style="FONT-FAMILY: 宋体; COLOR: black">在</span><span style="FONT-FAMILY: Simsun; COLOR: black">mysql</span><span style="FONT-FAMILY: 宋体; COLOR: black">中替换字符中的内容我们使用了</span><span style="FONT-FAMILY: Simsun; COLOR: black">replace</span><span style="FONT-FAMILY: 宋体; COLOR: black">就可以实现，如下实例。</span></p>
<div align="center">
<table style="WIDTH: 465pt; BACKGROUND: #ffbb77" class="MsoNormalTable" border="0" cellspacing="1" cellpadding="0" width="620">
<tbody>
<tr style="HEIGHT: 20.25pt">
<td style="PADDING-BOTTOM: 0.75pt; PADDING-LEFT: 0.75pt; WIDTH: 348pt; PADDING-RIGHT: 0.75pt; BACKGROUND: #ffe7ce; HEIGHT: 20.25pt; PADDING-TOP: 0.75pt" width="464">
<p><span style="FONT-FAMILY: 宋体; COLOR: black; FONT-SIZE: 9pt">代码如下</span></p></td>
<td style="PADDING-BOTTOM: 0.75pt; PADDING-LEFT: 0.75pt; WIDTH: 81.75pt; PADDING-RIGHT: 0.75pt; BACKGROUND: #ffe7ce; HEIGHT: 20.25pt; PADDING-TOP: 0.75pt" width="109"></td></tr>
<tr>
<td style="PADDING-BOTTOM: 7.5pt; PADDING-LEFT: 7.5pt; PADDING-RIGHT: 7.5pt; BACKGROUND: #fff4e8; PADDING-TOP: 7.5pt" valign="top" colspan="2">
<p style="MARGIN: 0cm="0cm"0cm="0cm"0pt"><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt">mysql&gt; update `table_name` set field = replace(field,'.rar','.7z');</span></p></td></tr></tbody></table></div>
<p style="MARGIN: 0cm="0cm"0cm="0cm"0pt"><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt">table_name</span><span style="COLOR: black; FONT-SIZE: 10.5pt">：要查询的表名，</span></p>
<p style="MARGIN: 0cm="0cm"0cm="0cm"0pt"><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt">field</span><span style="COLOR: black; FONT-SIZE: 10.5pt">：表里的字段名，</span></p>
<p style="MARGIN: 0cm="0cm"0cm="0cm"0pt"><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt">replace(field,'.rar','.7z'); </span><span style="COLOR: black; FONT-SIZE: 10.5pt">：正则匹配，把</span><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt">field</span><span style="COLOR: black; FONT-SIZE: 10.5pt">字段里的</span><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt"> .rar </span><span style="COLOR: black; FONT-SIZE: 10.5pt">替换为</span><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt"> .7z</span></p>
<p style="MARGIN: 0cm="0cm"0cm="0cm"0pt"><span style="COLOR: black; FONT-SIZE: 10.5pt">利用</span><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt">replace</span><span style="COLOR: black; FONT-SIZE: 10.5pt">加正则来替换字符</span></p>
<div align="center">
<table style="WIDTH: 465pt; BACKGROUND: #ffbb77" class="MsoNormalTable" border="0" cellspacing="1" cellpadding="0" width="620">
<tbody>
<tr style="HEIGHT: 20.25pt">
<td style="PADDING-BOTTOM: 0.75pt; PADDING-LEFT: 0.75pt; WIDTH: 348pt; PADDING-RIGHT: 0.75pt; BACKGROUND: #ffe7ce; HEIGHT: 20.25pt; PADDING-TOP: 0.75pt" width="464">
<p><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 9pt">&nbsp;</span><span style="FONT-FAMILY: 宋体; COLOR: black; FONT-SIZE: 9pt">代码如下</span></p></td>
<td style="PADDING-BOTTOM: 0.75pt; PADDING-LEFT: 0.75pt; WIDTH: 81.75pt; PADDING-RIGHT: 0.75pt; BACKGROUND: #ffe7ce; HEIGHT: 20.25pt; PADDING-TOP: 0.75pt" width="109"></td></tr>
<tr>
<td style="PADDING-BOTTOM: 7.5pt; PADDING-LEFT: 7.5pt; PADDING-RIGHT: 7.5pt; BACKGROUND: #fff4e8; PADDING-TOP: 7.5pt" valign="top" colspan="2">
<p style="MARGIN: 0cm="0cm"0cm="0cm"0pt"><br/><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt">UPDATE `table_name` SET `field_name` = replace (`field_name`,'from_str','to_str') WHERE `field_name` LIKE '%from_str%'</span></p></td></tr></tbody></table></div>
<p style="MARGIN: 0cm="0cm"0cm="0cm"0pt"><span style="COLOR: black; FONT-SIZE: 10.5pt">说明：</span><br/><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt">table_name —— </span><span style="COLOR: black; FONT-SIZE: 10.5pt">表的名字</span><br/><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt">field_name —— </span><span style="COLOR: black; FONT-SIZE: 10.5pt">字段名</span><br/><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt">from_str —— </span><span style="COLOR: black; FONT-SIZE: 10.5pt">需要替换的字符串</span><br/><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt">to_str —— </span><span style="COLOR: black; FONT-SIZE: 10.5pt">替换成的字符串</span></p>
<p style="MARGIN: 0cm="0cm"0cm="0cm"0pt"><span style="COLOR: black; FONT-SIZE: 10.5pt">下面是今天运行的两句</span><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt">SQL</span><span style="COLOR: black; FONT-SIZE: 10.5pt">批量替换语句，如果用到的可以参考下！</span></p>
<div align="center">
<table style="WIDTH: 465pt; BACKGROUND: #ffbb77" class="MsoNormalTable" border="0" cellspacing="1" cellpadding="0" width="620">
<tbody>
<tr style="HEIGHT: 20.25pt">
<td style="PADDING-BOTTOM: 0.75pt; PADDING-LEFT: 0.75pt; WIDTH: 348pt; PADDING-RIGHT: 0.75pt; BACKGROUND: #ffe7ce; HEIGHT: 20.25pt; PADDING-TOP: 0.75pt" width="464">
<p><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 9pt">&nbsp;</span><span style="FONT-FAMILY: 宋体; COLOR: black; FONT-SIZE: 9pt">代码如下</span></p></td>
<td style="PADDING-BOTTOM: 0.75pt; PADDING-LEFT: 0.75pt; WIDTH: 81.75pt; PADDING-RIGHT: 0.75pt; BACKGROUND: #ffe7ce; HEIGHT: 20.25pt; PADDING-TOP: 0.75pt" width="109"></td></tr>
<tr>
<td style="PADDING-BOTTOM: 7.5pt; PADDING-LEFT: 7.5pt; PADDING-RIGHT: 7.5pt; BACKGROUND: #fff4e8; PADDING-TOP: 7.5pt" valign="top" colspan="2">
<p style="MARGIN: 0cm="0cm"0cm="0cm"0pt"><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt">UPDATE boblog_blogs SET content = replace (content,'','');</span></p></td></tr></tbody></table></div>
<p style="MARGIN: 0cm="0cm"0cm="0cm"0pt"><span style="COLOR: black; FONT-SIZE: 10.5pt">查询表</span><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt"> boblog_blogs </span><span style="COLOR: black; FONT-SIZE: 10.5pt">将</span><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt"> content </span><span style="COLOR: black; FONT-SIZE: 10.5pt">字段中的字符</span><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt"> </span><span style="COLOR: black; FONT-SIZE: 10.5pt">替换成</span><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt"> </span></p>
<p style="MARGIN: 0cm="0cm"0cm="0cm"0pt"><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt">&nbsp;</span></p>
<div align="center">
<table style="WIDTH: 465pt; BACKGROUND: #ffbb77" class="MsoNormalTable" border="0" cellspacing="1" cellpadding="0" width="620">
<tbody>
<tr style="HEIGHT: 20.25pt">
<td style="PADDING-BOTTOM: 0.75pt; PADDING-LEFT: 0.75pt; WIDTH: 348pt; PADDING-RIGHT: 0.75pt; BACKGROUND: #ffe7ce; HEIGHT: 20.25pt; PADDING-TOP: 0.75pt" width="464">
<p><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 9pt">&nbsp;</span><span style="FONT-FAMILY: 宋体; COLOR: black; FONT-SIZE: 9pt">代码如下</span></p></td>
<td style="PADDING-BOTTOM: 0.75pt; PADDING-LEFT: 0.75pt; WIDTH: 81.75pt; PADDING-RIGHT: 0.75pt; BACKGROUND: #ffe7ce; HEIGHT: 20.25pt; PADDING-TOP: 0.75pt" width="109"></td></tr>
<tr>
<td style="PADDING-BOTTOM: 7.5pt; PADDING-LEFT: 7.5pt; PADDING-RIGHT: 7.5pt; BACKGROUND: #fff4e8; PADDING-TOP: 7.5pt" valign="top" colspan="2">
<p><span style="FONT-FAMILY: Simsun; COLOR: black">UPDATE boblog_blogs SET content = replace (content,'[//mycode]','');</span></p></td></tr></tbody></table></div>
<p style="MARGIN: 0cm="0cm"0cm="0cm"0pt"><span style="COLOR: black; FONT-SIZE: 10.5pt">查询表</span><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt"> boblog_blogs </span><span style="COLOR: black; FONT-SIZE: 10.5pt">将</span><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt"> content </span><span style="COLOR: black; FONT-SIZE: 10.5pt">字段中的字符</span><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt"> [//mycode] </span><span style="COLOR: black; FONT-SIZE: 10.5pt">替换成</span><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt"> </span></p>
<p style="MARGIN: 0cm="0cm"0cm="0cm"0pt"><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt">&nbsp;</span></p>
<div align="center">
<table style="WIDTH: 465pt; BACKGROUND: #ffbb77" class="MsoNormalTable" border="0" cellspacing="1" cellpadding="0" width="620">
<tbody>
<tr style="HEIGHT: 20.25pt">
<td style="PADDING-BOTTOM: 0.75pt; PADDING-LEFT: 0.75pt; WIDTH: 348pt; PADDING-RIGHT: 0.75pt; BACKGROUND: #ffe7ce; HEIGHT: 20.25pt; PADDING-TOP: 0.75pt" width="464">
<p><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 9pt">&nbsp;</span><span style="FONT-FAMILY: 宋体; COLOR: black; FONT-SIZE: 9pt">代码如下</span></p></td>
<td style="PADDING-BOTTOM: 0.75pt; PADDING-LEFT: 0.75pt; WIDTH: 81.75pt; PADDING-RIGHT: 0.75pt; BACKGROUND: #ffe7ce; HEIGHT: 20.25pt; PADDING-TOP: 0.75pt" width="109"></td></tr>
<tr>
<td style="PADDING-BOTTOM: 7.5pt; PADDING-LEFT: 7.5pt; PADDING-RIGHT: 7.5pt; BACKGROUND: #fff4e8; PADDING-TOP: 7.5pt" valign="top" colspan="2">
<p><span style="FONT-FAMILY: Simsun; COLOR: black">UPDATE boblog_blogs SET htmlstat=0 where htmlstat=1</span></p></td></tr></tbody></table></div>
<p style="MARGIN: 0cm="0cm"0cm="0cm"0pt"><span style="COLOR: black; FONT-SIZE: 10.5pt">由于</span><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt">mysql</span><span style="COLOR: black; FONT-SIZE: 10.5pt">字符替换时使用正则速度不如直接替换建义大家不在万不得己不要使用</span><span style="FONT-FAMILY: Simsun; COLOR: black; FONT-SIZE: 10.5pt">mysql<span style="FONT-FAMILY: 宋体; FONT-SIZE: 9pt">正则表达式</span></span><span style="COLOR: black; FONT-SIZE: 10.5pt">哦。</span></p>
页: [1]
查看完整版本: [转帖]MYSQL查询字符替换sql语句