Page 1 of 1

change autoincrement value

Posted: Wed Jun 11, 2003 12:38 pm
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

inset

Posted: Wed Jun 11, 2003 12:54 pm
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

Posted: Wed Jun 11, 2003 11:52 pm
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;

Posted: Thu Jun 12, 2003 12:36 am
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.

Posted: Thu Jun 12, 2003 2:11 am
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

Posted: Thu Jun 12, 2003 4:16 am
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

Posted: Thu Jun 12, 2003 2:59 pm
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.