Linux

Ubuntu 22.04 MySQL 8.0 新特性實務

Ubuntu 22.04 MySQL 8.0 New Features in Practice

本文將介紹 MySQL 8.0 在 Ubuntu 22.04 上的重要新特性，透過實際範例說明如何運用這些功能來提升資料庫開發與管理效率。

MySQL 8.0 概述

MySQL 8.0 是 MySQL 資料庫的重大版本更新，帶來了許多令人期待的新功能。Ubuntu 22.04 LTS 預設套件庫中包含 MySQL 8.0，讓使用者可以輕鬆體驗這些新特性。

主要新功能包括：

  • 預設認證外掛變更為 caching_sha2_password
  • 視窗函數（Window Functions）
  • 通用資料表運算式（Common Table Expressions, CTE）
  • JSON 功能增強
  • 不可見索引（Invisible Indexes）
  • 資源群組（Resource Groups）
  • 角色管理（Role-based Access Control）
  • 原子性 DDL（Atomic DDL）

確認 MySQL 版本：

1

mysql --version

輸出類似：

1

mysql  Ver 8.0.35-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))

預設認證外掛變更

MySQL 8.0 將預設認證外掛從 mysql_native_password 改為 caching_sha2_password，提供更強的密碼加密安全性。

查看目前認證外掛

1
2

-- 查看使用者的認證外掛
SELECT user, host, plugin FROM mysql.user;

建立使用新認證外掛的使用者

1
2
3
4
5
6

-- 使用 caching_sha2_password（預設）
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'SecurePassword123!';

-- 若舊版應用程式不相容，可使用舊版外掛
CREATE USER 'legacyuser'@'localhost'
IDENTIFIED WITH mysql_native_password BY 'LegacyPassword123!';

變更現有使用者的認證外掛

1
2

ALTER USER 'existinguser'@'localhost'
IDENTIFIED WITH caching_sha2_password BY 'NewSecurePassword123!';

視窗函數

視窗函數是 MySQL 8.0 最重要的新功能之一，允許在不使用 GROUP BY 的情況下執行聚合運算，同時保留原始資料列。

建立範例資料表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

CREATE DATABASE IF NOT EXISTS demo_db;
USE demo_db;

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    salesperson VARCHAR(50),
    region VARCHAR(50),
    amount DECIMAL(10,2),
    sale_date DATE
);

INSERT INTO sales (salesperson, region, amount, sale_date) VALUES
('Alice', 'North', 1500.00, '2024-01-15'),
('Bob', 'North', 2300.00, '2024-01-20'),
('Alice', 'North', 1800.00, '2024-02-10'),
('Carol', 'South', 2100.00, '2024-01-25'),
('Bob', 'South', 1900.00, '2024-02-15'),
('Carol', 'South', 2500.00, '2024-02-20');

ROW_NUMBER() - 資料列編號

1
2
3
4
5
6

-- 依銷售金額排名
SELECT
    salesperson,
    amount,
    ROW_NUMBER() OVER (ORDER BY amount DESC) AS ranking
FROM sales;

RANK() 和 DENSE_RANK() - 排名函數

1
2
3
4
5
6
7
8

-- RANK() 會在相同值時跳過排名
-- DENSE_RANK() 不會跳過排名
SELECT
    salesperson,
    amount,
    RANK() OVER (ORDER BY amount DESC) AS rank_num,
    DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank_num
FROM sales;

PARTITION BY - 分組視窗

1
2
3
4
5
6
7

-- 每個區域內的銷售排名
SELECT
    salesperson,
    region,
    amount,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS region_rank
FROM sales;

累計加總與移動平均

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

-- 累計銷售金額
SELECT
    salesperson,
    sale_date,
    amount,
    SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

