您现在的位置：程式碼之家 >> 編程語言 > >> 網頁程式設計 >> PHP 程式設計 >> PHP 相關知識

PHP 資訊發布系統資料庫設計與後台管理表結構完整解析

編輯：PHP 相關知識

系統概覽

典型資訊發布系統 (CMS/公告/文章) 核心實體：管理員、權限選項、分類、文章、附件、評論。本文解析提供的 SQL 結構，補充缺失表、優化建議、現代化遷移方向。

現有表結構分析

1. 管理員表 (jb_administrators)

CREATE TABLE `jb_administrators` (
  `administratorsID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `userID` mediumint(6) unsigned NOT NULL DEFAULT 0,
  `administratorsGroupID` int(10) unsigned NOT NULL DEFAULT 0,
  `administratorsName` varchar(50) NOT NULL DEFAULT '',
  `password` varchar(32) NOT NULL DEFAULT '',  -- ⚠️ MD5 不安全
  `email` varchar(50) NOT NULL DEFAULT '',
  `ipAddress` varchar(32) NOT NULL DEFAULT '',
  `isAdministrators` enum('y','n') NOT NULL DEFAULT 'n',
  `orderByID` int(10) unsigned NOT NULL DEFAULT 0,
  `createDate` bigint(14) unsigned NOT NULL DEFAULT 0, -- 時間戳
  PRIMARY KEY (`administratorsID`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

問題：password 32 字元暗示 MD5，極不安全；createDate 用 bigint 而非 DATETIME；MyISAM 不支援交易/外鍵。

2. 管理員選項表 (jb_administratorsoption)

CREATE TABLE `jb_administratorsoption` (
  `administratorsOptionID` int(8) NOT NULL AUTO_INCREMENT,
  `userID` mediumint(6) unsigned NOT NULL DEFAULT 0,
  `optionFieldName` varchar(50) NOT NULL DEFAULT '',
  `length` tinyint(2) unsigned NOT NULL DEFAULT 40,
  `rows` tinyint(2) unsigned NOT NULL DEFAULT 4,
  `types` varchar(10) NOT NULL DEFAULT '',  -- 'text', 'select', 'checkbox'...
  `isPublic` enum('y','n') NOT NULL DEFAULT 'y',
  `orderByID` int(8) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`administratorsOptionID`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;

用途：動態表單欄位配置 (如「聯絡電話」text、長度 40)。靈活但查詢複雜，現代建議用 JSON 欄位或 EAV 反模式謹慎使用。

缺失核心表補充

3. 分類表 (categories)

CREATE TABLE `categories` (
  `cat_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cat_name` varchar(100) NOT NULL,
  `cat_slug` varchar(120) NOT NULL, -- URL 友善
  `cat_parent_id` int(10) unsigned NOT NULL DEFAULT 0,
  `cat_depth` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `cat_path` varchar(500) NOT NULL DEFAULT '', -- 0/1/5/ 便於查子樹
  `cat_sort` int(10) unsigned NOT NULL DEFAULT 0,
  `cat_status` tinyint(1) unsigned NOT NULL DEFAULT 1,
  `cat_seo_title` varchar(200) DEFAULT '',
  `cat_seo_keywords` varchar(300) DEFAULT '',
  `cat_seo_desc` varchar(500) DEFAULT '',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`cat_id`),
  UNIQUE KEY `uk_slug` (`cat_slug`),
  KEY `idx_parent` (`cat_parent_id`),
  KEY `idx_path` (`cat_path`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

4. 文章/資訊表 (articles)

CREATE TABLE `articles` (
  `art_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `cat_id` int(10) unsigned NOT NULL DEFAULT 0,
  `admin_id` int(10) unsigned NOT NULL DEFAULT 0,
  `art_title` varchar(255) NOT NULL,
  `art_slug` varchar(280) NOT NULL,
  `art_summary` varchar(500) DEFAULT '',
  `art_content` longtext NOT NULL, -- 大文本
  `art_thumb` varchar(500) DEFAULT '', -- 縮圖
  `art_status` tinyint(1) unsigned NOT NULL DEFAULT 0, -- 0草稿 1發布 2下架
  `art_is_top` tinyint(1) unsigned NOT NULL DEFAULT 0, -- 置頂
  `art_view_count` int(10) unsigned NOT NULL DEFAULT 0,
  `art_like_count` int(10) unsigned NOT NULL DEFAULT 0,
  `publish_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`art_id`),
  UNIQUE KEY `uk_slug` (`art_slug`),
  KEY `idx_cat_status` (`cat_id`, `art_status`),
  KEY `idx_admin` (`admin_id`),
  KEY `idx_publish` (`publish_at`),
  FULLTEXT KEY `ft_title_content` (`art_title`, `art_content`) -- 全文搜尋
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

5. 附件表 (attachments)

CREATE TABLE `attachments` (
  `att_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `art_id` bigint(20) unsigned NOT NULL DEFAULT 0,
  `att_name` varchar(255) NOT NULL,
  `att_path` varchar(500) NOT NULL, -- 相對儲存路徑
  `att_mime` varchar(100) NOT NULL,
  `att_size` bigint(20) unsigned NOT NULL DEFAULT 0,
  `att_ext` varchar(20) NOT NULL,
  `att_downloads` int(10) unsigned NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`att_id`),
  KEY `idx_art` (`art_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

6. 權限角色表 (RBAC 核心)

CREATE TABLE `roles` (
  `role_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `role_name` varchar(50) NOT NULL,
  `role_slug` varchar(60) NOT NULL,
  `role_desc` varchar(200) DEFAULT '',
  PRIMARY KEY (`role_id`),
  UNIQUE KEY `uk_slug` (`role_slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `permissions` (
  `perm_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `perm_name` varchar(100) NOT NULL, -- 'article.create', 'article.edit', 'category.delete'
  `perm_desc` varchar(200) DEFAULT '',
  PRIMARY KEY (`perm_id`),
  UNIQUE KEY `uk_name` (`perm_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `role_permissions` (
  `role_id` int(10) unsigned NOT NULL,
  `perm_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`role_id`, `perm_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `admin_roles` (
  `admin_id` int(10) unsigned NOT NULL,
  `role_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`admin_id`, `role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

優化建議總表

項目	現狀	建議	優先級
儲存引擎	MyISAM	全表遷移 InnoDB (交易、外鍵、行鎖、崩潰恢復)	🔴 高
字符集	未指定/預設 latin1	全庫 `CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci` (支援 Emoji)	🔴 高
密碼儲存	MD5 (32字元)	`password_hash()` + `password_verify()` (bcrypt/argon2)，欄位擴充至 varchar(255)	🔴 高
時間欄位	bigint 時間戳	改 `DATETIME` / `TIMESTAMP`，原生函數支援、時區感知	🟡 中
軟刪除	無	加 `deleted_at` DATETIME NULL，查詢自動過濾	🟡 中
分類無限級	無/僅兩層	`cat_path` (Closure Table 更佳) + 遞歸 CTE 查詢	🟡 中
全文搜尋	無	InnoDB FULLTEXT 或接入 Elasticsearch/Meilisearch	🟢 低
審計日誌	無	新增 `admin_logs` 記錄關鍵操作 (誰、何時、什麼、IP、UA)	🟡 中

現代化遷移路徑

資料庫層：建立新 InnoDB/utf8mb4 表 → 寫遷移腳本 (PHP/Phinx/Laravel Migration) → 歷史資料轉換 (MD5→bcrypt、bigint→DATETIME) → 雙寫驗證 → 切換讀寫。
應用層：引入 ORM (Eloquent/Doctrine/Cycle) 或 Query Builder，棄用原生 mysql_*/mysqli 程式碼。
密碼升級：登入時檢測舊雜湊，驗證成功後重新 password_hash() 存入，漸進式遷移。
API 化：後台管理改為 SPA (Vue/React) + REST/GraphQL API，前後端分離。