5.1　MySQL數(shù)據(jù)類(lèi)型介紹

MySQL支持多種數(shù)據(jù)類(lèi)型，主要有數(shù)值類(lèi)型、日期/時(shí)間類(lèi)型和字符串類(lèi)型。

（1）數(shù)值類(lèi)型：包括整數(shù)類(lèi)型TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、浮點(diǎn)小數(shù)數(shù)據(jù)類(lèi)型FLOAT和DOUBLE，定點(diǎn)小數(shù)類(lèi)型DECIMAL。

（2）日期/時(shí)間類(lèi)型：包括YEAR、TIME、DATE、DATETIME和TIMESTAMP。

（3）字符串類(lèi)型：包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等。字符串類(lèi)型又分為文本字符串和二進(jìn)制字符串。

5.1.1　整數(shù)類(lèi)型

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

表5.1　MySQL中的整數(shù)型數(shù)據(jù)類(lèi)型

從表5.1中可以看到，不同類(lèi)型整數(shù)存儲(chǔ)所需的字節(jié)數(shù)是不同的，占用字節(jié)數(shù)最小的是TINYINT類(lèi)型，占用字節(jié)最大的是BIGINT類(lèi)型，相應(yīng)的占用字節(jié)越多的類(lèi)型所能表示的數(shù)值范圍越大。根據(jù)占用字節(jié)數(shù)可以求出每一種數(shù)據(jù)類(lèi)型的取值范圍。例如，TINYINT需要1字節(jié)（8 bits）來(lái)存儲(chǔ)，那么TINYINT無(wú)符號(hào)數(shù)的最大值為2⁸-1（255）、TINYINT有符號(hào)數(shù)的最大值為2⁷-1（127）。其他類(lèi)型的整數(shù)的取值范圍計(jì)算方法相同，如表5.2所示。

表5.2　不同整數(shù)類(lèi)型的取值范圍

在第4章中，有如下創(chuàng)建表的語(yǔ)句：

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

　　year INT(4)

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

在這里讀者要注意：顯示寬度和數(shù)據(jù)類(lèi)型的取值范圍是無(wú)關(guān)的。顯示寬度只是指明MySQL最大可能顯示的數(shù)字個(gè)數(shù)，數(shù)值的位數(shù)小于指定的寬度時(shí)會(huì)由空格填充；如果插入了大于顯示寬度的值，只要該值不超過(guò)該類(lèi)型整數(shù)的取值范圍，數(shù)值依然可以插入，而且能夠顯示出來(lái)。例如，向year字段插入一個(gè)數(shù)值19999，當(dāng)使用SELECT查詢(xún)?cè)摿兄档臅r(shí)候，MySQL顯示的將是完整的帶有5位數(shù)字的19999，而不是4位數(shù)字的值。

其他整型數(shù)據(jù)類(lèi)型也可以在定義表結(jié)構(gòu)時(shí)指定所需要的顯示寬度，如果不指定，則系統(tǒng)為每一種類(lèi)型指定默認(rèn)的寬度值，如例5.1所示。

【例5.1】創(chuàng)建表tmp1，其中字段x、y、z、m、n數(shù)據(jù)類(lèi)型依次為T(mén)INYINT、SMALLINT、MEDIUMINT、INT、BIGINT，SQL語(yǔ)句如下：

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

執(zhí)行成功之后，使用DESC查看表結(jié)構(gòu)，結(jié)果如下：

可以看到，系統(tǒng)將添加不同的默認(rèn)顯示寬度。這些顯示寬度能夠保證顯示每一種數(shù)據(jù)類(lèi)型可以取到取值范圍內(nèi)的所有值。例如，TINYINT有符號(hào)數(shù)和無(wú)符號(hào)數(shù)的取值范圍分別為-128~127和0~255，由于負(fù)號(hào)占了一個(gè)數(shù)字位，因此TINYINT默認(rèn)的顯示寬度為4。同理，其他整數(shù)類(lèi)型的默認(rèn)顯示寬度與其有符號(hào)數(shù)的最小值的寬度相同。

不同的整數(shù)類(lèi)型有不同的取值范圍，并且需要不同的存儲(chǔ)空間，因此應(yīng)該根據(jù)實(shí)際需要選擇最合適的類(lèi)型，這樣有利于提高查詢(xún)的效率和節(jié)省存儲(chǔ)空間。整數(shù)類(lèi)型是不帶小數(shù)部分的數(shù)值，現(xiàn)實(shí)生活中很多地方需要用到帶小數(shù)的數(shù)值，下面將介紹MySQL中支持的小數(shù)類(lèi)型。