-- 移動平均（前後各一筆）
SELECT
    salesperson,
    sale_date,
    amount,
    AVG(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS moving_avg
FROM sales;

CTE 通用資料表運算式

CTE（Common Table Expressions）讓複雜查詢更易讀、更易維護，並支援遞迴查詢。

基本 CTE 語法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- 計算每位銷售員的總銷售額與平均銷售額
WITH sales_summary AS (
    SELECT
        salesperson,
        COUNT(*) AS sale_count,
        SUM(amount) AS total_amount,
        AVG(amount) AS avg_amount
    FROM sales
    GROUP BY salesperson
)
SELECT
    salesperson,
    sale_count,
    total_amount,
    ROUND(avg_amount, 2) AS avg_amount
FROM sales_summary
WHERE total_amount > 2000;

多重 CTE

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 結合多個 CTE
WITH
regional_sales AS (
    SELECT region, SUM(amount) AS total
    FROM sales
    GROUP BY region
),
top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total > 4000
)
SELECT s.salesperson, s.region, s.amount
FROM sales s
JOIN top_regions t ON s.region = t.region;

遞迴 CTE

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

-- 建立組織結構表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

INSERT INTO employees VALUES
(1, 'CEO', NULL),
(2, 'CTO', 1),
(3, 'CFO', 1),
(4, 'Dev Manager', 2),
(5, 'Developer', 4);

-- 遞迴查詢組織階層
WITH RECURSIVE org_hierarchy AS (
    -- 基礎查詢：最高層主管
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 遞迴查詢：下屬員工
    SELECT e.id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN org_hierarchy h ON e.manager_id = h.id
)
SELECT id, name, level, REPEAT('  ', level - 1) || name AS hierarchy
FROM org_hierarchy
ORDER BY level, id;

JSON 增強功能

MySQL 8.0 大幅增強了 JSON 資料類型的支援，新增多個實用函數。

建立含 JSON 欄位的資料表

1
2
3
4
5
6
7
8
9

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    attributes JSON
);

INSERT INTO products (name, attributes) VALUES
('Laptop', '{"brand": "Dell", "specs": {"ram": 16, "storage": 512}, "tags": ["electronics", "computer"]}'),
('Phone', '{"brand": "Apple", "specs": {"ram": 8, "storage": 256}, "tags": ["electronics", "mobile"]}');

JSON_TABLE() - JSON 轉關聯式資料

1
2
3
4
5
6
7
8
9

-- 將 JSON 陣列展開為資料列
SELECT
    p.name,
    jt.tag
FROM products p,
JSON_TABLE(
    p.attributes,
    '$.tags[*]' COLUMNS (tag VARCHAR(50) PATH '$')
) AS jt;

JSON 聚合函數

1
2
3
4
5
6

-- JSON_ARRAYAGG() - 聚合為 JSON 陣列
SELECT JSON_ARRAYAGG(name) AS all_products FROM products;

-- JSON_OBJECTAGG() - 聚合為 JSON 物件
SELECT JSON_OBJECTAGG(name, attributes->'$.brand') AS product_brands
FROM products;

JSON 路徑運算式增強

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 使用 ->> 運算子取得純文字值
SELECT
    name,
    attributes->>'$.brand' AS brand,
    attributes->>'$.specs.ram' AS ram_gb
FROM products;

-- JSON_OVERLAPS() - 檢查 JSON 是否有重疊
SELECT name
FROM products
WHERE JSON_OVERLAPS(attributes->'$.tags', '["mobile", "tablet"]');

不可見索引

不可見索引允許在不刪除索引的情況下測試移除索引對效能的影響。

建立與管理不可見索引

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 建立不可見索引
CREATE INDEX idx_amount_invisible ON sales(amount) INVISIBLE;

-- 將現有索引設為不可見
ALTER TABLE sales ALTER INDEX idx_amount_invisible INVISIBLE;

-- 將索引設回可見
ALTER TABLE sales ALTER INDEX idx_amount_invisible VISIBLE;

-- 查看索引可見性
SHOW INDEX FROM sales;

暫時使用不可見索引

1
2
3
4
5
6
7
8

-- 在當前 session 中啟用不可見索引
SET SESSION optimizer_switch = 'use_invisible_indexes=on';

-- 執行查詢測試
EXPLAIN SELECT * FROM sales WHERE amount > 2000;

