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