Oracle DBMS_RANDOM and MySQL RAND Function - Oracle to Aurora MySQL Migration Playbook
Oracle UsageMySQL Usage
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Oracle DBMS_RANDOM and MySQL RAND Function

Feature compatibility Amazon SCT / Amazon DMS automation level Amazon SCT action code index Key differences

Three star feature compatibility

No automation

N/A

Different syntax and missing options may require code rewrite.

Oracle Usage

Oracle DBMS_RANDOM package provides functionality for generating random numbers or strings as part of an SQL statement or PL/SQL procedure.

The DBMS_RANDOM Package stored procedures include:

  • NORMAL — Returns random numbers in a standard normal distribution.

  • SEED — Resets the seed that generates random numbers or strings.

  • STRING — Returns a random string.

  • VALUE — Returns a number greater than or equal to 0 and less than 1 with 38 digits to the right of the decimal. Alternatively, you could generate a random number greater than or equal to a low parameter and less than a high parameter.

DBMS_RANDOM.RANDOM produces integers in the range [-2^^31, 2^^31].

DBMS_RANDOM.VALUE produces numbers in the range [0,1] with 38 digits of precision.

Examples

Generate a random number.

select dbms_random.value() from dual;

DBMS_RANDOM.VALUE()
.859251508

select dbms_random.value() from dual;

DBMS_RANDOM.VALUE()
.364792387

Generate a random string. The first character determines the returned string type and the number specifies the length.

select dbms_random.string('p',10) from dual;
DBMS_RANDOM.STRING('P',10)

la'?z[Q&/2

select dbms_random.string('p',10) from dual;
DBMS_RANDOM.STRING('P',10)

t?!Gf2M60q

For more information, see DBMS_RANDOM in the Oracle documentation.

MySQL Usage

The MySQL RAND function is not fully equivalent to Oracle DBMS_RANDOM because it does not generate string values. However, there are other functions in that can be used in combination to achieve full functionality.

RAND function returns a random floating-point value v in the range 0 ⇐ v < 1.0.

You can use the RAND function with a seed value to reset the seed. If an integer argument N is specified, it is used as the seed value:

  • With a constant initializer argument, the seed is initialized once when the statement is prepared and prior to execution.

  • With a non-constant initializer argument such as a column name, the seed is initialized with the value for each invocation of RAND().

Examples

Generate a random number.

select RAND();

RAND()
0.30244802525494996

To obtain a random integer R in the range i ⇐ R < j, use the expression FLOOR(i + RAND() * (j − i)). For example, to obtain a random integer in the range 7 ⇐ R < 12, use:

SELECT FLOOR(7 + (RAND() * 5));

FLOOR(7 + (RAND() * 5))
8

Generate an eight-character string of digits.

SELECT SUBSTRING(MD5(RAND()) FROM 1 FOR 8);

Generate an eight-character string containing characters only.

SELECT concat(substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', rand()*52+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', rand()*52+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', rand()*52+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', rand()*52+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', rand()*52+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', rand()*52+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', rand()*52+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', rand()*52+1, 1))

For more information, see RAND() in the MySQL documentation.