MySQL随机选条目、用户筛选更新插入及重复用户排除技术问询

Hey there! Let's work through each of your MySQL challenges with practical, actionable SQL solutions.

1. Randomly Select a Record Matching Specific Criteria #

First, let's nail down how to pick a random eligible user. The key is to first filter users who meet all your rules (no non-zero note history, exclude users 12 and 88, and handle duplicates), then pick one at random.

Step 1: Identify Eligible Users #

To get a list of unique users who qualify:

SELECT DISTINCT user_id
FROM your_table
WHERE user_id NOT IN (12, 88) -- Exclude the two specified users
AND NOT EXISTS (
    -- Ensure the user has NEVER had a non-zero note
    SELECT 1
    FROM your_table t2
    WHERE t2.user_id = your_table.user_id
    AND t2.note != 0
);

• DISTINCT cleans up duplicate user entries so we only consider each user once.
• NOT EXISTS is safer than NOT IN here (it avoids unexpected behavior if any user_id values are NULL).

Step 2: Randomly Pick One Eligible User #

Wrap the above query to select a single random user:

SELECT user_id
FROM (
    SELECT DISTINCT user_id
    FROM your_table
    WHERE user_id NOT IN (12, 88)
    AND NOT EXISTS (
        SELECT 1
        FROM your_table t2
        WHERE t2.user_id = your_table.user_id
        AND t2.note != 0
    )
) AS eligible_users
ORDER BY RAND()
LIMIT 1;

• ORDER BY RAND() works great for small to medium tables. For very large datasets, you can use a more optimized method (like generating a random ID range), but this is simple and effective for most cases.

2. Update the Selected User's Record & Insert a New Row #

Once you've picked a user, you need to perform the update and insert atomically (so neither operation fails without the other). Use a transaction to ensure data consistency:

START TRANSACTION;

-- Lock the selected user to prevent concurrent picks (critical for multi-user systems)
SET @selected_user = (
    SELECT user_id
    FROM (
        SELECT DISTINCT user_id
        FROM your_table
        WHERE user_id NOT IN (12, 88)
        AND NOT EXISTS (
            SELECT 1
            FROM your_table t2
            WHERE t2.user_id = your_table.user_id
            AND t2.note != 0
        )
    ) AS eligible_users
    ORDER BY RAND()
    LIMIT 1
    FOR UPDATE SKIP LOCKED -- Skip locked rows to avoid deadlocks in high concurrency
);

-- Update the user's existing record (adjust the SET clause to match your needs)
UPDATE your_table
SET note = 1 -- Example: set note to non-zero to mark them as ineligible for future picks
WHERE user_id = @selected_user;

-- Insert a new row for the user (fill in your actual column names and values)
INSERT INTO your_table (user_id, note, other_column)
VALUES (@selected_user, 0, 'your_custom_value');

COMMIT;

• The transaction (START TRANSACTION/COMMIT) ensures both the update and insert succeed or fail together.
• FOR UPDATE SKIP LOCKED prevents multiple processes from picking the same user at the same time.

3. Handling Duplicate Users & Excluding Specific Users #

The DISTINCT keyword in our initial query takes care of duplicate user entries—it ensures we only evaluate each unique user_id once. The user_id NOT IN (12, 88) clause directly excludes the two problematic users, and the NOT EXISTS subquery ensures we only keep users who have never had a non-zero note.

4. Why Only User 45 Is Left After Selecting User 23 #

This is actually expected behavior! When you update user 23's record to have a non-zero note, they're now excluded by the NOT EXISTS check (since they now have a history of non-zero note). If only user 45 remains in the eligible list, that means all other users either:

• Are user 12 or 88,
• Already have a non-zero note in their history,
• Or were marked ineligible by a previous update.

If this isn't what you intended, you'll need to adjust your eligibility rules. For example, if you only want to exclude users with a current non-zero note (not historical), modify the NOT EXISTS subquery to check for active records only (add a status filter if you have one).

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