Introduction
If you frequently find yourself working with relational database instances, and especially MariaDB or MySQL, you probably already know the nuances of a couple of data types offered by MySQL. Some of the data types provided by MySQL are suited for numbers; others fit variable-length values, of which some are also good fits for text-based values.
What Data Types are Available in MySQL?
Before explaining how to tune MySQL instances for specific (in this case, TEXT) data types, we must go over some of the data types offered by MySQL to make you understand how everything works in the first place. When it comes to data types, MySQL offers a few categories to choose from:
- String data types – such data types might be helpful when the bulk of our data consists of strings or variable character values (both strings and numbers.)
- Numeric data types – such data types might prove to be worth their salt if the data we are working on mainly consists of numbers.
- Date data types – such data types, as the name already suggests, store dates: they might be helpful if a need to store years, months, days, etc., arises.
- Spatial (geographic) data types – such data types can be useful to store polygon, geometric values, or the like.
- The
JSONdatatype – such data type can be helpful when storing JSON-formatted text.
For this blog post, we are interested in string values—specifically, TEXT values. TEXT values differ from all of the rest even in the string space; while there are various string types available ( CHAR, VARCHAR, BINARY, and BLOB being some of them), we have chosen TEXT because it’s different; it differs from, for example, VARCHAR, because for VARCHAR, we can set a variable maximum character size (that’s why it’s called VARCHAR) and the maximum character size must be between 1 and 65,535, but with TEXT datatype, a fixed maximum length of 65,535 characters is already set. Bear in mind that TEXT-based columns, generally, cannot be a part of indexes too: we cannot index TEXT-based columns as quickly as VARCHAR types.
Limitations of TEXT-based Values
By now, you should have a basic understanding of the capabilities of TEXT-based workloads. Here are some nuances of the TEXT string data type in MySQL:
TEXT-based Limitation |
Explanation |
|---|---|
| No default values |
TEXT-based columns cannot have preset DEFAULT values that VARCHAR columns can have. |
| Indexes | Contrary to what some people might believe, you can use indexes with TEXT-based columns in MySQL. However, when indexes on such columns are used, an index prefix length must be specified. |
| Data Storage Requirements |
TEXT-based values take up 2+ stored string length bytes of disk space. |
| Variations |
TEXT also has multiple variations. Available options include TINYTEXT, SMALLTEXT, MEDIUMTEXT, and LONGTEXT. |
| Variation Limitations |
TINYTEXT can store values up to 255 bytes in size. 1 character = 1 byte. Maximum size – 255 bytes.TEXT supports up to 65,535 characters. Maximum size – 64kB.MEDIUMTEXT supports up to 16,777,215 characters. Maximum size – 16MB.LONGTEXT supports up to 4,294,967,295 characters, maximum size – 4GB. All variations are very similar – the only difference is the amount of data that can be stored. |
Other than that, in general, data types derived off of the TEXT data type can be beneficial if we find ourselves storing longer text values: say, pieces of code or the like. VARCHAR might be a fit if we are storing usernames, email addresses, or hashed and salted passwords (salts prevent hashes from being cracked as fast, so please consider both hashing and salting your passwords – data breaches are rampant). TEXT data types might be just what we need if we want to store text that looks more like a small story but does not consist only of a few characters.
When to Use TEXT?
By now, you should have some understanding as to what kind of a beast you’re dealing with; indeed, TEXT-based values are another data type breed altogether. What makes them so different is that they are specifically suited for storing vast amounts of text. Text can not necessarily mean “text” in a traditional sense; you can store numbers as well, but remember that TEXT is not VARCHAR – once you elect to choose TEXT, be ready for particular challenges. Some are outlined above (you cannot add an index on the entire column, etc.), while others are lesser-known but still important to keep in mind. For example, TEXT data types would generally only be helpful if our database instances have a lot, and I mean, a lot of storage space since MEDIUMTEXT can store strings up to 16MB in size, and if we elect to use LONGTEXT, the limit is increased to 4GB.
In general, though, any variant of the TEXT datatype is useful if our application needs to store longer text strings and where we might find that VARCHAR just does not cut it.
SQL Clients?
Data types are, of course, important, but what’s just as important is choosing the correct SQL client to perform your tasks. SQL clients like Arctype are fast, and they provide you with the data types your MySQL instance is using, as well as some insight into how to optimize your schema further :
Anyway, we hope that this blog post has provided you with some insight into the TEXT data type world of MySQL. So, if you enjoyed reading, make sure to explore more content, and see you in the next one.
Lukas is an ethical hacker, a MySQL database administrator, and a frequent conference speaker. Since 2014 Lukas has found and responsibly disclosed security flaws in some of the most visited websites in Lithuania and abroad including advertising, gift-buying, gaming, hosting websites as well as some websites of government institutions. Lukas runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com and frequently blogs in multiple places educating people about information security and other topics. He also runs his own blog over at lukasvileikis.com
Top comments (0)