mysql字段類型有哪些
- 青灯夜游原創
- 2022-04-14 15:10:1332061瀏覽
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 | 很小的整數 | 1個位元組 |
| SMALLINT | 小的整數 | 2個太空節 |
| MEDIUMINT | 中等大小的整數 | 3個位元組 |
| INT (INTEGHR) | 普通大小的整數 | 4個位元組 |
| BIGINT | 大整數 | 8個位元組 |
從上表可以看到，不同類型的整數儲存所需的位元組數不相同，佔用位元組數最小的是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 中的小數型別和儲存需求。
| 類型名稱 | 說明 | 。儲存需求 |
|---|---|---|
| FLOAT | 單精確度浮點數 | 4 個位元組 |
| DOUBLE | 雙精確度浮點數 | 8 位元組 |
| DECIMAL (M, D)，DEC | 壓縮的「嚴格」定點數 | M 2 個位元組 |
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 | YYYY | 1901 ~ 2155 | 1 個位元組 |
| TIME | HH :MM:SS | -838:59:59 ~ 838:59:59 | 3 個位元組 |
| DATE | YYYY-MM-DD | 1000-01-01 ~ 9999-12-3 | 3 個位元組 |
| DATETIME | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 | #8 位元組 |
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：插入資料庫的 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表示可以為其指定長度。
| 類型名稱 | 說明 | 儲存需求 |
|---|---|---|
| CHAR(M) | #固定長度非二進位字串 | M 字節，1<=M<=255 |
| VARCHAR(M) | 變長非二進位字串 | L 1位元組，在此，L< = M和 1<=M<=255 |
| TINYTEXT | 非常小的非二進位字串 | L 1位元組，在此，L<2^8 |
| TEXT | ##小的非二進位字串L 2位元組，在此，L<2 | ^16 |
| 中大小的非二進位字串 | L 3位元組，在此，L<2^24 | |
| 大的非二進位字串 | #L 4字節，在此，L<2^32 | |
| 枚舉類型，只能有一個枚舉字串值 | 1或2個位元組，取決於枚舉值的數目(最大值為65535) | |
| 一個設置，字串物件可以有零個或多個SET成員 | 1、2、3、4或8個位元組，取決於集合成員的數量（最多64個成員） |
| 类型名称 | 说明 | 存储需求 |
|---|---|---|
| BIT(M) | 位字段类型 | 大约 (M+7)/8 字节 |
| BINARY(M) | 固定长度二进制字符串 | M 字节 |
| VARBINARY (M) | 可变长度二进制字符串 | M+1 字节 |
| TINYBLOB (M) | 非常小的BLOB | L+1 字节，在此，L<2^8 |
| BLOB (M) | 小 BLOB | L+2 字节，在此，L<2^16 |
| MEDIUMBLOB (M) | 中等大小的BLOB | L+3 字节，在此，L<2^24 |
| LONGBLOB (M) | 非常大的BLOB | L+4 字节，在此，L<2^32 |
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 类型的长度是固定的，指定长度后，不足最大长度的，将在它们右边填充 “\0” 补齐，以达到指定长度。例如，指定列数据类型为 BINARY(3)，当插入 a 时，存储的内容实际为 “\a0\0”，当插入 ab 时，实际存储的内容为“ab\0”，无论存储的内容是否达到指定的长度，存储空间均为指定的值 M。
VARBINARY 类型的长度是可变的，指定好长度之后，长度可以在 0 到最大值之间。例如，指定列数据类型为 VARBINARY(20)，如果插入的值长度只有 10，则实际存储空间为 10 加 1，实际占用的空间为字符串的实际长度加 1。
BLOB 类型
BLOB 是一个二进制的对象，用来存储可变数量的数据。BLOB 类型分为 4 种：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB
【相关推荐：mysql视频教程】
以上是mysql字段類型有哪些的詳細內容。更多資訊請關注PHP中文網其他相關文章！