MySQL實(shí)用教程(新體系.綜合應(yīng)用實(shí)例視頻)(第4版）第3章數(shù)據(jù)類型

1、MySQL+實(shí)用教程（第4版）MySQL 8.0包含豐富的數(shù)據(jù)類型，可以方便處理各種數(shù)據(jù)。系統(tǒng)數(shù)據(jù)類型如表3.1所示。數(shù)數(shù) 據(jù)據(jù) 類類 型型符符 號(hào)號(hào) 標(biāo)標(biāo) 識(shí)識(shí)整數(shù)類型tinyint, smallint, mediumint, int, bigint向下兼容：bool(boolean)實(shí)數(shù)類型float, double, decimal, numeric日期與時(shí)間類型year, time, date, datetime, timestamp字符串類型char, varchar, tinytext, text, mediumtext, longtextbit, binary, varbinar

2、y, tinyblob, blob, mediumblob, longblob枚舉、集合類型enum, setJSON數(shù)據(jù)類型json空間數(shù)據(jù)類型point, multipoint, polygon, multipolygon,geometry, geometrycollection, linestring, multilinestring,目 錄目 錄目 錄0101整數(shù)類型整數(shù)類型整數(shù)類型可表示有符號(hào)或無(wú)符號(hào)的整數(shù)數(shù)值，其最大值和最小值（含符號(hào)位）確定了默認(rèn)的顯示位數(shù)。表3.2列出了MySQL中所有的整數(shù)類型主要屬性。數(shù)據(jù)類型數(shù)據(jù)類型字節(jié)數(shù)字節(jié)數(shù)有符號(hào)范圍有符號(hào)范圍無(wú)符號(hào)范圍無(wú)符號(hào)范圍tin

3、yinttinyint1-128127（-2727-1）0255（028-1）smallintsmallint2-3276832767（-215215-1）065535（0216-1）mediumintmediumint3-83886088388607（-223223-1）016777215（0224-1）intintintegerinteger4-2147483648 2147483647（-231231-1）04294967295（0232-1）bigintbigint89.221018（-263263-1）01.841019（0264-1）（1）上表“數(shù)據(jù)類型”定義的都是有符號(hào)的，加上u

4、nsigned關(guān)鍵字，定義成無(wú)符號(hào)的類型。（2）MySQL中是沒(méi)有布爾類型的，但為了兼容SQL標(biāo)準(zhǔn)，也可以定義聲明bool（boolean）類型，但它們最終都會(huì)被轉(zhuǎn)換成tinyint(1)類型存儲(chǔ)。目 錄目 錄目 錄0202實(shí)數(shù)類型實(shí)數(shù)類型1. 浮點(diǎn)數(shù)2. 定點(diǎn)數(shù)3. 數(shù)值顯示長(zhǎng)度1. 1. 浮點(diǎn)數(shù)浮點(diǎn)數(shù)浮點(diǎn)數(shù)又有兩種格式：?jiǎn)尉雀↑c(diǎn)數(shù)（float）和雙精度浮點(diǎn)數(shù)（double），遵循IEEE 754標(biāo)準(zhǔn)。real是double的同義詞，除非啟用了real_as_float模式才能作為單精度浮點(diǎn)數(shù)使用。在IEEE 754標(biāo)準(zhǔn)下，浮點(diǎn)格式主要分為四種類型，即單精度格式、雙精度格式、擴(kuò)展單精度格式

5、和擴(kuò)展雙精度格式。其中32位單精度格式與64位雙精度格式作為基礎(chǔ)格式更為常用，擴(kuò)展格式則有特殊目的，一般對(duì)用戶透明。浮點(diǎn)格式可分為符號(hào)位s、指數(shù)位e以及尾數(shù)位f三部分，如圖3.1所示。2. 2. 定點(diǎn)數(shù)定點(diǎn)數(shù)定點(diǎn)數(shù)有decimal和numeric兩種類型，在MySQL中，numeric被實(shí)現(xiàn)為decimal，因此兩者具有相同的性質(zhì)。decimal類型通常寫為decimal(m, d)，用于存儲(chǔ)必須為確切精度的數(shù)值，占用m+2字節(jié)空間。其中：m：精度，表示總共的位數(shù)，取值范圍165，取0時(shí)會(huì)被設(shè)為默認(rèn)值，超出范圍會(huì)報(bào)錯(cuò)。m的默認(rèn)取值為10。d：標(biāo)度（dm），表示小數(shù)的位數(shù)，取值范圍030，超出范

6、圍會(huì)報(bào)錯(cuò)。d的默認(rèn)取值為0。3. 3. 數(shù)值顯示長(zhǎng)度數(shù)值顯示長(zhǎng)度創(chuàng)建表的時(shí)候，MySQL會(huì)為每種數(shù)值類型設(shè)定默認(rèn)的長(zhǎng)度，這個(gè)默認(rèn)長(zhǎng)度值是根據(jù)該類型所能表示取值范圍內(nèi)的最大數(shù)值位數(shù)確定的，有符號(hào)的整數(shù)類型tinyint、smallint和int在其取值范圍內(nèi)最大可能的數(shù)值位數(shù)分別為3、5、11，這就是它們的默認(rèn)長(zhǎng)度。【例【例3.13.1】定義類型設(shè)置的長(zhǎng)度與顯示長(zhǎng)度的關(guān)系。USE mydb;DROP TABLE IF EXISTS test;CREATE TABLE test(i1smallint,i2smallint(3),f1float,f2float(6,2),f3float(9,3),d

7、1decimal(9,3),c1 float(10,2) AS (f3*10),c2 decimal(10,2)AS (d1*10);INSERT INTO test(i1, i2, f1, f2,f3,d1)VALUES(12345, -12345, 1234567, 1234.567,123456.734,123456.734);SELECT * FROM test;運(yùn)行結(jié)果如圖3.2所示。說(shuō)明：說(shuō)明：（1）i1 smallint列：最大長(zhǎng)度為5位，123456就超過(guò)表達(dá)范圍。（2）i2 smallint(3)列：雖然定義3位顯示長(zhǎng)度，但-123456沒(méi)有超過(guò)表達(dá)范圍仍然可以存儲(chǔ)。（3）f

8、1 float列：最大有效位數(shù)6位，無(wú)法精確表達(dá)1234567，而只能將第7位四舍五入為123457存儲(chǔ)。（4）f2 float(6,2)列：精度6位、小數(shù)2位，整數(shù)部分只能表達(dá)4位，超過(guò)無(wú)法表達(dá)。小數(shù)后3位四舍五入。（5）f3 float(9,3)列：精度9位、小數(shù)3位，整數(shù)部分可表達(dá)6位，超過(guò)無(wú)法表達(dá)。即使精度定義超過(guò)9位，整數(shù)部分也不能超過(guò)6位，因?yàn)樽畲笥行粩?shù)6位。（6）計(jì)算列c1:因?yàn)槌^(guò)了float最大有效位數(shù)6位，數(shù)值計(jì)算結(jié)果就不準(zhǔn)確了。（7）計(jì)算列c2:定點(diǎn)數(shù)decimal沒(méi)有最大有效位數(shù)只有最大表達(dá)范圍，因?yàn)樵谄浔磉_(dá)范圍，計(jì)算結(jié)果準(zhǔn)確。目 錄目 錄目 錄0303位位 型型【例

