MySQL でのランダムデータの生成

-- Shuffle query results
SELECT * FROM products ORDER BY RAND() LIMIT 10;

-- Random integer in a range
SELECT FLOOR(RAND() * 100) AS random_0_to_99;

-- 16-byte cryptographic token
SELECT HEX(RANDOM_BYTES(16)) AS token;

-- Use as a session token
INSERT INTO sessions (token, user_id, expires_at)
VALUES (RANDOM_BYTES(32), 42, NOW() + INTERVAL 7 DAY);

INSTALL EXTENSION vsql_crypto;

-- Cryptographically secure random bytes
SELECT HEX(gen_random_bytes(16)) AS token_hex;
SELECT HEX(gen_random_bytes(32)) AS session_key;

-- Verify length
SELECT LENGTH(gen_random_bytes(24));  -- Returns 24

-- Random UUID v4
SELECT gen_random_uuid();
-- Returns: 'de2ae351-fc1b-4182-b6a5-72d11a341dc3' (different every call)

-- Insert 1000 rows of test data. WITH RECURSIVE must immediately precede
-- the SELECT it attaches to, so it goes after the INSERT's column list.
INSERT INTO test_users (username, email, score, token)
WITH RECURSIVE n AS (
    SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 1000
)
SELECT
    CONCAT('user_', i),
    CONCAT('user', i, '@example.com'),
    FLOOR(RAND() * 100),
    gen_random_bytes(16)
FROM n;

CREATE TABLE invites (
    token     VARBINARY(32) PRIMARY KEY,
    email     VARCHAR(255),
    used      TINYINT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO invites (token, email)
VALUES (gen_random_bytes(32), 'newuser@example.com');

-- Return token as hex for use in a URL
SELECT HEX(token) AS invite_link, email FROM invites WHERE used = 0;