MariaDB Tutorial / Choose String Types: CHAR, VARCHAR, and TEXT

Consider CHAR for fixed-length codes, VARCHAR for variable-length names and titles, and TEXT for longer descriptions. Their length declarations are not interchangeable with byte counts: check the character set, trailing-space behavior, and collation as well as the type name.

Choose a type from the value and its use

A fixed-length value does not make CHAR universally faster. Storage engine, encoding, actual lengths, and access patterns affect performance. Start with a sensible input policy and compare real queries if performance becomes a problem. VARCHAR(100) allows up to the declared character limit; it does not require exactly 100 characters. Storage capacity differs from transforming query output. Join, Extract, Replace, and Measure Strings compares character and byte counts alongside string transformations.

The example uses plain TEXT, whose standard capacity is 65,535 bytes. Multibyte text therefore cannot be assumed to fit 65,535 characters. For larger content, consider MEDIUMTEXT or LONGTEXT as appropriate. MariaDB also accepts an optional TEXT(M) form that chooses a sufficiently large member of the TEXT family; do not treat M as a VARCHAR-style promise to reject every longer string. See the TEXT reference for that syntax and its capacity rules.

Separate character counts from byte counts

A character set specifies how characters are represented as bytes. This exercise uses utf8mb4 for Unicode, including accented letters and emoji. Use the UTF-8 client connection described in the SQL-file guide as well as an appropriate table definition.

A collation controls comparison and ordering. It is different from the encoding: storing different strings does not necessarily make every comparison distinguish them. The database character-set and collation guide explains the setting boundaries. Try this under a utf8mb4 connection:

SELECT CHAR_LENGTH('café') AS characters,
       LENGTH('café') AS bytes;

The result is 4 characters and 5 bytes: the precomposed é uses two UTF-8 bytes while each of the other three letters uses one. CHAR_LENGTH counts database characters, not every user-perceived glyph or combined emoji. Applications may need a separate Unicode policy for visible-character limits.

Compare fixed and variable strings with the same input

Connect as course_user to the practice server prepared in the installation guides, with the shopcourse database available. The command-line client and SQL-file guide explains the connection and SOURCE command. Run these checks inside that connection, not in the operating-system shell:

SELECT VERSION() AS server_version, DATABASE() AS selected_database;
USE shopcourse;
SELECT DATABASE() AS selected_database;

The selected database should be shopcourse. Save a complete normal example as a UTF-8 .sql file and run it with SOURCE and its actual local path. Keep the intentionally failing statements separate: an error can stop a source file. Use a learning instance, not a connection containing production data.

Create a separate string_lab table. The example saves the session mode and explicitly selects STRICT_TRANS_TABLES, excluding modes such as PAD_CHAR_TO_FULL_LENGTH from this comparison. This makes the trailing-space behavior and the later length error easier to interpret:

USE shopcourse;
SET @saved_sql_mode = @@SESSION.sql_mode;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
CREATE TABLE string_lab (
  item_id INT PRIMARY KEY,
  fixed_code CHAR(4) NOT NULL,
  title VARCHAR(4) NOT NULL,
  description TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO string_lab VALUES
  (1, 'AB  ', 'AB  ', 'Product description'),
  (2, 'CD', 'é🙂', NULL);
SELECT item_id, CONCAT('[', fixed_code, ']') AS fixed_display,
       CHAR_LENGTH(fixed_code) AS fixed_chars,
       CONCAT('[', title, ']') AS title_display,
       CHAR_LENGTH(title) AS title_chars, LENGTH(title) AS title_bytes
FROM string_lab ORDER BY item_id;
SELECT CHAR_LENGTH('café') AS characters,
       LENGTH('café') AS bytes;

CONCAT places brackets around each value so trailing spaces can be observed. Under this mode, CHAR retrieval removes the padding after AB, while VARCHAR retains the two spaces. The second title is two characters but six bytes, and it fits VARCHAR(4). Treating the declared length as a four-byte limit would lead to the wrong conclusion. The CHAR trailing-space rules and VARCHAR reference explain the distinction.

Handle excessive length as an input-policy decision

Run this intentional failure separately, in the same strict-mode connection. The title column accepts at most four characters and abcde contains five:

INSERT INTO string_lab VALUES (3, 'EF', 'abcde', NULL);

Expect a Data too long error, then verify that SELECT COUNT(*) FROM string_lab; still returns two. Do not silently truncate names or identifiers to make an insert work. Decide whether the allowed length should change or the input should be rejected.

Disabling strict mode can change an error into truncation and a warning. Inputs whose excess consists of spaces can also have different handling, so do not generalize this non-space example to every possible overflow. Application validation gives helpful feedback; database limits maintain a boundary even when another client or file writes the data.

Distinguish text storage from comparison and binary data

TEXT may suit long product descriptions, but that does not make it the right type for every short code or frequently filtered column. Consider how a column is selected, sorted, indexed, or checked for duplicates. Index-length and prefix-index restrictions depend on the engine, encoding, and index being used.

Although VARCHAR preserves trailing spaces in this example, utf8mb4_unicode_ci comparisons may ignore relevant differences in case or padding. Different stored spellings do not guarantee that a UNIQUE check will accept both. The unique-key exercise demonstrates a case-insensitive duplicate.

Images and compressed files are byte data with different requirements. Do not use TEXT as a general-purpose container for every file format; consider binary types or separate file storage. This tutorial’s examples concern human-readable text.

Verify the definitions and repeat the exercise

• Compare CHAR_LENGTH and LENGTH without assuming they measure the same thing.
• Observe what happens to CHAR padding when values are retrieved.
• Inspect the character set and collation with SHOW CREATE TABLE string_lab.
• Distinguish a NULL description from an empty string, as explained in the NULL and default-value guide.

SET SESSION sql_mode = @saved_sql_mode;

Restore these session settings in the same connection that saved the variables. If you disconnected, those variables no longer contain the original values; inspect the new connection instead of applying an empty saved variable.

Before repeating the exercise, use SELECT DATABASE(); and SHOW CREATE TABLE table_name;, substituting each actual table name, to confirm that the targets are the disposable tables created here. Back up any data you need to keep. Only after verifying those targets, remove them with:

DROP TABLE shopcourse.string_lab;

This removes both the named tables and their rows. A data-definition statement such as DROP TABLE is not undone by an ordinary ROLLBACK. You can then rerun the complete normal example. The existing products table from the first product-table exercise is deliberately outside this reset.