MySQL - VARCHAR

MySQL：VARCHAR 数据类型

理解 VARCHAR

VARCHAR(M) 是 MySQL 中最常见的数据类型之一，用于存储变长字符串。M 表示您希望存储的最大字符数，理论最大值为 65,535。

与 CHAR(M) 不同，后者无论字符串实际长度如何，总是占用 M 个字符的存储空间，VARCHAR 仅使用字符串本身所需的空间，外加 1 或 2 字节的小前缀来存储字符串的长度。这使得它在存储长度可变的数据（如姓名、电子邮件或评论）时效率很高。

字符集和行大小限制

使用 VARCHAR 时的一个关键考虑因素是列的字符集。字符集定义了哪些字符是有效的以及它们如何以字节形式存储。对于现代应用程序，强烈推荐使用 utf8mb4。

• 最佳实践： 始终使用 CHARACTER SET utf8mb4 以支持完整的 Unicode 字符范围，包括表情符号和各种国际符号。
• 行大小限制： MySQL 的最大行大小限制为 65,535 字节。VARCHAR 列的存储需求会计入此限制。对于 utf8mb4 列，每个字符最多可占用 4 字节。因此，一个 VARCHAR(255) 的 utf8mb4 列将在行大小计算中保留 255 * 4 = 1020 字节，即使存储的数据较短。

示例：超出行大小

让我们尝试创建一个超出最大行大小的表。使用 CHARACTER SET utf8mb4 的 VARCHAR 列最大长度可以是 16,383（16383 * 4 + 2 略小于 65,535 字节）。

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

这将失败，因为最大潜在大小（10000 * 4 + 10000 * 4 + 长度前缀）超过 65,535 字节。

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.

对于非常大的字符串，请使用 TEXT 或 BLOB 类型，它们存储在页面外，不会以相同方式计入主行大小限制。

数据截断和尾随空格

MySQL 对 VARCHAR 的行为可能会有一些怪癖，尤其是在数据长度和空格方面。

示例：数据过长

如果您尝试插入一个比 VARCHAR 定义长度更长的字符串，MySQL（在严格模式下，这是默认且推荐的）将拒绝插入并引发错误。

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

-- 这将失败
INSERT INTO Products (product_code) VALUES ('TOOL-A123');

输出

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

示例：尾随空格

存储值时，MySQL 会截断 VARCHAR 列中的尾随空格。它会存储该值，但可能会发出警告。

INSERT INTO Products (product_code) VALUES ('A12  '); -- 注意两个尾随空格

插入成功，但带有警告。

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

如果我们现在检索数据并检查其长度，我们会发现空格已被删除。

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

产品代码	LENGTH(产品代码)
A12	3

记住这个行为很重要，因为它如果未在您的应用程序中一致处理，可能会影响数据比较。

在客户端程序中使用 VARCHAR

从应用程序代码定义表模式时，正确指定 VARCHAR 列至关重要，包括字符集和排序规则。

Python
Node.js
Java
PHP



此 Python 示例使用 `utf8mb4` 创建表，并演示了如何处理数据截断错误。

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:
                # 最佳实践：使用 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("表 'Users' 创建成功。")
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
            print("表 'Users' 已存在。")
        else:
            print(f"错误: {err}")

setup_varchar_table()

输出

表 'Users' 创建成功。



此 Node.js 示例使用 `mysql2/promise` 创建表，并展示如何从 `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("表 'Documents' 已准备就绪。");

        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 列信息:', cols[0]);

    } catch (error) {
        console.error(`数据库操作失败: ${error.message}`);
    } finally {
        if (connection) await connection.end();
    }
}

manageVarcharTable();

输出

表 'Documents' 已准备就绪。
Title 列信息: { CHARACTER_SET_NAME: 'utf8mb4', COLLATION_NAME: 'utf8mb4_unicode_ci' }



此 Java 示例使用 JDBC 创建表，明确设置 `VARCHAR` 列的字符集和排序规则。

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"); // 用于全新开始
            stmt.executeUpdate(sql);
            System.out.println("带有 VARCHAR 列的表 'Employees' 创建成功。");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

输出

带有 VARCHAR 列的表 'Employees' 创建成功。



此 PHP 示例使用 `mysqli` 扩展创建带有 `VARCHAR` 列的表。

<?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);
}

// 使用 IF NOT EXISTS 以防止后续运行出现错误
$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 "带有 VARCHAR 列的表 'Categories' 已准备就绪。";
} else {
    echo "创建表出错: " . $mysqli->error;
}

$mysqli->close();
?>

输出

带有 VARCHAR 列的表 'Categories' 已准备就绪。