[SQL]transaction的救援

最近在處理資料同時存取時出現異常的狀況。
研究了一下程式寫法後發現一些小瑕疵，
就是在撰寫多人同時使用的系統程式時，
再進行資料庫存取觀念要做一些注意。

雖說交易管理的觀念是在資料庫管理的基本觀念，
但在程式設計時還有會有被忽略的時候。
畢竟有些程式開發的時候，初步規劃會有單人單機操作的規劃設計。
因此會出現一些看似可行的做法。

最後多人同時上線作業時就會出現異常資料的可能性就會常常出現。

因此特別把交易管理的觀念在這裡紀錄一下。

一般而言，對資料庫存取時無非是insert、update、delete、select這四個動作。
但是除了select以外，其他的存取基本上都有可能出現互相影響的可能性。
因此需要知道資料庫的commit跟rollback這兩個觀念。
其實就是commit跟commit間可以有多個select指令。
但是如果遇到有update或insert的時候就需要去注意一下。
(會特別講是這兩個是因為小弟就是被這兩個指令害了，被轟了一天。哈哈)

在insert新資料時入沒有交易觀念的話，程式可能會出現同時insert的異常，而造成會有一些神奇的異常資料。
其實就是每一個指令在進行修改時，需要先鎖定該資料表，讓該資料不得因為其他指令而被修改。

這邊可能需要一些繪圖才能容易說明。
說簡單一點就是需要讓指令間會互相排隊等待。
如A下的指令到commit之後B下的指令才可以被執行。
這樣才能排除同時下指令的狀況出現。

至於為何會提到rollback這個指令呢?

其實很簡單
就是我們在下一次commit之前，可能會是做了很多條的更新指令。但是如果其中一條錯誤時，要還原資料難不成要DBA連入資料庫手動修改嗎?(可憐的DBA)

因此我會利用try catch方式來讓他去執行。
假設有其失敗的時候，就把全部剛剛做的事情都還原回去。
這樣就可以不用這辛苦了。

附送一句關鍵詞"不做好自動化，就自己做到死"

[SQL]Join的觀念

最近工作寫SQL時，突然發現一個有趣的觀念會讓人搞混與疑惑。
例如說JOIN的觀念。
畢竟JOIN可以說有幾類
inner join
left join
right join
full join

這四項。

其實一般最常被使用的就是INNER JOIN。
如：
select * from student a,course b where a.s_id=b.s_id
這樣的寫法其實就是
select * from student a inner join course b on a.s_id=b.s_id

至於結果呢!
就是所謂的有AB交集的才顯示

其二LEFT JOIN
就是以左邊為主，左邊資料表全數顯示，右邊資料表有對應到的話就是顯示資料，沒對應到的話就是顯示空白。
以數學就是A再加上AB交集的區域

其三RIGHT JOIN
使用方式與LEFT JOIN一樣，但方向剛好相反。
以數學說明就是B再加上BA交集區

其四FULL JOIN
顧名思義就是全部一起來。
就是所謂的聯集拉，有對道就顯示資料沒對道就給她空白值

一般大概就是這四種吧，別再搞混了喔。
懶得畫表個，因此今天只有簡單說明觀念。

[SQL]程式設計小觀念

有些系統需求會為了方便使用者使用而出現所謂的"勾選執行"、"全部執行"。

當然顧名思義看起來又是把"個別執行"的指令多下幾次給資料庫即可達到的需求。
但是
千萬別這麼做。

個人的悲慘經驗是，寫程式的人可能出有這樣的思維｢反正需求可以達到」。
但是這麼做的話下場就是對資料庫的存取次數過多，甚至可能多到直接當掉的可能。
當然Program designer 一開始並不會想到這麼多。

而且最常出現的回應是"去加強硬體設備吧!"

其實我個人也覺得加強硬體設備是手段之一。
但指令也是可以優化的。

如：
個別執行
product_id為1 的價碼更新為100
update product set price='100' where product_id='1'

選擇執行/全部執行
product_id為1與2 的價碼更新為100
update product set price='100' where product_id='1'
update product set price='100' where product_id='2'
或者
update product set price='100' where product_id='1' or product_id='1'
又或者
update product set price='100' where product_id in('1','2')


有沒有發現選擇執行/全部執行可已有三種寫法。
其中第一種存取數會很多，第二種寫的一堆OR 第三種則是用in 搞定一切
或許各有優缺。
目前讓我發現最大的缺點就是存取數過多會造成服務滿載的現象。

所以程式設計師或許不是DBA但在系統設計時替DBA想想。
彼此互助，好過一切動不了!!!

[SQL]資料庫資料搬移 insert select

程式設計時偶爾會有出現暫存表的使用需求。
當然有些設計是考慮到union或暫存表哪個速率快。
因此會有出現需要進行資料搬移的需求。
太久沒用這類需求，難得使用到，因此記錄下來方便以後參考用。

create temp_table (欄位)

建立暫存表的概念跟建立實體表一樣。
之後當然就是資料搬移的問題。
過去有些做法是利用程式的迴圈把資料一筆一筆讀出來之後再依比一比的insert到另一張表。
但這種作法雖然以程式設計人員而言可以掌控的很完整，但缺點就是需要多次存取資料庫的需求。

當然，其實資料庫本身就有其功能。
簡單範例概念如下：
insert table1(col1,col2)
select col1,col2 from table2 where 1=1

這樣就能直接一個請求需求，就達到資料搬移的效果了。

[SQL]複製資料表

最近剛好在做一些新功能時發現需要開新的資料表來用。
再加上想直接引用就程式來使用就好就突發奇想的要來個無痛複製。(似乎不是甚麼可取的方案，特別注意此方法常常犯的錯就是不懂得程式碼直接使用造成錯誤無法排除。)

小弟偏偏就是那個懶人，只是還是有一點點道德，先分析程式碼都看懂了再來複製。
以下就是直接複製資料表的SQL語法。
在這邊留作紀錄方便大家需要時可以領用。

這邊是將舊的資料表欄位及資料一同複製備份出來。
select * into new_table from old_table where 1=1

這邊是將舊的資料表複製出來而不複製其資料行
select * into new_table from old_table where 1=2

[SQL]指定排序

一般使用SQL在進行查詢時的固定用法
select * from table
但有常常會需要排序，一班的用的就是利用某個欄位進行排序
select * from table order by name asc
排序故名思義就是要做一下資料整理，因此固定用法中又有順排逆排
分別是"asc" 、 "desc" 這兩種排序方法。
當然偶爾會遇到一些排序需求是無法直接靠這樣固定排函數排法來實現。
因此若確定查詢出來的結果是固定的，其實我們可以在排序的地方改寫一下，就能讓她固定的排序方式了。
select * from table
order  by (case name
when "dion"  then "01"
when "Allen" then "02"
when "Zero" then "03"
End)

利用這樣的寫法下來，其實就可以達到強迫順序指定的效果了。

[SQL] 資料表大量更新作法

在資料搬移或是大量更新時，
偶爾會出現直接資料表內容搬移的動作，
一般單純的作法可能會用程式語言配合資料庫將資料讀取出來再做寫入的動作。
單然也可以直接透過資料庫語法完成。
使用範例如下：

update table1 set table1.col1=talbe2.col1,table1.col2=table2.col2...
from tabl2
where table1.keyid=table2.keyid

資料表不一定是某一張資料表，也可以是一個查詢多表查詢的結果。
只是在更新欄位上需要特別注意即可。