MySQL資料類型——數字類型（整型、浮點型、BIT型）

一、數字類型概述

• MySQL的數字類型包括整數 、定點數 、浮點數 和位值 ，如下圖所示：

• 除BIT之外，其他的數類型可以帶有正負号，也可以不帶正負号。有一種特殊的屬性，它可以讓列自動生成一組有序的整數或浮點值。在你需要一組唯一辨別編号的時候，可以使用它。

二、數字類型分類

• MySQL的數字資料類型分為以下3大類：
  • 精确值類型，它包括整數類型和DECIMAL。
    • 整數類型用來存放沒有小數部分的數字。隻要是能用整數來表示的資料(如精确到謗的重量值 、家庭人口數或者庫存量)，都可以用一個整型列來儲存。
    • DECIMAL類 型可以儲存帶有小數部分的精确值 ，如3.14159、-.00273或-4.78。這種類 型非常适合用來儲存像币值之類的資料。整數和DECIMAL類型的值都會被精确地儲存起來 ，不會進行四舍五入；并且關于它們的計算也是精确的。
  • 浮點類型，它細分為單精度(FLOAT)和雙精度(DOUBLE)。這些類型和DECIMAL—樣 ，也可以用來存放帶有小數部分的數字，但它們存儲的是可能發生四舍五入的近似值，如3.9E+4或-0.1E-100。如果對數值精确度的要求不太嚴格，或者因為數值很大，以至于DECIMAL類型無法表示，那麼浮點類型則是個不錯的選擇。諸如糧食平均畝産、星球距離等類型的資料都很 适合使用浮點值來表示。
  • BIT類型，用于存儲位域值。
• 帶小數部分的值可以指派給一個整型列，但會使用“四舍五入”規則近似成整數：如果小部分大于等于0.5，則舍棄小數部分，在整數部分上加1(對于負數，則是減1)。類似地，整值也可以賦給允許 有小數部分的類型列，隻不過它們的小數部分等于零。

取值範圍與存儲空間

• 取值範圍如下：

• 存儲空間如下：

• M表示整型的最大顯示寬度、浮點類型和DECIMAL類型的精度(有效位數)，以及BIT型的位數 。對于那些帶有小數部分的資料類型，代表的是數學精确度(小數點後面的數字數)， 也稱為“ 小數精度”（scale）。
• 類型DECIMAL的存儲空間要求取決于小數點左右兩側的數字個數：
  • 對于每一側，每9位數字需要4個位元組，最後剩下的數字需要1〜4個位元組。
  • 每一個值的存儲空間都等于小數點左右兩邊位元組存儲占用總和，是小數點左右兩側數值所需存儲空間的總和。
• 一個BIT(M)值大約需要(M+7)/8個位元組的存儲空間。

三、精确值數字類型

• 精确值資料類型包括整數類型和定點DECIMAL類型。

整數類型

• 整數類型包括TINYINT、SMALLINT、MEDIUMINT、INT和 BIGINT。其中，INTEGER是 INT的同義詞。
• 可以将整型列定義為UNSIGNED，以禁止出現負數 ，這也将其取值範圍上移到了從0開始的區間。DECIMAL類型

• 在定義整型列時，你可以為它指定一個可選的顯示寬度M，該值必須是1〜255的整數。它決定着MySQL将用多少個字元來顯示該列裡的值。例如，MEDIUMINT(4)會指定一個顯示寬度為4個字元的MEDIUMINT列。
• 假設聲明如下的INT類型的字段：該字段指名，在year字段中資料一般隻顯示4位數字的寬度

CREATE TABLE student(
    year INT(4)
);           

• 注意：顯示寬度和資料類型的取值範圍和占用空間是無關的。
• 顯示寬度隻是指明MySQL最大可能顯示的數字個數：
  • 數值的位數小于指定的寬度時會由空格填充；
  • 如果插入了大于顯示寬度的值，隻要該值不超過該類型整數的取值範圍，數值依然可以插入，而且能夠顯示出來。例如，向上面的year字段中插入數值19999，當使用SELECT查詢該列值的時候，MySQL顯示的将是完整的帶有5位數字的19999，而不是4位數字的值

• 如果不指定顯示的寬度，則系統為每一種類型指定預設的寬度值。如下所示：

