MySQL SELECT 中使用 LEFT JOIN 的空行
我有三个表：users、facilities 和 staff_facilities。
users 包含平均用户数据，在我的例子中最重要的字段是 users.id、users.first 和 users.last。
facilities 也包含相当多的数据，但除了 facilities.id 之外，没有一个数据一定与此示例相关。
staff_facilties 由 staff_facilities.id (int,auto_inc,NOT NULL)、staff_facilities.users_id (int,NOT NULL) 和 组成Staff_faciltities.facilities_id（int，NOT NULL）。 （太拗口了！）
staff_facilities 引用其他两个表的 id，我们调用该表来查找用户的设施和设施的用户。
这是我在 PHP 中的选择查询：
SELECT users.id, users.first, users.last FROM staff_facilities LEFT JOIN users ON staff_facilities.users_id=users.id WHERE staff_facilities.facilties_id=$id ORDER BY users.last
该查询在我们的开发服务器上运行得很好，但是当我将其放入客户端的生产环境中时，结果集中经常会出现空白行。 我们的开发服务器使用客户端生产服务器上已存在的复制表和数据，但硬件和软件差异很大。
这些行没有任何信息，包括需要在数据库中输入 NOT NULL 值的三个 id 字段。 通过后端的MySQL管理工具运行查询会返回相同的结果。 在表中搜索 NULL 字段没有找到任何结果。
另一个奇怪的事情是，空行的数量根据 WHERE 子句 id 检查导致的不同结果而变化。 通常大约有一到三个空行，但使用相同参数时它们是一致的。
我曾多次处理过由于 LEFT JOINS 而返回几乎重复的行，但我以前从未发生过这种情况。 就显示信息而言，我可以轻松地向最终用户隐藏它。 我主要担心的是，随着时间的推移和记录数量的增加，这个问题将会变得更加复杂。 目前，该系统刚刚安装完毕，staff_facilities 表中已经有 2000 多条记录。
任何见解或方向将不胜感激。 我还可以提供更详细的示例和信息。
I have three tables called: users, facilities, and staff_facilities.
users contains average user data, the most important fields in my case being users.id, users.first, and users.last.
facilities also contains a fair amount of data, but none of it is necessarily pertinent to this example except facilities.id.
staff_facilties consists of staff_facilities.id (int,auto_inc,NOT NULL),staff_facilities.users_id (int,NOT NULL), and staff_faciltities.facilities_id (int,NOT NULL). (That's a mouthful!)
staff_facilities references the ids for the other two tables, and we are calling this table to look up users' facilities and facilities' users.
This is my select query in PHP:
SELECT users.id, users.first, users.last FROM staff_facilities LEFT JOIN users ON staff_facilities.users_id=users.id WHERE staff_facilities.facilties_id=$id ORDER BY users.last
This query works great on our development server, but when I drop it into the client's production environment often times blank rows appear in the results set. Our development server is using the replicated tables and data that already exist on the client's production server, but the hardware and software vary quite a bit.
These rows are devoid of any information, including the three id fields that require NOT NULL values to be entered into the database. Running the query through the MySQL management tools on the backend returns the same results. Searching the table for NULL fields has not turned up anything.
The other strange thing is that the number of empty rows is changing based on the varying results caused by the WHERE clause id check. It's usually around one to three empty rows, but they are consistent when using the same parameter.
I've many times dealt with the returning of nearly duplicate rows due to LEFT JOINS, but I've never had this happen before. As far as displaying the information goes, I can easily hide it from the end user. My concern is primarily that this problem will be compounded as time passes and the number of records grows larger. As it sits, this system has just been installed, and we already have 2000+ records in the staff_facilities table.
Any insight or direction would be appreciated. I can provide further more detailed examples and information as well.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您仅从连接右侧的表中选择列。 当然，其中一些是完全空的，你做了左连接。 因此，这些记录与连接左侧表中的 ID 匹配，但不与连接右侧的任何数据匹配。 由于您没有从左表中返回任何列，因此您看不到任何数据。
You are only selecting columns from the table on the right side of the join. Of course some of them are completely null, you did a left join. So those records match to an id in the table on the left side of the join but not to any data on the right side of the join. Since you aren't returning any columns from the left table, you see no data.