MySQL question: How to insert unique non-integer employee ids?

I want to insert data into a table like the following:

emp_id          emp_data
----------------------------
emp001          Amit
emp002          Ajit
emp003          Arijit

Over here all the emp_id data should be unique but should follow the pat开发者_运维百科tern of emp***. Please help. I am a novice in db.

create table employee (
  empnum varchar(10) primary key
, empname varchar(60)
);

insert into employee values ('emp001', 'Employee 1');
insert into employee values ('emp002', 'Employee 2');
insert into employee values ('100emp', 'Employee 3');

once i asked similar question, but later i realized that , this is not good idea to add prefix/suffix with auto increment field

better u change your table structure

emp_id    emp_uniq_id    emp_data
----------------------------------
1         emp001          Amit
2         emp002          Ajit
3         emp003          Arijit

and u can use below query

mysql_query("UPDATE table_name SET emp_uniq_id=CONCAT('emp', ".str_pad(emp_id,3,'0',STR_PAD_LEFT).") )";

If the emp prefix is going to remain constant it makes more sense to store it your application logic and prepending it when fetching columns from the database.

Either way, you're only going to be able to AUTO_INCREMENT numeric columns.

That having been said, you can group your primary key like so:

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

The output of the select would be:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

Cribbed from the online docs: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html