在数据库中存储长字符串好吗？
我需要在数据库中存储长字符串。该字符串可能有 5 或 6 个句子长。您认为这是一个很好的设计策略吗？或者我应该存储该字符串的 id，然后创建与另一个包含存储该字符串的文件位置的表的关系。 您能给出两者的优点和缺点吗？
字符串已被预处理并存储在数据库中。任何修改都会读取整个字符串并完全替换它。所以你可以假设该字符串是不可分割的。
I need to store long strings in a database. the string may be 5 or 6 sentences long. do you think this is a good design strategy. or should I store an id for that string and then create a relationship with another table that contains the location of the file storing the string.
could you please give advantages and disadvantages of both.
the strings have been preprocessed and stored in the database. any modification would read the entire string and replace it completely. so you can assume that the string is indivisible.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
将字符串存储在数据库中应该没问题。如果您存储文件指针，则意味着每次要读取字符串时都需要执行文件 I/O。几句话并不是很长，如果需要，您可以随时使用长文本数据字段。显然你的数据库会更大一点，因为你有文本，但这没关系。这无疑是比存储文件更好的选择。
It should be fine to store the string in the database. If you store a file pointer instead, that means you need to do File I/O every time you want to read the string. A few sentences isn't terribly long and you can always use a longtext data field if you need to. Obviously your database will be a little bit larger because you have the text, but that's ok. It is certainly a better alternative than having to store the files.
你提到的字符串并不长。
当你提到“长”字符串时，我想到的是 32kB 及以上——有些句子小于 1kb——今天这不算什么。
你的技巧，存储一个 Id 会使事情变得更慢，因为你必须进行间接访问。
我唯一建议的是，当需要最大性能时，您应该只选择您需要的那些列（省略 SELECT *）——因此在不需要时省略文本列，因为将字符串从服务器传输到应用程序会产生成本最多的时间。这是一个很好的实践，不要触及不需要的列（特别是当它们可能包含大量数据时）。
The strings you mention are not at all long.
When you refered to "long" strings, I was thinking about 32kB and above -- some sentences are <1kb -- that is nothing today.
Your trick, storing an Id makes the things slower since you have to make an indirect access.
The only thing I would recommend, when maximum performance is needed, you should select only those columns that you need (omit SELECT *) -- so omit the text column, when not needed, since the transport of the string from server to application costs the most time. It is a good praxis, not to touch columns not needed (specially when they might contain much data).
我创建一个单独的表的唯一原因是这些长字符串对于许多记录来说都是相同的。否则，这只是一个额外的并发症，不太可能带来任何回报。
The only reason I would create a separate table is if those long strings will be the same for many records. Otherwise its just an extra complication that isn't likely to provide any payback.
对于现代 DBMS 来说，五六句话根本不算什么！将文本直接存储在数据库中。
（您提到的另一种技术 - 存储对另一个表的引用，该表本身具有对保存文本的外部文件的引用 - 使用起来会更麻烦并且性能更差。）
Five or six sentences is nothing to a modern DBMS! Store the text directly in the database.
(The other technique you mentioned - storing a ref to another table which itself has a ref to an external file holding the text - would be much more cumbersome to use and have much poorer performance.)
答案实际上取决于您打算存储的字符串量，以及您打算使用什么数据库来存储它。如果您没有存储很多字符串，您可能需要考虑将它们存储在 XML 或资源文件中，并预先将其加载到您的应用程序中。不过，如果您有大量字符串数据，那么您可能最好在需要时以内存方式读取字符串，而不是冒险将最终不使用的字符串读入内存。
The answer really depends on the volume of strings you intend to store, and what DB you intend to use to store it. If you aren't storing many strings, you might want to consider storing them in an XML or resource file, and loading that into your application up front. If you have lots of string data though, you'll probably be better off memorywise reading the string as and when you need it, rather than taking the chance of reading a string into memory that you don't end up using.
数据库本身在存储长字符串方面没有真正的问题。有些限制适用（例如 SQL Server 上的 8k 记录大小限制），但即便如此，您也可以在数据库中存储任意长度的文本，因为所有适当的都支持 BLOB/TEXT 数据类型，几乎没有上限。
五到六句话其实并不长。如果它们属于一起并且要作为一个整体进行检索和操作，您可以继续将它们存储在适当维度的 CHAR 数据类型字段中。
仅当您的应用程序/数据模型直接受益于这种方法（即实际上它们是独立的事物）时，才会出现是否将它们分开并为其附加 ID 的问题。就你而言，似乎没有理由走那条路。
The database itself has no real problem with storing long strings. Some restrictions apply (like the 8k record size limit on SQL Server), but even then you could store text of arbitrary length in a database, because all proper ones support BLOB/TEXT data types with virtually no upper limit.
Five to six sentences is not really long. If they belong together and are meant to be retrieved and manipulated as a whole, you can go ahead and store them in a CHAR data type field of appropriate dimensions.
The question whether to separate them and attach an ID to them arises only if your application/data model benefits directly from this approach, i.e. in reality they are separate things. In your case there seems to be no reason to go that way.
每个人都提到了性能，但没有人提出存储指向操作系统文件的指针是一个坏主意的另一个主要原因：备份和恢复。如果所有内容都在数据库中，那么我们就有单一的数据备份机制和单一的恢复机制。而对于操作系统上的文件，我们有两种不同的备份机制，可能是两种不同的粒度，并且恢复成为同步噩梦。
在某些情况下，这不适用，例如数据仓库，其事务非常不频繁，因此可以在没有重做或事务日志的情况下生存。
Everybody has mentioned performance, but nobody has raised the other major reason why storing pointers to OS files is a bad idea: backup and recovery. If everything is in the database then we have a single mechanism for backing up the data and a single mechanism for recovery. Whereas with files on the OS we have two different backup mechanisms, probably at two different granularities, and recovery becomes a synchronisation nightmare.
There are a few cases where this doesn't apply, such as data warehouses, which have very infrequent transactions and so can survive without redo or transaction logs.
除非有特殊情况，我会把这个字段留在原处。
唯一的其他选择是将字符串放入不同的表中（将实际的字符串放入其中）...将它们放入单独的文件中会降低你的性能。
Except in special cases, I would leave the field where it is.
The only other option would be to put the strings into a different table (putting the actual strings in there)... putting them in separate files will kill your performance.