MySQL教程（第4版） 課件 第2章 數(shù)據(jù)類型

第2章

數(shù)

據(jù)

類

型——數(shù)

值

型01整

數(shù)

類

型整數(shù)類型整數(shù)類型包括的數(shù)據(jù)類型如表。數(shù)據(jù)類型字節(jié)數(shù)有符號(hào)范圍無(wú)符號(hào)范圍（含）tinyint1-128~127（-27~27-1）0~255（0~28-1）smallint2-32768~32767（-215~215-1）0~65535（0~216-1）mediumint3-8388608~8388607（-223~223-1）0~16777215（0~224-1）intinteger4-2147483648~2147483647（-231~231-1）0~4294967295（0~232-1）bigint8±9.22×1018（-263~263-1）0~1.84×1019（0~264-1）02實(shí)

數(shù)

類

型單精度浮點(diǎn)類型雙精度浮點(diǎn)類型定點(diǎn)類型實(shí)數(shù)類型實(shí)數(shù)類型包括浮點(diǎn)類型和定點(diǎn)類型，浮類點(diǎn)類型分為單精度浮點(diǎn)型（float和real）和雙精度浮點(diǎn)型（double），它們遵循IEEE754標(biāo)準(zhǔn)；而定點(diǎn)型為decimal或者numeric型，實(shí)數(shù)類型定義格式如下：實(shí)數(shù)類型名(長(zhǎng)度.小數(shù)位)1．單精度浮點(diǎn)類型表達(dá)單精度浮點(diǎn)數(shù)的float或real類型數(shù)據(jù)占4字節(jié)，其中1位表示符號(hào)位，8位表示指數(shù)，23位表示尾數(shù)。其中：有符號(hào)數(shù)表示范圍：（-3.402823466E+38~1.175494351E-38)，0，(1.175494351E-38~3.402823466351E+38）；無(wú)符號(hào)數(shù)表示范圍：0，（1.175494351E-38~3.402823466E+38）。float可以用含小數(shù)的數(shù)值表示，也可以用科學(xué)記數(shù)法表示，但只保證6位有效數(shù)字的準(zhǔn)確性。定義float(m,d)表示數(shù)值總長(zhǎng)為m位，其中有d位小數(shù)。例如，列設(shè)置為float(10,2)，輸入-3584678.268，查詢結(jié)果為-3584678.25。實(shí)數(shù)類型2．雙精度浮點(diǎn)類型表達(dá)雙精度浮點(diǎn)數(shù)double類型數(shù)據(jù)占8字節(jié)，其中1位表示符號(hào)位，11位表示指數(shù)，52位表示尾數(shù)。其中：有符號(hào)數(shù)表示范圍：（-1.7976931348623157E+308~2.2250738585072014E-308），0，（2.2250738585072014E-308~1.7976931348623157E+308）；無(wú)符號(hào)數(shù)表示范圍：0,（2.2250738585072014E-308~1.7976931348623157E+308）。定義double(m,d)表示數(shù)值總長(zhǎng)為m位，其中有d位小數(shù)。double只保證16位有效數(shù)字的準(zhǔn)確性。實(shí)數(shù)類型3．定點(diǎn)類型定點(diǎn)類型為decimal或者numeric類型，它們是同義詞，用于存儲(chǔ)定點(diǎn)數(shù)，保存必須為確切精度的值，例如會(huì)計(jì)系統(tǒng)中的貨幣數(shù)據(jù)。定點(diǎn)類型數(shù)值總長(zhǎng)度范圍為1～65，默認(rèn)為10位數(shù)字，沒(méi)有小數(shù)位。定義decimal(m,d)表示總長(zhǎng)度m位，其中有d位小數(shù)。decimal(m)沒(méi)有小數(shù)。小數(shù)部分超出d位，若四舍五入后，整數(shù)部分沒(méi)有超出范圍，則只給出警告，但能成功操作并四舍五入刪除多余的小數(shù)位后保存；若四舍五入后，整數(shù)部分超出范圍，則會(huì)報(bào)錯(cuò)，并拒絕處理。例如，列設(shè)置為decimal(10,2)，輸入-3584678.268，查詢結(jié)果為-3584678.27。03位

類

型位

類

型位類型就是bit類型，bit(n)型允許存儲(chǔ)n位二進(jìn)制位值（n取值為1～64，默認(rèn)為1）。與tinyint相比，即使僅僅存儲(chǔ)0和1兩種狀態(tài)的值，也需要1個(gè)字節(jié)，而bit(1)存儲(chǔ)0和1兩種狀態(tài)的值只需要1個(gè)二進(jìn)制位。如果我們存儲(chǔ)的狀態(tài)不超過(guò)2n，就可以使用bit(n)。另外，向位類型列輸入的值只要<2n，就可以表達(dá)為二進(jìn)制、十進(jìn)制、十六進(jìn)制等（例如0b10011、63、0x41），但bit(n)查詢顯示輸出的是二進(jìn)制值。第2章

數(shù)

據(jù)

類

型——字符串和二進(jìn)制串型01字符串類型字符串描述字符集字符排序規(guī)則字符串類型1．字符串描述字符串?dāng)?shù)據(jù)類型如表。數(shù)據(jù)類型最多存放字符個(gè)數(shù)（字節(jié)）char(n)255varchar(n)65535（2）tinytext255（1）text65535（2）mediumtext16777215（3）longtext4294967295（4）字符串類型說(shuō)明：（1）char(n)和varchar(n)括號(hào)中的n代表字符的個(gè)數(shù)，不是字節(jié)數(shù)。例如，采用UTF8字符集，存放n個(gè)純ASCII碼字符，占用n個(gè)字節(jié)。（2）實(shí)際上，tinytext、text、mediumtext、longtexttext類型就相當(dāng)于varchar(n)，n就是該類型最大可存放的字符個(gè)數(shù)。例如：text類型相當(dāng)于varchar(65535)。（3）如果一個(gè)表中的任何列都有可變長(zhǎng)度，結(jié)果會(huì)使整個(gè)行變長(zhǎng)。如果一張表包含任何變長(zhǎng)的列（varchar、text等），則所有大于3個(gè)字符的char列都將被改變?yōu)関archar列。（4）可變長(zhǎng)度實(shí)際存儲(chǔ)空間除了字符本身外，還需要額外存儲(chǔ)當(dāng)前字符占用的空間字節(jié)數(shù)，而它本身占的字節(jié)為其數(shù)據(jù)類型最大字符數(shù)。例如：varchar(60000)，當(dāng)前值'abc'，占用空間為3+2=5，前者是內(nèi)容占用，后者為長(zhǎng)度占用，因?yàn)樽畲箝L(zhǎng)度60000需要用2個(gè)字節(jié)才能存放。（5）字符串?dāng)?shù)據(jù)類型數(shù)據(jù)用單引號(hào)和雙引號(hào)括起，內(nèi)容可以是平臺(tái)字符，也可以是轉(zhuǎn)義字符。例如“\'”為單引號(hào)，“\n”為回車。字符串類型2．字符集ASCII碼及其字符集ASCII碼即美國(guó)標(biāo)準(zhǔn)信息交換碼，是目前世界上的計(jì)算機(jī)中使用最廣泛的西文字符集編碼。西文字符集由拉丁字母、數(shù)字、標(biāo)點(diǎn)符號(hào)及一些特殊符號(hào)組成。在標(biāo)準(zhǔn)ASCII碼表中，20H～7EH部分屬于可打印字符，共95個(gè)。GB2312-80編碼及其字符集1981年，我國(guó)頒布信息交換漢字編碼的第一個(gè)國(guó)家標(biāo)準(zhǔn)，稱為GB2312-80。其字符集由以下3部分組成。（1）字母、數(shù)字和各種符號(hào)，包括拉丁字母、俄文、日文平假名與片假名、希臘字母、漢語(yǔ)拼音等，共682個(gè)。（2）一級(jí)常用漢字，共3755個(gè)，按漢語(yǔ)拼音排列。（3）二級(jí)常用漢字，共3008個(gè)，按偏旁部首排列。GB2312-80字符集構(gòu)成一個(gè)二維碼表，它分成94行和94列，行號(hào)稱為區(qū)號(hào)，列號(hào)稱為位號(hào)，如圖。字符串類型GB2312-80字符集的每個(gè)漢字或符號(hào)在碼表中都有各自的位置，即所在的區(qū)號(hào)及位號(hào)。用區(qū)號(hào)及位號(hào)組合在一起的編碼就是該漢字區(qū)位碼。例如，“中”字的區(qū)號(hào)是54，位號(hào)是48，區(qū)位碼是（54，48），用十六進(jìn)制表示為（36H，30H）。為了與西文字符共存，漢字內(nèi)碼不能與ASCII碼沖突。因?yàn)锳SCII碼用一個(gè)字節(jié)表示，最高位為0；漢字內(nèi)碼用2個(gè)字節(jié)表示，每個(gè)字節(jié)的最高位為1。如圖。字符串類型GBK編碼及其字符集GB2312編碼漢字太少，缺少繁體字，無(wú)法滿足人名、地名、古籍整理、古典文獻(xiàn)研究等應(yīng)用的需要。于是在1995年推出了“漢字內(nèi)碼擴(kuò)充規(guī)范”，稱為GBK標(biāo)準(zhǔn)，它在GB2312的基礎(chǔ)上增加了大量的漢字（包括繁體字）和符號(hào)，共21003個(gè)漢字和883個(gè)圖形符號(hào)。GB2312中的字符仍然采用原來(lái)的編碼，僅僅對(duì)新增加的符號(hào)和漢字進(jìn)行另外編碼。GBK漢字內(nèi)碼也使用雙字節(jié)表示，但僅第1字節(jié)最高位為“1”，如圖。字符串類型Unicode編碼及UTF8字符集Unicode只是字符集，卻沒(méi)有規(guī)定該如何存儲(chǔ)、傳輸，而UTF規(guī)定了字符如何傳輸和存儲(chǔ)。UTF又分為UTF-8、UTF-16和UTF-32，它們都可以完全表示Unicode標(biāo)準(zhǔn)中的所有字符，而UTF-8使用較多。UTF-8為單字節(jié)可變長(zhǎng)編碼，它根據(jù)Huffman編碼的思想（最常出現(xiàn)的字符編碼盡量短），以及不同符號(hào)使用不同的字節(jié)長(zhǎng)度表示，因此UTF-8采用了1～4個(gè)字節(jié)表示一個(gè)符號(hào)變長(zhǎng)的編碼方式。從Unicode到UTF-8的編碼方式如表。Unicode編碼(十六進(jìn)制)UTF-8字節(jié)流(二進(jìn)制)字符000000-00007F0xxxxxxxASCII字符000080-0007FF110xxxxx10xxxxxx拉丁、希臘、阿拉伯……000800-00FFFF1110xxxx10xxxxxx10xxxxxx中、日、韓統(tǒng)一整理出來(lái)的稱為CJK漢字010000-10FFFF11110xxx10xxxxxx10xxxxxx10xxxxxx其他字符。字符串類型3．字符排序規(guī)則字符排序又稱字符序，MySQL中眾多的排序規(guī)則可以歸納為以下兩大類排序集：（1）字符集_語(yǔ)言/other_ci/cs這是默認(rèn)的排序集，當(dāng)字符無(wú)須區(qū)分大小寫時(shí)采用該集合中的規(guī)則，規(guī)則命名以其對(duì)應(yīng)的字符集作為前綴，例如：字符序utf8_general_ci，表明它是字符集utf8的字符序。ci對(duì)大小寫不敏感，而cs對(duì)大小寫敏感。utf8字符集對(duì)應(yīng)的collation沒(méi)有cs。（2）字符集_bin當(dāng)字符需要區(qū)分大小寫時(shí)，選擇該排序集。每個(gè)字符集對(duì)應(yīng)一定數(shù)量的排序規(guī)則，可以指定數(shù)據(jù)集如何排序以及字符串的排序規(guī)則。MySQL提供了4個(gè)等級(jí)的默認(rèn)字符集以及排序規(guī)則，分別是服務(wù)器、數(shù)據(jù)庫(kù)、表和列級(jí)別。02二進(jìn)制串類型二進(jìn)制數(shù)據(jù)類型大二進(jìn)制數(shù)據(jù)類型二進(jìn)制串類型1．二進(jìn)制數(shù)據(jù)類型二進(jìn)制數(shù)據(jù)類型包括binary和varbinary，它們類似于字符串類型char和varchar，但不同的是，它們存儲(chǔ)的不是字符串，而是二進(jìn)制串。所以它們沒(méi)有字符集，并且排序和比較需要基于列字節(jié)的數(shù)值。二進(jìn)制數(shù)據(jù)類型列可以保存數(shù)值，也可以保存字符串，均變成對(duì)應(yīng)的字節(jié)保存。而且經(jīng)常將聲音、圖像、視頻等文件用系統(tǒng)函數(shù)轉(zhuǎn)換成字節(jié)數(shù)據(jù)保存在其中。當(dāng)保存binary值時(shí)，在它們右側(cè)填充0x00值以達(dá)到指定長(zhǎng)度。取值時(shí)不刪除尾部的字節(jié)。二進(jìn)制串類型2．大二進(jìn)制數(shù)據(jù)類型大二進(jìn)制數(shù)據(jù)類型為blob類型，是一個(gè)二進(jìn)制對(duì)象，可以存儲(chǔ)數(shù)據(jù)量很大的二進(jìn)制數(shù)據(jù)，也可視為足夠大的varbinary列，可用于存儲(chǔ)大的圖片、視頻等。有4種blob類型：tinyblob、blob、mediumblob和longblob，它們可容納值的最大長(zhǎng)度不同。tinyblob類型：最大長(zhǎng)度為255（28-1）字節(jié)。blob類型：最大長(zhǎng)度為65535（216-1）字節(jié)。mediumblob類型：最大長(zhǎng)度為16777215（224-1）字節(jié)。longblob類型：最大長(zhǎng)度為4294967295（232-1）字節(jié)。實(shí)際長(zhǎng)度與編碼有關(guān)，比如UTF8編碼，長(zhǎng)度會(huì)減半。二進(jìn)制串類型說(shuō)明：（1）當(dāng)保存或檢索blob和text列的值時(shí)不刪除尾部空格，這與varbinary和varchar列相同。（2）比較時(shí)將用空格對(duì)text進(jìn)行擴(kuò)充以適合比較的對(duì)象，正如char和varchar。（3）對(duì)于blob和text列的索引，必須指定索引前綴的長(zhǎng)度。對(duì)于char和varchar，前綴長(zhǎng)度是可選的。（4）blob和text列不能有默認(rèn)值。（5）mysqlconnector/odbc將blob值定義為longvarbinary，將text值定義為longvarchar。（6）blob或text對(duì)象的最大值由其類型確定，但在客戶端和服務(wù)器之間實(shí)際可以傳遞的最大值由可用內(nèi)存數(shù)量和通信緩存區(qū)大小確定。（7）每個(gè)blob或text值分別由內(nèi)部分配的對(duì)象表示。第2章

