項目三ＭySQL字符集與數(shù)據(jù)類型MySQL數(shù)據(jù)庫任務驅動式教程（第4版）（微課版｜AIGC拓展版）任務5認識和設置MySQL字符集【任務背景】MySQL8.0支持41種字符集和272個校對原則。MySQL對字符集的支持可以細化到4個層次：服務器、數(shù)據(jù)庫、表和連接層。MySQL8.0默認的字符集不再是“l(fā)atin”而是“utf8mb4”，它支持中文字符，但是在使用CommandLineClient查詢數(shù)據(jù)時仍可能出現(xiàn)亂碼。如何解決亂碼問題？GB2312和GBK也是中文字符集，它們與UTF8mb4之間有何異同？如何設置和正確使用字符集？本任務將給出這些問題的答案。【任務要求】本任務從認識字符集和校對原則著手，學習MySQL支持的字符集和校對原則，并著重了解latin1、UTF-8和GB2312字符集；通過認識描述字符集的系統(tǒng)變量，學習掌握修改默認字符集的方法，并掌握在實際應用中如何選擇合適的字符集。任務5認識和設置MySQL字符集【任務目標】1．知識目標（1）認識字符集和校對原則；（2）了解字符集UTF8mb4和GB2312。2．能力目標掌握通過命令行設置字符集。3．素養(yǎng)目標（1）養(yǎng)成嚴謹細致的習慣；（2）弘揚優(yōu)秀傳統(tǒng)文化，規(guī)范使用漢字。認識字符集和校對原則字符（Character）是指人類語言中最小的表義符號，如A、B等。給定一系列字符，對每個字符賦予一個數(shù)值，用數(shù)值來代表對應的字符，這一數(shù)值就是該字符的編碼（Encoding）。給定一系列字符并賦予對應的編碼后，所有字符和編碼對組成的集合就是字符集（CharacterSet）。例如，給定字符列表為{'A','B'}時，{'A'=>0,'B'=>1}就是一個字符集。字符校對原則（Collation）是指在同一字符集內字符之間的比較規(guī)則，又稱“字符序”。確定字符序后，才能在一個字符集上定義什么是等價的字符，以及字符之間的大小關系。每個字符序唯一對應一種字符集，但一種字符集可以對應多個字符序，其中有一個是默認字符序（DefaultCollation）。MySQL的字符序名稱遵從命名慣例：以字符序對應的字符集名稱開頭；以_ci（表示大小寫不敏感）、_cs（表示大小寫敏感）或_bin（表示按編碼值比較）結尾。MySQL8.0支持的字符集和校對原則MySQL8.0服務器能夠支持41種字符集和272個校對原則。可以使用SHOWCHARACTERSET語句列出可用的字符集，代碼如下。MySQL8.0支持的字符集如圖5.1所示。MySQL>SHOWCHARACTERSET;MySQL8.0支持的字符集和校對原則任何一個字符集可能有一個校對原則，也可能有幾個校對原則。要想列出一個字符集的校對原則，需要使用SHOWCOLLATION語句。例如，查看“l(fā)atin1”字符集的校對原則，可以使用mysql>showcollationlike'latin1%’;

