MYSQL insert random from list

问题:

I would like to add a random value to a table. While I know how to add random integers within a range, I am currently stumped on how to add a randomly selected item from a list.

Let's say I have a MYSQL table for IM accounts. I would like to fill it with random data.

INSERT INTO `im` (`im`, `service`)
SELECT LOWER(`last`), RANDOM-SELECTION-HERE
FROM `contact`;

What this query should do is add to the IM table the contact's last name with a random selection from an array I would give. For example, the array would be:

['AIM', 'ICQ', 'MSN', 'Yahoo', 'GTalk', 'Other']

So, I would like to add the users last name plus one of the random items from the array.

NOTE: I know this is fully possible with a programming language such as PHP, Perl, etc., but I am not trying to do that. Please try to provide a way to do this strictly with MYSQL.

回答1:

INSERT INTO `im`
(`im`, `service`)
SELECT LOWER(`last`),
    ELT(0.5 + RAND() * 6, 'AIM', 'ICQ', 'MSN', 'Yahoo', 'GTalk', 'Other')
FROM `contact`

回答2:

If you're willing to add the services to another table...

INSERT INTO `im` ( `im` , `service` )
SELECT LOWER( `last` ) , (
   SELECT `service`
   FROM `service`
   ORDER BY RAND( )
   LIMIT 1
)
FROM `contact`