數(shù)

據(jù)

類

型——日期時(shí)間型日期時(shí)間型1．日期類型日期類型包含datetime、date和timestamp類型，這3者都用來(lái)表示日期/時(shí)間。（1）datetime類型：以'yyyy-mm-ddhh:mm:ss'（年-月-日時(shí):分:秒）格式檢索和顯示。支持的范圍為'1000-01-0100:00:00'～'9999-12-3123:59:59'。（2）date類型：用'yyyy-mm-dd'（年-月-日）格式檢索和顯示。支持的范圍為'1000-01-01'～'9999-12-31'。（3）timestamp類型：包含日期和時(shí)間，范圍為'1970-01-0100:00:01'utc～'2038-01-1903:14:07'utc。該類型能夠自動(dòng)存儲(chǔ)記錄修改的時(shí)間。timestamp的顯示尺寸必須是偶數(shù)且在2～14范圍內(nèi)。如果指定0顯示尺寸或比14大，尺寸被強(qiáng)制設(shè)定為14。在1～13范圍內(nèi)的奇數(shù)值尺寸被強(qiáng)制設(shè)定為下一個(gè)更大的偶數(shù)。將它設(shè)為NULL，默認(rèn)為當(dāng)前的日期和時(shí)間?？梢允褂萌魏纬Ｒ姼袷街付╠atetime、date和timestamp值。對(duì)于包括日期部分間隔符的字符串值，如果日和月的值都小于10，則不需要指定兩位數(shù)，例如'1979-6-9'與'1979-06-09'相同。如果一個(gè)數(shù)值是8位或14位長(zhǎng)，則假定為'yyyymmdd'或'yyyymmddhhmmss'格式，前4位數(shù)表示年。如果為一個(gè)datetime或timestamp對(duì)象分配一個(gè)date值，結(jié)果值的時(shí)間部分被設(shè)置為'00:00:00'。函數(shù)返回的結(jié)果，其值適合datetime、date或者timestamp上下文，如now()或current_date。日期時(shí)間型2．時(shí)間類型time值的范圍為'-838:59:59'～'838:59:59'。（1）可以用各種格式指定time值，如下。'dhh:mm:ss.fraction'格式的字符串。還可以用'hh:mm:ss.fraction'、'hh:mm:ss'、'hh:mm'、'dhh:mm:ss'、'dhh:mm'、'dhh'或'ss'。這里d表示日期，取值范圍為0～31。'hhmmss'格式的沒(méi)有間隔符的字符串，假定是有意義的時(shí)間。例如，'101112'被理解為'10:11:12'。hhmmss格式的數(shù)值，假定是有意義的時(shí)間。例如，101112被理解為'10:11:12'。（2）函數(shù)返回的結(jié)果，其值適合time上下文，如current_time。（3）對(duì)于指定為包括時(shí)間部分間隔符的字符串的time值，如果時(shí)、分或者秒值小于10，則不需要指定兩位數(shù)。例如，'8:3:2'與'08:03:02'相同。（4）如果time值沒(méi)有冒號(hào)，則最右側(cè)的兩位數(shù)表示秒，time值表示過(guò)去的時(shí)間而不是當(dāng)天的時(shí)間。例如，'1112'和1112被解釋為'00:11:12'（11分12秒），'12'和12被解釋為'00:00:12'。相反，如果time值中使用冒號(hào)，則肯定被看作當(dāng)天的時(shí)間。例如，'11:12'表示'11:12:00'，而不是'00:11:12'。（5）無(wú)效time值被轉(zhuǎn)換為'00:00:00'。由于'00:00:00'本身是一個(gè)合法的time值，所以只從一個(gè)'00:00:00'值還不能判斷原來(lái)的值是'00:00:00'還是不合法的。日期時(shí)間型3．年類型年類型就是year類型，是一個(gè)單字節(jié)類型，用于表示年。MySQL以yyyy格式檢索和顯示year值，范圍為1901～2155。（1）可以指定各種格式的year值，如下。4位字符串：如'2018'。4位數(shù)字：如2018。2位字符串：'00'～69'轉(zhuǎn)換為2000～2069，'70'～'99'轉(zhuǎn)換為1970～1999。例如，'18'表示2018。一個(gè)字符串'0'或'00'可能被解釋為0000。2位整數(shù)：范圍為1～99。1～69轉(zhuǎn)換為2001～2069，70～99轉(zhuǎn)換為1970～1999。不能直接將零指定為數(shù)字并將它解釋為2000。（2）函數(shù)返回的結(jié)果，其值適合year上下文，如now()。（3）非法year值被轉(zhuǎn)換為0000。第2章

