php - SQL - 根据查询中的结果添加标志 - 最佳实践？

Question

我在 mySQL 数据库中有三个表:

tbl_user 和 tbl_item 和 tbljn_userItem (包含前两个主键的表，处于多对多关系。 )

我想创建一个 SQL 语句，该语句将返回 tbl_item 中所有项目的列表，并带有一个标志值，该标志值标识分配给特定 userID 的项目(tbl_user 主键)。

本质上是以下组合/衍生物:

SELECT tbl_item.*
FROM  tbl_item, tbljn_userItem
WHERE tbljn_useritem.userID=tbl_user.userID

和

SELECT * FROM tbl_item

SQL 不是我的母语，所以如果这是一个明显的问题，我很抱歉。

tbl_user

|- userID -|- firstName -|- lastName -|- streetAddress -|- city -|

tbl_item

|- itemID -|- name -|- description -|- category -|- cost -|

tbljn_userItem

|- userID -|- itemID -|

因此，我会给 SQL 语句提供 userID，它希望返回(示例):

|- itemID -|- name -|- description -|- category -|- cost -|- isOwned -|
|- itemID -|- name -|- description -|- category -|- cost -|- isOwned -|
|- itemID -|- name -|- description -|- category -|- cost -|- isOwned -|
|- itemID -|- name -|- description -|- category -|- cost -|- isOwned -|

其中 isOwned 是一个 bool 值，表示该项目的 itemID 列在 tbljn_userItem 中，与传递的 userID.

我意识到我可以在源代码中轻松完成此操作，而不是 SQL。但是，我想以“正确的方式”执行此操作并对其进行优化。我是否错误地假设如果我在 SQL 端执行此操作，它会更有效？我在运行时处理多个用户，我觉得每个用户执行一个数据库调用会更有效。

感谢所有帮助!

Answer 1

CREATE TABLE tbl_user
(
userId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
lastName VARCHAR(100) NOT NULL
);

-- drop table tbl_item;
CREATE TABLE tbl_item
(
itemId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
theName VARCHAR(100) NOT NULL,
description VARCHAR(255) NOT NULL,
category VARCHAR(20) NOT NULL,  -- normalize this?
cost DECIMAL(10,2) NOT NULL
);

-- drop table tbljn_userItem;
CREATE TABLE tbljn_userItem
(
userId INT NOT NULL,
itemId INT NOT NULL,
PRIMARY KEY (userId,itemId) -- in addition maybe a 2nd index going the other way, another covered index
-- will leave FK RI to you
);

INSERT tbl_user(lastName) VALUES ('smith'),('collins'),('jacobs');
INSERT tbl_item (theName,description,category,cost) VALUES ('flashlight','a working flashlight','camping',14.95);
INSERT tbl_item (theName,description,category,cost) VALUES ('mittens','mittens','winter wear',8.95);
INSERT tbl_item (theName,description,category,cost) VALUES ('scarf','red scarf, fuzzy','winter wear',8.95);
INSERT tbl_item (theName,description,category,cost) VALUES ('kite','yellow kite','kids',8.95);
INSERT tbljn_userItem (userId,itemId) VALUES (3,2),(2,2),(1,2);
-- select * from tbl_user;
-- select * from tbl_item;
-- select * from tbljn_userItem;

SELECT i.itemId,
i.theName,
i.description,
i.category,
i.cost,
( CASE 
    WHEN j.itemId IS NULL THEN 'no' ELSE 'yes' END
) AS isOwned
FROM tbl_item i
LEFT JOIN tbljn_userItem j ON j.itemId=i.itemId AND j.userId=1
LEFT JOIN tbl_user u
ON u.userId=j.userId AND u.userId=1
ORDER BY i.itemId

itemId  theName     description           category     cost   isOwned  
1       flashlight  a working flashlight  camping      14.95  no       
2       mittens     mittens               winter wear  8.95   yes      
3       scarf       red scarf, fuzzy      winter wear  8.95   no       
4       kite        yellow kite           kids         8.95   no