MySQL資料類型

標籤：style   blog   http   ar   color   使用   sp   strong   on   

　　MySQL與SQLServer的資料類型大同小異。因此，可以忽略與SQLServer相同的地方，在SQLServer的基礎上學習。

一、整型的顯示寬度

　　MySQL資料類型與SQLServer有些不同，其定義的基本形式如下：

整數型別(顯示寬度)

　　注意，正確的理解，其實是顯示寬度，因為整型的顯示寬度，對數值大小無影響，只是當設定了zerofill的時候，在顯示的時候補0而已。

　　

　　從上面的顯示我們看到，設定了寬度為8，並且zerofill了，這樣在查詢的時候，會在前面自動補0。寬度可以不設定，因為整數資料類型都有預設的顯示寬度，不設就採用預設的。

 　　另外，即使設定了顯示寬度，也能賦超過顯示寬度的值，整型的顯示寬度僅僅用於顯示補0。

二、浮點型與定點型

　　MySQL中可以指定浮點數和定點數的精度

資料類型(M,D)

• M：精度，資料的總長度；
• D：標度，小數點後的長度；

　　這種類型MySQL裡有3種如：Float(6,2)、Double(6,2)、Decimal(6,2)；

　　其區別在於：

1. 當不指定精度時，Float、Double預設會儲存實際精度，而Decimal預設是整數；
2. 當標度不夠時，都會四捨五入，但Decimal會警告資訊。

　　

三、日期與時間類型

　　MySQL日期時間所有種類如下：

類型	位元組數	取值範圍	零值
YEAR	1	1901~2155	0000
DATE	4	1000-01-01~9999-12-31	0000:00:00
TIME	3	-838:59:59~838:59:59	00:00:00
DATETIME	8	1000-01-01 00:00:00~9999-12-31 23:59:59	0000-00-00 00:00:00
TIMESTAMP	4	1970-01-01 08:00:01~2038-01-19 11:14:07	00000000000000

　　DATE類型前面可以加個D，D表示天即24小時，如往DATE類型插入 ‘2 15:20:20‘；

　　

　　另外TimeStamp類型與DateTime在無輸入的情況下要注意下：

1. TimeStamp使用Current_TimeStamp()而DateTime使用NOW(來擷取目前時間)；
2. 輸入NULL時，系統會輸入系統當前日期與時間；
3. 無任何輸入時，系統會輸入系統當前日期與時間；

四、字串類型

　　在MySQL中，字串類型包括CHAR、VARCHAR、BLOB、TEXT、ENUM、SET。

　　1、CHAR與VARCHAR

　　其定義方式為：

字串類型(M)

　　這個M是長度的意思，插入該列的字串長度將不允許超過M所指定的長度。另外，由於MySQL在建庫時就指定了字元集，因此就不存在nchar、nvarchar、ntext這種資料類型了。

• char最長可取255；
• varchar最長可取65535；

　　2、TEXT

　　TEXT分為4種，與SQLServer不同：

類型	允許的長度	儲存空間
TINYTEXT	0~255位元組	值的長度+2位元組
TEXT	0~65535位元組	值的長度+2位元組
MEDIUMTEXT	0~167772150位元組	值的長度+3位元組
LONGTEXT	0~4294967295位元組	值的長度+4位元組

 　3、ENUM類型

 

　　ENUM類型(枚舉類型)，與C#的概念一樣，在定義時指定取值範圍。

屬性名稱 ENUM(‘值1‘,‘值2‘,‘值3‘...‘值n‘)

• ENUM有NOT NULL屬性，其預設值為取值列表的第一個元素；
• ENUM無NOT NULL，則ENUM類型將允許插入NULL，並且NULL為預設值；

　　

　　CREATE TABLE Test4(Sex ENUM(‘男‘,‘女‘));　　INSERT INTO Test4 VALUES(‘男‘);　　INSERT INTO Test4 VALUES(‘爺‘);    --這行報錯　　SELECT * FROM Test4;

　　4、SET類型

