3

As the title, how to create a 9 digits number primary key which is random, unique, not repeated and from range 100000000 to 999999999? And this method must be work on the godaddy server, seems godaddy have so many limitation.

tinonetic
  • 7,751
  • 11
  • 54
  • 79
chengwei
  • 147
  • 3
  • 16
  • 1
    godaddy is the unspoken word. You shant speak it (from a Star Trek episode) – Drew Sep 03 '16 at 14:25
  • ...Made it so(for the title)...(most Star Trek TNG episodes) – tinonetic Sep 03 '16 at 14:47
  • 3
    How about letting MySQL use its own mechanism for primary key and you simply encode the number you got back using [Hashids](http://hashids.org/)? Instead of altering how MySQL works and breaking what hundreds of super-smart developers did, simply work with the output and deliver it in a different format. – N.B. Sep 03 '16 at 15:12
  • Why do they need to be random? Isn't "unique" enough? An ID's job is to identify the row, not obfuscate its identity. – Bohemian Sep 03 '16 at 15:34
  • @N.B.brilliant idea!! but i also have to use the id for input and that time i have to decode, what mechanism should i use for ur idea? – chengwei Sep 03 '16 at 15:37
  • @RyanVincent you means we pre-generate a list of unique random id, then when we update the pre-generate record in database? – chengwei Sep 03 '16 at 16:00
  • 1
    You use the library I linked for encoding **and** decoding the input. If user provided properly-encoded value, it will be decoded successfully. Since the library's salt, alphabet and length is configurable, you can receive what you wanted without involving MySQL. – N.B. Sep 03 '16 at 16:05
  • @N.B.library you linked? – chengwei Sep 03 '16 at 16:23
  • @chengwei - there's a link in his first comment, next to the library's name... – Mjh Sep 04 '16 at 13:33
  • @N.B.I found it, thank you very much! – chengwei Sep 05 '16 at 06:08

2 Answers2

2

I can only think of two reliable ways of creating unique numbers.

  • Use a systematic process, such as auto-incrementing, where you now the numbers are unique.
  • Store generated numbers in a table.

You want random numbers, so the first method could be applied using a pseudo-random number generator. But the second is probably simpler to implement.

It goes something like this:

create table numbers (
    numberid int auto_increment primary key,
    n varchar(10) not null unique
);

Then you need to create numbers using a loop. Do the following until it succeeds:

insert into numbers (n)
    select cast((rand(*) * 900000000) + 1000000000 as varchar);

You can use last_inserted_id() to then get the most recent number inserted.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • But what if 2 people insert the number at the same time to database, will it still unique? – chengwei Sep 03 '16 at 14:36
  • @chengwei . . . A `unique` constraint is a database guarantee. If two users attempt to insert the same number, then one of the inserts will fail. – Gordon Linoff Sep 03 '16 at 22:11
1

If pseudo-random is OK for you, you could create a trigger like this:

create trigger tr_setid before insert on mytable for each row
   set new.id := (
       select mod ((count(*) ^ 42) * 479001599 + 714320596, 900000000)+100000000
       from mytable);

This system is not good if you also delete records from your table, as this solution assumes count(*) is one larger every time this trigger runs.

The multiplier is a prime and not a divisor of 900000000, guaranteeing that no duplicate number will be generated before all possible numbers have been visited.

The ^ operator is just mapping the count(*) so to make the generated series a bit less predictable.

With this trigger the first 10 records in the table will get these id values:

232387754
711389353
174384556
653386155
348394150
827395749
290390952
769392551
900374962
479376561
trincot
  • 317,000
  • 35
  • 244
  • 286