问题描述
我正在从mySQL专家那里寻求一些有关存储长字符串数据的最佳方法的建议.
I'm looking for some advice on the best way to store long strings of data from the mySQL experts.
我有一个通用表，该表用于存储任何类型的数据，这意味着它应该能够容纳字母数字和数字数据.当前，表结构很简单，具有一个ID，实际数据存储在单个列中，如下所示:
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 INT(11)
data VARCHAR(128)
我现在需要存储大量数据(最多500个字符)，并且想知道最好的方法是仅增加varchar列大小，还是应该添加新列(TEXT类型)列?)，因为我需要存储更长的字符串.
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.
如果有任何专家有任何建议，我将为您倾听！我的首选方法是简单地增加varchar列，但这是因为我很懒.我正在运行的mySQL版本是5.0.77.
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.
我应该提到新的500个字符的要求仅适用于奇数记录；表中的大多数记录将不超过50个字符.我以为我将列128成为未来的证明.这表明我知道多少！
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!
推荐答案
通常来说，这不是一个具有正确"答案的问题. MySQL中没有无限长"文本存储类型.您可以使用LONGTEXT，但是它仍然有一个(非常高的)上限.但是，如果这样做，您将不得不面对DBMS的麻烦，因为它必须处理50个字符的文本列的那个荒谬的斑点.更不用说您几乎不做任何事情了.
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.
因此，大多数futureproofnessTM可能由LONGTEXT提供.但这也是解决问题的一种非常糟糕的方法.老实说，我会重新审视应用程序需求.存储没有域"(在其应用程序中定义明确)且具有任意长度的字符串不是RDBMS的优势之一.
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.
如果我想在应用程序设计"级别上解决此问题，我将为此使用NoSQL键值存储(而且我对它们的反NoSQL炒作是如此，所以您知道这很严重) )，尽管我知道对于这样的微小更改而言，这是一笔相当昂贵的更改.但是，如果这表明您的DBMS最终将要保持什么状态，则最好现在进行切换，以避免将来数百次避免相同的问题.数据域在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.
坚持使用MySQL吗?只需将其增加到VARCHAR(1000).如果您对数据没有要求，那么无论如何都无关紧要.
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存储长字符串的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！