如圖5.2所示。圖5.2以“l(fā)atin1”開頭的校對原則確定字符集和校對原則描述字符集的系統(tǒng)變量MySQL用于描述字符集的系統(tǒng)變量，用如下語句查看。mysql>showgloabvariableslike'%characte_set%';character_set_server和collation_server：這兩個變量是服務器的字符集，是默認的內部操作字符集。character_set_client：這個變量是客戶端來源數(shù)據(jù)使用的字符集，用來決定MySQL怎么解釋客戶端發(fā)送到服務器的SQL命令。character_set_connection和collation_connection：這兩個變量是連接層字符集，用來決定MySQL怎么處理客戶端發(fā)來的SQL命令。character_set_results：這個變量是查詢結果字符集。character_set_database和collation_database：這兩個變量是當前選中數(shù)據(jù)庫的默認字符集。character_set_filesystem：文件系統(tǒng)的編碼格式，把操作系統(tǒng)上的文件名轉換成該字符集，即把character_set_client轉換為character_set_filesystem，默認binary是不做任何轉換的。character_set_system：這是數(shù)據(jù)庫系統(tǒng)使用的編碼格式。確定字符集和校對原則MySQL對字符集的支持細化到4個層次：服務器、數(shù)據(jù)庫、數(shù)據(jù)表和連接。MySQL對字符集的指定可以細化到一個數(shù)據(jù)庫、一張表和一列應該用什么字符集。字符集的依存關系，如圖5.3所示。（1）MySQL默認的服務器級的字符集，決定客戶端級、連接層級和結果級的字符集。（2）服務器級的字符集決定數(shù)據(jù)庫級的字符集。（3）數(shù)據(jù)庫級的字符集決定表級的字符集。（4）表級的字符集決定字段級的字符集。圖5.3字符集的依存關系使用MySQL字符集時的建議建立數(shù)據(jù)庫、表和進行數(shù)據(jù)庫操作時，盡量顯式指出使用的字符集，而不是依賴MySQL的默認設置，否則MySQL升級時可能會有很大問題。當使用MySQLCAPI（MySQL提供C語言操作的API）時，在初始化數(shù)據(jù)庫句柄后馬上用MySQL_options設定MYSQL_SET_CHARSET_NAME屬性為UTF-8，這樣就不用顯式地用SETNAMES語句指定連接字符集，且用MySQL_ping重連斷開的長連接時也會把連接字符集重置為UTF-8。對于MySQLPHPAPI，一般頁面級的PHP程序運行時間較短，在連接到數(shù)據(jù)庫以后顯式地用SETNAMES語句設置一次連接字符集即可；但當使用長連接時，應注意保持連接通暢并在斷開重連后用SETNAMES語句顯式重置連接字符集。注意服務器級、結果級、客戶端級、連接層級、數(shù)據(jù)庫級、表級等的字符集的統(tǒng)一，當數(shù)據(jù)庫級的字符集設置為UTF-8時，表級和字段級的字符集也是UTF-8。如果查詢時出現(xiàn)數(shù)據(jù)庫中文亂碼現(xiàn)象，可以在發(fā)送查詢之前使用如下語句解決這個問題。mysql>setnames'gb2312';項目實踐修改MySQL的my.ini文件，將默認字符集修改為GB2312。習題簡答題1．如何解決中文亂碼的問題？2．各級字符集之間有怎樣的依存關系？任務6MySQL數(shù)據(jù)類型【任務背景】表是用來存放數(shù)據(jù)的，一個數(shù)據(jù)庫需要多少張表，一個表中應包含多少列（字段），各個列要選擇什么數(shù)據(jù)類型，是建表時必須考慮的問題。數(shù)據(jù)類型是否合理對數(shù)據(jù)庫性能也會產生一定的影響。在實際應用中，姓名、專業(yè)名、商品名和電話號碼等字段可以選擇VARCHAR類型；學分、年齡等字段是小整數(shù)，可以選擇TINYINT類型；成績、溫度和測量值等數(shù)據(jù)要求保留一定的小數(shù)位，可以選擇FLOAT類型；出生日期、工作時間等字段可以選擇DATE或DATETIME類型。數(shù)據(jù)類型是數(shù)據(jù)的一種屬性，其可以決定數(shù)據(jù)的存儲格式、取值范圍和相應的限制。MySQL包括整數(shù)類型、浮點數(shù)類型、定點數(shù)類型、字符串類型、二進制、日期和時間類型、枚舉類型和集合類型等數(shù)據(jù)類型。任務6MySQL數(shù)據(jù)類型【任務目標】1．知識目標認識MySQL的主要數(shù)據(jù)類型。2．能力目標學會合理使用MySQL的主要數(shù)據(jù)類型。3．素養(yǎng)目標（1）養(yǎng)成嚴謹細致的習慣；（2）培養(yǎng)科學存儲數(shù)據(jù)和提高數(shù)據(jù)庫運行效率的意識?！救蝿找蟆勘救蝿諏W習MySQL的主要的數(shù)據(jù)類型的含義、特點、取值范圍和存儲空間，并對相關數(shù)據(jù)類型進行比較；學習如何根據(jù)字段存儲數(shù)據(jù)的不同選擇合適的數(shù)據(jù)類型，以及怎樣附加數(shù)據(jù)類型的相關屬性。整數(shù)類型整數(shù)類型是數(shù)據(jù)庫中最基本的數(shù)據(jù)類型。標準SQL中支持INTEGER和SMALLINT兩種整數(shù)類型。MySQL數(shù)據(jù)庫除了支持這兩種類型以外，還擴展支持TINYINT、MEDIUMINT和BIGINT。整數(shù)類型及其取值范圍見表6.1。整數(shù)類型字節(jié)數(shù)無符號數(shù)的取值范圍有符號數(shù)的取值范圍TINYINT10～（28?1）?27~(27?1)SMALLINT20～（216?1）?215~(215?1)MEDIUMINT30～（224?1）?223~(223?1)INT(INTEGER)40～（232?1）?231~(231?1)BIGINT80～（264?1）?263~(263?1)浮點數(shù)類型和定點數(shù)類型MySQL使用浮點數(shù)類型和定點數(shù)類型來表示小數(shù)。浮點數(shù)類型包括單精度浮點數(shù)（FLOAT型）和雙精度浮點數(shù)（DOUBLE型），定點數(shù)類型就是DECIMAL型，見表6.2。類型字節(jié)數(shù)負數(shù)的取值范圍非負數(shù)的取值范圍FLOAT4-3.402823466E+38～-1.175494351E-380和1.175494351E-38～

