auto increment in Mysql database table with my own choice

Question

I have following table named oder in mysql database

         id    user_id   item_id 
         1       55      5813    
         2       55      359     
         3       56      559  
         4       62      4536    
         5       62      484     
         6       99      4698    
         7       29      435

In this table "id" is primary key and auto increment on this column is on. Now I want to add a column, it should also be auto incremented and value are like OD-001 , OD-002. Then my table should look like

         id    user_id   item_id  order_custom_id
         1       55      5813     OD-001
         2       55      359      OD-002
         3       56      559      OD-003
         4       62      4536     OD-004
         5       62      484      0D-005
         6       99      4698     OD-006
         7       29      435      OD-007

Note: I am currently working on the PHP Laravel framework. I have go through many questions and articles but nothing worked out for me,

Add an insert trigger that builds the ID for every inserted record — juergen d, Jun 16 '17 at 09:59
in your mysql server, because trigger will run on every insert operation of that table. — Shaunak Shukla, Jun 16 '17 at 10:02
Possible duplicate of [MySQL Auto Increment Custom Values](https://stackoverflow.com/questions/5228408/mysql-auto-increment-custom-values) — Laurenz Albe, Jun 16 '17 at 13:28
You can generate custom auto-increment values using stored procedures and a function which always returns the last stored value for custom auto increment field, as described in [Custom Auto Increment Field](http://en.latindevelopers.com/ivancp/2012/custom-auto-increment-values/) — RAUSHAN KUMAR, Jun 16 '17 at 10:08

score 1 · Answer 1 · answered Jun 16 '17 at 10:16

If want to do this by php and you have this table like a Model and your order_custom_id always end as your id you can use laravel events and create it by getting the last id of your table + 1.

public static function boot()
{
    parent::boot(); // Validate the model
    static::creating(function ($your_model) {
        $prev_model = YourModel::orderBy('id', 'desc')->first();
        $id = ($prev_model) ? ($prev_model->id +1) : 1;
        $your_model->order_custom_id = sprintf('OD-%s', str_pad($id, 3, "0", STR_PAD_LEFT));
    });
}

score 1 · Answer 2 · answered Jun 17 '17 at 11:25

If I understand correctly, you want to generate column values based on the auto-increment column values (e.g. 1 -> OD-001).

The first idea that popped into my mind was to use auto computed columns. Unfortunately, MySQL does not seem to support this feature. (it is related to how the auto-incremented and auto computed column values are generated internally):

Generated column 'computed_col' cannot refer to auto-increment column.

Another way is to use a trigger to update another column on insert, but personally I avoid triggers if possible, so using a view can be a decent solution:

CREATE VIEW orderView AS
SELECT id, user_id, item_id, CONCAT('OD-', LPAD(id, 3, '0')))
FROM order

The only disadvantage I see is that values are not persisted and are computed on the fly (more CPU). However, this should work just fine for a fairly small amount of selected rows (< thousands).

NOTE: using a view instead of a separate column also obeys 3NF

score 0 · Answer 3 · answered Jun 16 '17 at 10:34

You could try something like:

INSERT INTO table (user_id, item_id)
VALUES (20, CONCAT('OD-', LPAD((SELECT ISNULL(MAX(id) + 1, 1) FROM table), 3, '0')));

This will select the next incremental id from your table, and prepend that to OD- with leading zeros.

auto increment in Mysql database table with my own choice

3 Answers3