剛剛做完了一個手機客戶端的攻略的Html5 Web App頁面，新的需求出現了：由於攻略文章是抓取過來的，有很多外鏈，一開始沒有過濾。於是先用PHP寫了一個過濾函數，然後批量執行更新相關數據庫記錄即可。
public static function filter_newslink($aid){
$content = mod_news :: get_newscont($aid);
//先過濾圖片的外鏈
$content = preg_replace('/<a (.*)>(<img.*>)<\/a>/i', '${2}', $content);
//再過濾文字的外鏈<a title="" href="">文字</a>為文字
$content = preg_replace('/<a (.*)>(.*)<\/a>/i', '${2}', $content);
$data = array('news_id' => $aid, "content" => $content);
$status = mod_news :: update_newscontent($data);
return $status;
}
替換文章的關鍵詞標簽可以使用mysql導出後加工再導入數據庫，也可以使用存儲過程實現。這個的存儲過程就不寫了。
後來發現有些圖片沒有抓過來，而產品已經上線，重新抓取數據已是不現實的事情了。於是和同事協商後干脆把圖片所在塊一起去掉。攻略文章不再展示“卡牌數值”的圖片表格。實現方法是采用MySQL的一些不太常用的函數。
SELECT replace(content,SUBSTRING(content FROM POSITION("卡牌數值" IN content) FOR POSITION("-->" IN content)),"</span></h3></div>")
as x from CONTENT_TABLE c
where c.news_id in (select news_id from NEWS_TABLE where col_id = 66)
update CONTENT_TABLE c
set c.content = replace(c.content,SUBSTRING(c.content FROM POSITION("卡牌數值" IN c.content) FOR POSITION("-->" IN c.content)),"</span></h3></div>")
where c.news_id in (select news_id from NEWS_TABLE where col_id = 66)
鑒於抓來的html結構比較亂比較復雜，也只能這樣了。盡管效率一般，不過相關的文章不過幾百篇，還可以接受的解決方法。關於其中涉及的SQL函數這裡再重溫學習一下：
一、MySQL中LOCATE和別名函數POSITION等
函數LOCATE(substr,str) 作用同POSITION(substr IN str)和INSTR(str,substr)
作用：返回子串 substr 在字符串 str 中第一次出現的位置。如果子串 substr 在 str 中不存在，返回值為 0；LOCATE還有一種形式，包含三個參數：LOCATE(substr,str,pos) ，其返回子串 substr 在字符串 str 中的第 pos 位置後第一次出現的位置。INSTR(str,substr)和LOCATE()的雙參數形式相同，只是參數順序不一樣而已。
mysql> SELECT LOCATE('bar', 'foobarbar');
-> 4
mysql> SELECT INSTR('foobarbar', 'bar');
-> 4
mysql> SELECT LOCATE('xbar', 'foobar');
-> 0
mysql> SELECT INSTR('xbar', 'foobar');
-> 0
mysql> SELECT LOCATE('bar', 'foobarbar', 7);
-> 7
這個函數是多字節安全的。在 MySQL 3.23 中，這個函數是字母大小寫敏感的，當在 MySQL 4.0 中時，如有任一參數是一個二進制字符串，它才是字母大小寫敏感的。
以下語句可以實現同樣的查詢功能：
SELECT `column` FROM `table` where `condition` like `%keyword%’
SELECT `column` from `table` where locate(‘keyword’, `condition`)>0
SELECT `column` from `table` where position(‘keyword’ IN `condition`)
SELECT `column` from `table` where instr(`condition`, ‘keyword’ )>0
速度上後三個比使用 like 稍快了一點點。
二、MySQL的REPLACE用法
用法一：函數REPLACE(str,from_str,to_str)
在字符串 str 中所有出現的字符串 from_str 均被 to_str替換，然後返回這個字符串：
mysql> select REPLACE('www.8783.com/a/detail_list/', 'a', 'list');
-> www.8783.com/list/detlistil_list/
例：把表table中的name字段中的 '斗三國'替換為“全民斗三國”
mysql> update table set name=replace(name,'斗三國','全民斗三國')
這個函數也是多字節安全的。
用法二：REPLACE INTO
在向表中插入數據的時候，經常遇到這樣的情況：1. 首先判斷數據是否存在； 2. 如果不存在，則插入；3.如果存在，則更新。包括我在內的程序猿們常見的做法有三種：
第一種：MySQL很常見的一種做法，許多新手、甚至許多資深的高級coder也有這麼寫的，會在代碼中封裝三個函數，一個函數查詢記錄是否存在，一個函數實現直接插入，另一個函數對已有記錄進行更新。在不同的情況進行調用。這種方法多次excute執行數據操作，勢必造成比較大的開銷。
第二種：用一條SQL代替三種情況的封裝，來實現按需操作，或插入新記錄，或更新舊數據。SQL Server中的語句如下：
IF NOT EXISTS(select 1 from NEWS_bak where news_id = 1008)
insert into NEWS_bak(title, keyword, description) values('孫權', '三國','孫權-吳國老大')
else
update NEWS_bak set title = "孫權"and keyword='三國' and description='孫權-吳國老大' where news_id = 1008
說明：對於IF NOT EXISTS的相同表達，MySQL一般用作條件WHERE NOT EXISTS();由於exists(SELECT NULL )也會返回true，故select exists(SELECT NULL )的結果為1。
但是在MySQL 中如何實現此邏輯呢？方法有，且語法更簡潔——replace into 。
MySQL replace into 有三種形式：
1. replace into tbl_name(col_name, ...) values(...)
2. replace into tbl_name(col_name, ...) select ...
3. replace into tbl_name set col_name=value, ...
前兩種形式使用頻度比較高。其中 “into” 關鍵字可以省略，不過最好加上 “into”，這樣意思更加直觀。所有列的值均取自在REPLACE語句中被指定的值。所有缺失的列被設置為各自的默認值，這和INSERT一樣。您不能從當前行中引用值，也不能在新行中使用值。如果您使用一個例如“SET col_name = col_name + 1”的賦值，則對位於右側的列名稱的引用會被作為DEFAULT(col_name)處理。因此，該賦值相當於SET col_name = DEFAULT(col_name) + 1。
舉例：
replace into tableName (id,index) values('1','index-A'),('2','index-B')
此語句用於向表tableName中插入兩條記錄。 replace into 跟 insert 功能類似，不同點在於：replace into 首先嘗試插入數據到表中，如果發現表中已經有此行數據（根據主鍵或者唯一索引判斷）則先刪除此行數據，然後插入新的數據; 否則，直接插入新的數據。
為了能夠使用REPLACE，您必須同時擁有表的INSERT和DELETE權限。
REPLACE語句會返回一個數，來指示受影響的行的數目。該數是被刪除和被插入的行數的和。如果對於一個單行REPLACE該數為1，則一行被插入，同時沒有行被刪除。如果該數大於1，則在新行被插入前，有一個或多個舊行被刪除。如果表包含多個唯一索引，並且新行復制了在不同的唯一索引中的不同舊行的值，則有可能是一個單一行替換了多個舊行。
PHP可以使用mysql_affected_rows()函數獲得受影響的行數。受影響的行數可以容易地確定是否REPLACE只添加了一行，或者是否REPLACE也替換了其它行：只需檢查該數是否為1（添加）或更大（替換）。
必須注意：插入數據的表必須有主鍵PRIMARY KEY或者是唯一索引UNIQUE！否則，使用一個REPLACE語句沒有意義。該語句會與INSERT相同，因為沒有索引被用於確定是否新行復制了其它的行。這時，replace into 會直接插入數據，這將導致表中出現重復的數據。
三、MySQL SUBSTRING 函數的使用
SUBSTRING ( expression , start , length )
參數：
expression 是字符串、二進制字符串、text、image、列或包含列的表達式。不要使用包含聚合函數的表達式。
start是一個整數，指定子串的開始位置。若為負數，則從字符串末尾倒數。
length是一個整數，可選，指定子串的長度（要返回的字符數或字節數）。
函數共有4種格式：
SUBSTRING(str,pos) ,
SUBSTRING(str FROM pos) ,
SUBSTRING(str,pos,len) ,
SUBSTRING(str FROM pos FOR len)
不帶有len 參數的格式從字符串str返回一個子字符串，起始於位置 pos。帶有len參數的格式從字符串str返回一個長度同len字符相同的子字符串，起始於位置 pos。 使用 FROM的格式為標准 SQL 語法。也可能對pos使用一個負值。假若這樣，則子字符串的位置起始於字符串結尾的pos 字符，而不是字符串的開頭位置。在以下格式的函數中可以對pos 使用一個負值。
說明
由於在 text 數據上使用 SUBSTRING 時 start 和 length 指定字節數，因此 DBCS 數據（如日本漢字）可能導致在結果的開始或結束位置拆分字符。此行為與 READTEXT 處理 DBCS 的方式一致。然而，由於偶而會出現奇怪的結果，建議對 DBCS 字符使用 ntext 而非 text 。
返回類型
如果 expression 是支持的字符數據類型，則返回字符數據。如果expression 是支持的 binary 數據類型，則返回二進制數據。一般實際使用中text 數據多以 varchar 的形式返回，image 數據則以 varbinary 的形式返回。
返回字符串的類型與給定表達式的類型相同（表中顯示的除外）。
注釋
在字符數中必須指定使用 ntext 、char 或 varchar 數據類型的偏移量（start 和 length ）。在字節數中必須指定使用 text 、image 、binary 或 varbinary 數據類型的偏移量。
說明 兼容級別可能影響返回值。有關兼容級別的更多信息，請參見 sp_dbcmptlevel 。
其他字符串截取函數有：
1、left（str, length） 從左開始截取字符串
例如：SELECT LEFT(title,1) AS abstract FROM `NEWS_bak`
2、
right（str, length） 從右開始截取字符串
3、
substring_index（str,delim,count） 按關鍵字截取字符串
說明：substring_index（被截取字段，關鍵字，關鍵字出現的次數）
例：select substring_index（"www.8783.com"，"."，2） as domain from news_table的查詢結果是：www.8783
（注：如果關鍵字出現的次數是負數 如-2 則是從後倒數，到字符串結束）
