MySQL – How to Preserve Autoincrement ID

auto-incrementdatabase-designMySQL

I am working on an e-commerce webapp and here's what I am thinking about.

To make it simple in this question, the user table contains two columns:id (int,PK,NN,AI),name(varchar(45),NN). I want the employees have shorter ids, so I want to preserve short ids and assign them later. The id is divided in three categories.

  1. 1-1000 preserved for employees.
  2. 1001-5000 preserved for partners.
  3. 5001+ for users.

So is there a way I can manipulate the autoincrement id? My currently walkthrough is: fires create garbage records to fill up the ID, then allocate them for employers,partners.

Best Answer

To change the AUTO_INCREMENT value you would have to make the next query:

mysql> ALTER TABLE user AUTO_INCREMENT=next_id;

Where you would have to replace next_id with the value that is going to be set when you omit the id in the next insert statement.

Yet the property AUTO_INCREMENT value doesn't work like you want, at least not alone, you would have to search each time which is the next value you are looking, doing a sql query in the 3 cases you said.

For example, for the case you are looking for an employee next id, you would have to make the next query:

mysql> SELECT MAX(id) AS max_given_id FROM user WHERE id >= 1 AND id < 1000;

And then do a plus 1 to that value to get the id you are setting in your INSERT with out using AUTO_INCREMENT property, or make the first given statement to alter the table AUTO_INCREMENT value and then omit the id in the insert.

One approach to do this is by doing the procedure I just explained with stored procedures:

mysql> DELIMITER //
mysql>
mysql> DROP PROCEDURE IF EXISTS InsertUserByType;
    -> /*first we try to erase the procedure if it is set, so we dont redeclare*/
    ->
    -> CREATE PROCEDURE InsertUserByType(IN pname VARCHAR(45), IN type VARCHAR(45))
    -> BEGIN
    ->
    ->     /*this is the declaration of the variable where we are storing the next value to set in the insert*/
    ->     DECLARE next_id INT DEFAULT 0;
    ->
    ->     /*we do the first case where we are inserting an employee*/
    ->     IF (type = 'employe') THEN
    ->         /*we look for the max value in the range for the given case*/
    ->         SELECT MAX(id) INTO next_id FROM user WHERE id >= 1 AND id < 1000;
    ->         IF (next_id IS NULL) THEN
    ->             /*if the max value doesnt exists we set it to the first value for this case*/
    ->             SET next_id = 1;
    ->         ELSE
    ->             SET next_id = next_id + 1;
    ->         END IF;
    ->     ELSE
    ->         /*we do the second case where we are inserting a partner*/
    ->         IF (type = 'partner') THEN
    ->             /*we look for the max value in the range for the given case*/
    ->             SELECT MAX(id) INTO next_id FROM user WHERE id >= 1001 AND id < 5000;
    ->             IF (next_id IS NULL) THEN
    ->                 /*if the max value doesnt exists we set it to the first value for this case*/
    ->                 SET next_id = 1001;
    ->             ELSE
    ->                 SET next_id = next_id + 1;
    ->             END IF;
    ->         ELSE
    ->             /*we do the third case where we are inserting an user*/
    ->             IF (type = 'user') THEN
    ->                 /*we look for the max value in the range for the given case*/
    ->                 SELECT MAX(id) INTO next_id FROM user WHERE id >= 5001;
    ->                 IF (next_id IS NULL) THEN
    ->                     /*if the max value doesnt exists we set it to the first value for this case*/
    ->                     SET next_id = 5001;
    ->                 ELSE
    ->                     SET next_id = next_id + 1;
    ->                 END IF;
    ->             END IF;
    ->         END IF;
    ->     END IF;
    ->
    ->     /*we make our insert with the recolected value and the name of the user given as a param*/
    ->     INSERT INTO user (id, name) VALUES (next_id, pname);
    ->
    -> END//
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql>

And every time you want to insert a user then write the next statement:

mysql> CALL InsertUserByType("User Name", "partner");
Query OK, 1 row affected (0.01 sec)
Related Question