mysql字段類型有哪些

mysql字段類型有5種：1、整數類型，主要用來存儲整數數字，包括INT、BIGINT、TINYINT等；2、浮點數和定點數類型，用于存儲小數，浮點數有FLOAT和DOUBLE，定點數有DECIMAL；3、字符串類型，用來存儲字符串數據等。

本教程操作環境：windows7系統、mysql8版本、Dell G3電腦。

數據庫中的每個字段都應該有適當的數據類型，用于限制或允許該列中存儲的數據。例如，字段中存儲的為數字，則相應的數據類型應該為數值類型。

如果使用錯誤的數據類型可能會嚴重影響應用程序的功能和性能，所以在設計表時，應該特別重視數據列所用的數據類型。更改包含數據的列不是一件小事，這樣做可能會導致數據丟失。因此，在創建表時必須為每個列設置正確的數據類型和長度。

MySQL 字段的數據類型有大概可以分為 5 種，分別是整數類型、浮點數類型和定點數類型、日期和時間類型、字符串類型、二進制類型等。

注意：整數類型和浮點數類型可以統稱為數值數據類型。

1、整數類型

整數類型又稱數值型數據，數值型數據類型主要用來存儲整數數字。

MySQL?提供了多種數值型數據類型，不同的數據類型提供不同的取值范圍，可以存儲的值范圍越大，所需的存儲空間也會越大。

MySQL 主要提供的整數類型有?TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，其屬性字段可以添加 AUTO_INCREMENT 自增約束條件。下表中列出了 MySQL 中的數值類型。

從上表中可以看到，不同類型的整數存儲所需的字節數不相同，占用字節數最小的是 TINYINT 類型，占用字節最大的是 BIGINT 類型，占用的字節越多的類型所能表示的數值范圍越大。

根據占用字節數可以求出每一種數據類型的取值范圍。例如，TINYINT 需要 1 個字節（8bit）來存儲，那么 TINYINT 無符號數的最大值為 28-1，即 255；TINYINT 有符號數的最大值為 27-1，即 127。

2、浮點數和定點數類型

MySQL?中使用浮點數和定點數來表示小數。

浮點類型有兩種，分別是單精度浮點數（FLOAT）和雙精度浮點數（DOUBLE）；定點類型只有一種，就是?DECIMAL。

浮點類型和定點類型都可以用(M, D)來表示，其中M稱為精度，表示總共的位數；D稱為標度，表示小數的位數。

浮點數類型的取值范圍為 M（1～255）和 D（1～30，且不能大于 M-2），分別表示顯示寬度和小數位數。M 和 D 在 FLOAT 和DOUBLE 中是可選的，FLOAT 和 DOUBLE 類型將被保存為硬件所支持的最大精度。DECIMAL 的默認 D 值為 0、M 值為 10。

下表中列出了 MySQL 中的小數類型和存儲需求。

DECIMAL 類型不同于 FLOAT 和 DOUBLE。DOUBLE 實際上是以字符串的形式存放的，DECIMAL 可能的最大取值范圍與 DOUBLE 相同，但是有效的取值范圍由 M 和 D 決定。如果改變 M 而固定 D，則取值范圍將隨 M 的變大而變大。

從上表中可以看到，DECIMAL 的存儲空間并不是固定的，而由精度值 M 決定，占用 M+2 個字節。

FLOAT 類型的取值范圍如下：

• 有符號的取值范圍：-3.402823466E+38～-1.175494351E-38。

• 無符號的取值范圍：0 和 -1.175494351E-38～-3.402823466E+38。

DOUBLE 類型的取值范圍如下：

• 有符號的取值范圍：-1.7976931348623157E+308～-2.2250738585072014E-308。

• 無符號的取值范圍：0 和 -2.2250738585072014E-308～-1.7976931348623157E+308。

提示：不論是定點還是浮點類型，如果用戶指定的精度超出精度范圍，則會四舍五入進行處理。

FLOAT 和 DOUBLE 在不指定精度時，默認會按照實際的精度（由計算機硬件和操作系統決定），DECIMAL 如果不指定精度，默認為（10，0）。

浮點數相對于定點數的優點是在長度一定的情況下，浮點數能夠表示更大的范圍；缺點是會引起精度問題。

最后再強調一下：在 MySQL 中，定點數以字符串形式存儲，在對精度要求比較高的時候（如貨幣、科學數據），使用 DECIMAL 的類型比較好，另外兩個浮點數進行減法和比較運算時也容易出問題，所以在使用浮點數時需要注意，并盡量避免做浮點數比較。

3、日期/時間類型

MySQL?中有多處表示日期的數據類型：YEAR、TIME、DATE、DTAETIME、TIMESTAMP。當只記錄年信息的時候，可以只使用 YEAR 類型。

