分類: Database

分類
Database

使用CTE進行遞迴(Recursive)查詢

從 MS SQL 2005 起, 支援了 CTE (Common Table Expression) 語法, 可以參考之前的文章: https://diary.tw/archives/339 .

今天要來介紹的是有關應用在遞迴方面的查詢, 利用這種查詢, 可以很容易地將資料展開, 例如像是組織圖, 或是像分類項含有子分類這樣的樹狀資料, 當然, 簡單一點的像是累加也是一樣的, 先來介紹 CTE 用在累加的語法上.

WITH cte (num, mysum) AS (
  SELECT 1 as num, 1 as mysum
  UNION ALL
  SELECT num + 1, mysum + num + 1 FROM cte a where num <10
)
SELECT * FROM cte

看好, 在括號內的第一個 SELECT 1, 1 的部分, 就是原始值, 而後面的 SELECT .. FROM cte 則是開始自己對自己查詢, 最後的 num < 10 是他的 boundary condition, 也就是只做到 num < 10.

結果如下:
是的, 結果就是那個 1 + 2 + 3 + … + 10 = 55 .

再來看看其他的用法, 像是分類含有子分類的資料, 先來準備一些資料:

CREATE TABLE CATEGORY (CID int primary key, NAME nvarchar(200), PARENTID int)
INSERT INTO CATEGORY values (1, N'交通工具', 0), (2, N'陸上', 1), (3, N'水上', 1)
INSERT INTO CATEGORY values (4, N'機車', 2), (5, N'汽車', 2), (6, N'輪船', 3)
INSERT INTO CATEGORY values (7, N'空中', 1), (8, N'飛機', 7), (9, N'直升機', 7)

來組合吧, 使用CTE:

WITH CTE (CID, NAME, PARENTID, LEVEL) AS (
  SELECT CID, NAME, PARENTID, 0 FROM CATEGORY WHERE PARENTID = 0
  UNION ALL
  SELECT A.CID, A.NAME, A.PARENTID, LEVEL + 1 FROM CATEGORY A INNER JOIN CTE B ON A.PARENTID = B.CID
)
SELECT * FROM CTE

結果如下:

是不是很清楚看出來各分類的位階(LEVEL)狀況? 十分方便好用的語法. 再來看看如何展開成一行資料, 也就是各自分類結構合成一筆結果, 語法如下:

WITH CTE (CID, NAME, PARENTID, LEVEL, ORICID, DATA) AS (
  SELECT CID, NAME, PARENTID, 0, CID, CONVERT(NVARCHAR(MAX), NAME) FROM CATEGORY WHERE PARENTID = 0
  UNION ALL
  SELECT A.CID, A.NAME, A.PARENTID, LEVEL + 1, A.CID, DATA + '-' + A.NAME FROM CATEGORY A INNER JOIN CTE B ON A.PARENTID = B.CID
)
SELECT * FROM CTE

結果如下:

最後面的那個欄位就是想要呈現出來的結果, 說穿了, 就是一直累加上去, 只是是字串的累加(DATA), 不是數字的累加, 另外也多了一個欄位用來表現原本自己的CID(ORICID), 這樣看起來也就更清楚完整, 利用這樣的方式來表現分類結構, 非常好用呢!

另外記得字串累加的部分, 使用 NVARCHAR(MAX) 以避免發現 CTE 和累加的欄位不一致的問題, 這是要特別注意的地方.

無法對齊欄位的錯誤訊息為:
Types don’t match between the anchor and the recursive part in column “xx” of recursive query “xx”.
可以參考這篇解答: http://stackoverflow.com/questions/1838276/cte-error-types-dont-match-between-the-anchor-and-the-recursive-part

結論, CTE的遞迴語法, 其實也就是自己再對自己查詢, 只要把握好起始條件, 還有遞迴的終止條件, 就可以很順利的產出你要的結果. 大家可以多加利用這個好用的語法, Let’s CTE!!

分類
Database

資料庫整筆ROW比對

在資料庫操作時, 有時會需要比對資料表的內容是否一致, 或是某些資料的某些欄位是否一樣, 若是使用 SQL 指令來逐行比對, 會是個比較麻煩的操作方式, 使用 CHECKSUM 函數, 可以方便地將待比對的資料(多欄亦可), 計算出 hash index (int)後, 再進行比對, 舉例如下:

兩個 table 資料:

table TBL_TMP1
fno fdata fcreatetime
1 ‘data 1’ ‘2012/1/1’
2 ‘data 2’ ‘2012/2/3’
3 ‘data 3’ ‘2012/2/4’
4 ‘data 4’ ‘2012/4/1’

table TBL_TMP2
fno fdata fcreatetime
1 ‘data 001’ ‘2012/1/1’
2 ‘data 2’ ‘2012/2/12’
3 ‘data 3’ ‘2012/2/4’
4 ‘data 4’ ‘2012/4/1’