5.1.2　浮點(diǎn)數(shù)類(lèi)型和定點(diǎn)數(shù)類(lèi)型

MySQL中使用浮點(diǎn)數(shù)和定點(diǎn)數(shù)來(lái)表示小數(shù)。浮點(diǎn)數(shù)類(lèi)型有兩種：?jiǎn)尉雀↑c(diǎn)類(lèi)型（FLOAT）和雙精度浮點(diǎn)類(lèi)型（DOUBLE）。定點(diǎn)數(shù)類(lèi)型只有一種：DECIMAL。浮點(diǎn)數(shù)類(lèi)型和定點(diǎn)數(shù)類(lèi)型都可以用（M，N）來(lái)表示。其中，M稱(chēng)為精度，表示總共的位數(shù)；N稱(chēng)為標(biāo)度，表示小數(shù)的位數(shù)。表5.3列出了MySQL中的小數(shù)類(lèi)型和存儲(chǔ)需求。

表5.3　MySQL中的小數(shù)類(lèi)型

DECIMAL類(lèi)型不同于FLOAT和DOUBLE，DECIMAL實(shí)際是以串存放的，可能的最大取值范圍與DOUBLE一樣，但是其有效的取值范圍由M和D的值決定。如果改變M而固定D，則其取值范圍將隨M的變大而變大。從表5.3可以看到，DECIMAL的存儲(chǔ)空間并不是固定的，而由其精度值M決定的，占用M+2字節(jié)。

FLOAT類(lèi)型的取值范圍如下：

●　有符號(hào)的取值范圍：-3.402823466E+38 ~ -1.175494351E-38。

●　無(wú)符號(hào)的取值范圍：0和1.175494351E-38 ~ 3.402823466E+38。

DOUBLE類(lèi)型的取值范圍如下：

●　有符號(hào)的取值范圍：-1.7976931348623157E+308 ~ -2.2250738585072014E-308。

●　無(wú)符號(hào)的取值范圍：0和2.2250738585072014E-308 ~ 1.7976931348623157E+308。

【例5.2】創(chuàng)建表tmp2，其中字段x、y、z的數(shù)據(jù)類(lèi)型依次為FLOAT(5,1)、DOUBLE(5,1)和DECIMAL(5,1)，向表中插入數(shù)據(jù)5.12、5.15和5.123，SQL語(yǔ)句如下：

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

向表中插入數(shù)據(jù)：

　　mysql>INSERT INTO tmp2 VALUES(5.12, 5.15, 5.123);
　　Query OK, 1 row affected, 1 warning (0.00 sec)

可以看到在插入數(shù)據(jù)時(shí)，MySQL給出了一個(gè)警告信息，使用SHOW WARNINGS;語(yǔ)句查看警告信息：

可以看到，F(xiàn)LOAT和DOUBLE在進(jìn)行四舍五入時(shí)沒(méi)有給出警告，只給出z字段數(shù)值被截?cái)嗟木妗２榭唇Y(jié)果：

FLOAT和DOUBLE在不指定精度時(shí)，默認(rèn)會(huì)按照實(shí)際的精度（由計(jì)算機(jī)硬件和操作系統(tǒng)決定），DECIMAL若不指定精度則默認(rèn)為(10,0)。

浮點(diǎn)數(shù)相對(duì)于定點(diǎn)數(shù)的優(yōu)點(diǎn)是在長(zhǎng)度一定的情況下，浮點(diǎn)數(shù)能夠表示更大的數(shù)據(jù)范圍；它的缺點(diǎn)是會(huì)引起精度問(wèn)題。

5.1.3　日期與時(shí)間類(lèi)型

MySQL中有多種表示日期的數(shù)據(jù)類(lèi)型，主要有DATETIME、DATE、TIMESTAMP、TIME和YEAR。例如，當(dāng)只記錄年信息的時(shí)候，可以只使用YEAR類(lèi)型，而沒(méi)有必要使用DATE。每一個(gè)類(lèi)型都有合法的取值范圍，當(dāng)指定確實(shí)不合法的值時(shí)系統(tǒng)將“零”值插入到數(shù)據(jù)庫(kù)中。本節(jié)將介紹MySQL日期和時(shí)間類(lèi)型的使用方法。表5.4列出了MySQL中的日期與時(shí)間類(lèi)型。

表5.4　日期與時(shí)間數(shù)據(jù)類(lèi)型

1．YEAR

YEAR類(lèi)型是一個(gè)單字節(jié)類(lèi)型，用于表示年，在存儲(chǔ)時(shí)只需要1字節(jié)?？梢允褂酶鞣N格式指定YEAR值，如下所示：

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

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

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

