I have two tables one of suppliers and another of products
I want the ID of each product to contain 6 digits,
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
supplier that his ID is 158
The ID of each product that belongs to this supplier will read as follows:
And so on
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)