每一個類型都有合法的取值范圍，當指定確定不合法的值時，系統將“零”值插入數據庫中。

下表中列出了 MySQL 中的日期與時間類型。

YEAR 類型

YEAR 類型是一個單字節類型，用于表示年，在存儲時只需要 1 個字節。可以使用各種格式指定 YEAR，如下所示：

• 以 4 位字符串或者 4 位數字格式表示的 YEAR，范圍為 ‘1901’～’2155’。輸入格式為 ‘YYYY’ 或者 YYYY，例如，輸入 ‘2010’ 或 2010，插入數據庫的值均為 2010。

• 以 2 位字符串格式表示的 YEAR，范圍為 ’00’ 到 ’99’。’00’～’69’ 和 ’70’～’99’ 范圍的值分別被轉換為 2000～2069 和 1970～1999 范圍的 YEAR 值。’0′ 與 ’00’ 的作用相同。插入超過取值范圍的值將被轉換為 2000。

• 以 2 位數字表示的 YEAR，范圍為 1～99。1～99 和 70～99 范圍的值分別被轉換為 2001～2069 和 1970～1999 范圍的 YEAR 值。注意，在這里 0 值將被轉換為 0000，而不是 2000。

提示：兩位整數范圍與兩位字符串范圍稍有不同。例如，插入 3000 年，讀者可能會使用數字格式的 0 表示 YEAR，實際上，插入數據庫的值為 0000，而不是所希望的 3000。只有使用字符串格式的 ‘0’ 或 ’00’，才可以被正確解釋為 3000，非法 YEAR值將被轉換為 0000。

TIME 類型

TIME 類型用于只需要時間信息的值，在存儲時需要 3 個字節。格式為 HH:MM:SS。HH 表示小時，MM 表示分鐘，SS 表示秒。

TIME 類型的取值范圍為 -838：59：59～838：59：59，小時部分如此大的原因是 TIME 類型不僅可以用于表示一天的時間（必須小于 24 小時），還可能是某個事件過去的時間或兩個事件之間的時間間隔（可大于 24 小時，或者甚至為負）。

可以使用各種格式指定 TIME 值，如下所示。

• ‘D HH：MM：SS’ 格式的字符串。還可以使用這些“非嚴格”的語法：’HH：MM：SS’、’HH：MM’、’D HH’ 或 ‘SS’。這里的 D 表示日，可以取 0～34 之間的值。在插入數據庫時，D 被轉換為小時保存，格式為 “D*24+HH”。

• ‘HHMMSS’ 格式、沒有間隔符的字符串或者 HHMMSS 格式的數值，假定是有意義的時間。例如，’101112′ 被理解為’10：11：12’，但是 ‘106112’ 是不合法的（它有一個沒有意義的分鐘部分），在存儲時將變為 00：00：00。

提示：為 TIME 列分配簡寫值時應注意：如果沒有冒號，MySQL 解釋值時，假定最右邊的兩位表示秒。（MySQL 解釋 TIME 值為過去的時間而不是當前的時間）。例如，讀者可能認為 ‘1112’ 和 1112 表示 11：12：00（即 11 點過 12 分鐘），但MySQL 將它們解釋為 00：11：12（即 11 分 12 秒）。同樣 ’12’ 和 12 被解釋為00：00：12。相反，TIME 值中如果使用冒號則肯定被看作當天的時間，也就是說，’11：12′ 表示 11：12：00，而不是 00：11：12。

DATE 類型

DATE 類型用于僅需要日期值時，沒有時間部分，在存儲時需要 3 個字節。日期格式為 ‘YYYY-MM-DD’，其中 YYYY 表示年，MM 表示月，DD 表示日。

在給 DATE 類型的字段賦值時，可以使用字符串類型或者數字類型的數據插入，只要符合 DATE 的日期格式即可。如下所示：

• 以 ‘YYYY-MM-DD’ 或者 ‘YYYYMMDD’ 字符中格式表示的日期，取值范圍為 ‘1000-01-01’～’9999-12-3’。例如，輸入 ‘2015-12-31’ 或者 ‘20151231’，插入數據庫的日期為2015-12-31。

• 以 ‘YY-MM-DD’ 或者 ‘YYMMDD’ 字符串格式表示日期，在這里YY表示兩位的年值。MySQL 解釋兩位年值的規則：’00～69′ 范圍的年值轉換為 ‘2000~2069’，’70~99’ 范圍的年值轉換為 ‘1970～1999’。例如，輸入 ’15-12-31’，插入數據庫的日期為 2015-12-31；輸入 ‘991231’，插入數據庫的日期為 1999-12-31。

• 以 YYMMDD 數字格式表示的日期，與前面相似，00~69 范圍的年值轉換為 2000～2069，80～99 范圍的年值轉換為 1980～1999。例如，輸入 151231，插入數據庫的日期為 2015-12-31，輸入 991231，插入數據庫的日期為 1999-12-31。