【例5.3】創(chuàng)建數(shù)據(jù)表tmp3，定義數(shù)據(jù)類(lèi)型為YEAR的字段y，向表中插入值2010、‘2010’、‘2166’，SQL語(yǔ)句如下：

首先創(chuàng)建表tmp3：

　　CREATE TABLE tmp3(y YEAR );

向表中插入數(shù)據(jù)：

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

再次向表中插入數(shù)據(jù)：

　　mysql> INSERT INTO tmp3 values ('2166');
　　ERROR 1264 (22003): Out of range value for column 'y' at row 1

語(yǔ)句執(zhí)行之后，MySQL給出了一條錯(cuò)誤提示，使用SHOW查看錯(cuò)誤信息：

可以看到，插入的第3個(gè)值2166超過(guò)了YEAR類(lèi)型的取值范圍，此時(shí)不能正常執(zhí)行插入操作，查看結(jié)果：

由結(jié)果可以看到，當(dāng)插入值為數(shù)值類(lèi)型的2010或者字符串類(lèi)型的‘2010’時(shí)，都正確地儲(chǔ)存到了數(shù)據(jù)庫(kù)中；而當(dāng)插入值‘2166’時(shí)，由于超出了YEAR類(lèi)型的取值范圍，因此，不能插入值。

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

①刪除表中的數(shù)據(jù)：

　　DELETE FROM tmp3;

②向表中插入數(shù)據(jù)：

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

③查看結(jié)果：

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

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

①刪除表中的數(shù)據(jù)：

　　DELETE FROM tmp3;

②向表中插入數(shù)據(jù)：

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

③查看結(jié)果：

由結(jié)果可以看到，0被轉(zhuǎn)換為0000，78被轉(zhuǎn)換為1978，11被轉(zhuǎn)換為2011。

2．TIME

TIME類(lèi)型用在只需要時(shí)間信息的值，在存儲(chǔ)時(shí)需要3字節(jié)，格式為‘HH:MM:SS’。其中，HH表示小時(shí)，MM表示分鐘，SS表示秒。TIME類(lèi)型的取值范圍為-838:59:59 ～838:59:59，小時(shí)部分會(huì)如此大的原因是TIME類(lèi)型不僅可以用于表示一天的時(shí)間（必須小于24小時(shí)），還可能是某個(gè)事件過(guò)去的時(shí)間或兩個(gè)事件之間的時(shí)間間隔（可以大于24小時(shí)，或者甚至為負(fù)）?？梢允褂酶鞣N格式指定TIME值。

（1）‘D HH:MM:SS’格式的字符串?？梢允褂孟旅嫒魏我环N“非嚴(yán)格”的語(yǔ)法：‘HH:MM:SS’、‘HH:MM’、‘D HH:MM’、‘D HH’或‘SS’。這里的D表示日，可以取0~34之間的值。在插入數(shù)據(jù)庫(kù)時(shí)，D被轉(zhuǎn)換為小時(shí)保存，格式為“D*24+HH”。

（2）‘HHMMSS’格式的、沒(méi)有間隔符的字符串或者HHMMSS格式的數(shù)值，假定是有意義的時(shí)間。例如：‘101112’被理解為‘10:11:12’，但‘109712’是不合法的（它有一個(gè)沒(méi)有意義的分鐘部分），存儲(chǔ)時(shí)將變?yōu)?0:00:00。

【例5.6】創(chuàng)建數(shù)據(jù)表tmp4，定義數(shù)據(jù)類(lèi)型為T(mén)IME的字段t，向表中插入值‘10:05:05’，‘23:23’，‘2 10:10’，‘3 02’，‘10’，SQL語(yǔ)句如下：

①創(chuàng)建表tmp4：

　　CREATE TABLE tmp4( t TIME );

②向表中插入數(shù)據(jù)：

③查看結(jié)果：

由結(jié)果可以看到，‘10:05:05’被轉(zhuǎn)換為10:05:05；‘23:23’被轉(zhuǎn)換為23:23:00；‘2 10:10’被轉(zhuǎn)換為58:10:00，‘3 02’被轉(zhuǎn)換為74:00:00；‘10’被轉(zhuǎn)換成00:00:10。

【例5.7】向表tmp4中插入值‘101112’、111213,‘0’、107010，SQL語(yǔ)句如下：

①刪除表中的數(shù)據(jù)：

　　DELETE FROM tmp4;

②向表中插入數(shù)據(jù)：

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

③再向表中插入數(shù)據(jù)：

　　mysql>INSERT INTO tmp4 values ( 107010);
　　ERROR 1292 (22007): Incorrect time value: '107010' for column 't' at row 1

