How to make MySQL table primary key auto increment with some prefix

If you really need this you can achieve your goal with help of separate table for sequencing (if you don’t mind) and a trigger.

Tables

CREATE TABLE table1_seq
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE table1
(
  id VARCHAR(7) NOT NULL PRIMARY KEY DEFAULT '0', name VARCHAR(30)
);

Now the trigger

DELIMITER $$
CREATE TRIGGER tg_table1_insert
BEFORE INSERT ON table1
FOR EACH ROW
BEGIN
  INSERT INTO table1_seq VALUES (NULL);
  SET NEW.id = CONCAT('LHPL', LPAD(LAST_INSERT_ID(), 3, '0'));
END$$
DELIMITER ;

Then you just insert rows to table1

INSERT INTO Table1 (name) 
VALUES ('Jhon'), ('Mark');

And you’ll have

|      ID | NAME |
------------------
| LHPL001 | Jhon |
| LHPL002 | Mark |

Here is SQLFiddle demo