MySQL儲存引擎與資料類型

標籤：comm   不同   浮點   profiling   規範   ase   ports   float   更新   

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的具體資訊。例如以下所看到的：

ZEROFILL(0填充)
整形設定0填充，會使類型自己主動成為unsigned，而且各個整形類型都有預設填充位。例如以：

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類型儲存的是字串，而浮點數類型儲存的是近似值。對於精度要求不高的情況下。視儲存的資料的範圍而定。

做例如以下一個測試：
建立一個有float類型的f_data , double 類型的db_data, decimal 類型的 dm_data的表。並同一時候插入123.456到三個欄位中。

然後我們查詢123.456的f_data時發現查詢為空白，而查詢‘123.456’ 的dm_data時查詢出了結果。實驗驗證了上述結論，float儲存的是近似值。所以查詢不到。而decimal儲存的是字串，所以能夠精確查詢。

位類型

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

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

能夠使用b’value’符號寫位欄位值。

value是一個用0和1寫成的二進位值。例如以下：

2.3 字串 2.3.1 char系字串類型

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

在資料存放區範圍合適的情況下，當儲存的資料常常變動時使用推薦varchar。否則使用char 。

2.3.2 BINARY系字串

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

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

2.3.3 TEXT系字串類型

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

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

2.3.4 BLOB系字串

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

與TEXT系相似。不同的是BLOB系儲存的能夠是位元據（片、視頻、音樂），而TEXT系儲存的僅僅能是字元資料

2.3.5 枚舉類型

ENUM(‘value1’,’value2’,…)

枚舉類型僅僅能有一個值的字串，從值列’value1’。’value2’，…，NULL中或特殊 ”錯誤值中選出。ENUM列最多能夠有65,535個截然不同的值。

ENUM值在內部用整數表示，所以我們能夠使用整數插入或查詢來取代字串。

例如以下我們建立一個表，包括枚舉欄位day，我們插入枚舉值

我們還能夠通過整數值插入和查詢

當我們插入非設定的枚舉值時會出錯，例如以下錯誤：

mysql> insert into t_enum values(‘星期天‘);ERROR 1265 (01000): Data truncated for column ‘day‘ at row 1

2.3. 6 SET類型

SET(‘value1’,’value2’,…)

一個set集合。當中的字串對象能夠有零個或多個值的組合。每一個值必須來自列值’value1’，’value2’。…SET列最多能夠有64個成員。SET值在內部用整數表示。

插入時的資料順序與終於在表中的順序無關。

使用示範範例：

建立了一個t_set的表。day是一個set類型的欄位，能夠插入set中的隨意值的組合。插入順序不影響終於插入到表中的資料。能夠通過指定字串來查詢，此字串的順序卻對查詢結果有影響，這是一個奇怪的地方

使用整數插入、查詢、刪除、更新

set內部使用整數來表示，value、二進位、十進位的相應關係例如以下

當我們插入數字15時，即1+2+4+8。各個set的value相應的值（星期一~星期四）
當我們插入數字8時，即相應value為8的‘星期四’，其它組合依次類推

刪除指定值（更新與之相似，不列出了）

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儲存引擎與資料類型