最全mysql資料型別梳理彙總

整數型別

數值型資料型別主要用來儲存數位，mysql提供了多種數值資料型別，不同的資料型別提供不同的取值範圍，可以儲存的值範圍越大，其所需要的儲存空間也會越大。

mysql主要提供的整數型別有TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。整數型別的屬性欄位可以新增AUTO_INCREMENT自增約束條件。

看下下面兩個表；

• 表一：mysql中的整數型資料型別
• 表二、mysql不同整數型別的取值範圍

舉個例子：

year int(4);

該宣告指明，在year欄位中的資料一般只顯示4位數位的寬度；

注意點：顯示寬度和資料型別取值範圍是無關的。顯示寬度只是指明mysql最大可能顯示的數位個數，數值的位數小於指定的寬度時會由空格填充；如果插入了大於顯示寬度的值，只要該值不超過該型別整數的取值範圍，數值依然可以插入，而且能夠顯示出來。

例如：向year欄位插入一個數值12345，當使用select查詢該列值的時候，mysql顯示的將是完整的帶有5位數位的12345，而不是4位元數位的值。

其他整數型資料型別也可以在定義表結構時指定所需要的顯示寬度，如果不指定，則系統為每一種型別指定預設的寬度。

不同的整數型別有不同的取值範圍，並且需要不同的儲存空間，因此應該根據實際需要選擇最合適的型別，這樣有利於提高查詢的效率和節省儲存空間。整數型別是不帶小數部分的數值，現實生活中很多地方都需要用到帶小數的數值。下面我們就來介紹下小數型別。

注意點：顯示寬度只用於顯示，並不能限制取值範圍和佔用空間。

浮點數型別和定點數型別

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

浮點數型別有兩種：

• 單精度浮點型別（FLOAT）
• 雙精度浮點型別（DOUBLE）

定點數型別只有一種：

• DECIMAL

浮點數型別和定點數型別都可以用（M，N）來表示。其中呢，我們用M稱為精度，表示總共的位數；N稱為標度，表示小數的位數。

DECIMAL型別不同於FLOAT和DOUBLE，DECIMAL實際是以串存放的，可能的最大取值範圍和DOUBLRE一樣，但是其有效的取值範圍由M和D的值來決定。怎麼說呢？

如果改變M而固定D，則其取值範圍將隨著M的變大而變大。

那麼在mysql中，浮點數型別和定點數型別的取值範圍有多大呢？

幾乎正常業務情況下是不可能用完的，因為他們的取值範圍如下：

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

注意點：無論是定點數還是浮點數型別，如果使用者指定的精度超出精度範圍，則會四捨五入。

可能有點繞，舉個栗子吧：

建立一個資料表為yunweijia_1，欄位型別如下；

mysql> create table yunweijia_1 (x float(5,1), y double(5,1), z decimal(5,1));
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql>

然後我們在這個表中插入一條資料，sql語句如下：

mysql> insert into yunweijia_1 values (6.66, 7.77, 8.888);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql>

看上面的提示資訊，是不是在我們執行插入語句的時候，給了一個警告資訊，然後我們來看下這個警告資訊是什麼吧；

mysql> show warnings;
+-------+------+----------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------+
| Note | 1265 | Data truncated for column 'z' at row 1 |
+-------+------+----------------------------------------+
1 row in set (0.00 sec)
mysql>

可以看到他提示說，z欄位在插入的時候被截斷了，但是x和y截斷就沒有提出警告。

那麼檢視下結果吧：

mysql> select * from yunweijia_1;
+------+------+------+
| x | y | z |
+------+------+------+
| 6.7 | 7.8 | 8.9 |
+------+------+------+
1 row in set (0.00 sec)
mysql>

可以看到分別都進行了四捨五入，這點需要注意。

FLOAT和DOUBLE在不指定精度操作時，預設會按照實際的精度（由計算機硬體和作業系統決定），DECIMAL若不指定精度則預設為（10,0）；

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

