SELECT LAST_INSERT_ID（）在使用prepared语句后返回0 [英] SELECT LAST_INSERT_ID() returns 0 after using prepared statement

问题描述

我使用MySQL和一个准备好的语句插入 BLOB 记录（jpeg图像）。执行完准备语句后，我发出 SELECT LAST_INSERT_ID（）并返回0.

在执行命令后放一个断点，在MySQL命令（监视器）窗口中，我发出 SELECT LAST_INSERT_ID（），它返回零。

在MySQL命令（监视器）窗口中，我发出一个SQL语句来选择所有的ID，最后一个（仅）插入的ID是1（一个）。

我使用的是：

• 服务器版本：5.1.46-社区
  MySQL社区服务器/ li>
  
• Visual Studio 2008第9版。


• MySQL连接器C ++ 1.0.5

我的表描述：

  mysql>描述picture_image_data; 
 + --------------- + ------------------ + ------ + --- -  + --------- + ---------------- + 
 |字段|类型| Null | Key |默认|额外| 
 + --------------- + ------------------ + ------ + --- -  + --------- + ---------------- + 
 | ID_Image_Data | int（10）unsigned | NO | PRI | NULL | auto_increment | 
 | Image_Data | mediumblob | YES | | NULL | | 
 + --------------- + ------------------ + ------ + --- -  + --------- + ---------------- + 
集合中的两行（0.19秒）
  
 
 
 
使用MySQL监视器的结果：
  mysql>选择ID_Image_Data 
  - > from picture_image_data; 
 + --------------- + 
 | ID_Image_Data | 
 + --------------- + 
 | 1 | 
 + --------------- + 
集合中的一行（0.00秒）
 
 mysql> SELECT LAST_INSERT_ID（）; 
 + ------------------ + 
 | LAST_INSERT_ID（）| 
 + ------------------ + 
 | 0 | 
 + ------------------ + 
集合中的1行（0.00秒）
  
 Prepared语句：
  INSERT INTO Picture_Image_Data 
 （ID_Image_Data，Image_Data）
 VALUES 
（？，
？）; 
  
上面的结果显示ID_Image_Data字段为1，但LAST_INSERT_ID为0。 
 
 
 
 
 
 
编辑
：
 h2> 
 
 
我将ID字段设置为零，并将下一个字段设置为C ++  std :: istream * 。根据 MySQL手册页 LAST_INSERT_ID（） ：
 
 
 
   LAST_INSERT_ID / code>不会更改，如果您将行的AUTO_INCREMENT列设置为非魔术值（即非NULL且不为0 的值）。 
 
 
 
由于预准备语句中的ID值为0，因此LAST_INSERT_ID（）应返回1。
 
 
 
 
 
 
我需要准备语句来获取LAST_INSERT_ID吗？ 
 
 
 
  {搜索网络显示使用自定义MySQL API，但使用PHP和更高的评论说，它需要另一个连接。） p> 
 
解决方案
看起来问题是与MySQL C ++连接器。 
 
 
  LAST_INSERT_ID（）当
的ID字段为空时返回0 ，
 
插入。
 
 
  LAST_INSERT_ID（） ID字段未指定，
 隐含插入。
 
 
 $ c> BLOB （JPEG图像）从命令行（监视器），它的工作原理：
  mysql>描述picture_image_data; 
 + --------------- + ------------------ + ------ + --- -  + --------- + ---------------- + 
 |字段|类型| Null | Key |默认|额外| 
 + --------------- + ------------------ + ------ + --- -  + --------- + ---------------- + 
 | ID_Image_Data | int（10）unsigned | NO | PRI | NULL | auto_increment | 
 | Image_Data | mediumblob | YES | | NULL | | 
 + --------------- + ------------------ + ------ + --- -  + --------- + ---------------- + 
 2行集（0.03秒）
 mysql> PREPARE blob_stmt 
  - > FROM'INSERT INTO picture_image_data（ID_Image_Data，Image_Data）VALUES（？，LOAD_FILE（？））'; 
查询OK，0行受影响（0.02秒）
语句准备
 
 mysql> SET @id = 0; 
查询OK，0行受影响（0.00秒）
 
 mysql> SET @c ='KY_hot_brown_picture.jpg'; 
查询OK，0行受影响（0.00秒）
 
 mysql> EXECUTE blob_stmt USING @id，@c; 
查询OK，1行受影响（0.15秒）
 
 mysql> SELECT LAST_INSERT_ID（）; 
 + ------------------ + 
 | LAST_INSERT_ID（）| 
 + ------------------ + 
 | 2 | 
 + ------------------ + 
 
集合中的1行（0.00秒）
  
现在，解决方法是使用SQL语句插入带预准备语句的BLOB，而不是MySQL C ++连接器API。
 
I am using MySQL and a prepared statement to insert a BLOB record (jpeg image).  After executing the prepared statement, I issue a SELECT LAST_INSERT_ID() and it returns 0.  

In my code I put a breakpoint after the execution command and in a MySQL command (monitor) window, I issue the SELECT LAST_INSERT_ID() and it returns zero.

In the MySQL command (monitor) window, I issue an SQL statement to select all the IDs and the last (only) inserted ID is 1 (one).

I am using:  


Server version: 5.1.46-community
MySQL Community Server (GPL)
Visual Studio 2008, version 9.
MySQL Connector C++ 1.0.5


My table description:  
mysql> describe picture_image_data;
+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| ID_Image_Data | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Image_Data    | mediumblob       | YES  |     | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+
2 rows in set (0.19 sec)
Results using MySQL monitor:
mysql> select ID_Image_Data
    -> from picture_image_data;
+---------------+
| ID_Image_Data |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)
The Prepared statement:
INSERT INTO Picture_Image_Data
(ID_Image_Data, Image_Data)
VALUES
    (?,
       ?);
The results above show that the ID_Image_Data field is one, but the LAST_INSERT_ID is zero.  The table is created before this statement is executed, so this is the only record in the table.



Edit:

I am setting the ID field to zero and the next field to a C++ std::istream *.  According to the MySQL Manual Page for LAST_INSERT_ID():  

The value of LAST_INSERT_ID() is not changed if you set the AUTO_INCREMENT column of a row to a non-"magic" value (that is, a value that is not NULL and not 0). 

The LAST_INSERT_ID() should return 1 since the ID value in the prepared statement is 0.



Do I need to make a prepared statement for fetching LAST_INSERT_ID?  

{Searching the web showed to use a custom MySQL API, but that used PHP and futher comments said it needs another connection).
解决方案
Looks like the issues is with the MySQL C++ connector.  

LAST_INSERT_ID() returns 0 when
the ID field is null, explicit
insert.
LAST_INSERT_ID() returns 0 when
the ID field is not specified,
implicit insert.
I tried insert the BLOB (JPEG image) from the command line (monitor), and it works:
mysql> describe picture_image_data;
+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| ID_Image_Data | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Image_Data    | mediumblob       | YES  |     | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)
mysql> PREPARE blob_stmt
    ->     FROM 'INSERT INTO picture_image_data (ID_Image_Data, Image_Data) VALUES (?, LOAD_FILE(?))';
Query OK, 0 rows affected (0.02 sec)
Statement prepared

mysql> SET @id = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @c = 'KY_hot_brown_picture.jpg';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE blob_stmt USING @id, @c;
Query OK, 1 row affected (0.15 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+

1 row in set (0.00 sec)
For now, the workaround is to use SQL statements to insert the BLOB with a prepared statement rather than the MySQL C++ Connector API.

                        
这篇关于SELECT LAST_INSERT_ID（）在使用prepared语句后返回0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！