PHP 移動互聯網開發筆記：MySQL 資料庫基礎全面回顧

前言

MySQL 是 PHP 開發最核心的儲存引擎。本文系統整理資料類型選擇、常用操作指令、用戶權限管理、資料庫/表結構操作，作為開發隨查手冊。

一、資料類型精選指南

1. 整數類型

最佳實踐：UNSIGNED 非負業務優先；INT UNSIGNED 存 IPv4（配合 INET_ATON/NTOA）；避免 INT(11) 誤以為長度限制，括號僅為顯示寬度。

2. 浮點與定點數

鐵律：涉及金錢務必用 DECIMAL，FLOAT/DOUBLE 二進制表示導致 0.1+0.2≠0.3 等精度問題。

3. 字串類型

字符集：預設 utf8mb4 支援 Emoji，utf8 僅 3 字節不支援補助平面字符。

4. 日期時間類型

TIMESTAMP 自動屬性（5.6.5+ 可多欄位）：

• DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP：建立與更新自動賦值
• DEFAULT CURRENT_TIMESTAMP：僅建立自動賦值
• DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP：僅更新自動賦值

二、MySQL 客戶端核心操作

1. 連線參數速查

mysql -h host -u user -p[password] -P port -D database
# 常用簡寫
mysql -uroot -p123456 -h127.0.0.1 -P3306 -Dmydb

2. 退出指令

exit | quit | \q | Ctrl+D

3. 修改密碼（5.7.6+）

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;

4. 資料庫級操作

-- 建立（指定字符集/校對）
CREATE DATABASE [IF NOT EXISTS] db_name 
  DEFAULT CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;

-- 查看建立語句
SHOW CREATE DATABASE db_name;

-- 切換
USE db_name;

-- 刪除（謹慎！）
DROP DATABASE [IF EXISTS] db_name;

-- 列出所有
SHOW DATABASES;

5. 表結構操作

-- 建表範例
CREATE TABLE `user` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(32) NOT NULL UNIQUE,
  `email` VARCHAR(128) NOT NULL,
  `password_hash` CHAR(60) NOT NULL,  -- bcrypt 輸出固定 60 字元
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 查看結構
DESC `user`;
SHOW CREATE TABLE `user`;

-- 修改表
ALTER TABLE `user` 
  ADD COLUMN `avatar_url` VARCHAR(255) DEFAULT NULL AFTER `email`,
  MODIFY COLUMN `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1=正常 2=禁用',
  DROP COLUMN `obsolete_col`,
  ADD INDEX `idx_email` (`email`),
  ADD UNIQUE INDEX `uk_username` (`username`);

-- 刪表
DROP TABLE [IF EXISTS] `user`;

三、用戶與權限管理

1. 建立用戶

CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongPass123!';
CREATE USER 'readonly'@'10.0.%.%' IDENTIFIED BY 'ReadOnlyPass!';

2. 授權模板

-- 開發/應用帳號：增刪改查 + 結構變更
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX 
  ON mydb.* TO 'app_user'@'%';

-- 唯讀帳號：報表、備份、分析
GRANT SELECT, SHOW VIEW 
  ON mydb.* TO 'readonly'@'10.0.%.%';

-- 管理員：全權限（含 GRANT OPTION）
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- 刷新生效
FLUSH PRIVILEGES;

3. 查看權限

SHOW GRANTS FOR 'app_user'@'%';
SHOW GRANTS;  -- 當前用戶

4. 撤銷權限與刪除用戶

REVOKE INSERT, UPDATE ON mydb.* FROM 'app_user'@'%';
DROP USER 'old_user'@'%';

四、常用資訊查詢

-- 版本
SELECT VERSION();

-- 當前資料庫
SELECT DATABASE();

-- 當前用戶
SELECT USER(), CURRENT_USER();

-- 字符集/校對
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

-- 進程列表
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- 引擎狀態
SHOW ENGINE INNODB STATUS;

-- 表狀態
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern'];

五、總結

MySQL 基礎功力決定上層應用穩定度。核心記住：

1. 類型最小化：滿足業務前提下選最小儲存類型（UNSIGNED、CHAR vs VARCHAR、DECIMAL 精度）。
2. 字符集統一 utf8mb4：資料庫、表、連線、應用程式全鏈路一致。
3. TIMESTAMP 優於 DATETIME：省空間、時區安全、自動維護。
4. 權限最小化：應用帳號僅增刪改查，唯讀帳號僅 SELECT，生產禁用 root 遠程登入。
5. DDL 合併執行：單次 ALTER TABLE 完成多欄位/索引變更，減少鎖表重建次數。

將此文加入書籤，下次選型、建表、授權時隨查隨用。