可以看到，在插入數(shù)據(jù)時(shí)，MySQL給出了一個(gè)錯(cuò)誤提示信息，使用“SHOW WARNINGS;”查看錯(cuò)誤信息，如下所示：

可以看到，第二次在插入記錄的時(shí)候，數(shù)據(jù)超出了范圍，原因是107010的分鐘部分超過(guò)了60（分鐘部分是不會(huì)超過(guò)60的）。查看結(jié)果：

由結(jié)果可以看到，‘101112’被轉(zhuǎn)換為10:11:12；111213被轉(zhuǎn)換為11:12:13；‘0’被轉(zhuǎn)換為00:00:00；107010因?yàn)槭遣缓戏ǖ闹担虼瞬荒鼙徊迦搿?/p>

也可以使用系統(tǒng)日期函數(shù)向TIME字段列插入值。

【例5.8】向tmp4表中插入系統(tǒng)當(dāng)前時(shí)間，SQL語(yǔ)句如下：

①刪除表中的數(shù)據(jù)：

　　DELETE FROM tmp4;

②向表中插入數(shù)據(jù)：

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

③查看結(jié)果：

由結(jié)果可以看到，獲取系統(tǒng)當(dāng)前的日期時(shí)間插入到TIME類(lèi)型的列t，因?yàn)樽x者輸入語(yǔ)句的時(shí)間不確定，因此獲取的值可能與這里的不同，但都是系統(tǒng)當(dāng)前的日期時(shí)間值。

3．DATE類(lèi)型

DATE類(lèi)型用在僅需要日期值時(shí)，沒(méi)有時(shí)間部分，在存儲(chǔ)時(shí)需要3字節(jié)。日期格式為‘YYYY-MM-DD’。其中，YYYY表示年，MM表示月，DD表示日。在給DATE類(lèi)型的字段賦值時(shí)，可以使用字符串類(lèi)型或者數(shù)字類(lèi)型的數(shù)據(jù)插入，只要符合DATE的日期格式即可。

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

（2）以‘YY-MM-DD’或者‘YYMMDD’字符串格式表示的日期，在這里YY表示兩位的年值。包含兩位年值的日期會(huì)令人模糊，因?yàn)椴恢朗兰o(jì)。MySQL使用以下規(guī)則解釋兩位年值：‘00～69’范圍的年值轉(zhuǎn)換為‘2000～2069’；‘70～99’范圍的年值轉(zhuǎn)換為‘1970～1999’。例如，輸入‘12-12-31’，插入數(shù)據(jù)庫(kù)的日期為2012-12-31；輸入‘981231’，插入數(shù)據(jù)的日期為1998-12-31。

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

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

【例5.9】創(chuàng)建數(shù)據(jù)表tmp5，定義數(shù)據(jù)類(lèi)型為DATE的字段d，向表中插入“YYYY-MM-DD”和“YYYYMMDD”字符串格式日期，SQL語(yǔ)句如下：

①創(chuàng)建表tmp5：

　　MySQL> CREATE TABLE tmp5(d DATE);
　　Query OK, 0 rows affected (0.02 sec)

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

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

③查看插入結(jié)果：

可以看到，各個(gè)不同類(lèi)型的日期值都正確地插入到了數(shù)據(jù)表中。

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

①刪除表中的數(shù)據(jù)：

　　DELETE FROM tmp5;

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

　　mysql> INSERT INTO tmp5 values ('99-09-09'),( '990909'), ('000101') ,('111111');

③查看插入結(jié)果：

【例5.11】向tmp5表中插入YYYYMMDD和YYMMDD數(shù)字格式日期，SQL語(yǔ)句如下：

①刪除表中的數(shù)據(jù)：

　　DELETE FROM tmp5;

②向表中插入YYYYMMDD和YYMMDD數(shù)字格式日期：

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

③查看插入結(jié)果：

【例5.12】向tmp5表中插入系統(tǒng)當(dāng)前日期，SQL語(yǔ)句如下：

①刪除表中的數(shù)據(jù)：

　　DELETE FROM tmp5;

②向表中插入系統(tǒng)當(dāng)前日期：

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

③查看插入結(jié)果：

CURRENT_DATE只返回當(dāng)前日期值，不包括時(shí)間部分；NOW()函數(shù)返回日期和時(shí)間值，在保存到數(shù)據(jù)庫(kù)時(shí)，只保留了其日期部分。

4．DATETIME

