Shuffle database id

Question

I have a database with songs. Every song have an unique id. How I can generate a random unique value for every id in database?

Example:

id | song name    
1  | song1  
2  | song2  
3  | song3

After shuffle

id | song name    
45 | song1  
96 | song2  
10 | song3

Any idea?

Matt · Accepted Answer · 2016-03-31T08:47:04.920

1

Use a combination of FLOOR and RAND() to get what you want.

If there are 1000 songs in your db.

SELECT FLOOR(RAND() * (1 - 1000 + 1)) + 1 AS ID, "song name"
FROM yourtable

If there are 7000 songs in your db.

SELECT FLOOR(RAND() * (1 - 7000 + 1)) + 1 AS ID, "song name"
FROM yourtable

Update..

UPDATE yourtable
SET id = FLOOR(RAND() * (1 - 1000 + 1)) + 1;

edited Mar 31 '16 at 08:47

answered Mar 31 '16 at 08:41

Matt

14,906
27
99
149

And what if `ID` is unique(make sense) and the rand will hit the same number twice(also make sense) – sagi Mar 31 '16 at 08:50
2

How do you avoid generating duplicate values for the id with this? – schlonzo Mar 31 '16 at 08:51

score 0 · Answer 2 · edited May 23 '17 at 11:50

0

Does the ID have to be an integer? If not you could think about using GUIDS . If this is a possibility for you, then you get further information for migrating your table here: Generate GUID in MySQL for existing Data?

edited May 23 '17 at 11:50

Community

1
1

answered Mar 31 '16 at 08:58

schlonzo

1,409
13
16

score 0 · Answer 3 · answered Mar 31 '16 at 09:10

0

try this:

SELECT  @nr:=@nr+1 AS nr, tmp.* 
FROM (
  SELECT * FROM yourTable ORDER BY rand()
  ) AS tmp
CROSS JOIN ( SELECT @nr:=0 ) AS parameter

answered Mar 31 '16 at 09:10

Bernd Buffen

14,525
2
24
39

Shuffle database id

3 Answers3