MySQL 資料庫架構設計與 ETL 資料處理自動化實務

-- 氣象觀測資料表結構設計範例
-- 展示如何精確選擇資料型別以確保資料品質與查詢效能

CREATE TABLE weather_observation (
    -- 觀測站識別碼：使用 INT 型別作為主鍵
    -- AUTO_INCREMENT 確保每個觀測站都有唯一的識別編號
    station_id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- 觀測站名稱：台灣氣象站名稱通常不超過 50 字元
    -- NOT NULL 約束確保每個觀測站都必須有名稱
    station_name VARCHAR(50) NOT NULL,
    
    -- 觀測站所在縣市：使用 CHAR(10) 固定長度儲存
    -- 台灣縣市名稱長度相對固定，使用 CHAR 可提升查詢效能
    station_city VARCHAR(20) NOT NULL,
    
    -- 觀測站所在行政區：最多 20 字元涵蓋所有台灣鄉鎮市區名稱
    station_district VARCHAR(20) NOT NULL,
    
    -- 觀測站緯度：DECIMAL(6,4) 提供精確度至小數點後四位
    -- 範圍涵蓋北緯 21 度至 25 度，精確度達公尺級
    station_latitude DECIMAL(6,4) NOT NULL,
    
    -- 觀測站經度：DECIMAL(7,4) 提供精確度至小數點後四位  
    -- 範圍涵蓋東經 118 度至 122 度，精確度達公尺級
    station_longitude DECIMAL(7,4) NOT NULL,
    
    -- 觀測站海拔高度：以公尺為單位，DECIMAL(6,2) 涵蓋台灣所有海拔高度
    -- 從海平面以下（負值）到玉山頂峰（約 3952 公尺）
    station_elevation DECIMAL(6,2),
    
    -- 當前氣溫：攝氏度，DECIMAL(5,2) 儲存範圍 -99.99 到 99.99
    -- 涵蓋台灣所有可能的氣溫範圍並保持兩位小數精確度
    current_temperature DECIMAL(5,2),
    
    -- 體感溫度：考慮風速與濕度的綜合溫度指標
    feels_like_temperature DECIMAL(5,2),
    
    -- 風速：以公尺/秒為單位，DECIMAL(5,2) 儲存至小數點後兩位
    -- 範圍涵蓋微風到颱風級強風
    wind_speed DECIMAL(5,2),
    
    -- 風向：使用 VARCHAR(10) 儲存方位名稱（如東北風、西南風）
    -- 或使用角度值（0-360 度）的文字表示
    wind_direction VARCHAR(10),
    
    -- 降雨量：以毫米為單位，DECIMAL(6,2) 可記錄從微量到豪雨的所有降雨量
    precipitation DECIMAL(6,2) DEFAULT 0.00,
    
    -- 氣壓：以百帕（hPa）為單位，DECIMAL(6,2) 涵蓋正常氣壓變化範圍
    atmospheric_pressure DECIMAL(6,2),
    
    -- 能見度：以公里為單位，DECIMAL(5,2) 記錄從濃霧到晴空的能見度
    visibility DECIMAL(5,2),
    
    -- 相對濕度：百分比值，DECIMAL(5,2) 儲存範圍 0.00 到 100.00
    relative_humidity DECIMAL(5,2),
    
    -- 天氣描述：文字描述當前天氣狀況（晴天、多雲、雨天等）
    weather_description VARCHAR(100),
    
    -- 日出時間：使用 TIME 型別儲存，格式為 HH:MM:SS
    sunrise_time TIME,
    
    -- 日落時間：使用 TIME 型別儲存，格式為 HH:MM:SS
    sunset_time TIME,
    
    -- 觀測時間：使用 DATETIME 型別記錄精確的觀測時間點
    -- 包含年月日與時分秒，支援時間範圍查詢與趨勢分析
    observation_datetime DATETIME NOT NULL,
    
    -- 資料建立時間：記錄資料寫入資料庫的時間
    -- DEFAULT CURRENT_TIMESTAMP 自動填入當前時間
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 資料更新時間：記錄資料最後修改的時間
    -- ON UPDATE CURRENT_TIMESTAMP 在資料更新時自動更新時間戳記
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- 建立複合索引以加速常見的查詢操作
    -- 依觀測站與時間查詢是最常見的需求
    INDEX idx_station_datetime (station_id, observation_datetime),
    
    -- 地理位置索引，支援空間查詢（需要 MySQL 5.7 以上版本）
    -- 可用於查詢特定範圍內的觀測站
    INDEX idx_location (station_latitude, station_longitude)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='氣象觀測站即時資料表，儲存台灣各地氣象站的觀測數據';

#!/bin/bash
##############################################################################
# 氣象資料自動化載入處理腳本
# 
# 功能說明：
# 1. 檢查待處理的氣象觀測資料檔案是否存在
# 2. 執行資料載入至暫存表
# 3. 驗證載入的資料品質
# 4. 將驗證通過的資料複製至正式資料表
# 5. 歸檔已處理的資料檔案
#
# 執行方式：透過 crontab 設定定時執行
# 範例：*/5 * * * * /opt/weather/scripts/load_weather_data.sh
#
# 作者：玄貓（BlackCat）
# 版本：1.0
# 最後更新：2025-12-01
##############################################################################

# 設定工作目錄為氣象資料存放路徑
WORK_DIR="/opt/weather/data"
cd "${WORK_DIR}" || exit 1

# 設定資料檔案名稱
DATA_FILE="weather_observation.csv"

# 設定日誌檔案路徑
LOG_DIR="/var/log/weather"
LOAD_LOG="${LOG_DIR}/load_weather.log"
COPY_LOG="${LOG_DIR}/copy_weather.log"
ERROR_LOG="${LOG_DIR}/error_weather.log"

# 設定 MySQL 連線參數
# 實務上應使用環境變數或配置檔管理敏感資訊
MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3306"
MYSQL_DB="weather_system"
MYSQL_USER="weather_app"
# 密碼應透過 mysql_config_editor 或環境變數設定，此處僅為示範
MYSQL_PASS="SecurePassword123"

# 建立 MySQL 連線指令基本參數
MYSQL_CMD="mysql --host=${MYSQL_HOST} --port=${MYSQL_PORT} \
           --database=${MYSQL_DB} --user=${MYSQL_USER} \
           --password=${MYSQL_PASS} --silent"

# 記錄腳本執行開始時間
echo "[$(date '+%Y-%m-%d %H:%M:%S')] 開始執行資料載入流程" >> "${ERROR_LOG}"

# 檢查資料檔案是否存在
if [ ! -f "${DATA_FILE}" ]; then
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] 警告：資料檔案 ${DATA_FILE} 不存在，結束執行" >> "${ERROR_LOG}"
    exit 0
