Inserting random characters to MYSQL Database

I want to add 100 entry to users table numbers field, 开发者_运维百科random characters length is 10, all entry should be unique as well. How can i achieve this using MYSQL query code ?

Or do i need to use PHP ?

Help me with code snippets please. Thanks.

---

in mysql u can do like :

insert into table ( SUBSTRING(MD5(RAND()) FROM 1 FOR 10) , field2 , field3) , ( SUBSTRING(MD5(RAND()) FROM 1 FOR 10) , field2 , field3) , .........

..............

in php see this 2 links :

Short unique id in php

What is the best way to generate a random key within PHP?

---

That may create duplicates and it's too long. This is 10 char long:

UPDATE users SET numbers = SUBSTRING(MD5(RAND()) FROM 1 FOR 10)

However, you could still get duplicate values.

So you could put a unique restraint on your column. Then try the update. If there are duplicates, you will get an error. Then just try again. You only have 100 entries, so it's probably fine.

Is this for passwords?

If so, I'd recommend encrypting the value. Of course you then have the problem of not knowing what the value is. So you could create a temporary table, insert the random values in there. Then encrypt the values as they are inserted from the temp table into the real table. You can then use the temp table for reference (giving the users their passwords, etc). Hope that helps.

---

UPDATE TABLE users SET number_field = MD5(RAND());

---

You can't generate an unique random number. Over time, the randomness will generate a number already stored. You need to make a "quasi-random" number, meaning that it's a number based on another data, but it just looks random. You can use the primary key on the table as the base number to generate the "fake-random" number

INSERT INTO myTable(primaryKey,quasiRandom) 
    SELECT IFNULL(MAX(primaryKey),0)+1, CAST(CONCAT(IFNULL(MAX(primaryKey),0)+1,CHAR(FLOOR(RAND()*26)+65),FLOOR(100+RAND()*(500-100)))
 AS CHAR(10)) AS quasiRandom FROM myTable