MySQL Trigger for auto-increment a varchar (not Primary key)

Question

I think its already answered, but I'm confused with some threads. I created a table

ID - int (auto-increment) PK
IDNumber - varchar ----> sample data (AFC0000001)
EmployeeID - FK

Now, Im looking for some idea on how I can manage the auto-increment for 'IDNumber'. I know its useless idea, but in my current situation I need this. Maybe you know some trigger or any idea?

How to increment a varchar? 'A' -> 'B' -> 'C'? you need change your solution indeed. — Liping Huang, Sep 06 '17 at 04:43
@LipingHuang not like that, its a variable with numbers. like ABC00001 — pgrueda, Sep 06 '17 at 04:46
You could also just append the ID whenever you query IDNumber, and only store the prefix, or append the ID to the IDNumber after your INSERT. — Garr Godfrey, Sep 08 '17 at 21:09

score 0 · Accepted Answer · edited May 04 '19 at 10:20

0

Do you mean changing something like ABC00001 -> ABC00002? or 0001 -> 0002?

It's going to be tricky if your ID is something like ABD00001 because you have to ignore all the characters in front of the 1 and only increment 1 by 1 = 2, etc. on the other hand if your ID was something like 01 or 02 then you can auto increment the number using SQL so something like

mysql_query("UPDATE ed_names SET c_request = c_request+1 WHERE id = 'x'");

Edit

It wouldn't take more process really all you'd have to do is something like this

<input type="text" name="card_id" value="ABC<?php $length = 5; $randomString = substr(str_shuffle("0123456789"), 0, $length); echo $randomString;?>">

which would then output something like:

<input type="text" name="card_id" value="ABC65918">

The value would then change every single time the page is reloaded of course but if you wanted to be sure you can add a script that changes the value every 5 or 10 seconds.

edited May 04 '19 at 10:20

halfer

19,824
17
99
186

answered Sep 06 '17 at 04:49

JeanPaul98

492
6
18

well yeah. thats the main thing because its actually generating a ID card number. – pgrueda Sep 06 '17 at 05:00
I don't think I have the expertise for that but I'm sure a bit of googling will turn something up but in the meantime does the ID have to be in order? can you not just generate something like ABC72390 and then generate another random number ABC82337? – JeanPaul98 Sep 06 '17 at 05:13
yep. If I generate in random, it wil be a loop right? which will take more process. – pgrueda Sep 06 '17 at 05:16
But then again I've just found these: https://stackoverflow.com/a/5407487/8016771, https://stackoverflow.com/a/31874322/8016771, https://stackoverflow.com/a/34564162/8016771 – JeanPaul98 Sep 06 '17 at 05:17
wow thanks man! I think this one wil do, but I guset last option haha https://stackoverflow.com/questions/31873904/auto-increment-a-varchar-in-sql-query/31874322#31874322 – pgrueda Sep 06 '17 at 06:06

MySQL Trigger for auto-increment a varchar (not Primary key)

1 Answers1

Edit