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

1、MySQL+實用教程（第4版）MySQL 8.0包含豐富的數(shù)據(jù)類型，可以方便處理各種數(shù)據(jù)。系統(tǒng)數(shù)據(jù)類型如表3.1所示。數(shù)數(shù) 據(jù)據(jù) 類類 型型符符 號號 標(biāo)標(biāo) 識識整數(shù)類型tinyint, smallint, mediumint, int, bigint向下兼容：bool(boolean)實數(shù)類型float, double, decimal, numeric日期與時間類型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ù)類型可表示有符號或無符號的整數(shù)數(shù)值，其最大值和最小值（含符號位）確定了默認的顯示位數(shù)。表3.2列出了MySQL中所有的整數(shù)類型主要屬性。數(shù)據(jù)類型數(shù)據(jù)類型字節(jié)數(shù)字節(jié)數(shù)有符號范圍有符號范圍無符號范圍無符號范圍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ù)類型”定義的都是有符號的，加上u

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

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

6、圍會報錯。d的默認取值為0。3. 3. 數(shù)值顯示長度數(shù)值顯示長度創(chuàng)建表的時候，MySQL會為每種數(shù)值類型設(shè)定默認的長度，這個默認長度值是根據(jù)該類型所能表示取值范圍內(nèi)的最大數(shù)值位數(shù)確定的，有符號的整數(shù)類型tinyint、smallint和int在其取值范圍內(nèi)最大可能的數(shù)值位數(shù)分別為3、5、11，這就是它們的默認長度?！纠纠?.13.1】定義類型設(shè)置的長度與顯示長度的關(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;運行結(jié)果如圖3.2所示。說明：說明：（1）i1 smallint列：最大長度為5位，123456就超過表達范圍。（2）i2 smallint(3)列：雖然定義3位顯示長度，但-123456沒有超過表達范圍仍然可以存儲。（3）f

8、1 float列：最大有效位數(shù)6位，無法精確表達1234567，而只能將第7位四舍五入為123457存儲。（4）f2 float(6,2)列：精度6位、小數(shù)2位，整數(shù)部分只能表達4位，超過無法表達。小數(shù)后3位四舍五入。（5）f3 float(9,3)列：精度9位、小數(shù)3位，整數(shù)部分可表達6位，超過無法表達。即使精度定義超過9位，整數(shù)部分也不能超過6位，因為最大有效位數(shù)6位。（6）計算列c1:因為超過了float最大有效位數(shù)6位，數(shù)值計算結(jié)果就不準(zhǔn)確了。（7）計算列c2:定點數(shù)decimal沒有最大有效位數(shù)只有最大表達范圍，因為在其表達范圍，計算結(jié)果準(zhǔn)確。目 錄目 錄目 錄0303位位 型型【例

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

10、用0和1編寫的二進制值。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;運行結(jié)果如圖3.3所示。MySQL 8.0的日期時間類型較為豐富，能表達多種多樣不同格式的日期時間信息。日期時間類型及其主要屬性如表3.3所示。類類 型型 名名 格格 式式日日 期期 范范 圍圍存存 儲儲 空空 間間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ù)庫表year類

12、型列。值顯示為1901到2155或0000。2. 2. 日期日期 時間時間 日期時間類型包含了date、datetime和timestamp這3種子類型。（1）date：僅表示一個日期，支持的范圍是“1000-01-01”到“9999-12-31”。（2）datetime：表示日期和時間的組合，支持的范圍是“1000-01-01 00:00:00.000000”到“9999-12-31 23:59:59.999999”。（3）timestamp：表示一個時間戳，能夠自動存儲記錄修改的時間，范圍是“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)時間，時間戳不能表示值1970-01-01 00:00:00，因為它等于開始的0秒，而值0保留用于表示00:00 -00-00 00:00:00（即0時間戳值），將它設(shè)為null默認為當(dāng)前的日期和時間。3. 3. 時間（時間（timetime）（1）表示一段時間，范圍是-838:59:59.000000到838:59:59.000000。MySQL以hh:mm:ss.微秒格式顯示時間值，并允許使用字符串或數(shù)值兩種方式為時間列分配值?？梢越o出0到6長度范圍內(nèi)的可選fsp值，以指定小數(shù)秒精

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

