how does auto increment work in mysql?

4

I want to know how to add a column so that it is a primary key and auto-increment, but that the value is higher, eg:

  

ID: 3509. and not just 1,2,3,4 ... etc.

    
asked by anonymous 01.11.2017 / 19:20

2 answers

1

If you do not already have the field you can use the command ADD COLUMN

ALTER TABLE 'Sua_tabela'
  ADD COLUMN 'id' int(11) NOT NULL AUTO_INCREMENT,
  ADD PRIMARY KEY ('id');

If the field already exists, you can use CHANGE COLUMN

ALTER TABLE 'Sua_tabela'
  CHANGE COLUMN 'nome_atual_do_campo' 'novo_nome_do_campo' int(11) NOT NULL AUTO_INCREMENT,
  ADD PRIMARY KEY ('novo_nome_do_campo');

If you do not want to change the field name, just repeat the same name:

CHANGE COLUMN 'nome_do_campo' 'nome_do_campo' int(11...

And to set the value of AUTO_INCREMENT use:

 ALTER TABLE 'Sua_tabela'
    AUTO_INCREMENT=3509;
    
01.11.2017 / 19:46
4

You have to set the initial AUTO_INCREMENT value for your table.

ALTER TABLE users AUTO_INCREMENT=3509;

If you have not yet added an ID column, add it

ALTER TABLE users ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ADD INDEX (id);

See a more detailed example.

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);
ALTER TABLE animals AUTO_INCREMENT = 3509;

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

sqlfiddle

    
01.11.2017 / 19:25