作者：陳雄華
概述
在設計庫表時，經常會碰到用于保存"時間值"的字段，如create_date，begin_time，login_time等，舉不勝舉。針對這些類型的字段，在設置數據類型時，有一個有趣的現象，即其中一些人使用Date類型，而另外一些人使用Char(8)/Char(14)類型。一般而言，初學者，在校學生，甚至老師一般都屬于前者，他們一個鮮明的特征是對數據庫的理論掌握很好，但普遍缺少實際項目的開發經驗；而后者一般是那些有一定項目經驗的開發人員。乍一看，這些時間值字段，用Date類型應該是合情合理，天經地義的，為什么有一定項目經驗的人偏偏要這樣"棄暗投明"，這樣"特立獨行"呢？
這是典型的白貓黑貓問題，理論化的東西很光鮮，但有時在實踐中就是不靈光，而一些"旁門左道"的東西卻顯得更加方便易用。本文將通過一個具體例子的不同開發過程，分析Char類型時間字段為什么在實踐中更受歡迎。考慮到篇幅所限，我們僅對Date類型和Char(8)類型的時間值字段作比較分析，對于Date類型和Char(14)類型的分析，相信大家完全可以由此而及彼。
1、比較的例子
我們設計了一個具體的實例，對用Char類型和Date類型的日期進行比較分析，使用的是Oracle數據庫，現對該實例進行簡單的描述。
假設有一個T_USER表，有一個EXPIRE_DATE（過期日期）字段，要求記錄年、月、日的日期數據，對EXPIRE_DATE字段分別采用兩種實現方式：
 圖 1 T_USER表 |
左邊的T_USER(1)使用CHAR(8)保存日期值，以yyyymmdd格式保存，如20070606，20070501；而右邊的T_USER(2)使用Date數據類型，我們稱左邊的數據表設計為CHAR類型日期方案，而右邊的設計為DATE類型日期方案。
表中的數據當然不會生而有之，我們假設從Web的表單上提交上來，保存到表中，當然還要有查詢、統計等操作，我們就通過這些常見的數據操作分析這兩個方案的不同，通過這樣的分析，孰劣孰優，相信我們就可以進行很好的判斷了。
??????? 2、從表單添加記錄的比較
Struts+Spring+Hibernate是目前Web項目中流行的框架，在這個框架中，Hibernate需要為T_USER生成一個User.java的PO，CHAR類型日期方案的User.expireDate為String類型，而DATE類型日期方案的User.expireDate為java.sql.Date類型，如圖 2所示：
 圖 2 兩方案分別對應的User.java PO |
而對應Struts的展現層，需要提供一個UserActionForm，以獲取頁面表單的提交數據。不管采用哪種日期方案，UserActionForm.expireDate屬性類型均為String，因為這樣一來，可以直接從Struts的獲取數據，另外也方便數據回顯到頁面中；如果UserActionForm.expireDate采用java.sql.Date類型，則標簽的數據將無法正確地填充UserActionForm.expireDate對象屬性中。
 圖 3 UserActionForm.java |
表單提交上來的expireDate是帶時間格式的字符串，如2006-06-06，2001-10-12，UserActionForm.expireDate簡單地接受該值，在UserAction中，必須用UserActionForm的數據生成持久層所需的PO，即User對象。兩種日期方案在數據的轉換邏輯的區別分別描述如下：
·CHAR類型日期
由于User.expireDate也是String類型，因此，僅需要將UserActionForm.expireDate屬性完全拷貝到User中，然后再將User.expireDate屬性的日期格式符"-"去除卻可，卻將2006-06-06轉換為20060606，對應操作邏輯的主要代碼如下：
1. User user = new User();
2. //將userActionForm中的數據拷貝到user對象中
3. BeanUtils.copyProperties(user, userActionForm);
4. //將日期格式符去除，得到數據庫存儲日期格式，如將2006-06-06轉換為20060606
5. user.setExpireDate(user.getExpireDate().replace("-",""));
6. …
7. //調用服務對象，將user保存到T_USER中
8. userService.save(user); |
·DATE類型日期
在DATE類型日期方案中，由于PO User.expireDate屬性為java.sql.Date，和UserActionForm.expireDate 存在類型的不匹配，因此需要通過一個轉換函數，將String日期轉換為java.sql.Date的日期。其主要代碼如下：
1. User user = new User();
2. //由于expireDate不能直接進行拷貝，因此需要逐一拷貝屬性
3. BeanUtils.copyPropertie(user, userActionForm,"userId");
4. BeanUtils.copyPropertie(user, userActionForm,"userName");
5. //使用轉換函數str2Date()將String類型的時間轉換為java.sql.Date的時間
6. java.sql.Date expireDate = str2Date(userActionForm.getExpireDate());
7. //設置expireDate屬性
8. user.setExpireDate(expireDate);
9. …
10. //調用服務對象，將user保存到T_USER中
11. userService.save(user); |
通過上面的比較，可以看出，使用DATE時間方案比使用CHAR時間方案在添加數據的處理上要復雜一些，表現在：
1) 由于屬性名相同而類型存在不可直接轉換的問題將導致無法進行對象間屬性批量拷貝，即BeanUtils. copyProperties()批量屬性拷貝函數會拋出異常，因此只能手工逐一進行單個具體屬性的拷貝，如果屬性個數很多，這一機械式的屬性拷貝代碼塊就要相應增大，不但使代碼顯示臃腫難看，而且直接降低了代碼的可維護性，因為一但因表字段名改變，就需要手工調整這段代碼。
2) 需要提供一個將String日期串轉換為java.sql.Date的轉換函數，將年、月、日時間域分別從字串中抽取出來，并轉換為int類型，然后利用java.sql.Date(int year,int month,int date)構造函數得到對應的java.sql.Date對象。
3、在數據查詢上的比較
假設需要以EXPIRE_DATE字段為條件查詢T_USER的記錄，由于已經在T_USER.EXPIRE_DATE字段上建立了索引，在查詢時需要考慮使用該索引。Web的查詢界面如下：
 圖 4 查詢界面 |
