從第一章看過來的各位可能已經等不及要操控資料庫了,但是先等等,必須先了解資料型態才能在建立資料表的時候知道我們需要什麼樣的資料欄位,在新增資料時也才能輸入對的資料。

資料型態

本篇章節介紹資料表欄位的資料型態,常見有整數、小數、文字、日期時間等,而整數裡面又分為好幾種整數,小數、文字等類型也一樣,會分這麼細是因為儲存空間與執行效能的考量。

NULL型態

NULL比較特別,以下每種欄位都可以設定是否允許為NULL,預設都是允許,否則要加上NOT NULL。在大部分的程式語言與資料庫軟體中NULL跟0都是有點不同的概念。以紀錄餘額的欄位來說,通常餘額為0代表有計算過餘額,但是金額是0。但是餘額為NULL可能代表餘額為0、還沒計算過餘額、根本沒有錢包等狀況。

當欄位允許為NULL的時候,以下說明的大小要加上1 Byte。

整數數字

數字型態分為有負號與無負號兩種,有上過計算機概論的話過該有提過。以整數INT為例,有負號的整數型態是是INT SIGNED,也可以直接稱為INT,而對應的無負號的整數型態叫做INT UNSIGNED。一個無負號整數,而且不允許為NULL的宣告為INT UNSIGNED NOT NULL

名稱大小儲存範圍無負號範圍
TINYINT1 Byte-128 到 1270 到 255
SMALLINT2 Byte-32768 到 327670 到 65535
MEDIUMINT3 Byte-8388608 到 83886070 到 16777215
INT4 Byte-2147483648 到 21474836470 到 4294967295
BIGINT8 Byte-9223372036854775808 到
9223372036854775807
0 到
18446744073709551615

為了讓從頭學起的人不要太無聊,我們稍微跳級一下。以下示範如何宣告一個Table,裡面有名為age的欄位,用來儲存年齡的訊息。我們不允許有人儲存NULL,存進去的一定要是數字,不填就帶入預設數字0。

我們分析了年齡的數字頂多到100出頭而已,使用TINYINT就足夠了。而且我們用不到0以下的數字來記錄年齡,以防有人超過127歲,我們使用無負號整數來儲存年齡。

CREATE TABLE user (
  age TINYINT UNSIGNED DOT NULL DEFAULT 0
);

上方的程式碼是用來建立資料表的指令,在後面的章節會有更完整的介紹。

有小數點的數字

需要紀錄小數點可以使用FLOAT跟DOUBLE,也就是IEEE754規格的浮點數,這種數字是為了科學運算而誕生的,允許非常小的一點誤差。如果要算錢之類的沒辦法允許這種誤差,可以使用DECIMAL,他有兩個參數,分別是總位數與小數點位數。例如一個百位數與小數點後兩位的數字(如100.32)需要宣告為DECIMAL(5, 2),不需要負數可以宣告為DECIMAL(5, 2) UNSIGNED

名稱大小儲存範圍
FLOAT4 Byte單精度浮點數
DOUBLE8 Byte倍精度浮點數
DECIMAL(M, D)M+2 ByteM最大為65

文字

文字大概可以分為三種類型,分別是CHAR、VARCHAR、TEXT系列。

名稱大小字數上限
CHAR(M)M Byte255 字(1 Byte的字)
VARCHAR(M)M+1 Byte (M <= 255)
M+2 Byte (M > 255)
65535 字,
UTF-8字集最多 21844 字
TINYTEXT(M)M+1 Byte255 字
TEXT(M)M+2 Byte65535 字
MEDIUMTEXT最高約16 MB16,777,215 字
LONGTEXT最高約4GB4,294,967,295 字
註:這邊的字都是1 Byte的情況,UTF8中有些字可能到3 Byte

有些人說CHAR跟VARCHAR的差別是VARCHAR會依照實際儲存的字數縮減尺寸,比較減少空間,但通常只有在儲存到硬碟的時候才是如此。資料庫管理系統啟動後會把資料從硬碟讀到記憶體中提高反應速度,大部分的資料庫引擎看到VARCHAR(10)只儲存一個字還是會佔據11 Byte(M+1 Byte)。因此使用上最主要的差別是客戶端讀取的CHAR(10)只儲存一個字時,資料庫系統會給出1個字+9個空格,而VARCHAR(10)只儲存一個字就只會回應一個字。

例如「https://klab.tw」這串字有15個字,使用CHAR(16)儲存的話,讀取時會得到"https://klab.tw ";使用VARCHAR(16)儲存,讀取時會得到"https://klab.tw"。因為VARCHAR儲存的時候會佔據M+1 Byte,多的那一個Byte用來記錄實際儲存了幾個字。

VARCHAR跟TEXT看起來也很像,兩者的差別主要是索引與效能的考量,通常比較常存取、搜尋的資料使用VARCHAR。但是MariaDB 10.4之後TEXT也可以建立索引,讓兩者的關係變小了些(關於索引會在之後章節教學)。

日期

分別有只紀錄年(YEAR)、只紀錄年月日(DATE)、紀錄時分秒毫秒(TIME)、日期與時間都紀錄(DATETIME),以及紀錄32 bit的UNIX時間(TIMESTAMP)。

名稱大小儲存範圍
YEAR1 Byte1901 到 2155
DATE3 Byte1000-01-01 到 9999-12-31
TIME3 Byte-838:59:59.999999 到 838:59:59.999999
DATETIME8 Byte1000-01-01 00:00:00.000000 到
9999-12-31 23:59:59.999999
TIMESTAMP4 Byte1970-01-01 00:00:01 到 2038-01-19 03:14:07

要注意TIMESTAMP會遇到2038年問題,因為4 Byte的儲存大小只能記錄到2038-01-19 03:14:07,因此建議使用DATETIME


MariaDB教學系列說明

  1. 資料庫系統介紹
  2. 我該使用MariaDB還是MongoDB?
  3. 觀念篇
    1. 資料庫與資料表介紹
    2. 資料型態
    3. 主鍵、唯一鍵、索引鍵、外來鍵
    4. 資料正規化
  4. 基本操作篇
    1. 建立資料庫與資料表
    2. 新增與查詢資料
    3. 修改與刪除資料
  5. 進階查詢篇
    1. Sort排序、Limit筆數限制
    2. 函數(Function)
    3. SQL子查詢
    4. JOIN合併查詢
  6. 群組查詢篇
    1. Group by分類
    2. Group函數
    3. Having查詢
  7. 進階操作篇
    1. 視圖(View)
    2. 交易(Transaction)
    3. 修改與刪除資料庫
    4. 修改與刪除資料表
    5. 資料庫權限管理
    6. 備份與還原資料庫

還沒有連結的就是還在編寫中…😅。