how to set auto increment = 1

Experts,

if i run the following scripts i still get three records with id = 10, 20, and 30. What am I missing here?

i'm completely new to mySQL, but this is odd...

drop table admin_users;

CREATE TABLE `admin_users` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `admin_logon` varchar(32) NOT NULL,
  `admin_psswd` varchar(32) NOT NULL,
  `update_psswd` int(11) NOT NULL DEFAULT '0',
  `dept_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

alter table admin_users auto_increment = 1;

INSERT INTO `admin_users`
(`admin_logon`,
`admin_psswd`,
`update_psswd`,
`dept_id`,
`role_id`)
VALUES
('dduck','test',0,4,2);

INSERT INTO `admin_users`
(`admin_logon`,
`admin_psswd`,
`update_psswd`,
`dept_id`,
`role_id`)
VALUES
('mmouse','test',0,4,2);

INSERT INTO `admin_users`
(`admin_logon`,
`admin_psswd`,
`update_psswd`,
`dept_id`,
`role_id`)
VALUES
('pluto','test',0,4,2);

select * from admin_users;

Result:

ID

10      2011-09-21 04:09:39      dduck      test      0      4      2
20      2011-09-21 04:09:39      mmouse      test      0      4      2
30      2011-09-21 04:09:40      pluto      test      0      4      2

TIA!
crafuseAsked:
Who is Participating?
 
Pratima PharandeConnect With a Mentor Commented:
Check to see the seed value of the autoincrement isn't set to 10.

You can check by:

SELECT Auto_increment FROM information_schema.tables WHERE table_name='the_table_you_want';

 As noted elsewhere you can change by using the system variable @@set_auto_increment_increment

SET @@auto_increment_increment=1;

refer
http://stackoverflow.com/questions/206751/mysql-autoincrement-column-jumps-by-10-why
0
 
crafuseAuthor Commented:
thanks, ya i found that too...
0
All Courses

From novice to tech pro — start learning today.