MySQL 在数据库中存储聊天消息的最佳方式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7063051/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best way to store chat messages in a database?
提问by wilsonpage
I'm building a chat app and I want a full history off all messages ever sent in the chat conversation. At the moment I am storing each message as a single row in a table called 'messages'. I am aware that this table could grow huge as even small messages like 'Hi' would have their own database record.
我正在构建一个聊天应用程序，我想要在聊天对话中发送的所有消息的完整历史记录。目前，我将每条消息作为单行存储在名为“消息”的表中。我知道这个表可能会变得很大，因为即使是像“嗨”这样的小消息也会有自己的数据库记录。
Can anyone recommend a more scalable mysql solution? I don't require the individual messages to be searchable, editable or deletable. Could the whole conversation be stored in one huge field?
谁能推荐一个更具可扩展性的 mysql 解决方案？我不要求单个消息可搜索、可编辑或可删除。整个对话可以存储在一个巨大的字段中吗？
Would love to hear your ideas!
很想听听你的想法！
采纳答案by jasalguero
There's nothing wrong with saving the whole history in the database, they are prepared for that kind of tasks.
将整个历史记录保存在数据库中并没有错，它们已为此类任务做好了准备。
Actually you can find here in Stack Overflow a link to an example schema for a chat: example
实际上，您可以在 Stack Overflow 中找到指向聊天示例架构的链接：example
If you are still worried for the size, you could apply some optimizations to group messages, like adding a buffer to your application that you only push after some time (like 1 minute or so); that way you would avoid having only 1 line messages
如果您仍然担心大小，您可以对消息分组进行一些优化，例如向您的应用程序添加一个缓冲区，您只能在一段时间（例如 1 分钟左右）后推送；这样你就可以避免只有 1 行消息
回答by Kevin Burton
If you can avoid the need for concurrent writes to a single file, it sounds like you do not need a database to store the chat messages.
如果您可以避免并发写入单个文件的需要，那么您似乎不需要数据库来存储聊天消息。
Just append the conversation to a text file (1 file per user\conversation). and have a directory/ file structure
只需将对话附加到文本文件（每个用户\对话 1 个文件）。并有一个目录/文件结构
Here's a simplified view of the file structure:
这是文件结构的简化视图：
chat-1-bob.txt
201101011029, hi
201101011030, fine thanks.
chat-1-jen.txt
201101011030, how are you?
201101011035, have you spoken to bill recently?
chat-2-bob.txt
201101021200, hi
201101021222, about 12:22
chat-2-bill.txt
201101021201, Hey Bob,
201101021203, what time do you call this?
You would then only need to store the userid, conversation id (guid ?) & a reference to the file name.
然后，您只需要存储用户 ID、会话 ID（guid ？）和对文件名的引用。
I think you will find it hard to get a more simple scaleable solution.
我认为您会发现很难获得更简单的可扩展解决方案。
You can use LOAD_FILEto get the data too see: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
您也可以LOAD_FILE用来获取数据，请参见：http: //dev.mysql.com/doc/refman/5.0/en/string-functions.html
If you have a requirement to rebuild a conversation you will need to put a value (date time) alongside your sent chat message (in the file) to allow you to merge & sort the files, but at this point it is probably a good idea to consider using a database.
如果您需要重建对话，则需要在发送的聊天消息（在文件中）旁边放置一个值（日期时间）以允许您合并和排序文件，但此时这可能是一个好主意考虑使用数据库。
回答by Bernhard Kircher
You could create a database for x conversations which contains all messages of these conversations. This would allow you to add a new Database (or server) each time x exceeds. X is the number conversations your infrastructure supports (depending on your hardware,...).
您可以为 x 个对话创建一个数据库，其中包含这些对话的所有消息。这将允许您在每次 x 超过时添加一个新的数据库（或服务器）。X 是您的基础架构支持的对话数量（取决于您的硬件，...）。
The problem is still, that there may be big conversations (with a lot of messages) on the same database. e.g. you have database A and database B an each stores e.g. 1000 conversations. It my be possible that there are far more "big" conversations on server A than on server B (since this is user created content). You could add a "master" database that contains a lookup, on which database/server the single conversations can be found (or you have a schema to assign a database from hash/modulo or something).
问题仍然是，在同一个数据库上可能会有大的对话（有很多消息）。例如，您有数据库 A 和数据库 B，每个数据库都存储了 1000 个对话。服务器 A 上的“大”对话可能比服务器 B 上的多得多（因为这是用户创建的内容）。您可以添加一个包含查找的“主”数据库，在哪个数据库/服务器上可以找到单个对话（或者您有一个架构来从散列/模数或其他东西分配数据库）。
Maybe you can find real world architectures that deal with the same problems (you may not be the first one), and that have already been solved.
也许您可以找到处理相同问题的现实世界架构（您可能不是第一个），并且已经解决了。
