【MySQL8】查看/设置编码格式、排序规则和存储引擎

本文链接：https://blog.csdn.net/lklalmq/article/details/145041024

先说一下推荐设置

字符集和编码格式推荐配置

统一使用 utf8mb4: 推荐使用 utf8mb4 字符集，因为它支持更广泛的字符集，包括 emoji。
选择合适的排序规则: utf8mb4_unicode_ci 适合大多数情况，utf8mb4_general_ci 在某些情况下可能更快，但 Unicode 标准的支持不如 utf8mb4_unicode_ci。
服务器、数据库、表、列级别统一设置: 尽量保持字符集和排序规则在各个级别的一致性，避免出现不必要的转换问题。
在项目开始前就设置: 在项目开始前就正确设置字符集和排序规则，可以避免后续因为数据编码不一致导致的问题。

存储引擎推荐配置

推荐使用 InnoDB: 在大多数情况下，InnoDB 引擎是最佳选择，因为它支持事务、行级锁定，适合高并发和需要数据一致性的应用。
特殊需求时选择其他引擎: 只有在有特殊需求时，比如只需要读取数据，对并发写入要求不高，可以考虑使用 MyISAM 等其他引擎。

编码格式和排序规则

查看当前会话的编码格式

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%';

输出

client：客户端将SQL发送给MySQL服务器所用的字符集，例如Navicat写的SQL运行
connection：服务器解析处理从客户端接收的SQL所用的字符集
database：当前库的默认字符集，例如创建表列，如果没有指定则默认
filesystem：服务器与文件系统进行交互所用的字符集，例如读写文件时
results：服务器向客户端返回查询结果所用的字符集，要与client一致
server：服务器的内部默认字符集
system：服务器存储系统表使用的字符集，例如：information_schema中的表

查看当前会话的排序规则

SHOW VARIABLES WHERE Variable_name LIKE 'collation\_%';

查看数据库的编码格式和排序规则

SELECT DATABASE(), DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = DATABASE();

查看表的编码格式和排序规则

SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name';

查看字段的编码格式和排序规则

SELECT COLUMN_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

服务器级别设置

配置文件，my.cnf或my.ini

```ini
  [mysqld]
  character-set-server = utf8mb4
  collation-server = utf8mb4_unicode_ci
  ```
  
  *   **`character-set-server = utf8mb4`**: 设置服务器默认字符集为 utf8mb4。
  *   **`collation-server = utf8mb4_unicode_ci`**: 设置服务器默认排序规则为 utf8mb4_unicode_ci， 大小写不敏感。
  
  * **重启 MySQL 服务** 以使更改生效。

  * **验证服务器级别设置：**
    ```sql
    SHOW VARIABLES LIKE 'character_set_server';
    SHOW VARIABLES LIKE 'collation_server';
    ```

库级别设置

创建库时或之后

  * **创建数据库时：**
    ```sql
    CREATE DATABASE your_database_name
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
    ```
  
  * **修改现有数据库：**
    ```sql
    ALTER DATABASE your_database_name
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
    ```

  * **验证数据库级别设置：**
    ```sql
    SHOW CREATE DATABASE your_database_name;
    ```

表级别设置

创建表时或之后

* **创建表时:**
     ```sql
     CREATE TABLE your_table_name (
         ...
     )
     CHARACTER SET utf8mb4
     COLLATE utf8mb4_unicode_ci;
     ```
   
  * **修改现有表：**
    ```sql
    ALTER TABLE your_table_name
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
    ```

  * **验证表级别设置：**
    ```sql
    SHOW CREATE TABLE your_table_name;
    ```

列级别设置

创建表时或之后

 * **创建表时:**
     ```sql
     CREATE TABLE your_table_name (
         column1 VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
         column2 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
         ...
     );
     ```

   * **修改现有表：**
     ```sql
     ALTER TABLE your_table_name
     MODIFY COLUMN column1 VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
     ```

  * **验证列级别设置 (可以使用 `SHOW CREATE TABLE` 命令查看表定义)：**

存储引擎

MySQL 支持多种存储引擎，每种引擎都有其特定的用途和特性，最常用的有：

InnoDB: MySQL 8 的默认存储引擎，支持事务、行级锁定、崩溃恢复等特性，适合需要 ACID 事务的场景。
MyISAM: 不支持事务，表级锁定，适合读取频繁的应用，但对并发写入性能较差。

查看所有存储引擎

SHOW ENGINES;

查看表的存储引擎

SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name';

查看库的默认存储引擎

SHOW VARIABLES LIKE 'default_storage_engine';

服务器级别设置

ini [mysqld] default-storage-engine = InnoDB

  * `default-storage-engine = InnoDB`：设置默认存储引擎为 InnoDB。
  * **重启 MySQL 服务** 以使更改生效。

表级别设置

```sql
CREATE TABLE your_table_name (
...
) ENGINE = InnoDB;  

-- 或者
  CREATE TABLE your_table_name (
      ...
  ) ENGINE = MyISAM;
  ```

*   **注意:** 可以在每个表的定义中指定使用的存储引擎。
```sql
    ALTER TABLE your_table_name ENGINE=InnoDB;
    ```

总结：