MariaDB sample data for test queries? [duplicate]

Question

In PostgreSQL we use generate_series a lot to do quick test cases.

For example here I generate a quick test for that question using

CREATE TABLE foo
AS
  SELECT x::int AS a, (x%2*x)::int AS b
  FROM generate_series(1,3e6) AS gs(x);

What's the easy way to generate sample data in MariaDB? Is there anything like generate_series or how do experienced MariaDB users generate test cases?

Answer 1

In MariaDB (which is a better MySQL) there is a sequence storage engine that can be used to generate sequences:

CREATE TABLE foo
AS
SELECT seq AS a, seq%2*seq AS b
FROM seq_1_to_3000000;

Answer 2

A bit of an open-ended answer here, but I tend to use whatever tricks are necessary for this sort of thing. It's kind of limited only by one's imagination and the exact data you require.

Selecting data derived from querying existing big tables is often a quick win. e.g. to make 10,000 rows of data:

SELECT {some calculated columns}
FROM a_n_other_big_table
LIMIT 10000

Or if that's not big enough, use the oft-derided cross join

SELECT {some calculated columns}
FROM a_n_other_big_table t1
CROSS JOIN a_n_other_big_table t2
LIMIT 1000000

cross join as many as you need!

SELECT {some calculated columns}
FROM a_n_other_big_table t1
CROSS JOIN a_n_other_big_table t2
CROSS JOIN a_n_other_big_table t3
LIMIT 1000000000

and so on..

Another quick win if you can construct a few rows by one means or another, can be to then select the contents of that table back in to itself

INSERT INTO my_table
SELECT {some derivation of the existing rowset, or just the same rows again}
FROM my_table

And then execute that as many times as you need in order to repeatedly double up the rows