fi

# 檢查資料檔案是否為空
if [ ! -s "${DATA_FILE}" ]; then
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] 警告：資料檔案 ${DATA_FILE} 為空檔案，結束執行" >> "${ERROR_LOG}"
    exit 0
fi

echo "[$(date '+%Y-%m-%d %H:%M:%S')] 發現資料檔案：${DATA_FILE}" >> "${ERROR_LOG}"

# 執行第一階段：載入資料至暫存表
# 使用 --local-infile=1 啟用本地檔案載入功能
echo "[$(date '+%Y-%m-%d %H:%M:%S')] 階段一：載入資料至暫存表" >> "${ERROR_LOG}"

${MYSQL_CMD} --local-infile=1 <<EOF > "${LOAD_LOG}" 2>&1

-- 清空暫存表，確保乾淨的載入環境
TRUNCATE TABLE weather_observation_staging;

-- 使用 LOAD DATA LOCAL INFILE 批次載入資料
-- IGNORE 1 ROWS 跳過 CSV 檔案的標題列
LOAD DATA LOCAL INFILE '${WORK_DIR}/${DATA_FILE}'
INTO TABLE weather_observation_staging
FIELDS TERMINATED BY ','        -- 欄位分隔符號為逗號
OPTIONALLY ENCLOSED BY '"'      -- 文字欄位可選用雙引號包圍
LINES TERMINATED BY '\n'        -- 換行符號為 Unix 格式
IGNORE 1 ROWS                   -- 跳過標題列
(station_id, station_name, station_city, station_district,
 @latitude, @longitude, @elevation,
 @temperature, @feels_like, @wind_speed, wind_direction,
 @precipitation, @pressure, @visibility, @humidity,
 weather_description, @sunrise, @sunset, @obs_datetime)