9、【例3.23.2】表位（bit）型類型列存儲(chǔ)數(shù)據(jù)測(cè)試。（1）創(chuàng)建測(cè)試表，bit類型列可以顯式指定位長(zhǎng)，也可不指定。USE mydb;DROP TABLE IF EXISTS test;CREATE TABLE test(b1bit,b2bit(6);（3）當(dāng)插入數(shù)據(jù)的位數(shù)超出定義的位長(zhǎng)，在嚴(yán)格模式下將被拒絕并提示錯(cuò)誤，非嚴(yán)格模式時(shí)，當(dāng)超出位長(zhǎng)，從低位（右）起往高位取定義位長(zhǎng)的串，若超出部分（左）子串中含1，則存儲(chǔ)定義長(zhǎng)度的全1串；否則，存儲(chǔ)從低位起取到的定義位長(zhǎng)的串。（2）插入bit型數(shù)據(jù)其實(shí)就是存入二進(jìn)制的值，類似010110?？梢栽赟QL語(yǔ)句中直接寫二進(jìn)制串，也可以使用bx符號(hào)，其中x是使

10、用0和1編寫的二進(jìn)制值。INSERT INTO test(b1, b2) VALUES(0, 1);INSERT INTO test(b1, b2) VALUES(1, 001);INSERT INTO test(b1, b2) VALUES(0, b101);SELECT * FROM test;運(yùn)行結(jié)果如圖3.3所示。MySQL 8.0的日期時(shí)間類型較為豐富，能表達(dá)多種多樣不同格式的日期時(shí)間信息。日期時(shí)間類型及其主要屬性如表3.3所示。類類 型型 名名 格格 式式日日 期期 范范 圍圍存存 儲(chǔ)儲(chǔ) 空空 間間yearyearyyyy190121551字節(jié)timetimehh:mm:ss-83

11、8:59:59838:59:593字節(jié)datedateyyyy-mm-dd1000-01-019999-12-313字節(jié)datetimedatetimeyyyy-mm-dd hh:mm:ss1000-01-01 00:00:009999-12-31 23:59:598字節(jié)timestamptimestampyyyy-mm-dd hh:mm:ss1970-01-01 00:00:01 utc2038-01-19 03:14:07 utc4字節(jié)1. 1. 年（年（yearyear）year(4)是四位數(shù)格式的一年。MySQL以yyyy格式顯示年份值，但允許使用字符串或數(shù)字將值賦給數(shù)據(jù)庫(kù)表year類

12、型列。值顯示為1901到2155或0000。2. 2. 日期日期 時(shí)間時(shí)間 日期時(shí)間類型包含了date、datetime和timestamp這3種子類型。（1）date：僅表示一個(gè)日期，支持的范圍是“1000-01-01”到“9999-12-31”。（2）datetime：表示日期和時(shí)間的組合，支持的范圍是“1000-01-01 00:00:00.000000”到“9999-12-31 23:59:59.999999”。（3）timestamp：表示一個(gè)時(shí)間戳，能夠自動(dòng)存儲(chǔ)記錄修改的時(shí)間，范圍是“1970-01-01 00:00:01.000000 UTC”到“2038-01-19 03:14

13、:07.999999 UTC”。其中，UTC（Universal Time Coordinated）為世界標(biāo)準(zhǔn)時(shí)間，時(shí)間戳不能表示值1970-01-01 00:00:00，因?yàn)樗扔陂_(kāi)始的0秒，而值0保留用于表示00:00 -00-00 00:00:00（即0時(shí)間戳值），將它設(shè)為null默認(rèn)為當(dāng)前的日期和時(shí)間。3. 3. 時(shí)間（時(shí)間（timetime）（1）表示一段時(shí)間，范圍是-838:59:59.000000到838:59:59.000000。MySQL以hh:mm:ss.微秒格式顯示時(shí)間值，并允許使用字符串或數(shù)值兩種方式為時(shí)間列分配值。可以給出0到6長(zhǎng)度范圍內(nèi)的可選fsp值，以指定小數(shù)秒精

14、度。（2）如果開(kāi)啟了嚴(yán)格模式（STRICT_TRANS_TABLES），并且年月日中任何一個(gè)可為0，但不允許插入零日期（NO_ZERO_DATE）,0000-00-00除外。SET SQL_MODE = STRICT_TRANS_TABLES,NO_ZERO_DATE;（3）帶冒號(hào)字符串表示時(shí)間，最常見(jiàn)的是以標(biāo)準(zhǔn)的hh:mm:ss格式檢索和顯示時(shí)間值，對(duì)于較大的小時(shí)值可采用hhh:mm:ss或d hh:mm:ss格式。此外，MySQL還支持一些簡(jiǎn)短的“非嚴(yán)格”語(yǔ)法，如：hh:mm、d hh:mm、d hh或ss等（這里d表示日，取值范圍031）。（4）hhmmss格式的沒(méi)有冒號(hào)間隔符的字符串表