CREATE TABLE tmp1(
    x TINYINT,
    y SMALLINT,
    z MEDIUMINT,
    m INT,
    n BIGINT
);

DESC tmp1;           

• 從上圖可以看出，系統為每一種類型使用預設寬度，這些顯示寬度能夠保證顯示每一種資料類型可以取到取值範圍内的所有值

DECIMAL類型

• DECIMAL是一種定點類型，即所有值都有一個固定的小數位數。這 一點的重要性在于DECIMAL值不會像浮點數那樣存在四舍五入的問題——此特性使得DECIMAL非常适合于儲存貨币值 。
• NUMERIC和FIXED都是DECIMAL的同義詞。
• DECIMAL列也可以被定義成UNSIGNED。與 整數類型不同的是，把一個DECIMAL類型定義為UNSIGNED不會擴大該類型的取值範圍，而隻會“砍掉 ”整個負數部分。
• decimal(M,N)：在DECIMAL列的定義裡，可以包含一個最大有效位數M和一個小數位數D(即精度和小數位數 )。其中：
  • M的取值範圍是1〜65，D的取值範圍是0〜30，且不超過M。
  • M代表精度：表示總共的位數(不包括小數點)。N代表标度：小數的位數
  • 與浮點數類型一樣，如果使用者指定的數字超出了精度範圍，會進行四舍五入處理。
• 這裡的M和D都是可選的：
  • 如果省略了D，那麼其預設值為0。
  • 如果省略了M，那麼其預設值為10。
  • 換句話說，下列等式是成立的：

DECIMAL = DECIMAL(10) = DECIMAL(10,0)
DECIMAL(n)=DECIMAL(n,0)           

• DECIMAL類型的最大取值範圍取決于M和D的值：
  • 如果在D保持不變的情況下調整M，則取值範圍将随着M的變大而增加（如下圖所示）。
  • 如果在M保持不變的情況下調整D，則取值範圍将随着D的變大而減小（如下圖所示）。

四、近似值數字類型

• MySQL提供了兩種浮點類型：FLOAT和DOUBLE，用于存儲近似值數。
• DOUBLE PRECISION是 DOUBLE的同義詞 。在預設情況下，RAEL類型是DOUBLE的同義詞；如果啟用了SQL的REAL_AS_DEFAULT模式，則它會成為FLOAT的同義詞 。
• 浮點類型也可以定義為UNSIGNED。這樣，該類型的負數部分将被“砍掉”。
• 與DECIMAL類型相似，在浮點類型列的定義裡，可以包含一個最大有效位數M和一個小數位數 D(即精度和小數位數)：
  • 其中，M的取值範圍是1-255，D的取值範圍是0~30，且不超過M。
  • 這裡的M和D都是可選的。如果在定義列時省略了它們，那麼這些值會按你的硬體所支援的最大精度來存儲。
• 使用FLOAT(p)文法也是可以的。不過，這裡的p在标準SQL裡表示的是所要求的精度位數，這點與 MySQL的處理方式有所差異：
  • p的取值範圍是0~53，并且隻能用于确定此列存儲的是單精度值 ，還是雙精度值。
  • 如果p值落在0〜24的區間内 ，則此列會被視為單精度列，如果落在25~53的區間 内 ，則此列将被視為雙精度列。
  • 也就是說 ，此列會根據是否帶有用M和D值，來确定是FLOAT類型，還是DOUBLE類型。
• 注意事項：
  • 插入資料時，如果值越界了，會報錯。
  • 插入資料時，如果值沒有越界，但是小數越界了，會進行四舍五入處理。

五、BIT類型

• BIT資料類型可用于存放位域值。BIT列的定義裡可以包含一個可選的最大寬度M，它表示的是按二進制位計算列的“寬度”。M的取值必須是一個1~64的整數。如果省略，M的預設值将為1。
• BIT不支援UNSIGNED。
• 在預設情況下，從BIT列檢索出來的值不能列印顯示。要顯示一個位域值的可列印形式，需要加上零或是使用CAST()函數：

CREATE TABLE t(
    b BIT(3)
);
INSERT INTO t(b) VALUES(0),(b'11'),(b'101'),(b'111');
SELECT b, b+0, CAST(b AS UNSIGNED) FROM t;           

• 如果需要以二進制表示法來顯示某些位域值或是它們以二進制形式計算後的結果，那麼可以使用BIN()函數：

