:::

6-1 規劃資料庫欄位

  1. 資料有大有小!所以,我們也要規劃適當的欄位空間來存放資料!換句話說,「規劃欄位」就是選用適當的資料類型以及適當的大小空間來給資料存放!
  2. 不良的欄位規劃,可能會導致「浪費資料空間」、「拖慢資料庫執行速度」、「程式運算錯誤」...等情形。
  3. 資料有很多種形態,包括「文字」、「數字」、「日期」...等等,例如:以一個班級資料庫為例,學生學號是數字類型,姓名是文字類型,生日是日期類型...等,各種不同的資料,就要找出最適合它的資料類型。
  4. MySQL的資料形態可分為幾大類:數字、文字、日期時間、特殊類等資料形態。
  5. 底下的表格中,(M,D)中的M是顯示大小,最大為255,也就是設定欄位時,若寫int而已,那依內定可以顯示到10位數,若指定寫成int(8),則僅顯示8位數的數字。D則是小數位數。
  6. 若(M)或(M,D)是灰色,則表示可以不加,不加的話則以內定範圍為準。若是黑色的話就一定要加喔!
  7. unsigned表示正整數狀態,也就是沒有負數的狀態。
  8. zerofill表示位數不足補0,如int(4)話,且設為zerofill,則存28這個數字,資料庫會將之存成0028。

 數字類型

類型 bytes 範圍 選項 說明
TINYINT(M) 1 -128到127,unsigned狀態則為0到255 unsigned、zerofill TINYINT(非常小的整數)正整數僅到255,所以適合超小數值資料,例如:成績、座號、身高、體重...等資料。
SMALLINT(M) 2 -32768到32767,unsigned狀態則為0到65535 unsigned、zerofill SMALLINT(較小整數)正整數可以算到6萬左右的數字,適合小數值資料,例如:學校人數、萬元內的物品價錢...等。
MEDIUMINT(M) 3 -8388608到8388607,unsigned狀態則為0到16777215 unsigned、zerofill MEDIUMINT(中等整數)正整數可以計算到1600萬左右的數字,適合中型數值,例如:城市人口、土地大小、留言版的筆數序號...等。
INT(M) 4 -2147483648到2147483647unsigned狀態則為0到4294967295 unsigned、zerofill INT(標準整數)正整數已經可以用到42億左右的數字,適合用來做一國人口總數、公司營業額...等。
BIGINT(M) 8 -9223372036854775808到9223372036854775807unsigned狀態則為0到18446744073709551615 unsigned、zerofill 這個BIGINT(大整數)已經大到不像話了,正整數已經大到1800京左右了,工友想破頭也想不出要拿來記錄什麼非得用到這麼大不可,大概適用計算很精密的數字才用得到吧!
FLOAT(M) 4 FLOAT(M,D)最小非零值:±1.175494351E - 38FLOAT(4)最大非零值:±3.402823466E + 38FLOAT(8)最大非零值:±1.7976931348623157E + 308 zerofill FLOAT單精確度浮點數,能夠記錄小數點,例如精密的成績計算、各類數值記錄...等都相當適合!此外,FLOAT(4) 和FLOAT(8) 是為了與ODBC相容而提供的。
DOUBLE(M) 8 最小非零值:±2.2250738585072014E - 308 zerofill DOUBLE雙精度浮點數,和FLOAT差不多的用途,不過,DOUBLE所用掉的空間是FLOAT的兩倍,所以,除非特別需要高精度或範圍極大的值,一般來說用FLOAT來儲存資料應該是夠了。
DECIMAL(M,D) M 可變;其值的範圍依賴於M和D zerofill DECIMAL也是浮點數的一種,DECIMAL類型不同於FLOAT和DECIMAL,其中DECIMAL實際是以串存放的。DECIMAL可能的最大取值範圍與DOUBLE一樣,但是其有效的取值範圍由M和D的值決定。如果改變M而固定D,則其取值範圍將隨M的變大而變大。

日期類型資料:

底下的例子中,您會看到一堆的Y、M、D這些東西,Y代表的是年,YY代表2位數的年,如97年,YYYY代表4位數的年,如2001年,其餘的M(月)、D(日)依此類推。 