-- 關閉不可見索引使用
SET SESSION optimizer_switch = 'use_invisible_indexes=off';

資源群組

資源群組允許管理員控制執行緒的 CPU 資源分配，適用於多租戶環境。

建立資源群組

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 建立低優先權資源群組（需要 RESOURCE_GROUP_ADMIN 權限）
CREATE RESOURCE GROUP low_priority
TYPE = USER
VCPU = 0-1
THREAD_PRIORITY = 10;

-- 建立高優先權資源群組
CREATE RESOURCE GROUP high_priority
TYPE = USER
VCPU = 2-3
THREAD_PRIORITY = 0;

使用資源群組

1
2
3
4
5

-- 將當前執行緒指派到資源群組
SET RESOURCE GROUP low_priority;

-- 查看資源群組
SELECT * FROM information_schema.RESOURCE_GROUPS;

角色管理

MySQL 8.0 引入角色管理機制，簡化權限管理流程。

建立與管理角色

1
2
3
4
5
6
7

-- 建立角色
CREATE ROLE 'app_read', 'app_write', 'app_admin';

-- 授予角色權限
GRANT SELECT ON demo_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON demo_db.* TO 'app_write';
GRANT ALL PRIVILEGES ON demo_db.* TO 'app_admin';

將角色指派給使用者

1
2
3
4
5
6
7
8

-- 建立使用者
CREATE USER 'developer'@'localhost' IDENTIFIED BY 'DevPassword123!';

-- 授予角色
GRANT 'app_read', 'app_write' TO 'developer'@'localhost';

-- 設定預設角色
SET DEFAULT ROLE 'app_read', 'app_write' TO 'developer'@'localhost';

查看角色資訊

1
2
3
4
5
6
7
8
9

-- 查看使用者的角色
SHOW GRANTS FOR 'developer'@'localhost';

-- 查看目前啟用的角色
SELECT CURRENT_ROLE();

-- 動態切換角色
SET ROLE 'app_read';
SET ROLE ALL;

Atomic DDL

MySQL 8.0 支援原子性 DDL 操作，確保 DDL 語句的完整執行或完整回滾。

Atomic DDL 特性

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 以下 DDL 操作現在是原子性的
-- 若任何步驟失敗，整個操作會回滾

-- 範例：建立多個資料表
DROP TABLE IF EXISTS table1, table2, table3;

CREATE TABLE table1 (id INT PRIMARY KEY);
CREATE TABLE table2 (id INT PRIMARY KEY);
CREATE TABLE table3 (id INT PRIMARY KEY);

-- DROP TABLE 也是原子性的
DROP TABLE table1, table2, table3;

資料字典改進

1
2
3
4
5

-- 查看資料字典資訊（MySQL 8.0 使用 InnoDB 儲存資料字典）
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mysql'
LIMIT 10;

其他實用新功能

降序索引

1
2
3
4
5
6
7

-- 建立降序索引
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATETIME,
    amount DECIMAL(10,2),
    INDEX idx_date_desc (order_date DESC)
);

函數索引

1
2
3
4
5
6
7
8
9

-- 建立函數索引（表達式索引）
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255),
    INDEX idx_email_lower ((LOWER(email)))
);

-- 查詢時會使用函數索引
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

結論

MySQL 8.0 帶來了眾多強大的新功能，本文涵蓋的主要特性包括：

  • 認證外掛：更安全的 caching_sha2_password
  • 視窗函數：無需 GROUP BY 即可進行聚合運算
  • CTE：提升複雜查詢的可讀性，支援遞迴查詢
  • JSON 增強：更完整的 JSON 操作支援
  • 不可見索引：安全測試索引移除的影響
  • 資源群組：精細控制 CPU 資源分配
  • 角色管理：簡化權限管理流程
  • Atomic DDL：確保 DDL 操作的原子性

這些功能大幅提升了 MySQL 在企業級應用中的競爭力，建議開發者善加利用這些新特性來優化資料庫設計與效能。

參考資料

comments powered by Disqus
© 2020 - 2026 Astroicers Blog
Built with Hugo
Theme Stack designed by Jimmy