今天把MySQL學(xué)習(xí)筆記推到第34節(jié)。上午的內(nèi)容圍繞兩件事創(chuàng)建數(shù)據(jù)庫、運(yùn)行各類SQL。聽起來是入門操作但真正動(dòng)手會(huì)發(fā)現(xiàn)里面藏著不少值得展開的細(xì)節(jié)比如字符集怎么選、SQL按類型怎么劃分、一條報(bào)錯(cuò)怎么一步步查到根因。這篇文章就以學(xué)習(xí)筆記的形式整理出來適合剛裝好MySQL、還沒系統(tǒng)跑過SQL的同學(xué)也適合想快速復(fù)習(xí)建庫語法的老手。開始之前先交代一下環(huán)境我在本機(jī)用的是MySQL 8.0版本操作系統(tǒng)是Ubuntu。SQL標(biāo)準(zhǔn)本身是通用的但MySQL在函數(shù)、關(guān)鍵字和存儲(chǔ)引擎上有自己的實(shí)現(xiàn)所以學(xué)習(xí)時(shí)要結(jié)合具體版本。1. 環(huán)境準(zhǔn)備先把連接搞定再談建庫1.1 命令行登錄那些參數(shù)安裝完MySQL之后第一件事不是建庫而是確保能穩(wěn)定連上服務(wù)器。最常用的登錄方式是在終端執(zhí)行mysql -u root -p回車后輸入密碼就能進(jìn)入交互式命令行。這里有幾個(gè)參數(shù)值得說明-u指定用戶名缺省是當(dāng)前系統(tǒng)用戶-p提示輸入密碼注意是小寫-h指定主機(jī)默認(rèn)是localhost-P指定端口默認(rèn)是3306注意是大寫如果只在本機(jī)學(xué)習(xí)-h和-P可以不帶。但當(dāng)要連遠(yuǎn)程服務(wù)器時(shí)就得寫完整mysql -h 192.168.1.101 -P 3306 -u root -p有個(gè)坑我印象很深第一次遠(yuǎn)程連數(shù)據(jù)庫時(shí)我把端口參數(shù)寫成了小寫-p結(jié)果MySQL認(rèn)為-p后面是密碼于是提示“Access denied”白白折騰了十幾分鐘。后來才記住-p是密碼-P才是端口。如果連接時(shí)遇到SSL協(xié)議相關(guān)報(bào)錯(cuò)可以先臨時(shí)加參數(shù)mysql -u root -p --skip-ssl用這個(gè)方式排除是不是證書配置導(dǎo)致的問題。生產(chǎn)環(huán)境不能這么干但在學(xué)習(xí)環(huán)境里排查問題很實(shí)用。1.2 圖形化客戶端怎么選命令行適合學(xué)習(xí)和寫腳本但日?？磾?shù)據(jù)、改記錄圖形化工具效率更高。常見的MySQL客戶端有MySQL Workbench、DBeaver、Navicat。我個(gè)人常用DBeaver開源免費(fèi)、跨平臺(tái)、支持多種數(shù)據(jù)庫。Navicat功能也很完善但那是商業(yè)軟件想長期用就買授權(quán)別去找什么激活碼正版試用期足夠做評(píng)估。不管選哪個(gè)工具底層邏輯都是一樣的無論你在圖形界面怎么點(diǎn)最終發(fā)送到MySQL服務(wù)器上的仍然是一條條SQL。所以工具只是加速器語法才是基本功。這也是第34節(jié)把“運(yùn)行各類SQL”單獨(dú)拎出來的原因。1.3 確認(rèn)版本和運(yùn)行模式登錄之后建議先跑一句SELECT VERSION();這能快速確認(rèn)當(dāng)前MySQL版本。版本差異會(huì)影響很多細(xì)節(jié)MySQL 8.0的默認(rèn)字符集是utf8mb45.7則是utf8mb3不同版本的語法兼容性也不一樣。照著不同版本的教程敲命令時(shí)遇到奇怪報(bào)錯(cuò)先看版本再排查。還可以查看服務(wù)器默認(rèn)字符集SHOW VARIABLES LIKE character_set_server;如果這個(gè)值是utf8mb4就可以放心存中文和emoji。如果不是后續(xù)建庫時(shí)就要在CREATE DATABASE語句里顯式指定。2. 創(chuàng)建數(shù)據(jù)庫不在建庫這一步翻車2.1 CREATE DATABASE 語法細(xì)節(jié)創(chuàng)建數(shù)據(jù)庫的SQL核心就一句話CREATE DATABASE mydb;為了腳本可以重復(fù)執(zhí)行通常加上IF NOT EXISTSCREATE DATABASE IF NOT EXISTS mydb;這句的意思是存在就跳過不存在就新建。初始化腳本里寫上它執(zhí)行多少遍都不會(huì)報(bào)錯(cuò)。但建庫時(shí)真正要思考的是字符集和排序規(guī)則CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;字符集決定數(shù)據(jù)以什么編碼存儲(chǔ)排序規(guī)則決定字符串比較和排序的方式。重點(diǎn)說utf8mb4它能存儲(chǔ)emoji和生僻字兼容完整的Unicode。過去很多人用utf8但MySQL里的utf8其實(shí)只是utf8mb3只支持基本多語言平面遇到特殊字符或emoji就會(huì)存不進(jìn)去甚至報(bào)錯(cuò)“Incorrect string value”。排序規(guī)則里utf8mb4_0900_ai_ci是MySQL 8.0默認(rèn)值其中ai表示不區(qū)分重音ci表示不區(qū)分大小寫。如果業(yè)務(wù)要求區(qū)分大小寫就要用utf8mb4_bin或cs結(jié)尾的排序規(guī)則。這里我建議養(yǎng)成習(xí)慣建庫時(shí)都寫上CHARACTER SET別依賴默認(rèn)值。默認(rèn)值會(huì)隨版本升級(jí)或服務(wù)器配置變化顯式聲明才能保證腳本的可移植性。2.2 命名規(guī)范這些坑別踩庫名最好統(tǒng)一小寫用下劃線分詞例如user_center、order_service。原因是MySQL在Linux上表名區(qū)分大小寫在Windows上默認(rèn)不區(qū)分。如果開發(fā)環(huán)境在Windows、生產(chǎn)環(huán)境在Linux大小寫不一致就會(huì)導(dǎo)致應(yīng)用找不到表。統(tǒng)一用小寫可以避開這類問題。另一個(gè)坑是保留字。order、group、select、user這些單詞看起來正常但都是SQL保留字。真要拿它們當(dāng)表名或字段名語法會(huì)直接報(bào)錯(cuò)CREATE TABLE order ( id INT );加上反引號(hào)能救回來但每次寫SQL都要帶反引號(hào)維護(hù)成本高。更好的方案是換個(gè)名字比如orders、t_order。表名盡量直白不要怕多寫幾個(gè)字母。2.3 從業(yè)務(wù)需求倒推建庫設(shè)計(jì)如果只是練手隨便建庫沒問題。但做項(xiàng)目時(shí)建庫前先想清楚業(yè)務(wù)邊界。常見做法是一個(gè)業(yè)務(wù)域一個(gè)庫user_center用戶中心存放賬號(hào)、資料、登錄記錄order_service訂單服務(wù)存放訂單、訂單明細(xì)、支付記錄product_service商品服務(wù)存放商品、分類、庫存這樣設(shè)計(jì)的好處是權(quán)限容易控制可以把賬號(hào)只授予業(yè)務(wù)對應(yīng)庫的權(quán)限備份恢復(fù)也更靈活某個(gè)庫出問題不會(huì)拖累其他業(yè)務(wù)。建庫時(shí)還要考慮字符集對外鍵、索引的影響。比如兩個(gè)庫字符集不一致做JOIN連接查詢時(shí)MySQL可能因?yàn)榕判蛞?guī)則不兼容而報(bào)錯(cuò)“Illegal mix of collations”。所以同一個(gè)公司內(nèi)庫與庫之間的字符集最好保持一致。3. 運(yùn)行各類SQL從建表到查詢都要跑得動(dòng)3.1 DDL表結(jié)構(gòu)的增刪改數(shù)據(jù)庫建好后核心是建表。一個(gè)典型的用戶表CREATE TABLE users ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL, email VARCHAR(100) DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;字段類型的選擇邏輯id用INT UNSIGNED配合AUTO_INCREMENT作為自增主鍵足夠普通業(yè)務(wù)使用username用VARCHAR(50)用戶名長度普遍不超過50email用VARCHAR(100)且允許NULL不是所有用戶都填了郵箱created_at用DATETIME默認(rèn)值取CURRENT_TIMESTAMP應(yīng)用層不需要手動(dòng)傳時(shí)間AUTO_INCREMENT是MySQL的方便之處不需要單獨(dú)建序列對象。ENGINEInnoDB是默認(rèn)存儲(chǔ)引擎支持事務(wù)、行級(jí)鎖、外鍵。除非有極其特殊的統(tǒng)計(jì)場景否則InnoDB是穩(wěn)妥選擇。改表結(jié)構(gòu)常用四句ALTER TABLE users ADD COLUMN phone VARCHAR(20) DEFAULT NULL; ALTER TABLE users MODIFY COLUMN phone VARCHAR(30); ALTER TABLE users DROP COLUMN phone; ALTER TABLE users RENAME TO accounts;依次對應(yīng)加字段、改字段類型、刪字段、改表名。注意MODIFY COLUMN在數(shù)據(jù)量大時(shí)可能耗時(shí)較長盡量放在低峰期執(zhí)行。3.2 DML對數(shù)據(jù)動(dòng)手DML是數(shù)據(jù)操作語言包括INSERT、UPDATE、DELETE。插入單條INSERT INTO users (username, email) VALUES (tom, tomexample.com);插入多條INSERT INTO users (username, email) VALUES (jerry, jerryexample.com), (spike, spikeexample.com);多條VALUES一次提交比逐條INSERT少很多次網(wǎng)絡(luò)往返效率更高。更新數(shù)據(jù)一定要認(rèn)清WHEREUPDATE users SET email newexample.com WHERE username tom;如果漏掉WHERE整張表的email都會(huì)被改成同一值這種事故在實(shí)際工作中不是沒有。改數(shù)據(jù)之前先寫SELECT確認(rèn)目標(biāo)行再改成UPDATE這一招能救不少人。刪除數(shù)據(jù)同理DELETE FROM users WHERE id 1;DELETE是逐行刪除不會(huì)重置自增ID。而TRUNCATE TABLE users會(huì)清空全表并重置自增計(jì)數(shù)且不能加WHERE危險(xiǎn)系數(shù)高除非明確要重置表否則少用。3.3 DQL查詢是SQL的重頭戲SELECT是日常工作用到最多的語句?；A(chǔ)查詢SELECT id, username, email FROM users;按條件篩選SELECT id, username FROM users WHERE created_at 2026-03-01;去除空值用IS NOT NULLSELECT id, username FROM users WHERE email IS NOT NULL;去重用DISTINCTSELECT DISTINCT status FROM users;排序SELECT id, username FROM users ORDER BY created_at DESC;分頁SELECT id, username FROM users ORDER BY id LIMIT 20 OFFSET 40;這是第三頁、每頁20條數(shù)據(jù)的寫法。OFFSET別忘很多新手只記得LIMIT翻頁卻永遠(yuǎn)翻不動(dòng)。聚合統(tǒng)計(jì)SELECT status, COUNT(*) AS cnt FROM users GROUP BY status;如果想篩出數(shù)量超過10的分組用HAVINGSELECT status, COUNT(*) AS cnt FROM users GROUP BY status HAVING COUNT(*) 10;WHERE篩選原始行HAVING篩選分組后的結(jié)果兩個(gè)階段不能弄混。運(yùn)行SQL時(shí)我建議逐條執(zhí)行尤其在命令行里看清楚每條語句的返回結(jié)果再繼續(xù)。別把一堆不相關(guān)的SQL貼到一個(gè)事務(wù)里一把梭出了問題不好定位。3.4 事務(wù)DML的安全網(wǎng)雖然第34節(jié)的重點(diǎn)是運(yùn)行各類SQL但我還是提前把事務(wù)提一嘴因?yàn)镮NSERT、UPDATE、DELETE組合使用時(shí)事務(wù)能避免做到一半出錯(cuò)的尷尬。基本手法START TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_id 1; UPDATE account SET balance balance 100 WHERE user_id 2; COMMIT;兩條更新要么都成功要么都回滾。如果不加事務(wù)第一條成功、第二條失敗錢就對不上了?；A(chǔ)語法可以先記著等后面讀到隔離級(jí)別時(shí)再深入。4. 踩坑實(shí)錄幾次連接失敗和語句報(bào)錯(cuò)之后4.1 連接層訪問拒絕、端口不通、SSL報(bào)錯(cuò)最常見的是ERROR 1045 (28000): Access denied for user rootlocalhost (using password: YES)看到這個(gè)提示先確認(rèn)密碼再確認(rèn)來源主機(jī)。root默認(rèn)只允許localhost登錄如果遠(yuǎn)程連接用root會(huì)被拒絕。更好的做法是創(chuàng)建專用賬號(hào)并限定來源網(wǎng)段CREATE USER app192.168.1.% IDENTIFIED BY your_password; GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO app192.168.1.%; FLUSH PRIVILEGES;這里給的是最小權(quán)限只允許操作mydb庫的增刪改查而不是給ALL。權(quán)限越大出問題時(shí)的波及面越大。連接失敗還有一個(gè)常見原因是端口不通。檢查MySQL是否在監(jiān)聽netstat -tlnp | grep 3306如果沒輸出說明mysqld沒起來或者端口被改了。再看防火墻云服務(wù)器要單獨(dú)放行3306。很多人卡在這一步明明MySQL在運(yùn)行卻連不上。SSL報(bào)錯(cuò)常見于舊客戶端連新服務(wù)器。學(xué)習(xí)環(huán)境里用--skip-ssl能臨時(shí)繞過但正規(guī)項(xiàng)目還是要把SSL證書配好。4.2 SQL層語法錯(cuò)誤、保留字沖突、亂碼語法錯(cuò)誤最典型的是ERROR 1064ERROR 1064 (42000): You have an error in your SQL syntax排查思路依次是看拼寫、看關(guān)鍵字順序、看是否有保留字。例如CREATE TABLE order (id INT);會(huì)報(bào)1064因?yàn)閛rder是保留字。改成orders或者加反引號(hào)就好。亂碼問題多半出現(xiàn)在字符集不統(tǒng)一。建庫是utf8mb4客戶端卻用latin1中文顯示就是問號(hào)。臨時(shí)解決方式SET NAMES utf8mb4;這條語句把客戶端和連接相關(guān)的字符集統(tǒng)一設(shè)置。長期來看建表和建庫時(shí)把字符集固定成utf8mb4可以避免大部分亂碼。4.3 權(quán)限與安全別讓建庫變成捅婁子學(xué)習(xí)階段最容易犯的錯(cuò)是圖省事給賬號(hào)開ALL PRIVILEGES。本機(jī)學(xué)習(xí)可以但項(xiàng)目環(huán)境千萬控制住。最小權(quán)限原則就一句話能用SELECT解決的不授予UPDATE權(quán)限能限定一個(gè)庫的不授予全庫權(quán)限。再提一下SQL注入。如果代碼里直接字符串拼接SQLSELECT * FROM users WHERE username 用戶輸入;用戶輸入 OR 11這條語句會(huì)把整張表查出來。正確做法是參數(shù)化查詢讓數(shù)據(jù)庫把輸入當(dāng)數(shù)據(jù)而不是SQL邏輯。這個(gè)安全習(xí)慣從第一天學(xué)SQL就應(yīng)該種下去。我把近期遇到的典型問題整理成了一張表報(bào)錯(cuò)信息常見原因處理方式ERROR 1045 Access denied密碼錯(cuò)誤或來源主機(jī)未被授權(quán)檢查密碼或創(chuàng)建指定來源的賬號(hào)并GRANTERROR 2003 Cant connect端口不通、服務(wù)未啟動(dòng)檢查mysqld進(jìn)程放行防火墻端口ERROR 1064 syntax error拼寫錯(cuò)誤或保留字未加反引號(hào)逐詞校對避免保留字命名ERROR 1366 Incorrect string value字符集不一致統(tǒng)一utf8mb4執(zhí)行SET NAMES utf8mb4這張表以后還會(huì)繼續(xù)擴(kuò)充。每踩一個(gè)新坑就補(bǔ)一行慢慢就形成自己的排錯(cuò)手冊。5. 把第34節(jié)變成能復(fù)用的能力5.1 一套可以照做的練習(xí)清單學(xué)習(xí)SQL不能只看不動(dòng)手。建議按這個(gè)清單過一遍創(chuàng)建數(shù)據(jù)庫demo字符集用utf8mb4創(chuàng)建表students包含自增主鍵、姓名、年齡、班級(jí)、創(chuàng)建時(shí)間插入5條記錄其中2條姓名長度不同查出年齡大于18的學(xué)生按班級(jí)分組統(tǒng)計(jì)每班人數(shù)更新某位學(xué)生的班級(jí)刪除一條測試記錄練習(xí)一次去重查詢和分頁查詢每執(zhí)行完一步截圖或記錄輸出再對照預(yù)期結(jié)果。報(bào)錯(cuò)是正常的關(guān)鍵是學(xué)會(huì)讀報(bào)錯(cuò)信息。讀報(bào)錯(cuò)是DBA和開發(fā)的基本功別急著把整段報(bào)錯(cuò)復(fù)制到搜索引擎先自己讀一遍很多時(shí)候問題就出在某個(gè)單詞拼寫上。5.2 筆記沉淀SQL不靠背靠查學(xué)習(xí)筆記的核心價(jià)值在于好查。我會(huì)按SQL場景分類記錄建庫、建表、查詢、更新、刪除、統(tǒng)計(jì)。每個(gè)場景寫一個(gè)最簡例子再補(bǔ)充踩坑點(diǎn)。這樣寫出來的筆記是自己消化過的內(nèi)容而不是對文檔的簡單復(fù)制。筆記里可以放一些自己的SQL設(shè)計(jì)模板。比如創(chuàng)建表時(shí)我固定會(huì)包含id、created_at、updated_at三個(gè)字段。這個(gè)模板不一定適合所有業(yè)務(wù)但能保證表結(jié)構(gòu)有一定的一致性。5.3 從這個(gè)節(jié)點(diǎn)往后往哪里走第34節(jié)只是基礎(chǔ)節(jié)點(diǎn)下一個(gè)階段建議按這個(gè)順序擴(kuò)展索引優(yōu)化理解B樹為什么讓查詢變快學(xué)會(huì)用EXPLAIN看執(zhí)行計(jì)劃事務(wù)與隔離級(jí)別搞懂ACID的底層邏輯以及并發(fā)下可能出現(xiàn)的臟讀、幻讀視圖與存儲(chǔ)過程把復(fù)雜查詢封裝成獨(dú)立對象減少應(yīng)用層重復(fù)代碼備份恢復(fù)mysqldump、binlog日志這是運(yùn)維能力里繞不過的兩塊不用急著全部啃完。今天把建庫和基礎(chǔ)SQL跑熟練后面每一步都會(huì)更順。最后分享一個(gè)我自己的小習(xí)慣每次動(dòng)手前先在命令行跑一次SELECT VERSION();和SHOW DATABASES;確認(rèn)連的是對的那個(gè)實(shí)例。這個(gè)習(xí)慣幫我避免了好幾次“改了A庫、忘了B庫”的尷尬。SQL的學(xué)習(xí)就是不斷重復(fù)、不斷踩坑的過程第34節(jié)記下的內(nèi)容回頭半個(gè)月再看可能又有新的理解。各位同學(xué)可以拿自己的業(yè)務(wù)數(shù)據(jù)把今天這些語句重跑一遍踩過的坑比看十遍筆記都管用。