SET 
    -- 使用 STR_TO_DATE 函式轉換時間格式
    observation_datetime = STR_TO_DATE(@obs_datetime, '%Y%m%d %H:%i'),
    -- 數值欄位轉換，處理可能的空值情況
    station_latitude = NULLIF(@latitude, ''),
    station_longitude = NULLIF(@longitude, ''),
    station_elevation = NULLIF(@elevation, ''),
    current_temperature = NULLIF(@temperature, ''),
    feels_like_temperature = NULLIF(@feels_like, ''),
    wind_speed = NULLIF(@wind_speed, ''),
    precipitation = NULLIF(@precipitation, ''),
    atmospheric_pressure = NULLIF(@pressure, ''),
    visibility = NULLIF(@visibility, ''),
    relative_humidity = NULLIF(@humidity, ''),
    sunrise_time = STR_TO_DATE(@sunrise, '%H:%i:%s'),
    sunset_time = STR_TO_DATE(@sunset, '%H:%i:%s');

-- 顯示載入過程中的警告訊息
SHOW WARNINGS;

EOF

# 檢查載入日誌是否包含錯誤訊息
if [ -s "${LOAD_LOG}" ]; then
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] 警告：資料載入過程中出現訊息，詳見 ${LOAD_LOG}" >> "${ERROR_LOG}"
    cat "${LOAD_LOG}" >> "${ERROR_LOG}"
    
    # 若發現嚴重錯誤，終止執行
    if grep -qi "error" "${LOAD_LOG}"; then
        echo "[$(date '+%Y-%m-%d %H:%M:%S')] 錯誤：資料載入失敗，終止執行" >> "${ERROR_LOG}"
        exit 1
    fi
fi

# 執行第二階段：資料驗證與複製至正式表
echo "[$(date '+%Y-%m-%d %H:%M:%S')] 階段二：驗證資料並複製至正式表" >> "${ERROR_LOG}"

${MYSQL_CMD} <<EOF > "${COPY_LOG}" 2>&1

-- 資料品質驗證查詢
-- 檢查必要欄位是否有空值
SELECT 
    COUNT(*) as null_station_count
FROM weather_observation_staging
WHERE station_id IS NULL 
   OR station_name IS NULL 
   OR observation_datetime IS NULL;

-- 檢查數值範圍是否合理
SELECT 
    COUNT(*) as invalid_temperature_count
FROM weather_observation_staging
WHERE current_temperature < -50 
   OR current_temperature > 60;

-- 若驗證通過，將資料插入正式表
-- 使用 INSERT IGNORE 避免重複資料造成錯誤
INSERT IGNORE INTO weather_observation
SELECT * FROM weather_observation_staging
WHERE station_id IS NOT NULL
  AND station_name IS NOT NULL
  AND observation_datetime IS NOT NULL
  AND (current_temperature IS NULL 
       OR (current_temperature >= -50 AND current_temperature <= 60));

-- 回報處理結果
SELECT ROW_COUNT() as rows_inserted;

EOF

# 檢查複製結果
if [ $? -eq 0 ]; then
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] 成功：資料已複製至正式表" >> "${ERROR_LOG}"
else
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] 錯誤：資料複製失敗" >> "${ERROR_LOG}"
    exit 1
