mysql_query()的行为与命令行上的mysql不同

Question

我有下表：

insert_test | CREATE TABLE insert_test (
  id int(11) NOT NULL AUTO_INCREMENT,
  closed int(11) NOT NULL DEFAULT '0',
  user int(11) DEFAULT '-1',
  level int(11) DEFAULT '-1',
  comment text,
  count int(11) DEFAULT '1',
  PRIMARY KEY (id,closed),
  UNIQUE KEY user (user,level,closed)
)

当我在命令行上运行以下命令时：

INSERT INTO db.insert_test (closed, user, level, comment, count) VALUES (0, 1, 50, 'First insert', 1) ON DUPLICATE KEY UPDATE comment=VALUES(comment), count=count+1;
INSERT INTO db.insert_test (closed, user, level, comment, count) VALUES (0, 1, 75, 'Second insert', 1) ON DUPLICATE KEY UPDATE comment=VALUES(comment), count=count+1;

...this是我得到的输出：

+----+--------+------+-------+---------------+-------+
| id | closed | user | level | comment       | count |
+----+--------+------+-------+---------------+-------+
|  9 |      0 |    1 |    50 | First insert  |     1 |
| 10 |      0 |    1 |    75 | Second insert |     1 |
+----+--------+------+-------+---------------+-------+

当我使用mysql_query()运行这些命令时，得到的结果如下所示。

+----+--------+------+-------+---------------+-------+
| id | closed | user | level | comment       | count |
+----+--------+------+-------+---------------+-------+
| 11 |      0 |    1 |    50 | Second insert |     2 |
+----+--------+------+-------+---------------+-------+

因此，当我使用mysql_query()函数时，查询正在更新，而不是插入新行，因为这两个插入具有不同的级别，所以它们是unique...right？是我错了，还是这里发生了什么？

编辑:我使用的代码片段如下：

char* query = "INSERT INTO db.insert_test (closed, user, level, comment, count) VALUES (0, 1, 50, 'First insert', 1) ON DUPLICATE KEY UPDATE comment=VALUES(comment), count=count+1;";
char* query2 = "INSERT INTO db.insert_test (closed, user, level, comment, count) VALUES (0, 1, 75, 'Second insert', 1) ON DUPLICATE KEY UPDATE comment=VALUES(comment), count=count+1;";
mysql_query( link, query );
mysql_query( link, query2 );

我知道链接是正确的，并且这段代码正在运行，因为它确实可以工作(即运行查询)，除了它正在更新而不是插入的问题。

Answer 1

我不能重现任何问题。我刚刚使用上面的表格(感谢您创建了一个简单的测试用例)和一个快速的C程序对此进行了测试。它按照预期工作，插入了两行。使用MySQL 8.0.0-dmr。

#include <my_global.h>
#include <mysql.h>
#include <string.h>

int main(int argc, char **argv)
{
  MYSQL *con = mysql_init(NULL);

  if (con == NULL)
  {
      fprintf(stderr, "%s\n", mysql_error(con));
      exit(1);
  }

  mysql_real_connect(con, "localhost", "root", "password", "test", 0, NULL, 0);

  if (strlen(mysql_error(con)))
  {
      fprintf(stderr, "%s\n", mysql_error(con));
      mysql_close(con);
      exit(1);
  }

  char* query = "INSERT INTO insert_test (closed, user, level, comment, count) VALUES (0, 1, 50, 'First insert', 1) ON DUPLICATE KEY UPDATE comment=VALUES(comment), count=count+1;";
  char* query2 = "INSERT INTO insert_test (closed, user, level, comment, count) VALUES (0, 1, 75, 'Second insert', 1) ON DUPLICATE KEY UPDATE comment=VALUES(comment), count=count+1;";

  mysql_query(con, query);

  if (mysql_errno(con))
  {
      fprintf(stderr, "Error: %s\n", mysql_error(con));
      mysql_close(con);
      exit(1);
  }

  printf("Rows: %ld\n", (long) mysql_affected_rows(con));

  if (mysql_warning_count(con))
  {
      fprintf(stderr, "Warnings: %s\n", mysql_error(con));
      mysql_close(con);
      exit(1);
  }

  mysql_query(con, query2);

  if (mysql_errno(con))
  {
      fprintf(stderr, "Error: %s\n", mysql_error(con));
      mysql_close(con);
      exit(1);
  }

  printf("Rows: %ld\n", (long) mysql_affected_rows(con));

  if (mysql_warning_count(con))
  {
      fprintf(stderr, "Warnings: %s\n", mysql_error(con));
      mysql_close(con);
      exit(1);
  }

  mysql_close(con);
  exit(0);
}

输出显示：

Rows: 1
Rows: 1

表中的数据：

mysql> select * from insert_test;
+----+--------+------+-------+---------------+-------+
| id | closed | user | level | comment       | count |
+----+--------+------+-------+---------------+-------+
|  5 |      0 |    1 |    50 | First insert  |     1 |
|  6 |      0 |    1 |    75 | Second insert |     1 |
+----+--------+------+-------+---------------+-------+

多次运行该程序会使两行的count列递增。当发生这种情况时，它会显示每个语句受影响的2行，这是正常的。

mysql> select * from insert_test;
+----+--------+------+-------+---------------+-------+
| id | closed | user | level | comment       | count |
+----+--------+------+-------+---------------+-------+
| 23 |      0 |    1 |    50 | First insert  |     2 |
| 24 |      0 |    1 |    75 | Second insert |     2 |
+----+--------+------+-------+---------------+-------+