MySQL CHAR Data Type

Introduction

In this chapter, we will learn about the CHAR data type in MySQL. The CHAR data type is used to store fixed-length character strings. It is particularly useful for storing strings where the length is consistent, such as codes, fixed-size identifiers, or short strings. We will cover the syntax for defining CHAR columns, examples, and important considerations for using CHAR in MySQL.

Syntax

The basic syntax for defining a CHAR column in MySQL is:

CHAR(length)

• length: Specifies the fixed length of the character string. The length can be any value from 0 to 255.

Example

Here is an example of how to define a CHAR column in a table:

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code CHAR(10),
    name VARCHAR(100) NOT NULL
);

In this example, code is defined as a CHAR(10) column to store a fixed-length product code.

Full Example

Let’s go through a complete example where we create a database and a table with CHAR columns, insert data, and demonstrate their usage.

1. Create a Database:

CREATE DATABASE store;

2. Select the Database:

USE store;

3. Create the Products Table with CHAR Columns:

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code CHAR(10),
    name VARCHAR(100) NOT NULL,
    category CHAR(20)
);

4. Insert Data into the Products Table:

INSERT INTO products (code, name, category) VALUES
('P001', 'Laptop', 'Electronics'),
('P002', 'Smartphone', 'Electronics'),
('P003', 'Tablet', 'Electronics'),
('P004', 'Chair', 'Furniture'),
('P005', 'Desk', 'Furniture');

5. Query the Products Table:

SELECT * FROM products;

Output

In this example, the code and category columns are defined as CHAR to store fixed-length strings for product codes and categories.

Using CHAR Values in Queries

You can use CHAR values in queries to filter and sort data.

Example: Query Products by Category

SELECT * FROM products WHERE category = 'Electronics';

Output

Example: Query Products with a Specific Code

SELECT * FROM products WHERE code = 'P004';

Output

Important Considerations

• Fixed Length: The CHAR data type is best used for columns where the length of the string is consistent. If the length varies significantly, consider using VARCHAR instead.
• Storage Requirements: The CHAR data type always uses the specified length, even if the actual string is shorter. This can lead to wasted storage space if the strings are often shorter than the defined length.
• Padding: CHAR values are padded with spaces to the specified length. When retrieving CHAR values, these padding spaces are removed automatically.
• Performance: The CHAR data type can be more efficient than VARCHAR for short, fixed-length strings because it avoids the overhead of variable-length storage.

Conclusion

The CHAR data type is useful for storing fixed-length character strings in MySQL. This chapter covered the syntax for defining CHAR columns, provided a full example with different character attributes, and discussed important considerations.