This example is used for the development of a simple application. it means to randomly read all the data in the current table and save it to another table, this achieves the random record function. how does MySQL retrieve random data from the table?
I have discussed this issue in the group before. It is interesting. mysql syntax is really fun.
They all wanted to use PHP to implement random queries, but retrieving multiple entries seemed to require more than two queries.
After reading the manual, find the following statement to complete the task.
SELECT * FROM table_name order by rand () LIMIT 5;
Rand said this in the manual:
RAND ()
RAND (N)
Returns a random floating point value ranging from 0 to 1.0. If an integer parameter N is specified, it is used as a seed value.
Mysql> select RAND ();
-> 0.5925
Mysql> select RAND (20 );
-> 0.1811
Mysql> select RAND (20 );
-> 0.1811
Mysql> select RAND ();
-> 0.2079
Mysql> select RAND ();
-> 0.7888
You cannot use the column with the RAND () value in an order by clause, because order by will calculate the column multiple times. However, in MySQL3.23, you can do: SELECT * FROM table_name order by rand (), which is helpful for obtaining a data from select * FROM table1, table2 WHERE a = B AND c
But I tried it. it takes 0.08 sec to execute a-thousand-record table. it's a little slower.
Later I consulted google and got the following code:
SELECT * FROM table_name AS r1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM table_name)) AS id) AS r2 WHERE r1.id >= r2.id ORDER BY r1.id ASC LIMIT 5;
The execution efficiency requires 0.02 sec. Unfortunately, only mysql. * and above support such subqueries.
The following is the php code:
<? // The database connection is not written here $ s = isset ($ _ GET ['s'])? $ _ GET ['s ']: 0; $ e = isset ($ _ GET ['E'])? $ _ GET ['E']: 50; $ count = 85000; if ($ s <$ count) {$ SQL = "select * from table prefix _ info where isget = 0 order by id desc limit $ s, $ e"; $ query = mysql_query ($ SQL ); while ($ rs = mysql_fetch_array ($ query) {$ id = $ rs ['id']; $ sss = $ rs ['SS']; $ typeid = $ rs ['typeid']; $ isget = $ rs ['isget']; $ SQL = "insert into table prefix _ info_bak (id, table prefix, typeid, isget) values ('$ ID',' $ SS', '$ typeid',' $ isget') "; mysql_query ($ SQL); echo $ SQL; // exit; $ sqlu = "update table prefix _ info set isget = 1 where id = ". $ rs ['id']; mysql_query ($ sqlu);} echo'
Processing data. the current value is '. $ s.'... ';} else {echo'. all data is processed and then sorted randomly. ';}?>