DATETIME類(lèi)型用于需要同時(shí)包含日期和時(shí)間信息的值，在存儲(chǔ)時(shí)需要8字節(jié)。日期格式為‘YYYY-MM-DD HH:MM:SS’。其中，YYYY表示年，MM表示月，DD表示日，HH表示小時(shí)，MM表示分鐘，SS表示秒。在給DATETIME類(lèi)型的字段賦值時(shí)，可以使用字符串類(lèi)型或者數(shù)字類(lèi)型的數(shù)據(jù)插入，只要符合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’，插入數(shù)據(jù)庫(kù)的DATETIME值都為2012-12-31 05: 05: 05。

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

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

【例5.13】創(chuàng)建數(shù)據(jù)表tmp6，定義數(shù)據(jù)類(lèi)型為DATETIME的字段dt，向表中插入“YYYY-MM-DD HH:MM:SS”和“YYYYMMDDHHMMSS”字符串格式日期和時(shí)間值，SQL語(yǔ)句如下：

①創(chuàng)建表tmp6：

　　CREATE TABLE tmp6(　dt DATETIME );

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

③查看插入結(jié)果：

可以看到，各個(gè)不同類(lèi)型的日期值都正確地插入到了數(shù)據(jù)表中。

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

①刪除表中的數(shù)據(jù)：

　　DELETE FROM tmp6;

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

③查看插入結(jié)果：

【例5.15】向tmp6表中插入YYYYMMDDHHMMSS和YYMMDDHHMMSS數(shù)字格式日期和時(shí)間值，SQL語(yǔ)句如下：

①刪除表中的數(shù)據(jù)：

　　DELETE FROM tmp6;

②向表中插入YYYYMMDDHHMMSS和YYMMDDHHMMSS數(shù)字格式日期和時(shí)間：

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

③查看插入結(jié)果：

【例5.16】向tmp6表中插入系統(tǒng)當(dāng)前日期和時(shí)間值，SQL語(yǔ)句如下：

①刪除表中的數(shù)據(jù)：

　　DELETE FROM tmp6;

②向表中插入系統(tǒng)當(dāng)前日期：

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

③查看插入結(jié)果：

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

5．TIMESTAMP

TIMESTAMP的顯示格式與DATETIME相同，顯示寬度固定在19個(gè)字符，日期格式為YYYY-MM-DD HH:MM:SS，在存儲(chǔ)時(shí)需要4字節(jié)。TIMESTAMP列的取值范圍小于DATETIME的取值范圍，為‘1970-01-01 00:00:01’UTC～‘2038-01-19 03:14:07’UTC。其中，UTC（Coordinated Universal Time）為世界標(biāo)準(zhǔn)時(shí)間，因此在插入數(shù)據(jù)時(shí)，要保證在合法的取值范圍內(nèi)。

【例5.17】創(chuàng)建數(shù)據(jù)表tmp7，定義數(shù)據(jù)類(lèi)型為T(mén)IMESTAMP的字段ts，向表中插入值‘19950101010101’、‘950505050505’、‘1996-02-02 02:02:02’、‘97@03@03 03@03@03’、121212121212、NOW()，SQL語(yǔ)句如下：

①創(chuàng)建數(shù)據(jù)表tmp7：

　　CREATE TABLE tmp7( ts TIMESTAMP);

②向表中插入數(shù)據(jù)：

③查看插入結(jié)果：

由結(jié)果可以看到，‘19950101010101’被轉(zhuǎn)換為1995-01-01 01:01:01；‘950505050505’被轉(zhuǎn)換為1995-05-05 05:05:05；‘1996-02-02 02:02:02’被轉(zhuǎn)換為1996-02-02 02:02:02；‘97@03@03 03@03@03’被轉(zhuǎn)換為1997-03-03 03:03:03；121212121212被轉(zhuǎn)換為2012-12-12 12:12:12；NOW()被轉(zhuǎn)換為系統(tǒng)當(dāng)前日期時(shí)間2018-11-09 17:08:25。

【例5.18】向tmp7表中插入當(dāng)前日期，查看插入值，更改時(shí)區(qū)為東10區(qū)，再次查看插入值，SQL語(yǔ)句如下：

①刪除表中的數(shù)據(jù)：

　　DELETE FROM tmp7;

②向表中插入系統(tǒng)當(dāng)前日期：

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

③查看當(dāng)前時(shí)區(qū)下的日期值：

④查詢(xún)結(jié)果為插入時(shí)的日期值。讀者所在時(shí)區(qū)一般為東8區(qū)，下面修改當(dāng)前時(shí)區(qū)為東10區(qū)，SQL語(yǔ)句如下：

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

⑤再次查看插入時(shí)的日期值：