15、示時間，與此對應(yīng)的數(shù)值也可表達時間。【例【例3.33.3】日期、時間類型插入、存儲和顯示。USE mydb;DROP TABLE IF EXISTS test;CREATE TABLE test(日期date,日期時間datetime,時間戳timestamp,時間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;運行結(jié)果如圖3.4所示。說明：說明：（a）用yyyy-mm-dd或yyyy-mm-dd hh:mm:ss標(biāo)準(zhǔn)字符串格式表示日期時間值，其中，yyyy表示年，mm表示月，dd表示日，hh表示時，mm表示分，ss表示秒。（b）用不含間隔符的字符串格式表示日期時間，形如yyyymmdd或yyyymmd

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

18、位數(shù)表示年；如果數(shù)字是6或12位長，則假定為yymmdd或yymmddhhmmss格式，前2位數(shù)表示年。其它數(shù)字被解釋為仿佛用零填充到了最近的長度。目 錄目 錄目 錄0101文本字符串型文本字符串型1. 常用的文本字符串型2. 字符和文本存儲舉例MySQL8中根據(jù)保存的文本長度用途不同，可以選擇不同的文本數(shù)據(jù)類型。表3.4列出了MySQL文本字符串?dāng)?shù)據(jù)類型及其屬性。類類 型型 名名 稱稱存存 儲儲 空空 間間（實際字符數(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個枚舉）2（65535個枚舉）65535set1、2、3、4或864個成員1. 1. 常用的文本字符串型常用的文本字符串型最常用的文本字符串型包括char、varchar及各種text類型。（1）固定長度字符串型：charchar通常定義成char(m)的形式，m是字符串長，m255，實際占用的存儲空間為m*n，n為一個字符在字符集中的字節(jié)數(shù)。當(dāng)保存時字符個數(shù)L不足m，則在右側(cè)填充空格，以達到指定的長度m。當(dāng)檢索到char(m)值時，尾部的空格將被刪除。SQL語句定義表時，可不

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

21、存儲舉例【例【例3.43.4】創(chuàng)建表，包含3種類型的文本字符串列，然后對其進行測試。1）創(chuàng)建測試表，包含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中國夢我的夢, 2022北京冬奧, Hello!世界！);#（c）SELECT * FROM test;運行結(jié)果如圖3.5所示。說明：說明：（a）對于不超過類型長度的數(shù)值，MySQL將其以原樣字符串形式存儲。（b）對于超過類型長度的數(shù)值，在嚴(yán)格模式下插入會出錯；非嚴(yán)格模式下，MySQL將其截斷至定義長度存儲。以科學(xué)記數(shù)法表示的數(shù)值，MySQL會自動計算出其值并保存為字符串

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

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

25、 SET;【例【例3.53.5】創(chuàng)建表，設(shè)置4個列分別采用4種不同語言文字的字符集編碼，然后對其進行操作。1）創(chuàng)建測試表。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#韓文編碼);說明：說明：創(chuàng)建表時如果不顯式指定字符集，MySQL會采用默認的字符集。

26、MySQL 5及更早前版本默認字符集是utf8，從MySQL 8.0開始，默認采用增強的utf8mb4編碼，它是對utf8的擴充，為每個字符分配4字節(jié)長度，可以表示Unicode中一些不常用的字符。2）輸入字符集測試。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）混合文字測試。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;運行結(jié)果如圖3.7所示。說明說明: : （1）c1列采用utf8編碼，中、日、韓三種文字皆可輸入，當(dāng)然包括英文等各種文字和各種符號。（2）c2列采用GB2312編碼，只能插入GB2312編碼中包含的6763個漢字和字符，不包含在其中的漢字是不能進入的。而康熙字典共收錄漢字47035個，絕大多數(shù)漢字無法保存到這種編碼列。如果需要數(shù)據(jù)庫保存更多的漢字，

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

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