15、示時(shí)間，與此對(duì)應(yīng)的數(shù)值也可表達(dá)時(shí)間?！纠纠?.33.3】日期、時(shí)間類型插入、存儲(chǔ)和顯示。USE mydb;DROP TABLE IF EXISTS test;CREATE TABLE test(日期date,日期時(shí)間datetime,時(shí)間戳timestamp,時(shí)間time);INSERT INTO testVALUES(1983-09-25 07:25:16, 1983-9-25 7:25:16, 2013-11-3, 8:5:9);#（a）INSERT INTO testVALUES(19830925, 19830925072516, 20130113, 144908);#（b）INSER

16、T INTO testVALUES(99-9-25, 990925072516, 131103, 2 9:28);#（c）INSERT INTO tesVALUES(990925, 19990925072516, 19700101080001, 5 20);#（d）SELECT * FROM test;運(yùn)行結(jié)果如圖3.4所示。說(shuō)明：說(shuō)明：（a）用yyyy-mm-dd或yyyy-mm-dd hh:mm:ss標(biāo)準(zhǔn)字符串格式表示日期時(shí)間值，其中，yyyy表示年，mm表示月，dd表示日，hh表示時(shí)，mm表示分，ss表示秒。（b）用不含間隔符的字符串格式表示日期時(shí)間，形如yyyymmdd或yyyymmd

17、dhhmmss，其中的字符含義與標(biāo)準(zhǔn)字符串的相同，但這種寫法中的每個(gè)字符位置須嚴(yán)格對(duì)應(yīng)，不可再用單獨(dú)一位數(shù)表示小于10的值，例如，日期2013-11-3必須寫成20131103，簡(jiǎn)寫成2013113系統(tǒng)就無(wú)法識(shí)別，只能插入默認(rèn)“零”值。（c）用兩位年值的字符串格式表示日期時(shí)間，可以包含也可不含間隔符，例如，yy-mm-dd、yymmdd、yymmddhhmmss等，其中兩位年值的轉(zhuǎn)換規(guī)則同前“year”類型，0169對(duì)應(yīng)20012069，7099對(duì)應(yīng)19701999。（d）這是以數(shù)字格式表示的日期時(shí)間，如果一個(gè)數(shù)字是8或14位長(zhǎng)，則假定為yyyymmdd或yyyymmddhhmmss格式，前4

18、位數(shù)表示年；如果數(shù)字是6或12位長(zhǎng)，則假定為yymmdd或yymmddhhmmss格式，前2位數(shù)表示年。其它數(shù)字被解釋為仿佛用零填充到了最近的長(zhǎng)度。目 錄目 錄目 錄0101文本字符串型文本字符串型1. 常用的文本字符串型2. 字符和文本存儲(chǔ)舉例MySQL8中根據(jù)保存的文本長(zhǎng)度用途不同，可以選擇不同的文本數(shù)據(jù)類型。表3.4列出了MySQL文本字符串?dāng)?shù)據(jù)類型及其屬性。類類 型型 名名 稱稱存存 儲(chǔ)儲(chǔ) 空空 間間（實(shí)際字符數(shù)（實(shí)際字符數(shù)L L）最最 大大 字字 符符 數(shù)數(shù)char(m)m255varchar(m)L+165535tinytext(m)L+1255text(m)L+265535med

19、iumtext(m)L+316777215longtext(m)L+44294967295（4GB）enum1（255個(gè)枚舉）2（65535個(gè)枚舉）65535set1、2、3、4或864個(gè)成員1. 1. 常用的文本字符串型常用的文本字符串型最常用的文本字符串型包括char、varchar及各種text類型。（1）固定長(zhǎng)度字符串型：charchar通常定義成char(m)的形式，m是字符串長(zhǎng)，m255，實(shí)際占用的存儲(chǔ)空間為m*n，n為一個(gè)字符在字符集中的字節(jié)數(shù)。當(dāng)保存時(shí)字符個(gè)數(shù)L不足m，則在右側(cè)填充空格，以達(dá)到指定的長(zhǎng)度m。當(dāng)檢索到char(m)值時(shí)，尾部的空格將被刪除。SQL語(yǔ)句定義表時(shí)，可不

20、設(shè)定長(zhǎng)度，默認(rèn)m值為1。（2）可變長(zhǎng)度字符串型：varcharvarchar通常定義成varchar(m)存儲(chǔ)，m表示定義的字符串長(zhǎng)度，m65535。當(dāng)保存時(shí)字符個(gè)數(shù)L不足m，實(shí)際存儲(chǔ)的字符串為L(zhǎng)個(gè)字符和一個(gè)字符串結(jié)束符。varchar在值保存和檢索時(shí)尾部的空格仍保留。SQL語(yǔ)句定義表時(shí)，必須用(m)指定長(zhǎng)度。（3）變長(zhǎng)度文本型text也是變長(zhǎng)字符串類型，其存儲(chǔ)需求取決于字符串值的實(shí)際長(zhǎng)度而不是最大可能尺寸。text類型又分為4種（tinytext、text、mediumtext和 longtext），不同種類的text類型的存儲(chǔ)空間和最大數(shù)據(jù)長(zhǎng)度不同。2. 2. 字符和文本存儲(chǔ)舉例字符和文本

21、存儲(chǔ)舉例【例【例3.43.4】創(chuàng)建表，包含3種類型的文本字符串列，然后對(duì)其進(jìn)行測(cè)試。1）創(chuàng)建測(cè)試表，包含3種類型的文本字符串列。USE mydb;DROP TABLE IF EXISTS test;CREATE TABLE test(c1char(8),c2varchar(8),c3text);2）插入帶空格的字符串。INSERT INTO testVALUES(hello , hello , hello );3）插入數(shù)值、數(shù)字、中英文等各種類型數(shù)據(jù)。DELETE FROM test;INSERT INTO testVALUES(3.14, 12345678, 2.718281828459);

