如何避免列名冲突? [英] How do you avoid column name conflicts?

问题描述

我最近被分配创建拍卖系统的任务.在工作期间，我遇到了无数次由于列名不明确而导致包含联接的SQL查询无法执行的情况.考虑拍卖的这种(简化的)表结构:

I was recently assigned a task of creating an auction system. During my work, I met numerous occasions where my SQL queries that contained joins failed to execute due to ambiguous column names. Consider this (simplified) table structure for the auction:

表auction:

• id
• name
• uid(创建拍卖的用户的ID)

• id
• name
• uid (ID of the user that created the auction)

表item:

• id
• name
• uid(添加项目的用户的ID)
• aid(可提供该物品的拍卖的ID)
• price(初始价格)

• id
• name
• uid (ID of the user that added the item)
• aid (ID of the auction where the item is available)
• price (initial price)

表user:

• id
• name

• id
• name

表bid:

• id
• uid(出价的用户的ID)
• iid(价格已提高的商品)
• price(提供的价格)

• id
• uid (ID of the user that placed a bid)
• iid (item whose price has been raised)
• price (offered price)

如您所见，有许多列具有冲突的名称.连接这些表需要采取一些措施来消除歧义.

As you can see, there are numerous columns that have conflicting names. Joining these tables requires using some measures that will clear the ambiguities.

我可以想到两种方法来做到这一点.首先是重命名列，并为所有列加上缩写的表名，以使拍卖ID变为a_id，项目ID变为i_id，并且在bid表中的项目ID变为b_i_id.这很可靠，但是会降低列名的可读性.

I can think of two ways to do this. First is to rename the columns, prefixing all of them with an abbreviated table name, so that auction ID will become a_id, item ID will become i_id, and item ID within the bid table will become b_i_id. This is pretty solid, but reduces the readability of the column names.

我可以想到的另一种方法是编写显式查询:

Another way I can think of is writing explicit queries:

SELECT `bid`.`id`, `user`.`name`, `bid`.`price`
FROM `bid`
JOIN `item` ON `item`.`id` = `bid`.`iid`
JOIN `user` ON `user`.`id` = `bid`.`uid`
JOIN `auction` ON `auction`.`id` = `item`.`aid`
WHERE `bid`.`price` > `item`.`price`
AND `auction`.`id` = 1
GROUP BY `user`.`id`
ORDER BY `bid`.`price` DESC;

这是可读且明确的，但是需要很多额外的击键.

This is readable and unambiguous, but requires lots of extra keystrokes.

我使用第二种方法，但也许您在类似情况下成功使用了其他方法?如何避免SQL查询中的列名冲突?

I use the second approach, but maybe there are others that you have successfuly used in similar situations? How do you avoid column name conflicts in your SQL queries?

推荐答案

我的经验是，额外击键的好处远远超过了在短时间内键入所需的时间从长远来看，最晚需要查看一年前左右编写的查询时.

My experience is that that the benefits of the extra keystrokes outweighs the short time it takes to type them by far in the long run, at latest when you need to look at a query that you have written a year ago or so.

这篇关于如何避免列名冲突?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！