fi

# 歸檔已處理的資料檔案
# 使用時間戳記重新命名，保留處理記錄
ARCHIVE_DIR="/opt/weather/archive"
TIMESTAMP=$(date +%Y%m%d%H%M%S)
ARCHIVE_FILE="${ARCHIVE_DIR}/weather_observation_${TIMESTAMP}.csv"

mkdir -p "${ARCHIVE_DIR}"
mv "${DATA_FILE}" "${ARCHIVE_FILE}"

if [ $? -eq 0 ]; then
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] 資料檔案已歸檔：${ARCHIVE_FILE}" >> "${ERROR_LOG}"
else
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] 警告：資料檔案歸檔失敗" >> "${ERROR_LOG}"
fi

# 清理超過 30 天的歷史歸檔檔案
find "${ARCHIVE_DIR}" -name "weather_observation_*.csv" -mtime +30 -delete

echo "[$(date '+%Y-%m-%d %H:%M:%S')] 資料載入流程執行完成" >> "${ERROR_LOG}"
echo "----------------------------------------" >> "${ERROR_LOG}"

exit 0

-- 選舉投票系統資料庫架構完整設計
-- 展示主鍵、外鍵、唯一性約束、檢查約束等資料完整性機制

-- 建立投票系統資料庫
CREATE DATABASE IF NOT EXISTS election_system
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci
    COMMENT '台灣選舉投票管理系統';

USE election_system;

-- 選民資料表：儲存所有合格選民的基本資訊與投票資格
CREATE TABLE voter (
    -- 選民系統識別碼：自動遞增主鍵，確保每位選民的唯一性
    voter_id INT PRIMARY KEY AUTO_INCREMENT 
        COMMENT '選民唯一識別碼',
    
    -- 選民姓名：依據個人資料保護法，姓名屬於敏感資訊需加密儲存
    voter_name VARCHAR(50) NOT NULL 
        COMMENT '選民姓名（實務上應加密儲存）',
    
    -- 戶籍地址：完整戶籍地址，決定投票資格與選區
    voter_address VARCHAR(200) NOT NULL 
        COMMENT '戶籍地址',
    
    -- 縣市：台灣行政區劃第一層級
    voter_county VARCHAR(20) NOT NULL 
        COMMENT '戶籍所在縣市',
    
    -- 選區：立委或議員選舉的選區編號
    voter_district VARCHAR(10) NOT NULL 
        COMMENT '選舉區代碼',
    
    -- 投開票所：選民應前往投票的投開票所編號
    voter_precinct VARCHAR(10) NOT NULL 
        COMMENT '投開票所編號',
    
    -- 政黨傾向：選民登記的政黨傾向，可為空值（選民有權不表態）
    voter_party VARCHAR(30) NULL 
        COMMENT '政黨傾向（可為空）',
    
    -- 投票地點：投開票所的完整地址
    voting_location VARCHAR(150) NOT NULL 
        COMMENT '投票地點地址',
    
    -- 選民登記號碼：由戶政機關核發的唯一選民編號
    -- UNIQUE 約束確保不會有重複登記
    voter_registration_number VARCHAR(20) NOT NULL UNIQUE 
        COMMENT '選民登記證號碼（唯一）',
    
    -- 選民資格狀態：啟用或停權（例如因刑事判決停權）
    voter_status ENUM('active', 'suspended', 'deceased') 
        NOT NULL DEFAULT 'active' 
        COMMENT '選民資格狀態',
    
    -- 資料建立時間
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
        COMMENT '資料建立時間',
    
    -- 資料更新時間
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
        ON UPDATE CURRENT_TIMESTAMP 
        COMMENT '資料最後更新時間',
    
    -- 建立複合索引加速依縣市、選區查詢
    INDEX idx_county_district (voter_county, voter_district),
    
    -- 投開票所索引，方便統計各所選民人數
    INDEX idx_precinct (voter_precinct)
    
) ENGINE=InnoDB 
COMMENT='選民基本資料表，儲存所有合格選民資訊';

