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.
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 differ from all of the rest even in the string space; while there are various string types available (
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
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:
|No default values||
Contrary to what some people might believe, you can use indexes with
|Data Storage Requirements||
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.
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.
Got SQL or database questions? Join the Arctype forum.