如何在MySQL中生成0和1序列？求auto_increment外的其他方法

Question #

I've learned that I can use rand() and floor() functions to generate a 0/1 sequence in MySQL, with this example query:

SELECT floor(rand(0)*2) FROM security.users;

When executed, it returns this 16-row result:

+------------------+
| floor(rand(0)*2) |
+------------------+
| 0                |
| 1                |
| 1                |
| 0                |
| 1                |
| 1                |
| 0                |
| 0                |
| 1                |
| 1                |
| 1                |
| 0                |
| 1                |
| 1                |
| 1                |
| 0                |
+------------------+
16 rows in set (0.00 sec)

I'm wondering: are there other methods to achieve this same result, besides using AUTO_INCREMENT?

Answer #

Great question! Your current approach using floor(rand(0)*2) works perfectly for generating a fixed random 0/1 sequence (since you're using a seed value of 0). Depending on whether you need a random, unpredictable sequence or a deterministic, alternating one, here are several solid alternatives:

1. Random 0/1 sequence (no fixed seed) #

If you don't need the sequence to be repeatable (i.e., different results each run), you can omit the seed from RAND(), or use ROUND() instead of FLOOR() for a more readable equivalent:

-- Random 0/1 sequence (no fixed seed)
SELECT floor(rand()*2) AS seq FROM security.users;

-- Clean equivalent using ROUND()
SELECT round(rand()) AS seq FROM security.users;

Both will produce a roughly even mix of 0s and 1s across a large dataset.

2. Deterministic alternating 0/1 sequence (user variable) #

For a strict 0→1→0→1 pattern, use a user-defined variable to track row numbers and apply modulo 2:

SELECT MOD(@row := @row + 1, 2) AS seq
FROM security.users, (SELECT @row := 0) AS init;

This generates a predictable alternating sequence starting with 1. To start with 0 instead, adjust the initial value to @row := -1.

3. Deterministic alternating 0/1 sequence (window function, MySQL 8.0+) #

If you're using MySQL 8.0 or later, the ROW_NUMBER() window function offers a cleaner way to create an alternating sequence without user variables:

-- Alternating sequence starting with 1
SELECT MOD(ROW_NUMBER() OVER (ORDER BY id), 2) AS seq
FROM security.users;

-- Alternating sequence starting with 0
SELECT MOD(ROW_NUMBER() OVER (ORDER BY id) - 1, 2) AS seq
FROM security.users;

Replace id with any column you want to order by (like created_at) to ensure consistency across runs.

4. Explicit random 0/1 sequence with CASE statement #

For a more readable take on random 0/1 generation, use a CASE statement to check a random threshold:

SELECT 
    CASE WHEN RAND() > 0.5 THEN 1 ELSE 0 END AS seq
FROM security.users;

This behaves exactly like your original random approach but makes the "50/50 split" logic explicit.

Pick the method that fits your use case: random variants for unpredictability, or deterministic ones for consistent, ordered sequences.

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