按月-年關鍵面值分組儲存日期的最佳實踐
我正在製作一項需要保存按年月組合分組和計算的數據的服務。我知道如何計算數據並將其放在新表上。但是我很困惑應該使用哪種數據類型來儲存月 - 年值。這是我考慮過的。
- 兩個整數列,一個是一年,另一個是一個月(易於理解且易於操作(使用此表的任何人都可以輕鬆使用範圍和順序),但我認為這將比另一種解決方案佔用更多空間。
- 使用日期欄位,並始終為整個月儲存一個日期。(很難向使用者解釋它是如何工作的以及如何創建 WHERE 子句)
- 一個 varchar(7) 列並在那裡放置 2012-02,2013-01 之類的刺痛。對我來說,這很容易理解,但很難操作。
我應該選哪個?還是有其他解決方案。有人可以根據性能建議哪種方式是好的解決方案,因為大多數查詢將使用解決方案中的數據范圍,而我的新表將有大約 2-5 百萬條記錄。
我會使用帶有一個月第一天的日期欄位和一個 CHECK 約束來確保它保持在第一天。
這使其保持本機日期/時間格式(這是您對選項 3 的觀察)
選項 1 需要更少的儲存空間,但比較複雜。500 萬行並不多:您使用更少的儲存空間,但增加了程式碼和查詢的複雜性
我認為您應該根據需要檢索數據的方式選擇一個(或多個)選項。
對於 500 萬條記錄,空間真的不應該是您最關心的問題。對於您的每個選項,以下是檢索的優缺點:
(1) 兩個整數列: 如果您需要比較不同年份的月份,這是一種很好的方法。如果單獨索引年和月,按月提取和按年排序會快得多。如果這是使用數據的重要或頻繁模式,則選項 1 是最好的。另一方面,這種模式在提取年份和月份以外的範圍時很糟糕。例如,它不適用於跨年的範圍。當日期範圍跨越日曆年邊界時,WHERE 子句可能會變得比您希望的更複雜。(想想從 2011 年 11 月到 2012 年 2 月。)
(2) 日期欄位: 您和 gbn 都發現了這種格式的優點。它也適用於按時間順序排序和提取月份範圍。它恰好是最緊湊的表示(只有 3 個字節)。比較不同年份的月份並不好,也不適合在螢幕和報告中顯示。
(3) char(7) YYYY-MM 欄位: 請注意,如果您真的關心空間(並且您不應該在您的情況下),那麼您可以使用
char
而不是,varchar
因為每個項目的長度都是已知的。使用 YYYY-MM 有利於排序和過濾範圍。它不如 (2) 的空間好,但它更適合 WHERE 子句的顯示和簡單性 - 除非您需要跨年提取幾個月。您可能已經註意到 (1) 和 (2)/(3) 在您需要進行的提取的性質方面存在差異。如果您需要同時進行月/年和月範圍提取,那麼這些選項都不是完美的。如果是這種情況,我建議您考慮使用 (1) 和 (2) 或 (3) 的組合 - 我自己選擇 (3),因為我更看重顯示/使用的易用性而不是儲存空間。如果您確實使用了組合,請將其中一個或另一個作為計算列並將其編入索引以進行有效檢索。