将聊天消息存储在MySql表中 [英] Storing chat messages inside a MySql table

问题描述

在过去的几周中，我一直在研究基于Web的聊天客户端应用程序，但我有一个关于在MySql表中存储聊天消息的问题.我创建了一个名为对话"的表，目前它包含5个字段；

For the last couple of weeks I've been working on a web based chat client application and I have a question regarding storing chat messages inside a MySql table. I created a table called conversations and for now it consists of 5 fields;

user1ID，user2ID，消息(中文本)，状态，时间戳.

user1ID, user2ID ,messages(mediumtext), status, timestamp.

当我测试聊天应用程序时，所有功能都可以正常运行，但是问题是每次用户发送内容时，我都会将该值作为新行添加到消息"字段中.在检索消息时，我的sql代码将读取整个内容并将其显示给相应的用户.因此，数据量随添加到消息字段中的文本量线性增加. 我的问题是，有什么方法可以仅选择文本字段的最后一行，或者是否可以采用其他解决方案来减少传输的数据量.

When I test the chat application everything works perfectly fine, but the problem is every time a user sends something, I'm appending that value into my 'messages' field as a new line. And when it comes to retrieving the message, my sql code reads the whole thing and show it to the corresponding user. So the amount of data linearly increase by the amount of text added into the messages field. My question is, is there any way to SELECT only the last line from a text field or maybe another solution that will reduce the amount of transferred data.

推荐答案

您需要更好的数据库架构-更具关系性.这样做还可以为您带来其他一些改进(密码保护的聊天和多用户聊天，仅举几例)

You need a better db schema - more relational. Doing so will give you some other improvements as well (password protected chats and multi-user chat to name a couple)

这是您数据库的ERD之一.

Here is one take on an ERD for your db.

2016年5月6日编辑 添加(希望)具有改进的字段类型和名称的DDL

5/6/2016 edit Adding DDL with (hopefully) improved field types and names

CREATE TABLE user
(
    user_id CHAR(32),
    user_login VARCHAR(255),
    user_password CHAR(64),
    user_email VARCHAR(400),
    PRIMARY KEY (user_id)
);

CREATE TABLE message
(
    message_id CHAR(32),
    message_datetime DATETIME,
    message_text TEXT,
    message_chat_id CHAR(32),
    message_user_id CHAR(32),
    PRIMARY KEY (message_id)
);

CREATE TABLE user_chat
(
    user_chat_chat_id CHAR(32),
    user_chat_user_id CHAR(32),
    PRIMARY KEY (user_chat_chat_id,user_chat_user_id)
);

CREATE TABLE chat
(
    chat_id CHAR(32),
    chat_topic VARCHAR(32),
    chat_password CHAR(64),
    user_chat_user_id CHAR(32),
    PRIMARY KEY (chat_id)
);

CREATE INDEX user_login_idx ON user (user_login);
ALTER TABLE message ADD FOREIGN KEY message_chat_id_idxfk (message_chat_id) REFERENCES chat (chat_id);

ALTER TABLE message ADD FOREIGN KEY message_user_id_idxfk (message_user_id) REFERENCES user (user_id);

ALTER TABLE user_chat ADD FOREIGN KEY user_chat_user_id_idxfk (user_chat_user_id) REFERENCES user (user_id);

ALTER TABLE chat ADD FOREIGN KEY chat_id_idxfk (chat_id,user_chat_user_id) REFERENCES user_chat (user_chat_chat_id,user_chat_user_id);

这篇关于将聊天消息存储在MySql表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！