MySQL資料類型 轉

MySQL資料類型介紹0評/679閱發表於: 2011-01-24 13:15

 

1. MySQL的資料類型 

在mysql中有如下幾種資料類型： 

• 數值型 

數值是諸如32 或153.4 這樣的值。mysql 支援科學標記法，科學標記法由整數或浮點數後跟“e”或“e”、一個符號（“+”或“-”）和一個 整數指數來表示。1.24e+12 和23.47e-1 都是合法的科學標記法表示的數。而1.24e12 不是合法的，因為指數前的符號未給出。 

浮點數由整數部分、一個小數點和小數部分組成。整數部分和小數部分可以分別為空白，但不能同時為空白。 

數值前可放一個負號“-”以表示負值。 

• 字元（串）型 

字元型（也叫字串型，簡稱串）是諸如“hello, world!”或“一個饅頭引起的血案”這樣的值，或者是電話號碼87398413這樣的值。既可用單引號也可用雙引號將串值括起來。

初學者往往分不清數值87398143和字串87398143的區別。都是數字啊，怎麼一個要用數值型，一個要用字元型呢？關鍵就在於：數值型的 87398143是要參與計算的，比如它是金融中的一個貨款總額；而字元型的87398143是不參與計算的，只是表示電話號碼，這樣的還有街道號碼、門 牌號碼等等，它們都不參與計算。 

• 日期和時間型 

日期和時間是一些諸如“2006-07-12”或“12:30:43”這樣的值。mysql還支援日期/時間的組合，如“2006-07-12 12:30:43”。 

• null值

null表示未知值。比如填寫表格中郵寄地址不清楚留空不填寫，這就是null值。 

我們用create table語句建立一個表（參看前面的章節），這個表中包含列的定義。例如我們在前面建立了一個joke表，這個表中有content和writer兩個列：   

create table ( content varchar(20) not null, writer varchar(10) null )

 定義一個列的文法如下：   

col_name col_typy [col_attributes] [general_attributes]

其中列名由col_name 給出。列名可最多包含64個字元，字元包括字母、數字、底線及貨幣符號。列名可以名字中合法的任何符號（包括數字）開頭。 但列名不能完全由數字組成，因為那樣可能使其與資料分不開。mysql保留諸如select、delete和create這樣的詞，這些詞不能用做列名， 但是函數名（如pos 和min）是可以使用的。

列類型col_type表示列可儲存的特定值。列類型說明符還能表示存放在列中的值的最大長度。對於某些類型，可用一個數值明確地說明其長度。而另外一些 值，其長度由類型名蘊含。例如，char(10) 明確指定了10個字元的長度，而tinyblob值隱含最大長度為255個字元。有的類型說明符允許指 定最大的顯示寬度（即顯示值時使用多少個字元）。浮點類型允許指定小數位元，所以能控制浮點數的精度值為多少。 

可以在列類型之後指定可選的類型說明屬性，以及指定更多的常見屬性。屬性起修飾類型的作用，並更改其處理列值的方式，屬性有以下類型： 

1. 專用屬性用於指定列。例如，unsigned 屬性只針對整型，而binary屬性只用於char 和varchar。 
2. 通用屬性除少數列之外可用於任意列。可以指定null 或not null 以表示某個列是否能夠存放null。還可以用 default，def_value 來表示在建立一個新行但未明確給出該列的值時，該列可賦予值def_value。def_value 必須為一個常 量；它不能是運算式，也不能引用其他列。不能對blob 或text 列指定預設值。 

如果想給出多個列的專用屬性，可按任意順序指定它們，只要它們跟在列類型之後、通用屬性之前即可。類似地，如果需要給出多個通用屬性，也可按任意順序給出它們，只要將它們放在列類型和可能給出的列專用屬性之後即可。

2. MySQL的列（欄位）類型 

資料庫中的每個表都是由一個或多個列（欄位）構成的。在用create table語句建立一個表時，要為每列（欄位）指定一個類型。列（欄位）的類型比資料類型更為細化，它精確地描述了給定表列（欄位）可能包含的值的種類，如是否帶小數、是否文字很多。 

2.1數值列類型 

mysql有整數和浮點數值的列類型，如表1所示。整數列類型可以有符號也可無符號。有一種特殊的屬性允許整數列值自動產生，這對需要唯一序列或標識號的應用系統來說是非常有用的。

類型	說明
tinyint	非常小的整數
smallint	較小整數
mediumint	中等大小整數
int	標準整數
bigint	較大整數
float	單精確度浮點數
double	雙精確度浮點數
decimal	一個串的浮點數

 

表1：數值列類型

每種數實值型別的名稱和取值範圍如表2所示。