31、，例如：字符序utf8_general_ci，表明它是字符集utf8的字符序。ci即對大小寫不敏感，cs對大小寫敏感。utf8字符集對應(yīng)的collation沒有cs。（2）字符集_bin當(dāng)字符需要區(qū)分大小寫時，選擇該排序集。每個字符集對應(yīng)一定數(shù)量的排序規(guī)則，collation用于指定數(shù)據(jù)集如何排序以及字符串的排序規(guī)則。2. 2. 默認字符集及排序規(guī)則默認字符集及排序規(guī)則兩個不同的字符集不能有相同的排序規(guī)則。【例【例3.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）運行結(jié)果如圖3.10所示。 說明：說明：（a）雖然t2列僅僅設(shè)置字符集（gbk），但同時也就設(shè)置了對應(yīng)的默認排序規(guī)則（gbk_chinese_cs）。通過查詢information_schema.columns（系統(tǒng)數(shù)據(jù)庫表）可以獲取當(dāng)前t2列的字符排序規(guī)則對應(yīng)不區(qū)分大小寫。（b）由于mytab表t2列排序規(guī)則不區(qū)分大小寫，所以該語句將t2列值為“A”的記錄也顯示出來。（c）雖然ASCII碼Aa，但由于mytab表t2列的排序規(guī)則不區(qū)分大小寫，誰排列在前取決于查詢時先遇到的行，這里它把“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）運行結(jié)果如圖3.11所示。 說明：說明：（a）修改設(shè)置t2列為字符集（gbk）對應(yīng)區(qū)分大小寫排序規(guī)則（gbk_bin）。（b）由于mytab表t2列排序規(guī)則區(qū)分大小寫，所以該語句僅顯示t2列值為a的記錄。（c）由于ASCII碼大寫字符小于小寫字符，而ORDER BY t2默認從小到大排列，故這里把字符為A記錄排在前面，然

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

36、like utf8mb4%_cs; 上述語句相對于條件查詢information_schema系統(tǒng)數(shù)據(jù)庫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二進制字符串類型二進制字符串類型1. 二進制字符串類型介紹2. 二進制字符串的性質(zhì)3. 二進制字符串存儲不同類型1. 1.

37、二進制字符串類型介紹二進制字符串類型介紹二進制字符串類型一般用于存儲圖像、聲音、視頻等內(nèi)容，MySQL中可以定義的二進制字符串類型如表3.5所示。類類 型型 名名 稱稱存存 儲儲 需需 求求（實際字符數(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說明：說明：（1）binary和varbinary類型類似于char和varchar，不同點在于它們存儲的

38、是二進制字節(jié)形式的字符串。（2）blob是一個二進制大對象，用來存儲可變數(shù)量的數(shù)據(jù)，它又分tinyblob、blob、mediumblob和longblob幾種，每種可容納值的最大長度不同。2. 2. 二進制字符串的性質(zhì)二進制字符串的性質(zhì)【例【例3.73.7】創(chuàng)建表，測試二進制字符串?dāng)?shù)據(jù)的性質(zhì)。1）創(chuàng)建測試表。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）說明：說明：（a）專用于查看對應(yīng)列內(nèi)容的二進制串，可通過Navicat查看表中數(shù)據(jù)，運行結(jié)果如圖3.14所示。 其中，blob數(shù)據(jù)不是直接可見的，只顯示了類型和字節(jié)數(shù)，13的二進制1101占一個字節(jié)，blob在此基礎(chǔ)上加1，即2字節(jié)存儲。所有二進制字符串型數(shù)據(jù)都無法直接通過Navicat表查看器更改。（b）用于查看二進制字符串列內(nèi)容占用的字節(jié)長，運行結(jié)果如圖3.15所示。3. 3. 二進制字符串存儲不

