Auto increment MySQL column with custom value

Auto increment MySQL column with custom value

Posted date 2023-06-06

In this blog post, we will discuss how to implement auto increment with custom value in MySQL. Auto increment is a feature of MySQL that allows you to automatically generate unique values for your database rows. This can be useful for keeping track of records in your database, as you don’t have to worry about manually assigning values.

However, sometimes you may want to use a custom value for your auto increment column. This could be for a number of reasons, such as if you want to use a sequential number for your records, or if you want to use a unique identifier that is generated by your application.

In this blog post, we will show you how to implement auto increment with custom value in MySQL. We will also discuss the benefits of using custom values for your auto increment column.

Here are some of the benefits of using custom values for your auto increment column:

  • Uniqueness: Custom values are guaranteed to be unique, which can be helpful for keeping track of records in your database.
  • Efficiency: Using custom values can help to improve the efficiency of your database queries.
  • Flexibility: Custom values give you more flexibility in how you manage your database records.

Initially I did custom incement key to FrontEnd application …, yes I build offline application using only FrontEnd application and I stored all data of it appliaction in indexeddb, and also of course I have made a mistake.

I use library localforage to connect to indexeddb and manipulate data in it, all of manipulation data in indexeddb only using one file as the backbone, and for every adding new data will be given custom increment id which must be unique, the code that generates custom increment id is below :

export function generateId(lastId) {
    // get only identity e.g SU_22050000 become only SUP
    let id = lastId.slice(0, -8);
    
    let increment = Number(lastId.slice(-4)) + 1 + "";
    // 2022
    let fullYear = new Date().getFullYear() + "";
    // 5
    let weekNow = getWeekNumber();
    // 22
    let year = lastId.slice(id.length, id.length+2); //21
    // 05
    let week = lastId.slice(id.length + 2, id.length+4); //08
    //if the week same
    if (weekNow === Number(week) && Number(year) === Number(fullYear.slice(-2))) {
      id += year + week;
    }
    //if the week not same
    else {
      // if the week 9 change to 09
      weekNow = weekNow < 9 ? "0" + weekNow : weekNow;
      id += fullYear.slice(-2) + weekNow;
      increment = "0";
    }
    //0000
    let result = id + "0000".slice(increment.length) + increment;
  
    return result;
  }
  
  function getWeekNumber() {
    // get today
    let currentdate = new Date();
    // get the 1 january day
    var oneJan = new Date(currentdate.getFullYear(), 0, 1);
    // get the number of today (currentdate - oneJan) would be epoch number and divide 1 day epoch number
    var numberOfDays = Math.floor((currentdate - oneJan) / (24 * 60 * 60 * 1000));
    // get the number of day + 1 + number of days and divide 1 week ( 170 / 7)
    return Math.ceil((currentdate.getDay() + 1 + numberOfDays) / 7);
  }

As seen above, when we execute the function generateId(SU_23060000) we will get result SU_23060001, approxiately will run like this:

  • generateId(SU_23060000) => SU_23060001
  • generateId(SU_23060001) => SU_23060002
  • generateId(SU_23060002) => SU_23060003
  • generateId(SU_23060003) => SU_23060004
  • generateId(SU_23060004) => SU_23060005
  • etc

The details of the results above are as follows:

  • SU_ = name of table or name of category or whatever you want.
  • 23 = year
  • 06 = week
  • 0001 = uniquee increment

I used the code above without any unit testing, I thought everything will be okey, but when the application running around a few month, and the getWeekNumber function yields the number 9, then the application is broken, because the generateId function goes like the following:

  • generateId(SU_23080901) => SU_2390000
  • generateId(SU_2390000) => SU2390000
  • generateId(SU2390000) => S2390000
  • generateId(2390000) => 2390000
  • generateId(2390000) => 2390000
  • and all next id would be 2390000

All items made on that day given id 2390000 and of course the new record will be overwrite the old record that has the same id, so that as if the application only create 1 item although the application create items so much.

After being checked, the error was caused on line of code weekNow = weekNow < 9 ? “0” + weekNow : weekNow;, yes you are right, I should have used < 10 on that, I fixed that code and create unit testing by running generateId(SU_23060000) with a custom time in full 12 month, here is the code I have fixed:

generateId.js


function getWeekNumber(yourDate) {
    // get today
    let currentdate = new Date(yourDate);
    // get the 1 january day
    var oneJan = new Date(currentdate.getFullYear(), 0, 1);
    // get the number of today (currentdate - oneJan) would be epoch number and divide 1 day epoch number
    var numberOfDays = Math.floor((currentdate.getTime() - oneJan.getTime()) / (24 * 60 * 60 * 1000));
    // get the number of day + 1 + number of days and divide 1 week ( 170 / 7)
    return Math.ceil((currentdate.getDay() + 1 + numberOfDays) / 7);
  }

// this function will be used for any request in appliaction 
export function generateId(yourLastId) {
  const dateNow = new Date();
  const nextId = generateIdCutomDate(dateNow, yourLastId)
  return nextId;
}

// this function will be used for unit testing;
export function generateIdCutomDate(yourDate, yourLastId) {

    let id = yourLastId.substr(0, yourLastId.length -8);
    // add increment
    // get 4 string e.g 0000 would be 0001
    let increment = Number(yourLastId.slice(-4)) + 1 + "";
    // 2022
    let fullYear = new Date(yourDate).getFullYear() + "";
    let yearNow = fullYear.slice(2);
    // 5
    let weekNow = getWeekNumber(yourDate) + '';
    // 22
    let year = yourLastId.slice(id.length, id.length + 2); //21
    // 05
    let week = yourLastId.slice(id.length + 2, id.length + 4); //08
    //if the week same
    if (weekNow == week && year == yearNow) {
      id = id + yearNow + week;
    }
    //if the week not same
    else {
      // if the week 9 change to 09
      weekNow = Number(weekNow) < 10 ? "0" + weekNow : weekNow;
      id = id + yearNow + weekNow;
      increment = "0";
    }
    //0000
    let result = id + "0000".slice(increment.length) + increment;
    
    return result;
}

generateId.spec.js The unit testing code

import { generateIdCutomDate } from "../utils/generatorId";

import { describe, it, expect } from 'vitest'

describe("Next id must be oke", () => {

    
    it('Must be oke bro, understand?', async () => {
        const year = 2023;
        const startDate = new Date(`${year}-01-02`);
        const endDate = new Date(`${year}-12-25`);

        let currentdate = startDate;
        let week = 1;
        let idName = "SUPER_"
        let yearId = year.toString().slice(2);

        while(currentdate <= endDate) {
            let nextId = generateIdCutomDate(currentdate, idName +"22110000");

            let weekId = week < 10 ? "0" + week : week;
            let expectId = idName + yearId + weekId + "0000"
            expect(expectId).equal(nextId);

            let currentId = nextId;

            for(let i =1; i < 10; i++) {
                let nextId2 = generateIdCutomDate(currentdate, currentId);
                let nextExpectId2 = idName + yearId + weekId + "000" + i;
                expect(nextExpectId2).equal(nextId2);
                currentId = nextId2;
            }

            currentdate.setDate(currentdate.getDate() + 7);
            week++
        }


    })

}, 100000)

And then the application running normally, and I can sleep peacefully.

Because javascript is a single thread and non-blocking programing language and the user of application only me, so I can manage the process of the application goes alternately, and then a few months later I try to build the BackEnd for the application, and of course we will face another problem :).

The BackEnd that we will build uses the php language and mysql as database, because we already have the tested code in javacript language, so we just need to translate it to php languange:

generator_id.php

function generateId($lastId) {
    $date = date("Y-m-d");
    return generateIdWithCustomDate($lastId, $date);
}

