how does auto increment work in mysql?

Asked

Viewed 30,215 times

5

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

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

mysqli id

  • The "most self" you refer to would beLto (with L)?

  • You can set the initial autoincrement value.

2 answers

6


If you don’t have the field yet 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 don’t 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;

3

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

ALTER TABLE users AUTO_INCREMENT=3509;

If you haven’t added an identification column yet, 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

Browser other questions tagged mysqli id

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