如何根据日期在MySQL中写入或更新对应列的值？求技术协助

Hey there! Let's break down how to implement this MySQL data operation logic, and also share a better approach for long-term maintainability.

Solution for Your Original Table Design #

First, let's assume your table is structured to match your requirement (with paired columns for each date):

CREATE TABLE daily_inputs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ONE TEXT,
    DAY1 DATE,
    TWO TEXT,
    DAY2 DATE,
    THREE TEXT,
    DAY3 DATE
    -- Add more columns like FOUR, DAY4 if you need to support additional dates
);

To automate the logic of inserting/updating the right columns based on the date, we can create a stored procedure that dynamically determines which columns to target:

DELIMITER //

CREATE PROCEDURE save_or_update_daily_input(IN input_content TEXT, IN target_date DATE)
BEGIN
    DECLARE column_suffix INT;
    DECLARE content_column VARCHAR(20);
    DECLARE date_column VARCHAR(20);
    DECLARE record_exists INT;
    
    -- Calculate the offset from your base date ('2018-05-08') to get the column number
    SET column_suffix = DATEDIFF(target_date, '2018-05-08') + 1;
    
    -- Map the offset to column names (ONE/DAY1, TWO/DAY2, etc.)
    SET content_column = ELT(column_suffix, 'ONE', 'TWO', 'THREE', 'FOUR', 'FIVE');
    SET date_column = CONCAT('DAY', column_suffix);
    
    -- Check if we already have a base record (we'll use id=1 as the single row for all dates)
    SELECT COUNT(*) INTO record_exists FROM daily_inputs WHERE id = 1;
    
    IF record_exists = 0 THEN
        -- Insert a new record with the target columns populated
        SET @insert_query = CONCAT(
            'INSERT INTO daily_inputs (id, ', content_column, ', ', date_column, ') VALUES (1, ?, ?)'
        );
        PREPARE stmt FROM @insert_query;
        SET @content = input_content;
        SET @date = target_date;
        EXECUTE stmt USING @content, @date;
    ELSE
        -- Update the existing record's target columns
        SET @update_query = CONCAT(
            'UPDATE daily_inputs SET ', content_column, ' = ?, ', date_column, ' = ? WHERE id = 1'
        );
        PREPARE stmt FROM @update_query;
        SET @content = input_content;
        SET @date = target_date;
        EXECUTE stmt USING @content, @date;
    END IF;
    
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

How to Use the Procedure #

Call it with your input content and target date, and it'll handle insert/update automatically:

-- For '2018-05-08' (first date)
CALL save_or_update_daily_input('My first day content', '2018-05-08');

-- Call again with the same date to update the ONE column
CALL save_or_update_daily_input('Updated first day content', '2018-05-08');

-- For '2018-05-09' (second date)
CALL save_or_update_daily_input('Second day content', '2018-05-09');

A Better, Scalable Table Design #

While the above works, your original table structure has a big drawback: you'll need to add new columns every time you want to support a new date, which is not scalable and violates database normalization best practices.

Instead, let's restructure the table to store each date's content as a row (not a column):

CREATE TABLE daily_inputs_optimized (
    id INT AUTO_INCREMENT PRIMARY KEY,
    input_date DATE UNIQUE NOT NULL,
    input_content TEXT NOT NULL
);

Insert/Update with a Single Query #

Now, you can use MySQL's INSERT ... ON DUPLICATE KEY UPDATE to handle both insert and update in one line—no stored procedure needed:

INSERT INTO daily_inputs_optimized (input_date, input_content)
VALUES ('2018-05-08', 'My first day content')
ON DUPLICATE KEY UPDATE input_content = VALUES(input_content);

Why This Is Better #

• Scalability: No need to modify the table structure for new dates—just insert a new row.
• Easier Queries: Fetching content for any date is straightforward:

  SELECT input_content FROM daily_inputs_optimized WHERE input_date = '2018-05-08';
  

• Normalized Structure: Avoids redundant columns and follows database design best practices.

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