I found the mysql_insert_id function to retrieve the last auto generated ID.
Should I be using mysql_insert_id +1 to add a new ID or is there a call for adding a new unique ID?
Add a comment |
5 Answers
Using NULL for id:
INSERT INTO `database`.`table` (`id`, `user`, `result`) VALUES (NULL, 'Alice', 'green')"); OR not specifying id at all:
INSERT INTO `database`.`table` (`user`, `result`) VALUES ('Alice', 'green')"); Either way works just fine, more of a preference but personally I chose the second as its less typing.
6 Comments
TMN
And less confusing as well -- I'd expect the first example to throw an error, since you're trying to insert NULL into a (presumably) non-NULL column. Or does mySQL not differentiate between unspecified and explicit NULL values?
Chris
NULL is acceptable here when you are using id as a primary key.
Chris
@steve no worries this is what stackoverflow is here for. :-) cheers
Axel Heider
better use DEFAULT instead of NULL
Chris
@AxelHeider why?
|
If your id field is set to auto increment, you don't have to add an ID at all. It will be incremented and added automatically.
Comments
AUTO_INCREMENT in MySQL does exactly what it sounds like. When you insert a new record it will automatically generate a new ID for you. You do not need a separate call.
Comments
Insert a new record and set the auto-increment column to NULL, or just omit it entirely (which is implicitly setting it to NULL - it has the same result). The column will be set to the next auto-increment value instead of NULL.
Comments
When you delete a row and you insert again an another row, the new inserted id is not the same as what you delete before you insert again. example you have 3 row and the id value is 1, 2, 3, when you delete 3 then insert again, the id result is 4. And when you try to delete 2, the id result when you try insert again is 5.