由結(jié)果可以看到，因?yàn)闁|10區(qū)時(shí)間比東8區(qū)快2個(gè)小時(shí)，因此查詢(xún)的結(jié)果經(jīng)過(guò)時(shí)區(qū)轉(zhuǎn)換之后，顯示的值增加了2個(gè)小時(shí)。相同的，如果時(shí)區(qū)每減小一個(gè)值，則查詢(xún)顯示的日期中的小時(shí)數(shù)減1。

5.1.4　文本字符串類(lèi)型

字符串類(lèi)型用來(lái)存儲(chǔ)字符串?dāng)?shù)據(jù)，除了可以存儲(chǔ)字符串?dāng)?shù)據(jù)之外，還可以存儲(chǔ)其他數(shù)據(jù)，比如圖片和聲音的二進(jìn)制數(shù)據(jù)。MySQL支持兩類(lèi)字符型數(shù)據(jù)：文本字符串和二進(jìn)制字符串。本小節(jié)主要講解文本字符串類(lèi)型。文本字符串可以進(jìn)行區(qū)分或者不區(qū)分大小寫(xiě)的串比較，還可以進(jìn)行模式匹配查找。在MySQL中，文本字符串類(lèi)型是指CHAR、VARCHAR、TEXT、ENUM和SET。表5.5列出了MySQL中的文本字符串?dāng)?shù)據(jù)類(lèi)型。

表5.5　MySQL中文本字符串?dāng)?shù)據(jù)類(lèi)型

VARCHAR和TEXT類(lèi)型與下一小節(jié)講到的BLOB都是變長(zhǎng)類(lèi)型，其存儲(chǔ)需求取決于列值的實(shí)際長(zhǎng)度（在前面的表格中用L表示），而不是取決于類(lèi)型的最大可能尺寸。例如，一個(gè)VARCHAR(10)列能保存最大長(zhǎng)度為10個(gè)字符的字符串，實(shí)際的存儲(chǔ)需要是字符串的長(zhǎng)度L加上1字節(jié)（記錄字符串的長(zhǎng)度）。對(duì)于字符“abcd”，L是4而存儲(chǔ)要求是5字節(jié)。本節(jié)將介紹這些數(shù)據(jù)類(lèi)型的作用以及如何在查詢(xún)中使用這些類(lèi)型。

1．CHAR和VARCHAR類(lèi)型

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

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

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

表5.6　CHAR(4)與VARCHAR(4)存儲(chǔ)區(qū)別

對(duì)比結(jié)果可以看到，CHAR(4)定義了固定長(zhǎng)度為4的列，不管存入的數(shù)據(jù)長(zhǎng)度為多少，所占用的空間均為4個(gè)字節(jié)；VARCHAR(4)定義的列所占的字節(jié)數(shù)為實(shí)際長(zhǎng)度加1。

查詢(xún)時(shí)，CHAR(4)和VARCHAR(4)的值并不一定相同，如【例5.20】所示。

【例5.20】創(chuàng)建tmp8表，定義字段ch和vch數(shù)據(jù)類(lèi)型依次為CHAR(4)、VARCHAR(4)，向表中插入數(shù)據(jù)“ab　”，SQL語(yǔ)句如下：

①創(chuàng)建表tmp8：

②輸入數(shù)據(jù)：

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

③查詢(xún)結(jié)果：

從查詢(xún)結(jié)果可以看到，ch在保存“ab　”時(shí)將末尾的兩個(gè)空格刪除了，而vch字段保留了末尾的兩個(gè)空格。

2．TEXT類(lèi)型

TEXT列保存非二進(jìn)制字符串，如文章內(nèi)容、評(píng)論等。當(dāng)保存或查詢(xún)TEXT列的值時(shí)，不刪除尾部空格。Text類(lèi)型分為4種：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。不同的TEXT類(lèi)型的存儲(chǔ)空間和數(shù)據(jù)長(zhǎng)度不同。

（1）TINYTEXT最大長(zhǎng)度為255（2⁸–1）字符的TEXT列。

（2）TEXT最大長(zhǎng)度為65535（2¹⁶–1）字符的TEXT列。

（3）MEDIUMTEXT最大長(zhǎng)度為16777215（2²⁴–1）字符的TEXT列。

（4）LONGTEXT最大長(zhǎng)度為4294967295（2³²–1）或4GB字符的TEXT列。

3．ENUM類(lèi)型

ENUM是一個(gè)字符串對(duì)象，其值為表創(chuàng)建時(shí)在列規(guī)定中枚舉的一列值。語(yǔ)法格式如下：

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

