Text types: VARCHAR, TEXT, CHAR and ENUM
35 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)Names, cities, codes, reviews and order statuses are all text, but they are not stored the same way. MySQL gives you VARCHAR for most text, TEXT for long writing, CHAR for fixed-length codes and ENUM for a short fixed list of words.
VARCHAR(n) and the length limit
VARCHAR(n) stores variable-length text of up to n characters. It uses only the space the text needs, so it is the default choice for names, emails, cities and titles.
VARCHAR(n) stores text of up to n characters. The "var" means variable: a 4-letter name in a VARCHAR(60) column uses space for 4 characters plus 1 or 2 bytes that record the length, not 60.
Example 1 · VARCHAR stores text up to a limit
CREATE TABLE shops ( name VARCHAR(60), area VARCHAR(40), phone VARCHAR(15), email VARCHAR(100));
INSERT INTO shops (name, area, phone, email) VALUES ('Lakeview Electronics', 'Koramangala', '+91 80 41101234', 'hello@lakeview.example'), ('City Books', 'T. Nagar', '044 2434 5577', 'tnagar@citybooks.example');
SELECT name, CHAR_LENGTH(name), phone FROM shops ORDER BY name;+----------------------+-------------------+-----------------+ | name | CHAR_LENGTH(name) | phone | +----------------------+-------------------+-----------------+ | City Books | 10 | 044 2434 5577 | | Lakeview Electronics | 20 | +91 80 41101234 | +----------------------+-------------------+-----------------+
What this code does
CHAR_LENGTH(name)counts the characters actually stored: 10 and 20. Each value uses only that much space, not 60.- The phone column is
VARCHAR(15), so it keeps the+91, the spaces and the leading0that a number type would lose.
Why a limit at all? The limit is a rule about your data. A name of 3,000 characters is a bug or an attack, and VARCHAR(60) stops it. Choose n from the longest real value plus some room: 60 for a person's name, 100 for an email, 15 for a phone number with +91. The n counts characters, not bytes. With the utf8mb4 character set, Hindi or Tamil letters take 3 bytes each and an emoji 4, but VARCHAR(10) still holds 10 of them.
Predict the output
Predict the length
CHAR_LENGTH counts characters, and a space is a character. What does this return?
TEXT for long text
TEXT stores long text such as a review or a complaint, up to about 64 KB. Longer versions are MEDIUMTEXT (16 MB) and LONGTEXT (4 GB).
TEXT stores long text such as a product review, a complaint or a blog post, up to 65,535 bytes (about 64 KB). For more there are MEDIUMTEXT (16 MB) and LONGTEXT (4 GB).
Example 2 · TEXT for reviews, and the VARCHAR size limit
CREATE TABLE app_reviews ( reviewer VARCHAR(60), stars TINYINT, review TEXT);
INSERT INTO app_reviews (reviewer, stars, review) VALUES ('Divya', 4, 'The phone arrived quickly and works well. The app froze once at payment, but UPI went through.');
SELECT reviewer, stars, CHAR_LENGTH(review) FROM app_reviews;
CREATE TABLE long_notes ( note VARCHAR(20000));+----------+-------+---------------------+ | reviewer | stars | CHAR_LENGTH(review) | +----------+-------+---------------------+ | Divya | 4 | 94 | +----------+-------+---------------------+ ERROR 1074 (42000): Column length too big for column 'note' (max = 16383); use BLOB or TEXT instead
What this code does
TEXTholds the review whatever its length (up to about 64 KB); no limit had to be guessed.VARCHAR(20000)is refused with error 1074: withutf8mb4, one VARCHAR can be at most 16,383 characters. The message itself suggestsTEXT.
Why not a huge VARCHAR? All the columns of one row together may use at most 65,535 bytes. With utf8mb4, MySQL counts 4 bytes per character, so one VARCHAR can be at most 16,383 characters, and two big ones already break the row limit. TEXT values are stored mostly outside the row, so they do not count against it.
TEXT has a few limits of its own: it cannot have a simple DEFAULT value, and it is slower to sort and search. Use VARCHAR for short values you search, sort or show in lists; use TEXT only for long free writing.
CHAR(n) for fixed codes
CHAR(n) stores text in a fixed width of n characters. It fits values that always have the same length, such as a PIN code or an IFSC code, and it drops trailing spaces when read back.
CHAR(n) stores text in a fixed width of n characters. Shorter values are padded with spaces on disk, and MySQL removes trailing spaces when you read them back.
Example 3 · CHAR for PIN codes and IFSC codes
CREATE TABLE bank_branches ( ifsc CHAR(11), branch VARCHAR(40), state_code CHAR(2), pin_code CHAR(6));
INSERT INTO bank_branches (ifsc, branch, state_code, pin_code) VALUES ('SBIN0001234', 'Jayanagar', 'KA', '560041'), ('HDFC0000567', 'Andheri East', 'MH', '400069'), ('ICIC0007788', 'Test branch', 'TN', '6000');
SELECT * FROM bank_branches ORDER BY ifsc;
SELECT ifsc, CONCAT('[', pin_code, ']') AS pin_in_brackets, CHAR_LENGTH(pin_code)FROM bank_branchesORDER BY ifsc;+-------------+--------------+------------+----------+ | ifsc | branch | state_code | pin_code | +-------------+--------------+------------+----------+ | HDFC0000567 | Andheri East | MH | 400069 | | ICIC0007788 | Test branch | TN | 6000 | | SBIN0001234 | Jayanagar | KA | 560041 | +-------------+--------------+------------+----------+ +-------------+-----------------+-----------------------+ | ifsc | pin_in_brackets | CHAR_LENGTH(pin_code) | +-------------+-----------------+-----------------------+ | HDFC0000567 | [400069] | 6 | | ICIC0007788 | [6000] | 4 | | SBIN0001234 | [560041] | 6 | +-------------+-----------------+-----------------------+
What this code does
CHAR(11)andCHAR(6)say "always this size". But the 4-digit PIN6000was accepted: CHAR limits the maximum length only.CONCATjoins pieces of text into one. The brackets show that6000comes back with no padding spaces and a length of 4, even though the column is 6 wide on disk.
Example 4 · CHAR drops trailing spaces, VARCHAR keeps them
CREATE TABLE space_test ( as_char CHAR(6), as_varchar VARCHAR(6));
INSERT INTO space_test VALUES ('ab ', 'ab ');
SELECT CONCAT('[', as_char, ']') AS char_value, CONCAT('[', as_varchar, ']') AS varchar_value, CHAR_LENGTH(as_char), CHAR_LENGTH(as_varchar)FROM space_test;+------------+---------------+----------------------+-------------------------+ | char_value | varchar_value | CHAR_LENGTH(as_char) | CHAR_LENGTH(as_varchar) | +------------+---------------+----------------------+-------------------------+ | [ab] | [ab ] | 2 | 4 | +------------+---------------+----------------------+-------------------------+
What this code does
- Both columns received
abfollowed by two spaces.CHARgives backab(length 2);VARCHARgives back all four characters. - This rarely matters for codes, but it explains why a value read from a CHAR column can be shorter than the value you sent.
Examples: a 6-digit PIN code CHAR(6), a state code such as KA in CHAR(2), an 11-character IFSC code such as SBIN0001234 in CHAR(11). The practice table courses stores codes such as CS201 in CHAR(6).
The benefit is small: no length bytes, and the column tells every reader "this is a code of fixed size". The trap is that CHAR(6) does not force exactly 6 characters: only values that are too long are refused. The Keys & Constraints module shows how a CHECK rule can demand the exact length.
ENUM for a fixed list, and its limits
ENUM allows only the values in its list, such as ENUM('PLACED', 'SHIPPED'). A value not in the list is refused, and each value is stored as a small number, so it is compact.
ENUM('A', 'B', ...) allows only the values in its list. The practice table orders has status ENUM('PLACED', 'SHIPPED', 'DELIVERED', 'CANCELLED'), so a typo such as 'DELIVRED' can never be stored.
Example 5 · ENUM: allowed values, case and sort order
CREATE TABLE order_tracking ( order_no INT, status ENUM('PLACED', 'PACKED', 'OUT_FOR_DELIVERY', 'DELIVERED'));
INSERT INTO order_tracking (order_no, status) VALUES (101, 'DELIVERED'), (102, 'placed'), (103, 'OUT_FOR_DELIVERY'), (104, 'PACKED');
SELECT order_no, status FROM order_tracking ORDER BY status;
INSERT INTO order_tracking (order_no, status) VALUES (105, 'CANCELLED');+----------+------------------+ | order_no | status | +----------+------------------+ | 102 | PLACED | | 104 | PACKED | | 103 | OUT_FOR_DELIVERY | | 101 | DELIVERED | +----------+------------------+ ERROR 1265 (01000): Data truncated for column 'status' at row 1
What this code does
'placed'was stored asPLACED, the spelling in the list.ORDER BY statusfollows the list order, the order a parcel moves through, not A to Z.'CANCELLED'is not in the list, so the insert fails with error 1265. Supporting cancellations means changing the list withALTER TABLE.
Three rules to know:
- A value not in the list is refused with
ERROR 1265 (01000): Data truncated for column 'status'. The message says "truncated", but it means "not allowed". ORDER BYsorts by list position, not A to Z:PLACEDbeforeSHIPPEDbeforeDELIVERED.- Adding a new value later, such as
'RETURNED', needs anALTER TABLE, a change to the table design. UseENUMfor short lists that almost never change: order status,standard/express, a T-shirt size. When the list can grow (cities, categories, colleges), use a separate table instead; the Keys & Constraints module links tables with foreign keys.
The data too long error
Error 1406, Data too long, appears when text is longer than the column allows. In strict mode the whole row is refused, so you never end up with half a value.
Error 1406 appears when text is longer than the column allows: ERROR 1406 (22001): Data too long for column 'city' at row 1. Strict mode, on by default, refuses the whole row, so you never end up with half a value.
Example 6 · Data too long, with and without strict mode
CREATE TABLE hostel_students ( name VARCHAR(30), city VARCHAR(5));
SET SESSION sql_mode = '';INSERT INTO hostel_students (name, city) VALUES ('Asha Rao', 'Bengaluru');SHOW WARNINGS;SELECT * FROM hostel_students;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';INSERT INTO hostel_students (name, city) VALUES ('Ravi Kumar', 'Chennai');+---------+------+-------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------+ | Warning | 1265 | Data truncated for column 'city' at row 1 | +---------+------+-------------------------------------------+ +----------+-------+ | name | city | +----------+-------+ | Asha Rao | Benga | +----------+-------+ ERROR 1406 (22001): Data too long for column 'city' at row 1
What this code does
- With strict mode off,
Bengaluruis silently cut toBenga, and only a warning (code 1265) records it. - With strict mode back on,
Chennai(7 characters) in aVARCHAR(5)is refused with error 1406 and the row is not stored. The fix is a longer column, for exampleVARCHAR(40).
Fix it by checking which value is too long (the message names the column and the row of the insert), then either correct the value or make the column longer with ALTER TABLE, which a later lesson of this module covers.
Without strict mode, MySQL cuts the text to fit and only adds a warning: 'Bengaluru' in a VARCHAR(5) becomes 'Benga'. Silent cuts like this damage names and addresses, which is one more reason to keep strict mode on.
Fix the error
Fix the column
Read the real error, then pick the line that fixes it.
Chennai is refused by this column.
Error
ERROR 1406 (22001): Data too long for column 'city' at row 1
Next step
Check your understanding
11 quick questions