How to Use Auto Increment in Varchar data Type in PHP an MYSQL?

Question

can anyone tell us how to use auto increment in varchar data type?

i have look for other question and people always ask to use interger or use trigger. but however this is my college's project that has a rule for us to use varchar.

---

Automatically generated based on last Product ID existing in the database Ex. If the latest Product ID is ‘PR004’, then the new id will be ‘PR005’

so, we must set auto increment in php right?can someone tell me how to use this?

thank you

Answer 1

MYSQL 5.7.5:

You can accomplish the goal using Generated Columns and this.

Not tested

CREATE TABLE TEST ( 
     ProdID VARCHAR(20) AS CONCAT('PR','',PID) STORED,
    PID INT auto_increment)

For your convenience,

MSSQL:

The easiest method is to simply make a calculated column. It uses what is known as a "calculated" column in conjunction with an IDENTITY column.

    CREATE TABLE Test
             (
              ProdID AS CAST('PR'+RIGHT('000'+CAST(RowNum AS VARCHAR(3)),3) AS VARCHAR(30)) PERSISTED
             ,ProdName VARCHAR(30)
,RowNum INT IDENTITY(1,1) 
             );


  INSERT INTO Test (ProdName)
  SELECT 'Thread' UNION ALL
  SELECT 'Button Thread' UNION ALL
  SELECT 'Coat Thread';

  SELECT ProdID, ProdName FROM Test;

That returns the following (notice that the auto-increment worked):

 ProdID                         ProdName
 ------------------------------ ------------------------------
 PR001                          Thread
 PR002                          Button Thread
 PR003                          Coat Thread