4

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.

Haim Evgi
  • 123,187
  • 45
  • 217
  • 223
spotlightsnap
  • 1,095
  • 7
  • 21
  • 26

4 Answers4

8

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?

Community
  • 1
  • 1
Haim Evgi
  • 123,187
  • 45
  • 217
  • 223
8

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.

d-_-b
  • 6,555
  • 5
  • 40
  • 58
  • 1
    I do realize this answer is 6 years old, but still storing the plain text password even if in a separate "temp" table/column is just defeating the purpose of hashing and is a security hole. Passwords should be **irretrievable** whatsoever. And about "*giving the users their passwords*" -- Passwords should never be "given", and rather they should be entered by the end user. – Mohd Abdul Mujib Jun 07 '16 at 10:40
6
UPDATE TABLE users SET number_field = MD5(RAND());
aviv
  • 2,719
  • 7
  • 35
  • 48
  • If rand returns the same number twice, wouldn't that create a violation of the uniqueness constraint? – sum1stolemyname Mar 23 '10 at 07:59
  • will only work in mysql, in other DBMS you would need to truncate the md5 – knittl Mar 23 '10 at 07:59
  • hi, i want to add 100 entry by once ? How can i ? – spotlightsnap Mar 23 '10 at 08:01
  • Hi @sum1stolemyname you are right. my mistake. A function counting from 1 to 100 will be better. But anyway just wanted to point a direction for solution. – aviv Mar 23 '10 at 08:06
  • @spotlightsnap: are you talking about 'entry' (which I read as add to field for one user or 'row', as in : add 100 users with a 10-char number_field for each??? – lexu Mar 23 '10 at 08:06
  • hi lexu, it's like this, i have table name called users, at users table, there's field name called numbers, i want to generate 100 row and insert into that users table numbers field. Currently the above code generate only 1 row at a time. thanks – spotlightsnap Mar 23 '10 at 08:12
0

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
El Gucs
  • 897
  • 9
  • 18