MySQL MySQL存储长字符串的最佳方式

MySQL best way to store long strings

提问by Damian

I'm looking for some advice on the best way to store long strings of data from the mySQL experts.

我正在从 mySQL 专家那里寻求一些关于存储长字符串数据的最佳方法的建议。

I have a general purpose table which is used to store any kind of data, by which I mean it should be able to hold alphanumeric and numeric data. Currently, the table structure is simple with an ID and the actual data stored in a single column as follows:

我有一个用于存储任何类型数据的通用表，我的意思是它应该能够保存字母数字和数字数据。目前，表结构很简单，有一个ID，实际数据存储在单列中，如下所示：

id INT(11)
data VARCHAR(128)

I now have a requirement to store a larger amount of data (up to 500 characters) and am wondering whether the best way would be to simply increase the varchar column size, or whether I should add a new column (a TEXT type column?) for the times I need to store longer strings.

我现在需要存储大量数据（最多 500 个字符），我想知道最好的方法是简单地增加 varchar 列的大小，还是应该添加一个新列（TEXT 类型的列？）有时我需要存储更长的字符串。

If any experts out there has any advice I'm all ears! My preferred method would be to simply increase the varchar column, but that's because I'm lazy. The mySQL version I'm running is 5.0.77.

如果那里有任何专家有任何建议，我会全神贯注！我的首选方法是简单地增加 varchar 列，但那是因为我很懒。我正在运行的 mySQL 版本是 5.0.77。

I should mention the new 500 character requirement will only be for the odd record; most records in the table will be not longer than 50 characters. I thought I'd be future-proofing by making the column 128. Shows how much I knew!

我应该提到新的 500 个字符的要求仅适用于奇数记录；表中的大多数记录不会超过 50 个字符。我想我可以通过制作第 128 列来面向未来。显示我知道多少！

回答by Naltharial

Generally speaking, this is not a question that has a "correct" answer. There is no "infinite length" text storage type in MySQL. You could use LONGTEXT, but that still has an (absurdly high) upper limit. Yet if you do, you're kicking your DBMS in the teeth for having to deal with that absurd blob of a column for your 50-character text. Not to mention the fact that you hardly do anything with it.

一般来说，这不是一个有“正确”答案的问题。MySQL 中没有“无限长度”文本存储类型。您可以使用LONGTEXT，但它仍然有一个（高得离谱的）上限。然而，如果你这样做了，你就会因为不得不为你的 50 个字符的文本处理那一列荒谬的 blob 而让你的 DBMS 大吃一惊。更不用说您几乎不使用它做任何事情的事实。

So, most futureproofness(TM) is probably offered by LONGTEXT. But it's also a very bad method of resolving the issue. Honestly, I'd revisit the application requirements. Storing strings that have no "domain" (as in, being well-defined in their application) and arbitrary length is not one of the strengths of RDBMS.

因此，大多数 futureproofness(TM) 可能由LONGTEXT. 但这也是解决问题的一种非常糟糕的方法。老实说，我会重新审视申请要求。存储没有“域”（如在其应用程序中定义明确）和任意长度的字符串并不是 RDBMS 的优势之一。

If I'd want to solve this on the "application design" level, I'd use NoSQL key-value store for this (and I'm as anti-NoSQL-hype as they get, so you know it's serious), even though I recognize it's a rather expensive change for such a minor change. But if this is an indication of what your DBMS is eventually going to hold, it might be more prudent to switch now to avoid this same problem hundred times in the future. Data domain is very important in RDBMS, whereas it's explicitly sidelined in non-relational solutions, which seems to be what you're trying to solve here.

如果我想在“应用程序设计”级别解决这个问题，我会为此使用 NoSQL 键值存储（而且我和他们一样反对 NoSQL 炒作，所以你知道这很严重），甚至虽然我承认对于这样一个微小的改变来说这是一个相当昂贵的改变。但是，如果这表明您的 DBMS 最终将保留什么，那么现在切换可能会更加谨慎，以避免在未来一百次出现同样的问题。数据域在 RDBMS 中非常重要，而它在非关系解决方案中被明确排除在外，这似乎是您在这里试图解决的问题。

Stuck with MySQL? Just increase it to VARCHAR(1000). If you have no requirements for your data, it's irrelevant what you do anyway.

被 MySQL 卡住了？只需将其增加到VARCHAR(1000). 如果您对数据没有要求，那么无论如何都无关紧要。

回答by sonia kaushal

Careful if using text. TEXT data is not stored in the database server's memory, therefore, whenever you query TEXT data, MySQL has to read from it from the disk, which is much slower in comparison with CHAR and VARCHAR as it cannot make use of indexes.The better way to store long string will be nosql databases

使用文本时要小心。TEXT 数据不存储在数据库服务器的内存中，因此，每当您查询 TEXT 数据时，MySQL 都必须从磁盘中读取，这与 CHAR 和 VARCHAR 相比要慢得多，因为它不能使用索引。更好的方法存储长字符串将是 nosql 数据库