> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Generating Random Data in MySQL

> How to generate random data in MySQL — cryptographically secure bytes, random UUIDs, and test data — using RAND(), RANDOM_BYTES(), and VillageSQL's gen_random_bytes().

> [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md)
>
> All examples on this page work on VillageSQL. Install Now →

MySQL's `RAND()` generates pseudo-random numbers for things like shuffling query results. It isn't cryptographically secure, which matters when you're generating tokens, salts, or nonces. `RANDOM_BYTES()` fills that gap, and VillageSQL's `vsql_crypto` extension adds `gen_random_bytes()` and `gen_random_uuid()` as part of a unified crypto API.

## MySQL's Random Functions

MySQL provides two built-in random functions with very different purposes:

**`RAND()`** — Returns a pseudo-random float between 0 and 1. Fast and suitable for non-security applications: randomized query results, weighted sampling, random row selection. Not cryptographically secure.

```sql theme={null}
-- 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;
```

**`RANDOM_BYTES(n)`** — Returns `n` cryptographically secure random bytes as `VARBINARY`. Use this for tokens, salts, nonces, and keys.

```sql theme={null}
-- 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);
```

## With VillageSQL: gen\_random\_bytes() and gen\_random\_uuid()

VillageSQL's `vsql_crypto` extension adds `gen_random_bytes()` and `gen_random_uuid()`, which use OpenSSL's `RAND_bytes()` — the same entropy source as `RANDOM_BYTES()`.

```sql theme={null}
INSTALL EXTENSION vsql_crypto;
```

The main reason to use these over the built-ins is consistency: when you're already using `digest()`, `hmac()`, or `encrypt()` from `vsql_crypto`, keeping all crypto operations in one extension simplifies upgrades and auditing.

```sql theme={null}
-- 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)
```

### Generating test data

Random functions are useful for seeding test tables:

```sql theme={null}
-- 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;
```

### One-time tokens and invite codes

```sql theme={null}
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;
```

## Choosing the Right Function

| Function | Output | Cryptographically secure | Use when |
| :- | :- | :- | :- |
| `RAND()` | Float 0–1 | No | Shuffling, sampling, weighted selection |
| `FLOOR(RAND() * n)` | Integer 0–(n-1) | No | Random integers for test data |
| `RANDOM_BYTES(n)` | VARBINARY | Yes | Tokens, nonces, salts — MySQL built-in |
| `gen_random_bytes(n)` | VARBINARY | Yes | Same as RANDOM\_BYTES, unified with vsql\_crypto API |
| `gen_random_uuid()` | VARCHAR (UUID string) | Yes | UUID v4 as a string — no vsql\_uuid required |

`RANDOM_BYTES()` and `gen_random_bytes()` are functionally equivalent — both return the same quality of randomness. If you're already using `vsql_crypto`, use `gen_random_bytes()` to keep your crypto surface in one place. If you only need random bytes and nothing else, MySQL's `RANDOM_BYTES()` works fine.

If you need a UUID type with sortable variants (v7) and binary storage, use the `vsql_uuid` extension instead of `gen_random_uuid()`. See [UUIDs in MySQL](https://villagesql.com/docs/en/guides/uuids.md) for the full comparison.

## Frequently Asked Questions

#### Is RAND() safe for generating tokens?

No. `RAND()` is a pseudo-random number generator seeded from a predictable state. It's fine for `ORDER BY RAND()` and similar non-security uses, but tokens generated with `RAND()` can in theory be predicted. Use `RANDOM_BYTES()` or `gen_random_bytes()` for anything security-sensitive.

#### How many bytes do I need for a secure token?

32 bytes (256 bits) is standard for session tokens and API keys. 16 bytes (128 bits) is the minimum for practical security. Use 32 to be safe.

#### Can I use gen\_random\_bytes() as a DEFAULT?

No. MySQL itself permits non-deterministic functions in DEFAULT expressions — `UUID()`, `RAND()`, and `NOW()` all work there. `gen_random_bytes()` is rejected specifically because it's a VillageSQL extension function registered as non-deterministic, and VEF blocks those in DEFAULT expressions regardless of what MySQL itself allows. Generate the value in each INSERT statement instead.

#### What's the difference between gen\_random\_uuid() and UUID\_V4() from vsql\_uuid?

`gen_random_uuid()` returns a `VARCHAR` UUID string. `UUID_V4()` from the `vsql_uuid` extension returns a native `UUID` type with 16-byte binary storage. If you're using UUIDs as primary keys, prefer `UUID_V4()` or `UUID_V7()` from `vsql_uuid`. Use `gen_random_uuid()` when you need a UUID string and don't want to install a separate extension.

## Troubleshooting

| Problem | Solution |
| :- | :- |
| `FUNCTION <database>.gen_random_bytes does not exist` | Run `INSTALL EXTENSION vsql_crypto` |
| Tokens appear sequential or predictable | You may be using `RAND()` instead of `gen_random_bytes()` |
| `gen_random_bytes()` returns NULL | Check that the argument is a positive integer |
| `DEFAULT (gen_random_bytes(32))` rejected | MySQL doesn't allow non-deterministic defaults — generate in each INSERT |

## See also

* [Password Hashing in MySQL](https://villagesql.com/docs/en/guides/password-hashing.md) — using random bytes as a salt for credential storage
* [UUIDs in MySQL](https://villagesql.com/docs/en/guides/uuids.md) — UUID v4 is a common use of cryptographic randomness


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.