6. 認識MySQL資料庫
6-1 規劃資料庫欄位
- 資料有大有小！所以，我們也要規劃適當的欄位空間來存放資料！換句話說，「規劃欄位」就是選用適當的資料類型以及適當的大小空間來給資料存放！
- 不良的欄位規劃，可能會導致「浪費資料空間」、「拖慢資料庫執行速度」、「程式運算錯誤」...等情形。
- 資料有很多種形態，包括「文字」、「數字」、「日期」...等等，例如：以一個班級資料庫為例，學生學號是數字類型，姓名是文字類型，生日是日期類型...等，各種不同的資料，就要找出最適合它的資料類型。
- MySQL的資料形態可分為幾大類：數字、文字、日期時間、特殊類等資料形態。
- 底下的表格中，(M,D)中的M是顯示大小，最大為255，也就是設定欄位時，若寫int而已，那依內定可以顯示到10位數，若指定寫成int(8)，則僅顯示8位數的數字。D則是小數位數。
- 若(M)或(M,D)是灰色，則表示可以不加，不加的話則以內定範圍為準。若是黑色的話就一定要加喔！
- unsigned表示正整數狀態，也就是沒有負數的狀態。
- 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也差不多的意思，不過他可以複選，也就是同時可以儲存一個以上的資料項，例如：興趣調查、購物選購單...等。 |
建立資料表
- 建立資料表的方法如下：
CREATE TABLE 資料表名稱 (
欄位名稱1 資料類型 欄位設定選項,
欄位名稱2 資料類型 欄位設定選項,
︴
索引 欄位
);
- 一般來說，「欄位名稱」和「資料類型」都是必需的！至於「欄位設定選項」則是視情形存在。
- 常見的「欄位設定選項」有以下這些：
- 空值設定：NULL（預設）、NOT NULL
- 設定欄位的預設值：DEFAULT '預設內容'
- 自動編號（加流水號）：auto_increment
- 一個資料表只能有一個欄位使用auto_increment，而且此欄位必須是整數數值類型資料，而且這個欄位也必須設成索引！每次新增一筆資料，此欄位就會自動把編號加一。
- 基本上，沒有索引的資料表運作起來效率上可能會差了上百倍！所以，在整個欄位都設定好了之後，通常我們會指定某個欄位為資料庫索引，以加快資料庫的讀取效率。
- 索引有以下種類：
- PRIMARY KEY： 主鍵索引，一個資料表也只能有一個，因此，通常都設給有auto_increment的欄位。
- KEY 或 INDEX： KEY和INDEX相同，一個表可以有多個！
- UNIQUE： 不重複索引或唯一索引，一個表可以有多個！PRIMARY KEY也是不重複索引，不過，它只能一個。