22、#（a）SET SESSION sql_mode = ;INSERT INTO testVALUES(3.1415926, 1234567890, 2.718E02);#（b）INSERT INTO testVALUES(Hi中國(guó)夢(mèng)我的夢(mèng), 2022北京冬奧, Hello!世界！);#（c）SELECT * FROM test;運(yùn)行結(jié)果如圖3.5所示。說(shuō)明：說(shuō)明：（a）對(duì)于不超過(guò)類型長(zhǎng)度的數(shù)值，MySQL將其以原樣字符串形式存儲(chǔ)。（b）對(duì)于超過(guò)類型長(zhǎng)度的數(shù)值，在嚴(yán)格模式下插入會(huì)出錯(cuò)；非嚴(yán)格模式下，MySQL將其截?cái)嘀炼x長(zhǎng)度存儲(chǔ)。以科學(xué)記數(shù)法表示的數(shù)值，MySQL會(huì)自動(dòng)計(jì)算出其值并保存為字符串

23、形式。（c）MySQL 8.0中每個(gè)中文漢字與單個(gè)英文字母或數(shù)字一樣，都只占一個(gè)字符長(zhǎng)度。目 錄目 錄目 錄0202字符集編碼字符集編碼1. MySQL8字符集2. MySQL8字符集系統(tǒng)變量1. MySQL81. MySQL8字符集字符集在MySQL8中，不同的編碼方案對(duì)應(yīng)不同的紙飛機(jī)，比較常用字符集如下：（1）ascii字符集：基于羅馬字母表的一套ASCII碼字符集，它采用1個(gè)字節(jié)的低7位表示字符，高位始終為0。（2）latin1字符集：相對(duì)于ASCII碼字符集做了擴(kuò)展，仍然使用一個(gè)字節(jié)表示字符，但啟用了高位，擴(kuò)展了字符集的表示范圍。（3）gb2312字符集：是GB2312-80中文編碼的

24、字符集。（4）gbk字符集：支持中文，字符有一字節(jié)編碼和兩字節(jié)編碼兩種方式。（5）utf8字符集：采用13個(gè)字節(jié)表示UTF-8字符。utf8字符集表示Unicode中的基本多文種平面（BMP），任何不在BMP的Unicode字符都無(wú)法使用utf8字符集存儲(chǔ)，包括Emoji表情和很多不常用的漢字以及任何新增的Unicode字符等。而Emoji是一種特殊的Unicode編碼，常見(jiàn)于iOS和Android手機(jī)上（如微信程序）。（6）utf8mb4字符集：MySQL 8默認(rèn)字符集為utf8mb4，采用14個(gè)字節(jié)表示UTF-8編碼的所有字符，還擴(kuò)展了其他字符?？赏ㄟ^(guò)下列語(yǔ)句：SHOW CHARACTER

25、 SET;【例【例3.53.5】創(chuàng)建表，設(shè)置4個(gè)列分別采用4種不同語(yǔ)言文字的字符集編碼，然后對(duì)其進(jìn)行操作。1）創(chuàng)建測(cè)試表。USE mydb;DROP TABLE IF EXISTS test;CREATE TABLE test(idint,c1char(10) CHARACTER SET utf8,#通用編碼c2char(10) CHARACTER SET gb2312,#中文編碼c3char(10) CHARACTER SET sjis,#日文編碼c4char(10) CHARACTER SET euckr#韓文編碼);說(shuō)明：說(shuō)明：創(chuàng)建表時(shí)如果不顯式指定字符集，MySQL會(huì)采用默認(rèn)的字符集。

26、MySQL 5及更早前版本默認(rèn)字符集是utf8，從MySQL 8.0開(kāi)始，默認(rèn)采用增強(qiáng)的utf8mb4編碼，它是對(duì)utf8的擴(kuò)充，為每個(gè)字符分配4字節(jié)長(zhǎng)度，可以表示Unicode中一些不常用的字符。2）輸入字符集測(cè)試。INSERT INTO test(id, c1, c2)VALUES(1, 駿馬奔騰, 駿馬奔騰);INSERT INTO test(id, c1, c3)VALUES(2, 駿馬, 駿馬);INSERT INTO test(id, c1, c4)VALUES(3, , );SELECT * FROM test;INSERT INTO test(id, c3) VALUES(4,

27、 駿馬奔騰);INSERT INTO test(id, c4) VALUES(5, 駿馬奔騰);INSERT INTO test(id, c2) VALUES(6, 駿馬);INSERT INTO test(id, c4) VALUES(7, 馬乘);INSERT INTO test(id, c2) VALUES(8, );INSERT INTO test(id, c3) VALUES(9, );SELECT * FROM test;顯示結(jié)果如圖3.6所示。3）混合文字測(cè)試。DELETE FROM test;INSERT INTO test(id, c1, c2)VALUES(1, 駿馬, 駿

28、馬);#中日混合INSERT INTO test(id, c1, c4)VALUES(2, , );#韓日混合INSERT INTO test(id, c1, c4)VALUES(3, 駿馬, 駿馬);#韓中混合SELECT * FROM test;運(yùn)行結(jié)果如圖3.7所示。說(shuō)明說(shuō)明: : （1）c1列采用utf8編碼，中、日、韓三種文字皆可輸入，當(dāng)然包括英文等各種文字和各種符號(hào)。（2）c2列采用GB2312編碼，只能插入GB2312編碼中包含的6763個(gè)漢字和字符，不包含在其中的漢字是不能進(jìn)入的。而康熙字典共收錄漢字47035個(gè)，絕大多數(shù)漢字無(wú)法保存到這種編碼列。如果需要數(shù)據(jù)庫(kù)保存更多的漢字，

29、需要采用GBK、GB18030等編碼，或者使用Unicode編碼。因?yàn)橹形腉B2312編碼基本集中本身就有日文平假名和片假名的編碼字符區(qū)，如圖3.8所示。（3）c3列采用sjis編碼，只能輸入日文編碼字符，不能輸入中文和韓文（注意：該列也不能存放中文輸入法輸入的日文字符）。日文編碼中部分中文字符（繁體中文）是日文的組成部分，它們當(dāng)然可與日文一起混合插入日文字符集編碼的列中，因?yàn)榇藭r(shí)的這些中文字符本身就是日文。（4）c4列采用euckr編碼，可以存放韓文編碼字符。韓文編碼集中也有部分中文字符（繁體中文）和日文字符，這部分中日文也可與韓文一起混合錄。2. MySQL82. MySQL8字符集系統(tǒng)變

