MySQL - VARCHAR

MySQL: The VARCHAR Data Type

Understanding VARCHAR

VARCHAR(M) is one of the most common data types in MySQL, used for storing variable-length character strings. The M represents the maximum number of characters you want to store, with a theoretical maximum of 65,535.

Unlike CHAR(M), which always uses M characters of storage space regardless of the string’s actual length, VARCHAR only uses the space required by the string itself, plus a small prefix of 1 or 2 bytes to store the length of the string. This makes it highly efficient for storing data with varying lengths, like names, emails, or comments.

Character Sets and Row Size Limits

A critical consideration when using VARCHAR is the character set of the column. A character set defines which characters are valid and how they are stored in bytes. For modern applications, it is a strong best practice to use utf8mb4.

• Best Practice: Always use CHARACTER SET utf8mb4 to support a full range of Unicode characters, including emojis and various international symbols.
• Row Size Limit: MySQL has a maximum row size limit of 65,535 bytes. The storage requirement of a VARCHAR column counts towards this limit. For a utf8mb4 column, each character can consume up to 4 bytes. Therefore, a VARCHAR(255) column with utf8mb4 will reserve 255 * 4 = 1020 bytes in the row size calculation, even if the stored data is shorter.

Example: Row Size Exceeded

Let’s try to create a table that exceeds the maximum row size. A VARCHAR column with CHARACTER SET utf8mb4 can have a maximum length of 16,383 (16383 * 4 + 2 is just under 65,535 bytes).

CREATE TABLE Too_Large_Table (
    col1 VARCHAR(10000) NOT NULL,
    col2 VARCHAR(10000) NOT NULL
) CHARACTER SET 'utf8mb4';

This will fail because the maximum potential size (10000 * 4 + 10000 * 4 + length prefixes) exceeds 65,535 bytes.

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs.

For very large strings, use TEXT or BLOB types, which are stored off-page and do not contribute to the main row size limit in the same way.

Data Truncation and Trailing Spaces

MySQL’s behavior with VARCHAR can have some quirks, especially regarding data length and spaces.

Example: Data Too Long

If you try to insert a string longer than the defined VARCHAR length, MySQL (in strict mode, which is default and recommended) will reject the insertion and raise an error.

CREATE TABLE Products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_code VARCHAR(5) NOT NULL
);

-- This will fail
INSERT INTO Products (product_code) VALUES ('TOOL-A123');

Output

ERROR 1406 (22001): Data too long for column 'product_code' at row 1

Example: Trailing Spaces

When storing values, MySQL truncates trailing spaces from VARCHAR columns. It will store the value but may issue a warning.

INSERT INTO Products (product_code) VALUES ('A12  '); -- Note the two trailing spaces

The insertion succeeds, but with a warning.

Query OK, 1 row affected, 1 warning (0.01 sec)

If we now retrieve the data and check its length, we see the spaces have been removed.

SELECT product_code, LENGTH(product_code) FROM Products WHERE id = 2;

product_code	LENGTH(product_code)
A12	3

This behavior is important to remember, as it can affect data comparisons if not handled consistently in your application.

Using VARCHAR in Client Programs

When defining table schemas from application code, it’s crucial to specify VARCHAR columns correctly, including the character set and collation.

Python
Node.js
Java
PHP



This Python example creates a table using `utf8mb4` and demonstrates handling a data truncation error.

import mysql.connector
from mysql.connector import errorcode

DB_CONFIG = {'host':'localhost', 'user':'root', 'password':'your_password', 'database':'your_db'}

def setup_varchar_table():
    try:
        with mysql.connector.connect(**DB_CONFIG) as cnx:
            with cnx.cursor() as cursor:
                # Best practice: use utf8mb4
                create_table_sql = """
                    CREATE TABLE Users (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        username VARCHAR(20) NOT NULL UNIQUE
                    ) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                """
                cursor.execute(create_table_sql)
                print("Table 'Users' created successfully.")
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
            print("Table 'Users' already exists.")
        else:
            print(f"Error: {err}")

setup_varchar_table()

Output

Table 'Users' created successfully.



This Node.js example uses `mysql2/promise` to create a table and shows how to check column metadata from `INFORMATION_SCHEMA`.

const mysql = require('mysql2/promise');

const DB_CONFIG = {host: 'localhost', user: 'root', password: 'your_password', database: 'your_db'};

async function manageVarcharTable() {
    let connection;
    try {
        connection = await mysql.createConnection(DB_CONFIG);
        const createTableSQL = `
            CREATE TABLE IF NOT EXISTS Documents (
                doc_id CHAR(36) PRIMARY KEY,
                title VARCHAR(255) NOT NULL
            ) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;`;
        await connection.execute(createTableSQL);
        console.log("Table 'Documents' is ready.");

        const [cols] = await connection.execute(
            `SELECT CHARACTER_SET_NAME, COLLATION_NAME
             FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_NAME = 'Documents' AND COLUMN_NAME = 'title'`
        );
        console.log('Title column info:', cols[0]);

    } catch (error) {
        console.error(`Database operation failed: ${error.message}`);
    } finally {
        if (connection) await connection.end();
    }
}

manageVarcharTable();

Output

Table 'Documents' is ready.
Title column info: { CHARACTER_SET_NAME: 'utf8mb4', COLLATION_NAME: 'utf8mb4_unicode_ci' }



This Java example uses JDBC to create a table, explicitly setting the character set and collation for the `VARCHAR` column.

import java.sql.*;

public class VarcharExample {
    static final String DB_URL = "jdbc:mysql://localhost:3306/your_db";
    static final String USER = "root";
    static final String PASS = "your_password";

    public static void main(String[] args) {
        String sql = "CREATE TABLE Employees (" +
                     "  id INT PRIMARY KEY AUTO_INCREMENT," +
                     "  full_name VARCHAR(100) NOT NULL" +
                     ") ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             Statement stmt = conn.createStatement()) {
            stmt.executeUpdate("DROP TABLE IF EXISTS Employees"); // for fresh start
            stmt.executeUpdate(sql);
            System.out.println("Table 'Employees' with VARCHAR column created successfully.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Output

Table 'Employees' with VARCHAR column created successfully.



This PHP example uses the `mysqli` extension to create a table with a `VARCHAR` column.

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'your_password';
$dbname = 'your_db';

$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Use IF NOT EXISTS to prevent errors on subsequent runs
$sql = "CREATE TABLE IF NOT EXISTS Categories (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(50) NOT NULL UNIQUE,
            description VARCHAR(255)
        ) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";

if ($mysqli->query($sql) === TRUE) {
    echo "Table 'Categories' with VARCHAR columns is ready.";
} else {
    echo "Error creating table: " . $mysqli->error;
}

$mysqli->close();
?>

Output

Table 'Categories' with VARCHAR columns is ready.