第2單元MySQL數(shù)據(jù)類型MySQL數(shù)據(jù)庫應(yīng)用實(shí)戰(zhàn)教程（慕課版）（第2版）【目錄】2.1

數(shù)值類型2.2

字符串類型2.3

日期和時(shí)間類型2.4

復(fù)合類型2.5

JSON數(shù)據(jù)類型2.6

空間數(shù)據(jù)類型2.7

如何選取數(shù)據(jù)類型2.8

綜合實(shí)訓(xùn)：設(shè)計(jì)電商平臺(tái)商品表2.9

小結(jié)目錄【學(xué)習(xí)導(dǎo)讀】在MySQL中，選擇適當(dāng)?shù)臄?shù)據(jù)類型是設(shè)計(jì)數(shù)據(jù)庫和表的重要環(huán)節(jié)。根據(jù)需求，適當(dāng)?shù)臄?shù)據(jù)類型可以提供最佳的數(shù)據(jù)存儲(chǔ)和查詢性能。在電子商務(wù)網(wǎng)站的產(chǎn)品表中，使用整數(shù)類型（int）存儲(chǔ)產(chǎn)品的價(jià)格和庫存數(shù)量，以便進(jìn)行快速的數(shù)值比較和計(jì)算。對(duì)于產(chǎn)品名稱和描述等文本信息，使用varchar類型存儲(chǔ)，以便適應(yīng)不同長度的產(chǎn)品信息。對(duì)于產(chǎn)品的發(fā)布日期，使用date類型存儲(chǔ)，并使用相應(yīng)的日期函數(shù)進(jìn)行日期范圍的查詢。此外，對(duì)于產(chǎn)品的圖片，可以使用blob類型存儲(chǔ)二進(jìn)制圖像數(shù)據(jù)，以保持原始圖像質(zhì)量。通過選擇合適的數(shù)據(jù)類型，可以有效地存儲(chǔ)和檢索產(chǎn)品信息，并提供良好的用戶體驗(yàn)和性能。學(xué)習(xí)導(dǎo)讀【學(xué)習(xí)目標(biāo)】知識(shí)目標(biāo)1．掌握MySQL數(shù)值類型。2．掌握MySQL字符串類型。3．掌握MySQL日期和時(shí)間類型。4．了解MySQL復(fù)合類型。5．了解MySQLJSON數(shù)據(jù)類型。6．了解MySQL空間數(shù)據(jù)類型。能力目標(biāo)1．能夠針對(duì)不同場(chǎng)景選擇合適的數(shù)據(jù)類型。2．能夠優(yōu)化字段的數(shù)據(jù)類型，能夠節(jié)省存儲(chǔ)空間及加快訪問速度。素質(zhì)目標(biāo)1．培養(yǎng)舉一反三的能力。2．培養(yǎng)分析問題、解決問題的能力。學(xué)習(xí)目標(biāo)思維導(dǎo)圖2.1數(shù)值類型MySQL支持所有標(biāo)準(zhǔn)SQL中的數(shù)值類型，包括位值類型（bit）、整數(shù)類型（tinyint、smallint、mediumint、int、bigint）、浮點(diǎn)數(shù)類型（float、double、decimal）。位值類型（bit）bit(M)為位值類型。M表示值的位數(shù)，取值范圍為1到64，如果省略默認(rèn)為1。整數(shù)類型（tinyint、smallint、mediumint、int、bigint）整數(shù)類型適合存儲(chǔ)整數(shù)值；整數(shù)類型里，MySQL支持tinyint、smallint、mediumint、int、bigint這5種類型。浮點(diǎn)數(shù)類型（float、double、decimal）MySQL支持float、double、decimal這3種類型。float是單精度浮點(diǎn)數(shù)值、double是雙精度浮點(diǎn)數(shù)值、decimal是定點(diǎn)數(shù)值。2.1數(shù)值類型表2.1數(shù)值類型數(shù)值類型長度/字節(jié)有符號(hào)存儲(chǔ)范圍無符號(hào)存儲(chǔ)范圍含義bit(M)(M+7)/8——位值類型tinyint1(－128,127)(0,255)小整數(shù)值smallint2(－32768,32767)(0,65535)大整數(shù)值mediumint3(－8388608,8388607)(0,16777215)大整數(shù)值int或integer4(－2147483648,2147483647)(0,4294967295)大整數(shù)值bigint8(－9233372036854775808,

9223372036854775807)(0,18446744073709551615)極大整數(shù)值float(m,d)4－3.402823466E+38到－1.175494351E－38、0和1.175494351E－38到3.402823466E+38m是數(shù)字總位數(shù)，d是小數(shù)點(diǎn)后面的位數(shù)，如果m和d被省略，

