mysql查询所有列加上某一列的计数

发布于 2024-10-27 02:56:58 字数 219 浏览 1 评论 0原文

试图弄清楚我是否可以减少 mysql 查询以减少与数据库的连接量。他们是否会拉出所有行，然后只计算与某个字符串匹配的特定行，例如：

mysql_query(
    "SELECT *, (COUNT(unread = 1) AS msgsunread) 
          FROM message_received WHERE user_id = '$uid'");

原文

Trying to figure out if I can reduce my mysql queries to decrease the amount of connections to the database. Is their away to pull all the rows and then just count a certain row where is matches a certain string such as:

mysql_query(
    "SELECT *, (COUNT(unread = 1) AS msgsunread) 
          FROM message_received WHERE user_id = '$uid'");

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

不可一世的女人 2024-11-03 02:56:58

你走在正确的轨道上。如果将示例中的计数语句扩展为完整子查询，您将得到：

SELECT *,
    (SELECT COUNT(*) FROM message_received WHERE unread = 1 AND user_id = $uid) AS unread_count,
FROM message_recieved WHERE user_id = $uid;

其中将包含您要查找的未读计数（在每一行中！）。 MySQL 也应该足够聪明，能够缓存该计数查询的结果，这样您就可以通过一个连接有效地执行两个查询。

不过，将计数标记到每一行似乎有点过多……也许这是一个更好的选择，两个查询只是一个更好的选择 - 并且您可以尝试在其他地方使用连接进行优化，或者使用 MySQL 不太擅长的其他东西令人讨厌的是——计数是优势臀部的疼痛。

希望这有帮助！

PS：如果这是 PHP，您应该能够通过单个连接执行任意数量的查询 - 只需调用 mysql_query( ...) 在调用 mysql_close(...) 之前多次。

You're on the right track. If you extend the count statement in your example into a full subquery, you'll get:

SELECT *,
    (SELECT COUNT(*) FROM message_received WHERE unread = 1 AND user_id = $uid) AS unread_count,
FROM message_recieved WHERE user_id = $uid;

Which'll have the unread count you're looking for (in every row!). MySQL should be smart enough to cache the result of that count query, too, so you've effectively performed two queries over one connection.

Having the count tagged onto every row does seem a bit excessive, though... Maybe this is a place where two queries is just a better bet - and you can try to optimize elsewhere with joins, or something else that MySQL is less of a nuisance about - counts are a pain in the dominant buttock.

Hope this helps!

PS: If this is PHP, you should be able to perform any number of queries over a single connection - just call mysql_query(...) a bunch of times before calling mysql_close(...).

回复收藏 0 原文

~没有更多了~