如何在PHP、MySQL中存储应用配置数据且无需创建多表？

Great question—this is a super common requirement for almost every application, and there are several tried-and-true patterns to handle this without bloating your database with unnecessary tables. Let’s break down the most practical approaches:

1. Key-Value Store Table (Most Flexible) #

This is the go-to pattern for dynamic settings that might grow over time. You create a single table where each row represents one setting, using a unique key to identify it.

Table Structure Example #

CREATE TABLE app_settings (
    setting_id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(255) NOT NULL UNIQUE,
    setting_value TEXT NOT NULL,
    setting_type ENUM('string', 'integer', 'boolean', 'float') NOT NULL DEFAULT 'string',
    description VARCHAR(500) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

How to Use It #

• Add a setting:

  INSERT INTO app_settings (setting_key, setting_value, setting_type, description)
  VALUES ('site_name', 'My Awesome App', 'string', 'The public name of the application'),
         ('max_upload_size', '1048576', 'integer', 'Maximum file upload size in bytes'),
         ('enable_registration', '1', 'boolean', 'Allow new users to register');
  

• Retrieve a setting:

  SELECT setting_value FROM app_settings WHERE setting_key = 'max_upload_size';
  -- You’ll need to cast the value to the correct type in your app code (e.g., string to int)
  

Pros & Cons #

• ✅ Flexible: Add new settings anytime without altering the table structure
• ✅ Easy to audit: Track changes with the updated_at column (or add a modified_by column for user tracking)
• ❌ Slightly more work in app code: You have to handle type conversion from stored strings to your app’s data types
• ❌ Multiple queries for bulk settings: To get several settings at once, you’ll need a WHERE setting_key IN (...) clause or multiple selects

2. Single Structured Settings Table (Best for Static/Minimal Settings) #

If your list of settings is small and unlikely to change often, you can create a single table where each column represents a specific setting. This is simpler for read operations since you can fetch all settings in one query.

Table Structure Example #

CREATE TABLE app_settings (
    id INT PRIMARY KEY DEFAULT 1, -- Only one row exists for global settings
    site_name VARCHAR(255) NOT NULL DEFAULT 'My App',
    max_upload_size INT NOT NULL DEFAULT 1048576,
    enable_registration TINYINT(1) NOT NULL DEFAULT 1,
    support_email VARCHAR(255) NOT NULL DEFAULT 'support@myapp.com',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- Insert the initial row (only one row should ever exist here)
INSERT INTO app_settings (id) VALUES (1);

How to Use It #

• Update a setting:

  UPDATE app_settings SET enable_registration = 0 WHERE id = 1;
  

• Retrieve all settings:

  SELECT * FROM app_settings WHERE id = 1;
  

Pros & Cons #

• ✅ Native data types: No need to cast values in your app—use MySQL’s built-in types directly
• ✅ Single query for all settings: Fast and simple to fetch everything at once
• ❌ Inflexible: Adding a new setting requires altering the table (e.g., ALTER TABLE app_settings ADD COLUMN new_setting VARCHAR(255);)
• ❌ Not ideal for dynamic settings: If you expect to add many settings over time, this will get messy

3. Single Table with JSON Column (Middle Ground) #

If you want flexibility without the overhead of a key-value store, use MySQL’s JSON data type (available in 5.7+). Store all your settings in a single JSON column within a one-row table.

Table Structure Example #

CREATE TABLE app_settings (
    id INT PRIMARY KEY DEFAULT 1,
    settings_data JSON NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- Insert initial settings
INSERT INTO app_settings (id, settings_data)
VALUES (1, '{
    "site_name": "My App",
    "max_upload_size": 1048576,
    "enable_registration": true,
    "contact_info": {
        "email": "support@myapp.com",
        "phone": "555-1234"
    }
}');

How to Use It #

• Update a setting:

  UPDATE app_settings 
  SET settings_data = JSON_SET(settings_data, '$.enable_registration', false)
  WHERE id = 1;
  

• Retrieve a specific setting:

  SELECT JSON_EXTRACT(settings_data, '$.site_name') AS site_name FROM app_settings WHERE id = 1;
  

• Retrieve all settings:

  SELECT settings_data FROM app_settings WHERE id = 1;
  -- Parse the JSON in your app code to access individual values
  

Pros & Cons #

• ✅ Flexible: Add/modify settings without altering the table, even support nested structures
• ✅ Cleaner than key-value for grouped settings: Store related settings as nested objects (e.g., contact info)
• ❌ Limited query capabilities: Filtering or sorting based on JSON values requires MySQL’s JSON functions, which are less performant than native columns
• ❌ No built-in type enforcement: You’ll need to validate data types in your app code

Best Practices to Enhance Any Approach #

• Cache settings: Since app settings rarely change, cache them in your app’s memory (e.g., a singleton object) or a caching layer like Redis. This reduces database queries and speeds up your app.
• Add audit trails: Create a settings_history table to track every change, including the user who made it, timestamp, old value, and new value. This is critical for debugging and compliance.
• Validate inputs: Always validate setting values in your app before saving (e.g., ensure max_upload_size is a positive integer, support_email is a valid email address).
• Restrict access: Make sure only authorized admin users can modify these settings—add role-based access control (RBAC) to your update endpoints.

内容的提问来源于stack exchange，提问作者Murlidhar Fichadia