# 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。

#### &#x20;數字類型

| 類型           | 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（日）依此類推。&#x20;

| 類型           | bytes | 範圍                                      | 用途                          | 說明                                                                                                                                                                                                           |
| ------------ | ----- | --------------------------------------- | --------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| DATE         | 3     | 1000-01-01到9999-12-31                   | 以YYYY-MM-DD來儲存日期            | <p>MySQL接受以下的輸入方法：「2001/03/10」、「2001-3-10」、「01/3/10」、「2001\@3@10」、「20010310」，換句話說，只要可以辨認的分隔符號，都能成功的輸入到MySQL中。</p><p>當年份數字在00-69之間，則會被當作2000-2069，若是在70-99之間，則當作1970-1999！</p>                                |
| 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來記錄時間戳記      | <p>有些函數只接受TIMESTAMP的日期形態，因此，在某些情況下您是不得不用他的。</p><p>若是TIMESTAMP沒有特別去指定，則會以目前的時間來作記錄。</p>                                                                                                                       |
| TIME         | 3     | -838:59:59到838:59:59                    | 以hh:mm:ss來記錄時間              | <p>MySQL接受以下的輸入方法：「18:05:30」、「18.5.30」、「180530」，沒錯，只要是可以辨認的分隔符號，都能成功的輸入到MySQL中。</p><p>若是只輸入「2212」，那麼會被當成「00:22:12」，換言之，若有位數不足的情況下，MySQL會自動在前方補0，因此，若是您想輸入11時22分，那麼，您得寫成「112200」喔！不然，若只寫「1122」則會被當作11分22秒</p> |
| YEAR         | 1     | 1901到2155                               | 僅以YYYY來記錄年份                 | YEAR只能記錄年份...用在什麼地方呢？例如大範圍的生日年份調查統計...等。老實說，並不太常用。                                                                                                                                                           |

#### &#x20;文字類型資料：

| 類型         | bytes           | 範圍            | 用途               | 說明                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| ---------- | --------------- | ------------- | ---------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| CHAR(M)    | M位元組（隨M值大小來決定）  | 1<=M<=255     | 固定長度字串           | <p>CHAR固定長度字元，例如CHAR(4)則只能儲存4位元組（bytes）的資料，超出也不管...例如：CHAR(4)的欄位存入「abcdefg」的字串，則只剩下「abcd」四個字。所以哩！要用的時候，除非您確定字串不會超過某個範圍，例如IP都是固定15個位元組，在這種情形下再用CHAR吧！</p><p>請注意喔！CHAR(M)最多只能設到255。</p>                                                                                                                                                                                                                                                                                              |
| VARCHAR(M) | 視實際字串位元組大小+1    | 1<=M<=255     | 變動長度字串           | <p>VARCHAR就比CHAR有智慧一點了，例如：VARCHAR(6)的欄位遇到「abcdefg」的字串，那麼該欄位一樣只能存「abcdef」，而且，他還要多用一個位元組來儲存資料長度，導致儲存「abcdef」會用到7個位元組，這也就是為什麼他所用的空間大小是「視實際字串位元組大小+1」的原因了。那這個VARCHAR不就很糟糕？還要比CHAR多佔用一點空間？不！他有個優點，假設他遇到「abc」這樣的字串，那麼他會自動縮小所需空間，只要4個位元組就夠囉！如此，就省下空間啦！</p><p>VARCHAR (M)一樣最多也只能設到255。</p>                                                                                                                                                                                                 |
| 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          | <p>BLOB這種欄位格式可以用來儲存二進位的資料，例如圖像、音樂等，他和TEXT幾乎是一樣的功用！差別僅在於BLOB裡的資料是有分大小寫的，而TEXT裡的資料是不分大小寫。</p><p>BLOB和TEXT也都是和VARCHAR一樣，屬於可以自動判斷資料量而自動縮小其使用空間。例如您拿BLOB來存一個500個字元的資料，原本BLOB最大是可以存65535個位元組的，不過當他遇到這種情形時，他會自動縮小為502位元組而已（視實際內容所用之位元組大小+2），而不會真的用掉65535個位元組那麼多</p><p>BLOB或TEXT在ySQL 3.23以後版本中可以進行索引，不過再進行所以之前，您必須擷取前面幾個字元拿來當索引就好了！不然整個資料庫的速度可是會被拖垮的！因此，一般來說，我們也幾乎都不鼓勵用BLOB或TEXT來作為索引。</p><p>由於BLOB和TEXT值變化很大，尤其在常常刪減、更新的情形下，容易造成資料表資料破碎的情形產生，因此，我們應該定期地利用OPTIMIZE TABLE的指令，來讓資料表最佳化。</p> |
| BLOB       | 視實際內容所用之位元組大小+2 | 65535個字元      | 小型BLOB           |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| MEDIUMBLOB | 視實際內容所用之位元組大小+3 | 16777215個字元   | 中型BLOB           |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| LONGBLOB   | 視實際內容所用之位元組大小+4 | 4294967295個字元 | 大型BLOB           |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |

#### &#x20;特殊類型資料：&#x20;

| 類型   | bytes        | 範圍         | 用途   | 說明                                                                                                                                                                                                                  |
| ---- | ------------ | ---------- | ---- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| ENUM | 1或2位元組       | 最多65535個選項 | 單選選項 | <p>ENUM簡單講就是單選題，您自己預設一些內容，該欄位只能存入您所設定的內容之一，例如：男、女；低年級、中年級、高年級...等，這些固定且單一答案的選項，都適合用ENUM資料欄位。 </p><p>這種欄位是用機碼在運作的，因此速度相當快，所需空間小，而且不怕使用者亂填資料！因為使用者填寫的資料料若不在選項裡面，那他會當作是NULL值，因此，工友在此推薦，若您的欄位是固定選項、且單一資料，那一定要用ENUM。</p> |
| SET  | 1,2,3,4或8位元組 | 最多64個選項    | 複選選項 | SET和ENUM也差不多的意思，不過他可以複選，也就是同時可以儲存一個以上的資料項，例如：興趣調查、購物選購單...等。                                                                                                                                                        |

#### &#x20;建立資料表

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

   CREATE TABLE 資料表名稱 (

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

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

   ︴

   索引 欄位

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