根據(jù)硬件允許的限制來保存值單精度浮點(diǎn)數(shù)值double8－1.7976931348623157E+308到－2.22507385854072014E－308、0和2.22507385854072014E－308到1.7976931348623157E+308雙精度浮點(diǎn)數(shù)值decimal

(m,d)m指定小數(shù)點(diǎn)左邊和右邊可以存儲(chǔ)的十進(jìn)制數(shù)字的最大個(gè)數(shù)，最大為38；d指定小數(shù)點(diǎn)右邊可以存儲(chǔ)的十進(jìn)制數(shù)字的最大個(gè)數(shù)，小數(shù)位數(shù)必須是0～m的值，默認(rèn)為0定點(diǎn)數(shù)值2.2字符串類型MySQL提供了豐富的字符串類型，用于存儲(chǔ)和處理文本和二進(jìn)制數(shù)據(jù)。這些字符串類型分為定長字符串類型、變長字符串類型、大文本類型、二進(jìn)制字符串類型，每種類型都有不同的特性和用途。變長字符串類型定長字符串類型大文本類型二進(jìn)制字符串類型適合存儲(chǔ)長度固定的數(shù)據(jù)。它使用固定的存儲(chǔ)空間，但可能造成空間浪費(fèi)。用于存儲(chǔ)二進(jìn)制數(shù)據(jù)，如圖像或文件。它可用于保存任意類型的二進(jìn)制數(shù)據(jù)。存儲(chǔ)長度可變的數(shù)據(jù)的理想選擇。它根據(jù)實(shí)際存儲(chǔ)的數(shù)據(jù)長度分配空間，能夠節(jié)省存儲(chǔ)空間。用于存儲(chǔ)較大的文本內(nèi)容。它提供了更高的存儲(chǔ)容量，并具有適應(yīng)多種語言和字符集的能力。2.2字符串類型表2.2字符串類型分類數(shù)值類型長度/字節(jié)含義定長字符串類型char0～255固定長度，最多255個(gè)字符變長字符串類型varchar0～65535可變長度，最多65535個(gè)字符大文本類型tinytext0～255短文本數(shù)據(jù)大文本類型text0～65535長文本數(shù)據(jù)大文本類型mediumtext0～16777215中等長度文本數(shù)據(jù)大文本類型longtext0～4294967295極大文本數(shù)據(jù)二進(jìn)制字符串類型binary(m)—允許長度為0～m個(gè)字節(jié)的定長字節(jié)符串二進(jìn)制字符串類型varbinary(m)—允許長度為0～m個(gè)字節(jié)的定長字節(jié)符串，值的長度+1個(gè)字節(jié)二進(jìn)制字符串類型tinyblob0～255不超過255個(gè)字符的二進(jìn)制字符串二進(jìn)制字符串類型blob0～65535二進(jìn)制形式的長文本數(shù)據(jù)二進(jìn)制字符串類型mediumblob0～16777215二進(jìn)制形式的中等長度文本數(shù)據(jù)二進(jìn)制字符串類型longblob0～4294967295二進(jìn)制形式的極大文本數(shù)據(jù)2.3日期和時(shí)間類型MySQL數(shù)據(jù)庫提供了5種不同的日期和時(shí)間類型，即date、time、year、datetime、timestamp，用來存儲(chǔ)與日期和時(shí)間相關(guān)的數(shù)據(jù)。每個(gè)日期和時(shí)間類型有一個(gè)有效值范圍和一個(gè)“零”值，當(dāng)指定不合法的MySQL不能表示的值時(shí)使用“零”值，日期和時(shí)間類型如表2.3所示。表2.3日期和時(shí)間類型日期/時(shí)間類型長度/字節(jié)范圍格式含義date41000-01-01/9999-12-31yyyy-mm-dd日期值time3'-838:59:59'/'838:59:59'hh:mm:ss時(shí)間值year11901/2155yyyy年份值datetime81000-01-0100:00:00/9999-12-3123:59:59yyyy-mm-ddhh:mm:ss混合日期和時(shí)間值timestamp41970-01-0100:00:00/2037年