類型 bytes 範圍 用途 說明
DATE 3 1000-01-01到9999-12-31 以YYYY-MM-DD來儲存日期

MySQL接受以下的輸入方法:「2001/03/10」、「2001-3-10」、「01/3/10」、「2001@3@10」、「20010310」,換句話說,只要可以辨認的分隔符號,都能成功的輸入到MySQL中。

當年份數字在00-69之間,則會被當作2000-2069,若是在70-99之間,則當作1970-1999!

DATETIME 8 1000-01-01 00:00:00到9999-12-31 23:59:59 以YYYY-MM-DD hh:mm:ss來儲存日期時間 您在輸入DATETIME資料時,例如:2001年3月10日18時5分30秒,MySQL接受以下的輸入方法:「2001/03/10 18:05:30」、「2001-3-10 18+5+30」、「20010310180530」,換句話說,和DATE一樣,只要是可以辨認的分隔符號,都能成功的輸入到MySQL中。
TIMESTAMP(M) 4 1970-01-01 00:00:00到2037 以YYYYMMDDhhmmss來記錄時間戳記

有些函數只接受TIMESTAMP的日期形態,因此,在某些情況下您是不得不用他的。

若是TIMESTAMP沒有特別去指定,則會以目前的時間來作記錄。

TIME 3 -838:59:59到838:59:59 以hh:mm:ss來記錄時間

MySQL接受以下的輸入方法:「18:05:30」、「18.5.30」、「180530」,沒錯,只要是可以辨認的分隔符號,都能成功的輸入到MySQL中。

若是只輸入「2212」,那麼會被當成「00:22:12」,換言之,若有位數不足的情況下,MySQL會自動在前方補0,因此,若是您想輸入11時22分,那麼,您得寫成「112200」喔!不然,若只寫「1122」則會被當作11分22秒

YEAR 1 1901到2155 僅以YYYY來記錄年份 YEAR只能記錄年份...用在什麼地方呢?例如大範圍的生日年份調查統計...等。老實說,並不太常用。

 文字類型資料:

 

類型 bytes 範圍 用途 說明
CHAR(M) M位元組(隨M值大小來決定) 1<=M<=255 固定長度字串

CHAR固定長度字元,例如CHAR(4)則只能儲存4位元組(bytes)的資料,超出也不管...例如:CHAR(4)的欄位存入「abcdefg」的字串,則只剩下「abcd」四個字。所以哩!要用的時候,除非您確定字串不會超過某個範圍,例如IP都是固定15個位元組,在這種情形下再用CHAR吧!

請注意喔!CHAR(M)最多只能設到255。

VARCHAR(M) 視實際字串位元組大小+1 1<=M<=255 變動長度字串

VARCHAR就比CHAR有智慧一點了,例如:VARCHAR(6)的欄位遇到「abcdefg」的字串,那麼該欄位一樣只能存「abcdef」,而且,他還要多用一個位元組來儲存資料長度,導致儲存「abcdef」會用到7個位元組,這也就是為什麼他所用的空間大小是「視實際字串位元組大小+1」的原因了。那這個VARCHAR不就很糟糕?還要比CHAR多佔用一點空間?不!他有個優點,假設他遇到「abc」這樣的字串,那麼他會自動縮小所需空間,只要4個位元組就夠囉!如此,就省下空間啦!

VARCHAR (M)一樣最多也只能設到255。

TINYTEXT 視實際內容所用之位元組大小+1 255個字元 TINYTEXT 非常小的文本串 TEXT適用來儲存大容量資料的欄位,例如留言內容、文章內容等等。這一系列的TEXT除了空間大小不一樣以外,其餘的相同!由於TEXT和BLOB這種欄位實在是太像了,所以,我們也先來介紹一下BLOB再一併做比較。
TEXT 視實際內容所用之位元組大小+2 65535個字元 TEXT小文本串
MEDIUMTEXT 視實際內容所用之位元組大小+3 16777215個字元 MEDIUMTEXT中等文本串
LONGTEXT 視實際內容所用之位元組大小+4 4294967295個字元 LONGTEXT大文本串
TINYBLOB 視實際內容所用之位元組大小+1 255個字元 超小型BLOB