3.402823466E+38DOUBLE8-1.7976931348623157E+308～

-2.2250738585072014E-3080和2.2250738585072014E-308～

1.7976931348623157E+308DECIMAL(M,D)或DEC(M,D)M+2有效取值范圍由M和D決定，M的取值范圍為[1,65]，D的取值范圍為[0,30]即-10(-D)N-10(M-D)-1有效取值范圍由M和D決定，M的取值范圍為[1,65]，D的取值范圍為[0,30]，即10(-D)～10(M-D)-1CHAR類型和VARCHAR類型CHAR類型和VARCHAR類型比較見表6.3。名稱含義字符個數(shù)CHAR(n)固定長度的字符串最多255個字符VARCHAR(n)可變長度的字符串最多65535個字符CHAR和VARCHAR類型的區(qū)別如下。二者都可以通過指定n來限制存儲的最大字符數(shù)長度，CHAR(20)和VARCHAR(20)將最多只能存儲20個字符，超過的字符將會被截掉。CHAR類型指定了n值之后，如果存入的字符數(shù)小于n，后面將會以空格補齊，查詢的時候再將末尾的空格去掉。內部存儲的機制不同。CHAR類型的字符串檢索速度要比VARCHAR類型的快。TEXT類型和BLOB類型TEXT和BLOB類型是對應的，不過存儲方式不同，TEXT是以文本方式存儲的，而BLOB是以二進制方式存儲的。如果存儲英文的話，TEXT區(qū)分大小寫，而BLOB不區(qū)分大小寫。TEXT可以指定字符集，BLOB不用指定字符集。4種TEXT類型見表6.4。名稱字符個數(shù)TINYTEXT最多255個字符TEXT最多65535個字符MEDIUMTEXT最多224-1個字符LONGTEXT最多232-1個字符TEXT類型和BLOB類型二進制類型是在數(shù)據(jù)庫中存儲二進制數(shù)據(jù)的數(shù)據(jù)類型，MySQL是用BLOB數(shù)據(jù)類型存儲這些數(shù)據(jù)的。BLOB有4種類型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB，每種類型的最大字節(jié)長度與對應的4種TEXT類型的最大字符數(shù)相同，見表6.4和表6.5。名稱字節(jié)長度TINYBLOB最多255個字節(jié)BLOB最多65535個字節(jié)（65KB）MEDIUMBLOB最多224-1個字節(jié)（16MB）LONGBLOB最多232-1個字節(jié)（4GB）BINARY類型和VARBINARY類型BINARY和VARBINARY數(shù)據(jù)類型類似于CHAR和VARCHAR類型。不同之處在于BINARY與VARBINARY以字節(jié)為存儲單位，而CHAR與VARCHAR以字符為存儲單位。BINARY(n)：固定n個字節(jié)二進制數(shù)據(jù)。n的取值范圍為1～255，默認為1。若輸出的字節(jié)長度小于n，則不足部分以0填充。BINARY(n)數(shù)據(jù)存儲的字節(jié)長度為n+4。VARBINARY(n)：n個字節(jié)變長二進制數(shù)據(jù)。n的取值范圍為1～65535，默認為1。VARBINARY(n)數(shù)據(jù)存儲的字節(jié)長度為實際長度+4。日期和時間類型日期和時間類型是為了方便在數(shù)據(jù)庫中存儲日期和時間而設計的。見表6.6。TIMESTAMP類型比較特殊，如果定義一個字段的類型為TIMESTAMP，這個字段的時間會在其他字段修改的時候自動刷新。這個數(shù)據(jù)類型的字段存放的是這條記錄最后被修改的時間，而不是真正的存放時間。名稱含義取值范圍YEAR年份，如'2014'1901～2155TIME時間，如'12:25:36'

