MySQL 错误：键“PRIMARY”的重复条目“0”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18688694/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error: Duplicate entry '0' for key 'PRIMARY'
提问by Arvis Avens
I can't resolve my problem, this is the error from mysql that I'm getting:
我无法解决我的问题，这是我收到的 mysql 错误：
I can edit and update my data when I've got one record in the database but when I add two rows, I get the error.
当我在数据库中有一条记录时，我可以编辑和更新我的数据，但是当我添加两行时，我收到错误消息。
Some pictures from database
数据库中的一些图片
And when I change the row, row ID goes down to 0 and that's is a problem as I can't edit other rows.
当我更改行时，行 ID 变为 0，这是一个问题，因为我无法编辑其他行。
CREATE TABLE `dati` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`value1` varchar(255) NOT NULL,
`value2` varchar(255) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 PACK_KEYS=1
Update Code:
更新代码：
<?php // Izlabot datus datubāzē!
$titletxt = $_POST['title_edit'];
$value1 = $_POST['value1_edit'];
$value2 = $_POST['value2_edit'];
if(isset($_POST['edit'])){
$con=mysqli_connect("localhost","root","","dbname");
if (mysqli_connect_errno())
{
echo "Neizdevās savienoties ar MySQL: " . mysqli_connect_error();
}
$sql="UPDATE dati SET ID='$ID',title= '$titletxt',value1='$value1',value2='$value2' WHERE 1";
if (!mysqli_query($con,$sql))
{
die('Error: ' . mysqli_error($con));
}
echo '<script>
alert(" Ieraksts ir veiksmīgi labots! ");
window.location.href = "index.php";
</script>';
mysqli_close($con);
}
?>
From form:
从形式：
<?php
$con=mysqli_connect("localhost","root","","dbname");
if (mysqli_connect_errno())
{
echo "Neizdevās savienoties ar MySQL: " . mysqli_connect_error();
}
$result = mysqli_query($con,"SELECT * FROM dati");
while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td><input id='titled' type='text' name='title_edit' value='" . $row['title'] . "'></td>";
echo "<td><input id='value1d' type='text' name='value1_edit' value='" . $row['value1'] . "'></td>";
echo "<td><input id='value2d' type='text' name='value2_edit' value='" . $row['value2'] . "'></td>";
echo "<input type='hidden' name='id' value='" . $row['ID'] . "'>";
echo "<td><button name='edit' id='edit_btn' class='frm_btns' value='" . $row['ID'] . "'>Edit</button></td>";
echo "</tr>";
}
mysqli_close($con);
?>
It couldn't read the value of ID, as 0 was returned.
它无法读取 ID 的值，因为返回了 0。
回答by cssyphus
For those arriving at this question because of the question title (as I did), this solved my problem:
对于那些因为问题标题（正如我所做的那样）而到达这个问题的人，这解决了我的问题：
This error can indicate that the table's PRIMARY KEY is not set to AUTO-INCREMENT, (and your insert query did not specify an ID value).
此错误可能表明表的 PRIMARY KEY 未设置为 AUTO-INCREMENT （并且您的插入查询未指定 ID 值）。
To resolve:
解决：
Check that there is a PRIMARY KEY set on your table, andthat the PRIMARY KEY is set to AUTO-INCREMENT.
检查您的表上是否设置了 PRIMARY KEY ，并且PRIMARY KEY 设置为 AUTO-INCREMENT。
How to add auto-increment to column in mysql database using phpmyadmin?
回答by symcbean
The problem is that your code attempts to change every row in the data changing the primary key to the value in $ID. This is not set anywhere in your code, and presumably is being cast as 0
问题是您的代码尝试更改数据中的每一行，将主键更改为 $ID 中的值。这未在您的代码中的任何位置设置，并且可能被强制转换为 0
$sql="UPDATE `dati` SET `ID`='$ID',`title`=
'$titletxt',`value1`='$value1',`value2`='$value2' WHERE 1";
The primary key value should be sent to the form and returned so it can be processed by your code, but the value should be retained, hence....
主键值应发送到表单并返回，以便您的代码可以对其进行处理，但应保留该值，因此....
$sql="UPDATE `dati` SET `title`=
'$titletxt',`value1`='$value1',`value2`='$value2' WHERE `ID`=$ID";
You should also read up on MySQL injection - even after you've fixed the errors here, anyone can do just about anything they want with your database.
您还应该阅读有关 MySQL 注入的内容——即使您已经修复了此处的错误，任何人都可以对您的数据库做任何他们想做的事情。
回答by a14m
The problem in set ID = $ID
问题在 set ID = $ID
Try removing it so the code should be
尝试删除它，所以代码应该是
$sql="UPDATE `dati` `title`= '$titletxt',`value1`='$value1',`value2`='$value2' WHERE 1";
Be sure to change this where cause it'll update ever row with these values
一定要改变它，因为它会用这些值更新每一行
回答by Stefano Munarini
Try this:
尝试这个：
ID int(11) PRIMARY KEY AUTOINCREMENT(1,3)
回答by Eyal Sooliman
Just make sure that your primery keys are also A-I.
只需确保您的初级密钥也是 AI。
回答by Jose Carlos Ramos Carmenates
The error log like (In my case), I'm using AuroraDB:
错误日志如（就我而言），我使用的是AuroraDB：
PHP message: WordPress database error Duplicate entry '0' for key 'PRIMARY' for query INSERT INTO `date173_postmeta
PHP 消息：WordPress 数据库错误重复条目 '0' for key 'PRIMARY' for query INSERT INTO `date173_postmeta
How to fix it using MySQL Workbench:
如何使用MySQL Workbench修复它：
1- Connect at your DB, and go to the table with the issue, in my case date173_postmeta
1- 连接到您的数据库，然后转到有问题的表格，就我而言 date173_postmeta
2- Select the tools icon:
2- 选择工具图标：
3- In the windows/tab at right, select the AI checkboxand click on Apply button:
3- 在右侧的窗口/选项卡中，选中 AI 复选框并单击应用按钮：
Following the last steps my issues gone.
按照最后一步，我的问题消失了。
