2

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?

Steve
  • 21,163
  • 21
  • 69
  • 92

5 Answers5

6

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.

Chris
  • 11,780
  • 13
  • 48
  • 70
  • 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? – TMN Aug 25 '10 at 17:11
  • NULL is acceptable here when you are using id as a primary key. – Chris Aug 25 '10 at 17:25
  • @steve no worries this is what stackoverflow is here for. :-) cheers – Chris Aug 25 '10 at 17:37
  • 1
    better use DEFAULT instead of NULL – Axel Heider Sep 30 '17 at 15:51
  • @AxelHeider why? – Chris Oct 02 '17 at 22:57
  • @Chris: it explicitly states to use the database defaults here and not a NULL value. So technically there is no strong reason, but it improves maintainability of the queries – Axel Heider Oct 04 '17 at 00:29
3

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.

hookedonwinter
  • 12,436
  • 19
  • 61
  • 74
0

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.

Cfreak
  • 19,191
  • 6
  • 49
  • 60
0

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.

Hammerite
  • 21,755
  • 6
  • 70
  • 91
0

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.

nightClaimer
  • 1
  • 1