mysql basic question

[robert_gsfame]

if i have autoincrement column like for example:

 

I have 3 columns : ID (auto increment), Name, city

Then if i want to insert some records, can i use

 

INSERT INTO TABLE1 VALUES('James','Japan') which mean ignoring the ID or i still have to create this INSERT INTO TABLE1 (Name,City)VALUES('James','Japan')

 

thanks

[DavidAM]

In SQL if you do not name the columns in an INSERT statement, the data is applied to the columns in the order they are defined in the table.  So you first example INSERT INTO TABLE1 VALUES('James','Japan') would try to put 'James" into the ID column and 'Japan' into the Name column.  Of course this will fail because the ID column is an integer and 'James' is not.

 

So, yes, you would have to name the columns as in your second example: INSERT INTO TABLE1 (Name,City)VALUES('James','Japan') (by the way, you need a space before the VALUES clause).

 

As an alternative, you could order the columns in the table putting ID last, and then not have to name the columns in the INSERT (your first example would work).  Since there are only two values being inserted, the third (and any remaining columns) would be ignored and receive their default value.  I am not advocating this table layout.  I have never seen it done and it does not "feel" right to do it this way. But in theory it should work.

 

I recommend ALWAYS naming the columns in the INSERT statement so when you are reading the code you can see exactly what is happening without having to refer to the table layout when you (or someone else) has to modify the code later.