MySQL資料型別

1.        資料型別

1.1.        資料型別：概覽

在 MySQL 中，可用的資料型別分為四個主要類別： 數值、字元、二進位制、時間

在每個類別中，存在多種特定的資料型別，這些資料型別使用的記憶體大小和磁碟空間各不相同，因此會對效能產生不同的影響。對於單個記錄，為列選擇最佳資料型別所產生的效能影響相對較小，但隨著資料庫的增大，這些較小的影響可能會匯聚成大的影響。應在設計過程中事先考慮這些影響，以免造成效能問題。

MySQL 還支援“空間擴充套件” 儲存引擎功能。透過空間擴充套件，能夠以字元和二進位制格式生成、儲存和分析地理特徵。

資料型別的 ABC 要素：

Appropriate （適當）： 需要以最適合資料所代表的項的型別來表示資料。

Brief （簡潔）： 選擇所用儲存空間最少的資料型別。這可節省資源並提高效能。

Complete （完整）： 選擇的資料型別應分配有可儲存特定項的最大可能值的充足空間。

 

示例 1 ：列宣告

CREATE TABLE people (

id INT,

first_name CHAR(30),

last_name CHAR(30) );

示例 2 ：不允許負值和未知值

CREATE TABLE people (

id INT UNSIGNED NOT NULL,

first_name CHAR(30),

last_name CHAR(30) );

建立表時，宣告其中每個列，包括列名、資料型別（指示列中值的種類）以及某些屬性（選項，用於更加具體地定義 MySQL 應如何處理列）。

 

1.2.        數值資料型別

為儲存數值資料， MySQL 提供了以下數值資料型別類：

整數： 整數沒有小數部分。即，沒有小數位的單個整數值。

浮點數： 表示包含整數部分、小數部分或同時包括二者的近似值數值。此類資料型別使用伺服器主機的 CPU 所用的本機二進位制浮點格式 (IEEE 754) 來表示值。該資料型別用於儲存和計算會很高效，但值會存在舍入誤差。

定點數： 包含整數部分、小數部分或同時包括二者。

BIT ： BIT 列規範規定了一個寬度，指明每個值的位數（ 1 至 64 位）。

 

“精度” 和“範圍” 是適用於浮點值和定點值（這兩種型別可以同時包含整數部分和小數部分）的術語。

精度 ： 有效位數。

範圍 ： 小數點右側的位數。

 

數值資料型別表：

類	型別	說明
整數	TINYINT	極小整數資料型別
整數	SMALLINT	較小整數資料型別
整數	MEDIUMINT	中型整數資料型別
整數	INT	常規（平均）大小的整數資料型別
整數	BIGINT	較大整數資料型別
浮點數	FLOAT	小型單精度（四個位元組）浮點數
浮點數	DOUBLE	常規雙精度（八個位元組）浮點數
定點數	DECIMAL	包含整數部分、小數部分或同時包括二者的   精確值數值
BIT	BIT	位欄位值

 

整數型別按精度升序列出。即，後面的每個整數資料型別可以容納的整數值範圍要大於列表中前一個資料型別的整數值範圍。

浮點資料型別包括 FLOAT 和 DOUBLE 。這兩種型別均可用於表示包含整數部分、小數部分或同時包括二者的近似值數值。 FLOAT 和 DOUBLE 資料型別使用伺服器主機的 CPU 所用的本機二進位制浮點格式 (IEEE 754) 來表示值。該資料型別用於儲存和計算會很高效，但值會存在舍入誤差。對於 FLOAT 和 DOUBLE ：如果列可為空，則預設值為 NULL ；如果列不可為空，則預設值為 0 （數值零）。注： 您可以使用 = 後跟浮點值進行比較；但由於可能會出現舍入誤差，因此結果可能並不總是與預期一樣。

DECIMAL 使用小數點固定的儲存格式： DECIMAL 列中的所有值均包含相同的小數位數，並且完全按給定方式進行儲存。 DECIMAL 值儲存起來不如 FLOAT 或 DOUBLE 值高效，但 DECIMAL 值沒有舍入誤差，因此更加精確。例如， DECIMAL 資料型別通常用於儲存貨幣值（其中每個值的精度比其儲存大小更加重要）。

BIT 列規範規定了一個寬度，指明每個值的位數（ 1 至 64 位）。

 

1.3.        字串資料型別

字串資料型別表示給定字符集中的一個字母數字字元序列，用於儲存文字或二進位制資料，幾乎在每種程式語言中都有實現，支援字符集和 COLLATION 。

這些型別在以下幾個方面有所不同：

²   資料是以固定長度格式還是可變長度格式儲存

²   可儲存的最大長度

²   該型別是否支援非結構化字串值

 

字串資料型別

