MySQL用户变量与CONCAT中ORDER BY RAND()的使用故障求助

Let's walk through the problems in your current query and fix them so it works as intended:

What's Wrong with the Original Query? #

Your main issues are related to invalid subquery syntax and incorrect variable assignment placement inside the CONCAT() function:

• You can't directly embed a SELECT ... FROM clause inside CONCAT() like you did with @rYear := WarYear FROM new_schema.WW1-years ORDER BY RAND() LIMIT 1. This isn't valid SQL syntax—subqueries need to be wrapped in parentheses and properly structured.
• Mixing variable assignment with string concatenation in this way can lead to execution order issues in MySQL, where the variable might not be assigned before it's used (even if the syntax was correct).

Corrected Query Options #

Option 1: Assign the Random Year First (Explicit Variable Setup)

If you want to keep using the @rYear variable, split the logic into two steps to ensure the variable is properly populated before you use it:

-- Step 1: Assign a random WW1 year to @rYear
SET @rYear = (SELECT WarYear FROM new_schema.`WW1-years` ORDER BY RAND() LIMIT 1);
-- Step 2: Use the variable to fetch casualties for the matching date
SELECT Deaths 
FROM new_schema.totals 
WHERE Date = CONCAT(@rYear, '-', MONTH(CURDATE()), '-', DAYOFMONTH(CURDATE()));

This formalizes the separate-step approach you mentioned worked, ensuring the variable is ready before the main query runs.

Option 2: Embed the Random Year Subquery Directly (No Variables Needed)

If you don't need to reuse the @rYear variable elsewhere, you can simplify the query by embedding the random year subquery directly into the CONCAT() function:

SELECT Deaths 
FROM new_schema.totals 
WHERE Date = CONCAT(
    (SELECT WarYear FROM new_schema.`WW1-years` ORDER BY RAND() LIMIT 1),
    '-',
    MONTH(CURDATE()),
    '-',
    DAYOFMONTH(CURDATE())
);

The subquery is wrapped in parentheses here, which tells MySQL to execute it first and return a single value to use in the concatenation.

Bonus: Handle Edge Cases #

Keep in mind that not all WW1 years will have entries for the current month/day (e.g., if today is February 29, but the random year isn't a leap year). You might want to add a check or handle null results, but that's beyond the core syntax fix.

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