Mysql

如何從一個使用者那裡獲取所有未刪除的消息

  • May 15, 2012

我有兩個表，一個用於名為 default_messages 的消息，另一個用於名為 default_recipient 的收件人。這些表的 SQL 如下所示：

CREATE TABLE IF NOT EXISTS `default_messages` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `subject` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
 `message` text COLLATE utf8_unicode_ci NOT NULL,
 `sender_user_id` int(11) NOT NULL,
 `reply_to_message_id` int(11) NOT NULL,
 `thread_root_message_id` int(11) NOT NULL,
 `date` datetime NOT NULL,
 `deleted` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;

這是該表的數據：

INSERT INTO `default_messages` (`id`, `subject`, `message`, `sender_user_id`, `reply_to_message_id`, `thread_root_message_id`, `date`, `deleted`) VALUES
(1, 'asdasdadasdasdasdasd', 'sdasdasdad', 1, 0, 0, '2012-05-11 09:29:00', 1),
(2, 'sadasdasd', 'asdasd', 2, 0, 0, '2012-05-11 09:44:23', 0),
(3, 'Re: asdasdadasdasdasdasd', 'asdadadadadadad', 2, 1, 0, '2012-05-11 09:44:41', 1),
(4, 'Re: sadasdasd', 'asdadad respuesta', 1, 2, 0, '2012-05-11 09:46:22', 1);

現在對於第二個表，這是 SQL：

