How MySQL’s RAND() Function Generates Random Numbers

2026-11-03,

I’m reposting something I wrote on my old blog. (Original post)

The RAND() function in MySQL returns a floating-point value greater than or equal to 0 and less than 1.0 when no seed is specified. Under no circumstances does it generate a random value greater than or equal to 1.0.

Internally, MySQL uses a maximum value of 1073741823 when generating random numbers. The seed value is subjected to a modulo operation using this maximum value. If the seed exceeds 32 bits, an additional modulo operation with 2^32 is performed. Therefore, regardless of how large the seed value is, the RAND() function is ultimately constrained by the maximum value used in the random number generation process.

Let’s take a closer look at how random numbers are calculated, distinguishing between cases where a seed is provided and where no seed is specified.

Although the following explanation focuses primarily on the code involved in the random number generation process, it should not be difficult to understand if you read through it carefully.

When a Seed Value Is Provided

The seed value passed as an argument to the RAND() function is calculated and managed using a random number structure(rand structure).

SELECT RAND(123.11)

When a seed value of 123.11 is passed as shown above, the random value generated by the RAND() function is calculated through the following steps.

1) item_func.cc:Item_func_rand::seed_random

void Item_func_rand::seed_random(Item *arg) {
  /*
    TODO: do not do reinit 'rand' for every execute of PS/SP if
    args[0] is a constant.
  */
  const uint32 tmp = (uint32)arg->val_int();
  randominit(m_rand, (uint32)(tmp * 0x10001L + 55555555L),
             (uint32)(tmp * 0x10000001L));
}

Line 6

tmp: 123(Converted to an unsigned positive integer and assigned to the tmp variable.)

Line 7

m_rand: A pointer to the rand_struct type(Memory is allocated in Item_func.cc:Item_func_rand::fix_fields. Refer to item_func.h for the structure definition.)
(uint32)(tmp * 0x10001L + 55555555L): 63616606 → First seed value(Multiplying the seed value 123 by 0x10001 and adding 55555555 appears to be part of the random number generation algorithm.)
(uint32)(tmp * 0x10000001L): 2952790139 → Second seed value
randominit: Random number generator initialization

-> The seed value 123.11 is converted to a 32-bit unsigned integer. Two seed values are then calculated using different formulas and passed to the random number generator initialization function. If the provided seed value exceeds the maximum value representable by a 64-bit integer, it is capped at the maximum 64-bit value (silent overflow).

2) password.cc:randominit

void randominit(struct rand_struct *rand_st, ulong seed1,
                ulong seed2) { /* For mysql 3.21.# */
  rand_st->max_value = 0x3FFFFFFFL;
  rand_st->max_value_dbl = (double)rand_st->max_value;
  rand_st->seed1 = seed1 % rand_st->max_value;
  rand_st->seed2 = seed2 % rand_st->max_value;
}

Line 1

rand_st: Initializes the random number structure pointed to by m_rand through rand_st.

Line 3

rand_st->max_value: 1073741823 (2^30 – 1)(Sets the maximum value limit for the random number structure.)

Line 4

rand_st->max_value_dbl: 1073741823(Converts the maximum random number value to the double type and stores it.)

Lines 5 and 6

rand_st->seed1: 63616606 % 1073741823 = 63616606
rand_st->seed2: 2952790139 % 1073741823 = 805306493

-> The seed values and the maximum value used for random number generation are initialized in the random number structure. These initialized values are subsequently used during the actual random number generation process.

3) item_func.cc:Item_func_rand::var_real

double Item_func_rand::val_real() {
  assert(fixed);
  rand_struct *rand;
  if (arg_count > 0) {
    if (!args[0]->const_for_execution())
      seed_random(args[0]);
    else if (first_eval) {
      /*
        Constantness of args[0] may be set during JOIN::optimize(), if arg[0]
        is a field item of "constant" table. Thus, we have to evaluate
        seed_random() for constant arg there but not at the fix_fields method.
      */
       first_eval = false;
       seed_random(args[0]);
     }
     rand = m_rand;
   } else {
    /*
       Save the seed only the first time RAND() is used in the query
       Once events are forwarded rather than recreated,
       the following can be skipped if inside the slave thread
    */
     THD *const thd = current_thd;
     if (!thd->rand_used) {
      thd->rand_used = true;
       thd->rand_saved_seed1 = thd->rand.seed1;
       thd->rand_saved_seed2 = thd->rand.seed2;
     }
     rand = &thd->rand;
   }
   return my_rnd(rand);
 }

Line 16

rand: Assigns the random number structure pointed to by m_rand to the rand structure pointer declared on Line 3.

Line 31