數(shù)

據(jù)

類

型——枚舉、集合、鍵值和空間型01枚舉類型和集合類型枚舉類型：enum集合類型：set枚舉類型和集合類型1．枚舉類型：enum（1）定義enum('值1','值2','值3'...,'值n')定義需要enum需要設(shè)置成員表，成員為字符串（尾部空格將自動(dòng)被刪除），即使成員是數(shù)字，也必須是加引號(hào)的字符串。成員之間用“,”分隔。enum類型創(chuàng)建時(shí)指定的成員就是枚舉值，每個(gè)枚舉值位置對(duì)應(yīng)一個(gè)索引編號(hào)，MySQL存儲(chǔ)的就是這個(gè)索引編號(hào)。（2）存儲(chǔ)enum類型存儲(chǔ)的不是成員的字符串，而是成員的索引編號(hào)，定義1～255個(gè)成員的枚舉索引編號(hào)需要1個(gè)字節(jié)存儲(chǔ)；對(duì)于256～65535個(gè)成員，索引編號(hào)需要2個(gè)字節(jié)存儲(chǔ)。例如，“專業(yè)”枚舉定義enum('計(jì)算機(jī)','通信工程','人工智能')，該列可以取的值和對(duì)應(yīng)的索引編號(hào)如表。成員索引編號(hào)NULLNULL“0'計(jì)算機(jī)'1'通信工程'2'人工智能'3枚舉類型和集合類型2．集合類型：set（1）定義SET('值1','值2','值3'...,'值n')set類型成員不能為空，最少需要一個(gè)成員，最多包含64個(gè)成員。實(shí)際內(nèi)容為這些成員構(gòu)成的組合，1～8個(gè)成員的集合占1個(gè)字節(jié)，9～16個(gè)成員的集合占2個(gè)字節(jié)，17～24個(gè)成員的集合占3個(gè)字節(jié)，25～32個(gè)成員的集合占4個(gè)字節(jié)，33～64個(gè)成員的集合占8個(gè)字節(jié)。（2）存儲(chǔ)set類型存儲(chǔ)的不是成員的字符串，而是其二進(jìn)制位信息。例如：“興趣”集合定義set('唱歌','跳舞','智力游戲','足球運(yùn)動(dòng)','書法')?！芭d趣”成員和存儲(chǔ)值表示如表。成員二進(jìn)制值十進(jìn)制值'唱歌'11'跳舞'102'智力游戲'1004'足球運(yùn)動(dòng)'10008'書法'100001602鍵值（JSON）類型JSON對(duì)象JSON數(shù)組鍵值（JSON）類型1．JSON對(duì)象JSON類型的數(shù)據(jù)是以JSON對(duì)象的形式提供的，一個(gè)標(biāo)準(zhǔn)的JSON對(duì)象包含一組鍵值對(duì)，用逗號(hào)分隔，以“{”和“}”字符括起來(lái)：{"鍵1":"值1",...}創(chuàng)建JSON對(duì)象采用JSON_OBJECT系統(tǒng)函數(shù)格式如下：JSON_OBJECT(鍵1,值1,鍵2,值2,...)創(chuàng)建JSON對(duì)象和存儲(chǔ)遵循的規(guī)則基本如下：（1）鍵名（無(wú)論中英文）都必須以雙引號(hào)（"）括起來(lái)。（2）鍵值是字符串型（無(wú)論中英文）必須加雙引號(hào)（"）或單引號(hào)（'）括起來(lái)。（3）鍵值是數(shù)值型（整數(shù)或?qū)崝?shù)類型），可以以數(shù)值或字符串兩種格式存儲(chǔ)。當(dāng)以數(shù)值存儲(chǔ)時(shí)，直接寫出數(shù)值即可；而以字符串存儲(chǔ)時(shí)，鍵值需加雙引號(hào)或單引號(hào)。（4）鍵值是日期時(shí)間型，可以用兩種方式給出：一種是直接以字符串形式寫出；另一種是通過(guò)系統(tǒng)內(nèi)置的日期時(shí)間函數(shù)獲得。JSON對(duì)象構(gòu)建的數(shù)據(jù)如圖。鍵值（JSON）類型2．JSON數(shù)組當(dāng)需要同時(shí)存儲(chǔ)很多JSON對(duì)象或者標(biāo)量值時(shí)，可將它們集中在一起構(gòu)造出一個(gè)數(shù)組數(shù)據(jù)結(jié)構(gòu)來(lái)統(tǒng)一管理。JSON數(shù)組包含多個(gè)值，這些值由逗號(hào)分隔格式如下：JSON_ARRAY(值,...)JSON數(shù)組元素顯示包含在“[”和“]”中如下：[{鍵1":"值1",...},...]JSON數(shù)組構(gòu)建的數(shù)據(jù)如圖。03空

間

類

型單一空間類空間集合類空

間

類

型1．單一空間類（1）點(diǎn)類型：point(xy)存放一個(gè)坐標(biāo)值，用空格分隔。例如：point(18206)。point(經(jīng)度，維度)