SELECT BIN(b),BIN(b & b'101'), BIN(b | b'101') FROM t;           

• 對于八進制和十六進制的顯示，可以分别使用函數OCT()和HEX()。

六、數字資料類型的屬性

• UNSIGNED屬性可以防止出現負值。讓一個整型列具有UNSIGNED屬性，并不會改變底層資料類型的取值範圍的寬度，而隻是把這個範圍向右平移。
• 請看下面這個表聲明：itiny和itiny_u都是範圍跨度為256的TINYINT列，隻是允許的值集不一樣 。itiny的取值範圍是-128~127，而 itiny_u的取值範圍則左移到了0~255。

CREATE TABLE mytbl(
    itiny   TINYINT,
    itiny_u TINYINT UNSIGNED
);           

• 如果資料不可能出現負數值(如人口統計數字或觀衆人數 )，則應考慮給相應的整型列加上UNSIGNED屬性。如果用一個帶正負号的列類型來儲存這類資料，則隻能使用到該資料類型的一半取值範圍 。在為該列加上UNSIGNED屬性之後，可用範圍則立刻會增加一倍。例如，想用某列來 儲存序列編号 ，那麼在加上UNSIGNED屬性之後，可用編号的長度會翻 一倍。
• 你也可以為DECIMAL或浮點列加上UNSIGNED屬性，但最終效果與整型列的情況稍有不同。具體表現在：浮點值的取值範圍不會朝正數方向平移；相反，原取值範圍的上端将保持不變，而下端會變成0。

SIGNED屬性

• 所有支援UNSIGNED屬性的數字類型，也都可以用SIGNED屬性。
• 因為這些類型在預設情況下都是帶符号的，是以即使為允許使用負數的列加上SIGNED屬 性，也沒有什麼效果。

ZEROFILL屬性

• ZEROFILL屬性适用于除BIT以外的所有數字類型。它會在列裡的顯示值前面填充若幹0，使其寬度最終達到顯示寬度。
• 當你想要讓列值按指定的顯示寬度進行顯示時，就可以使用ZEROFILL。确切來講，顯示寬度其實是“一個給定的最小數字個數 ”，因為那些長度大于顯示寬度的數值，會全部顯示出來，而不會被截斷。
• 示範案例：請注意，最後那個值是完整顯示的，因為它比列的顯示寬 度寬。

CREATE TABLE mytbl(my_zerofill INT(5) ZEROFILL);
INSERT INTO mytbl VALUES(1),(100),(10000),(1000000);
SELECT my_zerofill FROM mytbl;           

• 如果給某個列指定了ZEROFILL屬性，那麼它将自動轉換成一個UNSIGNED列。

AUTO_INCREMENT

• 另外一個屬性是AUTO_INCREMENT，它可用于整數或浮點數類型(不過，用于浮點數類型的情況比較少見 )。當想要生成一組唯一辨別符或序列值時，可以指定AUTO_INCREMENT屬性。
• 當把NULL插到AUTO_INCREMENT列時，MySQL會自動生成下一個序列值，并把它存儲 到該列中。通常情況下，在沒有做出另外設定的時候，AUTO_INCREMENT的值将從1開 始，每新增一行，序列值也加1。如果在表裡删除了某幾行，那麼這個序列可能也會受到影響 。也就是說， 序列值可能會被再次使用，但是這種情況是否會發生，還要看具體的存儲引擎。
• 每個表最多隻能有一個 AUTO_INCREMENT列：
  • 該類型的列應該具備NOT NULL限制，并且必須被索引。一般情況下，最好能把AUTO_INCREMENT列聲明為一個 PRIMARY KEY或UNIQUE索引。
  • 此外，因為序列值不可能是負數 ，是以通常還應該為該列加上UNSIGNED屬性。
  • 例如，可以用下列任意一條語句來聲明一個AUTO_INCREMENT列：