DATE日期，如'2014-1-2''1000-01-01'～'9999-12-31'DATETIME日期和時間，如'2014-1-222:06:44'。日期、時間用空格隔開年份在1000～9999，不支持時區(qū)TIMESTAMP日期和時間，如'2014-1-222:06:44'年份在1970～2037，支持時區(qū)ENUM類型和SET類型ENUM類型和SET類型是比較特殊的字符串數(shù)據(jù)類型，它們的取值范圍是一個預先定義好的列表。被枚舉的值必須用單引號標注，不能為表達式或者一個變量估值。如果想用數(shù)值作為枚舉值，也必須用單引號標注。SET（集合）類型，其值同樣來自一個用逗號分隔的列表，最多可以有64個成員，可以選擇其中的０個或不限定的多個，占用存儲空間是1～8字節(jié)，由集合可能的成員數(shù)目決定。ENUM（枚舉）類型最多可以定義65535種不同的字符串，從中做出選擇時只能并且必須選擇其中一種；占用存儲空間是1個或2個字節(jié)，由枚舉值的數(shù)目決定。ENUM

（枚舉）SET（集合）如何選擇數(shù)據(jù)類型在MySQL中創(chuàng)建表時，需要考慮為字段選擇哪種數(shù)據(jù)類型是最合適的。選擇合適的數(shù)據(jù)類型會提高數(shù)據(jù)庫的使用效率。SMALLINT：存儲相對比較小的整數(shù)，如年齡、工齡和學分等。INT：存儲中等大小整數(shù)，如距離。BIGINT：存儲超大整數(shù)，如科學數(shù)據(jù)。FLOAT：存儲單精度的小的數(shù)據(jù)，如成績、溫度和測量值。DOUBLE：存儲雙精度的小數(shù)據(jù)，如科學數(shù)據(jù)。DECIMAL：以特別高的精度存儲小數(shù)據(jù)，如貨幣數(shù)額、單價和科學數(shù)據(jù)。CHAR：存儲通常包含預定義字符串的變量，如國家名稱、郵編和身份證號。VARCHAR：存儲不同長度的字符串值，如名字、商品名稱和密碼。TEXT：存儲大型文本數(shù)據(jù)，如新聞事件、產品描述和備注。如何選擇數(shù)據(jù)類型BLOB：存儲二進制數(shù)據(jù)，如圖片、聲音、附件和二進制文檔。YEAR：存儲年份，如畢業(yè)年、工作年和出生年。DATE：存儲日期，如生日和進貨日期。TIME：存儲時間或時間間隔，如開始/結束時間、兩時間之間的間隔。DATETIME：存儲包含日期和時間的數(shù)據(jù)，如事件提醒。TIMESTAMP：存儲即時時間，如當前時間、事件提醒器。ENUM：存儲字符屬性，只能從中選擇之一，如性別、布爾值。SET：存儲字符屬性，可從中選擇多個字符的聯(lián)合，如多項選擇業(yè)余愛好和興趣。數(shù)據(jù)類型的附加屬性在建表時，除了根據(jù)字段存儲的數(shù)據(jù)選擇合適的數(shù)據(jù)類型外，還可以附加相關的屬性。MySQL常見數(shù)據(jù)類型的屬性和含義見表