MySQL and INT fields auto_increment

Question

I am developing in LAMP (Linux + Apache + MySQL + PHP), as I remember myself. But one question has bothered me for many years. Hope you can help me find the answer and point me in the right direction. Here is my task:

Let's say we create a community site where we allow our users to register. The MySQL table in which we will store all users will look like this:

CREATE TABLE `users` (
  `uid` int(2) unsigned NOT NULL auto_increment COMMENT 'User ID',
  `name` varchar(20) NOT NULL,
  `password` varchar(32) NOT NULL COMMENT 'Password is saved as a 32-bytes hash, never in plain text',
  `email` varchar(64) NOT NULL,
  `created` int(11) unsigned NOT NULL default '0' COMMENT 'Timestamp of registration',
  `updated` int(11) unsigned NOT NULL default '0' COMMENT 'Timestamp of profile update, e.g. change of email',
  PRIMARY KEY  (`uid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

So, from this fragment you can see that we have a unique and automatically increasing for each new user field "uid". Like every good and loyal community website, we must give users the option to completely delete their profile if they want to cancel their participation in our community.

. , 3 : Alice (uid = 1), Bob (uid = 2) Chris (uid = 3). . Bob "users", "uid" , . -, . 3 :

1) "uid" SMALLINT (int (2)) , , BIGINT (int (8)) , uid .

2) 'is_deleted', ( ), uid . :

CREATE TABLE `users` (
  `uid` int(2) unsigned NOT NULL auto_increment COMMENT 'User ID',
  `name` varchar(20) NOT NULL,
  `password` varchar(32) NOT NULL COMMENT 'Password is saved as a 32-bytes hash, never in plain text',
  `email` varchar(64) NOT NULL,
  `is_deleted` int(1) unsigned NOT NULL default '0' COMMENT 'If equal to "1" then the profile has been deleted and will be re-used for new registrations',
  `created` int(11) unsigned NOT NULL default '0' COMMENT 'Timestamp of registration',
  `updated` int(11) unsigned NOT NULL default '0' COMMENT 'Timestamp of profile update, e.g. change of email',
  PRIMARY KEY  (`uid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

3) script, . . , (uid = 2) , (uid = 3), uid 2 (is_deleted = '1'), . uid , uid.

, , auto_increment. , .

!

Answer 1

ids - - mysql. , 1 000 000 , 2 - 999999 ... , , db . , auto_increment, . insert → AI + 1 → insert → AI + 1 → delete → AI ... ID, auto_increment 1,000,001, 1 000 000 .

unsigned BIGINT - , bigint, ;)

Answer 2

PHP, "" auto_increment, "" , "next auto_increment".

function mysql_fix_aigap($table,$column){

$fix_aigap=1;

$query_results=mysql_query("select * from $table");

while($row=mysql_fetch_array($query_results)){

mysql_query("update $table set `$column`='$fix_aigap' where `$column` like {$row[$column]};");

$fix_aigap=$fix_aigap+1;

  }

mysql_query("alter table `$table` AUTO_INCREMENT =$fix_aigap");

}

:

mysql_fix_aigap("gapped_table_to_be_fixed","column"); //"users" and "uid" in your case.

( script , , !)

.

, "" uid , ! (ID = )

.

Answer 3

; , "" uids? , ( BIGINT), 70.

-, , , , , , "". - , , , , , ...

, , , . , ...

Answer 4

, , . . , .

, MySQL INT(2) 2 , . INT(8) , INT(2) - BIGINT, .

Answer 5

The maximum unsigned int is 4,294,967,295. The current Internet population is about 1.8 billion. I would recommend using unsigned int for your purposes and not worry about spaces in your sequence.

On a philosophical note: Donald Knuth once said: "We must forget about little efficiency, say, about 97% of the time: premature optimization is the root of all evil."