日期條件值可以是yyyy、yyyy-mm、yyyy-mm-dd的格式，假如開始日期為2001，結束日期為2002，則表示日期區間為2001-01-01到2002-12-31，如果開始日期為2001，結束日期為2002-02，則表示日期區別為2001-01-01到2002-02-28，以此類推。此外，如果開始日期條件未提供，表示查詢所有小于等于結束日期的記錄，反之如果結束日期條件未提供，表示查詢所有大于等于開始日期的記錄。
·CHAR類型日期
CHAR類型日期數據表保存的是CHAR(8)類型的日期，此時可以用簡單的方法構造出查詢語義豐富，語句結構統一的查詢SQL語句，構造方法如下：
1) 去除格式符：將開始，結束查詢日期值中的時間格式符去除。
2) 補尾串：將開始日期字符串末尾用0補齊到8位長度，將結束日期字符串末尾用9補齊到8位長度。特別的，如果開始日期為空，則用00000000代替，而結束日期未提供則用99999999代替。
3) 構造查詢SQL：用以下SQL語句構造查詢語句：
| select * from T_USER where EXPIRE_DATE between <開始日期> and <結束日期> |
表 1 CHAR類型日期查詢SQL結構
舉個例子：假如開始，結束日期值分別為2001、2002-02，則按以上三步的處理過程分別為：
1)去除格式符：2001，200202
2)補尾串：20010000，20020299
3)構造查詢SQL：
| select * from T_USER where EXPIRE_DATE between '20010000'and '20020299' |
又如開始日期為空，結束日期為200203，則對應的查詢SQL為：
| select * from T_USER where EXPIRE_DATE between '00000000'and '20020399' |
·DATE類型日期
由于DATE類型日期在數據庫表中對應的是Date類型字段，首先，我們不能仿照CHAR類型日期的查詢SQL結構構造如下的查詢SQL：
select * from T_USER where to_char(EXPIRE_DATE,'yyyymmdd') between <開始日期> and <結束日期>
因為在EXPIRE_DATE上建立了索引，如果在EXPIRE_DATE施加了to_char()的數據庫函數，就無法使用該索引，將引發一個全表描述。
所以，還得將開始、結束日期字符串用to_date()數據庫函數轉換為Date類型，如：
| select * from T_USER where EXPIRE_DATE between to_date(<開始日期>,'yyyymmdd') and to_date(<結束日期>,'yyyymmdd') |
表 2 CHAR類型日期查詢SQL結構
但由于使用了to_date字符串日期轉換函數，就必須保證開始日期和結束日期的字符串必須是語義合法日期字符串，如20010101,20020228，如果是語義錯誤的日期字符串，如20010000,20020299，to_date函數將發生轉換錯誤，致使上面的查詢SQL語句運行錯誤。因此，只有開始日期和結束日期字符串都合法時，才可以使用上式的查詢SQL。
如果開始或結束日期未精確到日，即只精通到年或月，如2001,200202，則在應用程序的服務層，必須對日期串進行語義分析，將其補齊到8位合法日期字符串，如開始日期字符串"2001"就必須補齊為"20010101"，而結束日期字符串"200202"就必須先補齊為"20020228"（非潤年的平月），而這一轉換邏輯處理起來是比較費神費力的，一不小心就可能引入一個Bug。
第二個麻煩的問題是，如果開始日期和結束日期為空，SQL語句又該如何構造呢？如果還按照表 2的SQL結構進行構造，那么就必須回答一個問題：最小開始日期和最大的結束日期分別是多少，因為你不能用"00000000"來代表最小的日期，也不能用"99999999"來代表最大的日期。
為了避免回答這個問題，就需要在開始日期和結束日期為空時分別采用不同結構的查詢SQL語句：
| select * from T_USER where EXPIRE_DATE >= to_date(<開始日期>,'yyyymmdd') |
表 3 結束日期條件值為空時
| select * from T_USER where EXPIRE_DATE <= to_date(<結束日期>,'yyyymmdd') |
表 4 開始日期條件值為空時
綜上所述，為了使用在EXPIRE_DATE字段上的索引，DATE類型日期在構造查詢SQL上明顯比CHAR類型日期復雜，具體表現在以下兩點：
1) 需要對日期條件值進行語義分析，以得到精確到日的語義合法的日期字符串。
2) 需要為開始/結束日期條件值均不為空，開始日期條件值為空，結束日期條件值為空三種情況分別構造不同結構的SQL語句，也構造SQL的程序必須對應一個分支
posted on 2006-10-13 13:45
★yesjoy★ 閱讀(609)
評論(0) 編輯 收藏 所屬分類:
數據庫設計