mysql存储字符串_MySQL存储长字符串的最佳方法

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

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)

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.

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.

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!

解决方案

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.

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.

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.

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