🎯 核心理念
- 數據存儲：原始 YAML 內容，無需手動縮進
- 格式處理：查詢時自動添加正確縮進
- 用戶友好：編輯時只需關注配置內容，不用擔心格式
📊 完整 SQL 建立腳本
sql
-- Docker 配置管理系統 v2.2 - 智能縮進版本
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- 資料庫： `repiddeploy`
--
-- --------------------------------------------------------
--
-- Docker 配置主表 v2.2 - 智能縮進版
--
CREATE TABLE `docker_configs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`config_name` varchar(100) NOT NULL COMMENT '配置名稱 如：main-stack',
`service` varchar(50) NOT NULL COMMENT '服務名稱：apache, mariadb, phpmyadmin, composer, redis 等',
`description` text DEFAULT NULL COMMENT '服務描述',
`config_data` longtext NOT NULL COMMENT '服務的原始YAML配置內容（無需手動縮進）',
`service_order` tinyint(3) DEFAULT 0 COMMENT '服務在 compose 中的排序順序',
`is_active` tinyint(1) DEFAULT 1 COMMENT '是否啟用',
`created_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT '創建時間',
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '更新時間',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_config_service` (`config_name`, `service`),
KEY `idx_config_active` (`config_name`, `is_active`),
KEY `idx_service_type` (`service`),
KEY `idx_order` (`config_name`, `service_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Docker配置主表-智能縮進版';
-- --------------------------------------------------------
--
-- Docker 檔案管理表 v2.2
--
CREATE TABLE `docker_files` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`config_name` varchar(100) NOT NULL COMMENT '配置名稱',
`service` varchar(50) DEFAULT NULL COMMENT '關聯服務名稱（可為空）',
`file_name` varchar(255) NOT NULL COMMENT '檔案名稱',
`file_path` varchar(500) DEFAULT NULL COMMENT '檔案路徑',
`file_type` enum('dockerfile','config','script','compose','other') NOT NULL DEFAULT 'other' COMMENT '檔案類型',
`file_content` longtext NOT NULL COMMENT '檔案內容',
`description` text DEFAULT NULL COMMENT '檔案描述',
`is_active` tinyint(1) DEFAULT 1 COMMENT '是否啟用',
`created_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT '創建時間',
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '更新時間',
PRIMARY KEY (`id`),
KEY `idx_config_service` (`config_name`, `service`),
KEY `idx_type_active` (`file_type`, `is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Docker相關檔案表';
-- --------------------------------------------------------
--
-- 配置備份表 v2.2
--
CREATE TABLE `docker_configs_backup` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`config_name` varchar(100) NOT NULL COMMENT '配置名稱',
`service` varchar(50) NOT NULL COMMENT '服務名稱',
`config_data` longtext NOT NULL COMMENT '備份的配置數據',
`backup_note` varchar(255) DEFAULT NULL COMMENT '備份備註',
`backup_type` enum('manual','auto','pre_update') DEFAULT 'manual' COMMENT '備份類型',
`created_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT '備份時間',
PRIMARY KEY (`id`),
KEY `idx_config_service_time` (`config_name`, `service`, `created_at` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='配置備份表';
-- --------------------------------------------------------
--
-- 插入示例數據（原始格式，無需手動縮進）
--
-- 插入 main-stack 配置的各個服務
INSERT INTO `docker_configs` (`config_name`, `service`, `description`, `config_data`, `service_order`) VALUES
('main-stack', 'apache', 'PHP 8.4 Apache 服務',
'build:
context: .
dockerfile: Dockerfile
container_name: apache
environment:
- TZ=Asia/Hong_Kong
ports:
- "8080:80"
volumes:
- /home/docker/www:/var/www/html
- /home/docker/php.ini:/usr/local/etc/php/php.ini
- /etc/localtime:/etc/localtime:ro
- /home/docker/logs/cron.log:/home/docker/logs/cron.log
depends_on:
- mariadb
restart: always', 1),
('main-stack', 'mariadb', 'MariaDB 資料庫服務',
'image: mariadb:latest
container_name: mariadb
restart: always
environment:
MYSQL_ROOT_PASSWORD: ********
MYSQL_DATABASE: repiddeploy
MYSQL_USER: repiddeploy
MYSQL_PASSWORD: ********
TZ: Asia/Hong_Kong
ports:
- "3306:3306"
volumes:
- /home/docker/db_data:/var/lib/mysql
- /etc/localtime:/etc/localtime:ro', 2),
('main-stack', 'phpmyadmin', 'phpMyAdmin 管理介面',
'image: phpmyadmin/phpmyadmin
container_name: phpmyadmin
restart: always
environment:
PMA_HOST: mariadb
PMA_PORT: 3306
MYSQL_ROOT_PASSWORD: ********
ports:
- "8081:80"
depends_on:
- mariadb', 3),
('main-stack', 'composer', 'Composer PHP 依賴管理',
'image: composer:latest
container_name: composer
working_dir: /app
volumes:
- /home/docker/www:/app', 4);
-- 插入 Dockerfile
INSERT INTO `docker_files` (`config_name`, `service`, `file_name`, `file_path`, `file_type`, `file_content`, `description`) VALUES
('main-stack', 'apache', 'Dockerfile', './Dockerfile', 'dockerfile',
'FROM php:8.4-apache
# 安裝 APCu、Nano 和其他 PHP 擴展
RUN apt-get update && apt-get install -y \\
nano \\
mariadb-client \\
libicu-dev \\
libzip-dev \\
libfreetype6-dev \\
libjpeg62-turbo-dev \\
libpng-dev \\
libwebp-dev \\
unzip \\
&& docker-php-ext-install intl zip mysqli gd \\
&& pecl install apcu redis \\
&& docker-php-ext-enable apcu redis \\
&& apt-get clean && rm -rf /var/lib/apt/lists/*
RUN docker-php-ext-install opcache
# 設置 ServerName
RUN echo "ServerName localhost" >> /etc/apache2/apache2.conf
# 啟用必要的 Apache 模組，包括 mod_rewrite
RUN a2enmod rewrite
# 設置網站目錄權限（根據需要）
WORKDIR /var/www/html',
'PHP 8.4 Apache with extensions');
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
🚀 核心查詢操作
📋 生成完整 docker-compose.yml（智能縮進）
sql
-- 智能縮進版：自動處理所有格式
SELECT
CONCAT(
'services:\n',
GROUP_CONCAT(
CONCAT(' ', service, ':\n ', REPLACE(config_data, '\n', '\n '))
ORDER BY service_order, service
SEPARATOR '\n'
)
) as docker_compose_yml
FROM docker_configs
WHERE config_name = 'main-stack' AND is_active = 1;
🔍 查看所有服務
sql
-- 查看配置中的所有服務
SELECT
service,
description,
is_active,
service_order,
updated_at
FROM docker_configs
WHERE config_name = 'main-stack'
ORDER BY service_order, service;
🎯 查看單一服務配置
sql
-- 查看 Apache 服務的詳細配置
SELECT
service,
description,
config_data,
service_order,
updated_at
FROM docker_configs
WHERE config_name = 'main-stack'
AND service = 'apache'
AND is_active = 1;
📊 配置概覽
sql
-- 配置統計概覽
SELECT
config_name,
COUNT(*) as total_services,
COUNT(CASE WHEN is_active = 1 THEN 1 END) as active_services,
MAX(updated_at) as last_modified
FROM docker_configs
GROUP BY config_name;
✏️ 編輯操作 — 用戶友好版
➕ 添加新服務（原始格式，無需縮進）
sql
-- 添加 Redis 服務 - 用戶只需輸入原始內容
INSERT INTO docker_configs (config_name, service, description, config_data, service_order) VALUES
('main-stack', 'redis', 'Redis 記憶體資料庫',
'image: redis:7-alpine
container_name: redis
restart: always
ports:
- "6379:6379"
volumes:
- /home/docker/redis-data:/data
command: redis-server --appendonly yes', 5);
🔧 編輯服務配置（原始格式）
sql
-- 修改 Apache 服務端口 - 直接編輯原始內容
UPDATE docker_configs
SET config_data = 'build:
context: .
dockerfile: Dockerfile
container_name: apache
environment:
- TZ=Asia/Hong_Kong
ports:
- "8888:80"
volumes:
- /home/docker/www:/var/www/html
- /home/docker/php.ini:/usr/local/etc/php/php.ini
- /etc/localtime:/etc/localtime:ro
depends_on:
- mariadb
restart: always',
updated_at = NOW()
WHERE config_name = 'main-stack' AND service = 'apache';
❌ 移除服務
sql
-- 停用服務（軟刪除）
UPDATE docker_configs
SET is_active = 0, updated_at = NOW()
WHERE config_name = 'main-stack' AND service = 'phpmyadmin';
-- 永久刪除服務
DELETE FROM docker_configs
WHERE config_name = 'main-stack' AND service = 'phpmyadmin';
🔄 調整服務順序
sql
-- 重新排列服務順序
UPDATE docker_configs SET service_order = 1 WHERE config_name = 'main-stack' AND service = 'mariadb';
UPDATE docker_configs SET service_order = 2 WHERE config_name = 'main-stack' AND service = 'apache';
UPDATE docker_configs SET service_order = 3 WHERE config_name = 'main-stack' AND service = 'phpmyadmin';
UPDATE docker_configs SET service_order = 4 WHERE config_name = 'main-stack' AND service = 'composer';
🔀 批量操作
sql
-- 批量端口替換
UPDATE docker_configs
SET config_data = REPLACE(config_data, '"8080:80"', '"9080:80"'),
updated_at = NOW()
WHERE config_name = 'main-stack' AND service = 'apache';
-- 批量環境變數替換
UPDATE docker_configs
SET config_data = REPLACE(config_data, 'TZ: Asia/Hong_Kong', 'TZ: Asia/Taipei'),
updated_at = NOW()
WHERE config_name = 'main-stack';
🔧 實用工具查詢
💾 備份操作
sql
-- 備份整個配置
INSERT INTO docker_configs_backup (config_name, service, config_data, backup_note, backup_type)
SELECT config_name, service, config_data, '升級前備份', 'pre_update'
FROM docker_configs
WHERE config_name = 'main-stack' AND is_active = 1;
-- 備份單一服務
INSERT INTO docker_configs_backup (config_name, service, config_data, backup_note)
SELECT config_name, service, config_data, '手動備份 Apache'
FROM docker_configs
WHERE config_name = 'main-stack' AND service = 'apache';
📦 生成完整部署包
sql
-- 獲取完整部署包（包含所有檔案）
SELECT
'docker-compose.yml' as filename,
'compose' as file_type,
(SELECT
CONCAT(
'services:\n',
GROUP_CONCAT(
CONCAT(' ', service, ':\n ', REPLACE(config_data, '\n', '\n '))
ORDER BY service_order, service
SEPARATOR '\n'
)
)
FROM docker_configs
WHERE config_name = 'main-stack' AND is_active = 1
) as file_content
UNION ALL
SELECT
df.file_name as filename,
df.file_type,
df.file_content
FROM docker_files df
WHERE df.config_name = 'main-stack' AND df.is_active = 1
ORDER BY
CASE WHEN filename = 'docker-compose.yml' THEN 1 ELSE 2 END,
filename;
🔍 服務依賴分析
sql
-- 分析服務依賴關係
SELECT
service,
CASE
WHEN config_data LIKE '%depends_on:%' THEN
TRIM(SUBSTRING(
config_data,
LOCATE('depends_on:', config_data) + 11,
CASE
WHEN LOCATE('\n', config_data, LOCATE('depends_on:', config_data) + 11) > 0
THEN LOCATE('\n', config_data, LOCATE('depends_on:', config_data) + 11) - LOCATE('depends_on:', config_data) - 11
ELSE LENGTH(config_data)
END
))
ELSE 'No dependencies'
END as dependencies
FROM docker_configs
WHERE config_name = 'main-stack' AND is_active = 1;
📈 配置變更歷史
sql
-- 查看配置變更歷史
SELECT
service,
backup_note,
backup_type,
created_at as backup_time
FROM docker_configs_backup
WHERE config_name = 'main-stack'
ORDER BY created_at DESC;
✅ 配置驗證
sql
-- 檢查配置完整性
SELECT
service,
CASE
WHEN config_data LIKE '%image:%' OR config_data LIKE '%build:%' THEN '✓ 有'
ELSE '✗ 缺少'
END as has_source,
CASE
WHEN config_data LIKE '%container_name:%' THEN '✓ 有'
ELSE '✗ 缺少'
END as has_container_name,
CASE
WHEN config_data LIKE '%ports:%' THEN '✓ 有'
ELSE '⚠ 無'
END as has_ports,
service_order,
is_active
FROM docker_configs
WHERE config_name = 'main-stack'
ORDER BY service_order;
💡 基本 PHP 整合示例
php
<?php
class DockerConfigManager {
private $pdo;
public function __construct($pdo) {
$this->pdo = $pdo;
}
// 生成 docker-compose.yml
public function generateCompose($configName) {
$sql = "SELECT CONCAT('services:\n', GROUP_CONCAT(CONCAT(' ', service, ':\n ', REPLACE(config_data, '\n', '\n ')) ORDER BY service_order, service SEPARATOR '\n')) as compose FROM docker_configs WHERE config_name = ? AND is_active = 1";
$stmt = $this->pdo->prepare($sql);
$stmt->execute([$configName]);
return $stmt->fetchColumn();
}
// 獲取所有服務
public function getServices($configName) {
$sql = "SELECT * FROM docker_configs WHERE config_name = ? ORDER BY service_order, service";
$stmt = $this->pdo->prepare($sql);
$stmt->execute([$configName]);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// 添加服務
public function addService($configName, $service, $configData, $description = null, $order = 999) {
$sql = "INSERT INTO docker_configs (config_name, service, description, config_data, service_order) VALUES (?, ?, ?, ?, ?)";
$stmt = $this->pdo->prepare($sql);
return $stmt->execute([$configName, $service, $description, $configData, $order]);
}
// 更新服務
public function updateService($configName, $service, $configData) {
$sql = "UPDATE docker_configs SET config_data = ?, updated_at = NOW() WHERE config_name = ? AND service = ?";
$stmt = $this->pdo->prepare($sql);
return $stmt->execute([$configData, $configName, $service]);
}
// 刪除服務
public function removeService($configName, $service) {
$sql = "DELETE FROM docker_configs WHERE config_name = ? AND service = ?";
$stmt = $this->pdo->prepare($sql);
return $stmt->execute([$configName, $service]);
}
}
// 使用示例
$pdo = new PDO("mysql:host=localhost;dbname=repiddeploy", $username, $password);
$manager = new DockerConfigManager($pdo);
// 生成 docker-compose.yml
echo $manager->generateCompose('main-stack');
// 添加新服務
$nginxConfig = 'image: nginx:alpine
container_name: nginx
ports:
- "80:80"
volumes:
- ./nginx.conf:/etc/nginx/nginx.conf';
$manager->addService('main-stack', 'nginx', $nginxConfig, 'Nginx reverse proxy');
?>
🎯 新版本特點
✅ 極簡設計
✅ 用戶友好
✅ 高效查詢
- 單一查詢生成完美 docker-compose.yml
- 簡潔的 SQL，易於維護
✅ 擴展便利
這個精簡版本保持了核心功能的完整性，同時去除了不必要的複雜性！
