how to mysql random select table name

Question

perhaps i'm missing something here. I am trying to randomly select a table name from a database that only has numbers/numeric ONLY. Here's what I've gotten but when test running the query, mysql is telling me that my query is incorrect. Any kind of help I can get on this is greatly appreciated!

$sql = "SHOW TABLES FROM `master` WHERE TABLES is NUMERIC ORDER BY RAND() LIMIT 1";
$result = mysql_query($sql);

Akhil · Accepted Answer · 2015-06-02 10:29:05Z

2

I am not sure whether you can get the result using show tables.

But, you can definitely use information_schema to achieve the same

SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA = 'master' 
AND CAST(TABLE_NAME AS UNSIGNED) <> 0 
 ORDER BY RAND() LIMIT 1;

To Suppress Truncated data warnings, use the following query using regexp

SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA = 'master' 
AND TABLE_NAME REGEXP '^(-|\\+)?([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$'
 ORDER BY RAND() LIMIT 1;

edited Jun 2, 2015 at 10:29

answered Jun 2, 2015 at 9:42

Akhil

2,60225 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

thevoipman Over a year ago

Thank you - this works great, but I'm getting a bunch of errors for tables that is not numeric Truncated incorrect INTEGER value: 'Apple'

Collectives™ on Stack Overflow

how to mysql random select table name

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related