-- 選票資料表：記錄每一張投出的選票
CREATE TABLE ballot (
    -- 選票系統識別碼
    ballot_id INT PRIMARY KEY AUTO_INCREMENT 
        COMMENT '選票唯一識別碼',
    
    -- 選民識別碼：外鍵關聯至選民表
    -- UNIQUE 約束確保一位選民只能投一張票
    voter_id INT NOT NULL UNIQUE 
        COMMENT '投票選民 ID（外鍵，唯一）',
    
    -- 選票號碼：實體選票上的序號，用於對帳
    ballot_number VARCHAR(30) NOT NULL UNIQUE 
        COMMENT '選票序號（唯一）',
    
    -- 投票時間：精確記錄投票的時間點
    cast_datetime DATETIME NOT NULL 
        COMMENT '投票時間',
    
    -- 投票方式：親自投票或通訊投票
    cast_method ENUM('in_person', 'absentee') NOT NULL 
        COMMENT '投票方式',
    
    -- 資料建立時間
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
        COMMENT '記錄建立時間',
    
    -- 外鍵約束：確保投票的選民必須存在於選民表中
    CONSTRAINT fk_ballot_voter 
        FOREIGN KEY (voter_id) 
        REFERENCES voter(voter_id)
        ON DELETE RESTRICT   -- 禁止刪除已投票的選民記錄
        ON UPDATE CASCADE,   -- 選民 ID 更新時同步更新
    
    -- 投票時間索引，方便分析投票時段分布
    INDEX idx_cast_datetime (cast_datetime)
    
) ENGINE=InnoDB 
COMMENT='選票記錄表，儲存每張選票的投票資訊';

-- 選舉項目資料表：定義本次選舉包含的各項選舉
CREATE TABLE race (
    -- 選舉項目識別碼
    race_id INT PRIMARY KEY AUTO_INCREMENT 
        COMMENT '選舉項目唯一識別碼',
    
    -- 選舉項目名稱：例如「台北市市長」、「立法委員第一選區」
    race_name VARCHAR(100) NOT NULL UNIQUE 
        COMMENT '選舉項目名稱（唯一）',
    
    -- 選舉類型：總統、立委、縣市長、議員等
    race_type ENUM('president', 'legislator', 'mayor', 'councilor', 'other') 
        NOT NULL 
        COMMENT '選舉類型',
    
    -- 允許投票數：單選為 1，複選則為可投票數
    votes_allowed INT NOT NULL DEFAULT 1 
        COMMENT '允許投票數（單選=1）',
    
    -- 選舉日期
    election_date DATE NOT NULL 
        COMMENT '選舉投票日',
    
    -- 資料建立時間
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
        COMMENT '資料建立時間',
    
    -- 檢查約束：確保允許投票數為正整數
    CONSTRAINT chk_votes_allowed 
        CHECK (votes_allowed > 0)
    
) ENGINE=InnoDB 
COMMENT='選舉項目定義表，列舉本次選舉的所有選舉項目';

