MySQL 資料庫/表操作與 CRUD 核心注意事項：字元集、大小寫、型別、LIKE、LIMIT

MySQL 資料庫/表操作與 CRUD 核心注意事項：字元集、大小寫、型別、LIKE、LIMIT

整理 MySQL 日常開發中最易踩坑的細節，涵蓋連線、建庫表、字元集、大小寫敏感、資料型別選型、CRUD 語法細節、LIKE 模糊查詢、LIMIT 分頁。

1. 連線與進出

mysql -h localhost -u root -p
# 退出：EXIT / QUIT / Ctrl+C

2. 修改 root 密碼 (兩種場景)

# Shell 下 (mysqladmin)
mysqladmin -u root -p'oldpass' password 'newpass'

# SQL 下 (需 FLUSH PRIVILEGES)
SET PASSWORD = PASSWORD('newpass');  -- 5.6
ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpass';  -- 5.7+

3. 建庫：務必指定字元集

CREATE DATABASE IF NOT EXISTS mydb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

• 預設字元集因安裝方式不同而異 (latin1/gbk/utf8)，生產強制 utf8mb4 (支援 Emoji)。
• 全域配置 /etc/my.cnf：character-set-server=utf8mb4、collation-server=utf8mb4_general_ci。

4. 表操作注意事項

• SHOW TABLES FROM db; 或 USE db; SHOW TABLES;
• DESCRIBE tbl; (常用) vs SHOW CREATE TABLE tbl\G (完整 DDL)
• 建表範例：

  CREATE TABLE IF NOT EXISTS articles (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    content TEXT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

• 刪表：DROP TABLE tbl FROM db;

5. 資料型別選型指南

核心原則：能用較小型別就不用大型別；金額必用 DECIMAL；變長優先 VARCHAR (節省空間、減少碎片)。

6. CRUD 關鍵語法

INSERT INTO t (title) VALUES ('test');
DELETE FROM t WHERE id = 1;
UPDATE t SET title = 'new' WHERE id = 2;
SELECT * FROM t WHERE title LIKE '%keyword%' ORDER BY id DESC LIMIT 0, 10;

• LIMIT offset, count：offset 從 0 開始，LIMIT 10 OFFSET 20 同義。
• LIKE：% 匹配任意長度、_ 匹配單一字元。LIKE 'a%' 前綴可用索引，LIKE '%a%' 全掃。

7. 正則表達式 (REGEXP)

SELECT * FROM t WHERE title REGEXP '^[0-9]+$';  -- 純數字標題
-- MySQL 8.0 支援 ICU 正則，功能更強

8. 大小寫敏感控制

# 配置檔
lower_case_table_names = 1  # 0=敏感, 1=不敏感(存小寫), 2=存原比較不敏感
# 必須在建表前設定！

9. 避坑檢查清單

1. [ ] 建庫指定 utf8mb4
2. [ ] 主鍵用 BIGINT UNSIGNED AUTO_INCREMENT
3. [ ] 金額欄位 DECIMAL(18,2)
4. [ ] 時間戳用 DATETIME (建立) + TIMESTAMP (更新)
5. [ ] lower_case_table_names=1 已配置 (跨平台專案)
6. [ ] 分頁大偏移優化：WHERE id > last_id LIMIT 10 替代 LIMIT 100000, 10