MySQL uses the RAND function to implement random numbers [go]

How to write a statement can update hundreds of MySQL data! Need to test MySQL database, there is a database of tens of thousands of data, how to write a PHP file each time update hundreds of information, I am writing a cycle update a message, so I know with while write can be, if one update is like 100 data change how to write it! Thank you   The correct answer is: UPDATE cdb_posts  set  views = rand ();  by the way you find some examples of the MySQL rand function, as follows:  that in the Insert command, Value () with Rand (), note that the field width is sufficient to always think that MySQL randomly query a few data, using   select * from ' table ' ORDER by RAND () LIMIT 5. But the real test is that it's very inefficient. A library of more than 150,000, query 5 data, incredibly more than 8 seconds   View the official manual, also said Rand () in the ORDER BY clause will be executed several times, natural efficiency and very low.   you cannot use a column with RAND () values in an ORDER BY clause, because order by would evaluate the column m Ultiple times.  Search Google, the internet is basically querying max (ID) * RAND () to get data randomly.   select * from ' table ' as T1 joins (select ROUND (RAND () * (select MAX (ID) from ' table ')) as ID) as T2 WHERE t1.i D >= t2.id ORDER by T1.id ASC LIMIT 5, but this results in a continuous 5 record. The solution can only be one query at a time, query 5 times. Even so it is worth it, because 150,000 of the tables, the query only need 0.01 seconds less than.   The above statement uses the Join,mysql forum where someone uses   select * from ' table ' WHERE ID >= (The SELECT floor (MAX (ID) * RAND ()) from ' t Able ') ORDER by ID LIMIT 1; I tested it, it takes 0.5 seconds, and it's a good speed, but there's a big gap with the above statement. There is something wrong with the total sleep.   So I rewrote the sentence a bit.   select * from ' table ' WHERE ID >= (SELECT Floor (RAND () * (select MAX (ID) from ' table ') ")   ORDER by ID LIMIT 1; The efficiency is improved, the query time is only 0.01 seconds   Finally, the statement is refined, plus min (id) judgment. When I first tested it, it was because I didn't add the min (id) judgment, and half the time I always queried the previous rows in the table. The full query statement is:   select * from ' table ' WHERE ID >= (The Select Floor (RAND () * ((select MAX (ID) from ' table ')-(select MI N (ID) from ' table ') + (SELECT MIN (ID) from ' table '))   ORDER by ID of LIMIT 1; select * from ' table ' as T1 JOIN (S Elect ROUND (RAND () * ((select MAX (ID) from ' table ')-(select min (id) from ' table ') "+ (select min (id) from ' table ')" as ID) A S T2 where t1.id >= t2.id ORDER by t1.id LIMIT 1; Finally, the two statements are queried 10 times in PHP, the former takes 0.147433 seconds and the latter takes 0.015130 seconds to appear in the language of join Fabienne is much more efficient at using functions directly in the where.    random functions---------Sql newid () and rand ()-------------random functions of SQL NEWID () and Rand ()
Random functions of SQL Server NEWID () and Rand ()
SELECT * from Northwind. Orders ORDER by NEWID ()
--Random sorting
SELECT TOP Ten * from Northwind. Orders ORDER by NEWID ()
--Randomly remove 10 records from the Orders table
Example
A. Using the NEWID function with variables
The following example uses NEWID () to assign a value to a variable declared as a uniqueidentifier data type. The value is output before testing the value of the uniqueidentifier data type variable.
--Creating a local variable with declareset syntax.
DECLARE @myid uniqueidentifier
SET @myid = NEWID ()
PRINT ' Value of @myid is ' + CONVERT (varchar (255), @myid)
Here is the result set:
Value of @myid is 6f9619ff-8b86-d011-b42d-00c04fc964ff
Attention:
NEWID the values returned for each computer are different. The figures shown are only useful for interpreting.
Random function: rand ()
Execute in Query Analyzer: Select rand (), you can see that the result is similar to a random decimal: 0.36361513486289558, a decimal like this is used in the actual application is not much, generally to take random numbers will take random integers. Let's look at the following two methods of randomly taking integers:
1.
A:select Floor (rand () *n)---Number generated is this: 12.0
B:select cast (*n) as int---the number generated is this: 12
2.
A:select Ceiling (rand () * N)---Generated number is this: 12.0
B:select cast (Ceiling (rand () * N) as int)---the number generated is this: 12
where n is an integer you specify, such as 100, you can see that the a method of the two methods is a decimal with the. 0, and the B method is the real integer.
In general, there is no difference between the two methods, really no difference? In fact, there is a point where they generate a random number range:
The numeric range of Method 1: between 0 and N-1, such as CAST (Floor (rand () *100) as int) generates any integer from 0 to 99
The numeric range of Method 2: between 1 and N, such as cast (ceiling () as int) generates any integer from 1 to 100
For this difference, look at the SQL online Help on the cicada:
Compare CEILING and floor
The CEILING function returns the smallest integer greater than or equal to the given numeric expression. The floor function returns the largest integer less than or equal to the given number expression. For example, for a numeric expression 12.9273,ceiling will return 13,floor will return 12. Floor and CEILING The data type of the return value is the same as the data type of the input numeric expression.
Now, you can use these two methods to get the random number according to your own needs ^_^
In addition, we also need to remind you rookie, about random access to the table of arbitrary N Records of the method, very simple, with newid ():
Select TOP N * FROM table_name ORDER BY NEWID ()----N is an integer that you specify, and the table is the number of entries that are obtained for the record.
The function in Access is RND ()
SELECT Top 10 Table 1.*, RND (ID) as BB from table 1 ORDER by Rnd (ID)
SELECT Rnd (ID) as ME,RND () as from table 1
Select top tb_pess_paper.* from Tb_pess_paper OrDER by Rnd (IsNull (ID) *0+1);

MySQL uses the RAND function to implement random numbers [go]