Resequence a MySQL column

Relates to MySQL and PEAR

The other day I was asked if I knew a way to resequence a column in a MySQL table. For example if a record were removed from mid-table, how to collapse all the ids above that record to avoid gaps. Of course this would be highly undesirable and potentially destructive if relationships exist, but made an interesting little problem.

The initial solution I suggested, utilising the PEAR_DB package as abstraction layer for the PHP MySQL API, was as follows:

$id = {num}; // the id of the record that was deleted
$pr = $db->prepare('UPDATE table_name SET col_name = ! WHERE col_name = !');
do {
  $res = $db->execute($pr, array($id, ++$id));  
  if (DB::isError($res)) {
    // handle error
} while ($db->affectedRows() > 0);

Errors are trapped to allow break-out from the loop, and, since $db->affectedRows() can return -1 if the query fails, look for at least one (will be only one per loop execution) affected row.

While this seemed like a nice simple solution, it doesn't account for a column that AUTO_INCREMENTs, so assumes that values for col_name are entered manually. Then, courtesy of the MySQL cookbook, I stumbled across a more efficient solution that takes into account just that by utilising the DDL component of the MySQL query language:

$query = "ALTER TABLE table_name "
       . "DROP col_name, "
       . "AUTO_INCREMENT = 1";
$res = @mysql_query($query, $conn);
if (!$res) { // handle errors }

For simplicity, this just uses the standard PHP MySQL API. This will recreate col_name placing it first in the schema order (FIRST). Thus recreating the sequence starting the increment from 1 (AUTO_INCREMENT = 1 - prior to 3.23.39 only). Finally, there is no need to redefine the PRIMARY KEY if it is col_name, since MySQL does not drop the key during the execution of a single ALTER statement.

Personally I cannot think of any reason I would have to resequence a column, especially if it were the PRIMARY KEY, but nice to know it is possible?

Posted on Saturday, Apr 16, 2005 at 14:51:16.

Comments on Resequence a MySQL column (0)

Breadcrumbs Trail

[ Home ] -> TW Blog -> Apr 05 -> Resequence a MySQL column
Site Map

The Severn Solutions website achieves the following standards:

[ XHTML 1.0 ] [ CSS 2 ] [ WAI AA ] [ Bobby AA ]

Page compiled in 0.006 seconds