7/28/2023 0 Comments Mysql insert random data![]() ![]() Some caution: Since this query uses a User Defined Variable and these variables are connection specific, you can NOT use this MySQL query with a system that uses connection pooling or persistent connections. The number set would be sequential but each row get a random number within that set, without duplicates. This way, the next row will get this value + 1 and since we’re sorting randomly, each row will end up having a random AND unique value. So, for each updated row, we assign it the + 1) value while storing this value in the same variable. We first set the user-defined variable to 0, then we update all rows while sorting using RAND() as our sorting variable. UPDATE `productData` SET `randCol`= := + 1) So, if we want unique values in each row while randomness, we could go for something like this. But this query does not guarantee unique values for each row, even with a very large range. The previous query is useful when you want to fill a column with random numbers within a range. Generating UNIQUE random numbers in a table When adding 1 to these values we have both ends of the range are covered and no numbers outside it will ever be generated. On the other side of the range, the smallest value would occur when RAND() returns 0. With that in mind, the largest value which could be generated by RAND() * 100 would be 99.999999, which when floored would give 99. RAND would also return a 0 at times, which in turn would return a zero in the product calculation, an invalid value in our desired range. ![]() Wait! Why not simply use FLOOR( RAND( ) *100 )? Well, according to the MySQL documentation for RAND(), this function always returns a random floating-point value v in the range 0 <= v < 1.0, so it won’t ever return 1, leaving the high end of the range out. Then, if you want to update each row and place a value in the 1-100 range in “randCol”, you could do something like this: UPDATE `productData` SET `randCol` = FLOOR( 1 + RAND( ) *100 ) Although you might not expect it, random numbers are used in databases quite often, especially when generating dummy data, providing randomized lists of products, to name just a couple.įor instance, let’s say you have a table named “productData” and inside this table there’s a field named “randCol”.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |