Let's say I have a table with an integer column called ID being a primary key. It is unique and not-null, but we cannot guarantee it being sequential without gaps. I.e. we may have rows with ID = 1,2,6,7,8 and we do not have rows with ID 3,4,5 etc etc.
This is why we cannot just generate a random number and get a row with that corresponding ID.
Also we want all rows to have equal chance of being selected, so b/c of the gaps in ID values we cannot use simple approach with random number in 0 to max(id) range
The number of rows in the table is not known either.
How can I select a random row from that table?
Here is the table schema:
CREATE TABLE IF NOT EXISTS `test_data` (
`id` int(10) unsigned NOT NULL,
`create_date` datetime DEFAULT NULL,
`text_1` varchar(255) NOT NULL DEFAULT 'BMqFXUslYnGsYsPxHTtZVbcwnEWFmSXxTAUV9YxXXDH5ClUEUO8kFz0cW1xC3o9aMSwabnEr43W23KZnKvrk8PHEJv18SU5JHTH72sLTtleitBJBIWmIpul7LtuYOpc4iRDqEAT80UeG7L2l4r1pr2jEMW7222reAOuIcBIUcsH9LYlojeQjVkc9ZhYXgnN3xRGHLJ3L0MGoXO4GHttEv053DqkkKYEye34bpGI2tJ0IE9M8BIFf2u08jB50nhD',
`text_2` varchar(255) NOT NULL DEFAULT 'hoA6tWi8AEcikkJM50Mz800PGTUKNnyj3OCKhyJ4ExaJf6bYbqXlNWo4y0XXXo7HuvsNgYWnn16211RbKDesQ852QA33s1eni4pBoraEs3YiV0W69yMY7Nf0pvQI198HUVKYPWk9zpK38PDphtPJXO2z5Wb8mbBN0gN8iK5xzUQQDwoAJlO3Z8xXn2OWyVjKswRbZNKW6l0tvn0zN4S4BoR9gkN7s4Ov9tTGeF4uwWYhPEs0WsDqatMjmbnMQmC'
) ENGINE=InnoDB;
ALTER TABLE `test_data`
ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `id` (`id`);
Aucun commentaire:
Enregistrer un commentaire