30、量字符集系統(tǒng)變量在MySQL 8.0中，從服務(wù)器、數(shù)據(jù)庫(kù)、表和表列等均可指定字符集，并通過(guò)系統(tǒng)變量指定默認(rèn)字符集。從客戶端輸入命令到最終顯示查詢結(jié)果也通過(guò)不同系統(tǒng)變量對(duì)應(yīng)字符集，如圖3.9所示。目 錄目 錄目 錄0303字符排序規(guī)則字符排序規(guī)則1. 規(guī)則名和排序集2. 默認(rèn)字符集及排序規(guī)則3. 查看字符排序規(guī)則1. 1. 規(guī)則名和排序集規(guī)則名和排序集字符排序又稱字符序，由collation指定排序的規(guī)則名，MySQL中眾多的排序規(guī)則名可以歸納為以下兩大類排序集：（1）字符集_語(yǔ)言/other_ci/cs這是默認(rèn)的排序集，當(dāng)字符無(wú)須區(qū)分大小寫時(shí)采用該集合中的規(guī)則，規(guī)則命名以其對(duì)應(yīng)的字符集作為前綴

31、，例如：字符序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ī)則，collation用于指定數(shù)據(jù)集如何排序以及字符串的排序規(guī)則。2. 2. 默認(rèn)字符集及排序規(guī)則默認(rèn)字符集及排序規(guī)則兩個(gè)不同的字符集不能有相同的排序規(guī)則?！纠纠?.63.6】字符集的排序規(guī)則。1）大小寫不敏感排序規(guī)則設(shè)置和查詢。USE mydb;DROP TABLE IF EXISTS mytab;CREATE TABLE mytab( t

32、1 int, t2 char(20) CHARACTER SET gbk, t3 float(6,2);SELECT column_name,character_set_name,collation_name FROM information_schema.columns WHERE table_name = mytab;#（a）INSERT INTO mytab (t1,t2,t3) VALUES (3,a,3.45), (4,b,3.10), (5,A,10.23);SELECT * FROM mytab WHERE t2 = a;#（b）SELECT * FROM mytab ORDER

33、 BY t2;#（c）運(yùn)行結(jié)果如圖3.10所示。 說(shuō)明：說(shuō)明：（a）雖然t2列僅僅設(shè)置字符集（gbk），但同時(shí)也就設(shè)置了對(duì)應(yīng)的默認(rèn)排序規(guī)則（gbk_chinese_cs）。通過(guò)查詢information_schema.columns（系統(tǒng)數(shù)據(jù)庫(kù)表）可以獲取當(dāng)前t2列的字符排序規(guī)則對(duì)應(yīng)不區(qū)分大小寫。（b）由于mytab表t2列排序規(guī)則不區(qū)分大小寫，所以該語(yǔ)句將t2列值為“A”的記錄也顯示出來(lái)。（c）雖然ASCII碼Aa，但由于mytab表t2列的排序規(guī)則不區(qū)分大小寫，誰(shuí)排列在前取決于查詢時(shí)先遇到的行，這里它把“a”排在前面。2）大小寫敏感排序規(guī)則設(shè)置和查詢。USE mydb;ALTER TABL

34、E mytab MODIFY t2 char(20) CHARACTER SET gbk COLLATE gbk_bin; #（a）SELECT * FROM mytab WHERE t2 = a;#（b）SELECT * FROM mytab ORDER BY t2;#（c）運(yùn)行結(jié)果如圖3.11所示。 說(shuō)明：說(shuō)明：（a）修改設(shè)置t2列為字符集（gbk）對(duì)應(yīng)區(qū)分大小寫排序規(guī)則（gbk_bin）。（b）由于mytab表t2列排序規(guī)則區(qū)分大小寫，所以該語(yǔ)句僅顯示t2列值為a的記錄。（c）由于ASCII碼大寫字符小于小寫字符，而ORDER BY t2默認(rèn)從小到大排列，故這里把字符為A記錄排在前面，然

35、后是字符為a和“b”記錄。3. 3. 查看字符排序規(guī)則查看字符排序規(guī)則（1）查看所有的字符排序規(guī)則，用下面語(yǔ)句：SHOW COLLATION;（2）查看當(dāng)前字符集CHARACTER_SET_CONNECT、CHARACTER_SET_DATABASE、CHARACTER_SET_SERVER對(duì)應(yīng)的排序規(guī)則環(huán)境變量，用下面語(yǔ)句：SHOW VARIABLES LIKE COLLATION_%;運(yùn)行結(jié)果如圖3.12所示。（3）查詢字符集匹配的所有排序規(guī)則，用下面語(yǔ)句：SHOW collation LIKE 字符集名%例如，查詢以u(píng)tf8mb4打頭和_cs結(jié)尾的排序規(guī)則：show collation

36、like utf8mb4%_cs; 上述語(yǔ)句相對(duì)于條件查詢information_schema系統(tǒng)數(shù)據(jù)庫(kù)collations表記錄：select * from information_schema.collations where collation_name like utf8%;（4）查看指定表列的排序規(guī)則，例如：SHOW FULL COLUMNS FROM mytab;可看到表mytab的完整結(jié)構(gòu)屬性及列上定義的排序規(guī)則，如圖3.13所示。目 錄目 錄目 錄0404二進(jìn)制字符串類型二進(jìn)制字符串類型1. 二進(jìn)制字符串類型介紹2. 二進(jìn)制字符串的性質(zhì)3. 二進(jìn)制字符串存儲(chǔ)不同類型1. 1.

37、二進(jìn)制字符串類型介紹二進(jìn)制字符串類型介紹二進(jìn)制字符串類型一般用于存儲(chǔ)圖像、聲音、視頻等內(nèi)容，MySQL中可以定義的二進(jìn)制字符串類型如表3.5所示。類類 型型 名名 稱稱存存 儲(chǔ)儲(chǔ) 需需 求求（實(shí)際字符數(shù)（實(shí)際字符數(shù)L L）最最 大大 字字 符符 數(shù)數(shù)（m m）binary(m)m255varbinary(m)m+165535tinyblob(m)L+1255blob(m)L+265535mediumblob(m)L+316777215longblob(m)L+44294967295或4GB說(shuō)明：說(shuō)明：（1）binary和varbinary類型類似于char和varchar，不同點(diǎn)在于它們存儲(chǔ)的

38、是二進(jìn)制字節(jié)形式的字符串。（2）blob是一個(gè)二進(jìn)制大對(duì)象，用來(lái)存儲(chǔ)可變數(shù)量的數(shù)據(jù)，它又分tinyblob、blob、mediumblob和longblob幾種，每種可容納值的最大長(zhǎng)度不同。2. 2. 二進(jìn)制字符串的性質(zhì)二進(jìn)制字符串的性質(zhì)【例【例3.73.7】創(chuàng)建表，測(cè)試二進(jìn)制字符串?dāng)?shù)據(jù)的性質(zhì)。1）創(chuàng)建測(cè)試表。USE mydb;DROP TABLE IF EXISTS test;CREATE TABLE test(b1binary(8),b2varbinary(8),b3blob);2）插入整數(shù)數(shù)值。INSERT INTO test VALUES(13, 13, 13);SELECT BIN(

39、b1+0), BIN(b2+0), BIN(b3+0) FROM test;#（a）SELECT LENGTH(b1), LENGTH(b2), LENGTH(b3) FROM test;#（b）說(shuō)明：說(shuō)明：（a）專用于查看對(duì)應(yīng)列內(nèi)容的二進(jìn)制串，可通過(guò)Navicat查看表中數(shù)據(jù)，運(yùn)行結(jié)果如圖3.14所示。 其中，blob數(shù)據(jù)不是直接可見(jiàn)的，只顯示了類型和字節(jié)數(shù)，13的二進(jìn)制1101占一個(gè)字節(jié)，blob在此基礎(chǔ)上加1，即2字節(jié)存儲(chǔ)。所有二進(jìn)制字符串型數(shù)據(jù)都無(wú)法直接通過(guò)Navicat表查看器更改。（b）用于查看二進(jìn)制字符串列內(nèi)容占用的字節(jié)長(zhǎng)，運(yùn)行結(jié)果如圖3.15所示。3. 3. 二進(jìn)制字符串存儲(chǔ)不

