本篇簡單說明記帳簿專案資料表常使用的MySQL欄位資料型態。請先參考使用SpringBoot打造記帳簿專案(七)建立MySQL EER Diagram。
在上一篇使用SpringBoot打造記帳簿專案(七)建立MySQL EER Diagram。中設計了會員member及消費記錄expense_record兩張資料表，分別使用了下列資料型態。
INTBIGINTVARCHARDATETIMESTAMP
以下說明各型態的特色。
INT
INT為整數型態，屬於數字型態(Numeric Type)的一種，專門用來儲存整數。INT也可寫成INTEGER，兩者是相同的。
一個INT欄位的儲存空間為4位元組(Bytes)，而1 Bytes為8 bit，所以4 Bytes等於32 bits。
包含正負符號(SIGNED)的INT的可儲存數值範圍為
-2147483648到2147483647
不包含正負符號(UNSIGNED)的INT的可儲存數值範圍為
0到4294967295。
如果存入值的範圍超過範圍，MySQL會丟出Out of range錯誤。
INT使用在消費記錄expense_record的消費金額expense_amount欄位，用來儲存每一筆消費的金額。因為幾乎不會有人有一次消費二十億以上的金額，所以用INT來儲存消費金額是措措有餘了。
另外你可能會看到類似INT(8)後面有數字的設定，其實數字參數本身並不影響儲存空間的大小，INT(4)和INT(8)都一樣占用4 Bytes的空間，擁有相同的數值範圍，差別只在當欄位設定ZEROFILL時前面補0的長度。
BIGINT
BIGINT與INT類似同屬數字型態(Numeric Types)的一種，但可儲存的範圍更大，當然占用的儲存空間也更大。
一個BIGINT欄位的儲存空間為8 Bytes，也就是64 bits。
包含正負符號(SIGNED)的BIGINT的可儲存數值範圍為
-263(-9223372036854775808)到263-1(9223372036854775807)。
不包含正負符號(SIGNED)的BIGINT的可儲存數值範圍為
0到264-1(18446744073709551615)。
在計帳簿專案中BIGINT用在資料表的主鍵(PK)欄位id，並搭配設定AUTO_INCREMENT，作用為當新增一筆資料時，該欄位的值會自動加1。所以新增第一筆消費記錄時，expense_record.id的值為1，第二筆則為2，以此類推，因此每一筆消費記錄都以id來區分彼此不會有重複的情況發生。
會設計為BIGINT的原因是，每一個會員的消費記錄都儲存在同一張消費記錄資料表expense_record，一個會員的消費記錄一年約在3,000筆，二十年就是60,000筆，假設有一百萬名會員，則十年的消費記錄將會有60,000,000,000(六百億)筆資料，如果使用INT很快就會超過範圍。又id欄位僅是記錄序號，並不需要負數，所以設定了UNSIGNED，也就是不用考慮負數，因此可儲存的值最多到9223372036854775807，可以夠用上好一陣子了。即使你的會員數來到一億人，而且記錄了一百年的消費記錄也還是足夠。
VARCHAR
VARCHAR屬於字串資料型態(String Type)。VARCHAR後的數字代表可儲存的字元(Character)長度（在MySQL 4.1以前為Byte）。
The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters. (Before MySQL 4.1, the length is interpreted as number of bytes.)
在MySQL 5.0.3之前的版本最長為255個字元(character)(VARCHAR(255))，在之後的版本最長限制為65535個字元(VARCHAR(65535))。不過VARCHAR實際可設定的長度會小於65535 Bytes，因為MySQL一列(row)全部欄位資料最多限制在65535 Bytes，又每個字元占用的空間大小是依使用的字元集(Character Set)而有不同，例如MySQL若要儲存中文通常會使用utf8mb4 Character Set，而utf8mb4儲存一個字佔4 Bytes，其他影響最大長度限制的因素還包括選用的MySQL引擎（例如常見的MyISAM或InnoDB），row的format等，所以VARCHAR可設定長度最大限制是不定的。不過在記帳簿專案都不會用到VARCHAR長度上限，所以大概知道就好。
VARCHAR專門用來儲存文字，所以如會員帳號(email)member_account，會員名稱member_name，會員密碼member_password，消費項目expense_item等文字欄位皆使用VARCHAR來儲存，並依需要設定不同的可儲存長度範圍。
DATE
DATE屬於日期時間格式(Date and Time Types)。
DATE只儲存日期資訊，不儲存時間資訊。MySQL以'YYYY-MM-DD'格式來顯示日期。可儲存的日期範圍為1000-01-01到9999-12-31。
在記帳簿案中的消費日期expense_date以DATE來儲存消費的日期。不紀錄時間的原因是一般人很少在消費發生的當下立刻紀錄，通常都是一天結束後才根據當日的發票或記憶來紀錄當天的消費，因此就沒有必要去記錄到時間單位那麼細的情況。
此外事後在進行消費分析時，也多只會分析到一段日期，例如可能想知道這個月，或這一季，或半年來的消費總和，而不會去關心一天內不同時間的消費趨勢，所以記錄時間就沒有太大的意義。
DATETIME
DATETIME也是屬於日期時間格式(Date and Time Types)。
原本我是用TIMESTAMP，但發現MySQL的TIMESTAMP最大只到2038-01-19 03:14:07.999999，所以應改為DATETIME，其範圍為1000-01-01 00:00:00.000000到9999-12-31 23:59:59.999999。
DATETIME儲存的時間精確到小數點後6位數，且支援新增或修改資料時自動更新。
在記帳簿專案中的每個資料表基本上都會有create_time及update_time兩個欄位，分別記錄一筆資料新增及修改時的時間，使用DATETIME格式來儲存。
參考：
沒有留言:
張貼留言