Specific String auto increment in mysql

Question

I want to create a unique id for table Emp in MySQL like "Emp_0416_1", "Emp_0416_2" and "Emp_0516_1", "Emp_0516_2".

Here

 Emp is stand for Employee,
 04 is April month,
 16 is year,
 1 is emp code.

And

 Emp is stand for Employee,
 05 is May month,
 16 is year,
 1 is emp code.

Table is :

      create table emp (
          id varchar(20),
          name varchar(20)
      );

When I insert the emp name then it will auto increment like above unique id.

Have a look at http://stackoverflow.com/questions/17246078/how-do-i-create-an-index-from-a-stored-procedure-or-create-index-on-every-table and http://stackoverflow.com/questions/17242597/how-do-i-create-an-index-inside-a-stored-procedure . Possible duplicate. — Chloe, Apr 12 '16 at 07:09
Maybe you need to re-word the question, but I thought you want different names for tables using a stored procedure. If you want strings for the `id` that you create, I would say you should design the database differently and create the table like `create table emp ( id integer, month integer, year integer, employee_id integer, name varchar(20) );` and select from the table with `select *, concat('Emp_', month, year, '_', employee_id) as my_id from emp;` — Chloe, Apr 12 '16 at 07:16
You will want to use triggers in that case. Or else you can set the id from the java program when inserting — Imesha Sudasingha, Apr 12 '16 at 07:17

Ankit Agrawal · Answer 1 · 2016-04-12T07:58:26.837

here is your desired solution

      CREATE PROCEDURE `proc_name`(in emp_code int,in emp_name varchar(256),in_date date)
        BEGIN
                declare temp_date varchar(256);
                set temp_date=  (select concat(
                                                 if(length(month(in_date))>1,month(in_date),concat(0,month(in_date))),right(year(in_date),2)
                                              ) as dateyear
                                 );

                INSERT into table_name(id,`name`) 
                select concat('emp_',temp_date,'_',emp_code)as emp_id,emp_name;



        END

call procedure

 call proc_name(2,'batman','2016-04-11')

Specific String auto increment in mysql

1 Answers1