• 使用 CURRENT_DATE 或者 NOW()，插入當前系統日期。

提示：MySQL 允許“不嚴格”語法：任何標點符號都可以用作日期部分之間的間隔符。例如，’98-11-31’、’98.11.31’、’98/11/31’和’98@11@31′ 是等價的，這些值也可以正確地插入數據庫。

DATETIME 類型

DATETIME 類型用于需要同時包含日期和時間信息的值，在存儲時需要 8 個字節。日期格式為 ‘YYYY-MM-DD HH：MM：SS’，其中 YYYY 表示年，MM 表示月，DD 表示日，HH 表示小時，MM 表示分鐘，SS 表示秒。

在給 DATETIME 類型的字段賦值時，可以使用字符串類型或者數字類型的數據插入，只要符合 DATETIME 的日期格式即可，如下所示。

• 以 ‘YYYY-MM-DD HH：MM：SS’ 或者 ‘YYYYMMDDHHMMSS’ 字符串格式表示的日期，取值范圍為 ‘1000-01-01 00：00：00’～’9999-12-3 23：59：59’。例如，輸入 ‘2014-12-31 05：05：05’ 或者 ‘20141231050505’，插入數據庫的 DATETIME 值都為 2014-12-31 05：05：05。

• 以 ‘YY-MM-DD HH：MM：SS’ 或者 ‘YYMMDDHHMMSS’ 字符串格式表示的日期，在這里 YY 表示兩位的年值。與前面相同，’00～79′ 范圍的年值轉換為 ‘2000～2079’，’80～99’ 范圍的年值轉換為 ‘1980～1999’。例如，輸入 ’14-12-31 05：05：05’，插入數據庫的 DATETIME 為 2014-12-31 05：05：05；輸入 141231050505，插入數據庫的 DATETIME 為 2014-12-31 05：05：05。

• 以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 數字格式表示的日期和時間。例如，輸入 20141231050505，插入數據庫的 DATETIME 為 2014-12-31 05：05：05；輸入 140505050505，插入數據庫的 DATETIME 為 2014-12-31 05：05：05。

提示：MySQL 允許“不嚴格”語法：任何標點符號都可用作日期部分或時間部分之間的間隔符。例如，’98-12-31 11：30：45’、’98.12.31 11+30+35’、’98/12/31 11*30*45′ 和 ’98@12@31 11^30^45′ 是等價的，這些值都可以正確地插入數據庫。

TIMESTAMP 類型

TIMESTAMP 的顯示格式與 DATETIME 相同，顯示寬度固定在 19 個字符，日期格式為 YYYY-MM-DD HH：MM：SS，在存儲時需要 4 個字節。但是 TIMESTAMP 列的取值范圍小于 DATETIME 的取值范圍，為 ‘1970-01-01 00：00：01’UTC～’2038-01-19 03：14：07’UTC。在插入數據時，要保證在合法的取值范圍內。

提示：協調世界時（英：Coordinated Universal Time，法：Temps Universel Coordonné）又稱為世界統一時間、世界標準時間、國際協調時間。英文（CUT）和法文（TUC）的縮寫不同，作為妥協，簡稱 UTC。

TIMESTAMP 與 DATETIME 除了存儲字節和支持的范圍不同外，還有一個最大的區別是：

• DATETIME 在存儲日期數據時，按實際輸入的格式存儲，即輸入什么就存儲什么，與時區無關；

• 而 TIMESTAMP 值的存儲是以 UTC（世界標準時間）格式保存的，存儲時對當前時區進行轉換，檢索時再轉換回當前時區。即查詢時，根據當前時區的不同，顯示的時間值是不同的。

提示：如果為一個 DATETIME 或 TIMESTAMP 對象分配一個 DATE 值，結果值的時間部分被設置為 ’00：00：00’，因此 DATE 值未包含時間信息。如果為一個 DATE 對象分配一個 DATETIME 或 TIMESTAMP 值，結果值的時間部分被刪除，因此DATE 值未包含時間信息。

4、字符串類型

字符串類型用來存儲字符串數據，還可以存儲圖片和聲音的二進制數據。字符串可以區分或者不區分大小寫的串比較，還可以進行正則表達式的匹配查找。

MySQL?中的字符串類型有?CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET?等。

下表中列出了 MySQL 中的字符串數據類型，括號中的M表示可以為其指定長度。

VARCHAR 和 TEXT 類型是變長類型，其存儲需求取決于列值的實際長度（在前面的表格中用 L 表示），而不是取決于類型的最大可能尺寸。

例如，一個 VARCHAR(10) 列能保存一個最大長度為 10 個字符的字符串，實際的存儲需要字符串的長度 L 加上一個字節以記錄字符串的長度。對于字符 “abcd”，L 是 4，而存儲要求 5 個字節。