function generateIdWithCustomDate($lastId, $yourDate)
{
    $yourDate2 =  date_create($yourDate);
    // get uniquee id, delete the 8 last string, SUPERVISOR_22030001 become SUPERVISOR_
    $baseId = substr($lastId, 0, -8);
    // get uniquee number, the last 4 string, war22050000 become 0000
    $getNumber = substr($lastId, -4);
    // increment uniquee number by 1
    $increment = strval(floatval($getNumber) + 1);
    // full year
    $fullYearNow = $yourDate2->format("Y") . "";
    $yearNow = substr($fullYearNow, 2, 2);
    // week now
    $weekNow = $yourDate2->format("W");
    // year of last id
    $yearLastId = substr($lastId, strlen($baseId), 2); //22
    // week of last id
    $weekLastId = substr($lastId, (strlen($baseId) + 2), 2); //08
    
    if ($weekNow == $weekLastId && $yearNow == $yearLastId) {
        return $baseId . $yearLastId . $weekNow . substr("0000", strlen($increment)) . $increment;
    }
    
    return $baseId . $yearNow . $weekNow . "0000";
}

generator_id_Test.php The unit testing code

use PHPUnit\Framework\TestCase;
// Function to TEST.
require_once( __DIR__. '/../utils/generator_id.php');

// Class to run Testing.
class SimpleTest extends PHPUnit_Framework_TestCase
{
    public function testGeneratorId()
    {
        $year = 2023; // replace with the year you want to generate
        $start_date = new DateTime("$year-01-02");
        $end_date = new DateTime("$year-03-31");

        $current_date = $start_date;
        $week = 1;
        while ($current_date <= $end_date) {
            // echo $current_date->format("Y-m-d") . "<br>";
            $TestSentence = generateIdWithCustomDate("SUPER_22110000", $current_date->format("Y-m-d"));
            
            $weekId = $week < 10 ? "0". $week : $week;
            $expect = "SUPER_23". $weekId ."0000";
            // expect record
            $this->assertEquals($expect, $TestSentence);

            for($x = 1; $x < 10; $x++) {
                $currentId = "SUPER_23" . $weekId . "000" . ($x - 1);
                $nextId = generateIdWithCustomDate($currentId, $current_date->format("Y-m-d"));
                $nextIdExpect = "SUPER_23" . $weekId . "000" . $x;
                $this->assertEquals($nextIdExpect, $nextId);
            }

            $current_date->modify("+7 day");
            $week = $week + 1;
        }
            
    }
}

The unit test for generateId function was successful, and the unit test for create a record too, but something went wrong when to make multiple requests to the server at the same time, the server returns an error message Duplicate entry ‘WAREHOUSE_23060012’ for key ‘id’.

The model layer for inserting a new record into the database is as follows :

Warehouse_model.php

// the file that contain script to manipulate database
require_once(__DIR__ . '/../../../utils/database.php');
// the file that contain summary of table of database, in this script we store lastId and total record that created
require_once(__DIR__ . '/../../../utils/summary_db.php');

class My_report_warehouse_model
{
    protected $database;
    var $table = "my_report_warehouse";
    var $columns = "id, warehouse_name, warehouse_group, warehouse_supervisors";
    var $is_success = true;
    private $summary = null;

    function __construct()
    {
      // initiate new query builder using static method
        $this->database = Query_builder::getInstance();
        // initiate new summary db using static method
        $this->summary = SummaryDatabase::getInstance($this->table);
    }

    // the code to retrieve all data in table
    public function get_warehouses()
    {
        $result  = $this->database->select_from($this->table)->fetchAll(PDO::FETCH_ASSOC);
        
        if($this->database->error !== null) {
            $this->is_success = $this->database->error;
        }
        else {
            return $result;
        }
    }

    public function append_warehouse($warehouse_name, $warehouse_group, $warehouse_supervisors)
    {
        // script that run generator id
        $nextId = $this->summary->getNextId();
        // write to database
        $this->write_warehouse($nextId, $warehouse_name, $warehouse_group, $warehouse_supervisors);

        if($this->database->error !== null) {

            $this->is_success = $this->database->error;

        } else {

            return $nextId;

        }

    }

    public function get_warehouse_by_id($id)
    {

        $result = $this->database->select_where($this->table, 'id', $id)->fetchAll(PDO::FETCH_ASSOC);
        
        if($this->database->error !== null) {
            $this->is_success = $this->database->error;
            return array();
        } else {
            return $result;
        }

    }

    public function update_warehouse_by_id(array $data, $where, $id)
    {

        $result = $this->database->update($this->table, $data, $where, $id);

        if($this->database->error !== null) {
            $this->is_success = $this->database->error;
        } else {
            return $result;
        }

    }