若要找出資料不同的資料列, 可以使用如下指令:

SELECT * FROM
(SELECT *, CHECKSUM(*) AS CHK FROM TBL_TMP1) a INNER JOIN
(SELECT *, CHECKSUM(*) AS CHK FROM TBL_TMP2) b
ON a.fno = b.fno AND a.CHK != b.CHK

其中用了 CHECKSUM(*) 是將 table 中的各欄位合併起來計算 hash index, 再將兩個 table 含有 hash index (欄位名 CHK)拿來比對, 列出不同的資料, 結果如下:
1 data 1 2012-01-01 00:00:00.000 803471792 1 data 001 2012-01-01 00:00:00.000 1877235137
2 data 2 2012-02-03 00:00:00.000 266601110 2 data 2 2012-02-12 00:00:00.000 266601103

是很方便的工具函數.

附上建立上面 sample code 的 create table 及 insert data 指令:

CREATE TABLE TBL_TMP2 (fno int, fdata varchar(20), fcreatetime datetime)

INSERT INTO TBL_TMP1 values (1, 'data 1', '2012/1/1')
INSERT INTO TBL_TMP1 values (2, 'data 2', '2012/2/3')
INSERT INTO TBL_TMP1 values (3, 'data 3', '2012/2/4')
INSERT INTO TBL_TMP1 values (4, 'data 4', '2012/4/1')

INSERT INTO TBL_TMP2 values (1, 'data 001', '2012/1/1')
INSERT INTO TBL_TMP2 values (2, 'data 2', '2012/2/12')
INSERT INTO TBL_TMP2 values (3, 'data 3', '2012/2/4')
INSERT INTO TBL_TMP2 values (4, 'data 4', '2012/4/1')

大家可以試看看, 若是只要比對前兩欄, 則指令如下:

SELECT * FROM
(SELECT *, CHECKSUM(fno, fdata) AS CHK FROM TBL_TMP1) a INNER JOIN
(SELECT *, CHECKSUM(fno, fdata) AS CHK FROM TBL_TMP2) b
ON a.fno = b.fno AND a.CHK != b.CHK

結果如下, 就是只有第一筆資料的前兩欄不同而已:
1 data 1 2012-01-01 00:00:00.000 -1291957785 1 data 001 2012-01-01 00:00:00.000 -1493283680
如同我們想要的結果一樣. 這樣一來可以方便活用這個 CHECKSUM 函數, 十分方便好用!

相關資料:
http://msdn.microsoft.com/en-us/library/ms189788.aspx

資料上寫到是 SQL Server 2005 以上版本可用, 不過其實在 SQL Server 2000 也有這個指令:
http://msdn.microsoft.com/en-us/library/aa258245%28v=sql.80%29.aspx

分類
Database

[MSSQL]文字民國日期轉西元日期

剛朋友問到有關存在 MSSQL 中的文字資料是民國日期, 例如 67/3/12 這樣的格式, 要轉出西元日期, 其實還蠻單純的, 只需要找出第一個 ‘/’ 在什麼位置, 就很容易了, 查找 ‘/’ 的函數使用 charindex, 如下:

declare @a varchar(20)
select @a = '67/6/7'
select charindex('/', @a)

這樣取出的值為 3, 也就是第 3個字元, 而下一步就是取出年的部份, 如下:

declare @a varchar(20)
select @a = '67/6/7'
select substring(@a, 1, charindex('/', @a)-1)

這樣取出的值為 67, 於是要轉出西元年其實就是先轉成 int 後, 加上 1911 再轉回字串後, 加回原來的月/日的部分, 如下:

declare @a varchar(20)
select @a = '67/6/7'
select convert(varchar, convert(int, substring(@a, 1, charindex('/', @a)-1))+1911)+ substring(@a, charindex('/', @a), 10)

其中最後一個 substring 中的取出長度 hardcode 寫 10 是一個小技巧, 不用真的算出取出長度, 而固定給一個大於此字串的長度, 就可以取出完整字串, 所以上面的結果就會是 1978/6/7, 而 substring 最後一個參數可以參考線上說明:

http://msdn.microsoft.com/en-us/library/ms187748.aspx

其中的 length_expression 參數的說明, 其中有提到

If the sum of start_expression and length_expression is greater than the number of characters in value_expression, the whole value expression beginning at start_expression is returned.

如此一來, 便可以一次在 sql command 中將原存在資料庫中的民國年日期轉出為西元年, 例如 table:

userid birthday
—————
1 67/3/12
2 79/2/11

語法如下:

select userid, birthday, convert(varchar, convert(int, substring(birthday, 1, charindex('/', birthday)-1))+1911)+ substring(birthday, charindex('/', birthday), 10) as birthday2 from table1

