Asked  7 Months ago    Answers:  5   Viewed   112 times

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.

 Answers

65

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

Tuesday, June 1, 2021
 
petersaints
answered 7 Months ago
28

As documented under Using AUTO_INCREMENT:

For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

Therefore, you could do:

CREATE TABLE my_table (
  firstname VARCHAR(31) NOT NULL,
  lastname  VARCHAR(31) NOT NULL,
  counter   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (lastname, counter)
) Engine=MyISAM;

INSERT INTO my_table
  (firstname, lastname)
VALUES
  ('james', 'smith' ),
  ('terry', 'smith' ),
  ('john' , 'smith' ),
  ('jerry', 'fields'),
  ('tom'  , 'straus')
;

See it on sqlfiddle.

Wednesday, March 31, 2021
 
ajaybc
answered 9 Months ago
74

Insert id is a property of the MYSQLI object and not the MYSQLI result object:

$statement = $mysqli->query($insert);
echo $mysqli->insert_id; // correct
echo $statement->insert_id; //not correct

http://php.net/manual/en/mysqli.insert-id.php

Friday, May 28, 2021
 
cyber_truite
answered 7 Months ago
78

You can have an auto-Incrementing column that is not the PRIMARY KEY, as long as there is an index (key) on it:

CREATE TABLE members ( 
  id int(11)  UNSIGNED NOT NULL AUTO_INCREMENT,
  memberid VARCHAR( 30 ) NOT NULL , 
  `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , 
  firstname VARCHAR( 50 ) NULL , 
  lastname VARCHAR( 50 ) NULL , 
  PRIMARY KEY (memberid) ,
  KEY (id)                          --- or:    UNIQUE KEY (id)
) ENGINE = MYISAM; 
Sunday, June 13, 2021
 
mario
answered 6 Months ago
84

In InnoDB, the autoincrement value is not part of the table's metadata and is reset on each server restart.

InnoDB uses the following algorithm to initialize the auto-increment counter for a table t that contains an AUTO_INCREMENT column named ai_col: After a server startup, for the first insert into a table t, InnoDB executes the equivalent of this statement:

SELECT MAX(ai_col) FROM t FOR UPDATE

; InnoDB increments the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. By default, the value is incremented by one. This default can be overridden by the auto_increment_increment configuration setting.

If the table is empty, InnoDB uses the value 1. This default can be overridden by the auto_increment_offset configuration setting.

During the server executable lifetime, it's stored in a special structure but not persisted between server restarts:

If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk.

The variable you're setting defines the way this structure is accessed concurrently by different sessions, not its lifetime.

If you want to persist an auto-increment value between the server restarts, you should save it in a table not subject to transaction control (like MyISAM or Archive) on each write and reset it from the table on server restart.

Monday, November 8, 2021
 
devo
answered 4 Weeks ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share