Declaration
RANDSTR(N, gen)
Description
This function generates a random string of the specified length, containing characters from the sets A-Z, a-z, and 0-9.
N: the length of the generated random string:- If
N < 0, returnNULL. - If
N = 0, an empty string is returned. - If
N > 0andN <= max_allowed_packet, a random string of length N is returned. - Returns
NULLwhenN > max_allowed_packet. Here,max_allowed_packetis a system variable with a default value of 16777216.
- If
gen: the distribution function. Valid values:- If it is a constant, the same random string will be generated every time
RANDSTR()is called. - Random functions that return integer values, such as
RANDOM(). - Distribution functions, such as uniform distribution, Zipf distribution, and Gaussian distribution.
- If it is a constant, the same random string will be generated every time
Random string generation algorithm: The LCG algorithm is used to generate N random values, from which N characters are selected to form the random string. Therefore, when N is large, it consumes a significant amount of CPU time. For example, generating a random string of length 300 MB took 1.7 seconds.
obclient> SELECT LENGTH(RANDSTR(300000000, 1)) FROM DUAL;; +------------------------------+ | LENGTH(RANDSTR(300000000,1)) | +------------------------------+ | 300000000 | +------------------------------+ 1 row in set (1.73 sec)
Examples
The following example shows how to generate a random string of length 10 using the RANDSTR() function.
obclient> SELECT RANDSTR(10, RANDOM()) FROM TABLE(GENERATOR(7));
+-----------------------+
| RANDSTR(10, RANDOM()) |
+-----------------------+
| TGfdBAZ1BP |
| VvMbD2bChR |
| VNSB6MpgQw |
| 3QWYQYQVJ4 |
| CpyE58jM0o |
| vBvsJBGGmX |
| rg3Pkde0UJ |
+-----------------------+
7 rows in set
The RANDSTR() function allows you to control the frequency of random characters generated by using the gen parameter. In the following example, 1,000 one-character strings are generated and their distribution follows the Zipf distribution.
obclient> SELECT RANDSTR(1, ZIPF(1, 10, RANDOM())) s, COUNT(*) FROM TABLE(GENERATOR(1000)) GROUP BY s ORDER BY 1 ASC;
+------+----------+
| s | COUNT(*) |
+------+----------+
| 0 | 27 |
| 1 | 41 |
| 4 | 32 |
| 5 | 54 |
| 8 | 64 |
| 9 | 69 |
| C | 88 |
| D | 125 |
| G | 168 |
| H | 332 |
+------+----------+
10 rows in set
The RANDSTR() function can generate strings of different lengths by controlling the value of the N parameter, as shown in the following example.
obclient> SELECT RANDSTR(5, 40) FROM DUAL;
+----------------+
| RANDSTR(5, 40) |
+----------------+
| UU3g0 |
+----------------+
1 row in set
obclient> SELECT RANDSTR(1, 40) FROM DUAL;
+----------------+
| RANDSTR(1, 40) |
+----------------+
| U |
+----------------+
1 row in set
obclient> SELECT RANDSTR(0, 40) FROM DUAL;
+----------------+
| RANDSTR(0, 40) |
+----------------+
| |
+----------------+
1 row in set
obclient> SELECT RANDSTR(-1, 40) FROM DUAL;
+-----------------+
| RANDSTR(-1, 40) |
+-----------------+
| NULL |
+-----------------+
1 row in set
When gen is a constant, repeatedly calling the RANDSTR() function will generate the same random string, as shown in the following example.
obclient> SELECT RANDSTR(10, 1442) FROM TABLE(GENERATOR(7));
+-------------------+
| RANDSTR(10, 1442) |
+-------------------+
| 4dTm5bOtGc |
| 4dTm5bOtGc |
| 4dTm5bOtGc |
| 4dTm5bOtGc |
| 4dTm5bOtGc |
| 4dTm5bOtGc |
| 4dTm5bOtGc |
+-------------------+
7 rows in set
obclient> SELECT RANDSTR(10, 1442) FROM TABLE(GENERATOR(7));
+-------------------+
| RANDSTR(10, 1442) |
+-------------------+
| 4dTm5bOtGc |
| 4dTm5bOtGc |
| 4dTm5bOtGc |
| 4dTm5bOtGc |
| 4dTm5bOtGc |
| 4dTm5bOtGc |
| 4dTm5bOtGc |
+-------------------+
7 rows in set