mysql 自增长 前缀,如何使用某些前缀使MySQL表主键自动递增

I have table like this

table

id Varchar(45) NOT NULL AUTO_INCREMENT PRIMARY KEY,

name CHAR(30) NOT NULL,

I want to increment my id field like 'LHPL001','LHPL002','LHPL003'... etc.

What should I have to do for that? Please let me know any possible way.

解决方案

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