logo标签列表

PHP MySQL插入和重复键问题

phpmysqlon-duplicate-key
3
我的代码/语法有问题吗？我不确定出了什么问题，而且我是新手。我在PHPMyAdmin中创建了一个表格，它有两列，一列是"id"，是主键/自动递增的，另一列是"steamname"。
这段代码应该将人的在线名字输入到数据库中。如果已经有记录，它应该更新最新的名称。 phpmyAdmin中表格的名称是names。
        <?php
        // Capture person's name from XML file
        $profile = simplexml_load_file('UrlGoesHere.Com/?xml=1', 'SimpleXMLElement', LIBXML_NOCDATA);
        echo (string)$profile->steamID;

        //Enter name into databse and overwrite it if same/duplicate
        $con=mysqli_connect("localhost","username","password","databaseName");
        // Check connection
        if (mysqli_connect_errno())
        {
            echo "Failed to connect to MySQL: " . mysqli_connect_error();
        }

        // Perform queries
        mysqli_query($con,"INSERT INTO names (steamname) VALUES ('$profile') ON   DUPLICATE KEY UPDATE steamname = VALUES('$profile')");
        mysqli_close($con);
        ?>

我通过在PHPMyAdmin中手动更改“steamname”行的值为“woogy”，然后运行此脚本来查看是否会更新该数据库值进行了测试...但是没有任何反应。它应该将“woogy”更新为正确的名称。
-----------更新---------------
大家好，感谢你们的建议。现在我的代码如下所示。如果还有错误，请见谅，我正在学习。
            <?php
        $profile = simplexml_load_file('http://steamcommunity.com/profiles/76561198006938281/?xml=1', 'SimpleXMLElement', LIBXML_NOCDATA);
        echo (string)$profile->steamID;


        $con=mysqli_connect("localhost","userHere","passwordHERE","DBName");
        // Check connection
        if (mysqli_connect_errno())
        {
            echo "Failed to connect to MySQL: " . mysqli_connect_error();
        }

        // Perform queries
        mysqli_query($con,"INSERT INTO names (steamname) VALUES ('$profile') ON DUPLICATE KEY UPDATE ID = LAST_INSERT_ID(ID), steamname = VALUES(steamname)");
        mysqli_close($con);
        ?>

这是运行脚本之前数据库的样子：（woogy应该更改为Chatyak） 点击此处查看图片 现在，当我运行PHP页面时，我的数据库就会发生以下变化。 运行脚本后数据库的样子 不确定为什么它没有更新 - 还有为什么有这么大的空间？
1
你需要在查询中包含 id，否则重复键将无法匹配 -> "INSERT INTO names (id, steamname) VALUES ($profile->steamId,'$profile->name') ON DUPLICATE KEY UPDATE steamname = VALUES(steamname)"。请注意，$profile->steamId/$profile->name 是您的 id 和更新后的 name 值的猜测。 - Sean
@Sean，“ID”是自增的。在这种情况下，在“INSERT”语句中不需要它。OP的INSERT查询是正确的。 - Rahul
1
@Rahul 确实，由于 id 是自动递增的，所以不需要它，但问题在于永远不会与主键匹配，因此它将始终“插入”新行，而永远不会“更新”行。必须重新考虑该答案。 - Sean
@Sean，非常正确的观点，在MySQL文档中已经得到了很好的解决。 - Rahul
2
额外的空格，除了其他值之外，可能是由于您不正确使用$profile变量而导致的；它是包含在SimpleXMLElement实例中父元素中的空格。 - user1433150
显示剩余3条评论
3个回答
2

INSERT ... ON DUPLICATE KEY 的正确语法如下，你需要在 VALUES 中提及列名称，例如 VALUES(column_name) 而不是 VALUES('$profile')。此外，在 UPDATE 的情况下，你忘记了 PK 列 ID。因为你要更新特定 ID 值的 steamname。

INSERT INTO names (steamname) 
VALUES ('$profile') 
ON DUPLICATE KEY UPDATE ID = LAST_INSERT_ID(ID), steamname = VALUES(steamname)

（或）

INSERT INTO names (steamname) 
VALUES ('$profile') 
ON DUPLICATE KEY UPDATE steamname = VALUES(steamname)

引用自MySQL文档

如果一张表包含一个AUTO_INCREMENT列，当使用INSERT ... UPDATE插入一行数据时，函数 LAST_INSERT_ID() 将返回这个AUTO_INCREMENT的值。如果该语句更新了一行数据，LAST_INSERT_ID()将无意义。但是，您可以通过使用LAST_INSERT_ID(expr) 来解决此问题。

这仍然只是插入了一行新记录，因为它生成了一个新的自增ID值。 - Bill Karwin
“@BillKarwin写的内容。ON DUPLICATE KEY UPDATE ID = LAST_INSERT_ID(ID)是一个毫无意义的循环。INSERT INTO names (steamname)语句总是会生成一个新的id，因此永远不会发生更新，即使它以某种方式发生了更新，LAST_INSERT_ID()也没有任何意义。” - user1433150
我在我的回答中进行了编辑，详细阐述了我认为你的回答存在的问题，因为这个问题太长了，无法在评论中表达清楚。 - user1433150
1
你不能使用INSERT ON DUPLICATE KEY UPDATE（IODKU）来更新而不是插入新行，除非你尝试插入与现有主键或唯一键列冲突的值。
因为在这个INSERT中你没有为ID指定任何值，它将始终递增自动递增主键并插入新行。
如果你想要INSERT替换现有行的steamname，你必须在INSERT中指定ID值。

关于您的第一条评论：

我看了您的样例。创建一个新行并不奇怪。因为您在INSERT中没有指定ID，所以它生成了一个新的自增ID值，因此自然地创建了一行。它无法确定您要替换哪一行。

这是一个演示：

mysql> create table names (id serial primary key, steamname text);

mysql> insert into names (steamname) values ('Woogy') 
  on duplicate key update ID = LAST_INSERT_ID(ID), steamname = VALUES(steamname);

忽略 ID = LAST_INSERT_ID(ID) 部分，这没有影响。它是一个无操作（no-op）。@Rahul 建议使用它，但不幸的是他或她是错误的。我将在随后的测试中删除该术语。
那么在这个 INSERT 中会发生什么？您为 steamname 指定了一个值，但没有为 ID 指定值。因此，MySQL 为 ID 生成一个新值。那成为新行的主键值，并插入该行，其中 steamname 为 'Woogy'。
mysql> select * from names;
+----+-----------+
| id | steamname |
+----+-----------+
|  1 | Woogy     |
+----+-----------+

下一步，我们尝试将名称更改为“Chatyak”：

mysql> insert into names (steamname) values ('Chatyak') 
  on duplicate key update steamname = VALUES(steamname);

这个更改应用在哪一行？INSERT没有指定ID值，因此MySQL会自动递增另一个新的ID值。该值是新行的主键，这就是获得steamname“Chatyak”的行。
mysql> select * from names;
+----+-----------+
| id | steamname |
+----+-----------+
|  1 | Woogy     |
|  2 | Chatyak   |
+----+-----------+

这意味着，如果您让自动递增生成一个新的ID值进行插入，那么您永远无法触发ON DUPLICATE KEY部分。每次插入都会导致新行的生成。
正如我上面所说，除非您尝试插入与已存在于表中的某行的主键或唯一键冲突的值，否则ON DUPLICATE KEY不起作用。但在这种情况下，您没有发生冲突，而是始终生成一个新的ID值。因此，它会插入一行新数据。
如果steamname上有一个UNIQUE KEY约束，则这将是触发ON DUPLICATE KEY的另一个机会。但是，它仍然不能做到您想要的。
mysql> alter table names add unique key (steamname(20));

如果您试图插入已经存在的蒸汽名称，它不会插入新行而是会更新现有行。
mysql> insert into names (steamname) values ('Chatyak') 
  on duplicate key update ID = LAST_INSERT_ID(ID), steamname = VALUES(steamname);
Query OK, 0 rows affected (0.02 sec)

请注意，它显示该语句插入了“0行”。 但这仍不能让您更改现有的steam名称。如果指定一个新名称，它只会插入一行新数据。如果您指定的名称已被使用，则是重复项，因此它不会插入新行，但也不会更改该名称。 如果您让它自动增加新的ID值，您如何希望INSERT语句应用于现有行？您认为它应与哪个现有行冲突？

关于您的第二个评论：

您不需要一个次要的唯一键，我只是试图向您展示IODKU的工作原理。

在考虑SQL语法之前，您必须考虑问题的逻辑。换句话说，如果您指定一个新名称，您想要替换哪一行？

例如：

mysql> insert into names (id, steamname) values (123, 'Chatyak') 
  on duplicate key update steamname = VALUES(steamname);

这将起作用，因为如果存在一个 ID 为 123 的行，那么此 INSERT 将导致重复键冲突，从而触发 ON DUPLICATE KEY 子句。但在此示例中我们从哪里获取值 123 呢？假设您在应用程序中知道要更新的 id。是否有相应的变量？它是否对应于用户会话数据？
你好，Bill - 我似乎仍然卡住了。请查看我在原始帖子中的编辑和图片。 - Chatyak
比尔...感谢你的教程。我想你是在尝试向我展示关于制作表的思考方式，是吗？我明白了，除非在INSERT语句中实际声明一个ID值...否则默认情况下将始终创建一行新记录，因为主键没有重复检查的内容，对吗？那么我的问题是...在插入语句中声明ID的正确公式是什么？我尝试添加你建议的唯一键，但不确定它到底做了什么，也不知道如何从我的表中删除它。此外 - 如果我们有主键，为什么还需要唯一键？ - Chatyak
你好，比尔。感谢你抽出时间来。我已经测试了你的最新代码，它运行得非常好。更新此页面的网页将是PageName.php，并且我会设置一个定时器每隔几个小时运行它。ID将手动输入，因为只跟踪大约30个人左右。我遇到的问题是当我用'$profile'替换'Chatyak'时。它确实可以工作（因为id值在那里：)），但是steamname列现在又变成了那个大的空白空间...可能与用户在上面提到的我的变量使用不当有关。 - Chatyak
是的，那是一个与SQL问题无关的单独问题。您需要从XML中提取单个元素，就像@user1433150展示的那样。您还应该考虑使用trim()来删除空格。 - Bill Karwin
谢谢Bill。好的，我只是提取一个元素，因为当它打印到网页上时，它确实显示了我想要的内容...问题可能出在传输到数据库的过程中。这个trim应该在这行之后成为一个单独的变量吗？echo (string)$profile->steamID; - Chatyak
显示剩余2条评论
1

**************************编辑：*************************

回应@Rahul提供的答案。这个声明：

INSERT INTO names (steamname) 
VALUES ('$profile') 
ON DUPLICATE KEY UPDATE ID = LAST_INSERT_ID(ID), steamname = VALUES(steamname)

虽然从技术上讲是有效的，但无论如何都不会影响任何行。实际上，它有很多问题，很难列举出来（这也是进行此编辑的原因）。

MySQL文档中在this page底部给出的ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id)用法是一个示例，说明如何捕获已经更新而不是插入的行的id值，否则LAST_INSERT_ID()（没有参数）将返回最后插入的id，在更新的情况下，这对于相关行来说并没有意义。

因此，如果上述语句曾经起作用——尽管它不可能——那么它与以下语句实际上没有任何区别：

INSERT INTO names (steamname) 
VALUES ('$profile') 
ON DUPLICATE KEY UPDATE steamname = VALUES(steamname)

除此之外，您还需要将该行的id值更新为其已经存在的值。

除此之外，语句永远不会起作用的原因是，除非有重复键，否则ON DUPLICATE KEY UPDATE子句永远不会被评估。当我们尝试将steamname更新为等于steamname的值时，与没有ON DUPLICATE KEY UPDATE子句时发生的相同错误将会发生，即相同的值在第一次触发ON DUPLICATE KEY UPDATE子句时。

*****************************编辑结束***********************************

首先：当您尝试在查询中使用$profile时，它仍然是SimpleXMLElement对象的实例。因此，将调用SimpleXMLElement::__toString方法，但是SimpleXMLElement::__toString方法仅返回直接位于第一个元素内部的文本内容，并且不会返回任何后代的文本。因此，如果您有一些元素<steamname>和一些嵌套在由调用simplexml_load_file返回的父元素中的元素<steamID>，它们的值将不会被返回。请参见manual。

其次：使用带有UPDATE子句的VALUES函数时，正确的语法是像这样引用列名称：DUPLICATE KEY UPDATE colname=VALUES(some_col_name)。如果要使用显式值，则语法为DUPLICATE KEY UPDATE colname='value'。请参见manual。

第三点：由于您将id设置为AUTO_INCREMENT，因此在未显式插入id值或使用WHERE子句指定id值的情况下，插入到names表中的任何行都将创建一个新行，并具有新的自动递增id值，无论您是否使用DUPLICATE KEY UPDATE，因为AUTO_INCREMENT将确保您永远不会生成重复的键。

我认为您可以改用mysql的REPLACE语句。它是适合您所描述的需求的最简单的查询。

REPLACE INTO `names` (`id`,`steamname`)
VALUES ($id, $steamname)

其中$id是$profile->steamID，而$steamname是该行steamname列的新值（或旧值）。

REPLACE语句将简单地DELETE然后INSERT，如果id值已经存在。但由于您正在插入相同的id，因此效果是更新包含匹配的id值的行中的steamname值。请注意，如果在同一表中除了id和steamname之外还有其他列，并且您没有在REPLACE语句的VALUES中包括它们的值，则这些值将会丢失。

第四点：对于调试这些问题，重要的是a) 知道您实际传递给查询字符串的值。 b) 知道您使用的基本查询语法是否正确，除了任何特定的值。

最后，让自己的工作变得简单；既然您已经使用了SimpleXMLElement中的对象符号来访问元素值，为什么不在mysqli中也使用它呢？尝试一下吧。这是未经测试，但我认为它会对您有所帮助。
  <?php
    // Capture person's name and id from XML file
    $profile = simplexml_load_file('UrlGoesHere.Com/?xml=1', 'SimpleXMLElement', LIBXML_NOCDATA);
      $profile_id = (string)$profile->steamID;
      $profile_name = (string)$profile->steamName;

    //connect to database
    $mysqli = new mysqli("localhost","username","password","databaseName");

    // Check connection
    if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    //build query
    //IMPROTANT: this will delete other fields(if any)
    //> in the row, unless they are also refilled by this statement
    $q = ("
    REPLACE INTO `names` (`id`,`steamname`)
    VALUES (?, ?)
    ");

    //uncomment to debug query
      //echo "<pre>$q</pre>";

    //uncomment to debug values
      //echo "<pre>profile_name: \n $profile_name</pre>";
      //echo "<pre>profile_id: \n $profile_id</pre>";

    //prepare statement using above query
    $stmt = $mysqli->prepare($q);

    //fill in '?'s with actual values
    //first argument is the data types 
    //'i' [integer] 's' [string]
    //follownig aruments fill in '?'s in order
    $stmt->bind_param('is', $profle_id, $profile_name);

    // execute statement
    $stmt->execute();

    //close statement
    $stmt->close();
    //close connection
    $mysqli->close();
 ?>
嗨，用户 - 谢谢您的输入和想法。我会确保测试这个代码的。我只是刚开始学习所有这些，所以您的代码对我有点困惑。我知道我们把“replace”做成一个变量... 然后在准备好的语句中使用该变量。stmt有什么含义吗，还是只是您选择使用的随机变量？我还在下面留了一条评论，就比尔的答案而言，他的代码确实可以工作，但用'$profile'替换'Chatyak'又会留下一个巨大的空格... 可能是由于您参照不当而造成的。这段代码是否修复了这个问题？ - Chatyak
$stmt 是一个 mysqli 预处理语句。当我们调用 $mysqli = new mysqli([...]); 时，会返回一个 mysqli 类的对象。对象基本上只是一组变量和函数的集合，它们可以一起工作，因为它们都知道彼此的存在。您可以通过 $mysqli->memberName 这样的方式访问这些“成员”。$mysqli->prepare($q); 返回另一个相关的对象，具有不同的成员，例如 bind_param、execute 和 close。因此，在将该对象捕获到 $stmt 变量之后，我们访问其成员：$stmt->bind_param()。您已经在使用 $profile->steamID。 - user1433150
看起来你有点难以应付。你需要投入一些时间去了解更多自己代码中发生的事情，否则你想让它按照预期工作的机会就很渺茫。无论你是否使用我的代码，如果你不能理解1-4点，那么你基本上是在盲目地摸索。我建议你利用原本玩“捉迷藏”的时间来学习一些东西；这样你会更快地到达目标，并且会有更多的收获。 - user1433150
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接