文章目錄

• mysql數據類型匯總
• • 1、整數類型
  • 2、浮點數類型
  • 3、字符串類型
  • 4、枚舉類型（ENUM）
  • 5、日期和時間類型
• 面試題
• • 1. int(10) 和 bigint(10)能存儲的數據大小一樣嗎？
  • 2. varchar存儲規則
  • 3. MySQL中varchar最大長度是多少？
  • 4. char和varchar有什么區別？
  • 5. Datetime 和 Timestamp 之間抉擇
  • • 5.1 DateTime 類型沒有時區信息
    • 5.2 擴展：一些關于 MySQL 時區設置的一個常用 sql 命令
    • 5.3 DateTime 類型耗費空間更大
    • 5.4 數值型時間戳是更好的選擇嗎？
    • 5.5 總結

mysql數據類型匯總

分類類型名稱說明

整數類型	tinyInt	很小的整數(8位二進制)
	smallint	小的整數(16位二進制)
	mediumint	中等大小的整數(24位二進制)
	int(integer)	普通大小的整數(32位二進制)
	BigInt	64位
浮點數類型	float	單精度浮點數
	double	雙精度浮點數
	decimal(m,d)	壓縮嚴格的定點數,共m位，取d位存儲小部分，剩下m-d位存儲整數部分
日期類型	year	YYYY 1901~2155
	time	HH:MM:SS -838:59:59~838:59:59
	date	YYYY-MM-DD 1000-01-01~9999-12-3
	datetime	YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59
	timestamp	YYYY-MM-DD HH:MM:SS 19700101 00:00:01 UTC~2038-01-19 03:14:07UTC
文本、二進制類型	CHAR(M)	M為0~255之間的整數
	VARCHAR(M)	M為0~65535之間的整數
	TINYBLOB	允許長度0~255字節
	BLOB	允許長度0~65535字節
	MEDIUMBLOB	允許長度0~167772150字節
	LONGBLOB	允許長度0~4294967295字節
	TINYTEXT	允許長度0~255字節
	TEXT	允許長度0~65535字節
	MEDIUMTEXT	允許長度0~167772150字節
	LONGTEXT	允許長度0~4294967295字節
	VARBINARY(M)	允許長度0~M個字節的變長字節字符串
	BINARY(M)	允許長度0~M個字節的定長字節字符串

1、整數類型

• 包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分別表示1字節、2字節、3字節、4字節、8字節整數。任何整數類型都可以加上UNSIGNED屬性，表示數據是無符號的，即非負整數。
  • 長度：整數類型可以被指定長度，例如：INT(11)表示長度為11的INT類型。長度在大多數場景是沒有意義的，它不會限制值的合法范圍，只會影響顯示字符的個數，而且需要和UNSIGNED ZEROFILL屬性配合使用才有意義。
  • 例子：假定類型設定為INT(5)，屬性為UNSIGNED ZEROFILL，如果用戶插入的數據為12的話，那么數據庫實際存儲數據為00012。

2、浮點數類型

• 包括FLOAT、DOUBLE、DECIMAL。
  • DECIMAL可以用于存儲比BIGINT還大的整型，能存儲精確的小數。而FLOAT和DOUBLE是有取值范圍的，并支持使用標準的浮點進行近似計算。計算時FLOAT和DOUBLE相比DECIMAL效率更高一些，DECIMAL你可以理解成是用字符串進行處理。

3、字符串類型

• 包括VARCHAR、CHAR、TEXT、BLOB
  • VARCHAR用于存儲可變長字符串，它比定長類型更節省空間。VARCHAR使用額外1或2個字節存儲字符串長度。列長度小于255字節時，使用1字節表示，否則使用2字節表示。VARCHAR存儲的內容超出設置的長度時，內容會被截斷。
  • CHAR是定長的，根據定義的字符串長度分配足夠的空間。CHAR會根據需要使用空格進行填充方便比較。CHAR適合存儲很短的字符串，或者所有值都接近同一個長度。CHAR存儲的內容超出設置的長度時，內容同樣會被截斷。

使用策略：

