知识小屋

    字符集、排序规则和存储引擎

    这一节解决三个建库建表时经常看到的配置：

    • CHARACTER SET
    • COLLATE
    • ENGINE

    一、字符集是什么

    字符集决定数据库如何保存文字。

    MySQL 里建议新项目使用：

    utf8mb4

    不要简单使用 MySQL 里的 utf8。

    原因是 MySQL 的 utf8 历史上最多支持 3 字节字符，不能完整保存所有 Unicode 字符。utf8mb4 最多支持 4 字节，可以保存中文、emoji 和更多特殊字符。

    二、排序规则是什么

    排序规则决定字符串如何比较和排序。

    例如：

    SELECT 'A' = 'a';

    在很多默认排序规则下，结果是 1，表示相等，因为它不区分大小写。

    常见排序规则：

    排序规则简单说明
    utf8mb4_0900_ai_ciMySQL 8 常见默认规则，不区分重音和大小写
    utf8mb4_unicode_ci兼容性较好的 Unicode 排序规则
    utf8mb4_bin按二进制比较，区分大小写

    名字里的常见缩写：

    缩写含义
    cicase insensitive，不区分大小写
    cscase sensitive，区分大小写
    aiaccent insensitive，不区分重音
    binbinary，按二进制比较

    三、创建数据库时指定字符集

    推荐写法：

    CREATE DATABASE mysql_tutorial
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_0900_ai_ci;

    如果当前 MySQL 环境不支持 utf8mb4_0900_ai_ci，可以用：

    CREATE DATABASE mysql_tutorial
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;

    四、创建表时指定字符集

    CREATE TABLE users (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    如果表没有单独指定字符集，会继承数据库默认字符集。

    建议：

    • 建库时指定 utf8mb4。
    • 建表时也写上 DEFAULT CHARSET=utf8mb4，读起来更清楚。

    五、查看字符集

    查看数据库字符集：

    SHOW CREATE DATABASE mysql_tutorial;

    查看表字符集：

    SHOW CREATE TABLE users;

    查看所有支持的字符集：

    SHOW CHARACTER SET;

    查看所有支持的排序规则：

    SHOW COLLATION;

    六、存储引擎是什么

    存储引擎决定表底层怎么保存和管理数据。

    常见引擎：

    引擎特点
    InnoDB支持事务、行级锁、外键，现代 MySQL 默认选择
    MyISAM不支持事务，不适合大多数现代业务表
    MEMORY数据放内存里，重启会丢失，适合特殊临时场景

    业务表通常使用：

    ENGINE=InnoDB

    七、为什么推荐 InnoDB

    InnoDB 的关键能力：

    能力说明
    事务多条 SQL 可以一起提交或一起回滚
    行级锁修改一行数据时尽量只锁相关行
    外键可以维护表与表之间的引用关系
    崩溃恢复数据库异常退出后有恢复机制

    订单、支付、用户、文章这类业务表都应该优先使用 InnoDB。

    八、查看表引擎

    SHOW TABLE STATUS LIKE 'users';

    结果里的 Engine 字段就是表使用的存储引擎。

    也可以用：

    SHOW CREATE TABLE users;

    在建表语句末尾可以看到：

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

    九、统一推荐模板

    以后新建业务库，可以先用这个模板：

    CREATE DATABASE IF NOT EXISTS mysql_tutorial
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_0900_ai_ci;

    新建业务表，可以先用这个模板：

    CREATE TABLE table_name (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    如果当前环境不支持 utf8mb4_0900_ai_ci，把排序规则换成 utf8mb4_unicode_ci。