mysql取前後各n筆資料

這問題常常會遇到，因為大家都喜歡在網頁上加上「上一筆」、「下一筆」的按鈕
簡單的做法是把連結加個標記，告訴下一個網頁現在是哪一筆，你要抓上一筆還是下一筆，這樣單頁就可以只執行一次query，可是在大家注重SEO的時代，這個做法可以說不太聰明。
於是乎，程式人員只好在一頁裡多下幾次query把上一筆跟下一筆都先抓出來，不過程式人員的特性就是懶！想到要一直下query就不開心，所以呢，網路上就出現了很多類似的討論主題，不過最快速的方式也就是下。兩。次。。。是的，還是不能一次搞定。

簡單的說明下兩次的做法：
假設要找編號228的前後一筆資料，而且編號欄位的值在資料庫裡可能會有跳號狀況，因此絕對不能用+1,-1的做法處理，所以要分別下
SELECT * FROM 資料表 WHERE 編號>=228 ORDER BY 編號 LIMIT 2
SELECT * FROM 資料表 WHERE 編號<228 ORDER BY 編號 DESC LIMIT 1

可是呢，這個是單純的狀況
如果要從a,b兩個資料表抓資料出來編號228前後一筆資料，而且裡面一個類別的欄位值要跟編號228的一樣，但是下sql語法之前還不知道編號228的類別是什麼，如果一樣下
SELECT 編號,a.類別,b.類別名稱 FROM a,b WHERE 編號>=228 AND a.類別=b.類別ORDER BY 編號 LIMIT 2
SELECT 編號,a.類別,b.類別名稱 FROM a,b WHERE 編號<228 AND a.類別=b.類別 ORDER BY 編號 DESC LIMIT 1
就會發現抓出來的資料會擁有不同的類別值……

為了要先得到編號228的類別，就要先下一次
SELECT 編號,類別  FROM a WHERE 編號=228

當然，其實不一定要把這句獨立，可以把他併到上面那個例子裡當一個subquery，就也是2次搞定，不過有可能會得到一句落落長的sql語法，光看就頭昏眼花……
知道228的類別後，再來抓他同類的前後一筆，可是這個時候再下2次就很笨了，因為如果一開始就決定要下3次 ，還上網找答案做什麼呢？
於是我用了UNION
(SELECT '前' as tmp,編號 FROM a WHERE 編號>228 AND 類別=x ORDER BY 編號 LIMIT 1)

UNION

(SELECT '後' as tmp,編號 FROM a WHERE 編號<228 AND 類別=x ORDER BY 編號 DESC LIMIT 1)
tmp欄位只是標一下前後各是哪一筆，萬一228的前後剛好有一邊沒資料，就會比較好判斷

說穿了還是subquery的概念，只是因為前後一筆我們只需要知道部份資料，如果跟228一起搜出來，mysql回傳回來的一大堆資料其實都用不到，這樣就浪費了server資源
而用UNION把兩邊的資料一起抓出來，也會比分2次進資料庫抓資料來的省時間

目前看來好像就只有這幾種方式比較省力了，為什麼他們不出一個nearby的函數，專門用來處理這種狀況呢...............................

MySQL複製一筆資料

這個說起來很簡單，真的做起來其實也很簡單，但因為不常用，應該有不少人用起來很生疏吧！

語法其實很簡單
INSERT INTO tablename [VALUES (fields...)]
SELECT fields FROM tablename [WHERE ...]

我想這個在使用上最容易遇到的問題，就是主索引跟唯一值不能重複的問題了。
在最單純的狀況下，要把tableA的一筆資料複製，只要下

INSERT INTO tableA
SELECT * FROM tableA WHERE id=3

就行了。不過呢，如果遇到鍵值不能重複的話，就要在設定VALUES裡設定欄位，當然相對的在後面的SELECT裡，也要指定一樣的欄位。

這樣如果某個表欄位很多，又要避掉索引值的話，其實光用想的我就累了……應該有方法可以處理掉欄位的問題，但那是個懶方法，卻不一定是個好方法，在目前還沒遇到那麼棘手的問題前，還是勤勞的慢慢key欄位名稱好了～

這個方式也是可以用在Oracle跟MSSQL裡的，其他的資料庫因為手邊沒得測，就不知曉了。

設定與mysql連線所使用的語系

其實是之前就有遇過的問題，但是因為以前有前人寫好語法就直接copy沒有記，直到昨天有需要又找不到哪裡有用過，才認真的找了一下資料。

簡單的說，會遇到這個問題是因為資料庫使用的語系與程式預設與mysql連線時要使用的語系不一樣，這會造成中文字在mysql裡是正常的，但透過php抓出來的卻是問號。解決這樣的問題依php版本不同有不同的方式。

php5：mysql_set_charset(charset,resource)
php4：mysql_query('SET NAMES charset', resource)

範例：假設連線時要設定語系為utf8