型別	說明
CHAR	固定長度字串，最多為 255 個字元
VARCHAR	可變長度字串，最多為 65,535 個字元
TINYTEXT	可變長度字串，最多為 255 個字元
TEXT	可變長度字串，最多為 65,535 個字元
MEDIUMTEXT	可變長度字串，最多為 16,777,215 個字元
LONGTEXT	可變長度字串，最多為 4,294,967,295 個   字元
ENUM	由一組固定的合法值組成的列舉
SET	由一組固定的合法值組成的集

 

l   字符集和 COLLATION 支援

字符集：一組指定的符號和編碼

COLLATION ：指定的整理序列，定義字元排序順序，控制各個字元和字串如何進行相互比較；

字串資料型別的列定義可以指定列的字符集或 COLLATION ，屬性適用於 CHAR 、 VARCHAR 、 TEXT 、 ENUM 和 SET 資料型別，如以下示例所示：

CREATE TABLE t

( c1 VARCHAR(20) CHARACTER SET utf8,

c2 TEXT CHARACTER SET latin1

COLLATE latin1_general_cs );

字串具有以下特徵：

²   序列由特定字符集中的字元組成。

²   多位元組字符集中的每個字元所需的位元組數可能是固定的，也可能是可變的。

²   比較基於對字串所關聯的字符集的整理。

²   多位元組字元比較以字元而不是位元組為單位執行。

²   整理將驗證字元的大寫版本和小寫版本是否等效。

²   整理將決定同一個字元的不同重音標記是否等效。

²   整理可以為二進位制，其中基於數值字元值進行比較。

MySQL 具有一個包含大量可供選擇的字符集和整理的列表。選擇正確與否會對效能有很大的影響。要檢視可用的字符集，請使用以下語句：

SHOW CHARACTER SET;

使用 COLLATION 可以為相同的字符集選擇不同的排序順序。例如，要顯示所有 latin1 字符集，請使用以下語句：

SHOW COLLATION LIKE 'latin1%';

 

1.4.        二進位制字串資料型別

         二進位制： 用於儲存固定長度和可變長度的二進位制位元組字串。

         BLOB ： 在資料庫管理系統中儲存為單個值。 BLOB 通常為影像、音訊或其他多媒體物件，儘管有時二進位制程式碼也儲存為 BLOB 。

這些二進位制型別也是較小單元的序列，從這個意義上看，它們是字串型別。與字串不同，組成此類二進位制字串值的位元組不代表字元。因此，二進位制字串沒有附加的字元語義，而且缺少字串類所表示的字符集和整理資訊。

在 MySQL 中， BLOB 與 TEXT 型別非常相似，沒有附加的字符集和 COLLATION 。

類	型別	說明
二進位制	BINARY	類似於 CHAR （固定長度）型別，但儲存的是二進位制位元組字串，而不是非二進位制字串
二進位制	VARBINARY	類似於 VARCHAR （可變長度）型別，但儲存的是二進位制位元組字串，而不是非二進位制字串
BLOB	TINYBLOB	最大長度為 255 個位元組的 BLOB 列
BLOB	BLOB	最大長度為 65,535 個位元組的 BLOB 列
BLOB	MEDIUDMBLOB	最大長度為 16,777,215 個位元組的 BLOB 列
BLOB	LONGBLOB	最大長度為 4,294,967,295 個位元組的 BLOB 列

 

 

1.5.        時間資料型別

儲存日期、時間和年份值（或這些值的組合）

型別	格式	示例
DATE	YYYY-MM-DD	2006-08-04
TIME	hh:mm:ss[.uuuuuu]	12:59:02.123456
DATETIME	YYYY-MM-DD hh:mm:ss[.uuuuuu]	2006-08-04   12:59:02.123
TIMESTAMP	YYYY-MM-DD hh:mm:ss[.uuuuuu]	2006-08-04   12:59:02.12
YEAR	YYYY	2006

YYYY 、 MM 、 DD 、 hh 、 mm 、 ss 和 uuuuuu 分別表示“年” 、 “月” 、 “日” 、 “小時” 、“分鐘” 、 “秒” 和可選的“秒的小數位” 。

透過為該型別提供一個引數，可宣告秒的小數位（可選）。例如， TIME(3) 是 TIME 型別，其中秒的小數位部分最多可達三位。

DATE 值範圍為從 1000-01-01 至 9999-12-31 。

DATETIME 值範圍為從 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999 。

TIMESTAMP 值採用 UTC 進行儲存（將根據需要轉換為當地時間或從當地時間進行轉

換），其範圍為從 1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999 。

可以定義 DATETIME 和 TIMESTAMP 以自動記錄當前日期和時間，而不管對行執行 INSERT 或 UPDATE 操作的時間為何時。

TIMESTAMP 列在很多方面都與 DATETIME 列不同。 TIMESTAMP 列值的範圍比 DATETIME 列值的範圍小，因此儲存每個值所需的位元組數更少。透過為不允許出現 NULL 的 TIMESTAMP 指定 NULL 值，可以將其設定為當前日期和時間。

 