會取出:

userid birthday birthday2
————————
1 67/3/12 1978/3/12
2 79/2/11 1990/2/11

給大家參考!

分類
Database

找出SQL Server中的identity欄位值

一般在 SQL Server 中, 若要找某 table 中的 identity 最後值(最大值或目前值), 可以使用以下指令:

dbcc checkident('table_name', NORESEED)

可以參考之前的這篇文章: SQL Server的Identity欄位使用/複製/重設 – https://diary.tw/archives/457

不過, 若是 table 很多, 又想一次性的將各 table 中的 identity 欄位最後值找出, 可以利用系統資料表: sys.identity_columns (2005, 2008, 2008R2都有) 來查找, 配合 sys.objects 表, 可以一次將 table, column, 最後值(last_value) 查找出來, 如下:

select b.name, a.name, a.last_value from sys.identity_columns a inner join sys.objects b on a.object_id=b.object_id

這樣可以利用一個指令就將該資料庫中的所有資料表含有 identity 欄位的最後值, 若是只需要使用者自行定義的 table (不要含系統表), 可以多加上 b.type=’U’ 來進行過濾.

參考資料:
http://technet.microsoft.com/zh-tw/library/ms176057.aspx
http://msdn.microsoft.com/en-us/library/ms187334.aspx

分類
Database

MySQL的on duplicate key update語法

很有意思的一個語法, 不過可要小心使用, 在 mysql 5.0 版起, 可以在 insert 時, 指定若重覆 unique key 時, 則使用 update 語法, 這個有點像是在新增資料時, 若不存在則新增, 存在則 update 的方式.

來個例子:

CREATE TABLE `t3` (
`sid` INT NOT NULL ,
`v1` INT NOT NULL ,
`v2` INT NOT NULL ,
`v3` INT NOT NULL ,
PRIMARY KEY ( `sid` )
) 
insert into t3 values (1,1,1,1)
insert into t3 values (2,2,2,2)
insert into t3 values (2,2,2,2) on duplicate key update v1=v1+1

這時候, 會有一筆, 1,1,1,1 和 2,3,2,2 的資料產生, 如此一來, 可以在重覆 unique key 時, 用來做為更新的條件, 由於 unique key 只會出現一次在對應 table 上, 所以可以用來做為更新的條件值, 而 primary key 也是 unique key, 所以發生在 primary key 時是一樣的狀況.

不過若是新增的資料, 發生多筆 unique key 重覆時(指不同欄位), 該 update 會只更新一筆, 這是在程式上要避免發生的, 以免有資料更新錯誤的問題. 詳情可以參考官方網站資料: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

這種有趣的語法, 其實用的機會不是太多, 不過多學習了解很有意思.

分類
Database

FreeBSD上mysql的效能追蹤

最近因為有台主機的 mysql cpu 異常變高許多, 所以要來進行查詢問題所在.

因為 mysql 沒有像 mssql 的 sql profiler 那麼方便的工具, 所以利用 mysql 本身的 log 來進行, 也就是 slow query log 這個功能, 步驟如下:

1. 先將 /etc/my.cnf 中的 [mysqld] 內多加入下面資料:

log-slow-queries = /var/log/slow-query.log #slow query記錄檔的位置
long_query_time = 2 #query執行超過2秒時才記錄

2. 重啟 mysql 服務, 指令如下:

/usr/local/etc/rc.d/mysql-server restart

3. 執行一陣子後, 就可以看看該 log 檔內的 query , 接下來就是針對這些 query 來調整效能

以上是在 FreeBSD 環境下的作法. (其他環境其實也類似)

其實執行時間長不一定是效率不好, 不過若是常常發生的查詢是需要長時間的, 就有改善的必要, 簡單地說, 就是若一個查詢需要 5秒, 但一天跑不到 10次, 那根本不用管他, 不過若一個查詢需要 0.02 秒, 但一天要用到數萬次, 即使從 0.02 改善到 0.015 就會有很明顯的效能改善, 所以要看發生的頻率及所花費執行的時間, 平衡來看.

另外, 若是該 log 沒有產出, 記得權限要給對, 因為是 mysql service account 去執行寫入的動作, 就算沒有任何 log 也會有 mysql 啟動的資訊, 不會沒有任何產出的 log.

參考資料:
http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
http://blog.lansea-chu.com/index.php/archives/238
http://homeserver.com.tw/mysql/mysql%E7%9A%84%E6%9F%A5%E8%A9%A2%E6%99%82%E9%96%93log-slow-queries/
http://ezkuan.blogspot.com/2010/05/freebsd-mysql.html

分類
Database

MySQL的Log

在 MySQL 運行時, 若想要了解執行時期, 對資料庫下達指令的狀況, 可以利用 my.ini 中的參數來達成, 在 [mysqld] 中多加一行 log=路徑/檔名, 再重起服務即可.

