Menu
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.
274
One of the important tasks while creating a table is setting the Primary Key. The Auto Increment feature allows you to set the MySQL Auto Increment Primary Key field. This automatically generates a sequence of unique numbers whenever a new row of data is inserted into the table.
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
To change a primary key to auto_increment, you can use MODIFY command. Let us first create a table. Look at the above sample output, StudentId column has been changed to auto_increment.
In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name.
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
119
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