BLOB這種欄位格式可以用來儲存二進位的資料,例如圖像、音樂等,他和TEXT幾乎是一樣的功用!差別僅在於BLOB裡的資料是有分大小寫的,而TEXT裡的資料是不分大小寫。

BLOB和TEXT也都是和VARCHAR一樣,屬於可以自動判斷資料量而自動縮小其使用空間。例如您拿BLOB來存一個500個字元的資料,原本BLOB最大是可以存65535個位元組的,不過當他遇到這種情形時,他會自動縮小為502位元組而已(視實際內容所用之位元組大小+2),而不會真的用掉65535個位元組那麼多

BLOB或TEXT在ySQL 3.23以後版本中可以進行索引,不過再進行所以之前,您必須擷取前面幾個字元拿來當索引就好了!不然整個資料庫的速度可是會被拖垮的!因此,一般來說,我們也幾乎都不鼓勵用BLOB或TEXT來作為索引。

由於BLOB和TEXT值變化很大,尤其在常常刪減、更新的情形下,容易造成資料表資料破碎的情形產生,因此,我們應該定期地利用OPTIMIZE TABLE的指令,來讓資料表最佳化。

BLOB 視實際內容所用之位元組大小+2 65535個字元 小型BLOB
MEDIUMBLOB 視實際內容所用之位元組大小+3 16777215個字元 中型BLOB
LONGBLOB 視實際內容所用之位元組大小+4 4294967295個字元 大型BLOB

 特殊類型資料: 

類型 bytes 範圍 用途 說明
ENUM 1或2位元組 最多65535個選項 單選選項

ENUM簡單講就是單選題,您自己預設一些內容,該欄位只能存入您所設定的內容之一,例如:男、女;低年級、中年級、高年級...等,這些固定且單一答案的選項,都適合用ENUM資料欄位。 

這種欄位是用機碼在運作的,因此速度相當快,所需空間小,而且不怕使用者亂填資料!因為使用者填寫的資料料若不在選項裡面,那他會當作是NULL值,因此,工友在此推薦,若您的欄位是固定選項、且單一資料,那一定要用ENUM。

SET 1,2,3,4或8位元組 最多64個選項 複選選項 SET和ENUM也差不多的意思,不過他可以複選,也就是同時可以儲存一個以上的資料項,例如:興趣調查、購物選購單...等。

 建立資料表

  1. 建立資料表的方法如下:

    CREATE TABLE 資料表名稱 (

    欄位名稱1 資料類型 欄位設定選項,

    欄位名稱2 資料類型 欄位設定選項,

    索引 欄位

    ); 

  2. 一般來說,「欄位名稱」和「資料類型」都是必需的!至於「欄位設定選項」則是視情形存在。
  3. 常見的「欄位設定選項」有以下這些:
    • 空值設定:NULL(預設)、NOT NULL
    • 設定欄位的預設值:DEFAULT '預設內容'
    • 自動編號(加流水號):auto_increment
  4. 一個資料表只能有一個欄位使用auto_increment,而且此欄位必須是整數數值類型資料,而且這個欄位也必須設成索引!每次新增一筆資料,此欄位就會自動把編號加一。 
  5. 基本上,沒有索引的資料表運作起來效率上可能會差了上百倍!所以,在整個欄位都設定好了之後,通常我們會指定某個欄位為資料庫索引,以加快資料庫的讀取效率。 
  6. 索引有以下種類:
    • PRIMARY KEY: 主鍵索引,一個資料表也只能有一個,因此,通常都設給有auto_increment的欄位。
    • KEY 或 INDEX: KEY和INDEX相同,一個表可以有多個!
    • UNIQUE: 不重複索引或唯一索引,一個表可以有多個!PRIMARY KEY也是不重複索引,不過,它只能一個。

 

 


:::

搜尋

QR Code 區塊

https%3A%2F%2Fwww.tad0616.net%2Fmodules%2Ftad_book3%2Fpage.php%3Ftbsn%3D19%26tbdsn%3D475

書籍目錄

展開 | 闔起

線上使用者

72人線上 (19人在瀏覽線上書籍)

會員: 0

訪客: 72

更多…