3.1　MySQL數據類型介紹

MySQL支持多種數據類型，主要有數值類型、日期/時間類型和字符串類型。

?　數值數據類型：包括整數類型TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、浮點小數數據類型FLOAT和DOUBLE，定點小數類型DECIMAL。

?　日期/時間類型：包括YEAR、TIME、DATE、DATETIME和TIMESTAMP。

?　字符串類型：包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等。字符串類型又分為文本字符串和二進制字符串。

3.1.1　整數類型

數值型數據類型主要用來存儲數字，MySQL提供了多種數值數據類型，不同的數據類型提供不同的取值范圍，可以存儲的值范圍越大，其所需要的存儲空間也會越大。MySQL主要提供的整數類型有：TINYINT、SMALLINT、MEDIUMINT、INT(INTEGER）、BIGINT。整數類型的屬性字段可以添加AUTO_INCREMENT自增約束條件。表3.1列出了MySQL中的數值類型。

表3.1　MySQL中的整數型數據類型

從表3.1中可以看到，不同類型的整數存儲所需的字節數是不同的，占用字節數最小的是TINYINT類型，占用字節數最大的是BIGINT類型，相應的占用字節數越多的類型，所能表示的數值范圍越大。根據占用字節數可以求出每一種數據類型的取值范圍，例如TINYINT需要1字節（8 bits）來存儲，那么TINYINT無符號數的最大值為2⁸-1，即255；TINYINT有符號數的最大值為2⁷-1，即127。其他類型的整數的取值范圍計算方法相同，如表3.2所示。

表3.2　不同整數類型的取值范圍

在2.3節中，有如下創建表的語句：

id字段的數據類型為INT(11)，注意后面的數字11，表示的是該數據類型指定的顯示寬度，指定能夠顯示的數值中數字的個數。例如，假設聲明一個INT類型的字段：

     year INT(4)

該聲明指明，在year字段中的數據一般只顯示4位數字的寬度。

在這里讀者要注意：顯示寬度和數據類型的取值范圍是無關的。顯示寬度只是指明MySQL最大可能顯示的數字個數，數值的位數小于指定的寬度時會由空格填充；如果插入了大于顯示寬度的值，那么只要該值不超過該類型整數的取值范圍，數值依然可以插入，而且能夠顯示出來。例如，假如向year字段插入一個數值19999，當使用SELECT查詢該列值的時候，MySQL顯示的將是完整的帶有5位數字的19999，而不是4位數字的值。

其他整型數據類型也可以在定義表結構時指定所需要的顯示寬度，如果不指定，系統就為每一種類型指定默認的寬度值，如例3.1所示。

【例3.1】創建表tmp1，其中字段x、y、z、m、n的數據類型依次為TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，SQL語句如下：

     CREATE TABLE tmp1 ( x TINYINT,  y SMALLINT,  z MEDIUMINT,  m INT,  n BIGINT );

執行成功之后，便用DESC查看表結構，結果如下：

可以看到，系統將添加不同的默認顯示寬度。這些顯示寬度能夠保證顯示的每一種數據類型可以取到取值范圍內的所有值。例如，TINYINT有符號數和無符號數的取值范圍分別為-128~127和0~255，由于負號占了一個數字位，因此TINYINT默認的顯示寬度為4。同理，其他整數類型的默認顯示寬度與其有符號數的最小值的寬度相同。

不同的整數類型有不同的取值范圍，并且需要不同的存儲空間，因此，應該根據實際需要選擇最合適的類型，這樣有利于提高查詢的效率和節省存儲空間。整數類型是不帶小數部分的數值，現實生活中很多地方需要用到帶小數的數值。下面將介紹MySQL中支持的小數類型。

3.1.2　浮點數類型和定點數類型

MySQL中使用浮點數和定點數來表示小數。浮點類型有兩種：單精度浮點類型（FLOAT）和雙精度浮點類型（DOUBLE）。定點類型只有一種：DECIMAL。浮點類型和定點類型都可以用（M，N）來表示，其中M稱為精度，表示總共的位數；N稱為標度，表示小數的位數。表3.3列出了MySQL中的小數類型和存儲需求。

表3.3　MySQL中的小數類型

DECIMAL類型不同于FLOAT和DOUBLE，DECIMAL實際是以串存放的，DECIMAL可能的最大取值范圍與DOUBLE一樣，但是其有效的取值范圍由M和D的值決定。若改變M而固定D，則其取值范圍將隨M的變大而變大。從表3.3可以看到，DECIMAL的存儲空間并不是固定的，而由其精度值M決定，占用M+2字節。

FLOAT類型的取值范圍如下。

?　有符號的取值范圍：-3.402823466E+38 ~ -1.175494351E-38。

?　無符號的取值范圍：0和1.175494351E-38 ~ 3.402823466E+38。

DOUBLE類型的取值范圍如下。

?　有符號的取值范圍：-1.7976931348623157E+308 ~ -2.2250738585072014E-308。

?　無符號的取值范圍：0和2.2250738585072014E-308 ~ 1.7976931348623157E+308。

【例3.2】創建表tmp2，其中字段x、y、z的數據類型依次為FLOAT(5,1)、DOUBLE(5,1)和DECIMAL(5,1)，向表中插入數據5.12、5.15和5.123，SQL語句如下：

     CREATE TABLE tmp2 ( x FLOAT(5,1),  y DOUBLE(5,1),  z DECIMAL(5,1) );

向表中插入數據：

可以看到在插入數據時，MySQL給出了一個警告信息，使用SHOW WARNINGS;語句查看警告信息：

可以看到FLOAT和DOUBLE在進行四舍五入時沒有給出警告，而給出z字段數值被截斷的警告。查看結果：

FLOAT和DOUBLE在不指定精度時，默認會按照實際的精度（由計算機硬件和操作系統決定），DECIMAL若不指定精度，則默認為(10,0)。

浮點數相對于定點數的優點是在長度一定的情況下，浮點數能夠表示更大的數據范圍；缺點是會引起精度問題。

3.1.3　日期與時間類型

MySQL中有多種表示日期的數據類型，主要有DATETIME、DATE、TIMESTAMP、TIME和YEAR。例如，當只記錄年信息的時候，可以只使用YEAR類型，而沒有必要使用DATE。每一個類型都有合法的取值范圍，當指定確實不合法的值時，系統將“零”值插入數據庫中。本節將介紹MySQL日期和時間類型的使用方法。表3.4列出了MySQL中的日期與時間類型。

表3.4　日期與時間數據類型

1. YEAR類型

YEAR類型是一個單字節類型，用于表示年，在存儲時只需要1字節。可以使用各種格式指定YEAR值，如下所示：

（1）以4位字符串或者4位數字格式表示的YEAR，范圍為‘1901’～‘2155’。輸入格式為‘YYYY’或者YYYY，例如，輸入‘2010’或2010，插入到數據庫的值均為2010。

（2）以2位字符串格式表示的YEAR，范圍為‘00’到‘99’?！?0’～‘69’和‘70’～‘99’范圍的值分別被轉換為2000～2069和1970～1999范圍的YEAR值。‘0’與‘00’的作用相同。插入超過取值范圍的值將被轉換為2000。

（3）以2位數字表示的YEAR，范圍為1～99。1～69和70～99范圍的值分別被轉換為2001～2069和1970～1999范圍的YEAR值。注意：在這里0值將被轉換為0000，而不是2000。

【例3.3】創建數據表tmp3，定義數據類型為YEAR的字段y，向表中插入值2010、‘2010’、‘2166’，SQL語句如下：

首先創建表tmp3：

     CREATE TABLE tmp3(y YEAR );

向表中插入數據：

     mysql> INSERT INTO tmp3 values(2010),('2010');

再次向表中插入數據：

語句執行之后，MySQL給出了一條錯誤提示，使用SHOW查看錯誤信息：

可以看到，插入的第3個值2166超過了YEAR類型的取值范圍，此時不能正常地執行插入操作，查看結果：

由結果可以看到，當插入值為數值類型的2010或者字符串類型的‘2010’時，都正確地存儲到了數據庫中；而當插入值‘2166’時，由于超出了YEAR類型的取值范圍，因此不能插入值。

【例3.4】向tmp3表中的y字段插入2位字符串表示的YEAR值，分別為‘0’、‘00’、‘77’和‘10’，SQL語句如下：

首先刪除表中的數據：

     DELETE FROM tmp3;

向表中插入數據：

     INSERT INTO tmp3 values('0'),('00'),('77'),('10');

查看結果：

由結果可以看到，字符串‘0’和‘00’的作用相同，分別都轉換成了2000年；‘77’轉換為1977；‘10’轉換為2010。

【例3.5】向tmp3表中的y字段插入2位數字表示的YEAR值，分別為0、78和11，SQL語句如下：

首先刪除表中的數據：

     DELETE FROM tmp3;

向表中插入數據：

     INSERT INTO tmp3 values(0),(78),(11);

查看結果：

由結果可以看到，0被轉換為0000；78被轉換為1978；11被轉換為2011。

2. TIME類型

TIME類型用于只需要時間信息的值，在存儲時需要3字節。TIME類型的格式為‘HH:MM:SS’，HH表示小時，MM表示分鐘，SS表示秒。TIME類型的取值范圍為-838:59:59～838:59:59，小時部分會如此大的原因是TIME類型不僅可以用于表示一天的時間（必須小于24小時），還可能是某個事件過去的時間或兩個事件之間的時間間隔（可以大于24小時，或者為負）?？梢允褂酶鞣N格式指定TIME值，如下所示：

（1）‘D HH:MM:SS’格式的字符串。還可以使用下面任何一種“非嚴格”的語法：‘HH:MM:SS’、‘HH:MM’、‘D HH:MM’、‘D HH’或‘SS’。這里的D表示日，可以取0~34之間的值。在插入數據庫時，D被轉換為小時保存，格式為“D*24 + HH”。

（2）‘HHMMSS’格式的、沒有間隔符的字符串或者HHMMSS格式的數值，假定是有意義的時間。例如，‘101112’被理解為‘10:11:12’，但‘109712’是不合法的（它有一個沒有意義的分鐘部分），存儲時將變為00:00:00。

【例3.6】創建數據表tmp4，定義數據類型為TIME的字段t，向表中插入值‘10:05:05’、‘23:23’、‘2 10:10’、‘3 02’、‘10’，SQL語句如下：

首先創建表tmp4：

     CREATE TABLE tmp4( t TIME );

向表中插入數據：

查看結果：

由結果可以看到，‘10:05:05’被轉換為10:05:05；‘23:23’被轉換為23:23:00；‘2 10:10’被轉換為58:10:00，‘3 02’被轉換為74:00:00；‘10’被轉換成00:00:10。

【例3.7】向表tmp4中插入值‘101112’、111213、‘0’、107010，SQL語句如下：

首先刪除表中的數據：

     DELETE FROM tmp4;

向表中插入數據：

     mysql>INSERT INTO tmp4 values('101112'),(111213),( '0');

再向表中插入數據：

可以看到，在插入數據時，MySQL給出了一個錯誤提示信息，使用SHOW WARNINGS;查看錯誤信息，如下所示：

可以看到，第二次在插入記錄的時候，數據超出了范圍，原因是107010的分鐘部分超過了60，分鐘部分是不會超過60的，查看結果：

由結果可以看到，‘101112’被轉換為10:11:12；111213被轉換為11:12:13；‘0’被轉換為00:00:00；由于107010是不合法的值，因此不能被插入。

也可以使用系統日期函數向TIME字段列插入值。

【例3.8】向tmp4表中插入系統當前時間，SQL語句如下：

首先刪除表中的數據：

     DELETE FROM tmp4;

向表中插入數據：

     mysql> INSERT INTO tmp4 values (CURRENT_TIME) ,(NOW());

查看結果：

由結果可以看到，獲取系統當前的日期時間插入TIME類型的列t，由于讀者輸入語句的時間不確定，因此獲取的值與這里的可能是不同的，但都是系統當前的日期時間值。

3. DATE類型

DATE類型用于僅需要日期值時，沒有時間部分，在存儲時需要3字節。日期格式為‘YYYY-MM-DD’，其中YYYY表示年，MM表示月，DD表示日。在給DATE類型的字段賦值時，可以使用字符串類型或者數字類型的數據插入，只要符合DATE的日期格式即可，具體如下：

（1）以‘YYYY-MM-DD’或者‘YYYYMMDD’字符串格式表示的日期，取值范圍為‘1000-01-01’～‘9999-12-3’。例如，輸入‘2012-12-31’或者‘20121231’，插入數據庫的日期都為2012-12-31。

（2）以‘YY-MM-DD’或者‘YYMMDD’字符串格式表示的日期，在這里YY表示兩位的年值。包含兩位年值的日期會令人模糊，因為不知道世紀。MySQL使用以下規則解釋兩位年值：‘00～69’范圍的年值轉換為‘2000～2069’；‘70～99’范圍的年值轉換為‘1970～1999’。例如，輸入‘12-12-31’，插入數據庫的日期為2012-12-31；輸入‘981231’，插入數據的日期為1998-12-31。

（3）以YY-MM-DD或者YYMMDD數字格式表示的日期，與前面相似，00～69范圍的年值轉換為2000～2069，70～99范圍的年值轉換為1970～1999。例如，輸入12-12-31，插入數據庫的日期為2012-12-31；輸入981231，插入數據的日期為1998-12-31。

（4）使用CURRENT_DATE或者NOW()，插入當前系統日期。

【例3.9】創建數據表tmp5，定義數據類型為DATE的字段d，向表中插入“YYYY-MM-DD”和“YYYYMMDD”字符串格式的日期，SQL語句如下：

首先創建表tmp5：

向表中插入“YYYY-MM-DD”和“YYYYMMDD”格式的日期：

     MySQL> INSERT INTO tmp5 values('1998-08-08'),('19980808'),('20101010');

查看插入結果：

可以看到，各個不同類型的日期值都正確地插入了數據表中。

【例3.10】向tmp5表中插入“YY-MM-DD”和“YYMMDD”字符串格式的日期，SQL語句如下：

首先刪除表中的數據：

     DELETE FROM tmp5;

向表中插入“YY-MM-DD”和“YYMMDD”格式的日期：

查看插入結果：

【例3.11】向tmp5表中插入YYYYMMDD和YYMMDD數字格式日期，SQL語句如下：

首先刪除表中的數據：

     DELETE FROM tmp5;

向表中插入YYYYMMDD和YYMMDD數字格式日期：

     mysql> INSERT INTO tmp5 values (19990909),(990909), ( 000101) ,( 111111);

查看插入結果：

【例3.12】向tmp5表中插入系統當前日期，SQL語句如下：

首先刪除表中的數據：

     DELETE FROM tmp5;

向表中插入系統當前日期：

     mysql> INSERT INTO tmp5 values( CURRENT_DATE() ),( NOW() );

查看插入結果：

CURRENT_DATE只返回當前日期值，不包括時間部分；NOW()函數返回日期和時間值，在保存到數據庫中時，只保留了其日期部分。

4. DATETIME

DATETIME類型用于需要同時包含日期和時間信息的值，在存儲時需要8字節。日期格式為'YYYY-MM-DD HH:MM:SS'，其中YYYY表示年，MM表示月，DD表示日，HH表示小時，MM表示分鐘，SS表示秒。在給DATETIME類型的字段賦值時，可以使用字符串類型或者數字類型的數據插入，只要符合DATETIME的日期格式即可，如下所示：

（1）以'YYYY-MM-DD HH:MM:SS'或者'YYYYMMDDHHMMSS'字符串格式表示的值，取值范圍為'1000-01-01 00:00:00'～'9999-12-3 23:59:59'。例如輸入'2012-12-31 05: 05: 05'或者'20121231050505'，插入數據庫的DATETIME值都為2012-12-31 05: 05: 05。

（2）以'YY-MM-DD HH:MM:SS'或者'YYMMDDHHMMSS'字符串格式表示的日期，在這里YY表示兩位的年值。與前面相同，'00～69'范圍的年值轉換為'2000～2069'；'70～99'范圍的年值轉換為'1970～1999'。例如輸入'12-12-31 05: 05: 05'，插入數據庫的DATETIME為2012-12-31 05: 05: 05；輸入'980505050505'，插入數據庫的DATETIME為1998-05-05 05: 05: 05。

（3）以YYYYMMDDHHMMSS或者YYMMDDHHMMSS數字格式表示的日期和時間，例如輸入20121231050505，插入數據庫的DATETIME為2012-12-31 05:05:05；輸入981231050505，插入數據的DATETIME為1998-12-31 05: 05: 05。

【例3.13】創建數據表tmp6，定義數據類型為DATETIME的字段dt，向表中插入“YYYY-MM-DD HH:MM:SS”和“YYYYMMDDHHMMSS”字符串格式日期和時間值，SQL語句如下：

首先創建表tmp6：

     CREATE TABLE tmp6(  dt DATETIME );

向表中插入“YYYY-MM-DD HH:MM:SS”和“YYYYMMDDHHMMSS”格式的日期：

查看插入結果：

可以看到，各個不同類型的日期值都正確地插入了數據表中。

【例3.14】向tmp6表中插入“YY-MM-DD HH:MM:SS”和“YYMMDDHHMMSS”字符串格式的日期和時間值，SQL語句如下：

首先刪除表中的數據：

     DELETE FROM tmp6;

向表中插入“YY-MM-DD HH:MM:SS”和“YYMMDDHHMMSS”格式的日期：

查看插入結果：

【例3.15】向tmp6表中插入YYYYMMDDHHMMSS和YYMMDDHHMMSS數字格式的日期和時間值，SQL語句如下：

首先刪除表中的數據：

     DELETE FROM tmp6;

向表中插入YYYYMMDDHHMMSS和YYMMDDHHMMSS數字格式的日期和時間：

     mysql> INSERT INTO tmp6 values(19990909090909), (101010101010);

查看插入結果：

【例3.16】向tmp6表中插入系統當前日期和時間值，SQL語句如下：

首先刪除表中的數據：

     DELETE FROM tmp6;

向表中插入系統當前日期：

     mysql> INSERT INTO tmp6 values( NOW() );

查看插入結果：

NOW()函數返回當前系統的日期和時間值，格式為“YYYY-MM-DD HH:MM:SS”。

5. TIMESTAMP

TIMESTAMP的顯示格式與DATETIME相同，顯示寬度固定在19個字符，日期格式為YYYY-MM-DD HH:MM:SS，在存儲時需要4字節。但是TIMESTAMP列的取值范圍小于DATETIME的取值范圍，為‘1970-01-01 00:00:01’UTC～‘2038-01-19 03:14:07’UTC，其中，UTC（Coordinated Universal Time）為世界標準時間，因此在插入數據時要保證在合法的取值范圍內。

【例3.17】創建數據表tmp7，定義數據類型為TIMESTAMP的字段ts，向表中插入值‘19950101010101’、‘950505050505’、‘1996-02-02 02:02:02’、‘97@03@03 03@03@03’、121212121212、NOW()，SQL語句如下：

     CREATE TABLE tmp7( ts TIMESTAMP);

向表中插入數據：

查看插入結果：

由結果可以看到，‘19950101010101’被轉換為1995-01-01 01:01:01，‘950505050505’被轉換為1995-05-05 05:05:05，‘1996-02-02 02:02:02’被轉換為1996-02-02 02:02:02，‘97@03@03 03@03@03’被轉換為1997-03-03 03:03:03，121212121212被轉換為2012-12-12 12:12:12，NOW()被轉換為系統當前日期時間2018-11-09 17:08:25。

【例3.18】向tmp7表中插入當前日期，查看插入值，更改時區為東10區，再次查看插入值，SQL語句如下：

首先刪除表中的數據：

     DELETE FROM tmp7;

向表中插入系統當前日期：

     mysql> INSERT INTO tmp7 values( NOW() );

查看當前時區下的日期值：

查詢結果為插入時的日期值，讀者所在時區一般為東8區，下面修改當前時區為東10區，SQL語句如下：

     mysql> set time_zone='+10:00';

再次查看插入時的日期值：

由結果可以看到，因為東10區時間比東8區快兩個小時，因此查詢的結果經過時區轉換之后，顯示的值增加了兩個小時。相同的，時區每減小1，查詢顯示的日期中的小時數減1。

3.1.4　文本字符串類型

字符串類型除了可以存儲字符串數據之外，還可以存儲其他數據，比如圖片和聲音的二進制數據。MySQL支持兩類字符型數據：文本字符串和二進制字符串。本小節主要講解文本字符串類型，文本字符串可以進行區分或者不區分大小寫的串比較，另外，還可以進行模式匹配查找。MySQL中文本字符串類型指CHAR、VARCHAR、TEXT、ENUM和SET。表3.5列出了MySQL中的文本字符串數據類型。

表3.5　MySQL中的文本字符串數據類型

VARCHAR和TEXT類型與下一小節將要講到的BLOB一樣是變長類型，其存儲需求取決于列值的實際長度（在表3.5中用L表示），而不是取決于類型的最大可能尺寸。例如，一個VARCHAR(10)列能保存一個最大長度為10個字符的字符串，實際的存儲需要是字符串的長度L，加上1字節以記錄字符串的長度。對于字符“abcd”，L是4，而存儲要求是5字節。本節將介紹這些數據類型的作用以及如何在查詢中使用這些類型。

1. CHAR和VARCHAR類型

CHAR(M)為固定長度字符串，在定義時指定字符串列長。當保存時在右側填充空格以達到指定的長度。M表示列長度，M的范圍是0~255字符。例如，CHAR(4)定義了一個固定長度的字符串列，其包含的字符個數最大為4。當檢索到CHAR值時，尾部的空格將被刪除。

VARCHAR(M)是長度可變的字符串，M表示最大列長度。M的范圍是0~65 535。VARCHAR的最大實際長度由最長的行的大小和使用的字符集確定，而其實際占用的空間為字符串的實際長度加1。例如，VARCHAR(50)定義了一個最大長度為50的字符串，如果插入的字符串只有10個字符，那么實際存儲的字符串為10個字符和一個字符串結束字符。VARCHAR在值保存和檢索時尾部的空格仍保留。

【例3.19】將不同字符串保存到CHAR(4)和VARCHAR(4)列，說明CHAR和VARCHAR之間的差別，如表3.6所示。

表3.6　CHAR(4)與VARCHAR(4)存儲的區別

對比結果可以看到，CHAR(4)定義了固定長度為4的列，無論存入的數據長度為多少，所占用的空間均為4字節。VARCHAR(4)定義的列所占的字節數為實際長度加1。

當查詢時，CHAR(4)和VARCHAR(4)的值并不一定相同，如【例3.20】所示。

【例3.20】創建tmp8表，定義字段ch和vch的數據類型依次為CHAR(4)、VARCHAR(4)，向表中插入數據“ab ”，SQL語句如下：

創建表tmp8：

輸入數據：

     INSERT INTO tmp8 VALUES('ab  ', 'ab  ');

查詢結果：

從查詢結果可以看到，ch在保存“ab ”時將末尾的兩個空格刪除了，而vch字段保留了末尾的兩個空格。

在表3.6中，最后一行的值只有在使用“不嚴格”模式時，字符串才會被截斷插入；如果MySQL運行在“嚴格”模式，超過列長度的值就不會被保存，并且會出現錯誤信息：“ERROR 1406(22001): Data too long for column”，即字符串長度超過指定長度，無法插入。

2. TEXT類型

TEXT列保存非二進制字符串，如文章內容、評論等。當保存或查詢TEXT列的值時，不刪除尾部空格。TEXT類型分為4種：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。不同的TEXT類型的存儲空間和數據長度不同。

（1）TINYTEXT　最大長度為255(28–1)字符的TEXT列。

（2）TEXT　最大長度為65 535(216–1)字符的TEXT列。

（3）MEDIUMTEXT　最大長度為16 777 215(224–1)字符的TEXT列。

（4）LONGTEXT　最大長度為4 294 967 295或4GB(232–1)字符的TEXT列。

3. ENUM類型

ENUM是一個字符串對象，其值為表創建時在列規定中枚舉的一列值。語法格式如下：

     字段名 ENUM('值1','值2',... '值n')

字段名指將要定義的字段，值n指枚舉列表中的第n個值。ENUM類型的字段在取值時，只能在指定的枚舉列表中取，而且一次只能取一個。如果創建的成員中有空格，其尾部的空格就會自動被刪除。ENUM值在內部用整數表示，每個枚舉值均有一個索引值：列表值所允許的成員值從1開始編號，MySQL存儲的就是這個索引編號。枚舉最多可以有65 535個元素。

例如定義ENUM類型的列('first'，'second'，'third')，該列可以取的值和每個值的索引如表3.7所示。

表3.7　ENUM類型的取值范圍

ENUM值依照列索引順序排列，并且空字符串排在非空字符串前，NULL值排在其他所有的枚舉值前。這一點也可以從表3.7中看到。

在這里，有一個方法可以查看列成員的索引值，如【例3.21】所示。

【例3.21】創建表tmp9，定義ENUM類型的列enm('first'，'second'，'third')，查看列成員的索引值，SQL語句如下：

首先，創建tmp9表：

     CREATE TABLE tmp9( enm ENUM('first','second','third') );

插入各個列值：

     INSERT INTO tmp9 values('first'),('second') ,('third') , (NULL);

查看索引值：

可以看到，這里的索引值和前面所述的相同。

【例3.22】創建表tmp10，定義INT類型的字段soc、ENUM類型的字段level，列表值為('excellent','good', 'bad')，向表tmp10中插入數據'good'、1、2、3、'best'，SQL語句如下：

首先，創建數據表：

     CREATE TABLE tmp10 (soc INT, level enum('excellent', 'good','bad') );

插入數據：

     INSERT INTO tmp10 values(70,'good'), (90,1),(75,2),(50,3);

再次插入數據：

這里系統提示錯誤信息，可以看到，由于字符串值“best”不在ENUM列表中，因此對數據進行了阻止插入操作，查詢結果如下：

由結果可以看到，因為ENUM列表中的值在MySQL中都是以編號序列存儲的，所以插入列表中的值“good”和插入其對應的序號2的結果是相同的；“best”不是列表中的值，因此不能插入數據。

4. SET類型

SET是一個字符串對象，可以有零個或多個值，SET列最多可以有64個成員，其值為表創建時規定的一列值。指定包括多個SET成員的SET列值時，各成員之間用逗號（,）間隔開。語法格式如下：

     SET('值1','值2',... '值n')

與ENUM類型相同，SET值在內部用整數表示，列表中每一個值都有一個索引編號。當創建表時，SET成員值的尾部空格將自動被刪除。但與ENUM類型不同的是，ENUM類型的字段只能從定義的列值中選擇一個值插入，而SET類型的列可從定義的列值中選擇多個字符的聯合。

如果插入SET字段中的列值有重復，MySQL就自動刪除重復的值。插入SET字段的值的順序并不重要，MySQL會在存入數據庫時按照定義的順序顯示。如果插入了不正確的值，默認情況下，MySQL將忽視這些值，并給出警告。

【例3.23】創建表tmp11，定義SET類型的字段s，取值列表為('a', 'b', 'c', 'd')，插入數據('a')、('a,b,a')、('c,a,d')、('a,x,b,y')、SQL語句如下：

首先創建表tmp11：

     CREATE TABLE tmp11 ( s SET('a', 'b', 'c', 'd'));

插入數據：

     INSERT INTO tmp11 values('a'),( 'a,b,a'),('c,a,d');

再次插入數據：

由于插入了SET列不支持的值，因此MySQL給出錯誤提示。

查看結果：

從結果可以看到，對于SET來說，如果插入的值為重復的，就只取一個，例如“a,b,a”，則結果為“a,b”；如果插入了不按順序排列的值，就自動按順序插入，例如“c,a,d”，結果為“a,c,d”；如果插入了不正確的值，該值將被阻止插入，例如插入值“a,x,b,y”。

3.1.5　二進制字符串類型

前面講解了存儲文本的字符串類型，這一小節將講解MySQL中存儲二進制數據的字符串類型。MySQL中的二進制數據類型有BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB，本節將講解各類二進制字符串類型的特點和使用方法。表3.8列出了MySQL中的二進制字符串類型。

表3.8　MySQL中的二進制字符串類型

1. BIT類型

BIT類型是位字段類型。M表示每個值的位數，范圍為1~64。如果M被省略，就默認為1。如果為BIT(M)列分配的值的長度小于M位，就在值的左邊用0填充。例如，為BIT(6)列分配一個值b'101'，其效果與分配b'000101'相同。BIT數據類型用來保存位字段值，例如以二進制的形式保存數據13，13的二進制形式為1101，在這里需要位數至少為4位的BIT類型，即可以定義列類型為BIT(4)。大于二進制1111的數據是不能插入BIT(4)類型的字段中的。

【例3.24】創建表tmp12，定義BIT(4)類型的字段b，向表中插入數據2、9、15。

首先創建表tmp12，SQL語句如下：

     CREATE TABLE tmp12( b BIT(4) );

插入數據：

     mysql> INSERT INTO tmp12 VALUES(2), (9), (15);

查詢插入結果：

b+0表示將二進制的結果轉換為對應的數字的值，BIN()函數將數字轉換為二進制。從結果可以看到，成功地將3個數插入表中。

2. BINARY和VARBINARY類型

BINARY和VARBINARY類型類似于CHAR和VARCHAR，不同的是它們包含二進制字節字符串。其使用的語法格式如下：

     列名稱 BINARY(M)或者VARBINARY(M)

BINARY類型的長度是固定的，指定長度之后，不足最大長度的，將在它們的右邊填充‘\0’補齊，以達到指定長度。例如，指定列數據類型為BINARY(3)，當插入“a”時，存儲的內容實際為“a\0\0”，當插入“ab”時，實際存儲的內容為“ab\0”，無論存儲的內容是否達到指定的長度，其存儲空間均為指定的值M。

VARBINARY類型的長度是可變的，指定好長度之后，其長度可以在0到最大值之間。例如，指定列數據類型為VARBINARY(20)，如果插入的值的長度只有10，實際存儲空間就為10加1，即其實際占用的空間為字符串的實際長度加1。

【例3.25】創建表tmp13，定義BINARY(3)類型的字段b和VARBINARY(3)類型的字段vb，并向表中插入數據‘5’，比較兩個字段的存儲空間。

首先創建表tmp13，輸入SQL語句如下：

插入數據：

     INSERT INTO tmp13 VALUES(5,5);

查看兩個字段存儲數據的長度：

可以看到，b字段的值數據長度為3，而vb字段的數據長度僅為插入的一個字符的長度1。

如果想要進一步確認‘5’在兩個字段中不同的存儲方式，輸入如下語句：

由執行結果可以看出，b字段和vb字段的長度是截然不同的，因為b字段不足的空間填充了‘\0’，而vb字段則沒有填充。

3. BLOB類型

BLOB是一個二進制大對象，用來存儲可變數量的數據。BLOB類型分為4種：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB，它們可容納值的最大長度不同，如表3.9所示。

表3.9　BLOB類型的存儲范圍

BLOB列存儲的是二進制字符串（字節字符串），TEXT列存儲的是非二進制字符串（字符字符串）。BLOB列沒有字符集，并且排序和比較基于列值字節的數值；TEXT列有一個字符集，并且根據字符集對值進行排序和比較。