Mysql order by rand and setting user variable

Can anyone explain what is happening with the following SQL? I understand order by rand is not a good practice, but I am interested in an explanation of why this is happening

开发者_如何学Python

create database test_views_rand;
use test_views_rand;
create table test_table (number int);

insert into test_table  values (1), (2), (3), (4);

select @test := number from test_table order by rand() limit 1;
# selects a random number from 1 - 4
select @test;
# Always gives 4, regardless of what the actual number was in the select query

Just in case it isn't clear, the first select statement is random as expected. The second select statement always gives 4, regardless of the outcome of the first select statement.

---

The assignments are done before order by like @bw_üezi said.

select @test := number, @aa:=(@a:=@a+1), @a, @r 
  from test_table, (select @a:=0) a 
 order by (@r:=rand());

results

|| 3 || 3 || 3 || 0.160410950109745  ||
|| 4 || 4 || 4 || 0.0159870376959322 ||
|| 1 || 1 || 1 || 0.161011773311544  ||
|| 2 || 2 || 2 || 0.928689247862146  ||

and then select @test,@aa,@a,@r results

|| 2 || 2 || 4 || 0.015987037695932  ||

How @aa can ever be 4 if in the end it is only 2?

This is because of the bugfix: http://bugs.mysql.com/bug.php?id=16861

updates variable from the result_field value when being sent to a client.

User-variable assignments that are not part of the expression in the select part are done twice (at least for last row, not sure about others)

Manipulating user-variables inside query can often yield in unexpected results.

You are better off using query:

select @test := (select number from test_table order by rand() limit 1);

or

set @test := (select number from test_table order by rand() limit 1); 

---

the assignment @test := number is done before order and limit.

As Richard points out this is not quite right. Explain path shows "Using filesort".
On the other hand Imre points out there was bug in MySQL with ORDER BY rand()

---

(your code is missing an INSERT keyword) On my version of MySQL (5.5), these are my results

drop table if exists test_table;
create table test_table (number int);

insert into test_table (number) values (1),(2),(3),(4);

select @test := number from test_table order by rand() limit 1;
# selects a random number from 1 - 4
select @test;

Results

3,4,3,1,4,3,4,3,1,3

However, if you order by rand(now()) and do it quickly, it will stick at 4....3....2....4... because it is no longer random, you have given it a specific seed.

If you run it with order by rand(1), the answer is ALWAYS the same, because you have asked it to seed based on 1 consistently.

---

To see what RAND() produces (which is what is used to sort the rows), check out this query:

select rand() from (
select 1 union all select 2 union all
select 1 union all select 2 union all
select 2
) x;

Run it many times. Then swap for rand(now()) and run it many times quickly. Then swap for rand(1) and run again.

---

To see how this actually affects your query, just remove the ORDER BY and see the magic happening.

select @test := number, rand(2) from test_table
order by rand(2)

Change rand(2) to rand(1) or just rand() (remember to change in both places). Watch the 2 columns closely. As for how @test := works, it does set it to the first row. BUT, it continues to set it for EVERY row, and ends up with the value set at the last row encountered.

---

It isn't true

If I run this query

select @test := number from test_table order by rand() limit 1;

I get different results.

---

Lets break it down

This creates a database on your server called 'test_views_rand', all of our tables will be in this database Create database test_views_rand;

This command says to use the newly created database 'test_views_rand'

use test_views_rand;

This command creates the first table called 'test_table' in the 'test_view_rand' database. The table contains only one column named 'number' which holds integers

create table test_table (number int);

This command inserts four entries into the 'test_table' table. This command isn't syntax I normally see.

into test_table  values (1), (2), (3), (4);

This command selects all the numbers from the 'test_table' table and mixes the order in which the results appear ie. 1,3,2,4 or 4,2,3,1, or 1,2,3,4, etc. and picks the number out of these four so you get one number.

select @test := number from test_table order by rand() limit 1;

Since we inserted the entires 1,2,3,4 it will return 4 since that is the top entry?

select @test;

---

It is working perfectly fine for me.

Chances are that you didn't try enough times or something. Output of my cases:

mysql> select @test := number from test_table order by rand() limit 1;
+-----------------+
| @test := number |
+-----------------+
|               4 |
+-----------------+
1 row in set (0.00 sec)

mysql> select @test;
+-------+
| @test |
+-------+
|     4 |
+-------+
1 row in set (0.00 sec)

mysql> select @test := number from test_table order by rand() limit 1;
+-----------------+
| @test := number |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

mysql> select @test;
+-------+
| @test |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)