Repeating Rows in MySQL and MariaDB

You might come across a need to repeat a row a set number of times in your SQL. There's an easy way to do this but you first need to create a numbers table with just one column which is a count starting at 1 and up to whatever number you need. For this example I'll use 10, purely to save space but you would just increase this to any suitable value.

CREATE TABLE `numbers` (
  `count` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

BEGIN;
INSERT INTO `numbers` VALUES (1);
INSERT INTO `numbers` VALUES (2);
INSERT INTO `numbers` VALUES (3);
INSERT INTO `numbers` VALUES (4);
INSERT INTO `numbers` VALUES (5);
INSERT INTO `numbers` VALUES (6);
INSERT INTO `numbers` VALUES (7);
INSERT INTO `numbers` VALUES (8);
INSERT INTO `numbers` VALUES (9);
INSERT INTO `numbers` VALUES (10);
COMMIT;

Nopw we have a table we can join our rows against up to the count stored in the numbers table. Lets take a trivial example. Say I want to repeat the rows for a user in the user table 10 times. Here's how you would do this:

select * 
from mdl_user u join numbers n
on n.count <= 10
where u.id = 2;

This will repeat the same row in the user table for the user with id equals 2 10 times.