使用mysql和mariadb时不同的结果查询 [英] Different result query when use mysql and mariadb

问题描述

这是我的问题：

我的数据库有table Book，Post。每本书都有许多帖子

表帖子包含字段' book_id '，即外键参考表Book primary key（id）。

这是我的索引页面。这个想法是从每本书和发布日期的订单中获取最新的帖子。

当我在localhost上编码时，每件事情都可以。我可以从每本书中获取最新的帖子，并按发布日期排序。但是，当我部署在vps中。它没有得到最新的文章，它从每本书中获得第一篇文章。我对此没有任何经验。请帮忙，谢谢

在本地主机上，我使用：Apache-2.2，PHP-5.3，Mysql-5.5，引擎类型为表InnoDB。

在VPS上，我使用：Nginx 1.7.6，PHP-FPM 5.5.18，MariaDB，引擎类型为表MyIsam

我访问的问题是InnoDB和MyIsam，我试图修复它。但是，如果你有空闲时间，请给我一些好的建议。非常感谢

p / s：抱歉我的英语不好

  SELECT * FROM`my_book_store`.`books` 
 AS`Book` 
 INNER JOIN 
（
 SELECT * 
 FROM posts 
 WHERE posts.published = 1 AND posts.published_date< = NOW（）
 ORDER BY posts.published_date DESC 
）AS`Post` 
 ON（`Post`.``_book_id` =`Book`.` id`）
 WHERE 1 = 1 
 GROUP BY`Book`.`id` 
 ORDER BY`Post`.`published_date` desc 
 LIMIT 100 
  code> 




解决方案

试试这个：



  SELECT b。*，p。* 
 FROM my_book_store.books AS b 
 INNER JOIN帖子p ON b.id = p.book_id 
 INNER JOIN （SELECT p.book_id，MAX（p.published_date）published_date 
 FROM posts p 
 WHERE posts.published = 1 AND posts.published_date< = NOW（）
 GROUP BY p.book_id 
）AS p1 ON p.book_id = p1.book_id AND p.published_date = p1.publish ed_date 
 GROUP BY b.id 
 ORDER BY p.published_date DESC 
 LIMIT 100 
  




Here is my problem:

My database have table Book, Post. Each book has many post

Table posts has field 'book_id', that is foreign key reference table Book primary key (id).

This is my index page. The idea is to get latest post from each book and order by published date.

When I code on localhost, every thing is OK. I can get latest post from each book and order by publish date. But when I deploy it in vps. It didn't get latest post, it get first post from each book. I didn't have any experience about it. Please help, thanks

On localhost, I use: Apache-2.2, PHP-5.3, Mysql-5.5, ENGINE type for table is InnoDB.

On VPS, I use: Nginx 1.7.6, PHP-FPM 5.5.18, MariaDB, ENGINE type for table is MyIsam

I guest the problem is InnoDB and MyIsam, I try to fix it. But, if you have free time, please give me some good advise. Thanks a lot

p/s: Sorry about my poor english

SELECT * FROM `my_book_store`.`books` 
AS `Book` 
INNER JOIN 
(
    SELECT * 
    FROM posts 
    WHERE posts.published = 1 AND posts.published_date <= NOW() 
    ORDER BY posts.published_date DESC
) AS `Post` 
ON (`Post`.`book_id` = `Book`.`id`) 
WHERE 1 = 1 
GROUP BY `Book`.`id` 
ORDER BY `Post`.`published_date` desc 
LIMIT 100   

解决方案

Try this:

SELECT b.*, p.*  
FROM my_book_store.books AS b 
INNER JOIN posts p ON b.id = p.book_id
INNER JOIN (SELECT p.book_id, MAX(p.published_date) published_date
            FROM posts p 
            WHERE posts.published = 1 AND posts.published_date <= NOW() 
            GROUP BY p.book_id
          ) AS p1 ON p.book_id = p1.book_id AND p.published_date = p1.published_date 
GROUP BY b.id 
ORDER BY p.published_date DESC 
LIMIT 100   

这篇关于使用mysql和mariadb时不同的结果查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！