i want to create a mysql trigger to create primary key as varchar autoincrement

Question

CREATE TABLE `user` (
  `user_id` varchar(16) NOT NULL,
  `person_id` varchar(16) NOT NULL,
  `org_id` varchar(16) NOT NULL,
  `email` varchar(32) NOT NULL,
  `login_id` varchar(10) NOT NULL,
  `password` varchar(32) NOT NULL,
  `mobile_no` varchar(12) NOT NULL,
  `android_id` varchar(16) NOT NULL,
  `activation_status` int(2) NOT NULL,
  `pin` varchar(6) NOT NULL,
  `role_id` varchar(16) NOT NULL,
  `imei` varchar(16) NOT NULL,
  `booth_id` varchar(16) NOT NULL,
  `Assignment_id` varchar(16) NOT NULL,
  `created_by` varchar(16) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `updated_by` varchar(16) DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Answer 1

(Borrowing from How to make MySQL table primary key auto increment with some prefix)

Assuming you already have your user table created, you can write a trigger to generate the id for you automatically. You'll need a dummy table to hold id's auto-generated by MySQL, allowing the trigger can easily pull new, unique ID's from.

The Dummy (Sequence) Table:

CREATE TABLE user_seq
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);

The Trigger:

DELIMITER $$
CREATE TRIGGER tg_user_insert
BEFORE INSERT ON user
FOR EACH ROW
BEGIN
  INSERT INTO user_seq VALUES (NULL);
  SET NEW.user_id = LAST_INSERT_ID();
END$$
DELIMITER ;

I used this to handle a case where I had to generate some seed data for an existing schema (e.g. I could not change the schema). Please note I'm using this in a dev environment - no idea how performant or otherwise production-worthy something like this would be. Hopefully a competent dba can provide more insight - but since the question stands, I had a similar issue, and couldn't find an alternative, hopefully this will help someone else...