MySQL儲存引擎與資料類型

標籤：comm   檔案   佔用   datetime   欄位   man   float   查看   dynamic   

1 資料存放區引擎

儲存引擎的概念是MySQL的一個特性，它指定了表的類型（諸如表如何儲存與索引資料、是否支援事務、外鍵等），表在電腦中的儲存方式。

1.1 MySql支援的資料存放區引擎

查看引擎資訊

通過命令來查看引擎資訊

 show engines;

預設儲存引擎為InnoDB，如下列出：

Engine	Support	Comment	Transactions	XA	Savepoints
InnoDB	DEFAULT	Supports transactions, row-level locking, and foreign keys	YES	YES	YES
MRG_MYISAM	YES	Collection of identical MyISAM tables	NO	NO	NO
MEMORY	YES	Hash based, stored in memory, useful for temporary tables	NO	NO	NO
BLACKHOLE	YES	/dev/null storage engine (anything you write to it disappears)	NO	NO	NO
MyISAM	YES	MyISAM storage engine	NO	NO	NO
CSV	YES	CSV storage engine	NO	NO	NO
ARCHIVE	YES	Archive storage engine	NO	NO	NO
PERFORMANCE_SCHEMA	YES	Performance Schema	NO	NO	NO
FEDERATED	NO	Federated MySQL storage engine	NULL	NULL	NULL

Support表示是否支援該引擎，Comment表示關於該引擎的評論，Transactions比表示該引擎是否支援事務，XA表示此引擎支援的分布式是否符合XA規範，Savepoints表示此引擎是否支援交易處理中的儲存點。

還可以通過show variables來查看，命令如下

show variables like ‘have%‘;  

顯示結果如下：

Variable_name	Value
have_compress	YES
have_crypt	NO
have_dynamic_loading	YES
have_geometry	YES
have_openssl	DISABLED
have_profiling	YES
have_query_cache	YES
have_rtree_keys	YES
have_ssl	DISABLED
have_statement_timeout	YES
have_symlink	YES

yes表示支援，no表示不支援，DISABLED表示支援但未開啟

查看預設儲存引擎

show variables like ‘%storage_engine%‘;

查詢結果如下：

Variable_name	Value
default_storage_engine	InnoDB
default_tmp_storage_engine	InnoDB
disabled_storage_engines
internal_tmp_disk_storage_engine	InnoDB

1.2 修改預設引擎

方式1: 修改my.ini設定檔

在安裝目錄下的my.ini檔案中添加或修改default-storage-engine，如設定預設引擎為INNODB，在my.ini檔案中添加如下一行內容：

default-storage-engine=INNODB 

重啟mysql服務生效。

方式2：通過命令設定

一般在建立表時我們可以設定引擎

CREATE TABLE `test` (  `db` double DEFAULT NULL,  `dm` decimal(10,0) DEFAULT NULL,  `gender` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8

當建立完成，我們希望修改表的引擎時，可以執行如下命令：

 alter table table_name engine= engine_name;

比如我們想設定表test的引擎為MyISAM，可以執行：

alter table test engine=MyISAM; 

1.3 儲存引擎的選擇

選擇一個合適的儲存引擎，才能發揮資料庫更好的功能。因為各種資料引擎有著不同的特性優勢與適用場合，因此需要先瞭解它們的特性，才能有助於我們更好的選擇合適的引擎。

下面列出了幾種常見的引擎以及他們的特性：

MyISAM
此儲存引擎不支援事務、外鍵，但訪問速度快，對事務完整性不要求，適合於對事務沒有要求，並發相對較低，以訪問為主的場合。

InnoDB
支援事務，具有提交、復原、崩潰恢複的事務安全，相對MySQL來說，寫處理能力差些，且會佔用較多磁碟空間以保留資料和索引。在需要進行頻繁的更新刪除操作，實現並發控制，需要支援事務的場合適合使用它。

MEMORY
該儲存引擎使用存在於記憶體中的內容來建立表，每個表實際對應一個磁碟檔案，格式為.frm；這類表因為資料在記憶體中，且預設使用HASH索引，所以訪問速度非常快；但一旦服務關閉，表中的資料會丟失。在涉及資料量較小，需要進行快速存取的場合適用此引擎。

2 資料類型

MySql提供了資料類型來定義我們儲存的資料的類型，不同的資料類型有著不同的儲存功能與佔用的空間大小。Mysql提供的資料類型有：整數類型、浮點數類型與定點數類型、位類型、字串類型、日期與時間類型。

2.1 整數類型

類型	位元組	範圍（有符號）	範圍（無符號）
TINYINT	1	(-128，127)	(0，255)
SMALLINT	2	(-32 768，32 767)	(0，65 535)
MEDIUMINT	3	(-8 388 608，8 388 607)	(0，16 777 215)
INT或INTEGER	4	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)
BIGINT	8	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)

