MySQL 數據類型全解析與數據表操作實戰：從選型原則到建表增刪改查

MySQL 數據類型全解析與數據表操作實戰：從選型原則到建表增刪改查

數據類型決定存儲空間、計算效率、索引性能。本文系統梳理 MySQL 五大類型族、核心選型原則，並演示數據表全生命周期操作命令，助你建立「恰如其分」的表設計習慣。

一、 MySQL 五大數據類型族

1. 整數類型

原則：夠用就好，越小越好。節省存儲、緩存、索引深度、CPU 比較開銷。

2. 浮點/定點類型

避坑：浮點類型存儲近似值，0.1 + 0.2 != 0.3。金額務必用 DECIMAL(10,2)。

3. 日期時間類型

實戰建議：

• 跨時區應用：存儲 BIGINT 毫秒時間戳（UTC），應用層轉換展示。
• 不跨時區/歷史數據：用 DATETIME 避開 2038 年上限。
• 自動更新時間戳：updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。

4. 字符串類型

5. 二進制類型

BINARY/VARBINARY、BLOB 族：存儲圖片、文件流、加密數據。不建議大文件入庫，存對象存儲路徑。

二、 數據表全生命周期操作

1. 創建表

USE db_name;

CREATE TABLE IF NOT EXISTS `users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
  `username` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '用戶名',
  `email` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '郵箱',
  `password_hash` CHAR(64) NOT NULL DEFAULT '' COMMENT '密碼哈希',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1正常 0禁用',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`),
  KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用戶表';

2. 查看表

SHOW TABLES;                    -- 當前庫所有表
SHOW TABLES FROM other_db;      -- 指定庫
SHOW TABLES LIKE 'user%';       -- 模糊匹配

3. 查看表結構

DESC users;              -- 簡要
DESCRIBE users;          -- 同 DESC
SHOW CREATE TABLE users\G -- 完整 DDL（含引擎、字符集、索引、註釋）

4. 記錄插入與查詢

-- 插入
INSERT INTO users(username, email, password_hash) VALUES('tom', 'tom@test.com', 'hash123');
INSERT INTO users(username, email, password_hash) VALUES('jerry', 'jerry@test.com', 'hash456'), ('spike', 'spike@test.com', 'hash789');

-- 查詢
SELECT * FROM users;
SELECT id, username FROM users WHERE status = 1 ORDER BY created_at DESC LIMIT 10;

5. 空值與非空約束

• NOT NULL：插入 NULL 報錯 Column 'col' cannot be null。
• DEFAULT：隱式插入時自動填充，配合 NOT NULL 保證數據完整性。
• 避免不必要的 NULL，增加統計複雜度、索引效率損耗。

三、 選型決策速查表

「選對類型，是性能優化的起點；拒絕過度設計，是架構演進的智慧。」