sql具有多个值的列(cpp文件中的查询实现) [英] sql Column with multiple values (query implementation in a cpp file )
问题描述
我使用这链接。
我已将我的cpp文件与Eclipse连接到具有3个表的数据库(两个简单的表
Person
和项
和第三个 PersonItem
连接它们)。在第三个表中,我使用一个简单的主键,然后使用两个外键,如:
CREATE TABLE PersonsItems(PersonsItemsId int not null auto_increment主键
Person_Id int not null,
Item_id int not null,
约束fk_Person_id外键(Person_Id)引用Person(PersonId),
约束fk_Item_id外键(Item_id)引用Items(ItemId));
那么,在c中嵌入sql我想要一个Person有多个项目。
我的代码:
mysql_query(connection,\
INSERT INTO PersonsItems(PersonsItemsId,Person_Id,Item_id)VALUES(1,1,5),(1,1,8););
printf(%ld PersonsItems Row(s)Updated!\\\
,(long)mysql_affected_rows(connection));
//选择新插入的记录。
mysql_query(connection,\
SELECT Order_id FROM PersonsItems);
//返回数据行的资源结构。
resource = mysql_use_result(connection);
//获取多个结果
while((result = mysql_fetch_row(resource))){
printf(%s%s\\\
,result [0] result [1]);
}
我的结果是
-1 PersonsItems行更新!
5
但 VALUES(1,1,5) ,(1,1,8);
我想要
-1 PersonsItems行已更新!
5 8
可以告诉我为什么这不会发生?
我怀疑这是因为你的第一个插入失败,出现以下错误:
键'PRIMARY'的重复条目'1'
因为您尝试在 PersonsItemsId
中插入两次 1
主键因此必须是唯一的(它也是auto_increment所以没有必要指定一个值);
这是为什么行受影响是-1,为什么在这行:
printf(%s%s\\\
,result [0],result [1] );
您只能看到 5
在已插入值(1,1,5)
后,语句失败,因此表中仍有一行数据。
我想要获得您期望的行为,您需要使用 ON DUPLICATE KEY UPDATE 语法:
INSERT INTO PersonsItems(PersonsItemsId,Person_Id, order_id)
VALUES(1,1,5),(1,1,8)
ON DUPLICATE KEY UPDATE Person_id = VALUES(person_Id),Order_ID = VALUES(Order_ID);
或者不要指定personsItemsID的值,让auto_increment :
INSERT INTO PersonsItems(Person_Id,order_id)
VALUES(1,5),
I am using this link.
I have connected my cpp file with Eclipse to my Database with 3 tables (two simple tables
Person
and Item
and a third one PersonItem
that connects them). In the third table I use one simple primary and then two foreign keys like that:
CREATE TABLE PersonsItems(PersonsItemsId int not null auto_increment primary key,
Person_Id int not null,
Item_id int not null,
constraint fk_Person_id foreign key (Person_Id) references Person(PersonId),
constraint fk_Item_id foreign key (Item_id) references Items(ItemId));
So, then with embedded sql in c I want a Person to have multiple items.
My code:
mysql_query(connection, \
"INSERT INTO PersonsItems(PersonsItemsId, Person_Id, Item_id) VALUES (1,1,5), (1,1,8);");
printf("%ld PersonsItems Row(s) Updated!\n", (long) mysql_affected_rows(connection));
//SELECT newly inserted record.
mysql_query(connection, \
"SELECT Order_id FROM PersonsItems");
//Resource struct with rows of returned data.
resource = mysql_use_result(connection);
// Fetch multiple results
while((result = mysql_fetch_row(resource))) {
printf("%s %s\n",result[0], result[1]);
}
My result is
-1 PersonsItems Row(s) Updated!
5
but with VALUES (1,1,5), (1,1,8);
I would like that to be
-1 PersonsItems Row(s) Updated!
5 8
Can somone tell me why is this not happening? Kind regards.
I suspect this is because your first insert is failing with the following error:
Duplicate entry '1' for key 'PRIMARY'
Because you are trying to insert 1
twice into the PersonsItemsId
which is the primary key so has to be unique (it is also auto_increment so there is no need to specify a value at all);
This is why rows affected is -1, and why in this line:
printf("%s %s\n",result[0], result[1]);
you are only seeing 5
because the first statement failed after the values (1,1,5)
had already been inserted, so there is still one row of data in the table.
I think to get the behaviour you are expecting you need to use the ON DUPLICATE KEY UPDATE syntax:
INSERT INTO PersonsItems(PersonsItemsId, Person_Id, order_id)
VALUES (1,1,5), (1,1,8)
ON DUPLICATE KEY UPDATE Person_id = VALUES(person_Id), Order_ID = VALUES(Order_ID);
Or do not specify the value for personsItemsID and let auto_increment do its thing:
INSERT INTO PersonsItems( Person_Id, order_id)
VALUES (1,5), (1,8);
这篇关于sql具有多个值的列(cpp文件中的查询实现)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!