40、同類型二進制字符串存儲不同類型【例【例3.73.7續(xù)】續(xù)】采用上例test表，測試二進制字符串型對各種不同類型數(shù)據(jù)的存儲性質(zhì)。1）插入字符串、數(shù)值。USE mydb;DELETE FROM test;INSERT INTO test VALUES(hello, hello, hello);#（a）INSERT INTO test VALUES(2022, 中國, 2022中國夢);#（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）說明：說明：（a）對于字符型數(shù)據(jù)，binary依舊是補足至定義長度，varbinary和blob按照實際字節(jié)數(shù)（一個字符占一個字節(jié)）存儲。（b）儲存一個漢字要占用16個二進制位，即2字節(jié)，varbinary和blob在其上加1存儲，故一個漢字要用3個字節(jié)存儲，這樣“2022中國夢”一共占用43313個字節(jié)。（c）英文字符、數(shù)字、空格也都是占用一個字節(jié)。（d）若直接插入浮點數(shù)，MySQL將其每個數(shù)字位作為一個字符看待，若數(shù)值是以科學(xué)記數(shù)法格式給出，MySQL會先計算其值，

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

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

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

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

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

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

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

49、 錄0101JSONJSON數(shù)據(jù)類型數(shù)據(jù)類型1. JSON對象2. JSON數(shù)組1. JSON1. JSON對象對象JSON類型的數(shù)據(jù)是以JSON對象的形式提供的，一個標(biāo)準(zhǔn)的JSON對象包含一組鍵值對，用逗號分隔，以“”和“”字符括起來：鍵1: 值1,.創(chuàng)建JSON對象采用JSON_OBJECT系統(tǒng)函數(shù)：JSON_OBJECT(鍵1,值1,鍵2,值2,.)其中，鍵與值必須成對出現(xiàn)，且不能為空。一旦出現(xiàn)鍵名為空或參數(shù)總數(shù)為奇數(shù)的情形，系統(tǒng)就會報錯。創(chuàng)建JSON對象和存儲遵循如下一些基本的規(guī)則：（1）鍵名（無論中英文）都必須以雙引號（）括起來。（2）鍵值是字符串型（無論中英文）必須加雙引號（）或單

50、引號（）。（3）鍵值是數(shù)值型（整數(shù)或?qū)崝?shù)類型），可以以數(shù)值或字符串兩種格式存儲。當(dāng)以數(shù)值存儲時，直接寫出數(shù)值即可；而以字符串存儲時，鍵值需加雙引號或單引號。（4）鍵值是日期時間型，可以用兩種方式給出：一種是直接以字符串形式寫出；另一種是通過系統(tǒng)內(nèi)置的日期時間函數(shù)獲得，不同函數(shù)返回的日期時間字符串的格式不同，均以字符串的形式存儲到JSON對象中。【例【例3.93.9】JSON對象的創(chuàng)建和存儲。1）首先創(chuàng)建一個含JSON類型列的測試表。USE mydb;DROP TABLE IF EXISTS test;CREATE TABLE test(jjson);2）向表中插入一組JSON對象。INSERT

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

52、出一個數(shù)組數(shù)據(jù)結(jié)構(gòu)來統(tǒng)一管理。JSON數(shù)組包含多個值，這些值由逗號分隔：JSON_ARRAY(值,.)JSON數(shù)組元素顯示包含在“”和“”中：鍵1: 值1,.,.【例【例3.93.9續(xù)】續(xù)】插入一些不同的標(biāo)量值與JSON對象一起作為JSON數(shù)組的元素，并嘗試嵌套的結(jié)構(gòu)，插入上例為j（JSON類型）列test表。向插入測試表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;運行結(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、其中的點表示為P（x，y），其中x是點P到x軸上的原點（0,0）的距離，而y是 與y軸上的原點（0,0）的距離。 因此，如果知道一個點的x，y，則可以將其定位在坐標(biāo)面上。計算P點到原點的距離為x和y平方和的平方根。如圖3.23所示。（2）球面直角坐標(biāo)系球面遵循大地參考系來繪制表面上的位置，大地參考系P（x，y）基于經(jīng)度和緯度值。地球上某個位置（點）的緯度是它與赤道的度數(shù)（沿地球旋轉(zhuǎn)軸測量時）。 地球上某個位置的經(jīng)度是距本初子午線的度數(shù)（沿赤道測量時）。 緯度和經(jīng)度都是從地球中心測量的。緯度的最大值為90，最小值為-90。 同樣，經(jīng)度的最大值為180，而最小值為-180。如圖3.24所示。表面上

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

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

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