This blog is a comprehensive guide on all categories of SQL data types, including an in-depth guide on string data types and when to use them.
In My SQL, we have several categories of data types, and it is essential to understand when to use which sort of data type, so in this blog, we will give a list of all SQL data type categories:
In My SQL, we have several categories of data types; we have
- String Type
- Numeric Type
- Data & Time Type
- Blob Type (For Storing Binary Data)
- Spatial type ( For Storing Geometric and Geographical Values)
In this blog, we will explore All Data Types of String Category in Depth and go into detail about that.
String Types:
In the string category, we have a bunch of data types. The most common of these are
CHAR
- For fixed-length strings, e.g., abbreviation of State (Los Angeles LA)
VARCHAR
- For variable length strings like username and password.
- Max Limit: 65,535 ( Characters exceeding the limit will be truncated)
- Space: 64 KB.
What if I want to store a more significant value?
In that case, you can use
MEDIUMTEXT
- This type that allows you to store roughly around 16 million characters.
- Takes space of up to 16 MB
- Ideal for: Storing JSON Objects, CSV’s
In case we want to store even more extensive data, we use the following data types of string categories:
LONGTEXT
- Takes Space up to 4GB
- User Case: Storing full books and years of log files.
These are the main types you would be using most of the time. But we also have some other data types, too
TINYTEXT:
- Can store a maximum of 255 bytes
TEXT:
- Can store a maximum of 64KB
Choosing Between TEXT and VARCHAR Data Types in SQL: Which One Should You Use?
VARCHAR, Yes, that’s the answer and the reason is VARCHAR TYPES get indexed and indexing is used to speed up database queries.
SQL Data Types: Why String Types are Better for Storing Phone Numbers and Other Non-Numeric Data?
Note: These types can be used for string numeric values too such as Zip code and phone numbers as we are not applying any mathematical operation on it.
Another reason for using these types is sometimes phone numbers are stored with a ‘-‘ sign so it is better to store them as a string than numerical or any other type.