• VARCHAR是最常見的字符串類型。VARCHAR節省了存儲空間，所以對性能也有幫助。但是，由于行是可變的，在UPDATE時可能使行變得比原來更長，這就導致需要做額外的工作。如果一個行占用的空間增長，并且在頁內沒有更多的空間可以存儲，MyISAM會將行拆成不同的片段存儲；InnoDB則需要分裂頁來使行可以放進頁內。
  • 下面這些情況使用VARCHAR是合適的：字符串的最大長度比平均長度大很多；列的更新很少，所以碎片不是問題；使用了像UTF-8這樣復雜的字符集，每個字符都使用不同的字節數進行存儲。
• 當存儲CHAR值時，MySQL會刪除所有的末尾空格。CHAR值會根據需要采用空格進行填充以方便比較。 CHAR適合存儲很短的字符串，或者所有值都接近同一個長度，如密碼的MD5值。對于經常變更的數據，CHAR也比VARCHAR更好，因為CHAR不容易產生碎片（行間碎片？）。

4、枚舉類型（ENUM）

把不重復的數據存儲為一個預定義的集合。有時可以使用ENUM代替常用的字符串類型。ENUM存儲非常緊湊，會把列表值壓縮到一個或兩個字節。ENUM在內部存儲時，其實存的是整數。盡量避免使用數字作為ENUM枚舉的常量，因為容易混亂。排序是按照內部存儲的整數排序。

5、日期和時間類型

• DATETIME： 1. 能夠保存從 1001 年到 9999 年的日期和時間，精度為秒，使用 8 字節的存儲空間。 2. 它與時區無關。3. 默認情況下，MySQL 以一種可排序的、無歧義的格式顯示 DATETIME 值，例如“2008-01-16 22:37:08”，這是 ANSI 標準定義的日期和時間表示方法。

• TIMESTAMP： 和 UNIX 時間戳相同，保存從 1970 年 1 月 1 日午夜(格林威治時間)以來的秒數，使用 4 個字節，只能表示從 1970 年 到 2038 年。 它和時區有關，也就是說一個時間戳在不同的時區所代表的具體時間是不同的。 MySQL 提供了 FROM_UNIXTIME() 函數把 UNIX 時間戳轉換為日期，并提供了 UNIX_TIMESTAMP() 函數把日期轉換為 UNIX 時間戳。

  • 默認情況下，如果插入時沒有指定 TIMESTAMP 列的值，會將這個值設置為當前時間。應該盡量使用 TIMESTAMP，因為它比 DATETIME 空間效率更高。

面試題

1. int(10) 和 bigint(10)能存儲的數據大小一樣嗎？

不一樣，具體原因如下：

• int 能存儲四字節有符號整數。
• bigint 能存儲八字節有符號整數。

所以能存儲的數據大小不一樣，其中的數字 10 代表的只是數據的顯示寬度。

• 顯示寬度指明Mysql最大可能顯示的數字個數，數值的位數小于指定的寬度時數字左邊會用空格填充，空格不容易看出。
• 如果插入了大于顯示寬度的值，只要該值不超過該類型的取值范圍，數值依然可以插入且能夠顯示出來。
• 建表的時候指定 zerofill 選項，則不足顯示寬度的部分用 0 填充，如果是 1 會顯示成 0000000001。
• 如果沒指定顯示寬度， bigint 默認寬度是 20 ，int默認寬度 11。
• 如果一個數據超過指定的寬度，但是數據大小只要不超過數據類型的最大值依然可以插入成功。

2. varchar存儲規則

• 4.0版本以下，varchar(20)，指的是20字節，如果存放UTF8漢字時，只能存6個（每個漢字3字節）
• 5.0版本以上，varchar(20)，指的是20字符，無論存放的是數字、字母還是UTF8漢字（每個漢字3字節），都可以存放20個，最大的大小是65532字節

3. MySQL中varchar最大長度是多少？

參考文檔

varchar底層

4. char和varchar有什么區別？

區別一，定長和變長：

char 表示定長，長度固定；varchar表示變長，即長度可變。當所插入的字符串超出它們的長度時，視情況來處理，如果是嚴格模式，則會拒絕插入并提示錯誤信息，如果是寬松模式，則會截取然后插入。如果插入的字符串長度小于定義長度時，則會以不同的方式來處理，如char（10），表示存儲的是10個字符，無論你插入的是多少，都是10個，如果少于10個，則用空格填滿。而varchar（10），小于10個的話，則插入多少個字符就存多少個。
varchar怎么知道所存儲字符串的長度呢？實際上，對于varchar字段來說，需要使用一個（如果字符串長度小于255）或兩個字節（長度大于255）來存儲字符串的長度。但是因為他需要有一個prefix來表示他具體bytes數是多少（因為varchar是變長的，沒有這個長度值他不知道如何讀取數據）。

