12

I just want my mysql table id(primary key) to start from 0..

As I have seen, I used ALTER TABLE yourtable AUTO_INCREMENT =0 but it starts from 1..What is that I need to do?

Edit1

I also emptied my table with truncate option

Shehzad
  • 337
  • 4
  • 25
user2234992
  • 543
  • 4
  • 8
  • 20
  • Why? Don't try to influence the `AUTO_INCREMENT` value. Leave it up to the database to do it. It's arbitrary anyway. If you want to add some kind of ID you want to assign yourself, you shouldn't make it `AUTO_INCREMENT`. – Bart Friederichs Apr 26 '13 at 08:59
  • Could be duplicate of http://stackoverflow.com/questions/1142472/how-to-force-mysql-to-take-0-as-a-valid-auto-increment-value – Frhay Apr 26 '13 at 09:00
  • @BartFriederichs actually it has to do some work with arrays in php so i need it..guess I will start from 1 and decrement it in my php code for arrays.. – user2234992 Apr 26 '13 at 09:00
  • @BartFriederichs will it work ok when I try to access it?? I mean with index 0?? – user2234992 Apr 26 '13 at 09:02
  • Check the answer posted by Mathew... http://stackoverflow.com/questions/1142472/how-to-force-mysql-to-take-0-as-a-valid-auto-increment-value – Vinayak Pahalwan Apr 26 '13 at 09:02
  • 2
    @user2234992 if you want to populate an array from db, then use associative arrays as they are more faster and occupies less memory. If you delete few rows from DB, then indexed array will still occupy memory even their value is not assigned – Amit Apr 26 '13 at 09:03

2 Answers2

21
SET [GLOBAL|SESSION] sql_mode='NO_AUTO_VALUE_ON_ZERO'

NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number.

This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT column. (Storing 0 is not a recommended practice, by the way.)

Reference

Reference 2

Community
  • 1
  • 1
Hanky Panky
  • 46,730
  • 8
  • 72
  • 95
  • 3
    wtf? Why isn't zero okay. It's a perfectly normal number. – Darth Egregious Oct 15 '14 at 19:04
  • 10
    @Fuser97381: yes it's a number but it makes life harder. In many programming languages, you have to pay attention when you use boolean functions and pass a 0 as argument. For example, in php, some functions interpret 0 as FALSE. if you get an id from the database, store it in the variable $id and you use : empty($id), to check if $id is a valid id, that function returns FALSE when $id = 0. – Frank Dec 19 '17 at 18:13
3

You cannot force this to be zero. This is limited by auto_increment_offset server variable. The default value is 1, the range is [1 .. 65535].

More information: auto_increment_offset, How to set AUTO_INCREMENT step.

Michael Mior
  • 28,107
  • 9
  • 89
  • 113
Devart
  • 119,203
  • 23
  • 166
  • 186