Fetching multiple random rows from a database. As a follow up to my earlier article about fetching a single random row from MySQL I will tell you today, how you can fetch multiple random rows from a table without any hassle. Compared to the solution with fetching a single random row, fetching multiple random rows requires some tricks. Let us start with creating two new test tables: CREATE TABLE `test` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR( 32 ) NOT NULL, PRIMARY KEY (`id`) ) ENGINE = InnoDB; CREATE TABLE `test_gapless` ( `test_id` INT UNSIGNED NOT NULL, `random_id` INT UNSIGNED NOT NULL, PRIMARY KEY (`test_id`), UNIQUE (`random_id`) ) ENGINE = InnoDB; Now we have two tables. Our primary table containing the usual stuff like a primary ID and a name.
DELIMITER // CREATE TRIGGER `test_insert` AFTER INSERT ON `test` FOR EACH ROW BEGIN DECLARE `next_random_id` INT; SELECT (COALESCE(MAX(`random_id`), 0) + 1) INTO `next_random_id` FROM `test_gapless`; INSERT INTO `test_gapless` ( `test_id`, `random_id` ) VALUES ( NEW. SQL - How can I remove duplicate rows. MySQL 5.1 Reference Manual. SQL Statement Syntax. Functions and Operators. String Functions. Date and Time Functions.