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.
- Create a Database:
CREATE DATABASE store;
- Select the Database:
USE store;
- 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)
);
- 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');
- Query the Products Table:
SELECT * FROM products;
Output
id | code | name | category |
---|---|---|---|
1 | P001 | Laptop | Electronics |
2 | P002 | Smartphone | Electronics |
3 | P003 | Tablet | Electronics |
4 | P004 | Chair | Furniture |
5 | P005 | Desk | Furniture |
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
id | code | name | category |
---|---|---|---|
1 | P001 | Laptop | Electronics |
2 | P002 | Smartphone | Electronics |
3 | P003 | Tablet | Electronics |
Example: Query Products with a Specific Code
SELECT * FROM products WHERE code = 'P004';
Output
id | code | name | category |
---|---|---|---|
4 | P004 | Chair | Furniture |
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 usingVARCHAR
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 retrievingCHAR
values, these padding spaces are removed automatically. - Performance: The
CHAR
data type can be more efficient thanVARCHAR
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.