my_rnd(rand): Passes the rand pointer as an argument to the my_rnd function.

-> Calls the my_rnd function to generate a random number.

4) my_rnd.cc:my_rnd

double my_rnd(struct rand_struct *rand_st) {
  rand_st->seed1 = (rand_st->seed1 * 3 + rand_st->seed2) % rand_st->max_value;
  rand_st->seed2 = (rand_st->seed1 + rand_st->seed2 + 33) % rand_st->max_value;
  return (((double)rand_st->seed1) / rand_st->max_value_dbl);
}

Line 2, 3

rand_st->seed1: (63616606 * 3 + 805306493) % 1073741823 = 996156311
rand_st->seed2: (996156311 + 805306493 + 33) % 1073741823 = 727721014

Line 4

return: 996156311 / 1073741823 = 0.92774286114400517 ≒ 0.9277428611440052

mysql> SELECT RAND(123.11);
+--------------------+
| RAND(123)          |
+--------------------+
| 0.9277428611440052 |
+--------------------+

→ The first and second seed values are used to generate a single final random number. Since the result of a modulo operation cannot be greater than or equal to the divisor, the seed1 and seed2 values used in random number generation must always be smaller than the maximum random number value. Furthermore, because the seed value is divided by the maximum random number value (i.e., a smaller value is divided by a larger value), the result can never be greater than or equal to 1.

Therefore, the generated random number always falls within the range 0 ≤ a < 1.

When No Seed Value Is Provided

The process is similar when no seed value is provided, but there are some minor differences.

SELECT RAND();

< On the First Execution >

1) sql_class.cc:randominit

auto_inc_intervals_forced.clear();
  {
    ulong tmp;
    tmp = sql_rnd_with_mutex();
    randominit(&rand,
               tmp + static_cast<ulong>(reinterpret_cast<uintptr_t>(&rand)),
               tmp + (ulong)::atomic_global_query_id);
  }

Line 1

auto_inc_intervals_forced.clear(): When a query is executed, the THD object is initialized through THD::init. During this process, the rand structure member variable within the object is initialized.

Line 4

tmp: 47641308128(A random number is generated by the sql_rnd_with_mutex function in mysqld.cc and assigned to the tmp variable.)

ulong sql_rnd_with_mutex() {
  mysql_mutex_lock(&LOCK_sql_rand);
  ulong tmp =
      (ulong)(my_rnd(&sql_rand) * 0xffffffff); /* make all bits random */
  mysql_mutex_unlock(&LOCK_sql_rand);
  return tmp;
}

Lines 5, 6, and 7

tmp + static_cast<ulong>(reinterpret_cast<uintptr_t>(&rand)): 663490581(Converts the memory address of the rand structure pointer to an integer, adds it to the tmp variable, and generates the first seed value used for random number generation.)
tmp + (ulong)::atomic_global_query_id: 266834210(Adds the globally unique query ID to the tmp variable to generate the second seed value used for random number generation.)

(lldb) p rand
(rand_struct) $346 = (seed1 = 663490581, seed2 = 266834210, max_value = 1073741823, max_value_dbl = 1073741823)

2) mysql_rnd.cc:my_rnd

double my_rnd(struct rand_struct *rand_st) {
  rand_st->seed1 = (rand_st->seed1 * 3 + rand_st->seed2) % rand_st->max_value;
  rand_st->seed2 = (rand_st->seed1 + rand_st->seed2 + 33) % rand_st->max_value;
  return (((double)rand_st->seed1) / rand_st->max_value_dbl);
}

Line 1, 2

rand_st->seed1: (663490581 * 3 + 266834210) % 1073741823 = 109822307
rand_st->seed2: (109822307 + 266834210 + 33) % 1073741823 = 376656550

Line 3

return: (109822307 / 1073741823) = 0.10227999380070715

mysql> SELECT RAND();
+---------------------+
| rand()              |
+---------------------+
| 0.10227999380070715 |
+---------------------+

(lldb) p thd->rand
(rand_struct) $368 = (seed1 = 109822307, seed2 = 376656550, max_value = 1073741823, max_value_dbl = 1073741823)

→ When no seed value is provided, the randominit function in sql_class.cc is called only once during initialization. The two seed values used for random number generation are stored in the thread’s rand member variable and used as the initial seed values for the next call to the RAND() function. The seed values are updated and stored each time the RAND() function is called, as long as the server connection remains active.

< On the Second Execution >

SELECT RAND();

1) item_func.cc:Item_func_rand::fix_fields

bool Item_func_rand::fix_fields(THD *thd, Item **ref) {
  if (Item_real_func::fix_fields(thd, ref)) return true;
  if (arg_count > 0) {  // Only use argument once in query
    /*
      Allocate rand structure once: we must use thd->stmt_arena
      to create rand in proper mem_root if it's a prepared statement or
      stored procedure.
      No need to send a Rand log event if seed was given eg: RAND(seed),
      as it will be replicated in the query as such.
     */
      assert(m_rand == nullptr);
      m_rand = pointer_cast<rand_struct *>(thd->alloc(sizeof(*m_rand)));
      if (m_rand == nullptr) return true;
    } else {
      /*
        Save the seed only the first time RAND() is used in the query
        Once events are forwarded rather than recreated,
        the following can be skipped if inside the slave thread
      */
      if (!thd->rand_used) {
        thd->rand_used = true;
        thd->rand_saved_seed1 = thd->rand.seed1;
        thd->rand_saved_seed2 = thd->rand.seed2;
      }
    }
    return false;
  }

Lines 22 and 23

By dereferencing the object pointed to by the thd pointer, we can confirm that the seed1 and seed2 values are stored in the rand member variable.

(lldb) frame variable thd->rand
(rand_struct) thd->rand = (seed1 = 109822307, seed2 = 376656550, max_value = 1073741823, max_value_dbl = 1073741823)

thd->rand_saved_seed1: 109822307
thd->rand_saved_seed2: 376656550

→ The thread’s seed value stored in the previous step is assigned to the thread’s saved seed variable (rand_saved_seed1).

2) item_func.cc:Item_func_rand::val_real

double Item_func_rand::val_real() {
 assert(fixed);
 rand_struct *rand;
 if (arg_count > 0) {
   if (!args[0]->const_for_execution())
     seed_random(args[0]);
   else if (first_eval) {
     /*
       Constantness of args[0] may be set during JOIN::optimize(), if arg[0]
        is a field item of "constant" table. Thus, we have to evaluate
        seed_random() for constant arg there but not at the fix_fields method.
      */
      first_eval = false;
     seed_random(args[0]);
    }
    rand = m_rand;
  } else {
    /*
      Save the seed only the first time RAND() is used in the query
      Once events are forwarded rather than recreated,
      the following can be skipped if inside the slave thread
    */
    THD *const thd = current_thd;
    if (!thd->rand_used) {
      thd->rand_used = true;
      thd->rand_saved_seed1 = thd->rand.seed1;
      thd->rand_saved_seed2 = thd->rand.seed2;
    }
    rand = &thd->rand;
  }
  return my_rnd(rand);
 }

Line 24

thd->rand_used: Since this variable was set to true in Item_func_rand::fix_fields, the if statement is skipped.

Line 29

rand: Assigns the memory address of the rand member variable pointed to by thd to the rand structure pointer.(The rand->seed1 and rand->seed2 values are updated.)

Line 31

my_rnd(rand): rand->seed1: 109822307, rand->seed2: 376656550(Passes the rand pointer as an argument to the my_rnd function.)

-> The memory address is assigned so that the rand structure pointer of type rand_struct points to thd->rand, and the pointer is then passed as an argument to the my_rnd function.

3) my_rnd.cc:my_rnd

double my_rnd(struct rand_struct *rand_st) {
  rand_st->seed1 = (rand_st->seed1 * 3 + rand_st->seed2) % rand_st->max_value;
  rand_st->seed2 = (rand_st->seed1 + rand_st->seed2 + 33) % rand_st->max_value;
  return (((double)rand_st->seed1) / rand_st->max_value_dbl);
}

Line 2, 3

rand_st->seed1: (109822307 * 3 + 376656550) % 1073741823 = 706123471
rand_st->seed2: (706123471 + 376656550 + 33) % 1073741823 = 9038231

Line 4

return: 706123471 / 1073741823 = 0.6576287296206027

mysql> SELECT RAND();
+--------------------+
| rand()             |
+--------------------+
| 0.6576287296206027 |
+--------------------+

→ When no seed value is provided, random numbers are generated through two distinct stages, as described above: the initial execution and subsequent executions.

Conclusion

Regardless of whether a seed value is provided to the RAND() function, the two internally calculated seed values are subjected to modulo operations using the maximum random number value. The resulting values are then divided by the same maximum value. Consequently, the generated random number can never be greater than or equal to 1.

When a seed value is provided, the random number generation process involves no unpredictable factors other than the specified seed value. Therefore, the same seed value always produces the same random number.

When no seed value is provided, the initial execution generates a random number using thread-specific seed values. On subsequent executions, random numbers are generated using the seed values from the previous operation (the values used in the preceding RAND() calculation). As a result, the seed values are not fixed, and different random numbers are generated on successive calls.