MySQL insert random rows from select

Question

I've got a table set up like this -

Users
===========
id
userid
friendid

and another set up like this

Game_Objs
===========
id
obj_id

For testing, I need to insert a random number of obj_ids from Game_Objs into a new table associated with friendids, so something like this -

-------------------------------------------------------------------
Friend_Objs
-------------------------------------------------------------------
id                | friendid              | obj_id
-------------------------------------------------------------------
1                 | 123                   | aaa
1                 | 123                   | bbb
1                 | 123                   | ccc
1                 | 456                   | abc
1                 | 456                   | bbb
1                 | 456                   | cde

Where the number of obj_ids is random for each friendid.

Is this possible using just mysql? If not, what would be the best way to accomplish it in PHP?

I smell homework. Anyway, I dont know how to do it only mysql but with php its very possible just do a random for obj_id in php and put it in the query of Insert to executed in mysql. — oneofakind, Jun 20 '13 at 02:58
Not homework, but my MySQL skills are definitely a sword that needs sharpened. This is just a test case where we need a lot of data to pull from. The problem with doing it in PHP is that the Users table is 400k+ rows, and the game_objs table is 20k, so that's a lot of data for PHP to store. I was mostly looking for a mysql solution to this. Thanks for the answer, though. — michael, Jun 20 '13 at 15:28

Pradeep Pati · Answer 1 · 2013-06-20T03:01:31.140

0

Use something like the following

$Query = "insert into `Friend_Objs` " . 
"values (1, 123, " . substr(str_shuffle("abcefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"), 0, 3);

Change the 3 to the length of string you want.

edited Jun 20 '13 at 03:01

answered Jun 20 '13 at 02:55

Pradeep Pati

5,779
3
29
43

Sorry, I may not have been clear, but Friend_Objs.obj_id has to be a value from Game_Objs.obj_id – michael Jun 20 '13 at 03:17

score 0 · Answer 2 · edited May 23 '17 at 10:32

0

Here is how to load 600k random rows into a MySQL table fast: ( MySQL select 10 random rows from 600K rows fast )

Integrate that with:

INSERT INTO table(column1, column2 ) SELECT ...

Use the select query in the above link that returns random rows.

edited May 23 '17 at 10:32

Community

1
1

answered Jun 22 '13 at 01:52

oneofakind

552
17
41

MySQL insert random rows from select

2 Answers2