    public function write_warehouse($id, $warehouse_name, $warehouse_group, $warehouse_supervisors)
    {
        $data = array(
            "id" => $id,
            'warehouse_name' => $warehouse_name,
            'warehouse_group' => $warehouse_group,
            'warehouse_supervisors' => $warehouse_supervisors
        );

        $this->database->insert($this->table, $data);

        if($this->database->error !== null) {
            $this->is_success = $this->database->error;
        } else {
            $this->summary->updateLastId($id);
            return $id;
        }

    }

    public function last_id()
    {
        return $this->summary->getLastId();
    }
}

Warehouse_test.php


require_once(__DIR__ . '/../httpCall.php');
require_once(__DIR__ . '/../../vendor/fakerphp/faker/src/autoload.php');

class MyReportWarehousesTest extends PHPUnit_Framework_TestCase
{
    private $url = "http://localhost/rest-php/myreport/";
    private $url_host_id = null;

    public function testPostEndpoint()
    {
        $faker = Faker\Factory::create();
        $http = new HttpCall($this->url . "warehouse");
        // Define the request body
        $data = array(
            'warehouse_name' => $faker->firstName('female'),
            'warehouse_group' => $faker->firstName('female'),
            'warehouse_supervisors' => $faker->firstName('female')
        );

        // implement the request body
        $http->setData($data);
        $http->addJWTToken();
        $response = $http->getResponse("POST");

        $convertToAssocArray = json_decode($response, true);
        // Verify that the response same as expected
        $this->assertArrayHasKey('success', $convertToAssocArray);
        $this->assertArrayHasKey('id', $convertToAssocArray, $response);
        $this->assertEquals($convertToAssocArray['success'], true);
        $this->url_host_id = $this->url . 'warehouse/' . $convertToAssocArray['id'];
    }
}

When we run 1 unit testing process, only 1 request is received by the server to create a new record, while create new record, the application runs as below:

| Process                                 | Result Request 1             |
| --------------------------------------- | ---------------------------- |
| Get last id from db                     | WAREHOUSE_23060012           |
| Get next id                             | WAREHOUSE_23060013           |
| Write record to database(success\error) | Show message(succecss)       |
| Update last id                          | Last id = WAREHOUSE_23060013 |

No matter how many times we run the unit test, we will not see any error message, because the server only receive 1 request.

I never figured out how to do a stress test on a rest server, but I’d love to do it, I try to run 4 unit test process simultaneously, and of course we got an error message, the flow process while server receive 4 requests simultaneously as below:

| Process                             | Result Request 1             | Result Request 2                  | Result Request 3                  | Result Request 4                  |
| ----------------------------------- | ---------------------------- | --------------------------------- | --------------------------------- | --------------------------------- |
| Get last id from db                 | WAREHOUSE_23060012           | WAREHOUSE_23060012                | WAREHOUSE_23060012                | WAREHOUSE_23060012                |
| Get next id                         | WAREHOUSE_23060013           | WAREHOUSE_23060013                | WAREHOUSE_23060013                | WAREHOUSE_23060013                |
| Write database(success\error)       | Show message(succecss)       | Show message(Error Duplicate key) | Show message(Error Duplicate key) | Show message(Error Duplicate key) |
| Update last id                      | Last id = WAREHOUSE_23060013 | Process not executed              | Process not executed              | Process not executed              |

As you can see above, server failure caused duplicate key while write data on primary key column, unlike FrontEnd application before, we have complete control over the process being so we can waiting the write data process until finished and then continue the next write process, otherwise the php server running processes concurrently.

I explored to find a solution to the above problem, I foundn that we can embed a trigger function in mysql database for a specific purpose, in this case, we’ll run the trigger before the application inserts data into the table.

First, we need to create a new table with an auto increment column as prefix:

CREATE TABLE warehouse_prefix
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);

Second, we need to create a new trigger function so before mysql inserts new data into warehouse table, mysql will enter new data into warehouse_prefix table in advance, and then use the last inserted id as prefix increment:

DELIMITER $$

CREATE TRIGGER tg_warehouse_insert

BEFORE INSERT ON warehouse

FOR EACH ROW