其中，“字段名”指將要定義的字段，“值n”指枚舉列表中的第n個(gè)值。ENUM類(lèi)型的字段在取值時(shí)，只能在指定的枚舉列表中取，而且一次只能取一個(gè)。創(chuàng)建的成員中有空格時(shí)，其尾部的空格將自動(dòng)被刪除。ENUM值在內(nèi)部用整數(shù)表示，并且每個(gè)枚舉值均有一個(gè)索引值：列表值所允許的成員值從1開(kāi)始編號(hào)，MySQL存儲(chǔ)的就是這個(gè)索引編號(hào)。枚舉最多可以有65535個(gè)元素。

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

表5.7　ENUM類(lèi)型的取值范圍

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

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

【例5.21】創(chuàng)建表tmp9，定義ENUM類(lèi)型的列enm('first'，'second'，'third')，查看列成員的索引值，SQL語(yǔ)句如下：

①創(chuàng)建tmp9表：

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

②插入各個(gè)列值：

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

③查看索引值：

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

【例5.22】創(chuàng)建表tmp10，定義INT類(lèi)型的soc字段，ENUM類(lèi)型的字段level，并且列表值為('excellent','good', 'bad')，向表tmp10中插入數(shù)據(jù)(70,'good')、(90,1)、(75,2)、(50,3)、(100,'best')，SQL語(yǔ)句如下：

①創(chuàng)建數(shù)據(jù)表：

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

②插入數(shù)據(jù)：

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

③再次插入數(shù)據(jù)：

　　mysql>INSERT INTO tmp10 values (100,'best');
　　ERROR 1265 (01000): Data truncated for column 'level' at row 1

這里系統(tǒng)提示錯(cuò)誤信息，可以看到，由于字符串值'best'不在ENUM列表中，所以對(duì)數(shù)據(jù)進(jìn)行了阻止插入操作，查詢(xún)結(jié)果如下：

由結(jié)果可以看到，因?yàn)镋NUM列表中的值在MySQL中都是以編號(hào)序列存儲(chǔ)的，所以插入列表中的值“good”或者插入其對(duì)應(yīng)序號(hào)‘2’的結(jié)果是相同的。“best”不是列表中的值，因此不能插入數(shù)據(jù)。

4．SET類(lèi)型

SET是一個(gè)字符串對(duì)象，可以有零或多個(gè)值。SET列最多可以有64個(gè)成員，其值為表創(chuàng)建時(shí)規(guī)定的一列值。指定包括多個(gè)SET成員的SET列值時(shí)，各成員之間用逗號(hào)(,)間隔開(kāi)。語(yǔ)法格式如下：

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

與ENUM類(lèi)型相同，SET值在內(nèi)部用整數(shù)表示，列表中每一個(gè)值都有一個(gè)索引編號(hào)。當(dāng)創(chuàng)建表時(shí)，SET成員值的尾部空格將自動(dòng)被刪除。與ENUM類(lèi)型不同的是，ENUM類(lèi)型的字段只能從定義的列值中選擇一個(gè)值插入，而SET類(lèi)型的列可從定義的列值中選擇多個(gè)字符的聯(lián)合。

如果插入SET字段中列值有重復(fù)，則MySQL自動(dòng)刪除重復(fù)的值；插入SET字段的值的順序并不重要，MySQL會(huì)在存入數(shù)據(jù)庫(kù)時(shí)按照定義的順序顯示；如果插入了不正確的值，默認(rèn)情況下，MySQL將忽視這些值，并給出警告。

【例5.23】創(chuàng)建表tmp11，定義SET類(lèi)型的字段s，取值列表為('a', 'b', 'c', 'd')，插入數(shù)據(jù)('a')、('a,b,a')、('c,a,d')、('a,x,b,y')，SQL語(yǔ)句如下：

①創(chuàng)建表tmp11：

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

②插入數(shù)據(jù)：

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

③再次插入數(shù)據(jù)：

　　mysql>INSERT INTO tmp11 values ('a,x,b,y');
　　ERROR 1265 (01000): Data truncated for column 's' at row 1

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

④查看結(jié)果：

從結(jié)果可以看到，對(duì)于SET來(lái)說(shuō)，如果插入的值為重復(fù)的，則只取一個(gè)，例如插入“a,b,a”，則結(jié)果為“a,b”；如果插入了不按順序排列的值，則自動(dòng)按順序插入，例如插入“c,a,d”，結(jié)果為“a,c,d”；如果插入了不正確的值，那么該值將被阻止插入，例如插入值“a,x,b,y”。

5.1.5　二進(jìn)制字符串類(lèi)型

