一、核心數據類型速查
| 分類 | 類型 | 佔用空間 | 範圍/特點 |
|---|
| 整數 | TINYINT | 1B | -128~127 / 0~255 (UNSIGNED) |
| SMALLINT | 2B | -32K~32K / 0~65K |
| MEDIUMINT | 3B | -8M~8M / 0~16M |
| INT / INTEGER | 4B | -2.1B~2.1B / 0~4.2B |
| BIGINT | 8B | -9.2E18~9.2E18 / 0~1.8E19 |
| 浮點 | FLOAT | 4B | 單精度，~7 位有效數字 |
| DOUBLE | 8B | 雙精度，~15 位有效數字 |
| 定點 | DECIMAL(M,D) | M+2 B | 精確數值，金融必用，M=總位數 D=小數位 |
| 日期時間 | DATE | 3B | 1000-01-01 ~ 9999-12-31 |
| TIME | 3B | -838:59:59 ~ 838:59:59 |
| DATETIME | 8B | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 |
| TIMESTAMP | 4B | 1970-01-01 ~ 2038-01-19，自動更新、時區感知 |
| YEAR | 1B | 1901 ~ 2155 |
| 字串 | CHAR(N) | N 字元 | 定長，右側空格填充，N≤255 |
| VARCHAR(N) | 實際長度+1/2B | 變長，N≤65535 (受行大小限制) |
| 大對象 | TINYBLOB/TEXT | 255B | |
| BLOB/TEXT | 64KB | |
| MEDIUMBLOB/TEXT | 16MB | |
| LONGBLOB/TEXT | 4GB | |
二、MySQL 小知識速查
2.1 登入/退出
mysql -u root -p # 交互式登入
exit # 退出
2.2 庫操作
CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SHOW DATABASES;
USE db_name;
DROP DATABASE db_name;
2.3 備份/恢復 (Shell 環境)
# 備份
mysqldump -u root -p db_name > backup.sql
mysqldump -u root -p --single-transaction --routines --triggers --events db_name > prod.sql
# 恢復 (MySQL 互動環境)
SOURCE /path/backup.sql;
2.4 字元集檢查/設定
SHOW VARIABLES LIKE 'character_set_%';
SET NAMES utf8mb4; -- 會話級
SET character_set_results = gbk; -- 臨時輸出編碼
三、中文亂碼徹底解決 (Windows/XP 經驗)
3.1 方案一：會話級臨時修正 (僅當前窗口生效)
SET character_set_client = gbk;
SET character_set_connection = gbk;
SET character_set_results = gbk;
-- 或簡寫
SET NAMES gbk;
3.2 方案二：配置文件永久生效 (需重啟服務)
# my.ini / my.cnf
[client]
default-character-set = gbk
[mysql]
default-character-set = gbk
[mysqld]
character-set-server = gbk
collation-server = gbk_chinese_ci
現代建議：全鏈路統一 **utf8mb4**，避免 GBK 兼容性問題。
四、表增刪改核心操作
4.1 INSERT
INSERT INTO t (a,b) VALUES (1,'x');
INSERT INTO t (a,b) VALUES (1,'x'), (2,'y'); -- 批次插入
4.2 UPDATE
UPDATE t SET a=2 WHERE b='x';
4.3 DELETE 三種方式對比
| 命令 | 行為 | 適用場景 | Binlog | 自增ID | 觸發器 |
|---|
| DELETE FROM t | 逐行刪除，可 WHERE | 小資料量、條件刪除 | 記錄每行 | 不重置 | 觸發 |
| TRUNCATE TABLE t | 重建表結構，不可 WHERE | 大資料量、全表清空 | 不記錄 (DDL) | 重置 | 不觸發 |
| DROP TABLE t | 刪除表結構+資料 | 不再需要該表 | 記錄 DDL | 不存在 | 不觸發 |
4.4 外鍵關聯刪除順序
先刪子表，後刪父表；或暫時 SET FOREIGN_KEY_CHECKS=0; (謹慎使用)。
五、查詢操作核心語法
5.1 基礎 SELECT
SELECT DISTINCT col FROM t;
SELECT col AS alias, expr AS alias FROM t;
SELECT * FROM t WHERE condition;
5.2 運算與 NULL
NULL + 1 = NULL
NULL = NULL → NULL (未知)
使用 IS NULL / IS NOT NULL / <=> 判斷 NULL
5.3 排序
ORDER BY col1 ASC, col2 DESC;
-- NULL 排在最前 (ASC) 或最後 (DESC)
-- ORDER BY 必須是最後子句 (LIMIT 除外)
5.4 聚合函數
| 函數 | 說明 | NULL 處理 |
|---|
| COUNT(*) | 行數 | 包含 NULL 行 |
| COUNT(col) | 非 NULL 個數 | 忽略 NULL |
| SUM(col) | 求和 | 忽略 NULL，全 NULL 返回 NULL |
| AVG(col) | 平均 | 忽略 NULL |
| MAX/MIN | 極值 | 忽略 NULL |
六、實用技巧匯總
- 多行 SQL：以
; 結束，可跨行輸入
- 歷史命令：↑/↓ 方向鍵調取
- 當前庫：
SELECT DATABASE();
- 當前用戶：
SELECT USER();
- 修改 root 密碼：
UPDATE mysql.user SET authentication_string=PASSWORD('new') WHERE User='root'; FLUSH PRIVILEGES;
- 文本導入：
LOAD DATA LOCAL INFILE 'file.txt' INTO TABLE t FIELDS TERMINATED BY '\t';
- 備份單表：
mysqldump -u root -p db table > table.sql
- 備份結構：
mysqldump -u root -p -d --add-drop-table db > struct.sql
總結
掌握 **數據類型選型、字元集統一 utf8mb4、DELETE/TRUNCATE/DROP 三選一、聚合函數 NULL 語義、備份恢復雙軌制**，即可應對 90% 日常 MySQL 開發運維場景。
- 上一篇文章： 從 MSSQL 遷移 MySQL 語法差異全對比 (五)：運算符、正則、位運算與特殊字元
- 下一篇文章： MySQL 官方安裝包下載指南：MSI 安裝版 vs 綠色版
Copyright ©
程式碼之家 All Rights Reserved
