Solved

how to set auto increment = 1

Posted on 2011-09-21
2
455 Views
Last Modified: 2012-05-12
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!
0
Comment
Question by:crafuse
2 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 36572420
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
 

Author Closing Comment

by:crafuse
ID: 36572461
thanks, ya i found that too...
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now