40、同類型二進(jìn)制字符串存儲(chǔ)不同類型【例【例3.73.7續(xù)】續(xù)】采用上例test表，測(cè)試二進(jìn)制字符串型對(duì)各種不同類型數(shù)據(jù)的存儲(chǔ)性質(zhì)。1）插入字符串、數(shù)值。USE mydb;DELETE FROM test;INSERT INTO test VALUES(hello, hello, hello);#（a）INSERT INTO test VALUES(2022, 中國(guó), 2022中國(guó)夢(mèng));#（b）INSERT INTO test VALUES(mysql8, mysql , 123mysql);#（c）INSERT INTO test VALUES(3.1416, 2.718e03, 3.141592

41、6);#（d）SELECT LENGTH(b1), LENGTH(b2), LENGTH(b3) FROM test;#（e）說(shuō)明：說(shuō)明：（a）對(duì)于字符型數(shù)據(jù)，binary依舊是補(bǔ)足至定義長(zhǎng)度，varbinary和blob按照實(shí)際字節(jié)數(shù)（一個(gè)字符占一個(gè)字節(jié)）存儲(chǔ)。（b）儲(chǔ)存一個(gè)漢字要占用16個(gè)二進(jìn)制位，即2字節(jié)，varbinary和blob在其上加1存儲(chǔ)，故一個(gè)漢字要用3個(gè)字節(jié)存儲(chǔ)，這樣“2022中國(guó)夢(mèng)”一共占用43313個(gè)字節(jié)。（c）英文字符、數(shù)字、空格也都是占用一個(gè)字節(jié)。（d）若直接插入浮點(diǎn)數(shù)，MySQL將其每個(gè)數(shù)字位作為一個(gè)字符看待，若數(shù)值是以科學(xué)記數(shù)法格式給出，MySQL會(huì)先計(jì)算其值，

42、然后再以同樣規(guī)則存儲(chǔ)。test表記錄如圖3.16所示。（e）查詢得到test表中各數(shù)據(jù)的長(zhǎng)度（字節(jié)）如圖3.17所示。 2）對(duì)于長(zhǎng)度超出定義的情形，binary和varbinary類型都會(huì)報(bào)錯(cuò)，拒絕插入。INSERT INTO test(b1) VALUES(3.1415926);#（a）INSERT INTO test(b2) VALUES(中國(guó)夢(mèng));#（b）INSERT INTO test(b2) VALUES(2022beijing);#（c）執(zhí)行結(jié)果出錯(cuò)，出錯(cuò)信息分別如圖3.18（a）、（b）、（c）所示。 目 錄目 錄目 錄0101枚舉類型和集合類型枚舉類型和集合類型枚舉類型：枚舉類

43、型：enumenum（1）定義enum(值1,值2,值3.,值n)定義需要enum需要設(shè)置成員表，成員為字符串（尾部空格將自動(dòng)被刪除），即使成員是數(shù)字，也必須是加引號(hào)的字符串。成員之間用“,”分隔。（2）存儲(chǔ)enum類型存儲(chǔ)不是成員的字符串，而是成員的索引編號(hào)，定義1255個(gè)成員的枚舉索引編號(hào)需要1個(gè)字節(jié)存儲(chǔ)；對(duì)于25665535個(gè)成員，索引編號(hào)需要2個(gè)字節(jié)存儲(chǔ)。例如，“專業(yè)”枚舉定義enum(計(jì)算機(jī), 通信工程, 人工智能)，該列可以取的值和對(duì)應(yīng)的索引編號(hào)如表3.6所示。成員成員索引編號(hào)索引編號(hào)NULLNULL“0計(jì)算機(jī)1通信工程2人工智能3目 錄目 錄目 錄0202集合類型集合類型集合類型

44、：集合類型：setset（1）定義SET(值1,值2,值3.,值n)定義set類型基本形式與enum類型一樣。set類型成員不能為空，最小需要一個(gè)成員，最多包含64個(gè)成員。實(shí)際內(nèi)容這些成員構(gòu)成的組合，18成員的集合占1個(gè)字節(jié)，916成員的集合占2個(gè)字節(jié)，1724成員的集合占3個(gè)字節(jié)，2532成員的集合占4個(gè)字節(jié)，3364成員的集合占8個(gè)字節(jié)。（2）存儲(chǔ)set類型存儲(chǔ)不是成員的字符串，而是二進(jìn)制位信息。例如：“興趣”集合定義set(唱歌, 跳舞, 智力游戲, 足球運(yùn)動(dòng), 書法)?！芭d趣”成員和存儲(chǔ)值表示如表3.7所示。成員成員二進(jìn)制值二進(jìn)制值十進(jìn)制值十進(jìn)制值 唱歌唱歌 11 跳舞跳舞 102 智

