What I want is that when entering a product in my MYSQL database, it generates its own identification code in this way:
- P001 - Detergent
- P002 - Oil
- P003 - Rice
I was suggested to do it with stored procedure, but I don't know how. Any idea how to do it?
As Leandro told you, the identifier code of a table is recommended to be a numerical data, what you could do is an extra field that works as a second code, perhaps called code_secondary. You can do it with Stored Procedure, I give you a small example:
With that you can generate codes as you need with the structure 'P001'
The solution proposed by Christopher, based on Leando's, is quite good. In spite of this, more than a
store procedure
, it would do it with triggers, in such a way that before inserting a new product in your table, this second field is calculated. For example, this would be a trigger that would solve this:Explaining the code in parts:
A variable is declared to retrieve from the schema the next id to use (as it is autoincremental and the trigger runs prior to insertion, we do not have the value for the id
The field is set
codigo_secundario
as the next id to insert with a padding (fill) of zeros in front, until completing a string of length 8 together with a prefix with the letterP
. The length of 8, I took it since it allows you to have10^8-1
products in total. If you estimate that you will have more than 99,999,999 products, increase this value or decrease it if you will have much less than that.I hope I've helped.
Cheers
An identifier for the table is not designed that way, the id must be something simple, a sequential number or guid.
A calculated field is not recommended as it does not conform to normal forms
Database Normalization
in your case define a sequential numeric field that takes the values 1,2,3 etc and then another field that represents the letter P, I couldn't tell you what name to give this field because it would be something related to the business
When you show the data to the user you could compose the value by joining the letter field with the numeric sequential
You can use, if it's php, str_pad
and so add zeros in front of the code.
I ran into the same problem, I did some research and they recommend me not to use
information_schema
instead it is more recommended to useLAST_INSERT_ID()
andLimit 1
Trigger: