带有线程/回复的私人消息系统

Question

我目前正在创建一个私人消息系统（PHP/MySQL），其中用户可以一次向多个收件人发送消息，然后这些用户可以决定回复。

这是我目前正在处理的内容：

tbl_pm tbl:
id
date_sent
title
content
status ENUM ('unread', 'read') DEFAULT 'unread'

tblpm_info tbl:
id
message_id
sender_id
receiver_id

但是，我需要一些帮助来确定两件事的逻辑：

1）创建新消息时，“id”应该自动递增吗？ 如果两个表中的“id”列都设置为自动递增，那么如何在“关系表”中设置“message_id”列？

例如，当创建一条新消息时，我的MySQL语句如下：

<?php
mysql_query("INSERT INTO `tblpm` (title, content, sender_id, date_sent) VALUES ('$subject', '$message', '$sender', NOW())" );

在同一条语句中，如何将tblpm的“自动递增”值输入到tblpm_info“message_id”字段中？

2）当用户回复消息时，我的MySQL语句应该是什么样子？

也许我让这件事变得比我需要的更复杂。 任何帮助是极大的赞赏！

Answer 1

1）肯定是的，id 应该自动递增，除非您提供唯一的主键的不同方式。 您可以直接从 mysql 使用 mysql_insert_id() 或 LAST_INSERT_ID() 获取插入的 id，因此要发布一些连接信息，您可以执行 或

   mysql_query("INSERT INTO table1 ...")
   $foreign_key=mysql_insert_id(); //this gives you the last auto-increment for YOUR connection

，但前提是您绝对确定没有其他人同时写入表或控制事务，插入后执行：

$foreign_key=mysql_query("SELECT LAST_INSERT_ID()")
INSERT INTO table2 message_id=$foreign_key

或者，不将 FK 拉到 php，所有这些都在一个事务中（我还建议将 SQL 包装为事务也）类似：

"INSERT INTO table1...; INSERT INTO table2 (message_id,...) VALUES(LAST_INSERT_ID(),...)"

根据您的语言和 mysql 库，您可能无法发出多查询方法，因此您最好使用第一种方法。

2）这可以有很多方法，具体取决于您是否也需要回复所有收件人（例如会议），以类似线程/论坛的方式回复，客户端是否可以存储最后检索到的消息/id（例如在 cookie 中；也会影响您是否真的需要“读取”字段）。

“私人聊天”方法是最简单的一种，然后您可能最好将消息存储在一个表中，并将“从-到”关系存储到另一个表中（并在它们上使用 JOIN），或者简单地将消息重新填充到一个表中表（因为现在存储很便宜）。 因此，简单化的模型将是一张表：（

table: message_body,from,to
$recepients=array(1,2,3..);
foreach($recepients as $recepient)
 mysql_query("INSERT INTO table (...,message_body,from,to) VALUES(...,$from,$recepient)");

复制消息等，仅接收者更改）

或

message_table: id,when,message_body
to-from-table: id,msg_id,from,to

$recepients=array(1,2,3,...);
mysql_insert("INSERT INTO message_table (when,message_body) VALUES(NOW(),$body)");
$msg_id=mysql_insert_id();
foreach($recepients as $recepient)
 mysql_query("INSERT INTO to-from-table (msg_id,from,to) VALUES($msg_id,$from,$recepient)");

（插入一次消息，存储所有接收者的关系和 FK）

然后每个客户端存储他/她收到的最后一个 message_id（默认为0），并假设所有先前的消息已阅读）：

"SELECT * FROM message WHERE from=$user_id OR to=$user_id WHERE $msg_id>$last_msg_id"

或者我们只是记下用户的最后输入时间并查询此后的任何新消息：

"SELECT * FROM message WHERE from=$user_id OR to=$user_id WHERE when>='".date('Y-m-d H:i:s',$last_input_time)."' "

---

如果您需要更多类似会议或论坛的方法，并且需要跟踪谁阅读了消息或没有跟踪消息，您可能需要跟踪所有涉及的用户。

假设一次“多用户会议”中不会有一百多人，我会使用一张表来存储消息，并使用我经常使用的“逗号分隔和换行列表”技巧来存储标签。

id autoincrement (again, no need for a separate message id)
your usual: sent_at, title (if you need one), content
sender (int)
recepients (I'd go with varchar or shorter versions of TEXT; whereas TEXT or BLOB gives you unlimited number of users but may have impact on performance)
readers (same as above)

收件人/读者字段的秘密是填充它们以逗号分隔的 ID 列表，并再次用逗号将其括起来（稍后我将详细解释原因）。

因此，您必须再次将收件人的 id 收集到数组中，例如 $recepients=array(2,3,5) 并修改您的插入：

"INSERT INTO table (sent_at,title,content,sender,recepients) VALUES(NOW(),'$title','$content',$sender_id,',".implode(',', $recepients).",')"

您会得到像
这样的表行
... 发件人 | 收件人
...          1 | ,2, //单个用户消息
...          1 | ,3,5, //多用户消息

，用于选择 id 为 $user_id=2 的用户的所有消息

SELECT * FROM table WHERE sender=$user_id OR INSTR(recepients, ',$user_id,')

之前我们包装了内爆的收件人列表，例如 '5,2,3' 变为 ' ,5,2,3,' 和 INSTR 这里告诉您 ',2,' 是否作为子字符串包含在某处 - 因为仅搜索 '2',',2' 或 '2,' 可能会给您带来误报，例如 '相应地 234,56','1**,234','9,452,**89' - 这就是为什么我们必须将列表包装在第一名。

当用户阅读/接收他/她的消息时，您将他们的 id 附加到读者列表中，例如：

UPDATE table SET readers=CONCAT(',',TRIM(TRAILING ',' FROM readers),',$user_id,') WHERE id=${initial message_id here}

这会导致：

... 收件人| 读者
...          1 | ,2,             | ,2,
...          1 | ,3,5,           | ,3,5,2,

或者我们现在可以修改初始查询，添加一列“is_read”来说明用户之前是否阅读过该消息：

SELECT * FROM table WHERE INSTR(recepients, ',$user_id,'),INSTR(readers, ',$user_id,') AS is_read

从结果中收集消息 ID 并将“recepients”字段更新为 1去

"UPDATE table SET readers=CONCAT(',',TRIM(TRAILING ',' FROM readers),',$user_id,') WHERE id IN (".implode(',' ,$received_msg_ids).")"

Answer 2

您不应依赖两个 ID 的自动增量，因为两个用户可能几乎同时发布两条消息。 如果第一个脚本将数据插入到 tbl_pm 表中，则第二个脚本会在第一个脚本完成之前执行其 tbl_pm 和 tblpm_info 插入操作在其 tblpm_info 插入中，第一个脚本的两个数据库插入将具有不同的 ID。

除此之外，您的数据库结构似乎没有很好地组织手头的任务。 假设您的消息可能很长，并且发送给大量用户，那么理想的情况是将消息内容存储一次，并且为每个收件人提供未读状态、阅读时间等。例如：

CREATE TABLE `pm_data` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `date_sent` timestamp NOT NULL,
  `title` varchar(255)
  `sender_id` smallint(5) unsigned,
  `parent_message_id` smallint(5) unsigned,
  `content` text,
  PRIMARY_KEY (`id`)
);
CREATE TABLE `pm_info` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `pm_id` smallint(5) unsigned NOT NULL,
  `recipient_id` smallint(5) unsigned,
  `read` tinyint(1) unsigned default 0,
  `read_date` timestamp,
  PRIMARY_KEY (`id`)
);

创建这两个表，并注意它们都有一个设置为自动递增的 'id' 值，但 'info' 表还有一个 pm_id 字段，该字段将保存它所记录的 'data' 行的 ID 号指的是，这样您就可以确定“info”表中的每一行都有一个可用于选择的主键。

如果您想要使用 MySQL 建立真正的关系数据库，请确保您的引擎设置为 InnoDB，它允许在表之间建立关系，因此（例如）如果您尝试将某些内容插入到引用的“info”表中如果“data”表中不存在 pm_id，则 INSERT 将失败。

选择数据库结构后，您的 PHP 代码将如下所示：

 <?php
 // Store these in variables such that if they change, you don't need to edit all your queries
 $data_table = 'data_table';
 $info_table = 'info_table';
 mysql_query("INSERT INTO `$data_table` (title, content, sender_id, date_sent) VALUES ('$subject', '$message', '$sender', NOW())" );
 $pmid = mysql_insert_id(); // Get the inserted ID
 foreach ($recipent_list as $recipient) {
      mysql_query("INSERT INTO `$info_table` (pm_id, recipient_id) VALUES ('$pmid', '$recipient')" );
 }

Answer 3

是的。 你肯定会在两个 id 上设置 auto_increment 。

要设置 message_id，您可以通过编程方式将其插入其中。

您的查询将如下所示：

mysql_query("INSERT INTO `tblpm` (title, content, sender_id, date_sent) VALUES ('$subject', '$message', '$sender', NOW())" );

请注意，它是相同的！ 如果 id 设置为 auto_increment，它将为您发挥所有作用。

Answer 4

在普通的 PHP/Mysql 调用中， mysql_insert_id() 返回上一个 INSERT 操作的自动递增值

因此，您插入消息，收集新生成的 ID，并将该值放入另一个表中。

Answer 5

就您个人而言（假设示例没有简化，并且没有更多我看不到的内容），我会将这两个表中的数据存储在一个表中，因为它们似乎直接相关：

tbl_pm tbl:

message_id

date_sent

title

content

status ENUM ('unread', 'read') DEFAULT 'unread'

sender_id

receive_id

所以你最终会得到类似上面的结果，实际上不需要连接，因为关系总是 1 对 1？ 您在 tbl_pm 表中已读/未读，每个收件人肯定会更改该表，这意味着无论如何您都必须为每个收件人存储邮件的副本。 也许 staus 应该在 tbl_pm 信息表中。

如果您确实想插入到两个表中，请尝试在查询中使用 last_insert_id() 或
如上所述，来自 php.ini 中的 mysql_insert_id()

Answer 6

我可能会做类似于加文建议的事情，但如果您想要线程消息，则必须添加另一个键，如下所示：

private_messages
- title (text)
- date (timestamp)
- content (text)
- status (enum)
- sender_id (int)
- receiver_id (int)
- parent_message_id (int)

然后您可以在没有单独的表或系统的情况下嵌套消息。