對於整數類型，我們需要根據實際儲存資料的大小來決定我們選擇哪種類型來儲存。諸如當要儲存的資料大小不超過255，我們選擇TINYINT就ok，既滿足要求又節約記憶體。

在使用的時候，當我們不清楚那個類型的範圍，我們可以通過協助命令來查看，諸如查看int類型，我們可以鍵入 ？ int; （或 help int；）來查看int的詳細資料。如下所示：

2.2 浮點數類型、定點數類型、位類型

浮點數類型

類型	位元組	範圍（有符號）	範圍（無符號）	作用
FLOAT	4	(-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	單精確度浮點數值
DOUBLE	8	(1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	雙精確度浮點數值
DECIMAL(M,D)	M+2	依賴於M和D的值	依賴於M和D的值	高精度小數值

關於DECIMAL特別指出：

• M是數位最大數（精度）。其範圍為1～65，M 的預設值是10。M的值為整數的長度加小數的長度，不包含小數點與 “-” 負號。
• D是小數點右側數位數目（標度）。其範圍是0～30，但不得超過M。如果D省略不寫則預設為0，此時無小數部分。
• 儲存整數部分長度為M-D（不滿足會出錯），小數長度為D（大於D的部分會被截斷）

關於用法：
對於浮點數類型與DECIMAL的選擇，當我們對小數精度要求很高時，此時推薦使用DECIMAL類型，因為DECIMAL類型儲存的是字串，而浮點數類型儲存的是近似值。對於精度要求不高的情況下，視儲存的資料的範圍而定。

位類型

位類型	位元組	最大值(位)	最小值(位)
BIT(M)	1~8	BIT(64)	BIT(1)

位類型BTM佔用的位元組數是M，取值在1~8之間。BIT類型的儲存大小視其精度而定。

可以使用b’value’符號寫位欄位值。value是一個用0和1寫成的二進位值。如下：

2.3 字串

char系字串類型

類型	位元組	描述
CHAR(M)	M	M為0~255之間的整數
VARCHAR(M)	M	M為0~65535之間的整數

當儲存的資料經常變動時使用varchar，否則使用char

BINARY系字串

類型	位元組	描述
BINARY(M)	M	長度0~M個位元組的定長位元組字串
VARBINARY(M)	M	長度0~M個位元組的變長位元組字串，值的長度+1個位元組

與char系類似，不同的是BINARY系儲存的可以是位元據（片、視頻、音樂），而char系只能儲存字元資料。

TEXT系字串類型

類型	位元組	描述
TINYTEXT	0~255位元組	值的長度+2個位元組
TEXT	0~65535位元組	值的長度+2個位元組
MEDIUMTEXT	0~167772150位元組	值的長度+3個位元組
LONGTEXT	0~4294967295位元組	值的長度+4個位元組

當需儲存大量的字元資料時，可以選擇TEXT系字串，至於選擇哪一個看儲存字元長度。

BLOB系字串

類型	位元組
TINYBLOB	0~255位元組，值的長度+1個位元組
BLOB	0~65535位元組，值的長度+2個位元組
MEDIUMBLOB	0~167772150位元組，值的長度+3個位元組
LONGBLOB	0~4294967295位元組，值的長度+4個位元組

與TEXT系類似，不同的是BLOB系儲存的可以是位元據（片、視頻、音樂），而TEXT系儲存的只能是字元資料

2.4 日期和時間類型

類型名	位元組	格式	範圍
date	3	YYYY-MM-DD	1000-01-01 ~ 9999-12-31
datetime	8	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
timestamp	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 ~ 2038
year	1	YYYY	1901 ~ 2155
TIME	3		-838:59:59 ~ 838:59:59

通過命令 select curdate(); 或select now();來查看當前日期

日期與時間類型的使用

• 如果要表示年月日，一般使用date
• 如果要表示年月日時分秒，一般使用datetime
• 如果要頻繁更新目前時間，則可以選擇使用timestamp
• 如果要表示年，可以使用year，佔用位元組少
• 如果要表示時分秒，可以使用time

使用樣本

後記：總結了下MySql的儲存引擎與資料類型，更具體的用法再慢慢完善吧~

MySQL儲存引擎與資料類型