這個目的對於在追蹤應用程式執行指令很有幫助, 不過若是繁忙系統下, 很可能會有大量的資料, 而難以閱讀, 建議使用在開發環境, 再執行對應的 application 記錄下來的 log 就會單純許多, 以方便追蹤.

在 MSSQL 中可以利用 SQL Profiler 來達成, 不過 MySQL 無法過濾那麼多資訊, 只能整個記錄下來. 記得這些操作對於系統效能都會有一定的衝擊, 開起來之後, 記得要關掉, 否則將會影響效能.

另外還有一個好用的參數, 就是一樣在 [mysqld] 中, 加一行 log_slow_queries=路徑/檔名, 這個將會讓系統記錄執行時間較長的 query, 也方便做效能調校及追蹤使用, 預設是執行時間超過 10秒的會被記錄下來, 參數是 long_query_time 可以參考:

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_long_query_time

這兩種方式分別做完整記錄及長時查詢記錄, 對於想要了解資料庫執行時期的問題及效能調校, 都可以提供不少幫忙.

相關閱讀:
http://blog.wabow.com/archives/54

分類
Database

使用TABLESAMPLE方式取樣

先弄清楚, 這個 TABLESAMPLE 是用來做以”頁(page)”為單位的取樣方式, 和所謂的隨機不同, 不過在超大型資料表上, 若要快速地取出一部分的資料, 可以應用這樣的方式來達成.

支援 MS SQL 2005 以上的版本, 使用方式很單純, 只需要在查詢指令的 table 之後加上 TABLESAMPLE 子句即可, 如下:

TABLESAMPLE [SYSTEM] (sample_number [ PERCENT | ROWS ] )
[ REPEATABLE (repeat_seed) ]

範例如下:
使用 percent百分比:

USE AdventureWorks2008R2 ;
GO
SELECT FirstName, LastName
FROM Person.Person
TABLESAMPLE (10 PERCENT) ;

或是指定筆數:

USE AdventureWorks2008R2 ;
GO
SELECT FirstName, LastName
FROM Person.Person
TABLESAMPLE (100 ROWS) ;

要特別注意的是, 由於是以 page 為單位, 所以取出的資料只是約略的 page 內容物組合而成的, 不過若是對於超大內容的 table 來說, 比較如下兩個指令:

SELECT TOP 100 * FROM TABLE1 ORDER BY NEWID()

SELECT TOP 100 * FROM TABLE1 TABLESAMPLE(1 PERCENT) ORDER BY NEWID()

效率上會有很大的差異(當然是後者會快很多), 不過若是有 WHERE 條件下, 就不適合使用 TABLESAMPLE 了, 因為是無法以指定的條件來取出資料, 但仍可以下達 WHERE 子句, 是指取出的 TABLESAMPLE 內容, 再過濾的資料, 如下:

SELECT * FROM TABLE1 TABLESAMPLE(1 PERCENT) WHERE FIELD1 > 5000

記得是先取出 sample data 後再過濾的行為, 所以極有可能沒有資料發生, 使用上要特別留意.

官方資料: http://technet.microsoft.com/zh-tw/library/ms189108.aspx
繼續閱讀: http://sharedderrick.blogspot.com/2011/04/tablesample.html

分類
Database

MySQL的Text欄位長度

MySQL 中, text 屬性的欄位, 用來存放文字的資料格式, 共區分為以下 4種, tinytext, text, mediumtext, longtext, 分別的長度大小為 2^8, 2^16, 2^24, 2^32 bytes 這麼大.

簡單地說, 分別就是 256bytes, 64kbytes, 16Mbytes, 4Gbytes, 在 MySQL 中的 text 有大小不同的限制呢, 所以一般若是只開 text 的話, 最大也只能存到 64kbytes 而已.

參考資料:
http://dev.mysql.com/doc/refman/5.5/en/storage-requirements.html
http://plog.longwin.com.tw/programming/2009/10/20/mysql-text-field-type-length-limit-2009

分類
Database

SQL Server Replication示意圖

SQL Server Replication (複寫), 也就是用來做資料庫內容同步的一種應用架構, 通常用來做備援或查詢主機應用, 剛在 MSDN 上看到一張說明的圖示很棒, 引用過來, 方便想了解 Replication 技術的朋友參考:

圖片引用自: http://msdn.microsoft.com/en-us/library/aa179423(SQL.80).aspx

其中很重要的觀念在於 snapshot 是做整個資料的複寫, 而 log reader 則是把 transcation log 讀出來存入 distribution database , 再對 subscriber 寫入, 而存在 distribution database 中的 transaction log 用來做資料同步後的持續異動記錄, 以繼續同步 subscriber 中的資料. 由這張圖上看就清楚很多了.