MySQL RAND() for Random Records

RAND() returns a floating-point value from 0 (inclusive) to 1 (exclusive). It is often combined with ORDER BY and LIMIT to choose sample rows.

SELECT * FROM student4 ORDER BY RAND() LIMIT 1;

SELECT * FROM student4 WHERE status = TRUE ORDER BY RAND() LIMIT 1;

SELECT * FROM student4 WHERE status = 1 ORDER BY RAND() LIMIT 10;

SELECT * FROM student4 WHERE class IN ('Four', 'Seven') ORDER BY RAND() LIMIT 5;

SELECT * FROM student4 WHERE class = IF(RAND() < 0.5, 'Four', 'Seven') ORDER BY RAND() LIMIT 5;

The filters in these examples build on WHERE, IN(), and Boolean/TINYINT flags.

Update random records

These examples intentionally update random rows. Test on disposable sample data first.

UPDATE student4 SET status = FALSE ORDER BY RAND() LIMIT 2;

UPDATE student4 SET status = FALSE WHERE status = TRUE ORDER BY RAND() LIMIT 15;

UPDATE student4 SET status = TRUE;

Create a table from random rows

See CREATE TABLE for table-definition fundamentals.

Use current CREATE TABLE ... AS SELECT syntax.

CREATE TABLE my_student AS
SELECT * FROM student4
ORDER BY RAND()
LIMIT 10;

CREATE TABLE IF NOT EXISTS my_student AS
SELECT * FROM student4
ORDER BY RAND()
LIMIT 10;

Student4 sample table

CREATE TABLE `student4` (
  `id` int NOT NULL DEFAULT '0',
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  `class` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  `mark` int NOT NULL DEFAULT '0',
  `gender` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'male',
  `status` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `student4`
--

INSERT INTO `student4` (`id`, `name`, `class`, `mark`, `gender`, `status`) VALUES
(1, 'John Deo', 'Four', 75, 'female', 1),
(2, 'Max Ruin', 'Three', 85, 'male', 1),
(3, 'Arnold', 'Three', 55, 'male', 1),
(4, 'Krish Star', 'Four', 60, 'female', 1),
(5, 'John Mike', 'Four', 60, 'female', 1),
(6, 'Alex John', 'Four', 55, 'male', 1),
(7, 'My John Rob', 'Five', 78, 'male', 1),
(8, 'Asruid', 'Five', 85, 'male', 1),
(9, 'Tes Qry', 'Six', 78, 'male', 1),
(10, 'Big John', 'Four', 55, 'female', 1),
(11, 'Ronald', 'Six', 89, 'female', 1),
(12, 'Recky', 'Six', 94, 'female', 1),
(13, 'Kty', 'Seven', 88, 'female', 1),
(14, 'Bigy', 'Seven', 88, 'female', 1),
(15, 'Tade Row', 'Four', 88, 'male', 1),
(16, 'Gimmy', 'Four', 88, 'male', 1),
(17, 'Tumyu', 'Six', 54, 'male', 1),
(18, 'Honny', 'Five', 75, 'male', 1),
(19, 'Tinny', 'Nine', 18, 'male', 1),
(20, 'Jackly', 'Nine', 65, 'female', 1),
(21, 'Babby John', 'Four', 69, 'female', 1),
(22, 'Reggid', 'Seven', 55, 'female', 1),
(23, 'Herod', 'Eight', 79, 'male', 1),
(24, 'Tiddy Now', 'Seven', 78, 'male', 1),
(25, 'Giff Tow', 'Seven', 88, 'male', 1),
(26, 'Crelea', 'Seven', 79, 'male', 1),
(27, 'Big Nose', 'Three', 81, 'female', 1),
(28, 'Rojj Base', 'Seven', 86, 'female', 1),
(29, 'Tess Played', 'Seven', 55, 'male', 1),
(30, 'Reppy Red', 'Six', 79, 'female', 1),
(31, 'Marry Toeey', 'Four', 88, 'male', 1),
(32, 'Binn Rott', 'Seven', 90, 'female', 1),
(33, 'Kenn Rein', 'Six', 96, 'female', 1),
(34, 'Gain Toe', 'Seven', 69, 'male', 1),
(35, 'Rows Noump', 'Six', 88, 'female', 1);
COMMIT;

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: