我正在创建一个自动递增的数据库。我犯了一些错误,所以我不得不删除并在数据库的中间插入几个id。现在,每次我在表中插入新的id数据时,尽管明确表示了我希望将新数据分配给哪个id,但它仍然将新数据指定给一个完全错误的id号。例如,我有ID为1-20的数据。我删除了5-20的数据,留下id 1-4。现在,当我插入一个新的ID时,我希望它是5,而不是21。我如何使用终端或php(如下)来完成这项工作?
<?php
// 1. Create a database connection
$dbhost = "127.0.0.1";
$dbuser = "widget_cms";
$dbpass = "xxxxxxx";
$dbname = "widget_corp";
$connection = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
// Test if connection failed. If yes, exit.
if(mysqli_connect_errno()) {
die("Database connection failed: " .
mysqli_connect_error() .
" (" . mysqli_connect_errno() . ")"
);
}
$id = 5;
$menu_name = "New ID";
$position = 5;
$visible = 1;
// 2. Perform database query
$query = "UPDATE subjects SET ";
$query .= "menu_name = '{$menu_name}', ";
$query .= "position = '{$position}', ";
$query .= "visible = '{$visible}' ";
$query .= "WHERE id = '{$id}' ";
$result = mysqli_query($connection, $query);
if ($result && mysqli_affected_rows($connection) == 1) {
// Success. Usually redirects to some other page
echo "<h1>Success!</h1>";
} else {
die("Database query error. " . mysqli_error($connection));
}
?>
您可以使用以下MySQL命令:
ALTER TABLE subjects AUTO_INCREMENT = 5
但正如Mark Baker在评论中指出的那样:"自动增量是针对唯一id的,而不是连续值。"因此,您的代码不应该依赖于连续值。