Recently, it is necessary to study the randomized extraction method of mysql. For example, you have to randomly extract a record from the TableName table. Everyone is a select * from tablename Order by rand () Limit 1, but later I checked the official manual of MySQL, which is the following prompts for Rand () : you can not use a column with RAND () values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times in MySQL Version 3.23, you can, however, do:. SELECT * FROM table_name ORDER BY RAND () Probably, the RAND () function cannot be used in the ORDER BY clause, as this will cause the data column to be scan multiple times. However, in the MySQL 3.23 version, it is still possible to achieve random by order by rand (). Then I tried the feasibility, order by rand () can execute on my own version 4.0, but can't be executed in the company's 3.x (specific forgot), it seems that it seems a bit different from the official manual. Later, I found some information on the Internet. I learned that select * from tablename Order by rand () Limit 1 scans the entire table and then returns a record again. For tables, usually not more than 300,000, this way is practical. But once the record is greater than 300,000, this process will become very slow! ! ! Therefore, the conclusion is that I suggest that it is not necessary to use order by rand ()! Because one can avoid the inefficiency caused by increasing the amount of table data in the future; second to avoid this write method. Finally, give a relatively practical alternative method: Assumption ID is the primary key: SELECT MIN (ID), max (ID) from tablename then: $ ID = RAND ($ min, $ max); // Take Rand Returns an ID number between the maximum ID and the minimum ID just taken. Finally: select * from tablename where id = '$ ID' LIMIT 1 If you use Auto Increment ID number, you may have an ID column once deleted, causing the largest and minimum ID inseparable, here You can first determine if the randomly generated ID number exists.
Some people will definitely ask: If it is not random search, it is necessary to randomly search multiple, such as what should I do? ? ?
