title: MYSQL Batch insert but assign unique personalized label
body: <p>I have a rather unique requirement that I have been trying to solve for weeks now. I have a solution but I don't think it's robust enough.</p>
  <p><strong>Problem</strong></p>
  <p>I need to generate unique <strong>personalized</strong> <strong>unique</strong> <strong>labels</strong> for items that I upload with a CSV file. These items can grow to billions over time and they expire usually after 60/90 days. So the more records I add over the time I will be erasing them too. Deleting is important because I want to make sure that I give a label with minimum length.</p>
  <p>eg. Let's say I need to assign labels to following items.</p>
  <pre><code>department = 'A'
  items = [ 1 =&gt; foo, 2 =&gt; bar, 3 =&gt; nik, 4 =&gt; mik, 5 =&gt; baz, 6 =&gt; nik, 7 =&gt; baz, 8 =&gt; baz, 9 =&gt; baz, 10 =&gt; naz]
  
  department = 'B'
  items = [ 1 =&gt; foo, 2 =&gt; bar, 3 =&gt; nik, 4 =&gt; mik, 5 =&gt; baz, 6 =&gt; nik, 7 =&gt; baz, 8 =&gt; baz, 9 =&gt; baz, 10 =&gt; naz]
  </code></pre>
  <p>After inserting these records I need the output to be as following.</p>
  <p>departments</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>name</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>A</td>
  </tr>
  <tr>
  <td>2</td>
  <td>B</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>items</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>name</th>
  <th>label</th>
  <th style="text-align: center;">segment_value</th>
  <th style="text-align: center;">department_id</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>foo</td>
  <td>foo</td>
  <td style="text-align: center;">0</td>
  <td style="text-align: center;">1</td>
  </tr>
  <tr>
  <td>2</td>
  <td>bar</td>
  <td>bar</td>
  <td style="text-align: center;">0</td>
  <td style="text-align: center;">1</td>
  </tr>
  <tr>
  <td>3</td>
  <td>nik</td>
  <td>nik</td>
  <td style="text-align: center;">0</td>
  <td style="text-align: center;">1</td>
  </tr>
  <tr>
  <td>4</td>
  <td>mik</td>
  <td>mik</td>
  <td style="text-align: center;">0</td>
  <td style="text-align: center;">1</td>
  </tr>
  <tr>
  <td>5</td>
  <td>baz</td>
  <td>baz</td>
  <td style="text-align: center;">0</td>
  <td style="text-align: center;">1</td>
  </tr>
  <tr>
  <td>6</td>
  <td>nik</td>
  <td>nik1</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">1</td>
  </tr>
  <tr>
  <td>7</td>
  <td>baz</td>
  <td>baz1</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">1</td>
  </tr>
  <tr>
  <td>8</td>
  <td>baz</td>
  <td>baz2</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">1</td>
  </tr>
  <tr>
  <td>9</td>
  <td>baz</td>
  <td>baz3</td>
  <td style="text-align: center;">3</td>
  <td style="text-align: center;">1</td>
  </tr>
  <tr>
  <td>11</td>
  <td>foo</td>
  <td>foo</td>
  <td style="text-align: center;">0</td>
  <td style="text-align: center;">2</td>
  </tr>
  <tr>
  <td>12</td>
  <td>bar</td>
  <td>bar</td>
  <td style="text-align: center;">0</td>
  <td style="text-align: center;">2</td>
  </tr>
  <tr>
  <td>13</td>
  <td>nik</td>
  <td>nik</td>
  <td style="text-align: center;">0</td>
  <td style="text-align: center;">2</td>
  </tr>
  <tr>
  <td>14</td>
  <td>mik</td>
  <td>mik</td>
  <td style="text-align: center;">0</td>
  <td style="text-align: center;">2</td>
  </tr>
  <tr>
  <td>15</td>
  <td>baz</td>
  <td>baz</td>
  <td style="text-align: center;">0</td>
  <td style="text-align: center;">2</td>
  </tr>
  <tr>
  <td>16</td>
  <td>nik</td>
  <td>nik1</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">2</td>
  </tr>
  <tr>
  <td>17</td>
  <td>baz</td>
  <td>baz1</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">2</td>
  </tr>
  <tr>
  <td>18</td>
  <td>baz</td>
  <td>baz2</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">2</td>
  </tr>
  <tr>
  <td>19</td>
  <td>baz</td>
  <td>baz3</td>
  <td style="text-align: center;">3</td>
  <td style="text-align: center;">2</td>
  </tr>
  <tr>
  <td>20</td>
  <td>naz</td>
  <td>naz</td>
  <td style="text-align: center;">0</td>
  <td style="text-align: center;">2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The reason why I want the behaviour is because I need a way to reverse lookup an item with a param like this. <code>A/baz3</code> should be able to map item 9 on the <strong>items</strong> table.</p>
  <p><strong>Current Solution</strong></p>
  <p>Right now I have a trigger which updates the label after inserting the record. I maintain an external counter and for each insert the trigger will use that information to update the label.</p>
  <p><strong>Limitations/Current Issues</strong></p>
  <p>I am inserting these items 100,000 records or 200,000 records per batch. So I want an efficient insert. Also I want the labels to have a minimum numeric values (It's better if I can reuse <code>baz1</code> after it's erased in 60 days). Also my current trigger doesn't use a new <code>segment_value</code> for each <code>department_id</code></p>
  <pre><code>CREATE TRIGGER insert_items
              BEFORE INSERT ON items
                  FOR EACH ROW BEGIN
                     SET NEW.segment_value = ((SELECT IFNULL(MAX(segment_value), 0) from purl_links where segment_key = NEW.segment_key) + 1);
                     SET NEW.label = CONCAT(NEW.label,NEW.segment_value);
  
                  END
  </code></pre>
  <p>I am looking for a decent solution which should help me avoid collision and be able to insert in batch and produce a label with minimum amount of numerics possible (by using available old slots if possible).</p>
  <p><strong>PS : I tried to abstract out my problem in the best way possible and I am looking for any help that I can get.</strong></p>