//建立與mysql db的連線
$dbConnection=mysql_connect("localhost","acc","password");
//選擇要操作哪一個資料庫
mysql_select_db("dbname",$dbConnection);

//如果是php5
mysql_set_charset('utf8',$dbConnection);
//如果是php4
mysql_query('SET NAMES utf8', $dbConnection);


這樣子把資料庫裡的中文字抓出來時，就會正常顯示了。


中文還真是麻煩的東西啊！

mysql資料庫備份與還原

資料庫備份是身為資訊人員例行的工作項目，還原倒是不一定了，通常要去還原資料庫，表示事情大條啦～不過學起來總是有備無患，免得急要用時空有備份資料，不會還原方法，這樣有備份跟沒備份是一樣滴。
這裡就來簡單說明一下用指令備份跟還原mysql資料庫的方式。

備份：mysqldump [要備份的資料庫名稱] -u [登入mysql帳號] --password='[密碼]' [OPTIONS] > [目標檔案完整路徑]
還原：mysql -u [登入mysql帳號] --password='[密碼]' < [來源檔案完整路徑]

範例：
將myWeb資料裡的所有table備份到/home/backup/myWeb.sql，登入mysql的帳號為master，密碼為12345。
　　mysqldump myWeb -u master --password='12345' > /home/backup/myWeb.sql
將備份的/home/backup/myWeb.sql還原進mysql裡
　　mysql -u master --password='12345' < /home/backup/myWeb.sql

這樣子就搞定囉！

官方指令介紹：
mysqldump　mysql

時間相減第二回

之前講過用PHP做時間相減的動作，但是如果要在mysql裡就把時間減好再抓出來呢？
使用上仍然是先把時間轉換成timestamp，然後再來做運算。

來個範例就一目了然了：
SELECT UNIX_TIMESTAMP(欄位ａ) - UNIX_TIMESTAMP(欄位ｂ) FROM 表格名稱

UNIX_TIMESTAMP(欄位ａ) 的工作就是把欄位ａ的日期資料轉換成timestamp，這樣相減後得到的結果是相差秒數，如果要取分鐘數或小時數，就要再去除，像這樣：

SELECT (UNIX_TIMESTAMP(欄位ａ) - UNIX_TIMESTAMP(欄位ｂ))/3600 FROM 表格名稱

因為會有先乘除後加減的問題，所以相減要括起來先做運算，結果再去除，這樣就搞定啦！
相當容易的呢～

mysql,oracle取得當前時間

在新增或修改資料的時候，常常會需要記錄操作時間，雖然可以用程式指令先產生時間再放進去，但就是覺得這樣有點笨……

在oracle，當要抓資料庫主機目前時間，要用sysdate；在mysql，則是用NOW()
修改資料庫欄位值時，就用欄位=NOW()或欄位=sysdate就行了。值得注意的是，在oracle使用sysdate後，會產生系統格式的時間，通常需要用to_char去把撈出來的資料先做轉換，看到的日期格式才會是符合需求的；在mysql，則可不用這麼麻煩，now()產生的時間格式即為Y-M-D H:m:s的格式，用起來相當方便。

mysql組合欄位當join時的key

table A有兩個欄位：新增年月,當月流水號
table B有一個欄位：文件制式編號
其中table B的文件制式編號欄位值是table A的新增年月跟當月流水號組起來的結果
就是說呢，table A的這兩個欄位可能是09503跟0004，table B的文件製式編號就會像095030004
這時候如果要以這些值來join這兩個table，就可以用這樣的SQL語法：
SELECT ...... FROM 'TABLE A' JOIN 'TABLE B' ON 文件制式編號=CONCAT(新增年月,當月流水號)

這樣就可以順利的把兩個表裡對應的資料抓出來，雖然這樣可能不是很正規……

yyy=~~~

一大早就發生一件很靈異又很搞笑的事件

用php新增一筆tagname=yyy的資料進mysql，結果顯示出來變~~~
進mysql搜尋tagname=yyy，結果也是出來tagname=~~~那筆
在mysql裡直接打sql語法新增一筆tagname=yyy的資料，新增成功，然後再搜尋一次tagname=yyy，出現兩筆，一筆是一開始透過php新增的~~~，另一筆是直接在mysql裡新增的yyy
可是如果新增的tagname=yes，就不會變成~es，也就是說y重複多次的話，對mysql來說y=~，又y重覆6次，y=y
結論是這問題屬mysql本身的bug，導因可能是我們的mysql使用big5版，在mysql裡編碼有衝到
處理方式是不予處理，因為yyy或yyyy或yyyyy本身並沒有任何意義，只有在測試時才有可能會新增這類無意義資料，正常使用上不可能會遇到

發現bug也是軟體使用上的一種樂趣，但是如果像windows那樣叫你回報開發人員，然後強制關閉，這樣就一點樂趣也沒有了。