[SOLVED] Column population and auto_increment

Issue

I have two tables one of suppliers and another of products

I want the ID of each product to contain 6 digits,
plus
That the ID of each product will start with the ID digits of its supplier,
And that the last digits will be auto_increment with zeros appearing in between
For example:

supplier that his ID is 158
The ID of each product that belongs to this supplier will read as follows:

158000001,
158000002,
158000003
And so on

Solution

I think you should use StoredProcedure for this. You can use LPAD function for that. Suppose if you use LAPD(17,3,0) & LAPD(6,3,0), IT WILL RETURN 017 & 006 simultaneously. I would prefer StoredProcedure for this because, you have separate tables. If not, Trigger is more suitable.

You can check https://database.guide/how-to-add-leading-zeros-to-a-number-in-mysql/ for this

DROP PROCEDURE IF EXISTS  `SpProductID` ;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `SpProductID`(
    V_SUP_ID    CHAR(3)
    )
BEGIN

# call SpProductID('158');

DECLARE V_PROD_NO   INT;
DECLARE V_PROD_ID   CHAR(9);

SELECT  IFNULL(MAX(CAST(RIGHT(PRODUCT_ID,6) AS INT)),0)+1
FROM    PRODUCT
WHERE   LEFT(SUP_ID,3)=V_SUP_ID INTO    V_PROD_NO;

SET V_PROD_ID=CONCAT(V_SUP_ID,LPAD(V_PROD_NO,6,0));

#   now insert it in product table


    END $$
DELIMITER ;

When supplier id is 158 & product_no is 1, then product_id will be 158000001.

Answered By – Deep

Answer Checked By – Clifford M. (BugsFixing Volunteer)