Search code examples
mysqldatabasedatabase-designauto-incrementcreate-table

MySQL: the inconsistency of auto increment in id numbers

I wonder if you have come across this that the id numbers of auto increment don't arrange in correct order when you click on Browse button on phpMyAdmin - is it just me? Something I have set incorrectly in the db table?

For instance, when you insert a series of data into a table and you have deleted some of them as well, so when you check this table on phpMyAdmin, these data should appear in an order like this,

id

2
24
28
296
300

but in most of my tables they don't appear in orders, instead they appear in something like this,

id

24
300    
2
296    
28

as in this picture below,

alt text

Can we do something about it so that the IDs appear in the correct order?

EDIT 1:

I think most of you have misunderstood the issue I raided here. I mean when you click on Browser button on the phpMyAdmin to list all the data in your tables - not when you use SQL query to list the output by using order by.

Does it make sense?

alt text

EDIT 2:

This is one of my table structure - does it help you to see what is wrong in it?

CREATE TABLE IF NOT EXISTS `root_pages` (
  `pg_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pg_url` varchar(255) DEFAULT NULL,
  `pg_title` varchar(255) DEFAULT NULL,
  `pg_subtitle` varchar(255) DEFAULT NULL,
  `pg_description` text,
  `pg_introduction` text,
  `pg_content_1` text,
  `pg_content_2` text,
  `pg_content_3` text,
  `pg_content_4` text,
  `pg_order` varchar(255) DEFAULT NULL,
  `pg_hide` varchar(255) DEFAULT '0',
  `pg_highlight` varchar(255) DEFAULT '0',
  `pg_important` varchar(255) DEFAULT '0',
  `pg_parent` varchar(255) DEFAULT '0',
  `parent_id` varchar(255) DEFAULT NULL,
  `pg_cat_id` varchar(255) DEFAULT NULL COMMENT 'page category id',
  `ps_cat_id` varchar(255) DEFAULT NULL COMMENT 'post category id',
  `tmp_id` varchar(255) DEFAULT NULL COMMENT 'template id',
  `pg_backdate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `pg_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `pg_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`pg_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Solution

  • Not sure if this applies to you but is your PK of type INT? If it's some sort of VARCHAR or TEXT it could order like the example you showed. And are you sure that column is actually your PK? Post the SHOW CREATE TABLE output of the table and it might become clearer.

    And which storage engine are you using. The default order of a select is insert order for MYISAM and PK for InnoDB if I remember correctly. This would also depend of your version of MySql. To be sure in all circumstances you SHOULD use ORDER BY in your select statement.