change autoincrement value

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
vipul73
Forum Commoner
Posts: 27
Joined: Mon May 12, 2003 5:32 am

change autoincrement value

Post by vipul73 »

I have a table in mysql and and field a 'id' in it which is set on auto increment.

I would like to start the value from 100 instead of 1.

can anyone please help me with this
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

inset

Post by phpScott »

insert a dummy record where you are setting the autoincrement field.

Code: Select all

INSERT INTO someTable (autoField, otherField1, otherField2) VALUES (100, 'dummy1', 'dummy2')
The next record inserted will be 101, and so on.
The autoincrement by default starts at 0 and increments by 1 from the last inserted record, so it the last record inserted is 100 the next one will be 101.

phpScott
vipul73
Forum Commoner
Posts: 27
Joined: Mon May 12, 2003 5:32 am

Post by vipul73 »

Hmm.. I tried setting the value of autoincrement to 3424 but it has set to 127 and further when I am trying to enter any new entry it is saying

Duplicate entry '127' for key 1

The structure of the table is as below

Code: Select all

CREATE TABLE contact (
  id tinyint(4) NOT NULL auto_increment,
  email varchar(50) NOT NULL default '',
  PRIMARY KEY  (id),
  UNIQUE KEY id (id)
) TYPE=MyISAM;
Paddy
Forum Contributor
Posts: 244
Joined: Wed Jun 11, 2003 8:16 pm
Location: Hobart, Tas, Aussie
Contact:

Post by Paddy »

I'd say that is because tinyint only has enough memory to go up to 127. So when you try to add a higher number it defaults to the highest value it can have which is 127.

But I might be wrong.
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post by []InTeR[] »

It's allso posseble to change the auto incr field by alter.
ALTER TABLE `aandoeningen` AUTO_INCREMENT = 2042;
to start @ a other value
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Paddy's got the answer - TINYINT only goes up to 127 so if you need numbers bigger than that try SMALLINT or MEDIUMINT or INT or BIGINT depending on how high the auto_increment will need to go:
http://www.mysql.com/doc/en/Numeric_types.html

Mac
vipul73
Forum Commoner
Posts: 27
Joined: Mon May 12, 2003 5:32 am

Post by vipul73 »

Thanks a lot to every1 who has taken his valuable time to respond to my queries and being patient with me

I have been able to complete the website niftyonline.com upto my satisfaction. I know you guys would be able to suggest a lot of better things after visiting the site and am ready for the brickbats.

Thanx once again.
Post Reply