CHAR 和 VARCHAR 類型

CHAR(M) 為固定長度字符串，在定義時指定字符串列長。當保存時，在右側填充空格以達到指定的長度。M 表示列的長度，范圍是 0～255 個字符。

例如，CHAR(4) 定義了一個固定長度的字符串列，包含的字符個數最大為 4。當檢索到 CHAR 值時，尾部的空格將被刪除。

VARCHAR(M) 是長度可變的字符串，M 表示最大列的長度，M 的范圍是 0～65535。VARCHAR 的最大實際長度由最長的行的大小和使用的字符集確定，而實際占用的空間為字符串的實際長度加 1。

例如，VARCHAR(50) 定義了一個最大長度為 50 的字符串，如果插入的字符串只有 10 個字符，則實際存儲的字符串為 10 個字符和一個字符串結束字符。VARCHAR 在值保存和檢索時尾部的空格仍保留。

TEXT 類型

TEXT 列保存非二進制字符串，如文章內容、評論等。當保存或查詢 TEXT 列的值時，不刪除尾部空格。

TEXT 類型分為 4 種：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。不同的 TEXT 類型的存儲空間和數據長度不同。

• TINYTEXT 表示長度為 255（28-1）字符的 TEXT 列。

• TEXT 表示長度為 65535（216-1）字符的 TEXT 列。

• MEDIUMTEXT 表示長度為 16777215（224-1）字符的 TEXT 列。

• LONGTEXT 表示長度為 4294967295 或 4GB（232-1）字符的 TEXT 列。

ENUM 類型

ENUM 是一個字符串對象，值為表創建時列規定中枚舉的一列值。其語法格式如下：

?ENUM(?'值1',?'值1',?…,?'值n'?)

字段名指將要定義的字段，值 n 指枚舉列表中第 n 個值。

ENUM 類型的字段在取值時，能在指定的枚舉列表中獲取，而且一次只能取一個。如果創建的成員中有空格，尾部的空格將自動被刪除。

ENUM 值在內部用整數表示，每個枚舉值均有一個索引值；列表值所允許的成員值從 1 開始編號，MySQL 存儲的就是這個索引編號，枚舉最多可以有 65535 個元素。

SET 類型

SET 是一個字符串的對象，可以有零或多個值，SET 列最多可以有 64 個成員，值為表創建時規定的一列值。指定包括多個 SET 成員的 SET 列值時，各成員之間用逗號,隔開，語法格式如下：

SET(?'值1',?'值2',?…,?'值n'?)

與 ENUM 類型相同，SET 值在內部用整數表示，列表中每個值都有一個索引編號。當創建表時，SET 成員值的尾部空格將自動刪除。

但與 ENUM 類型不同的是，ENUM 類型的字段只能從定義的列值中選擇一個值插入，而 SET 類型的列可從定義的列值中選擇多個字符的聯合。

提示：如果插入 SET 字段中的列值有重復，則 MySQL 自動刪除重復的值；插入 SET 字段的值的順序并不重要，MySQL 會在存入數據庫時，按照定義的順序顯示；如果插入了不正確的值，默認情況下，MySQL 將忽視這些值，給出警告。

5、二進制類型

MySQL 支持兩類字符型數據：文本字符串和二進制字符串。上節《MySQL字符串類型》我們講了文本字符串，這節我們來講解二進制字符串。

二進制字符串類型有時候也直接被稱為“二進制類型”。

MySQL 中的二進制字符串有 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

下表中列出了 MySQL 中的二進制數據類型，括號中的M表示可以為其指定長度。

BIT 類型

位字段類型。M 表示每個值的位數，范圍為 1～64。如果 M 被省略，默認值為 1。如果為 BIT(M) 列分配的值的長度小于 M 位，在值的左邊用 0 填充。例如，為 BIT(6) 列分配一個值 b’101’，其效果與分配 b’000101′ 相同。

BIT 數據類型用來保存位字段值，例如以二進制的形式保存數據 13，13 的二進制形式為 1101，在這里需要位數至少為 4 位的 BIT 類型，即可以定義列類型為 BIT(4)。大于二進制 1111 的數據是不能插入 BIT(4) 類型的字段中的。

提示：默認情況下，MySQL 不可以插入超出該列允許范圍的值，因而插入數據時要確保插入的值在指定的范圍內。

BINARY 和 VARBINARY 類型

BINARY 和 VARBINARY 類型類似于 CHAR 和 VARCHAR，不同的是它們包含二進制字節字符串。使用的語法格式如下：

列名稱?BINARY(M)?或者?VARBINARY(M)

BINARY 類型的長度是固定的，指定長度后，不足最大長度的，將在它們右邊填充 “