1.6.        空間資料型別

MySQL 支援空間資料型別擴充套件，以便允許生成、儲存和分析地理特徵。地理特徵涉及世界上具有位置的任何事物：實體（山脈、池塘或城市）、空間（城鎮或熱帶地區）、可定義的位置（十字路口）。可以對 InnoDB 、 MyISAM 、 NDB 和 ARCHIVE 儲存引擎表使用空間擴充套件。

MySQL 實現了由開放地理空間協會 (Open Geospatial Consortium, OGC) 提出的“包含幾何型別的 SQL ” 環境的子集。

l   用於單個幾何體值：

- GEOMETRY ：層次結構的根類，任何型別的值

- POINT ：座標空間中的單個位置

- CURVE ：一維幾何體，由點組成的序列

- LINESTRING ：點之間具有線性內插的曲線

- SURFACE ：二維幾何體

- POLYGON ：表示多邊幾何體的平面

l   用於儲存幾何體值的集合：

- MULTIPOINT ： Point 元素

- MULTICURVE ： Curve 元素

- MULTILINESTRING ： LineString 元素

- MULTISURFACE ： Surface 元素

- MULTIPOLYGON ： Polygon 元素

- GEOMETRYCOLLECTION ：任意型別的幾何體

有關空間擴充套件的更多資訊，請參閱《 MySQL 參考手冊》：

http://dev.mysql.com/doc/refman/5.6/en/spatial-extensions.html

 

1.7.        列屬性

列屬性的類別：

數值： 適用於數值資料型別（ BIT 除外）

字串： 適用於非二進位制字串資料型別

常規： 適用於所有資料型別

資料型別	屬性	說明
數值	UNSIGNED	禁止使用負值
僅整數	AUTO_INCREMENT	生成包含連續唯一整數值的序列
字串	CHARACTER SET	指定要使用的字符集
字串	COLLATE	指定字符集整理
字串	BINARY	指定二進位制整理
全部 *	NULL 或 NOT NULL	指示列是否可以包含 NULL 值
全部	DEFAULT	如果未為新記錄指定值，則為其提供預設值

* 除 TIMESTAMP 之外列屬性

以下屬性適用於所有資料型別：

NULL 或 NOT NULL ：如果未指定這兩個屬性中的任何一個，則預設情況下允許列中出現 NULL 值。

DEFAULT ：例如，如果執行 INSERT 語句時沒有為表中的所有列提供值，將使用預設值。

示例：

CREATE TABLE t(

i INT UNSIGNED NOT NULL ,

c CHAR(10) CHARACTER SET utf8 ,

d DATE DEFAULT '2013-01-01');

示例中的表包含一個不能包含 NULL 值的 UNSIGNED 整數列、一個使用 utf8 字符集的字串列，以及一個預設值為“ 2013-01-01 ” 的日期列。

 

l   將資料型別設定為 NULL

在 SQL 中，對錶達式求值的結果可以為 null 。 null 值是一個特殊的值，表示值無法進行計算或未知。

何時使用 NULL ？

在資料庫設計的開始階段，當列出要包含在內的資料時，發現某些資料可能不適用於所有列。請檢查這些情況並決定是否應允許 null 值。此外，如果由於列中出現 null 值而檢測到問題，則可以更改現有表的此設定。

何時不應使用 NULL ？

在某些情況下，不應允許列中使用 null 值。最常見的情況即該列為主鍵時。另一個例子是：當列必須包含值時，資料庫設計才有意義。

型別	說明
Unknown （未知）	存在一個值，但此時不知道精確值。
Not applicable （不適用）	如果指定了一個值，則該值不是準確的表示

 

1.8.        選擇資料型別

考慮哪些資料型別和字符集可以最大限度地減少儲存和磁碟 I/O ；如果儲存的所有字串值的長度相同，則考慮使用固定長度資料型別；如果儲存的字串值不同或者是多位元組字符集則使用可變長度資料型別：對於頻繁使用的字元，使用佔用空間較少的多位元組字符集，使用 BMP (Basic Multilingual Plane) 之外的其他 Unicode 字符集。

         為了獲得最佳的儲存效果，您應該嘗試在所有情況下使用最精確的型別。例如，如果整型列用於 1 到 99999 之間的值，則無符號的 MEDIUMINT 是最佳型別。在表示所有所需值的型別中，此型別使用的儲存空間最少。

         DECIMAL 列的所有基本計算 (+ 、 - 、 * 和 /) 的精度都是 65 位十進位制 ( 以 10 為基數 ) 。

         如果精度不是太重要，或者速度是最高的優先順序， DOUBLE   可能足夠好。為了獲得更高的精度，可以將其轉換定點型別 BIGINT 。這使您能夠使用 64 位整數進行所有計算，然後根據需要將結果轉換回浮點值。

    PROCEDURE ANALYSE 可用於獲得最優列資料型別的建議。