Reset AUTO_INCREMENT without losing data

Asked

Viewed 263 times

0

I am working with a table of offices, where the number of the craft and the year in which it was created are a composite primary key. I am generating the number of the craft through AUTO_INCREMENT and picking up the current year with php, However, I would like it if, for example, when the year 2017 came to an end, the number of the letter went back to the beginning again. For Letter 01 of the year 2018 is different from Letter 01 of the year 2017. My table is this: 

CREATE TABLE oficio (
    numero int AUTO_INCREMENT,
    ano int,
    interessado varchar(100),
    assunto  text,
    CONSTRAINT pk_NumeroAno PRIMARY KEY(numero,ano)
);

I’ve read about the command:

ALTER TABLE oficio AUTO_INCREMENT = 1;

But for it to work I need to delete all information from the table. How can I reset the AUTO_INCREMENTE without losing the data?

mysql
  • 1

    See if it fits your case: Document protocol

  • Lucas I made a test here using "ALTER TABLE officio AUTO_INCREMENT = 1;" and not zeroed my table, she continued with the data.

  • So Kayo, the table is not reset, but the AUTO_INCREMENT continues in number before, do you understand? I needed him to return to the first.

  • rray, that’s what I was thinking. I’ll try to implement.

  • 1

    @Lucasramos ta almost there, so I saw the difference is in the order of the fields in primary key

  • I just didn’t quite understand the part about: ENGINE=Myisam DEFAULT CHARSET=utf8.

  • 1

    Each engine have different functionalities, for example the MyISAM has no referential integrity already the innoDB has been used each for a given context. o charset says in which encoding the data will be stored, in case it is utf-8 but could be latin-1 or other.

  • Got it, and it worked. Thank you!

Show 3 more comments
No answers

Browser other questions tagged mysql

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