Geek asks and answers

MySQL fix auto-increment spaces in two tables

I have two tables:

id_image   foo    bar
1          3      5
2          8      1
3          17     88
7          14     23
8          12     9


id_image   bar    foo
1          2      3
1          5      6
2          18     11
2          10     12
3          8      21
3          17     81
7          29     50
7          1      14
8          10     26
8          27     34

The first table has a space in autoincremented id_image. The second table id_imagerefers to id_imagethe first table, and there are two of each identifier.

Note: This table is theoretical. I have no idea where the gap is exactly, or whether there are even multiple spaces. All I know is that the first value 1, and the last value is greater than the total number of rows.

Now I would like to correct this gap.

Before saying that spaces don't matter, and if they do, this is a bad database design, let me tell you; I agree with you.

, , - ( ) , , . , , - , , .

, :

  • id_image , .
  • id_image , , .

? , MySQL, PHP . !:)

+5
3

, , , . . ( : host, db, user, pass )

try {
    $pdo = new PDO('mysql:host=HOST;dbname=DB', 'user', 'pass');

    $pdo->beginTransaction();

    // Iterate through all id in the first table
    $stmt = $pdo->exec('SELECT image_id FROM TableOne ORDER BY image_id ASC');
    $stmt->bindColumn('image_id', $id);

    if(!$stmt->fetch(PDO::FETCH_BOUND)) {
        throw Exception('No rows in table');
    }

    $lastId = $id;
    $gaps = array();

    // Find all the gaps
    while($stmt->fetch(PDO::FETCH_BOUND)) {
        if($id != ($lastId + 1)) {
            $gaps[] = $id;
        }

        $lastId = $id;
    }


    if(!isset($gaps[0])) {
        throw new Exception('No gaps found');
    }

    // For each gap, update the range from the last gap to that gap by subtracting
    // the number of gaps there has been from the id
    $lastGap = $gaps[0];

    for($i = 1; $i < count($gaps); $i++) {
        $stmt = $pdo->prepare('UPDATE TableOne SET image_id = image_id - :i WHERE image_id BETWEEN :lastGap AND :gap');
        $stmt->execute(array(
            ':i' => $i,
            ':lastGap' => $lastGap,
            ':gap' => $gaps[$i]
        ));

        $stmt = $pdo->prepare('UPDATE TableTwo SET image_id = image_id - :i WHERE image_id BETWEEN :lastGap AND :gap');
        $stmt->execute(array(
            ':i' => $i,
            ':lastGap' => $lastGap,
            ':gap' => $gaps[$i]
        ));

        $lastGap = $gaps[$i];
    }

    // Finally, fix the gap between the last found gap and the end of the table
    $stmt = $pdo->prepare('UPDATE TableOne SET image_id = image_id - :i WHERE image_id BETWEEN :lastGap AND :gap');
    $stmt->execute(array(
        ':i' => $i,
        ':lastGap' => $lastGap,
        ':gap' => $gaps[$i]
    ));

    $stmt = $pdo->prepare('UPDATE TableTwo SET image_id = image_id - :i WHERE image_id BETWEEN :lastGap AND :lastId');
    $stmt->execute(array(
        ':i' => $i,
        ':lastGap' => $lastGap,
        ':lastId' => $lastId
    ));

    // Verify everything is correct
    $stmt = $pdo->exec('SELECT image_id FROM TableOne ORDER BY image_id ASC');
    $stmt->bindColumn('image_id', $id);

    if(!$stmt->fetch(PDO::FETCH_BOUND)) {
        throw new Exception('No rows'); // Should never be thrown
    }

    $lastId = $id;

    while($stmt->fetch(PDO::FETCH_BOUND)) {
        if($id != ($lastId + 1)) {
            throw new Exception('There was an error between ids ' . $lastId . ' and '. $id);
        }

        $lastId = $id;
    }

    $stmt = $pdo->exec('SELECT image_id FROM TableTwo ORDER BY image_id ASC');
    $stmt->bindColumn('image_id', $id);

    if(!$stmt->fetch(PDO::FETCH_BOUND)) {
        throw new Exception('No rows in table two'); // Shouldn't hit this
    }

    $lastId = $id;
    $ids = array($id);

    while($stmt->fetch(PDO::FETCH_BOUND)) {
        $ids[] = $id;

        if(count($ids) == 2) {
            if($ids[0] !== $ids[1]) {
                throw new Exception('Table two error on ids ');
            }

            if($ids[0] !== $lastId) {
                throw new Exception('Table two error on id gapfix');
            }

            $lastId = $ids[0];
            $ids = array();
        }
    }

    $pdo->commit();
} catch(Exception $e) {
    $pdo->rollBack();

    var_dump($e);
}

: , CLI: php -f gapfix.php $pdo->commit(), , , . , , . , . , . !

+1
ALTER TABLE table2
ADD FOREIGN KEY FK_IMAGE (id_image)
REFERENCES table1 (id_image)
ON DELETE CASCADE
ON UPDATE CASCADE;

SET @currentRow = 0;

UPDATE table1 INNER JOIN (
    SELECT @currentRow := @currentRow + 1 AS id_image_new, id_image AS id_image_old
    FROM table1
    ORDER BY id_image ASC) t on t.id_image_old = table1.id_image
SET table1.id_image = t.id_image_new;

ALTER TABLE table1 AUTO_INCREMENT = 1;

FK .

, mysql , , . , (), .

+3

.

, ​​ , Id_Image int, rownumber

pseudo row_number ,

Insert into NewTable
Select id_image,foo,bar,@RowNumber := @RowNumber + 1 order by id_image.

, , . 1, , , , .

, ?

, , , . RowNumber , Id_Image. Mysql ....

+1

More articles:


All Articles