類型說明	取值範圍
tinyint[(m)]	有符號值：-128 到127（- 27 到27 - 1）  無符號值：0到255（0 到28 - 1）
smallint[(m)]	有符號值：-32768 到32767（- 215 到215 - 1）  無符號值：0到65535（0 到21 6 - 1）
mediumint[(m)]	有符號值：-8388608 到8388607（- 22 3 到22 3 - 1 ）  無符號值：0到16777215（0 到22 4 - 1）
int[(m)]	有符號值：-2147683648 到2147683647（- 231 到231- 1）  無符號值：0到4294967295（0 到232 - 1）
bigint[(m)]	有符號值：-9223372036854775808 到9223373036854775807（- 263到263-1）  無符號值：0到18446744073709551615（0到264 – 1）
float[(m, d)]	最小非零值：±1.175494351e - 38
double[(m,d)]	最小非零值：±2.2250738585072014e - 308
decimal (m, d)	可變；其值的範圍依賴於m 和d

 

表2：數值列類型的取值範圍

各種類型值所需的儲存量如表3所示。

類型說明	儲存需求
tinyint[(m)]	1位元組
smallint[(m)]	2位元組
mediumint[(m)]	3位元組
int[(m)]	4位元組
bigint[(m)]	8位元組
float[(m, d)]	4位元組
double[(m, d)]	8位元組
decimal (m, d)	m位元組（mysql < 3.23），m+2位元組（mysql > 3.23 ）

 

表3：數值列類型的儲存需求

mysql提供了五種整型： tinyint、smallint、mediumint、int和bigint。int為integer的縮寫。這些類型在 可表示的取值範圍上是不同的。整數列可定義為unsigned從而禁用負值；這使列的取值範圍為0以上。各種類型的儲存量需求也是不同的。取值範圍較大的 類型所需的儲存量較大。 

mysql 提供三種浮點類型： float、double和decimal。與整型不同，浮點類型不能是unsigned的，其取值範圍也與整型不同， 這種不同不僅在於這些類型有最大值，而且還有最小非零值。最小值提供了相應類型精度的一種度量，這對於記錄科學資料來說是非常重要的（當然，也有負的最大 和最小值）。 

在選擇了某種數實值型別時，應該考慮所要表示的值的範圍，只需選擇能覆蓋要取值的範圍的最小類型即可。選擇較大類型會對空間造成浪費，使表不必要地增大，處 理起來沒有選擇較小類型那樣有效。對於整型值，如果資料取值範圍較小，如人員年齡或兄弟姐妹數，則tinyint最合適。mediumint能夠表示數百 萬的值並且可用於更多類型的值，但儲存代價較大。bigint在全部整型中取值範圍最大，而且需要的儲存空間是表示範圍次大的整型int類型的兩倍，因此 只在確實需要時才用。對於浮點值，double佔用float的兩倍空間。除非特別需要高精度或範圍極大的值，一般應使用只用一半儲存代價的float型 來表示資料。 

在定義整型列時，可以指定可選的顯示尺寸m。如果這樣，m應該是一個1 到255的整數。它表示用來顯示列中值的字元數。例如，mediumint(4) 指定了一個具有4個字元顯示寬度的mediumint列。如果定義了一個沒有明確寬度的整數列，將會自動分配給它一個預設的寬度。預設值為每種類型的“最 長”值的長度。如果某個特定值的可列印表示需要不止m個字元，則顯示完全的值；不會將值截斷以適合m個字元。 

對每種浮點類型，可指定一個最大的顯示尺寸m 和小數位元d。m 的值應該取1 到255。d的值可為0 到3 0，但是不應大於m - 2（如果熟悉 odbc 術語，就會知道m 和d 對應於odbc 概念的“精度”和“小數點位元”）。m和d對float和double 都是可選的，但對於 decimal是必須的。在選項m 和d時，如果省略了它們，則使用預設值。 

2.2字串列類型

mysql提供了幾種存放字元資料的串類型，其類型如下：

類型名	說明
char	定長字串
varchar	可變長字串
tinyblob	非常小的blob（二進位大對象）
blob	小blob
mediumblob	中等的blob
longblob	大blob
tinytext	非常小的文本串
text	小文本串
mediumtext	中等文本串
longtext	大文本串
enum	枚舉；列可賦予某個枚舉成員
set	集合；列可賦予多個集合成員

 

表4：字串列類型

下表給出了mysql 定義串值列的類型，以及每種類型的最大尺寸和儲存需求。對於可變長的列類型，各行的值所佔的儲存量是不同的，這取決於實際存放在列中的值的長度。這個長度在表中用l 表示。

類型說明	最大尺寸	儲存需求
char( m)	m 位元組	m 位元組
varchar(m)	m 位元組	l + 1位元組
tinyblob, tinytext	28- 1位元組	l + 1位元組
blob, text	216 - 1 位元組	l + 2位元組
mediumblob, mediumtext	224- 1位元組	l + 3位元組
longblob, longtext	232- 1位元組	l + 4位元組
enum(“value1”, “value2”, ...)	65535 個成員	1 或2位元組
set (“value1”, “value2”, ...)	64個成員	1、2、3、4 或8位元組

 

