MySql資料類型

標籤：sage   har   影響   推理   十進位   括弧   直接   自訂   屬性   

藍海豹

MySQL資料類型，可以被分為3類：數實值型別、日期和時間類型以及字串(字元)類型

 方括弧(“[”和“]”)指出可選的類型修飾符的部分

  M 
    指出最大的顯示尺寸。最大的合法的顯示尺寸是 255 。 
  D 
   適用於浮點類型並且指出跟隨在十進位小數點後的數位的數量。最大可能的值是30，但是應該不大於M-2。

  UNSIGNED
    為“無符號”的意思, 即為非負數。是MYSQL自訂的類型，非標準SQL。unsigned 屬性只針對整型,用途：
    1.UNSIGNED 可用來約束資料的範圍，例如有些年齡這種值一般能是負數，那麼就可以設定一個 UNSIGNED ，這樣可以不允許負數插入。
    2.可以增加數值範圍（相當於把負數那部分加到正數上）。不過少用，不方便移植。

  ZEROFILL
    填零，即在數字長度不夠的資料前面填充0，以達到設定的長度，MySQL中欄位設定為該屬性時，將為該列自動地增加UNSIGNED屬性。

一、數實值型別

    整型：（1個位元組是8位）

            在MySQL中建立整型欄位INT（或其它像tinyint）時，可以設定該欄位的位元。如int(11),int(5),如果不指定位元，INT型預設長度為11。欄位插入長度與int設定的M無關，當插入長度大於設定的M時，欄位值不會被截斷，還是按照類型的實際精度進行儲存。
            整型欄位有個ZEROFILL屬性，在數字長度不夠的資料前面填充0，以達到設定的長度。M值只當屬性為ZEROFILL時，才有區別效果。
            如表結構為id1 int(10) ，id2 int(5),對應資料為  第一行為（1,1），第二行為（1111111,1111111），當id1，id2欄位設定為ZEROFILL屬性時，顯示資料為第一行為（0000000001,00001），第二行為（0001111111,1111111），不夠的位元會以0補齊。

資料類型	大小	M (預設值)	範圍（有符號）	範圍（無符號）	用途
tinyint [(M)] [UNSIGNED] [ZEROFILL]	1位元組	4	-128~127  (-27~27-1)	0 ~ 255 (0 ~ 28-1)	非常小整數值
smallint [(M)] [UNSIGNED] [ZEROFILL]	2位元組	6	-32768~32767（-215 ~ 215-1)	0 ~ 65535 (0 ~ 216-1)	較小整數
mediumint [(M)] [UNSIGNED] [ZEROFILL]	3位元組	9	-8388608 ~ 8388607 （-223 ~ 223-1）	0 ~ 16777215 (0 ~ 224-1)	中等大小整數
int [(M)] [UNSIGNED] [ZEROFILL]	4位元組	11	-2147483648 ~ 2147483647（-231 ~ 231-1）	0 ~ 4294967295 (0 ~ 232-1)	標準整數
integer [(M)] [UNSIGNED] [ZEROFILL]	4位元組	11	-2147483648 ~ 2147483647（-231 ~ 231-1）	0 ~ 4294967295 (0 ~ 232-1)	和int相同
bigint [(M)] [UNSIGNED] [ZEROFILL]	8位元組	20	-9223372036854775808 ~9223372036854775807 （-263 ~ 263-1）	0 ~ 18446744073709551615 (0 ~ 264-1)	較大整數

   浮點型：

            (M,D) 表示總共M位，D個小數位，D包含於M中。浮點類型不能是unsigned的。 

            對每種浮點類型，可指定一個最大的顯示尺寸M和小數位元D。M的值應該取1到255。D的值可為0到30，但是不應大於M-2。M和D對float和double都是可選的，但對於decimal是必須的，在選項M和D時，如果省略了它們，則使用預設值，如果D被省略，它被設定為0。如果M被省掉，它被設定為10。

資料類型	大小	用途
float [(M,D)] [ZEROFILL]	4位元組	單精確度浮點型，8位精度；參數m隻影響顯示效果，不影響精度，d卻不同，會影響到精度；m是十進位數位總個數，d是小數點後面的數字個數
double[(M,D)] [ZEROFILL]	8位元組	雙精確度浮點型，16位精度；參數m隻影響顯示效果，不影響精度，d卻不同，會影響到精度
real[(M,D)] [ZEROFILL]	8位元組	同double
decimal[(M[,D])] [ZEROFILL]	4位元組	decimal(m,d) 定點類型浮點型在資料庫中存放的是近似值，而定點類型在資料庫中存放的是精確值。參數m是定點類型數位最大個數（精 度），範圍為0~65，d小數點右側數位個數，範圍為0~30，但不得超過m。對定點數的計算能精確到65位元字。DECIMAL 資料類型用於精度要求非常高的計算中，這種類型允許指定數值的精度和計數方法作為選擇參數。精度在這裡指為這個值儲存的有效數位總個數，而計數方法表示小數點後數位位元。比如語句 DECIMAL(7,3) 規定了儲存的值不會超過 7 位元字，並且小數點後不超過 3 位
numeric[(M,D)]  [ZEROFILL]	4位元組	同decimal

           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，則不儲存小數點。這樣做的結果是擴充了列的取值範圍，因為過去用來儲存小數點的位元組現在可用來存放其他數字了。

二、字元類型

      對於可變長的字元類型，其值所佔的儲存量是不同的，這取決於實際存放在列中的值的長度，這個長度用L表示。

資料類型	大小（範圍）	用途
char(M)[BINARY]	M個位元組，0 <= M <= 255 (L為固定的=255，不夠補空格)	定長字串；CHAR 類型可以使用 BINARY 修飾符。當用於比較運算時，這個修飾符使 CHAR 以二進位方式參於運算，而不是以傳統的區分大小寫方式。CHAR值根據預設字元集以大小寫不區分的方式排序和比較，除非給出BINARY關鍵詞
varchar(M) [BINARY]	L+1個位元組，其中L <= M 且0 <= M <= 65535（MySQL5.0之前都是最大255)	變長字串；VARCHAR 類型在使用 BINARY 修飾符時與 CHAR 類型完全相同
tinyblob，tinytext	L+1個位元組，其中L < 28-1 (255)	tinyblob：不超過 255 個字元的二進位字串；tinytext：短文本字串
blob,text	L+2個位元組，其中L < 216-1 (65535)	blob：二進位形式的長文本資料，在分類和比較時BLOB 類型區分大小寫;text：長文本資料，在分類和比較時TEXT 不區分大小寫
mediumblob,mediumtext	L+3個位元組，其中L < 224-1	mediumblob：二進位形式的中等長度文本資料；mediumtext ：中等長度文本資料，
longblob,longtext	L+4個位元組，其中L < 232-1	longblob：二進位形式的極大文本資料   ；longtext ：極大文本資料
enum(‘value1‘,‘value2‘,...)	1或2個位元組，取決於枚舉值的個數(最多65,535個值)
set(‘value1‘,‘value2‘,...)	1、2、3、4或者8個位元組，取決於set成員的數目(最多64個成員)

三、日期類型

      MySQL 帶有 5 個不同的資料類型可供選擇。它們可以被分成簡單的日期、時間類型，和混合日期、時間類型。根據要求的精度，子類型在每個分類型中都可以使用，並且 MySQL 帶有內建功能可以把多樣化的輸入格式變為一個標準格式。

