MySQL 資料庫基礎回顧：建表、增刪改查、索引、引擎、字元集核心知識點

一、資料表結構設計

核心約束

• NOT NULL：非空
• UNIQUE：唯一
• PRIMARY KEY：主鍵 (隱含 NOT NULL + UNIQUE)
• FOREIGN KEY：外鍵 (參照完整性)
• CHECK：條件檢查 (8.0.16+ 生效)
• AUTO_INCREMENT：自增序號

DDL 常用語法

-- 建表
CREATE TABLE `user` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) NOT NULL,
  `email` VARCHAR(128) NOT NULL UNIQUE,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 修改結構
ALTER TABLE `user` ADD `age` TINYINT UNSIGNED NOT NULL DEFAULT 0;
ALTER TABLE `user` MODIFY `email` VARCHAR(255) NOT NULL UNIQUE;
ALTER TABLE `user` DROP `age`;
ALTER TABLE `user` RENAME TO `member`;

-- 刪表
DROP TABLE IF EXISTS `member`;

二、DML 資料操作

-- 新增
INSERT INTO `user` (`name`,`email`) VALUES ('Tom','tom@mail.com');
INSERT INTO `user` (`name`,`email`) VALUES ('A','a@mail.com'),('B','b@mail.com');

-- 查詢
SELECT `id`,`name` AS `username` FROM `user` WHERE `id` BETWEEN 1 AND 10;
SELECT DISTINCT `email` FROM `user`;
SELECT * FROM `user` WHERE `name` IN ('Tom','A');
SELECT * FROM `user` WHERE `name` LIKE 'T%';  -- % 多字元，_ 單字元

-- 更新
UPDATE `user` SET `name`='Tommy' WHERE `id`=1;

-- 刪除
DELETE FROM `user` WHERE `id` > 100;

三、索引體系

CREATE INDEX idx_name ON `user`(`name`);
CREATE FULLTEXT INDEX ft_content ON `article`(`content`);

四、儲存引擎選擇

SHOW ENGINES;  -- 查看支援引擎

• InnoDB：預設、事務、行鎖、外鍵、崩潰恢復、生產首選
• MyISAM：表鎖、全文索引(舊版)、唯讀/日誌分析適用
• MEMORY：記憶體表、重啟資料消失、暫存運算用

資料檔案通常位於 datadir (如 /var/lib/mysql 或 C:\ProgramData\MySQL\Data)。

五、字元集與排序規則

查看當前設定

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

層級設定 (優先級高到低)

1. 伺服器級：character-set-server (my.cnf)
2. 資料庫級：CREATE DATABASE ... CHARSET=utf8mb4
3. 資料表級：CREATE TABLE ... CHARSET=utf8mb4
4. 欄位級：col VARCHAR(32) CHARSET=utf8mb4
5. 連線級：SET NAMES utf8mb4

推薦生產設定 (my.cnf)

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

Windows 客戶端亂碼快速修正：連線後執行 SET NAMES gbk (僅適用舊系統)，新專案請全鏈路 utf8mb4。