mysql消息表_mysql – 个人消息的表结构

我将首先回答你的主要问题,然后显示我将使用的表格结构.

要获取特定对话框的最后一个未删除的消息：

select

Message.Id

,Message.Subject

,Message.Content

from Message

join Junc_Message_To on Fk_Message = Message.Id

where Junc_Message_To.Fk_User = {RECIPIENT_ID}

and Message.Fk_User__From = {SENDER_ID}

and Junc_Message_To.Deleted is null

order by Junc_Message_To.Sent desc

limit 1

可以使用简单的三个表格结构.

表1存储用户记录 – 每个用户一个记录.

表2存储消息记录 – 每个消息一个记录,外键与发送消息的用户有关.

表3存储发送消息的消息和用户之间的相关性.

以下是用于创建上表的SQL：

create table `User` (

`Id` int not null auto_increment ,

`Username` varchar(32) not null ,

`Password` varchar(32) not null ,

primary key (`Id`) ,

unique index `Username_UNIQUE` (`Username` ASC) )

engine = InnoDB

create table `Message` (

`Id` int not null auto_increment ,

`Fk_User__From` int not null ,

`Subject` varchar(256) not null ,

`Content` text not null ,

primary key (`Id`) ,

index `Fk_Message_User__From` (`Fk_User__From` ASC) ,

constraint `Fk_Message_User__From`

foreign key (`Fk_User__From` )

references `User` (`Id` )

on delete cascade

on update cascade)

engine = InnoDB

create table `Junc_Message_To` (

`Fk_Message` int not null ,

`Fk_User` int not null ,

`Sent` datetime not null ,

`Read` datetime not null ,

`Deleted` datetime not null ,

PRIMARY KEY (`Fk_Message`, `Fk_User`) ,

INDEX `Fk_Junc_Message_To__Message` (`Fk_Message` ASC) ,

INDEX `Fk_Junc_Message_To__User` (`Fk_User` ASC) ,

constraint `Fk_Junc_Message_To__Message`

foreign key (`Fk_Message` )

references `Message` (`Id` )

on delete cascade

on update cascade,

constraint `Fk_Junc_Message_To__User`

foreign key (`Fk_User` )

references `User` (`Id` )

on delete cascade

on update cascade)

engine = InnoDB