注意點：在mysql中，定點數以字串形式儲存，在對精度要求比較高的時候，使用DECIMAL的型別比較好，另外，兩個浮點數進行減法和比較運算時，容易出現問題，所以在使用浮點數時需要注意，並儘量避免做浮點數比較。

日期與時間型別

mysql中有多種表示日期的資料型別，主要有：

• DATETIME
• DATE
• TIMESTAMP
• TIME
• YEAR

比如說，當只記錄年資訊的時候，可以只使用YEAR型別，而沒有必要使用DATE。

每一個型別都有合法的取值範圍，當指定確實不合法的值時系統將“零”值插入到資料庫中。

YEAR

year型別是一個單位元組型別，用於表示年，在儲存時只需要1位元組。可以使用各種格式指定year值，如下：

• 1、以4位元字串或者4位元數位格式表示的year，範圍為1901~2155。輸入格式為yyyy或者'yyyy'，例如：輸入‘2022’或者2022，插入到資料庫的值均為2022。
• 2、以2位字串格式表示的year，範圍為“00”到“99”。“00”~“69”和“70”~“99”範圍的值分別被轉換為“2022”~“2069”和“1970”~“1999”範圍的year值。“0”與“00”的作用相同。插入超過取值範圍的值將被轉換成2000。
• 3、以兩位數位表示的YEAR，範圍為1~99。1~69和70~99範圍的值分別被轉換為200·~2069和1970~1999範圍的YEAR

值。和字元相比較而言，這裡的0值，將被轉換成0000，而不是2000。

注意點：

兩位整數範圍與兩位字串範圍稍有不同。例如，插入2000年，有人可能會使用數位格式的0表示year，實際上，插入資料庫的值為0000，而不是所希望的2000。只有使用字串格式的“0”或者“00”，才可以被正確的解釋為2000,。非法YEAR值將被轉換為0000。

栗子：

我們建立一個名為yunweijia_2的資料表，使用以下sql語句：

mysql> create table yunweijia_2 (y year);
Query OK, 0 rows affected (0.04 sec)
mysql>

然後我們往表中插入一條資料；

mysql> insert into yunweijia_2 values(2022), ('2022');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>

再次嘗試插入一條資料；

mysql> insert into yunweijia_2 values ('2166');
ERROR 1264 (22003): Out of range value for column 'y' at row 1
mysql>

我們發現‘2166’超出了YEAR型別的取值範圍，所以不能插入資料，這個時候我們看下mysql資料庫中的資料；

mysql> select * from yunweijia_2;
+------+
| y |
+------+
| 2022 |
| 2022 |
+------+
2 rows in set (0.00 sec)
mysql>

栗子：

首先我們先刪除yunweijia_2資料表中的資料；

mysql> delete from yunweijia_2;
Query OK, 2 rows affected (0.01 sec)
mysql>

可以看到提示刪除了兩條資料；

然後我們再插入以下資料；

mysql> insert into yunweijia_2 values ('0'), ('00'), ('88'), ('22');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>

然後看下結果是什麼：

mysql> select * from yunweijia_2;
+------+
| y |
+------+
| 2000 |
| 2000 |
| 1988 |
| 2022 |
+------+
4 rows in set (0.00 sec)
mysql>

從上面i看到，字串‘0’和‘00’的作用相同，分別都轉換成了2000年；‘88’轉換成了‘1988’；‘22’轉換成了‘2022’。

栗子：

老規矩，先刪除表中現有的資料；

mysql> delete from yunweijia_2;
Query OK, 4 rows affected (0.01 sec)
mysql>

然後再插入資料：

mysql> insert into yunweijia_2 values (0), (99), (22);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>

看下結果：

mysql> select * from yunweijia_2;
+------+
| y |
+------+
| 0000 |
| 1999 |
| 2022 |
+------+
3 rows in set (0.00 sec)
mysql>

可以看到，0被轉換成了0000,99被轉換成了1999，22被轉換成了2022。

TIME

time型別用在只需要時間資訊的值，在儲存時需要3位元組，格式為HH:MM:SS。

