Hey there, this is totally feasible! Your requirement boils down to a date-grouped auto-increment sequence—taking the month and day from a date as a prefix, then starting a counter at 1 that increments for each new entry on that same date. Let’s break down how to implement this in common scenarios:

1. Database Implementation (MySQL Example) #

If you need this sequence directly in a database, you have two solid options depending on your concurrency needs:

Option A: Dynamic Calculation with Variables #

Great for single inserts where you want to compute the ID on the fly:

-- Define your target date
SET @target_date = '2018-03-18';

-- Get the next sequence number for the day (start at 1 if no entries exist)
SET @next_seq = (
    SELECT IFNULL(MAX(CAST(SUBSTRING(custom_id, 5) AS UNSIGNED)), 0) + 1
    FROM your_table
    WHERE DATE(date_column) = @target_date
);

-- Insert the new record with the custom ID
INSERT INTO your_table (date_column, custom_id)
VALUES (@target_date, CONCAT(DATE_FORMAT(@target_date, '%m%d'), @next_seq));

• How it works: We first find the highest existing sequence number for the day, add 1, then concatenate it with the MMDD formatted date.
• Heads up: For high-concurrency environments, wrap this in a transaction to avoid duplicate IDs.

Option B: Dedicated Sequence Table #

Better for batch operations or heavy traffic—this uses a separate table to track daily counters safely:

-- Create a table to hold daily sequence values
CREATE TABLE daily_sequence (
    target_date DATE PRIMARY KEY,
    current_seq INT DEFAULT 1
);

-- Use a transaction to safely get the next sequence
BEGIN TRANSACTION;

-- Try to increment the existing counter for the day
UPDATE daily_sequence
SET current_seq = current_seq + 1
WHERE target_date = '2018-03-18';

-- If no counter exists for the day, create one
IF ROW_COUNT() = 0 THEN
    INSERT INTO daily_sequence (target_date, current_seq)
    VALUES ('2018-03-18', 1);
    SET @next_seq = 1;
ELSE
    SET @next_seq = (SELECT current_seq FROM daily_sequence WHERE target_date = '2018-03-18');
END IF;

-- Insert your record with the custom ID
INSERT INTO your_table (date_column, custom_id)
VALUES ('2018-03-18', CONCAT(DATE_FORMAT('2018-03-18', '%m%d'), @next_seq));

COMMIT;

• Benefit: The transaction ensures that even with multiple concurrent inserts, you won’t get duplicate sequence numbers for the same day.

2. Code Implementation (Python Example) #

If you’re generating these IDs in application code, you can use a distributed counter (like Redis) or a local variable to track daily sequences:

from datetime import datetime
import redis

# Initialize Redis client (adjust host/port as needed)
redis_client = redis.Redis(host='localhost', port=6379, db=0)

def generate_custom_id(target_date: datetime) -> str:
    # Format date to MMDD string
    date_prefix = target_date.strftime("%m%d")
    # Use the date prefix as a Redis key for the daily counter
    counter_key = f"daily_seq:{date_prefix}"
    
    # Atomically increment the counter (starts at 1 if key doesn't exist)
    next_seq = redis_client.incr(counter_key)
    
    # Set the key to expire at the end of the day so we start fresh tomorrow
    if next_seq == 1:
        end_of_day = datetime.combine(target_date.date(), datetime.max.time())
        seconds_until_end = int((end_of_day - datetime.now()).total_seconds())
        redis_client.expire(counter_key, seconds_until_end)
    
    return f"{date_prefix}{next_seq}"

# Test it out!
test_date = datetime(2018, 3, 18)
print(generate_custom_id(test_date))  # Outputs '03181' on first call, '03182' next, etc.

• Single-process alternative: If you don’t need distributed counters, replace Redis with a dictionary to track daily sequences locally (just make sure to handle date resets).

Edge Cases to Consider #

• Cross-day transitions: The sequence will automatically reset to 1 for the next day—e.g., March 19th will start with 03191.
• Multi-digit sequences: If you have more than 9 entries in a day, the sequence will naturally extend to two digits (like 031810) without extra formatting work.
• Concurrency safety: Both the database transaction and Redis atomic increment prevent duplicate IDs in busy environments.

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