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

桌面中心系統實戰（一）：MySQL多表創建與分類存儲設計

編輯：PHP 相關知識

本文作為「桌面中心」系列首篇，記錄MySQL數據庫建表腳本，創建5張結構相同的表（jpg至jpg4）分別存儲5類桌面壁紙數據，每表包含13字段存放4組圖片URL、縮圖、說明及主鍵ID。

建表SQL腳本

-- 登入MySQL後執行：
CREATE TABLE jpg (
    id INT(8) NOT NULL AUTO_INCREMENT,
    url CHAR(255) NOT NULL,
    mess CHAR(100) NOT NULL,
    fromx CHAR(50) NOT NULL,
    url1 CHAR(255) NOT NULL,
    mess1 CHAR(100) NOT NULL,
    fromx1 CHAR(50) NOT NULL,
    url2 CHAR(255) NOT NULL,
    mess2 CHAR(100) NOT NULL,
    fromx2 CHAR(50) NOT NULL,
    url3 CHAR(255) NOT NULL,
    mess3 CHAR(100) NOT NULL,
    fromx3 CHAR(50) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE jpg1 ( ... 結構同jpg ... );
CREATE TABLE jpg2 ( ... 結構同jpg ... );
CREATE TABLE jpg3 ( ... 結構同jpg ... );
CREATE TABLE jpg4 ( ... 結構同jpg ... );

設計意圖分析

分表存儲：按類別（水彩畫、明星寫真、動漫、廣告、其他）建立獨立表
橫向展開：單行存儲4組圖片，適配前端2×2表格佈局
字段類型：CHAR定長存儲，早期MyISAM引擎下查詢較快，但浪費空間

關鍵缺陷與規範化重構

缺陷	影響	重構方案
重複表結構	DDL維護噩夢，修改結構需執行5次	單表`wallpapers`+`category_id`外鍵
橫向冗餘字段	第5組圖片需ALTER TABLE，第N組同理	一圖一行：`image_url, thumb_url, description`
CHAR定長浪費	存儲空間膨脹，`CHAR(255)`實佔255字節	`VARCHAR(255)`或`TEXT`按實際長度存儲
無外鍵約束	類別完整性無保障	建立`categories`表，外鍵關聯
無時間戳	無法排序/審計/清理舊數據	添加`created_at`、`updated_at`
無索引優化	按類別/時間查詢全表掃描	組合索引`(category_id, created_at)`

現代化設計：單表多類別

CREATE TABLE categories (
    id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL UNIQUE,
    slug VARCHAR(20) NOT NULL UNIQUE,  -- url友好標識
    sort_order TINYINT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO categories (name, slug) VALUES
('水彩畫', 'watercolor'),
('明星寫真', 'celebrity'),
('動漫桌布', 'anime'),
('廣告創意', 'advertising'),
('其他', 'others');

CREATE TABLE wallpapers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id TINYINT UNSIGNED NOT NULL,
    image_url VARCHAR(500) NOT NULL,
    thumb_url VARCHAR(500) NOT NULL,
    description VARCHAR(500),
    width SMALLINT UNSIGNED,
    height SMALLINT UNSIGNED,
    file_size INT UNSIGNED,
    mime_type VARCHAR(50),
    status TINYINT DEFAULT 1,  -- 1正常 0隱藏
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id),
    INDEX idx_cat_time (category_id, created_at),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;