LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices


Reply
  Search this Thread
Old 10-20-2008, 06:17 AM   #1
shan_nathan
Member
 
Registered: Jun 2007
Location: India
Distribution: Redhat
Posts: 137

Rep: Reputation: 15
Mysql Row id problem


Dear all,

I am new to mysql, I am using mysql database with the front end php.

I am using contacts table for my office contact details.

i created the table using the following command

mysql > CREATE TABLE Contacts ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Name TEXT, Email DATE NOT NULL );

now i added lot of contacts and the id was AUTO_INCREMENT so i did not give any ID . When i delete a contact ( example 355 Last contact )

After i add a new contact it's taking 356 ID only. the auto adjustment is not happening
can some one help me how to solve this issue.

Thanks in advance
Shan
 
Old 10-20-2008, 10:48 AM   #2
rawdata
LQ Newbie
 
Registered: Aug 2008
Distribution: Gentoo
Posts: 21

Rep: Reputation: 0
To my knowledge, it won't adjust. Just a shortcoming of the auto incrementer I guess. If anyone knows different, please post here. I have roster that uses auto inc and would like to make it adjust too if it will.
 
Old 10-20-2008, 12:59 PM   #3
Wim Sturkenboom
Senior Member
 
Registered: Jan 2005
Location: Roodepoort, South Africa
Distribution: Ubuntu 12.04, Antix19.3
Posts: 3,794

Rep: Reputation: 282Reputation: 282Reputation: 282
Source: 'MySQL, The definite guide to using, programming and administering MySQL 4.1 and 5.0, 3rd edition' by Paul Dubois

Quote:
The sequence might be affected if you delete rows from the table. That is, sequence values might be reused; it is storage engine-dependent whether this occurs
For MyISAM tables, the normal behaviour is not to reuse. However, you can work around it by making the auto-increment column part of a composite index.

Code:
create table mytable
{
columnA char(1) default 'a' NOT NULL,
id INT auto_increment NOT NULL,
name char(20),
..
other colmns here
..
PRIMARY KEY (columnA, id)
} ENGINE=MYISAM;
The trick is to always insert the same value in columnA. If you now delete a record, MYISAM will reuse deleted id (according to the book, not tested). Disadvantage is obviously the sue of extra space due to the extra column.

BDB tables do reuse deleted values
InnoDB tables and MEMORY tables do not reuse deleted values

Notes:
1)
The id column MUST be specified as the last field in the primary key (so id, columnA wil not work)
2)
The given example allows the occurence of the same id a number of times
Code:
columnA id name
  a      1  shan_nathan
  a      2  rawdata
  b      1  wim sturkenboom
  a      3  someone else
3)
As far as I know, deleted values will be reused when the end of the range is reached
 
Old 10-20-2008, 08:45 PM   #4
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 7.7 (?), Centos 8.1
Posts: 17,905

Rep: Reputation: 2615Reputation: 2615Reputation: 2615Reputation: 2615Reputation: 2615Reputation: 2615Reputation: 2615Reputation: 2615Reputation: 2615Reputation: 2615Reputation: 2615
auto-increment means what it says. Its a common feature of all RDBMS's that they don't re-use values. some may wrap around at the end, but given that they are usually used for nums that shouldn't repeat eg txn ids, its not a good idea....
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Shell script to parse csv-like output, row by row utahnix Linux - General 8 12-08-2007 05:03 AM
mysql alter row data kpachopoulos Programming 8 09-26-2007 02:54 AM
getting the next row from mysql database? tzouse Linux - Software 4 01-29-2006 07:17 PM
MySQL - Warning: mysql_result(): Unable to jump to row 0 on MySQL result index 9 jasontn Linux - Software 0 02-09-2005 12:17 PM
MySQL Row Limit agallant Programming 1 06-25-2004 11:16 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Server

All times are GMT -5. The time now is 08:12 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration