如何在MySQL查询中引用表格点列符号 [英] How do I reference table dot column notation in a MySQL query

问题描述

SELECT * from meets
 LEFT JOIN teams as hteam on meets.meet_hometeam=hteam.team_id 
 LEFT JOIN teams as ateam on meets.meet_awayteam=ateam.team_id 
 LEFT JOIN teams as altloc on (meets.meet_altloc=altloc.team_id and meets.meet_altloc!='')
 where meet_date between ($now+(4*86400)) and ($now+(5*86400) or meets.meet_id='2')

在PHP中:

$var = $queryvar->fetch_object();

我在调用$var->ateam.team_town时遇到问题，它只是将点视为串联而不是作为表的对象.

I'm having issues when I call $var->ateam.team_town it is just treating the dot as concatenation and not as an object to the table.

推荐答案

进行JOIN或UNION查询时不要SELECT *.相反，请具体说明所需的列，如果不同表中的列名称相同，则需要为其分配别名.

Don't SELECT * when doing JOIN or UNION queries. Instead, be specific about the columns you need, and if columns have the same name in different tables, you need to assign aliases to them.

除了区分具有相似名称的列的必要性之外，您还可以确定返回它们的顺序，并免受将来添加到您的列中的架构更改所带来的影响不想被自动拉入(例如图像blob数据之类的东西).

Aside from the necessity of differentiating columns with similar names, you get the benefit of being deterministic about the order in which they're returned and protection from future schema changes that add columns you don't want from being automatically pulled into this (like image blob data or something).

SELECT
  /* specify columns from each table with aliases */
  hteam.team_id AS hometeam_id,
  ateam.team_id AS awayteam_id,
  hteam.team_town AS hometeam_town,
  ateam.team_town AS awayteam_town,
  ...
  ...
  etc...
FROM meets
 LEFT JOIN teams as hteam on meets.meet_hometeam=hteam.team_id 
 LEFT JOIN teams as ateam on meets.meet_awayteam=ateam.team_id 
 LEFT JOIN teams as altloc on (meets.meet_altloc=altloc.team_id and meets.meet_altloc!='')
 WHERE meet_date between ($now+(4*86400)) and ($now+(5*86400) or meets.meet_id='2')

然后在您的PHP中，只需使用您选择的别名来调用它即可:$var->hometeam_town, $var->hometown_id, $var->awayteam_town等...

Then in your PHP, just call it by the alias you chose: $var->hometeam_town, $var->hometown_id, $var->awayteam_town etc...

这篇关于如何在MySQL查询中引用表格点列符号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！