How to auto increment in MySQL without declaring the columns in INSERT?

Question

How to auto increment in MySQL without declaring the columns in INSERT?

Navigation

#1 by (2 votes)

2

I noticed that when I have a column with auto_increment , I have to declare the columns in INSERT .

For example, I created this test table:

create table teste(
id int auto_increment,
nome varchar(100) not null,
valor decimal(5,2),
constraint id_pk primary key (id));

I wanted to be able to insert the data without declaring the columns, as in this example:

insert into teste values ('Danilo',333.33)

But he does not understand, and in the end I have to state it like this:

insert into teste (nome,valor) values ('Danilo',333.33)

My question is if I have to INSERT without declaring all the columns, type using a reserved word or something of the type that MySQL understands.

mysql sql sintaxe

asked by anonymous 30.07.2018 / 01:52

1 answer

Why is the class attribute unchanged? The insert of the Node express with problem

score 2 · Accepted Answer

Since you are using a positional syntax with all the columns you have to send values to all of them. And as you do not want to send to the first column, send a null. Since it has an automatic default value this value will be put in place of the null.

insert into teste values (null, 'Danilo',333.33)

See running SQL Fiddle . Also put it in GitHub for future reference .

If you think that the output is bad, you should use the syntax of named arguments to not consider the position of each column.