表5：串列類型最大尺寸及儲存需求

l 以外所需的額外位元組為存放該值的長度所需的位元組數。mysql 通過儲存值的內容及其長度來處理可變長度的值。這些額外的位元組是不帶正負號的整數。請注意， 可變長類型的最大長度、此類型所需的額外位元組數以及佔用相同位元組數的不帶正負號的整數之間的對應關係。例如，mediumblob 值可能最多224 - 1字 節長並需要3 個位元組記錄其結果。3 個位元組的整數類型mediumint 的最大無符號值為224 - 1。這並非偶然。

2.3日期時間列類型 

mysql 提供了幾種時間值的列類型，它們分別是： date、datetime、time、timestamp和year。下表給出了mysql 為定義儲存日期和時間值所提供的這些類型，並給出了每種類型的合法取值範圍。

類型名	說明
date	“yyyy-mm-dd”格式表示的日期值
time	“hh:mm:ss”格式表示的時間值
datetime	“yyyy-mm-dd hh:mm:ss”格式
timestamp	“yyyymmddhhmmss”格式表示的時間戳記值
year	“yyyy”格式的年份值

 

表6：日期時間列類型

類型名	取值範圍	儲存需求
date	“1000-01-01”到“9999-12-31”	3位元組
time	“-838:59:59”到“838:59:59”	3位元組
datetime	“1000-01-01 00:00:00” 到“9999-12-31 23:59:59”	8位元組
timestamp	19700101000000 到2037 年的某個時刻	4位元組
year	1901 到2155	1位元組

 

表7： 日前時間列類型的取值範圍和儲存需求

下面舉個例子：  

create table student ( name varchar(20) not null， chinese tinyint(3)， maths tinyint(3)， english tinyint(3)， birthday date )

 這個例子建立一個student表，這個表中有name欄位，字元類型列，不允許null（空值）。有chinese、maths和english三個整數類型列。還有個birthday日期類型列。

MySQL decimal 類型

一般賦予浮點列的值被四捨五入到這個列所指定的十進位數。如果在一個FLOAT(8, 1)的列中儲存1. 2 3 4 5 6，則結果為1. 2。如果將相同的值存入FLOAT(8, 4) 的列中，則結果為1. 2 3 4 6。這表示應該定義具有足夠位元的浮點列以便得到儘可能精確的值。如果想精確到千分之一，那就不要定義使該類型僅有兩位小數。

浮點值的這種處理在MySQL3.23 中有例外，FLOAT(4) 和FLOAT(8) 的效能有所變化。這兩種類型現在為單精確度（4 位元組）和雙精確度（8 位元組）的類型，在其值按給出的形式存放（只受硬體的限制）這一點上說，這兩種類型是真浮點類型。

DECIMAL 類型不同於FLOAT和DECIMAL，其中DECIMAL 實際是以串存放的。DECIMAL 可能的最大取值範圍與DOUBLE 一樣，但是其有效取值範圍由M 和D 的值決定。如果改變M 而固定D，則其取值範圍將隨M 的變大而變大。表2 - 7的前三行說明了這一點。如果固定M 而改變D，則其取值範圍將隨D 的變大而變小（但精度增加）。表2 - 7的後三行說明了這一點。

給定的DECIMAL 類型的取值範圍取決於MySQL的版本。對於MySQL3.23 以前的版本，DECIMAL(M, D) 列的每個值佔用M 位元組，而符號（如果需要）和小數點包括在M 位元組中。因此，類型為DECIMAL(5, 2) 的列，其取值範圍為-9.99 到9 9 . 9 9，因為它們覆蓋了所有可能的5 個字元的值。

正如MySQL3.23 一樣，DECIMAL 值是根據ANSI 規範進行處理的，ANSI 規範規定DECIMAL(M, D) 必須能夠表示M 位元字及D 位小數的任何值。例如，DECIMAL(5, 2) 必須能夠表示從-999.99 到999.99 的所有值。而且必須儲存符號和小數點，因此自MySQL3.23以來DECIMAL 值占M + 2 個位元組。對於DECIMAL(5, 2)，“最長”的值（- 9 9 9 . 9 9）需要7個位元組。在正取值範圍的一端，不需要正號，因此MySQL利用它擴充了取值範圍，使其超過了ANSI 所規範所要求的取值範圍。如DECIMAL(5, 2) 的最大值為9 9 9 9 . 9 9，因為有7 個位元組可用。

簡而言之，在MySQL3.23 及以後的版本中，DECIMAL(M, D) 的取值範圍等於更早版本中的DECIMAL(M + 2, D) 的取值範圍。在MySQL的所有版本中，如果某個DECIMAL 列的D 為0，則不儲存小數點。這樣做的結果是擴充了列的取值範圍，因為過去用來儲存小數點的位元組現在可用來存放其他數字了。