Re: Re: Create unique non-autoincrement key for 700,000 records?

support@trafficregenerator.com schreef: >> On Monday, December 15, 2008 7:29 PM, gouldimg@mac.com wrote: >> ... > <?php > > error_reporting(E_ALL); > ini_set('error_reporting', E_ALL); > ini_set('display_startup_errors','1'); > ini_set('display_errors','1'); > > > function dec2base($dec) > { > $digits = "23456789ABCDEFGHJKLMNPQRSTUVWXYZ"; > $value = ""; > $base = strlen($digits); > while($dec>$base-1) > { > $rest = $dec % $base; > $dec = $dec / $base; > $value = $digits[$rest].$value; > } > > $value = $digits[intval($dec)].$value; > return (string) $value; > } > > /* > // Step 1) define database connection > > define('DB_HOST', 'localhost'); // Change this to the proper DB Host name > define('DB_USERNAME', 'myusername'); // Change this to the proper DB User > define('DB_PASSWD', 'mypassword'); // Change this to the proper DB User > password > define('DB_NAME', 'mydatabase'); // Change this to the proper DB Name > > @mysql_connect(DB_HOST, DB_USERNAME, DB_PASSWD) or die("Error: Database > connection information is incorrect"); > @mysql_select_db(DB_NAME) or die("Error: Database connection information > is incorrect"); > > */ > > /* > // Step 2) create test schema > > CREATE TABLE IF NOT EXISTS test ( > id int(11) NOT NULL auto_increment, > mykey varchar(20) NOT NULL, > PRIMARY KEY (id) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; > > */ > > > /* > // Step 3) create 700,000 records > > for ($i=1; $i <= 700000; $i++) > { > @mysql_query("INSERT INTO test VALUES ('', '')"); > } > > */ > > /* > // Step 4) update 700,000 records > > > // The larger this number is detrmines the size of "mykey" > $int = 1000000; > > $result = @mysql_query("SELECT id FROM test ORDER BY id ASC"); > > if (@mysql_num_rows($result) > 0) > { > while ($row = @mysql_fetch_object($result)) > { > // Add the two numbers together and base it > $mykey = dec2base($int+$row->id); > @mysql_query("UPDATE test SET mykey='".$mykey."' WHERE > id='".$row->id."'"); > } > } > > */ or just: mysql_query("UPDATE test SET mykey=UUID()"); can't see any reason to go down the 'loop the dataset and roll your own much less random, much more likely to collide, unique value' road. sometimes less is more (although oddly more is never less ;-) just be sure to read the docs regarding charsets and indexes to make sure you don't inadvertently create a performance problem (due to unused indexes): http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_uuid > ?> >