　　在建立表時，就指定SET類型的取值範圍。

屬性名稱 SET(‘值1‘,‘值2‘,‘值3‘...,‘值n‘)

　　它與ENUM的區別在什麼地方呢？

　　

　　基本上就是多選的ENUM。

　　5、二進位類型

　　二進位類型是在資料庫中儲存位元據的資料類型。二進位類型包括BINARY、VARBINARY、BIT、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB。

類型	取值範圍
BINARY(M)	位元組數為M，允許長度為0~M的定長二進位字串
VARBINARY(M)	允許長度為0~M的變長二進位字串，位元組數為值的長度加1
BIT(M)	M位位元據，M最大值為64
TINYBLOB	可變長位元據，最多255個位元組
BLOB	可變長位元據，最多2的16次方-1個位元組
MEDIUMBLOB	可變長位元據，最多2的24次方-1個位元組
LONGBLOB	可變長位元據，最多2的32次方-1個位元組

　　1、BINARY和VARBINARY

　　兩者唯一的差別在於BINARY當長度不夠時會補\0。

　　2、BIT類型

　　BIT類型與SQLServer裡的就差別大了。 其定義方式為：

BIT(M)

　　其中"M"指定了該二進位的最大位元組長度為M，M的最大值為64。如BIT(4)就是資料類型為BIT類型，長度為4。其能夠儲存的值為0-15。因為變成二進位後，15的值為1111。

　　在查詢BIT類型的資料時，要用BIN(欄位名+0)來將值轉換為二進位顯示。

　　

CREATE TABLE Test6(BB BIT(4));INSERT INTO Test6 VALUES(12);SELECT BIN(BB+0) FROM Test6;

　　上面的結果以二進位顯示，最大的長度為4。

　　3、BLOB類型

　　BLOB類型是一種特殊的二進位類型。BLOB可以儲存資料量很大的位元據，片，視頻等。BLOB類型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它們之間的區別也只是最大長度不同。

五、資料類型的選擇

1. 整型：根據要顯示的最大值決定；
2. 浮點型：要顯示小數。如果要精確到小數點後10位，就選擇DOUBLE，而不應該選擇FLOAT。DECIMAL精度較高，浮點數會出現誤差，如果精度較高，則應選擇定點數DECIMAL；
3. 字串型：定長與變長的區別，CHAR類型佔用空間比較大，但是處理速度比VARCHAR快，如果長度變化不大，如社會安全號碼碼那種，最好選擇CHAR類型。而對於評論字串，最好選擇VARCHAR；
4. 時間：根據需要顯示的類型咯，特別是TIMESTAMP,如果需要顯示的時間與時區對應，就應該選擇TIMESTAMP；
5. ENUM類型和SET類型：長度不同，ENUM類型最多可以由65535個成員，而SET類型最多隻能包含64個成員。且ENUM只能單選，而SET類型可以多選；
6. TEXT類型和BLOB類型：TEXT只能儲存字元資料，而BLOB可以儲存位元據。如果是純文字，適合TEXT。如果是圖片等適合存二進位；

六、問題總結

　　1、儲存路徑的問題

　　MySQL中，如果路徑中使用"\"符號時，這個符號會被過濾。解決的辦法是路徑中用"/"或"\\"來代替"\"。這樣MySQL就不會自動過濾路徑中的分隔字元。

　　2、MySQL中的布爾類型

　　MySQL中沒有Bool或Boolean類型，但是為了支援SQL標準，也可以定義Bool或Boolean類型的，但是Bool或Boolean類型最後轉換成的是TinyInt(1)，也就是說,在MySQL中，布爾類型實際上是TinyInt(1)。

　　3、MySQL中如何儲存JPG圖片或MP3音樂

　　一般情況下，資料庫中不直接儲存圖片和音頻檔案，而是儲存圖片或音頻檔案的路徑，如果在特殊情況下需要在MySQL資料庫中儲存圖片和音頻檔案，可以選擇BLOB類型；

MySQL資料類型