What types of data exist in Mysql for text?

Question

What kind of data exists in Mysql?

What’s best for storing long texts? (HTML-shaped document for example) Why?

Answer 1

It depends on what you need. No details, context in question.

A VARCHAR allows 65535 characters (bytes if using an older version < 5.0). But this limit is lower in practice. The row size is also this, so you cannot have multiple columns of this type with large sizes. A single column can fill the entire row or it can have hundreds of columns VARCHAR. As the name says, the size effectively occupied varies depending on the need.

A TEXT allows 65535 bytes. And this differentiation is important if you are using UTF-8 or other encoding multibyte since a character can occupy 3 bytes (this may change according to the charset chosen - the use of 3 bytes was an error in the initial standard UTF-8, so there is another charset).

Just like the TINYTEXT supports 255 bytes. And if you need more than this you have to use MEDIUMTEXT (16MB) or LONGTEXT (4GB).

The storage of these types TEXT is done outside the normal line, is in specific area and does not take up space on the line (except for the pointer, which is very small, for the location of the data, so it does not pay to know that most texts will be small).

In Myisam a small part of the text is on the line, even though it has this rule. The occupation of the space on the line is also variable.

I won’t even mention the CHAR fixed size that seems to be well out of what it is needing.

To decision is not so simple.

It is obvious that texts in VARCHAR they need to have their sizes more controlled, they can’t be that big. When you don’t have as much control or know that the size will be quite large, ie it is a text and not just a simple string, have to use the TEXT even, there is no way.

The TEXT usually have slower access in most situations. Nothing very significant but it is common for it to need more access to the disk than the VARCHAR even having the same size. As the text is stored separately, it is like having another table and making a JOIN, although the process is different.

Temporary tables with any type of TEXT need to be on disk, which is much slower than the normal tables which can stay only in memory.

Using a TEXT in common indices (no full-text) it is necessary to determine the size of the key. You cannot index the whole column, you need to tell how many characters you want in the index. And another important point is that the texts in the key are always stored with the spaces so that all keys have the same size. This is inefficient and if you do not know what you are doing, you will get false results.

TEXT does not allow values DEFAULT, the VARCHAR yes.

I’ve seen some people say you should always use TEXT. This is not quite true. It has drawbacks. Use the VARCHAR until you have a reason to use the TEXT.

Your case seems to require at least one MEDIUMTEXT, not only by the size, but by the semantics of what you’re storing. But if some of these restrictions create problems for you and you can ensure that these Htmls are small and will not have multiple on the same line, don’t discard the VARCHAR.

Answer 2

The type of data you intend to use (TEXT) supports data at most around 65kb.

For a reference, the HTML returned from the news portal Globe with. has the size of ~48kb, and the BBC, more than 100kb.

The two examples above show that an HTML file can easily exceed the maximum limit of a field TEXT mysql.

In view of this, there would be two other types of text data that would be more appropriate to your case:

MEDIUMTEXT = supports up to ~16MB of data.

LONGTEXT = supports up to ~4.000MB of data.

By way of comparison, where a field is MEDIUMTEXT, it would support over 100,000 BBC "HTML codes" before reaching the limit. If it were a LONGTEXT, would be over 42 million copies of BBC HTML code.

So in your case, I imagine that a MEDIUMTEXT would already provide more than enough space to store HTML page codes.

^{References:
https://stackoverflow.com/questions/6766781/maximum-length-for-mysql-type-text
http://help.scibit.com/Mascon/masconMySQL_Field_Types.html
https://dev.mysql.com/doc/refman/5.0/en/string-type-overview.html}