MySQL在两个表中修正自动增量缺口 [英] MySQL fixing autoincrement gaps in 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
自动递增的 id_image 。在第二个表中， id_image 是指第一个表中的 id_image ，其中有两个ID
注意：此表是理论上的。我不知道在哪里的差距是正确的，或者是否有甚至多个差距。我知道的是，第一个值是 1 ，最后一个值高于总行数。
现在，我想修正这个缺口。
在说你的差距没有关系，如果他们这样做，是坏的数据库设计，让我告诉你; 我同意你的意见。
但是，我处理的是一个（无望的后端）第三方开源系统我需要将大量的没有交叉引用ID的现有数据导入到多个表中。我可以确保相同的数据在整个系统的每个表中获得匹配的ID是唯一的方法是顺序输入，这意味着我不能有间隙。
现在我需要做的是：
- 修复
id_image
- 编辑第一个表中的<$ c>列，以便最后一个值与行计数匹配。
我如何开始这样做呢？我理解这可能是在MySQL查询语言的能力之外，所以PHP答案也是可以接受的。谢谢！ ：）
解决方案这里的基本思想是首先找到所有的缺口，以确定需要减少每个id的数量。然后，您必须遍历这两个表并应用减量。 （您需要添加：host，db，user，pass和实际表名）
$ b $ pdo = new PDO（'mysql：host = HOST; dbname = DB'，'user'，'pass'）;
$ pdo-> beginTransaction（）;
//迭代第一个表中的所有id
$ 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异常（'表中没有行'）;
}
$ lastId = $ id;
$ gaps = array（）;
//查找所有的缺口
while（$ stmt-> fetch（PDO :: FETCH_BOUND））{
if（$ id！=（$ lastId + 1） ）{
$ gaps [] = $ id;
}
$ lastId = $ id;
}
if（！isset（$ gaps [0]））{
throw new Exception（'No gaps found'）;
}
//对于每个缺口，通过减去
来更新从最后一个缺口到该缺口的范围//从id中获得的缺口数量
$ lastGap = $ gaps [0];
for（$ i = 1; $ i
$ 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];
}
//最后，修正最后找到的间隙和表结尾之间的差距
$ 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
））;
//验证一切正确
$ 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'）; //永远不应该抛出
}
$ lastId = $ id;
while（$ stmt-> fetch（PDO :: FETCH_BOUND））{
if（$ id！=（$ lastId + 1））{
throw new Exception 'ids'。$ lastId。'和'。$ 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'）; // should not 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表二误差ids'）;
}
if（$ ids [0]！== $ lastId）{
throw new Exception（'id 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（） / code>，返回所有ids的列表，以便您可以验证操作是否按预期工作。如果没有，你可以回滚，如果没有发生。代码现在检查自己，如果第一个表是正确的顺序。I have two tables like so;
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 34There is a gap in the autoincremented
id_imagein the first table. In the second table, theid_imagerefers to theid_imagein the first table, and there's two of each ID in there.Notice: This table is theoretical. I have no idea where the gap is exactly, or whether or not there are even multiple gaps. All I know is that the first value is
1and the last value is higher than the total row count.Now, I'd like to fix this gap.
Before you say that the gaps don't matter and if they do, it's bad database design, let me tell you; I agree with you.
However, what I'm dealing with is a (hopelessly rear end backwards) third-party open source system to which I need to import a huge amount of existing data that doesn't have cross-referenceable IDs into multiple tables. The only way I can make sure that the same data gets a matching ID in every table throughout the system is to input it sequentially, and that means I can't have gaps.
So what I do now need to do is;
- Fix the gap in the
id_imagecolumn in the first table, so that the last value matches with the row count. - Edit the
id_imagecolumn in the second table so that its value corresponds to the same row is corresponded to before the gap fix.
How would I begin to do this? I understand that this might be outside the capabilities of the MySQL query language, so PHP answers are also acceptable. Thanks! :)
解决方案The basic idea here is to find all of the gaps first to determine how much you need to decrement each id. Then, you have to iterate through both tables and apply the decrement. (You'll need to add: host, db, user, pass, and the actual table names)
try { $pdo = new PDO('mysql:host=HOST;dbname=DB', 'user', 'pass'); $pdo->beginTransaction(); // Iterate through all id's 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); }Important:
You might want to throw this in a file and run via the CLI:php -f gapfix.phpand include a query before$pdo->commit()that returns a list of all the ids so you can verify the operation worked as expected. If it didn't, you can roll it back as if nothing happened.The code now checks for itself if the first table is in the right order. It doesn't however check the second table yet.All checking has been implemented!这篇关于MySQL在两个表中修正自动增量缺口的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