其中，HH表示小時，MM表示分鐘，SS表示秒。

mysql中time型別的取值範圍為-838:59:59~838:59:598，小時部分為什麼會這麼大呢，這是因為time型別不僅可以用來表示一天的時間，還可能是某個事件過去的時間或者兩個事件之間的時間間隔。

（1）‘D HH:MM:S’格式的字串，可以使用下面任何一種“非嚴格”的語法：

• ‘HH:MM:SS’
• ‘HH:MM’
• ‘D HH:MM’

這裡的D表示日，可以取0~34的值，在插入資料庫時，D被轉換成小時儲存，格式為“D*24+HH”；

（2）‘HH:MM:SS’格式的、沒有間隔符的字串或者HHMMSS格式的數值，假定是有意義的時間。例如：'223344'被理解成‘22:33:44’，但是‘223366’是不合法的（他有一個沒有意義的秒部分），儲存時將變成00:00:00。

注意點：

為time列分配簡寫時應該注意，如果沒有冒號，mysql解釋值時，假定最右邊的兩位表示秒。如果time值中使用了冒號，則肯定被單做是當天的時間。

例如：

11:22 會被mysql解釋成 00:11:22

'11:22'會被mysql解釋成11:22:00

栗子

建立一個名為yunweijia_3的資料表；

mysql> create table yunweijia_3(t time);
Query OK, 0 rows affected (0.03 sec)
mysql>

往這個表中插入資料：

mysql> insert into yunweijia_3 values ('10:06:06'), ('22:22'), ('2 10:10'), ('3 00'), ('20');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>

看下錶中的資料：

mysql> select * from yunweijia_3;
+----------+
| t |
+----------+
| 10:06:06 |
| 22:22:00 |
| 58:10:00 |
| 72:00:00 |
| 00:00:20 |
+----------+
5 rows in set (0.00 sec)
mysql>

小提示：在使用‘D HH’格式時，小時一定要使用雙位數值，如果是小於10的小時數，應在前面加0以用來補全雙位數值。

舉個栗子 ：

首先我們刪除資料表yunweijia_3裡面的資料；

mysql> delete from yunweijia_3;
Query OK, 5 rows affected (0.01 sec)
mysql>

然後再表中插入資料：

mysql> insert into yunweijia_3 values ('101112'), (111213), ('0');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>

再次向表中插入一條資料；

mysql> insert into yunweijia_3 values (107010);
ERROR 1292 (22007): Incorrect time value: '107010' for column 't' at row 1
mysql>

可以看到這條資料沒有插入成功，我看看下提示資訊；

mysql> show warnings;
+-------+------+--------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------+
| Error | 1292 | Incorrect time value: '107010' for column 't' at row 1 |
+-------+------+--------------------------------------------------------+
1 row in set (0.00 sec)
mysql>

為什麼這次會插入失敗呢，是不是因為我們的分鐘應該是小於60的啊，但是這裡是70，所以報錯了。

我們檢視下這個資料庫中的資料現在的展示吧：

mysql> select * from yunweijia_3;
+----------+
| t |
+----------+
| 10:11:12 |
| 11:12:13 |
| 00:00:00 |
+----------+
3 rows in set (0.00 sec)
mysql>

又舉個栗子：

我們還可以使用系統函數向time欄位列插入值；

刪除資料表yunweijia_3裡面的資料；

mysql> delete from yunweijia_3;
Query OK, 3 rows affected (0.01 sec)
mysql>

然後在表中插入資料；

mysql> insert into yunweijia_3 values (CURRENT_TIME), (NOW());
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>

檢視下資料庫中的結果；

mysql> select * from yunweijia_3;
+----------+
| t |
+----------+
| 23:03:41 |
| 23:03:41 |
+----------+
2 rows in set (0.00 sec)

mysql>

從結果中可以看到，獲取系統當前的紀錄檔插入到time型別的列中。

到此這篇關於最全mysql資料型別梳理彙總的文章就介紹到這了,更多相關mysql資料型別內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！