MySQL skip a column name when inserting values

Question

I have a table in which the first column is auto_increment. I want to insert data into the table, but skip the first column as it is updated automatically when a new row is begun. so:

INSERT INTO table VALUES (NULL,"lady","gaga","rulz");

But NULL cannot be inserted into a column as I specified earlier. What do I need to replace NULL with so that the column doesn't get anything inserted into it?

Answer 1

You need to explicitly specify the column names and order. In other words

INSERT INTO table (field2, field3, field4) VALUES ("lady","gaga","rulz");

BTW it is typically a good idea to avoid the implicit insert syntax (but maybe for the simplest / debug time snippets), lest you get surprised when/if the underlying table schema was somehow changed.

Also, so you know, something, will get inserted into the (here) field1 column. When you define or alter the schema of a table, a given field can either be nullable or not, and/or it can have a default value or not. In the SQL provided above, you can only omit the values for fields that are either nullable or have a default value; SQL will otherwise return an error and won't insert any part of the new record.

Answer 2

Just make sure you specify the respective column names

INSERT INTO table (col1, col2, col3) VALUES ("lady","gaga","rulz");

You don't even need to fill all columns (if they are not required), Ie.

INSERT INTO table (col2) VALUES ("gaga");

Answer 3

insert into table(field1, field2, field3) values('lady', 'gaga', 'sucks')