MySQL String Data Type

What Are String Data Types and Why Do We Use Them?

String data types are used to store text-based values in MySQL, including names, addresses, email IDs, phone numbers, product names, and other character data such as "John", "Delhi", "Laptop", and "john@gmail.com". When you create a table in MySQL, you define the string data type (such as CHAR, VARCHAR, TEXT, etc.) for a column so that it can store text values of the specified type.

In MySQL, we choose string data types when creating a table so that a column is designed to store text values rather than other types of data, such as numbers. String data types help MySQL understand what type of values a particular column can store and how those values should be processed. They also help MySQL determine how much storage space is required for storing that data.

Syntax:

CREATE TABLE table_name (
    column_name CHAR(length),
    column_name2 VARCHAR(length),
    column_name3 TEXT
);

Example:

Imagine you want to create a table to store customer information, where customer_name stores a customer's name, email stores an email address, and address stores a long address. In this case, you use string data types.

Query:

CREATE TABLE customers (
    customer_name CHAR(20),
    email VARCHAR(100),
    address TEXT
);

Insert Values into the Table:

INSERT INTO customers (customer_name, email, address)
VALUES ('John', 'john@gmail.com', 'New Delhi, India');

Output:

customer_name | email             | address
--------------------------------------------
John          | john@gmail.com    | New Delhi, India

Explanation:

• customer_name stores fixed-length text values.
• email stores variable-length text values.
• address stores long text values.

How MySQL Internally Processes and Stores String Data Types

When you create a table in MySQL, you specify all the information about the column. In that information, you specify the data type and the type of values it will store. You choose a specific column in the table and define a string data type such as CHAR, VARCHAR, or TEXT for that column.

When you insert a value into the column, MySQL checks whether the value is correct according to the selected data type. It also checks whether the value is within the allowed length and determines how much storage space is needed to store that value.

For example, if a column is defined as VARCHAR(20) and you insert the value John, MySQL checks whether the value fits within the specified length, converts it into an internal string format, and stores it in the database storage system.

Classification of String Data Types

MySQL divides string data types into the following categories:

1. Fixed-Length String Types

   This data type stores text values with a fixed length. It is called the Fixed-Length String data type.

   For example:

   CHAR(10) → John
   

   Where,

   • CHAR(10) = Data Type
   • John = String Value

2. Variable-Length String Types

   This data type stores text values with a variable length. It is called the Variable-Length String data type.

   For example:

   VARCHAR(100) → john@gmail.com
   

   Where,

   • VARCHAR(100) = Data Type
   • john@gmail.com = String Value

3. Large Text Types

   This data type stores large amounts of text. It is called the Large Text data type.

   For example:

   TEXT → This is a product description.
   

   Where,

   • TEXT = Data Type
   • This is a product description. = String Value

4. Binary String Types

   This data type stores binary data such as images, audio, videos, and other binary files. It stores data as bytes instead of characters.

   For example:

   BLOB → profile_photo
   

   Where,

   • BLOB = Data Type
   • profile_photo = Binary Data

5. ENUM and SET Types

   These data types store predefined string values. ENUM stores only one value from a predefined list, whereas SET stores one or more values from the predefined list.

   For example:

   ENUM('Male','Female') → Male
   
   SET('HTML','CSS','PHP') → HTML,CSS
   

   Where,

   • ENUM = Stores one predefined value.
   • SET = Stores one or more predefined values.

Common Mistakes to Avoid When Choosing String Data Types

You should avoid mistakes when choosing string data types; keep the following points in mind:

1. You should avoid using the CHAR data type for values whose length changes frequently.
2. You should not use the TEXT data type to store short text values because VARCHAR is usually more appropriate.
3. You should carefully choose the maximum length of the VARCHAR data type according to the data you want to store.
4. You should always make sure that the text value does not exceed the maximum length allowed by the selected data type.
5. You should not choose a larger string data type if a smaller one is sufficient because it can waste storage space.
6. You should use the CHAR data type only when all values have the same length.
7. You should use the TEXT data type only when you need to store large amounts of text.

How Do You Choose the Right String Data Type in MySQL?

The following points cover how to choose the right string data type if you create a table in MySQL:

1. Check the Value Length:

   You should choose the data type according to the length of the text you need to store. Use CHAR for fixed-length values and VARCHAR for variable-length values.

   Example:

   CHAR(5) → code → A102
   
   VARCHAR(100) → name → Rahul Sharma
   

   Here,

   The variable code uses the CHAR data type because it stores a fixed-length value.

   The variable name uses the VARCHAR data type because it stores a variable-length value.

2. Check the Maximum Storage Requirement:

   You should choose a data type according to the maximum amount of text your column may contain.

   Common Maximum Length of String Data Types

   Data Type	Maximum Length
   CHAR	255 Characters
   VARCHAR	Up to 65,535 Bytes*
   TINYTEXT	255 Bytes
   TEXT	65,535 Bytes
   MEDIUMTEXT	16,777,215 Bytes
   LONGTEXT	4,294,967,295 Bytes

3. Check the Type of Data:

   You should choose the data type according to the type of text you want to store, such as short text, long text, or predefined values.

   Example:

   VARCHAR(100) → email → john@gmail.com
   
   TEXT → description → This is a detailed product description.
   
   ENUM('Male','Female') → gender → Male
   

   Here,

   The variable email uses the VARCHAR data type because it stores normal text values.

   The variable description uses the TEXT data type because it stores large amounts of text.

   The variable gender uses the ENUM data type because it stores one value from a predefined list.

4. Consider Storage Space:

   You should choose the appropriate data type so that storage space is not wasted. If your text values are short, you can choose a smaller string data type. If your text values are long, you can choose a larger string data type.

   Example:

   CHAR(5) → code → A102
   
   TEXT → description → This is a detailed product description.
   

   Here,

   The variable code uses the CHAR data type because it stores a short fixed-length value.

   The variable description uses the TEXT data type because it stores a large amount of text.