2026年3月4日上午MySQL 學習筆記第 34 節(jié)創(chuàng)建數(shù)據(jù)庫與運行各類 SQL。這句話如果放在以前我可能會覺得只是例行記錄但今天把它當標題認真寫下來其實是在提醒自己數(shù)據(jù)庫入門階段最容易忽略的恰恰是這些每天都要用的基礎操作。很多人裝完 MySQL 之后第一反應是趕緊去查“SELECT 怎么寫”“JOIN 怎么連”結果連數(shù)據(jù)庫都還沒建出來。今天上午我給自己定的任務很簡單也很扎實先創(chuàng)建一個 bookstore 數(shù)據(jù)庫再建一張 books 表插入幾條數(shù)據(jù)最后把增刪改查完整跑一遍。這條路徑走完SQL 的基本盤就穩(wěn)了。1. 動筆之前先把 SQL 的類型理清楚1.1 SQL 四大分類每一類到底在管什么剛開始學 SQL 的人最容易犯的毛病是“見什么敲什么”完全不看這句話屬于哪一類。實際開發(fā)中這條語句屬于哪一類決定了你的操作范圍、影響對象以及能不能回滾。DDLData Definition Language數(shù)據(jù)定義語言CREATE、ALTER、DROP、TRUNCATE。管的是表結構、數(shù)據(jù)庫結構這類元數(shù)據(jù)執(zhí)行后一般立即生效很多操作沒法簡單回滾。DMLData Manipulation Language數(shù)據(jù)操作語言INSERT、UPDATE、DELETE。管的是表里的數(shù)據(jù)是日常開發(fā)寫最多的語句。DQLData Query Language數(shù)據(jù)查詢語言SELECT。雖然也有資料把它歸到 DML 里但單獨拎出來更好理解它不改變數(shù)據(jù)。DCLData Control Language數(shù)據(jù)控制語言GRANT、REVOKE。管的是用戶權限。至于COMMIT、ROLLBACK這類事務控制有的資料單獨列為 TCL。我建議你在練習時先問自己一句這會改結構、改數(shù)據(jù)還是只是看數(shù)據(jù)答案不同對待方式也不同。改結構的語句要格外謹慎改數(shù)據(jù)的語句先確認 WHERE查數(shù)據(jù)的語句隨便練出錯最多浪費一點時間。1.2 學習路徑為什么要按“庫→表→數(shù)據(jù)→查詢”走數(shù)據(jù)庫里的一切操作都離不開一個前提你得先有庫和表。沒有庫你的 SQL 不知道該往哪執(zhí)行沒有表數(shù)據(jù)就沒有存放的容器。今天上午我安排的順序是建庫 → 建表 → 插數(shù) → 查詢 → 更新與刪除。每一步都是下一步的基礎。比如只有先建好表你才能談插入數(shù)據(jù)時字段類型是否匹配只有插入了幾行數(shù)據(jù)你才能驗證查詢條件到底寫沒寫對。這種遞進關系比單純背語法要牢固得多。提示學習階段可以大膽建庫刪庫但進入公司項目后不管是 DDL 還是 DML都要先想清楚影響范圍。尤其 DDL很多都沒有“撤銷”按鈕。2. 創(chuàng)建數(shù)據(jù)庫一條 CREATE DATABASE 背后的門道2.1 標準語法與每個參數(shù)的含義創(chuàng)建數(shù)據(jù)庫的語法很簡單不過簡單不代表可以隨便寫CREATE DATABASE [IF NOT EXISTS] bookstore CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;拆開看中括號里的IF NOT EXISTS表示如果同名庫已經存在就不報錯。第一次練習時我嫌它多余后來批量腳本跑多了才發(fā)現(xiàn)這個保護很有用腳本重復執(zhí)行不會中斷。bookstore是庫名。庫名最好用小寫字母、數(shù)字和下劃線別用減號、空格也不要跟 SQL 保留字撞車。CHARACTER SET utf8mb4指定字符集COLLATE utf8mb4_general_ci指定排序規(guī)則。CHARACTER SET和COLLATE其實可以不寫MySQL 會采用配置文件里的默認值。但我們剛學習我建議每次都寫出來。理由有兩個一是不依賴服務器環(huán)境換臺機器跑結果一樣二是以后從備份文件恢復數(shù)據(jù)庫時你靠SHOW CREATE DATABASE能一眼看出當初的設計意圖。2.2 為什么建議選 utf8mb4而不是直接寫 utf8這是今天上午我自己最容易踩的坑。很多新手看到utf8就以為萬事大吉結果 MySQL 里的utf8是utf8mb3每個字符最多 3 個字節(jié)存不了四字節(jié)的 emoji就連某些特殊漢字和生僻字也容易出問題。utf8mb4才是完整的 UTF-8 編碼每個字符最多 4 個字節(jié)能覆蓋 emoji、生僻字等更廣的字符集。所以從 5.5.3 開始引入后新項目基本都默認utf8mb4。MySQL 8.0 的默認值也已經是utf8mb4但這不代表你可以忽略它。排序規(guī)則可以簡單理解為字符比較和排序的方式。utf8mb4_general_ci里的ci是 case insensitive 的縮寫也就是大小寫不敏感適合中英文混合場景。如果你對排序規(guī)則有更高要求可以了解utf8mb4_unicode_ci和utf8mb4_0900_ai_ci但初學階段記住utf8mb4_general_ci已經足夠穩(wěn)妥。再提醒一個連帶影響因為utf8mb4每個字符最多占 4 字節(jié)所以一個被索引的VARCHAR(255)字段在舊版 InnoDB 里容易觸發(fā)“索引鍵太長”的錯誤Specified key was too long。設計字段長度時得把字符集因素算進去。2.3 查看、切換與修改數(shù)據(jù)庫的基本命令創(chuàng)建完不是結束至少要會三件事看、切、改。-- 查看所有數(shù)據(jù)庫 SHOW DATABASES; -- 查看庫的創(chuàng)建語句看字符集和排序規(guī)則 SHOW CREATE DATABASE bookstore; -- 切換到目標庫 USE bookstore; -- 修改庫的字符集 ALTER DATABASE bookstore CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;SHOW DATABASES輸出的結果里除了剛建的bookstore還有 MySQL 自帶的幾個系統(tǒng)庫像information_schema、mysql、performance_schema這些不建議亂動。USE這條命令很關鍵。很多人寫完CREATE DATABASE后直接在下一行敲建表語句結果報No database selected原因就是沒切庫。切庫之后你后續(xù)的建表、插數(shù)、查詢才都能落在bookstore里。至于ALTER DATABASE它修改的是庫級別的字符集和排序規(guī)則不影響已經建好的表。想要讓已存在的表也改過來需要單獨對每張表執(zhí)行ALTER TABLE。這個細節(jié)我后面還會提到先記上一筆。3. 建表實戰(zhàn)把字段類型和約束一次弄明白3.1 常用數(shù)據(jù)類型怎么選才不容易翻車建表之前先看表結構。今天我用了一張極簡的圖書表字段設計如下。字段名類型說明idINT UNSIGNED主鍵自增book_nameVARCHAR(100)書名不可為空authorVARCHAR(50)作者默認“佚名”priceDECIMAL(10,2)定價精確到分publish_dateDATE出版日期可為空stockINT UNSIGNED庫存數(shù)量默認 0created_atDATETIME入庫時間默認當前時間選型要點整數(shù)優(yōu)先看范圍。TINYINT占 1 字節(jié)INT占 4 字節(jié)BIGINT占 8 字節(jié)。用戶數(shù)、商品數(shù)這類數(shù)據(jù)用INT UNSIGNED通常就夠無符號范圍能到 42 億左右不要一上來就BIGINT沒必要地浪費存儲空間。價格不用FLOAT或DOUBLE因為浮點數(shù)在二進制存儲里有精度誤差0.1 加 0.2 不一定等于 0.3。涉及錢用DECIMAL(10,2)總位數(shù) 10小數(shù)位 2最大能表示 99999999.99覆蓋絕大多數(shù)定價場景。日期要區(qū)分DATE、DATETIME和TIMESTAMP。DATE只存年月日DATETIME存年月日時分秒范圍更大TIMESTAMP受數(shù)據(jù)庫時區(qū)影響。業(yè)務表里記錄創(chuàng)建時間用DATETIME比較多。3.2 一條完整的建表語句拆解基于上面的字段設計完整建表語句如下CREATE TABLE IF NOT EXISTS books ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主鍵ID, book_name VARCHAR(100) NOT NULL COMMENT 書名, author VARCHAR(50) NOT NULL DEFAULT 佚名 COMMENT 作者, price DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 定價, publish_date DATE DEFAULT NULL COMMENT 出版日期, stock INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 庫存數(shù)量, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 入庫時間, PRIMARY KEY (id), UNIQUE KEY uk_book_name_author (book_name, author) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT圖書信息表;幾個容易忽略的細節(jié)NOT NULL說的是字段值不能為空。為什么book_name要非空因為一本書如果連名字都沒有這條數(shù)據(jù)基本沒有意義。DEFAULT說的是插入時不顯式給值用什么默認值。這里給author設了默認“佚名”給price設了默認 0給stock設了默認 0這樣后期插入時可以只填關鍵字段。AUTO_INCREMENT必須配合索引使用通常就是主鍵。每次插入時不給 id它會自動加 1。不過要留意事務回滾之后自增值可能不連續(xù)這不影響業(yè)務判斷。COMMENT是注釋。給字段加注釋剛開始覺得啰嗦三個月后回頭看能幫你省下大量回憶時間。唯一聯(lián)合索引uk_book_name_author約束“書名 作者”不能重復防止不小心插兩條一模一樣的記錄。3.3 約束與存儲引擎建表時就要想清楚建表語句末尾寫了ENGINEInnoDB。InnoDB 是 MySQL 5.5 之后的默認存儲引擎支持事務、行級鎖和外鍵。早期教材里常出現(xiàn)的 MyISAM 不支持事務也不支持外鍵現(xiàn)在做業(yè)務系統(tǒng)基本可以不考慮。約束里的主鍵、唯一鍵、非空、默認值我建議在建表時就一起寫好不要等數(shù)據(jù)多了再回頭補。原因很簡單約束是數(shù)據(jù)庫層面的護欄。沒有唯一約束靠程序去判斷重復總會有漏網之魚沒有非空約束寫入的臟數(shù)據(jù)就很難從源頭攔住。建表成功后可以用下面三條命令確認表結構和創(chuàng)建信息SHOW TABLES; DESC books; SHOW CREATE TABLE books;DESC是DESCRIBE的簡寫查看字段名、類型、是否允許為空、鍵信息。做練習時我每建完一張表都會先DESC看一眼再去拼 SQL 語句免得類型想錯。4. 增刪改查實戰(zhàn)INSERT / SELECT / UPDATE / DELETE 的完整跑法4.1 插入數(shù)據(jù)單行插入與多行批量插入表建好之后先插幾條數(shù)據(jù)。插入語法不難但要小心字段列表和值的順序一一對應。INSERT INTO books (book_name, author, price, publish_date, stock) VALUES (三體, 劉慈欣, 39.50, 2008-01-01, 100); INSERT INTO books (book_name, author, price, publish_date, stock) VALUES (圍城, 錢鐘書, 29.00, 1991-02-01, 50), (活著, 余華, 25.00, 2012-08-01, 80);第一條是單行插入第二條是多行插入。多行插入的好處很明顯一次網絡往返效率比一行一行插高很多。數(shù)據(jù)量上來以后比如初始化一批測試數(shù)據(jù)用多行VALUES是最省事的方式。注意我特意沒有在主鍵id和created_at上給值。id由自增生成created_at由CURRENT_TIMESTAMP默認值填充。這樣以后寫程序時這兩個字段基本不用管。如果插入時漏了某個非空字段且該字段沒有默認值MySQL 會報Field xxx doesnt have a default value。遇到這個錯誤先別急著改表多數(shù)原因是你沒把插入的字段列表寫全。4.2 從 SELECT 到 WHERE、ORDER BY、LIMIT 的查詢套路查詢是 SQL 使用頻率最高的一類也是你驗證前面建表是否正確的手段。-- 查全部 SELECT * FROM books; -- 指定字段按價格從高到低 SELECT id, book_name, price FROM books ORDER BY price DESC; -- 帶條件查詢 SELECT id, book_name, price FROM books WHERE price 30 ORDER BY price DESC; -- 分頁查詢 SELECT id, book_name, price FROM books WHERE price 20 ORDER BY price DESC LIMIT 10;WHERE后面寫篩選條件ORDER BY控制排序LIMIT控制返回行數(shù)。三者加起來已經能覆蓋大多數(shù)簡單查詢場景。我說一下執(zhí)行順序并不是先 SELECT 再 WHERE。大概的順序是先找表FROM再過濾WHERE再分組GROUP BY再過濾分組結果HAVING再排序ORDER BY最后限制行數(shù)LIMIT。把這個順序搞明白你就知道為什么 WHERE 里不能直接用聚合函數(shù)別名而 ORDER BY 可以。還有一個容易忽略的組合LIMIT的偏移量。LIMIT 10表示最多返回 10 行LIMIT 10, 20表示跳過前 10 行取 20 行。這個語法在不同數(shù)據(jù)庫里寫法不一樣但 MySQL 里就是這么記的。如果想順手算點數(shù)據(jù)可以加計算列SELECT book_name, price, stock, price * stock AS inventory_value FROM books;inventory_value是計算出來的庫存價值不影響表結構。這種“查詢時臨時計算”的寫法很常用。4.3 聚合查詢COUNT / SUM / AVG / GROUP BY 的基本用法建表后數(shù)據(jù)不多但聚合查詢的習慣要早養(yǎng)成。SELECT author, COUNT(*) AS book_count, AVG(price) AS avg_price FROM books GROUP BY author ORDER BY book_count DESC;這句的意思是按作者分組統(tǒng)計每個作者的圖書數(shù)量和平均價格。AS是給查詢結果起別名方便閱讀。GROUP BY之后SELECT 里能出現(xiàn)的非聚合列一般只能是分組列不然語義會亂。這個規(guī)則一開始我不適應踩了幾次語法報錯后才理解。如果要對分組后的結果再篩選用HAVING比如只保留有 2 本及以上圖書的作者SELECT author, COUNT(*) AS book_count FROM books GROUP BY author HAVING COUNT(*) 2;WHERE篩選的是原始行HAVING篩選的是分組后的結果兩者不要用混。想統(tǒng)計總數(shù)、最大值、最小值對應SUM、MAX、MIN思路和上面一樣。4.4 更新與刪除動手之前先把 WHERE 看清楚更新數(shù)據(jù)的寫法核心是 SET 后面寫要改的字段WHERE 后面寫范圍。UPDATE books SET stock stock - 1 WHERE id 1;這句模擬賣出一本《三體》庫存減 1。寫SET stock stock - 1而不是SET stock 99更符合真實業(yè)務。你也應該特別注意如果 UPDATE 忘寫 WHERE整張表的對應字段都會被更新。這大概是我見過最貴的一行命令。刪除數(shù)據(jù)也一樣DELETE FROM books WHERE id 2;刪除前我的習慣是先用同條件 SELECT 看一下SELECT * FROM books WHERE id 2;確認這條確實是你要刪的再把 SELECT 替換成 DELETE。多花一秒鐘能避免刪錯行。DELETE只是刪數(shù)據(jù)自增計數(shù)器不會重置。想清空整表并重置自增用TRUNCATE TABLE books;但它是 DDL執(zhí)行前更要想清楚而且不能按條件刪只能整表清空。5. 高頻報錯與實戰(zhàn)排查這些坑我基本都踩過5.1 常見的語法與結構報錯學習階段最容易遇到的錯誤整理成一張速查表。報錯信息常見原因處理方法ERROR 1049數(shù)據(jù)庫不存在先用 SHOW DATABASES 確認庫名ERROR 1050表已存在加 IF NOT EXISTS或 DROP 后重建ERROR 1062唯一索引沖突檢查重復字段改為更新或換數(shù)據(jù)ERROR 1064SQL 語法錯誤逐字檢查關鍵字、逗號、引號ERROR 1366字符集或編碼不匹配確認連接字符集和表字符集一致No database selected沒切庫執(zhí)行 USE 庫名最常犯的還是ERROR 1064。比如把保留字order當字段名又不加反引號MySQL 就會報語法錯。遇到這種我第一個動作是看報錯位置前后 5 個字符八成是拼寫或符號問題。另外一個是環(huán)境差異問題Windows 和 Linux 下表名大小寫是否敏感不同。MySQL 在 Windows 上默認不區(qū)分表名大小寫Linux 上默認區(qū)分所以換環(huán)境部署時經常莫名其妙找不到表。項目里約定統(tǒng)一小寫表名能少很多麻煩。5.2 中文亂碼十有八九是字符集鏈路沒打通創(chuàng)建數(shù)據(jù)庫時特意指定了utf8mb4但如果客戶端連接字符集不對查詢中文依然會亂碼。排查思路是這樣表字符集、連接字符集、客戶端顯示字符集三段都要一致。命令行客戶端可以先執(zhí)行SET NAMES utf8mb4;這條命令相當于同時設置了character_set_client、character_set_connection和character_set_results。執(zhí)行完再跑 SELECT中文通常就正常。圖形化工具里一般都有連接字符集選項默認選中utf8mb4即可。如果已經建好表但數(shù)據(jù)亂碼先備份數(shù)據(jù)再改表字符集最后重新導入。注意單純的ALTER TABLE books CONVERT TO CHARACTER SET utf8mb4;會修改列字符集但不一定能自動修復已經亂碼的數(shù)據(jù)。亂碼問題最好的對策是創(chuàng)建庫表時堅決寫清楚字符集而不是事后補救。5.3 安全底線SQL 注入與最小權限寫 SQL 的時候腦子里要一直有一根安全弦。SQL 注入攻擊依然是 Web 應用最大的風險之一網上相關的搜索熱度一直很高。作為開發(fā)人員正確的做法是和數(shù)據(jù)庫交互時不拼接 SQL 字符串使用參數(shù)化查詢或預編譯語句賬號權限最小化只給業(yè)務所需的增刪改查權限敏感數(shù)據(jù)不要在日志里明文記錄。至于“萬能密碼繞過登錄”這類思路屬于危害系統(tǒng)安全的行為不應該去研究更不應該用在任何項目里。把自己的程序做安全才是對用戶負責。排查問題的順序我建議是“先結構、再字符集、再權限”不要一上來就懷疑數(shù)據(jù)庫壞了。多數(shù)時候是低級錯誤比如庫名寫錯、少了個括號、忘記切庫。把每一步執(zhí)行結果看清楚比瞎猜快得多。說回到今天上午這 34 節(jié)筆記。我以前圖快建表時連字符集都懶得指定后面吃了不少苦頭?，F(xiàn)在養(yǎng)成兩個習慣任何庫、任何表創(chuàng)建語句一定完整寫清楚字符集、排序規(guī)則和注釋任何 UPDATE 和 DELETE 之前先跑一條同條件的 SELECT 確認目標。這兩個習慣讓我少走了很多彎路。希望這節(jié)筆記對正在學 MySQL 的你也有同樣的作用。 MySQL入門：從建庫到增刪改查的完整SQL實踐與避坑指南
2026年3月4日上午MySQL 學習筆記第 34 節(jié)創(chuàng)建數(shù)據(jù)庫與運行各類 SQL。這句話如果放在以前我可能會覺得只是例行記錄但今天把它當標題認真寫下來其實是在提醒自己數(shù)據(jù)庫入門階段最容易忽略的恰恰是這些每天都要用的基礎操作。很多人裝完 MySQL 之后第一反應是趕緊去查“SELECT 怎么寫”“JOIN 怎么連”結果連數(shù)據(jù)庫都還沒建出來。今天上午我給自己定的任務很簡單也很扎實先創(chuàng)建一個 bookstore 數(shù)據(jù)庫再建一張 books 表插入幾條數(shù)據(jù)最后把增刪改查完整跑一遍。這條路徑走完SQL 的基本盤就穩(wěn)了。1. 動筆之前先把 SQL 的類型理清楚1.1 SQL 四大分類每一類到底在管什么剛開始學 SQL 的人最容易犯的毛病是“見什么敲什么”完全不看這句話屬于哪一類。實際開發(fā)中這條語句屬于哪一類決定了你的操作范圍、影響對象以及能不能回滾。DDLData Definition Language數(shù)據(jù)定義語言CREATE、ALTER、DROP、TRUNCATE。管的是表結構、數(shù)據(jù)庫結構這類元數(shù)據(jù)執(zhí)行后一般立即生效很多操作沒法簡單回滾。DMLData Manipulation Language數(shù)據(jù)操作語言INSERT、UPDATE、DELETE。管的是表里的數(shù)據(jù)是日常開發(fā)寫最多的語句。DQLData Query Language數(shù)據(jù)查詢語言SELECT。雖然也有資料把它歸到 DML 里但單獨拎出來更好理解它不改變數(shù)據(jù)。DCLData Control Language數(shù)據(jù)控制語言GRANT、REVOKE。管的是用戶權限。至于COMMIT、ROLLBACK這類事務控制有的資料單獨列為 TCL。我建議你在練習時先問自己一句這會改結構、改數(shù)據(jù)還是只是看數(shù)據(jù)答案不同對待方式也不同。改結構的語句要格外謹慎改數(shù)據(jù)的語句先確認 WHERE查數(shù)據(jù)的語句隨便練出錯最多浪費一點時間。1.2 學習路徑為什么要按“庫→表→數(shù)據(jù)→查詢”走數(shù)據(jù)庫里的一切操作都離不開一個前提你得先有庫和表。沒有庫你的 SQL 不知道該往哪執(zhí)行沒有表數(shù)據(jù)就沒有存放的容器。今天上午我安排的順序是建庫 → 建表 → 插數(shù) → 查詢 → 更新與刪除。每一步都是下一步的基礎。比如只有先建好表你才能談插入數(shù)據(jù)時字段類型是否匹配只有插入了幾行數(shù)據(jù)你才能驗證查詢條件到底寫沒寫對。這種遞進關系比單純背語法要牢固得多。提示學習階段可以大膽建庫刪庫但進入公司項目后不管是 DDL 還是 DML都要先想清楚影響范圍。尤其 DDL很多都沒有“撤銷”按鈕。2. 創(chuàng)建數(shù)據(jù)庫一條 CREATE DATABASE 背后的門道2.1 標準語法與每個參數(shù)的含義創(chuàng)建數(shù)據(jù)庫的語法很簡單不過簡單不代表可以隨便寫CREATE DATABASE [IF NOT EXISTS] bookstore CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;拆開看中括號里的IF NOT EXISTS表示如果同名庫已經存在就不報錯。第一次練習時我嫌它多余后來批量腳本跑多了才發(fā)現(xiàn)這個保護很有用腳本重復執(zhí)行不會中斷。bookstore是庫名。庫名最好用小寫字母、數(shù)字和下劃線別用減號、空格也不要跟 SQL 保留字撞車。CHARACTER SET utf8mb4指定字符集COLLATE utf8mb4_general_ci指定排序規(guī)則。CHARACTER SET和COLLATE其實可以不寫MySQL 會采用配置文件里的默認值。但我們剛學習我建議每次都寫出來。理由有兩個一是不依賴服務器環(huán)境換臺機器跑結果一樣二是以后從備份文件恢復數(shù)據(jù)庫時你靠SHOW CREATE DATABASE能一眼看出當初的設計意圖。2.2 為什么建議選 utf8mb4而不是直接寫 utf8這是今天上午我自己最容易踩的坑。很多新手看到utf8就以為萬事大吉結果 MySQL 里的utf8是utf8mb3每個字符最多 3 個字節(jié)存不了四字節(jié)的 emoji就連某些特殊漢字和生僻字也容易出問題。utf8mb4才是完整的 UTF-8 編碼每個字符最多 4 個字節(jié)能覆蓋 emoji、生僻字等更廣的字符集。所以從 5.5.3 開始引入后新項目基本都默認utf8mb4。MySQL 8.0 的默認值也已經是utf8mb4但這不代表你可以忽略它。排序規(guī)則可以簡單理解為字符比較和排序的方式。utf8mb4_general_ci里的ci是 case insensitive 的縮寫也就是大小寫不敏感適合中英文混合場景。如果你對排序規(guī)則有更高要求可以了解utf8mb4_unicode_ci和utf8mb4_0900_ai_ci但初學階段記住utf8mb4_general_ci已經足夠穩(wěn)妥。再提醒一個連帶影響因為utf8mb4每個字符最多占 4 字節(jié)所以一個被索引的VARCHAR(255)字段在舊版 InnoDB 里容易觸發(fā)“索引鍵太長”的錯誤Specified key was too long。設計字段長度時得把字符集因素算進去。2.3 查看、切換與修改數(shù)據(jù)庫的基本命令創(chuàng)建完不是結束至少要會三件事看、切、改。-- 查看所有數(shù)據(jù)庫 SHOW DATABASES; -- 查看庫的創(chuàng)建語句看字符集和排序規(guī)則 SHOW CREATE DATABASE bookstore; -- 切換到目標庫 USE bookstore; -- 修改庫的字符集 ALTER DATABASE bookstore CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;SHOW DATABASES輸出的結果里除了剛建的bookstore還有 MySQL 自帶的幾個系統(tǒng)庫像information_schema、mysql、performance_schema這些不建議亂動。USE這條命令很關鍵。很多人寫完CREATE DATABASE后直接在下一行敲建表語句結果報No database selected原因就是沒切庫。切庫之后你后續(xù)的建表、插數(shù)、查詢才都能落在bookstore里。至于ALTER DATABASE它修改的是庫級別的字符集和排序規(guī)則不影響已經建好的表。想要讓已存在的表也改過來需要單獨對每張表執(zhí)行ALTER TABLE。這個細節(jié)我后面還會提到先記上一筆。3. 建表實戰(zhàn)把字段類型和約束一次弄明白3.1 常用數(shù)據(jù)類型怎么選才不容易翻車建表之前先看表結構。今天我用了一張極簡的圖書表字段設計如下。字段名類型說明idINT UNSIGNED主鍵自增book_nameVARCHAR(100)書名不可為空authorVARCHAR(50)作者默認“佚名”priceDECIMAL(10,2)定價精確到分publish_dateDATE出版日期可為空stockINT UNSIGNED庫存數(shù)量默認 0created_atDATETIME入庫時間默認當前時間選型要點整數(shù)優(yōu)先看范圍。TINYINT占 1 字節(jié)INT占 4 字節(jié)BIGINT占 8 字節(jié)。用戶數(shù)、商品數(shù)這類數(shù)據(jù)用INT UNSIGNED通常就夠無符號范圍能到 42 億左右不要一上來就BIGINT沒必要地浪費存儲空間。價格不用FLOAT或DOUBLE因為浮點數(shù)在二進制存儲里有精度誤差0.1 加 0.2 不一定等于 0.3。涉及錢用DECIMAL(10,2)總位數(shù) 10小數(shù)位 2最大能表示 99999999.99覆蓋絕大多數(shù)定價場景。日期要區(qū)分DATE、DATETIME和TIMESTAMP。DATE只存年月日DATETIME存年月日時分秒范圍更大TIMESTAMP受數(shù)據(jù)庫時區(qū)影響。業(yè)務表里記錄創(chuàng)建時間用DATETIME比較多。3.2 一條完整的建表語句拆解基于上面的字段設計完整建表語句如下CREATE TABLE IF NOT EXISTS books ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主鍵ID, book_name VARCHAR(100) NOT NULL COMMENT 書名, author VARCHAR(50) NOT NULL DEFAULT 佚名 COMMENT 作者, price DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 定價, publish_date DATE DEFAULT NULL COMMENT 出版日期, stock INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 庫存數(shù)量, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 入庫時間, PRIMARY KEY (id), UNIQUE KEY uk_book_name_author (book_name, author) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT圖書信息表;幾個容易忽略的細節(jié)NOT NULL說的是字段值不能為空。為什么book_name要非空因為一本書如果連名字都沒有這條數(shù)據(jù)基本沒有意義。DEFAULT說的是插入時不顯式給值用什么默認值。這里給author設了默認“佚名”給price設了默認 0給stock設了默認 0這樣后期插入時可以只填關鍵字段。AUTO_INCREMENT必須配合索引使用通常就是主鍵。每次插入時不給 id它會自動加 1。不過要留意事務回滾之后自增值可能不連續(xù)這不影響業(yè)務判斷。COMMENT是注釋。給字段加注釋剛開始覺得啰嗦三個月后回頭看能幫你省下大量回憶時間。唯一聯(lián)合索引uk_book_name_author約束“書名 作者”不能重復防止不小心插兩條一模一樣的記錄。3.3 約束與存儲引擎建表時就要想清楚建表語句末尾寫了ENGINEInnoDB。InnoDB 是 MySQL 5.5 之后的默認存儲引擎支持事務、行級鎖和外鍵。早期教材里常出現(xiàn)的 MyISAM 不支持事務也不支持外鍵現(xiàn)在做業(yè)務系統(tǒng)基本可以不考慮。約束里的主鍵、唯一鍵、非空、默認值我建議在建表時就一起寫好不要等數(shù)據(jù)多了再回頭補。原因很簡單約束是數(shù)據(jù)庫層面的護欄。沒有唯一約束靠程序去判斷重復總會有漏網之魚沒有非空約束寫入的臟數(shù)據(jù)就很難從源頭攔住。建表成功后可以用下面三條命令確認表結構和創(chuàng)建信息SHOW TABLES; DESC books; SHOW CREATE TABLE books;DESC是DESCRIBE的簡寫查看字段名、類型、是否允許為空、鍵信息。做練習時我每建完一張表都會先DESC看一眼再去拼 SQL 語句免得類型想錯。4. 增刪改查實戰(zhàn)INSERT / SELECT / UPDATE / DELETE 的完整跑法4.1 插入數(shù)據(jù)單行插入與多行批量插入表建好之后先插幾條數(shù)據(jù)。插入語法不難但要小心字段列表和值的順序一一對應。INSERT INTO books (book_name, author, price, publish_date, stock) VALUES (三體, 劉慈欣, 39.50, 2008-01-01, 100); INSERT INTO books (book_name, author, price, publish_date, stock) VALUES (圍城, 錢鐘書, 29.00, 1991-02-01, 50), (活著, 余華, 25.00, 2012-08-01, 80);第一條是單行插入第二條是多行插入。多行插入的好處很明顯一次網絡往返效率比一行一行插高很多。數(shù)據(jù)量上來以后比如初始化一批測試數(shù)據(jù)用多行VALUES是最省事的方式。注意我特意沒有在主鍵id和created_at上給值。id由自增生成created_at由CURRENT_TIMESTAMP默認值填充。這樣以后寫程序時這兩個字段基本不用管。如果插入時漏了某個非空字段且該字段沒有默認值MySQL 會報Field xxx doesnt have a default value。遇到這個錯誤先別急著改表多數(shù)原因是你沒把插入的字段列表寫全。4.2 從 SELECT 到 WHERE、ORDER BY、LIMIT 的查詢套路查詢是 SQL 使用頻率最高的一類也是你驗證前面建表是否正確的手段。-- 查全部 SELECT * FROM books; -- 指定字段按價格從高到低 SELECT id, book_name, price FROM books ORDER BY price DESC; -- 帶條件查詢 SELECT id, book_name, price FROM books WHERE price 30 ORDER BY price DESC; -- 分頁查詢 SELECT id, book_name, price FROM books WHERE price 20 ORDER BY price DESC LIMIT 10;WHERE后面寫篩選條件ORDER BY控制排序LIMIT控制返回行數(shù)。三者加起來已經能覆蓋大多數(shù)簡單查詢場景。我說一下執(zhí)行順序并不是先 SELECT 再 WHERE。大概的順序是先找表FROM再過濾WHERE再分組GROUP BY再過濾分組結果HAVING再排序ORDER BY最后限制行數(shù)LIMIT。把這個順序搞明白你就知道為什么 WHERE 里不能直接用聚合函數(shù)別名而 ORDER BY 可以。還有一個容易忽略的組合LIMIT的偏移量。LIMIT 10表示最多返回 10 行LIMIT 10, 20表示跳過前 10 行取 20 行。這個語法在不同數(shù)據(jù)庫里寫法不一樣但 MySQL 里就是這么記的。如果想順手算點數(shù)據(jù)可以加計算列SELECT book_name, price, stock, price * stock AS inventory_value FROM books;inventory_value是計算出來的庫存價值不影響表結構。這種“查詢時臨時計算”的寫法很常用。4.3 聚合查詢COUNT / SUM / AVG / GROUP BY 的基本用法建表后數(shù)據(jù)不多但聚合查詢的習慣要早養(yǎng)成。SELECT author, COUNT(*) AS book_count, AVG(price) AS avg_price FROM books GROUP BY author ORDER BY book_count DESC;這句的意思是按作者分組統(tǒng)計每個作者的圖書數(shù)量和平均價格。AS是給查詢結果起別名方便閱讀。GROUP BY之后SELECT 里能出現(xiàn)的非聚合列一般只能是分組列不然語義會亂。這個規(guī)則一開始我不適應踩了幾次語法報錯后才理解。如果要對分組后的結果再篩選用HAVING比如只保留有 2 本及以上圖書的作者SELECT author, COUNT(*) AS book_count FROM books GROUP BY author HAVING COUNT(*) 2;WHERE篩選的是原始行HAVING篩選的是分組后的結果兩者不要用混。想統(tǒng)計總數(shù)、最大值、最小值對應SUM、MAX、MIN思路和上面一樣。4.4 更新與刪除動手之前先把 WHERE 看清楚更新數(shù)據(jù)的寫法核心是 SET 后面寫要改的字段WHERE 后面寫范圍。UPDATE books SET stock stock - 1 WHERE id 1;這句模擬賣出一本《三體》庫存減 1。寫SET stock stock - 1而不是SET stock 99更符合真實業(yè)務。你也應該特別注意如果 UPDATE 忘寫 WHERE整張表的對應字段都會被更新。這大概是我見過最貴的一行命令。刪除數(shù)據(jù)也一樣DELETE FROM books WHERE id 2;刪除前我的習慣是先用同條件 SELECT 看一下SELECT * FROM books WHERE id 2;確認這條確實是你要刪的再把 SELECT 替換成 DELETE。多花一秒鐘能避免刪錯行。DELETE只是刪數(shù)據(jù)自增計數(shù)器不會重置。想清空整表并重置自增用TRUNCATE TABLE books;但它是 DDL執(zhí)行前更要想清楚而且不能按條件刪只能整表清空。5. 高頻報錯與實戰(zhàn)排查這些坑我基本都踩過5.1 常見的語法與結構報錯學習階段最容易遇到的錯誤整理成一張速查表。報錯信息常見原因處理方法ERROR 1049數(shù)據(jù)庫不存在先用 SHOW DATABASES 確認庫名ERROR 1050表已存在加 IF NOT EXISTS或 DROP 后重建ERROR 1062唯一索引沖突檢查重復字段改為更新或換數(shù)據(jù)ERROR 1064SQL 語法錯誤逐字檢查關鍵字、逗號、引號ERROR 1366字符集或編碼不匹配確認連接字符集和表字符集一致No database selected沒切庫執(zhí)行 USE 庫名最常犯的還是ERROR 1064。比如把保留字order當字段名又不加反引號MySQL 就會報語法錯。遇到這種我第一個動作是看報錯位置前后 5 個字符八成是拼寫或符號問題。另外一個是環(huán)境差異問題Windows 和 Linux 下表名大小寫是否敏感不同。MySQL 在 Windows 上默認不區(qū)分表名大小寫Linux 上默認區(qū)分所以換環(huán)境部署時經常莫名其妙找不到表。項目里約定統(tǒng)一小寫表名能少很多麻煩。5.2 中文亂碼十有八九是字符集鏈路沒打通創(chuàng)建數(shù)據(jù)庫時特意指定了utf8mb4但如果客戶端連接字符集不對查詢中文依然會亂碼。排查思路是這樣表字符集、連接字符集、客戶端顯示字符集三段都要一致。命令行客戶端可以先執(zhí)行SET NAMES utf8mb4;這條命令相當于同時設置了character_set_client、character_set_connection和character_set_results。執(zhí)行完再跑 SELECT中文通常就正常。圖形化工具里一般都有連接字符集選項默認選中utf8mb4即可。如果已經建好表但數(shù)據(jù)亂碼先備份數(shù)據(jù)再改表字符集最后重新導入。注意單純的ALTER TABLE books CONVERT TO CHARACTER SET utf8mb4;會修改列字符集但不一定能自動修復已經亂碼的數(shù)據(jù)。亂碼問題最好的對策是創(chuàng)建庫表時堅決寫清楚字符集而不是事后補救。5.3 安全底線SQL 注入與最小權限寫 SQL 的時候腦子里要一直有一根安全弦。SQL 注入攻擊依然是 Web 應用最大的風險之一網上相關的搜索熱度一直很高。作為開發(fā)人員正確的做法是和數(shù)據(jù)庫交互時不拼接 SQL 字符串使用參數(shù)化查詢或預編譯語句賬號權限最小化只給業(yè)務所需的增刪改查權限敏感數(shù)據(jù)不要在日志里明文記錄。至于“萬能密碼繞過登錄”這類思路屬于危害系統(tǒng)安全的行為不應該去研究更不應該用在任何項目里。把自己的程序做安全才是對用戶負責。排查問題的順序我建議是“先結構、再字符集、再權限”不要一上來就懷疑數(shù)據(jù)庫壞了。多數(shù)時候是低級錯誤比如庫名寫錯、少了個括號、忘記切庫。把每一步執(zhí)行結果看清楚比瞎猜快得多。說回到今天上午這 34 節(jié)筆記。我以前圖快建表時連字符集都懶得指定后面吃了不少苦頭?，F(xiàn)在養(yǎng)成兩個習慣任何庫、任何表創(chuàng)建語句一定完整寫清楚字符集、排序規(guī)則和注釋任何 UPDATE 和 DELETE 之前先跑一條同條件的 SELECT 確認目標。這兩個習慣讓我少走了很多彎路。希望這節(jié)筆記對正在學 MySQL 的你也有同樣的作用。