45、力游戲智力游戲 1004 足球運(yùn)動(dòng)足球運(yùn)動(dòng) 10008 書法書法 1000016【例【例3.83.8】enum類型與set類型的應(yīng)用。1）創(chuàng)建enum類型與set類型測(cè)試表。USE mydb;DROP TABLE IF EXISTS xs;CREATE TABLE xs(姓名char(4),專業(yè)enum(計(jì)算機(jī), 通信工程, 人工智能), 總學(xué)分int,興趣set(唱歌, 跳舞, 智力游戲, 足球運(yùn)動(dòng), 書法);2）插入記錄。INSERT INTO xsVALUES(劉文, 計(jì)算機(jī),32,智力游戲 );#（a）INSERT INTO xsVALUES(周和進(jìn), 通信工程,30,足球運(yùn)動(dòng),書法)

46、;#（b）INSERT INTO xsVALUES(劉一龍, 2, 32,足球運(yùn)動(dòng),唱歌);#（c）INSERT INTO xsVALUES(顧紅, 1, 34,6);#（d）說(shuō)明：說(shuō)明：（a）刪除智力游戲 字符串后的空格后再匹配興趣成員。（b）足球運(yùn)動(dòng),書法包含兩個(gè)興趣成員，“，”之間不能包含空格。（c）（enum）專業(yè)枚舉值通信工程采用索引編號(hào)。足球運(yùn)動(dòng),唱歌興趣沒(méi)有按照定義順序，但仍然按照定義時(shí)的先后順序保存。（d）（set）興趣值跳舞和智力游戲采用成員對(duì)應(yīng)十進(jìn)制值相加（2+4=6）表示。6不能采用二進(jìn)制表示（0b110）。3）查詢記錄。SELECT * FROM xs;#（a）SELE

47、CT * FROM xs WHERE 專業(yè)=計(jì)算機(jī);#（b）SELECT * FROM xs WHERE 專業(yè)=1;#（b）SELECT * FROM xs WHERE 興趣=唱歌,足球運(yùn)動(dòng);#（c）SELECT * FROM xs WHERE 興趣=9;#（c）SELECT * FROM xs WHERE 興趣=足球運(yùn)動(dòng),唱歌;#（d）運(yùn)行結(jié)果如圖3.19所示。 說(shuō)明：說(shuō)明：（a）顯示所有記錄，存儲(chǔ)的是數(shù)字，顯示的是成員字符串。（b）enum類型查詢內(nèi)容表達(dá)既可以是成員字符串，又可以是數(shù)字。（c）set類型查詢內(nèi)容表達(dá)可以是按定義順序包含的成員字符串，或者成員對(duì)應(yīng)十進(jìn)制值相加，不能采用二進(jìn)制

48、表示。（d）set類型查詢內(nèi)容表達(dá)不是按定義順序包含的成員字符串，找不到記錄。4）更新記錄。UPDATE xs SET 興趣=(興趣|3) WHERE 專業(yè)=通信工程;#（a）SELECT * FROM xs;#（a）UPDATE xs SET 興趣=(興趣& 0b11110) WHERE 專業(yè)=通信工程;#（b）SELECT * FROM xs;#（b）運(yùn)行結(jié)果如圖3.20所示。說(shuō)明：說(shuō)明：（a）set類型加入一個(gè)成員，只要將原來(lái)的值位或（|）該成員的數(shù)字。（b）set類型減去一個(gè)成員，只要將原來(lái)的值位與（&）該成員二進(jìn)制為0其他成員為1（或者對(duì)應(yīng)的十進(jìn)制數(shù)）。目 錄目 錄目

49、 錄0101JSONJSON數(shù)據(jù)類型數(shù)據(jù)類型1. JSON對(duì)象2. JSON數(shù)組1. JSON1. JSON對(duì)象對(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,.)其中，鍵與值必須成對(duì)出現(xiàn)，且不能為空。一旦出現(xiàn)鍵名為空或參數(shù)總數(shù)為奇數(shù)的情形，系統(tǒng)就會(huì)報(bào)錯(cuò)。創(chuàng)建JSON對(duì)象和存儲(chǔ)遵循如下一些基本的規(guī)則：（1）鍵名（無(wú)論中英文）都必須以雙引號(hào)（）括起來(lái)。（2）鍵值是字符串型（無(wú)論中英文）必須加雙引號(hào)（）或單

50、引號(hào)（）。（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ù)獲得，不同函數(shù)返回的日期時(shí)間字符串的格式不同，均以字符串的形式存儲(chǔ)到JSON對(duì)象中。【例【例3.93.9】JSON對(duì)象的創(chuàng)建和存儲(chǔ)。1）首先創(chuàng)建一個(gè)含JSON類型列的測(cè)試表。USE mydb;DROP TABLE IF EXISTS test;CREATE TABLE test(jjson);2）向表中插入一組JSON對(duì)象。INSERT