BEGIN

INSERT INTO warehouse_prefix VALUES (NULL);

SET NEW.id = CONCAT('WRH', LPAD(LAST_INSERT_ID(), 4, '0'));

END$$

DELIMITER ;

From the code above, we create a new trigger with a name tg_warehouse_insert which will be executed before insert new record on table warehouse , thing todo every add new reocrd is to add new record on table warehouse_prefix, if LAST_INSERT_ID on table warehouse_prefix is 1, so the id to be entered on table warehouse is “WRH0001”.

However, we have the next problem, when the records have reached 1000, then the next id to be entered into table warehouse is WRH0001, which will be cause error #1062 - Duplicate entry ‘WRH0001’ for key ‘PRIMARY’.

To sovle the issue, we need to embed a new prefix in to the id, e.g YEAR and or MONTH instead of we only embed table name (WHR) and the uniquee id (0001), so the custom primary key would be WRH23070001, WRH the table name, 23 year created, 07 month created, and 0001 the uniquee number, we need to modify the trigger to be:

DELIMITER $$

CREATE TRIGGER tg_warehouse_insert

BEFORE INSERT ON warehouse

FOR EACH ROW

BEGIN

INSERT INTO warehouse_prefix VALUES (NULL);

SET NEW.id = CONCAT('WRH', RIGHT(YEAR(CURRENT_DATE), 2), LPAD(MONTH(CURRENT_DATE), 2, '0'), LPAD(LAST_INSERT_ID(), 4, '0'));

END$$

DELIMITER ;

To avoid error duplicate key and or to make the increment id reset to 1 every month, we need to create new trigger function to reset the auto increment to 1 by emptying the table warehouse_prefix as below:

DELIMITER $$
CREATE TRIGGER truncate_table_on_first_month
AFTER INSERT ON warehouse_prefix
FOR EACH ROW
BEGIN
  IF DAYOFMONTH(CURRENT_DATE()) = 1 THEN
    TRUNCATE TABLE warehouse_prefix;
  END IF;
END;
$$
DELIMITER ;

It would be better if we emptying table warehouse_prefix manually in our code, because the trigger function above will execute every 1st day of every month, if we don’t add new data on the first date, then the auto increment id will continue without emptying warehouse_prefix.

Here are some of the pros and cons of using a custom primary key with a prefix in MySQL instead of auto increment or UUID:

Pros:

  • More descriptive: A custom primary key with a prefix can be more descriptive than an auto-incrementing number or a UUID. This can make it easier to identify and track records in the database.
  • More flexible: A custom primary key with a prefix can be more flexible than an auto-incrementing number or a UUID. For example, you can use a prefix to represent the type of record, the date the record was created.

Cons:

  • More complex: Creating and managing a custom primary key with a prefix can be more complex than using an auto-incrementing number or a UUID.
  • Less portable: A custom primary key with a prefix may be less portable than an auto-incrementing number or a UUID. This is because a prefix is specific to a particular database schema.
  • Less secure: A custom primary key with a prefix can be less secure than a UUID. This is because a prefix can be more easy to guess than a random number or a UUID.

Conclusion:

Whether or not to use a custom primary key with a prefix in MySQL depends on your specific needs. If you need a primary key that is more descriptive, flexible, then a custom primary key with a prefix may be a good option. However, if you need a primary key that is simple to create and manage, then an auto-incrementing number or a UUID may be a better choice.

Here are some additional considerations when deciding whether or not to use a custom primary key with a prefix:

  • The size of your database: If you have a large database, then a custom primary key with a prefix may take up more space than an auto-incrementing number or a UUID.
  • The frequency of data changes: If you have a database that is frequently updated, then a custom primary key with a prefix may be more difficult to manage than an auto-incrementing number or a UUID.
  • The compatibility of your database with other systems: If you need to share your database with other systems, then you will need to make sure that the other systems can understand the custom primary key with a prefix.

https://www.mysqltutorial.org/create-the-first-trigger-in-mysql.aspx

https://www.w3schools.com/sql/func_mysql_week.asp

https://stackoverflow.com/questions/17893988/how-to-make-mysql-table-primary-key-auto-increment-with-some-prefix