CREATE TABLE IF NOT EXISTS `default_recipient` (
 `message_id` int(11) NOT NULL,
 `user_id` int(11) NOT NULL,
 `read` int(11) NOT NULL DEFAULT '0',
 `recipient_read_date` datetime DEFAULT NULL,
 `deleted` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

數據是這個：

INSERT INTO `default_recipient` (`message_id`, `user_id`, `read`, `recipient_read_date`, `deleted`) VALUES
(1, 2, 1, NULL, 0),
(2, 1, 1, NULL, 1),
(3, 1, 1, NULL, 1),
(4, 2, 1, NULL, 1);

現在我有這個查詢來獲取 user_id=x 的所有消息，其中兩個表 default_recipient 和 default_messages 中的已刪除列不等於 1 但它不起作用，因為當表接收者的 user_id 或表消息的 sender_user_id 需要時，我得到兩個結果而不是一個值等於 2：

SELECT p.*, rcp.*, msg.*, msg.id as message_id FROM default_messages msg 
       LEFT JOIN default_recipient rcp ON (msg.id = rcp.message_id)
       LEFT JOIN default_profiles p ON (p.user_id = msg.sender_user_id)
       WHERE msg.sender_user_id = 2 OR rcp.user_id = 2
       AND msg.deleted = 0 AND rcp.deleted = 0
       ORDER BY msg.date DESC

我做錯了什麼？

**編輯：**這裡的正確問題是：如何獲取一個使用者“X”的所有消息讓說 rcp.deleted = 0 OR msg.deleted = 0

兩個建議

建議 #1：重構查詢

SELECT
   p.*, rcp.*, msg.*, msg.id as message_id 
FROM
   (
       SELECT id FROM default_messages
       WHERE sender_user_id = 2 AND deleted = 0
       ORDER BY date DESC
   ) msgkey
   LEFT JOIN default_messages msg ON (msgkey.id = msg.id)
   LEFT JOIN 
   (
       SELECT * FROM default_recipient
       WHERE user_id = 2 AND deleted = 0
   ) rcp ON (msgkey.id = rcp.message_id) 
   LEFT JOIN default_profiles p ON (msg.sender_user_id = p.user_id) 
;

建議#2：索引表以支持子查詢

ALTER TABLE default_messages ADD INDEX (sender_user_id,deleted,date,id);
ALTER TABLE default_recipient ADD INDEX (user_id,deleted);

例如，我從您的問題中載入了數據並為這兩個表建立了索引

mysql> use reynierpm
Database changed
mysql> drop table if exists default_messages;
Query OK, 0 rows affected (0.05 sec)

mysql> drop table if exists default_recipient;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE IF NOT EXISTS `default_messages` (
   ->   `id` int(11) NOT NULL AUTO_INCREMENT,
   ->   `subject` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
   ->   `message` text COLLATE utf8_unicode_ci NOT NULL,
   ->   `sender_user_id` int(11) NOT NULL,
   ->   `reply_to_message_id` int(11) NOT NULL,
   ->   `thread_root_message_id` int(11) NOT NULL,
   ->   `date` datetime NOT NULL,
   ->   `deleted` int(11) NOT NULL DEFAULT '0',
   ->   PRIMARY KEY (`id`)
   -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;
Query OK, 0 rows affected (0.13 sec)

mysql> CREATE TABLE IF NOT EXISTS `default_recipient` (
   ->   `message_id` int(11) NOT NULL,
   ->   `user_id` int(11) NOT NULL,
   ->   `read` int(11) NOT NULL DEFAULT '0',
   ->   `recipient_read_date` datetime DEFAULT NULL,
   ->   `deleted` int(11) NOT NULL DEFAULT '0'
   -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.08 sec)

mysql> ALTER TABLE default_messages ADD INDEX (sender_user_id,deleted,date,id);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE default_recipient ADD INDEX (user_id,deleted);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `default_messages` (`id`, `subject`, `message`, `sender_user_id`, `reply_to_message_id`, `thread_root_message_id`, `date`, `deleted`) VALUES

   -> (1, 'asdasdadasdasdasdasd', 'sdasdasdad', 1, 0, 0, '2012-05-11 09:29:00', 1),
   -> (2, 'sadasdasd', 'asdasd', 2, 0, 0, '2012-05-11 09:44:23', 0),
   -> (3, 'Re: asdasdadasdasdasdasd', 'asdadadadadadad', 2, 1, 0, '2012-05-11 09:44:41', 1),
   -> (4, 'Re: sadasdasd', 'asdadad respuesta', 1, 2, 0, '2012-05-11 09:46:22', 1);
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `default_recipient` (`message_id`, `user_id`, `read`, `recipient_read_date`, `deleted`) VALUES
   ->  (1, 2, 1, NULL, 0),
   ->  (2, 1, 1, NULL, 1),
   ->  (3, 1, 1, NULL, 1),
   ->  (4, 2, 1, NULL, 1);
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT
   ->     rcp.*, msg.*, msg.id as message_id
   -> FROM
   -> (
   ->     SELECT id FROM default_messages
   ->     WHERE sender_user_id = 2 AND deleted = 0
   ->     ORDER BY date DESC
   -> ) msgkey
   -> LEFT JOIN default_messages msg ON (msgkey.id = msg.id)
   -> LEFT JOIN
   -> (
   ->     SELECT * FROM default_recipient
   ->     WHERE user_id = 2 AND deleted = 0
   -> ) rcp ON (msgkey.id = rcp.message_id)
   -> \G
*************************** 1. row ***************************
           message_id: NULL
              user_id: NULL
                 read: NULL
  recipient_read_date: NULL
              deleted: NULL
                   id: 2
              subject: sadasdasd
              message: asdasd
       sender_user_id: 2
  reply_to_message_id: 0
thread_root_message_id: 0
                 date: 2012-05-11 09:44:23
              deleted: 0
           message_id: 2
1 row in set (0.01 sec)

mysql>

我更改了查詢以測試兩個表的連接：

mysql> SELECT
   ->     rcp.*, msg.*, msg.id as message_id
   -> FROM
   -> (
   ->     SELECT id FROM default_messages
   ->     WHERE sender_user_id = 2 AND deleted = 0
   ->     ORDER BY date DESC
   -> ) msgkey
   -> LEFT JOIN default_messages msg ON (msgkey.id = msg.id)
   -> LEFT JOIN
   -> (
   ->     SELECT * FROM default_recipient
   ->     WHERE user_id = 2 AND deleted = 0
   -> ) rcp ON (msgkey.id = rcp.message_id)
   -> \G
*************************** 1. row ***************************
           message_id: NULL
              user_id: NULL
                 read: NULL
  recipient_read_date: NULL
              deleted: NULL
                   id: 2
              subject: sadasdasd
              message: asdasd
       sender_user_id: 2
  reply_to_message_id: 0
thread_root_message_id: 0
                 date: 2012-05-11 09:44:23
              deleted: 0
           message_id: 2
1 row in set (0.01 sec)

mysql>

我得到一排。隨著這兩個表的增長，索引將幫助重構的查詢快速執行。

試一試 ！！！

引用自：https://dba.stackexchange.com/questions/17879

相關問答

Mysql

慢查詢記錄一切

  • December 31, 2021
檢視問答
Mysql

改進慢查詢。附慢查詢日誌

  • December 22, 2019
檢視問答
Mysql

如何在聚合查詢中計算前一周的百分比？

  • January 19, 2019
檢視問答
Mysql

此查詢要索引哪一列？

  • December 26, 2018
檢視問答
Mysql

MySql - 如何改進此查詢？

  • January 12, 2018
檢視問答
Mysql

如何在一個查詢中進行多個計數？

  • March 13, 2017
檢視問答