How do I select four random tables from a MySQL database having thousands of tables?

To select four random tables, use ORDER BY RAND(). Following is the syntax −

select TABLE_NAME AS anyAliasName
from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA = ‘yourDatabaseName’;
order by rand()
limit yourLimitNumber;

Let us implement the above syntax in order to select four random tables from a MySQL database that has thousands of tables.

Here, LIMIT is used to set the number of records you want to fetch. Since we want 4 records, therefore we would be using LIMIT 4. Following is the query −

mysql> select TABLE_NAME AS Random4TableName
   from INFORMATION_SCHEMA.TABLES
   where TABLE_SCHEMA = 'web'
   order by rand()
   limit 4;

This will produce the following output −

+------------------+
| Random4TableName |
+------------------+
| demotable474     |
| demotable313     |
| demotable452     |
| demotable382     |
+------------------+
4 rows in set (0.20 sec)

AmitDiwan

e

Updated on: 01-Oct-2019

85 Views

Kickstart Your Career

Get certified by completing the course

Get Started