Generate a unique number in a auto-incrementing 6-8 pattern mysql
<p>Lets say I have a table, e.g.</p>

<pre><code>id | memberName | memberPhoneNumber | prefix | suffix
</code></pre>

<p>I want to generate a prefix and suffix as a composite key that must be in a 6-8 pattern. My primary key is still id. These two columns are used as a reference number, e.g.</p>

<pre><code>Prefix-Suffix
000000-00000000
000000-00000001
000000-00000002
...
000000-99999999
000001-00000000
000001-00000001
000001-00000002
...
567889-48329484
</code></pre>

<p>And so on. Ideally this would be auto-incrementing. What is the best way to do this? I was initially doing this through a staging table where I would populate a number I had generated through a function for the 8 pattern, and then auto incrementing the 6 pattern prefix outside of the database based on the count of the staging table where I was storing the used numbers, then truncating it to rinse/repeat but I want a simpler auto-incrementing solution. Is this possible?</p>

<p>I've been doing reading on composite keys but since I already have a primary key that's distinct and not to be used, I'm unsure of how I could do this outside of my current method.</p>

<p>Any thoughts?</p>