CREATE TABLE ai(i INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE ai(i INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE);
CREATE TABLE ai(i INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(i));
CREATE TABLE ai(i INT UNSIGNED NOT NULL AUTO_INCREMENT, UNIQUE(i));           

• 其中，前面兩種形式将索引資訊指定為列定義的一部分。緊接着的兩種形式将索引指定為 CREATETABLE語句的一個獨立子句。如果這個索引隻包含AUTO_INCREMENT列，那麼 使用獨立子句的方式則是可選的。如果想要建立一個涉及多個列且包含AUTO_INCREMENT列的索引，那麼必須使用獨立子句。更多相關示例會在後面"資料類型之處理序列中介紹"。

• 以上介紹 的各種屬性都是數字類型列所特有的。在這些屬性後面，你可以繼續指定NULL或 NOT NULL屬性。如果沒有為數字類型列指定NULL或NOT NULL，那麼它将預設允許使用NULL值(通常情況是這樣。不過，如上所述，AUTO_INCREMENT列預設使用的是NOT NULL)。
• 你還可以利用DEFAULT屬性來指定預設值。下面這個表包含了3個INT列，它們的預設值分别為-1、1和NULL：

CREATE TABLE t(
    i1 INT DEFAULT -1,
    i2 INT DEFAULT 1,
    i3 INT DEFAULT NULL
);           

• 如果列定義沒有包含DEFAULT子句，那麼MySQL将為它選擇一個預設值。

七、選擇數字資料類型

• 在為數字列選擇資料類型時，需要考慮資料的取值範圍 ，并選擇一個能夠覆寫該範圍的最小類 型。選擇較大的類型，會浪費存儲空間，會導緻表毫無必要地變得很大，也會導緻在處理這樣的表時，無法像處理選擇了更小類型的表那樣有效率。
  • 如果資料的取值範圍很小(如人的年齡 或兄弟姐妹人數 )，則選擇TINYINT類型是最佳的。
  • MEDIUMINT類型能夠表示上百萬的值，它可用于很多類型的資訊，隻是在存儲空間方面會有些額外的開銷。
  • BIGINT類型的取值範圍最大，但它占用的存儲空間是最小整數類型(INT)的兩倍，是以應該隻在真的需要時才使用它。
  • 對于浮點值，DOUBLE類型所占用的存儲空間是FLOAT類型的兩倍。是以，隻有在需要非常高的精确度或者資料的取值範圍非常大的情況下，才需要使用DOUBLE來代替FLOAT；否則 ，會浪費掉一半的存儲開銷。
• 每個數字列的取值範圍都由其類型決定。如果想要把某個超出了列的取值範圍的值插入這個列裡，那麼最終的結果将取決于是否已啟用嚴格的SQL模式。
  • 如果已啟用，則取值超出範圍時将導緻一個錯誤 。
  • 如果未啟用，則會進行截斷：MySQL會先把這個值替換為該列的取值範圍的相應端點值 ，然後将處理後的結果值插入列中，同時生成一條警告消息。
• 值的截斷處理依據的是資料類型的取值範圍 ，而不是它的顯示寬度。例如，SMALLINT(3) 列的顯示寬度隻有3個字元，但它的取值 範圍 卻為-32768~32767。因為數值12345比列的顯示寬度更寬 ，但仍在該 列的取值範圍内 ，是以不需要對它進行截斷即可插入它，而且在檢索時得到的結 果也是12345。對 于數 值 99999，由于它超出了該 列的取值範圍，是以它在插入時會被截斷為32767。以後的檢索結果也會是32767。
• 對于定點或浮點列，如果存儲值的小數位數多于該列定義所規定的值 ，那麼會出現取舍情況 。例如，把 1.23456插到一個FLOAT(8, 1)列裡，其結果将是1.2。如果把這個值插到一個FL0AT(8, 4)列裡，其結果将是1.2346。也就是說，必須根據自己對精确度的要求來標明一個小數點後面的位數 。如果需要精确到千分之一，則不能隻把小數點後面的位數聲明為2位。
• FLOAT和DOUBLE在不指定精度時，預設會按照實際的精度（由計算機硬體和作業系統決定），DECIMAL如不指定精度，預設為(10,0)。

示範案例

• SQL如下：

CREATE TABLE tmp2(
    x FLOAT(5,1),
    Y DOUBLE(5,1),
    Z DECIMAL(5,1)
);
INSERT INTO tmp2 VALUES(5.12,5.12,5.12);
SHOW WARNINGS;           

• 可以看到，在執行INSERT的時候MySQL給出了一個警告，接着檢視警告，顯示FLOAT和DOUBLE在四舍五入時沒有警告，而DECIMAL類型給出了警告。
• 檢視結果：

SELECT * FROM tmp2;