區別之二，存儲的容量不同：

對 char 來說，最多能存放的字符個數 255，和編碼無關。而 varchar 呢，最多能存放 65532 個字符。VARCHAR 的最大有效長度由最大行大小和使用的字符集確定。整體最大長度是 65,532字節

5. Datetime 和 Timestamp 之間抉擇

Datetime 和 Timestamp 是 MySQL 提供的兩種比較相似的保存時間的數據類型。他們兩者究竟該如何選擇呢？

通常我們都會首選 Timestamp。 下面說一下為什么這樣做!

5.1 DateTime 類型沒有時區信息

DateTime 類型是沒有時區信息的（時區無關） ，DateTime 類型保存的時間都是當前會話所設置的時區對應的時間。這樣就會有什么問題呢？當你的時區更換之后，比如你的服務器更換地址或者更換客戶端連接時區設置的話，就會導致你從數據庫中讀出的時間錯誤。不要小看這個問題，很多系統就是因為這個問題鬧出了很多笑話。

Timestamp 和時區有關。 Timestamp 類型字段的值會隨著服務器時區的變化而變化，自動換算成相應的時間，說簡單點就是在不同時區，查詢到同一個條記錄此字段的值會不一樣。 下面實際演示一下！ 建表 SQL 語句：

建表SQL語句：

CREATE TABLE `time_zone_test` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`date_time` datetime DEFAULT NULL,`time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入數據

INSERT INTO time_zone_test(date_time,time_stamp) VALUES(NOW(),NOW());

查看數據

select date_time,time_stamp from time_zone_test;

結果

修改當前會話的時區

set time_zone = '+10:00'

再次查看數據

5.2 擴展：一些關于 MySQL 時區設置的一個常用 sql 命令

# 查看當前會話時區 SELECT @@session.time_zone; # 設置當前會話時區 SET time_zone = 'Europe/Helsinki'; SET time_zone = "+00:00"; # 數據庫全局時區設置 SELECT @@global.time_zone; # 設置全局時區 SET GLOBAL time_zone = '+8:00'; SET GLOBAL time_zone = 'Europe/Helsinki';

5.3 DateTime 類型耗費空間更大

Timestamp 只需要使用 4 個字節的存儲空間，但是 DateTime 需要耗費 8 個字節的存儲空間。但是，這樣同樣造成了一個問題，Timestamp 表示的時間范圍更小。

• DateTime ：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
• Timestamp： 1970-01-01 00:00:01 ~ 2037-12-31 23:59:59

Timestamp 在不同版本的 MySQL 中有細微差別。

5.4 數值型時間戳是更好的選擇嗎？

很多時候，我們也會使用 int 或者 bigint 類型的數值也就是時間戳來表示時間。

這種存儲方式的具有 Timestamp 類型的所具有一些優點，并且使用它的進行日期排序以及對比等操作的效率會更高，跨系統也很方便，畢竟只是存放的數值。缺點也很明顯，就是數據的可讀性太差了，你無法直觀的看到具體時間。

數據庫中實際操作：

mysql> select UNIX_TIMESTAMP('2020-01-11 09:53:32'); +---------------------------------------+ | UNIX_TIMESTAMP('2020-01-11 09:53:32') | +---------------------------------------+ | 1578707612 | +---------------------------------------+ 1 row in set (0.00 sec)mysql> select FROM_UNIXTIME(1578707612); +---------------------------+ | FROM_UNIXTIME(1578707612) | +---------------------------+ | 2020-01-11 09:53:32 | +---------------------------+ 1 row in set (0.01 sec)

5.5 總結

每種方式都有各自的優勢，根據實際場景才是王道。下面再對這三種方式做一個簡單的對比，以供大家實際開發中選擇正確的存放時間的數據類型。

總結

以上是生活随笔為你收集整理的MySQL面试 (一) 数据类型的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。