title: How to generate incremental unique id on MySQL with existing values?
body: <p>I have a table with data which has 3 rows:</p>
  
  <p><a href="https://i.stack.imgur.com/LfcDy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/LfcDy.png" alt="enter image description here"></a></p>
  
  <p>"id" is primary key and auto increment.
  order_no is NOT unique index because there may be many company_ids with the same order_no.</p>
  
  <p>I can generate and show user next order_no with this query:</p>
  
  <pre><code>select LPAD(count(order_no)+1, 4, '0')  from customers_orders where company_id=28
  </code></pre>
  
  <p>which gives me "0004". It's OK. But the user can also enter id manually and the data is like this somethimes:</p>
  
  <p><a href="https://i.stack.imgur.com/lssZp.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/lssZp.png" alt="enter image description here"></a></p>
  
  <p>Now it doesn't start with 0001 and the generate query gives me "0003". But I already have "0003" in there. The query should return like "0005". </p>
  
  <p>If I get the latest order_no and add it 1 (for example max(order_no)+1), it won't work. Because the data can be also like this:</p>
  
  <p><a href="https://i.stack.imgur.com/5iypB.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5iypB.png" alt="enter image description here"></a></p>
  
  <p>If the data is like this; the query have to generate "0003".</p>
  
  <p>I think I can make this with while loop in PHP, but i want to do this with MySQL if it is possible. Thank you.</p>