某時(shí)yyyymmddhhmmss混合日期和時(shí)間值，時(shí)間戳，特別地，timestamp也可以自動(dòng)更新為當(dāng)前的日期和時(shí)間2.4復(fù)合類型適合存儲(chǔ)表單界面中的“單選值”設(shè)定enum類型的時(shí)候，需要給定“固定的幾個(gè)選項(xiàng)”存儲(chǔ)的時(shí)候就只存儲(chǔ)其中的一個(gè)值設(shè)定enum類型的格式為enum("選項(xiàng)1","選項(xiàng)2","選項(xiàng)3",...)。enum單選字符串?dāng)?shù)據(jù)類型set多選字符串?dāng)?shù)據(jù)類型適合存儲(chǔ)表單界面中的“多選值”設(shè)定set類型的時(shí)候，同樣需要給定“固定的幾個(gè)選項(xiàng)”存儲(chǔ)的時(shí)候可以存儲(chǔ)其中的若干個(gè)值設(shè)定set類型的格式為set("選項(xiàng)1","選項(xiàng)2","選項(xiàng)3",...)2.5JSON數(shù)據(jù)類型MySQL8.0引入了JSON數(shù)據(jù)類型，它是一種特殊的數(shù)據(jù)類型，用于存儲(chǔ)和處理JSON（JavaScriptObjectNotation）格式的數(shù)據(jù)。010203JSON數(shù)據(jù)類型：以緊湊的二進(jìn)制格式存儲(chǔ)JSON數(shù)據(jù)，比將JSON數(shù)據(jù)存儲(chǔ)為字符串更有效存儲(chǔ)格式：以緊湊的二進(jìn)制格式存儲(chǔ)，優(yōu)化了空間占用和查詢性能。以原生的JSON格式存儲(chǔ)，包括對(duì)象、數(shù)組、字符串、數(shù)字、布爾值和NULL等。JSON函數(shù)和操作符：一套內(nèi)置的JSON函數(shù)和操作符，用于處理和查詢JSON數(shù)據(jù)。包括JSON_EXTRACT、JSON_ARRAY、JSON_SEARCH、JSON_OBJECT等，可以進(jìn)行JSON數(shù)據(jù)的解析、提取、修改和查詢。04索引和搜索：支持在JSON列上創(chuàng)建函數(shù)索引，以提高JSON數(shù)據(jù)的檢索性能。050607驗(yàn)證和嚴(yán)格模式：會(huì)驗(yàn)證存儲(chǔ)在JSON列中的數(shù)據(jù)是否符合JSON格式。在嚴(yán)格模式下，存儲(chǔ)的JSON數(shù)據(jù)必須是有效的JSON格式，否則會(huì)拋出錯(cuò)誤。查詢和過濾：使用JSON函數(shù)和操作符，可以進(jìn)行復(fù)雜的查詢和過濾操作JSON聚合：提供了諸如JSON_ARRAYAGG和JSON_OBJECTAGG等函數(shù)，允許用戶聚合JSON數(shù)據(jù)并將其作為單個(gè)JSON文檔返回2.6空間數(shù)據(jù)類型最通用的空間數(shù)據(jù)類型，用于表示任何類型的幾何對(duì)象。它可以存儲(chǔ)點(diǎn)、線、多邊形等幾何形狀GEOMETRY用于存儲(chǔ)二維空間中的點(diǎn)，表示地理位置信息。它由經(jīng)度和緯度坐標(biāo)組成POINT用于存儲(chǔ)二維空間中的線段，表示兩個(gè)或多個(gè)點(diǎn)之間的連續(xù)直線LINESTRING用于存儲(chǔ)二維空間中的多邊形，表示由多個(gè)邊界點(diǎn)組成的封閉區(qū)域POLYGON最復(fù)雜的空間數(shù)據(jù)類型，用于存儲(chǔ)任意類型的幾何對(duì)象組合GEOMETRYCOLLECTION用于存儲(chǔ)多個(gè)多邊形，表示多邊形的集合MULTIPOLYGON用于存儲(chǔ)多條線段，表示線段的集合MULTILINESTRING用于存儲(chǔ)多個(gè)二維點(diǎn)，表示點(diǎn)的集合MULTIPOINT2.7如何選取數(shù)據(jù)類型大小合適就是好的盡量避免使用NULL選擇一種能夠有效執(zhí)行這些操作并且?guī)в凶畲笙薅褥`活性的類型簡單存放就好數(shù)據(jù)操作和應(yīng)用處理的要求2.7如何選取數(shù)據(jù)類型1．char和varchar如果列中要存儲(chǔ)的數(shù)據(jù)的長度差不多是一致的，則應(yīng)該考慮使用char，否則應(yīng)該考慮使用varchar；如果列中最大數(shù)據(jù)的長度小于50個(gè)字節(jié)，則一般也考慮使用char（當(dāng)然，如果這個(gè)列很少用，則基于節(jié)省空間和減少I/O的考慮，還是可以使用varchar）；一般不宜定義大于50個(gè)字節(jié)的char類型列。2．text和blob在保存大文本時(shí)，通常選擇text或者blob。blob可以保存二進(jìn)制數(shù)據(jù)（如照片）。text和blob又分別包括text、mediumtext、longtext和blob、mediumblob、longblob，它們之間的區(qū)別是存儲(chǔ)文本長度不同和存儲(chǔ)字節(jié)不同。在不必要的時(shí)候應(yīng)避免檢索較大的blob或text值，把blob或text列分離到單獨(dú)的表中。2.7如何選取數(shù)據(jù)類型3．浮點(diǎn)數(shù)和定點(diǎn)數(shù)decimal用于存儲(chǔ)精確數(shù)據(jù)，而float只能用于存儲(chǔ)非精確的數(shù)據(jù)，故精確數(shù)據(jù)最好使用decimal類型。由于float的存儲(chǔ)空間的開銷一般比decimal小（精確到7位小數(shù)只需要4個(gè)字節(jié)，而精確到15位小數(shù)只需要8個(gè)字節(jié)），故非精確數(shù)據(jù)建議使用float。4．日期類型的選擇根據(jù)實(shí)際需要選擇能夠滿足應(yīng)用的最小存儲(chǔ)日期類型。如果記錄年、月、日、時(shí)、分、秒，并且記錄年份比較久遠(yuǎn)，則最好使用datetime，不要使用timestamp；如果記錄的日期需要讓不同時(shí)區(qū)的用戶使用，則最好使用timestamp，因?yàn)槿掌诤蜁r(shí)間類型中只有它能夠和實(shí)際時(shí)區(qū)相對(duì)應(yīng)。2.8綜合實(shí)訓(xùn)：設(shè)計(jì)電商平臺(tái)商品表要設(shè)計(jì)一個(gè)在線商城系統(tǒng)，其中需要存儲(chǔ)商品的信息，包括商品名稱、描述、價(jià)格、庫存數(shù)量和發(fā)布日期。根據(jù)這個(gè)場(chǎng)景，可以選擇以下數(shù)據(jù)類型來實(shí)現(xiàn)。010203商品名稱：varchar(100)。使用varchar類型來存儲(chǔ)商品的名稱，長度為100，以適應(yīng)不同長度的商品名稱。商品描述：text。使用text類型來存儲(chǔ)商品的詳細(xì)描述，可以存儲(chǔ)較長的文本內(nèi)容。價(jià)格：decimal(10,2)。使用decimal類型來存儲(chǔ)商品的價(jià)格，其中10表示總共可存儲(chǔ)10位數(shù)字，2表示小數(shù)點(diǎn)后保留2位，以滿足價(jià)格的精度要求。04庫存數(shù)量：int。使用int類型來存儲(chǔ)商品的庫存數(shù)量，適用于存儲(chǔ)整數(shù)值。05發(fā)布日期：datetime。使用datetime類型來存儲(chǔ)商品的發(fā)布日期和時(shí)間，以方便進(jìn)行與日期和時(shí)間相關(guān)的操作。2.8綜合實(shí)訓(xùn)：設(shè)計(jì)電商平臺(tái)商品表根據(jù)以上數(shù)據(jù)類型的選擇，可以創(chuàng)建一個(gè)名為“products”的表來存儲(chǔ)商品信息，代碼如下CREATETABLEproducts(product_idINTAUTO_INCREMENTPRIMARYKEY,product_nameVARCHAR(100),descriptionTEXT,priceDECIMAL(10,2)