MySQL动态加入 [英] MySQL dynamic JOIN

问题描述

假设我在这个db上有这些表：

 项目
类别
武器
盾
剑
  

我需要创建一个这样的连接： p>

  SELECT items。*，{swords}。* 
 FROM items 
 INNER JOIN类别
 ON categories.id = items.category_id＃<  - 这里我知道这个项目是一把剑
 INNER JOIN {swords} 
 ON {swords} .item_id = item.id 
 WHERE items .id = 12 
  

但是， {swords} 部分是动态的，因为我发现一个项目是剑，检查 categories.name 字段。

如果 categories.name 是屏蔽，则查询将更改为：

  SELECT items。*，shields。* 
 FROM items 
 INNER JOIN类别
 ON categories.id = items.category_id 
 INNER JOIN shields 
 ON shields.item_id = item.id 
 WHERE items.id = 13 
  

我使用 {和} 围绕 swords 显示为变量

感谢您的回答和抱歉我的英语！ ：）

解决方案

从技术上讲，你只能用动态SQL来实现 - 这意味着或字符串连接在创建查询之前提交它的.blogspot.com / 2005/11 / mysql-5-prepared-statement-syntax-and.htmlrel =nofollow noreferrer到数据库。准备语句是更好的选择，因为更好的SQL注入防御。

您可以对各种表使用LEFT JOIN，但这意味着包括每个表（武器，盾牌，剑...），如果类别不匹配，则为空。尝试从单个查询中提取数据是可怕的...

PS：为什么有一个 SWORD 表？不会被 WEAPON 表覆盖？

Let's say that I have these tables on my db:

items
categories
weapons
shields
swords

And I need to create a join like this:

SELECT items.*, {swords}.*
FROM items
INNER JOIN categories
  ON categories.id = items.category_id # <-- here I know that the item is a sword
INNER JOIN {swords}
  ON {swords}.item_id = item.id
WHERE items.id = 12

But the {swords} part is dynamic since I found that an item is a sword checkgin the categories.name field.

The query will change if the categories.name is "shield" to this:

SELECT items.*, shields.*
FROM items
INNER JOIN categories
  ON categories.id = items.category_id
INNER JOIN shields
  ON shields.item_id = item.id
WHERE items.id = 13

I used { and } around the swords to show it like a variable

Thank you for your answer and sorry about my english! :)

解决方案

Technically, you can only do this with dynamic SQL - which means MySQL's Prepared Statement syntax, or string concatenation to create the query prior to submitting it to the database. Prepared Statements are the preferable choice, due to better SQL injection defense.

You could use LEFT JOINs to the various tables, but it would mean including numerous columns from each of the tables (weapons, shields, swords...) that would be null if the category didn't match. It'd be horrible to try to pull out data from a single query...

PS: Why is there a SWORD table? Wouldn't that be covered by the WEAPON table?

这篇关于MySQL动态加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！