51、 INTO test VALUES(JSON_OBJECT(帳號(hào),b02020622,金額,1758.82,時(shí)間,NOW();INSERT INTO test VALUES(帳號(hào):y06111577,金額:63888,時(shí)間:2020-05-2814:01:21);INSERT INTO test VALUES(JSON_OBJECT(Accounts,k08439558,amount ,456.5,CTime,CURTIME();SELECT * FROM test;運(yùn)行結(jié)果如圖3.21所示。2. JSON2. JSON數(shù)組數(shù)組當(dāng)需要同時(shí)存儲(chǔ)很多JSON對(duì)象或者標(biāo)量值時(shí)，可將它們集中在一起構(gòu)造

52、出一個(gè)數(shù)組數(shù)據(jù)結(jié)構(gòu)來(lái)統(tǒng)一管理。JSON數(shù)組包含多個(gè)值，這些值由逗號(hào)分隔：JSON_ARRAY(值,.)JSON數(shù)組元素顯示包含在“”和“”中：鍵1: 值1,.,.【例【例3.93.9續(xù)】續(xù)】插入一些不同的標(biāo)量值與JSON對(duì)象一起作為JSON數(shù)組的元素，并嘗試嵌套的結(jié)構(gòu)，插入上例為j（JSON類型）列test表。向插入測(cè)試表JSON數(shù)組的元素：USE mydb;DELETE FROM test;INSERT INTO testVALUES( JSON_ARRAY ( 1B0601, JSON_OBJECT(商品,碭山梨10斤箱裝大果), 19.90, 2020-07-29 12:18:29.69

53、8 );INSERT INTO testVALUES( JSON_ARRAY( JSON_OBJECT(類別, JSON_ARRAY(3C, 海參), JSON_ARRAY(A3, 大連凱洋世界海鮮有限公司);SELECT * FROM test;運(yùn)行結(jié)果如圖3.22所示。目 錄目 錄目 錄0202空間數(shù)據(jù)類型空間數(shù)據(jù)類型1. 基于位置信息信息轉(zhuǎn)化（地理信息系統(tǒng)）2. 幾何值類型介紹3. 幾何值類型表的創(chuàng)建、列插入和查詢4. 集合類5. 空間數(shù)據(jù)內(nèi)部格式（WKB）1. 1. 基于位置信息信息轉(zhuǎn)化（地理信息系統(tǒng)）基于位置信息信息轉(zhuǎn)化（地理信息系統(tǒng)）（1 1）平面直角坐標(biāo)系）平面直角坐標(biāo)系二維平面

54、其中的點(diǎn)表示為P（x，y），其中x是點(diǎn)P到x軸上的原點(diǎn)（0,0）的距離，而y是 與y軸上的原點(diǎn)（0,0）的距離。 因此，如果知道一個(gè)點(diǎn)的x，y，則可以將其定位在坐標(biāo)面上。計(jì)算P點(diǎn)到原點(diǎn)的距離為x和y平方和的平方根。如圖3.23所示。（2）球面直角坐標(biāo)系球面遵循大地參考系來(lái)繪制表面上的位置，大地參考系P（x，y）基于經(jīng)度和緯度值。地球上某個(gè)位置（點(diǎn)）的緯度是它與赤道的度數(shù)（沿地球旋轉(zhuǎn)軸測(cè)量時(shí)）。 地球上某個(gè)位置的經(jīng)度是距本初子午線的度數(shù)（沿赤道測(cè)量時(shí)）。 緯度和經(jīng)度都是從地球中心測(cè)量的。緯度的最大值為90，最小值為-90。 同樣，經(jīng)度的最大值為180，而最小值為-180。如圖3.24所示。表面上

55、的點(diǎn)（P（x，y）可以基于其所在的坐標(biāo)系而具有不同的含義，需要一個(gè)與點(diǎn)簽名關(guān)聯(lián)的坐標(biāo)參考系統(tǒng)標(biāo)識(shí)符（即SRID），SRID是唯一整數(shù)。MySQL數(shù)據(jù)庫(kù)附帶了5000多個(gè)這樣的坐標(biāo)系，可以在系統(tǒng)數(shù)據(jù)庫(kù)information_schema中使用以下查詢：SELECT * FROM ST_SPATIAL_REFERENCE_SYSTEMS;其中，SRS_NAME是空間參考系統(tǒng)名稱，SRS_ID是空間參考系統(tǒng)的ID標(biāo)識(shí)。查詢指定SRS_ID的空間參考系統(tǒng)信息，SQL語(yǔ)句如下：SELECT * FROM information_schema.ST_SPATIAL_REFERENCE_SYSTEMS WH

56、ERE SRS_ID = 4326G;2. 2. 幾何值類型介紹幾何值類型介紹單一幾何值類型是一類比較簡(jiǎn)單的空間數(shù)據(jù)類型，包括：POINT（點(diǎn)）、LINESTRING（線）、POLYGON（多邊形）、GEOMETRY，其中GEOMETRY是通用的幾何類型，可以存儲(chǔ)任何點(diǎn)、線和多邊形值。（1 1）單一幾何類）單一幾何類lPOINT: 點(diǎn)，有一個(gè)坐標(biāo)值，沒(méi)有長(zhǎng)度、面積、邊界。點(diǎn)用經(jīng)緯度表示，經(jīng)度（longitude）在前，緯度（latitude）在后，用空格分隔。例如，表示一個(gè)點(diǎn)：POINT(15 20)。lLINESTRING: 線，由一系列點(diǎn)連接而成。如果線從頭至尾沒(méi)有交叉，那就是簡(jiǎn)單的（si

57、mple）；如果起點(diǎn)和終點(diǎn)重疊，那就是封閉的（closed）。點(diǎn)與點(diǎn)之間用逗號(hào)分隔，一個(gè)點(diǎn)中的經(jīng)緯度用空格分隔，與POINT格式一致。例如，表示一條線：LINESTRING(0 0, 10 10, 20 25, 50 60)，其中，點(diǎn)坐標(biāo)對(duì)由逗號(hào)分隔。lPOLYGON: 多邊形，可以是一個(gè)實(shí)心平面形，即沒(méi)有內(nèi)部邊界，也可以有空洞，類似紐扣。由一個(gè)表示外部邊界的LineString和0個(gè)或多個(gè)表示內(nèi)部邊界的LineString組成，最簡(jiǎn)單的就是只有一個(gè)外邊界的情況。例如，表示一個(gè)外環(huán)和一個(gè)內(nèi)環(huán)的多邊形：POLYGON(0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5)。（2）基類：GEOMETRYGEOMETRY是所有擴(kuò)展中類型的基類，POINT,LINESTRING,POLYGON等都是GEOMETRY的子類。GEOMETRY有一些屬性，這些屬性是所有其他幾何類的共有屬性。ltype: 幾何值類型。如POINT, LINESTRING, .。lsrid: 幾何對(duì)象的坐標(biāo)空間的空間坐標(biāo)系統(tǒng)。lcoordinates: 坐標(biāo)值。linterior, boundary, exterior: i