正确使用MySQL類型字段

字段類型

MySQL的字段類型大緻可以分為四種：1、數值類型 2、字元串類型 3、時間類型 4、複合類型

數值類型

MySQL的數值資料類型可以大緻劃分為兩個類别，一個是整數，另一個是浮點數或小數。

1bit 位 1位元組=8bit 1k=1024位元組 1M=1024k

類型 大小 範圍（SIGNED） 範圍（UNSIGNED） 用途

tinyint 1位元組 （-128，127） (0，255) 小整數值

smallint 2位元組 （-32768,32767） (0，65 535) 大整數值

mediumint 3位元組 (-8 388 608，8 388 607) (0，16 777 215) 大整數值

int/integer 4位元組 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整數值

bigint 8位元組 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 極大整數值

float 4位元組 (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 單精度浮點數值

double 8位元組 (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 雙精度浮點數值

decimal decimal（M,D），如果M>D則為M+2，否則為D+2，要求M>=D 依賴于M，D的值 依賴于M，D的值 精确數值，會計系統中的貨币資料

補充：有符号數與無符号數的差別？

最明顯的差別就是二者表示的範圍不同： 無符号數中，所有的位都用于直接表示該值的大小。

有符号數中最高位用于表示正負，是以，當為正值時，該數的最大值就會變小。有符号數高位段0代表正數，1代表負數。

舉一個1位元組的例子：

有符号：0111 1111 = 2^6+2^5+2^4+2^3+2^2+2^1+2^0 = 127; ==> 範圍是 -128 ~ 127

無符号：1111 1111 = 2^7+2^6+2^5+2^4+2^3+2^2+2^1+2^0 = 255；==> 範圍是 0 ~ 255

有符号數包括負數，無符号數隻有整數，在同一資料類型中，記憶體長度是一樣的。同樣一個位元組大小，有符号和無符号表示的範圍不同，但個數相同均為256個。

INT

在 MySQL 中支援的 5 個主要整數類型是 TINYINT，SMALLINT，MEDIUMINT，INT 和 BIGINT。這些類型在很大程度上是相同的，隻有它們存儲的值的大小是不相同的。

補充：int(1) 與 int(11) 的差別？

int(1)表示存儲1位數？int(11)表示存儲11位數？這是錯誤的！

int(M) 這裡的M代表的并不是存儲在資料庫中的具體的長度，以前總是會誤以為int(3)隻能存儲3個長度的數字，int(11)就會存儲11個長度的數字，這是大錯特錯的。

其實當我們在選擇使用int的類型的時候，不論是int(3)還是int(11)，它在資料庫裡面存儲的都是4個位元組的長度，在使用int(3)的時候如果你輸入的是10，會預設給你存儲位010,也就是說這個3代表的是預設的一個長度，當你不足3位時，會幫你補全，當你超過3位時，就沒有任何的影響。

我們在建表時可以選擇是否填充零，以下是Navicat for MySQL建表時可選功能。

FLOAT、DOUBLE 和 DECIMAL

MySQL 支援的三個浮點類型是 FLOAT、DOUBLE 和 DECIMAL 類型。FLOAT 數值類型用于表示單精度浮點數值， DOUBLE 數值類型用于表示雙精度浮點數值，DECIMAL 數值類型用于表示定點型數值。MySQL 浮點型和定點型可以用類型名稱後加（M，D）來表示，M表示該值的總共長度，D表示小數點後面的長度，M和D又稱為精度和标度。

與整數一樣，這些類型也帶有附加參數：一個顯示寬度訓示器和一個小數點訓示器(必須要帶有訓示器,要不然會查不到結果,并且寬度訓示器和XXint類型的寬度訓示器不同,這裡是有實際限制寬度的)。

FLOAT(7,3) 規定顯示的值不會超過 7 位數字(包括小數位，最大值表示為 9999.999 )，小數點後面帶有 3 位數字。對于小數點後面的位數超過允許範圍的值，MySQL 會自動将它四舍五入為最接近它的值，再插入它。

DECIMAL(M,D) （M是表示有效數字數的精度。 M範圍為1〜65。D是表示小數點後的位數。 D的範圍是0~30。MySQL要求D<=M）資料類型用于精度要求非常高的計算中，這種類型允許指定數值的精度和計數方法作為選擇參數。精度在這裡指為這個值儲存的有效數字的總個數，而計數方法表示小數點後數字的位數。比如語句 DECIMAL(7,3) 規定了存儲的值不會超過 7 位數字，并且小數點後不超過 3 位。和FLOAT一樣，對于小數點後面的位數超過允許範圍的值，MySQL 會自動将它四舍五入為最接近它的值，再插入它。

DOUBLE 類型和FLOAT類似，對于小數點後面的位數超過允許範圍的值，MySQL 會自動将它四舍五入為最接近它的值，再插入它。

UNSIGNED 和 ZEROFILL 修飾符也可以被 FLOAT、DOUBLE 和 DECIMAL 資料類型使用。并且效果與 INT 資料類型相同。

在實際開發中我們盡量不要使用FLOAT、DOUBLE，使用DECIMAL 更好！因為FLOAT和DOUBLE求SUM的結果都是不精确的。

字元串類型

MySQL 提供了 10 個基本的字元串類型，可以存儲的範圍從簡單的一個字元到巨大的文本塊或二進制字元串資料。

UTF-8：UTF-8一個漢字占用(3)個位元組，英文占用(1)個位元組。

GBK：GBK一個漢字占用(2)個位元組，英文占用(1)個位元組。

類型 大小 用途

char 0-255位元組 定長字元串

varchar 0-65535位元組 變長字元串

tinyblob 0-255位元組 不超過255個字元的二進制字元串

blob 0-65535位元組 二進制形式的長文本資料

tinytext 0-255位元組 短文本字元串

text 0-65535位元組 長文本資料

mediumblob 0-16777215位元組 二進制形式的中等長度文本資料

mediumtext 0-16777215位元組 中等長度文本資料

longblob 0-4294967295位元組 二進制形式的極大文本資料

longtext 0-4294967295位元組 極大文本資料

在實際開發中，我們建表都是類似 char(20) 那麼這括号中的20，是什麼意思呢？注意，這裡的20表示字元個數。

varchar(20)及char(20)都隻能存儲20個漢字或者隻能存儲20個字母或者隻能存儲漢字和字母的數量達到20的混合字。

時間類型

類型 大小（位元組） 範圍 格式 用途

date 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值

time 3 '-838:59:59'/'838:59:59' HH:MM:SS 時間值或持續時間

year 1 1901/2155 YYYY 年份值

datetime 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和時間值

timestamp 4 1970-01-01 00:00:00/2038 YYYYMMDD HHMMSS 混合日期和時間值，時間戳

DATE、TIME 和 YEAR 類型

MySQL 用 DATE 和 YEAR 類型存儲簡單的日期值，使用 TIME 類型存儲時間值。

DATETIME 和 TIMESTAMP 類型

除了日期和時間資料類型，MySQL 還支援 DATETIME 和 TIMESTAMP 這兩種混合類型。它們可以把日期和時間作為單個的值進行存儲。這兩種類型通常用于自動存儲包含目前日期和時間的時間戳，并可在需要執行大量資料庫事務和需要建立一個調試和審查用途的審計跟蹤的應用程式中發揮良好作用。

DateTime和TimeStamp最大的差別隻是用的場景不同，如果你的應用 是用于不同時區（就是國内和國外同時使用），這時候如果用dateTime就會出現各種各樣的問題，但是如果使用TimeStamp就不會出現這種時差的問題。

複合類型

MySQL 還支援兩種複合資料類型 ENUM 和 SET，它們擴充了 SQL 規範。雖然這些類型在技術上是字元串類型，但是可以被視為不同的資料類型。一個 ENUM 類型隻允許從一個集合中取得一個值，類似于單選按鈕的功能。例如一個人的性别從集合{'男','女'}中取值，且隻能取其中的一個值。；而 SET 類型允許從一個集合中取得任意多個值，類似于複選框的功能。例如一個人的興趣愛好可以從集合{'聽音樂','購物','看電影','打球','打遊戲'}中取值，且可以多取多個值。