5

Is there a way to set primary key auto increment, type of varchar in mySql?

John Woo
  • 258,903
  • 69
  • 498
  • 492
Ye Wint
  • 849
  • 3
  • 13
  • 20
  • 2
    What do you mean by auto-incrementing a varchar? – Sayan Dec 26 '12 at 09:36
  • An auto increment field should be `INT` – Shiridish Dec 26 '12 at 09:37
  • Possible duplicate of http://stackoverflow.com/questions/3455557/mysql-how-to-use-varchar-as-auto-increment-primary-key – Shiridish Dec 26 '12 at 09:37
  • Check this [possible duplicate](http://stackoverflow.com/questions/4699591/how-to-autoincrement-varchar) question in Stackoverflow. – Zeina Dec 26 '12 at 09:41
  • Maybe you better look this link anyway, that a same question telling about.. http://stackoverflow.com/questions/4699591/how-to-autoincrement-varchar – Yanuar Dec 26 '12 at 09:38
  • Does this answer your question? [MySQL - how to use VARCHAR as AUTO INCREMENT Primary Key](https://stackoverflow.com/questions/3455557/mysql-how-to-use-varchar-as-auto-increment-primary-key) – Tofandel Aug 30 '22 at 13:29

4 Answers4

6

NONE. So far, AUTO_INCREMENT can be set for INT only. It needs to have your own logic to do that. eg,

  • Get Last ID
  • Get the INT part within the string
  • Increment the value
  • Concatenate
  • Save to DB
John Woo
  • 258,903
  • 69
  • 498
  • 492
0

Duplication of MySQL - how to use VARCHAR as AUTO INCREMENT Primary Key

According to my point of view you have to change it as integer type. Since AUTO_INCREMENT is for INT type only, moreover setting the field as PRIMARY KEY which prevents duplication of values.

Community
  • 1
  • 1
Nidhish Krishnan
  • 20,593
  • 6
  • 63
  • 76
0

AutoIncrement fields are integer in mysql.

You can mirror the auto-increment field in a varchar field and create a trigger which updates the varchar field on insert/update.

Create Trigger

Rahul
  • 15,979
  • 4
  • 42
  • 63
0

for my experience, you can make Varcar ID as auto increment but you must have logic..let say

 Dim crnum As String = "CR00001"
    Dim iTemp As Integer = 0

                iTemp = CInt(crnum.Substring(4, crnum.Length - 4)) '// get only the #'s from String and Convert them to Integer.
                iTemp += 1 '// increase the ID # + 1.
                crnum = crnum.Substring(0, 4) & iTemp.ToString("00000") '// set the ID back with String and #'s formatted to 5 digit #.

but you must do your own logic ,because after u run it for second time it will become duplicate primary key so, you must think your logic to apply your code. Anything is possible. ;)

kolapopo
  • 108
  • 1
  • 4
  • 14