-- 候選人資料表：儲存參選人資訊
CREATE TABLE candidate (
    -- 候選人識別碼
    candidate_id INT PRIMARY KEY AUTO_INCREMENT 
        COMMENT '候選人唯一識別碼',
    
    -- 選舉項目識別碼：候選人參選的項目
    race_id INT NOT NULL 
        COMMENT '參選項目 ID（外鍵）',
    
    -- 候選人姓名
    candidate_name VARCHAR(50) NOT NULL 
        COMMENT '候選人姓名',
    
    -- 候選人號次：選票上的號碼
    candidate_number INT NOT NULL 
        COMMENT '候選人號次',
    
    -- 候選人地址
    candidate_address VARCHAR(200) NOT NULL 
        COMMENT '候選人戶籍地址',
    
    -- 政黨：候選人所屬政黨，無黨籍可為空
    candidate_party VARCHAR(30) NULL 
        COMMENT '所屬政黨（無黨籍可空）',
    
    -- 現任標誌：是否為現任民意代表
    incumbent_flag BOOLEAN DEFAULT FALSE 
        COMMENT '現任標誌',
    
    -- 資料建立時間
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
        COMMENT '資料建立時間',
    
    -- 外鍵約束：確保候選人參選的項目必須存在
    CONSTRAINT fk_candidate_race 
        FOREIGN KEY (race_id) 
        REFERENCES race(race_id)
        ON DELETE RESTRICT 
        ON UPDATE CASCADE,
    
    -- 唯一性約束：同一選舉項目中候選人號次不可重複
    CONSTRAINT uk_race_number 
        UNIQUE (race_id, candidate_number),
    
    -- 選舉項目索引
    INDEX idx_race (race_id)
    
) ENGINE=InnoDB 
COMMENT='候選人資料表，儲存參選人基本資訊';

-- 選票明細資料表：記錄選票上勾選的候選人
CREATE TABLE ballot_candidate (
    -- 選票識別碼
    ballot_id INT NOT NULL 
        COMMENT '選票 ID（外鍵）',
    
    -- 候選人識別碼
    candidate_id INT NOT NULL 
        COMMENT '候選人 ID（外鍵）',
    
    -- 勾選時間：記錄該候選人被勾選的時間
    selected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
        COMMENT '勾選時間',
    
    -- 複合主鍵：確保同一張選票不會重複勾選同一位候選人
    PRIMARY KEY (ballot_id, candidate_id),
    
    -- 外鍵約束：確保選票必須存在
    CONSTRAINT fk_bc_ballot 
        FOREIGN KEY (ballot_id) 
        REFERENCES ballot(ballot_id)
        ON DELETE CASCADE    -- 選票刪除時一併刪除明細
        ON UPDATE CASCADE,
    
    -- 外鍵約束：確保候選人必須存在  
    CONSTRAINT fk_bc_candidate 
        FOREIGN KEY (candidate_id) 
        REFERENCES candidate(candidate_id)
        ON DELETE RESTRICT 
        ON UPDATE CASCADE,
    
    -- 候選人索引，方便統計得票數
    INDEX idx_candidate (candidate_id)
    
) ENGINE=InnoDB 
COMMENT='選票明細表，記錄選票上勾選的候選人';

-- 選民資料變更歷程表：追蹤選民資料的所有變更記錄
CREATE TABLE voter_change_log (
    -- 變更記錄識別碼
    log_id BIGINT PRIMARY KEY AUTO_INCREMENT 
        COMMENT '變更記錄唯一識別碼',
    
    -- 選民識別碼
    voter_id INT NOT NULL 
        COMMENT '變更的選民 ID',
    
    -- 變更類型：新增、更新、刪除
    change_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL 
        COMMENT '變更類型',
    
    -- 變更欄位名稱
    field_name VARCHAR(50) NULL 
        COMMENT '變更的欄位名稱',
    
    -- 變更前值
    old_value VARCHAR(200) NULL 
        COMMENT '變更前的值',
    
    -- 變更後值
    new_value VARCHAR(200) NULL 
        COMMENT '變更後的值',
    
    -- 變更原因
    change_reason VARCHAR(200) NULL 
        COMMENT '變更原因說明',
    
    -- 變更執行者
    changed_by VARCHAR(50) NOT NULL 
        COMMENT '執行變更的使用者',
    
    -- 變更時間
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
        COMMENT '變更執行時間',
    
    -- 選民索引
    INDEX idx_voter (voter_id),
    
    -- 變更時間索引
    INDEX idx_changed_at (changed_at)
    
) ENGINE=InnoDB 
COMMENT='選民資料變更歷程表，記錄所有資料異動';