前面講解了存儲(chǔ)文本的字符串類(lèi)型，這一小節(jié)將講解MySQL中存儲(chǔ)二進(jìn)制數(shù)據(jù)的字符串類(lèi)型。MySQL中的二進(jìn)制數(shù)據(jù)類(lèi)型有BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。本節(jié)將講解各類(lèi)二進(jìn)制字符串類(lèi)型的特點(diǎn)和使用方法。表5.8列出了MySQL中的二進(jìn)制數(shù)據(jù)類(lèi)型。

表5.8　MySQL中的二進(jìn)制字符串類(lèi)型

1．BIT類(lèi)型

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

【例5.24】創(chuàng)建表tmp12，定義BIT(4)類(lèi)型的字段b，向表中插入數(shù)據(jù)2、9、15。

①創(chuàng)建表tmp12：

　　CREATE TABLE tmp12( b BIT(4) );

②插入數(shù)據(jù)：

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

③查詢(xún)插入結(jié)果：

b+0表示將二進(jìn)制的結(jié)果轉(zhuǎn)換為對(duì)應(yīng)的數(shù)字的值，BIN()函數(shù)將數(shù)字轉(zhuǎn)換為二進(jìn)制。從結(jié)果可以看到，成功地將3個(gè)數(shù)插入表中。

2．BINARY和VARBINARY類(lèi)型

BINARY和VARBINARY類(lèi)型類(lèi)似于CHAR和VARCHAR，不同的是它們包含二進(jìn)制字節(jié)字符串。其使用的語(yǔ)法格式如下：

　　列名稱(chēng) BINARY(M)或者VARBINARY(M)

BINARY類(lèi)型的長(zhǎng)度是固定的，指定長(zhǎng)度之后，不足最大長(zhǎng)度的，將在它們右邊填充‘\0’補(bǔ)齊以達(dá)到指定長(zhǎng)度。例如：指定列數(shù)據(jù)類(lèi)型為BINARY(3)，當(dāng)插入‘a(chǎn)’時(shí)，存儲(chǔ)的內(nèi)容實(shí)際為“a\0\0”，當(dāng)插入“ab”時(shí)，實(shí)際存儲(chǔ)的內(nèi)容為“ab\0”，不管存儲(chǔ)的內(nèi)容是否達(dá)到指定的長(zhǎng)度，其存儲(chǔ)空間均為指定的值M。

VARBINARY類(lèi)型的長(zhǎng)度是可變的，指定好長(zhǎng)度之后，其長(zhǎng)度可以在0到最大值之間。例如：指定列數(shù)據(jù)類(lèi)型為VARBINARY(20)，如果插入的值的長(zhǎng)度只有10，則實(shí)際存儲(chǔ)空間為10加1，即實(shí)際占用的空間為字符串的實(shí)際長(zhǎng)度加1。

【例5.25】創(chuàng)建表tmp13，定義BINARY(3)類(lèi)型的字段b和VARBINARY(3)類(lèi)型的字段vb，并向表中插入數(shù)據(jù)‘5’，比較兩個(gè)字段的存儲(chǔ)空間。

①創(chuàng)建表tmp13：

　　CREATE TABLE tmp13(
　　b binary(3),　vb varbinary(3));

②插入數(shù)據(jù)：

　　INSERT INTO tmp13 VALUES(5,5);

③查看兩個(gè)字段存儲(chǔ)數(shù)據(jù)的長(zhǎng)度：

可以看到，b字段的值數(shù)據(jù)長(zhǎng)度為3，而vb字段的數(shù)據(jù)長(zhǎng)度僅為插入的一個(gè)字符的長(zhǎng)度1。

如果想要進(jìn)一步確認(rèn)‘5’在兩個(gè)字段中不同的存儲(chǔ)方式，輸入如下語(yǔ)句：

由執(zhí)行結(jié)果可以看出，b字段和vb字段的長(zhǎng)度是截然不同的，因?yàn)閎字段不足的空間填充了‘\0’，而vb字段則沒(méi)有填充。

3．BLOB類(lèi)型

BLOB是一個(gè)二進(jìn)制大對(duì)象，用來(lái)存儲(chǔ)可變數(shù)量的數(shù)據(jù)。BLOB類(lèi)型分為4種：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB，它們可容納值的最大長(zhǎng)度不同，如表5.9所示。

表5.9　BLOB類(lèi)型的存儲(chǔ)范圍

BLOB列存儲(chǔ)的是二進(jìn)制字符串（字節(jié)字符串），TEXT列存儲(chǔ)的是非二進(jìn)制字符串（字符字符串）。BLOB列沒(méi)有字符集，并且排序和比較基于列值字節(jié)的數(shù)值；TEXT列有一個(gè)字符集，并且根據(jù)字符集對(duì)值進(jìn)行排序和比較。