類型	大小	範圍	格式	用途
YEAR[(2|4)]	1位元組	1901/2155	YYYY	年份值
DATE	3位元組	‘1000-01-01‘--‘9999-12-31‘	YYYY-MM-DD	日期值
TIME	3位元組	‘-838:59:59‘到‘838:59:59‘	HH:MM:SS	時間值或期間
DATETIME	8位元組	‘1000-01-01 00:00:00‘--‘9999-12-31 23:59:59‘	YYYY-MM-DD HH:MM:SS	混合日期和時間值
TIMESTAMP[(M)]	8位元組	1970-01-01 00:00:00/2037 年某時	YYYYMMDD HHMMSS	混合日期和時間值，時間戳記

 (1)YEAR

   給YEAR類型賦值可以有三種方法。

   第一種是直接插入4位字串或者4位元字。

   第二種是插入2位字串，這種情況下如果插入‘00’~‘69’，則相當於插入2000~2069；如果插入‘70’~‘99’，則相當於插入1970~1999。第二種情況下插入的如果是‘0’，則與插入‘00’效果相同，都是表示2000年。

    第三種是插入2位元字，它與第二種（插入兩位字串）不同之處僅在於：如果插入的是一位元字0，則表示的是0000，而不是2000年。所以在給YEAR類型賦值時，一定要分清0和‘0’，雖然兩者相差個引號，但實際效果確實相差了2000年。

(2)DATE

   MySQL是以YYYY-MM-DD格式來顯示DATE類型的值，插入資料時，資料可以保持這種格式。另外，MySQL還支援一些不嚴格的文法格式，分隔字元“-”可以用“@”、“.”等眾多富豪來替代。在插入資料時，也可以使用“YY-MM-DD”格式，YY轉化成對應的年份的規則與YEAR類型類似。如果我們想插入當前系統的時間，則可以插入CURRENT_DATE或者NOW()。
   允許使用字串或數字把值賦給DATE列。

(3)TIME

    TIME類型表示為“時：分：秒”，儘管小時範圍一般是0~23，但是為了表示某些特殊時間間隔，MySQL將TIME的小時範圍擴發了，而且支援負值。對TIME類型賦值，標準格式是"HH：MM：SS"，但不一定非要是這種格式。如果插入的是"D HH：MM：SS"格式，則類似插入了"（D*24+HH）：MM：SS"。比如插入"2 23:50:50"，相當於插入了"71:50:50"。如果插入的是"HH：MM"或"SS"格式，則效果是其他未被表示位的值賦為零值。比如插入"30"，相當於插入了"00:00:30"；如果插入"11:25"，相當於插入了"11:25:00"。
    另外也可以插入‘D HH’和‘D HH：MM’，效果按上面的例子可以推理出來了吧。 在MySQl中，對於‘HHMMSS‘格式，系統能夠自動轉化為標準格式。如果我們想插入當前系統的時間，則可以插入CURRENT_TIME或者NOW()。     
    TIME類型允許使用字串或數字把值賦給TIME列,只佔3個位元組，如果只是儲存時間資料，它最合適了。
    需要注意的是，沒有冒號分隔字元的 TIME 類型值，將會被 MySQL 理解為持續的時間，而不是時間戳記。

(4)DATETIME

    通常用於自動儲存包含當前日期和時間的時間戳記，並可在需要執行大量資料庫事務和需要建立一個調試和審查用途的審計跟蹤的應用程式中發揮良好作用。

(5)TIMESTAMP

    通常用於自動儲存包含當前日期和時間的時間戳記，並可在需要執行大量資料庫事務和需要建立一個調試和審查用途的審計跟蹤的應用程式中發揮良好作用。   
    TIMESTAMP的取值範圍比較小，沒有DATETIME的取值範圍大，因此輸入值時一定要保證在TIMESTAMP的範圍之內。它的插入也與插入其他日期和時間資料類型類似。
    那麼TIMESTAMP類型如何插入目前時間？第一，可以使用CURRENT_TIMESTAMP；第二，輸入NULL，系統自動輸入當前的TIMESTAMP；第三，無任何輸入，系統自動輸入當前的TIMESTAMP。
    另外有很特殊的一點：TIMESTAMP的數值是與時區相關.
    MySQL以YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD或YYMMDD格式來顯示TIMESTAMP值，取決於是否M是14（或省略)、12、8或6，但是允許你使用字串或數字把值賦給TIMESTAMP列。一個TIMESTAMP列對於記錄一個INSERT或UPDATE操作的日期和時間是有用的，因為如果你不自己給它賦值，它自動地被設定為最近操作的日期和時間。你以可以通過賦給它一個NULL值設定它為當前的日期和時間

MySql資料類型