Random Number Range In MySQL

Note: This post is over two years old and so the information contained here might be out of date. If you do spot something please leave a comment and we will endeavour to correct.

5th April 2008 - 2 minutes read time

To create a number between one value and the next you can use the following formula, where i is the lower end of the range and j is the higher end of the range.

FLOOR(i + RAND() * (j – i))

Rather than put in (j-i) in your query you should put in the result. So for a number between 1 and 10 you would make i = 1 and j = 11. 11-1 = 10 so the query would run like this.

SELECT FLOOR(1 + (RAND() * 10));

For a number between 64 and 104 you would use the following query.

SELECT FLOOR(64 + (RAND() * 41));

range

MySQL

Comments

This is brilliant. I wrote 30 lines to accomplish this but never thought of the 'Floor Plan' so to speak.

Submitted by Dr. Dey on Mon, 06/16/2014 - 23:58

Permalink

Add new comment

Related Content

Case Insensitive Like Searches In MySQL

2nd July 2011

I needed to create a query that did a case insensitive search using the LIKE command in MySQL and I quickly realised that in order to do this I would need to alter both the parameter and the table data to be the same case. This can be done by using the MySQL UPPER() command on the table data and the strtoupper() PHP function on the input data.

Connecting To A Non Standard MySQL Socket In PHP

11th February 2011

Connecting to a MySQL database in PHP is usually not a difficult thing to do, in fact it is one of the first things that many tutorials will go though. However, there are certain curcumstances that require more information than the standard host, password and username details.

Count Number Of Characters In A String With MySQL

30th September 2010

Today I needed to grab some data from a table where there was more than one occurrence of a string within another string. Basically, I needed to find all URL's from a table that were more than 3 levels deep (i.e. with 3 slashes), but realised there wasn't a function to do this in MySQL.

MySQL Order Table By Character Length

24th September 2009

As part of debugging a bit of code I needed to know the longest possible field lengths that a record contains. You might need to know this if you are performing a database migration. The following query returns a field, along with the length of the string, and orders the results by the number of characters in that string.

Backup MySQL Database PHP Script

12th August 2009

There are quite a few scripts available on the Internet that allow you to dump data from a database into a format that can be used to replicate that database structure elsewhere. The following function is my take on this commonly occurring script.

Starting And Stopping MySQL Using Windows .bat Files

13th May 2009

I use my PC for a lot of different things, and I don't necessarily need to have MySQL server running all the time, especially when I want to play a game. So I wondered if there was a simple way in which I could start and stop the server using a simple .bat file.