sql - Generate a unique number in a auto-incrementing 6-8 pattern mysql -

lets have table, e.g.

id | membername | memberphonenumber | prefix | suffix 

i want generate prefix , suffix composite key must in 6-8 pattern. primary key still id. these 2 columns used reference number, e.g.

prefix-suffix 000000-00000000 000000-00000001 000000-00000002 ... 000000-99999999 000001-00000000 000001-00000001 000001-00000002 ... 567889-48329484 

and on. ideally auto-incrementing. best way this? doing through staging table populate number had generated through function 8 pattern, , auto incrementing 6 pattern prefix outside of database based on count of staging table storing used numbers, truncating rinse/repeat want simpler auto-incrementing solution. possible?

i've been doing reading on composite keys since have primary key that's distinct , not used, i'm unsure of how outside of current method.

any thoughts?

your prefix-suffix pattern equivalent normal integer primary key.

imagine had suffix length of 1 digit (instead of 8 in example). make explaining easier.

prefix  | suffix  |  key 000000  | 0       |  0 000000  | 1       |  1 000000  | 2       |  2 ... 000000  | 9       |  9 000001  | 0       |  10 000001  | 1       |  11 

as can see, can enumerate prefix-suffix combinations , assign each 1 integer.

what's more, given integer, can derive prefix-suffix combination corresponds:

key 57 = suffix 7, prefix 5 key 99 = suffix 9, prefix 9 

if working 8-digit suffixes, nothing changes. formulas stay suffix = key % (max_prefix + 1) (with % being modulus or "remainder" operator) , prefix = (key - suffix) / (max_prefix + 1).

what recommend doing using normal auto-increment primary key , deriving prefixes/suffixes have shown above. if want derivative columns stored in database, can on insert trigger populates columns.

if aren't allowed use primary key, use normal sequence number. important point don't have worry "two values"; there's 1 sequence here regardless of how break display.