How to customize Auto_increment numbering in Mysql?

Asked

Viewed 3,098 times

6

I have a column Contrato in my Mysql database, it is like Auto_Increment, by numbering the contracts as 1, 2, 3... But I needed, if possible, to organize these numbers in the actual format of contract numbers.

That’s how it works, Ex.:

  • Client Ana, Contract: 22010-2015
  • Client Tony, Contract 22020-2015

That is, increase the class by 10 22000 and stay the year -2015

It is possible?

Even if you have to disable Auto_Increment and put a php code in the registration area?

mysql sql auto-increment
  • 1

    This is a terrible practice, you are in a relational database, you can simply create a field to store this date and use it as a criterion in the querys

  • But to follow the relational model I already have the ID field, which follows the standard database, in case I wanted a facility to consult the contracts, so I would no longer have another database, which currently is Microsoft Excel and would leave everything online.

  • you can build a Trigger on the Insert, give a look Trigger

1 answer

8


You’ll have to do three things:

  1. Create a table to store the number of the last contract.
  2. Create a trigger to ensure that each insert uses the sequential number
  3. Create a function that calculates the next contract number based on the defined rules and the number found in the above table.

No doubt you won’t be able to use the AUTO INCREMENT mysql.

Something like that:

CREATE TABLE sequencia_contrato (
    sequencial INT NOT NULL PRIMARY KEY,
    ano INT
);

Trigger:

CREATE TRIGGER numero_contrato BEFORE INSERT ON contrato
FOR each ROW
BEGIN
   SET NEW.contrato = PegaNumeroContrato();
END

Function:

Basically you will have to value the table above, add up 10, if I understood that this is what you want. And catch the current year. If the current year is greater than the year recorded in the control table of the sequence, the year there should be updated to the current year and the sequence should be reset. Maybe this isn’t exactly what you want.

CREATE FUNCTION PegaNumeroContrato() RETURNS VARCHAR(10)
BEGIN
    //precisa melhorar ainda, falta tratar o ano, por exemplo
    DECLARE ultimo_valor INT;
    SET ultimo_valor = (SELECT sequencial FROM sequencia_contrato);
    SET ultimo_valor = ultimo_valor + 1;
    UPDATE sequencia_contrato SET sequencial = ultimo_valor;
    SET @resultado = (SELECT concat(lpad(ultimo_valor, 5, '0'), '-', CAST((SELECT ano FROM sequencia_contrato) AS CHAR(4))));
    RETURN @resultado;
END

I put in the Github for future reference.

  • I’m just a "constructor" in php, I don’t know this type of code, if it is complex, I can put the year as part of the previous code, e.g.: 201522010, and I can fill it in manually, since it takes time to change, the important thing for me is to add 10 to each number entered, and I already have registration starting from 22000

  • The whole process will be done in Mysql, it is not safe to do by PHP. Really can hit manual but is not ideal.

  • I am migrating from SQL Server pro Mysql and taking advantage of the question, how can I increment starting from 0? In SQL Server it looks like this: ID INT NOT NULL IDENTITY(0,1), but how do I do it in Mysql?

  • Questions should be asked as questions, this area is only to comment on the answer given, not to continue the subject.

  • Take care because the volume of simultaneous inclusions may occur key duplicities, I do not know how the "lock" table works in Mysql to opine , as said Isvaldo a bad practice , type of the thing that is only done if there is legal requirement.

Browser other questions tagged mysql sql auto-increment

You are not signed in. Login or sign up in order to post.