# [FAQ] 高版本 TiDB 使用 strcmp 结果集异常 ## Key Words strcmp, utf8mb3, mysql8.0, mysql5.7, 兼容性 ## Description 1. 高版本 TiDB，使用 mysql client 执行 strcmp 出现非预期行为。 a. 非预期行为 ```sql mysql> select strcmp('ABC','ABc'),strcmp('ABC','ABC'),strcmp('ABc','ABC'); +---------------------+---------------------+---------------------+ | strcmp('ABC','ABc') | strcmp('ABC','ABC') | strcmp('ABc','ABC') | +---------------------+---------------------+---------------------+ | 0 | 0 | 0 | +---------------------+---------------------+---------------------+ ``` b. 预期行为 ```sql mysql> select strcmp('ABC','ABc'),strcmp('ABC','ABC'),strcmp('ABc','ABC'); +---------------------+---------------------+---------------------+ | strcmp('ABC','ABc') | strcmp('ABC','ABC') | strcmp('ABc','ABC') | +---------------------+---------------------+---------------------+ | -1 | 0 | 1 | +---------------------+---------------------+---------------------+ ``` 2. 低版本 TiDB，如 v2 版本函数执行结果符合预期。 ## Content 1. 使用不同版本 mysql client 连接高版本 tidb server 对比字符集，说明该问题与字符集有关（character_set_client, character_set_connection, character_set_results），情况如下： a. mysql client v5(低版本) ``` % /usr/bin/mysql -V /usr/bin/mysql Ver 15.1 Distrb 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1 mysql> show variables like '%character%'; +--------------------------+--------------------------------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-5.6.25-osx10.8-x86_64/share/charsets/ | +--------------------------+--------------------------------------------------------+ ``` b. mysql client v8(高版本) ``` % /opt/homebrew/bin/mysql -V mysql Ver 8.0.32 for macos13.0 on arm64 (Homebrew) mysql> show variables like '%character%'; +--------------------------+--------------------------------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-5.6.25-osx10.8-x86_64/share/charsets/ | +--------------------------+--------------------------------------------------------+ ``` 2. 使用高版本 mysql client（v8） 连接 mysql server 8.0 更改字符集发现 warning，因为 mysql 的 utf8 是通俗 utf8 的子集（utf8mb3），高版本默认使用 utf8mb4。 ```sql mysql> show variables like 'character_set_client'; +----------------------+---------+ | Variable_name | Value | +----------------------+---------+ | character_set_client | utf8mb3 | +----------------------+---------+ 1 row in set (0.01 sec) mysql> set character_set_client='utf8'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. | +---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.32 | +-----------+ 1 row in set (0.00 sec) ``` 3. 在高版本 mysql client 改变字符集配置后，查询对应函数结果集与高版本 TiDB 相同。 ```sql mysql> show variables like '%character%'; +--------------------------+--------------------------------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------------------------------+ | character_set_client | utf8mb3 | | character_set_connection | utf8mb3 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb3 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-5.6.25-osx10.8-x86_64/share/charsets/ | +--------------------------+--------------------------------------------------------+ mysql> select strcmp('ABC','ABc'),strcmp('ABC','ABC'),strcmp('ABc','ABC'); +---------------------+---------------------+---------------------+ | strcmp('ABC','ABc') | strcmp('ABC','ABC') | strcmp('ABc','ABC') | +---------------------+---------------------+---------------------+ | 0 | 0 | 0 | +---------------------+---------------------+---------------------+ 1 row in set (0.00 sec) ``` 4. 测试 jdbc mysql-connector-java-5.1.49.jar 连接高版本 TiDB v6.5.0 行为，结果正常，不会有兼容性问题。 ```java import java.sql.*; public class Example { static final String DB_URL = "jdbc:mysql://127.0.0.1:4000/test?sessionVariables=max_execution_time=1&useSSL=false&useServerPrepStmts=true&cachePrepStmts=true&useConfigs=maxPerformance&prepStmtCacheSize=1000&prepStmtCacheSqlLimit=2048"; static final String USER = "root"; static final String PASS = ""; static final String QUERY = "select strcmp('ABC','ABc') as A,strcmp('ABC','ABC') as B,strcmp('ABc','ABC') as C, version() as Version, @@session.character_set_client as character_set_client;"; public static void main(String[] args) { // Open a connection try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(QUERY);) { // Extract data from result set while (rs.next()) { // Retrieve by column name System.out.print("global max_execution_time: " + rs.getInt("exec_time")); System.out.print(", B: " + rs.getInt("B")); System.out.println(", C: " + rs.getString("C")); System.out.println("\nversion: " + rs.getString("Version")); System.out.println("session character_set_client: " + rs.getString("session_character_set_client")); } } catch (SQLException e) { e.printStackTrace(); } } } $ java -cp ./lib/jdbc/mysql-connector-java-5.1.49.jar Example.java A: -1, B: 0, C: 1 version: 5.7.25-TiDB-v6.5.0 session character_set_client: utf8mb4 ``` 综上，该行为是由于[低版本选择了兼容 MySQL5.7](https://docs-archive.pingcap.com/zh/tidb/v2.1/mysql-compatibility#%E4%B8%8E-mysql-%E5%85%BC%E5%AE%B9%E6%80%A7%E5%AF%B9%E6%AF%94)，而[高版本 TiDB 选择了兼容 MySQL8](https://docs.pingcap.com/zh/tidb/v6.1/character-set-and-collation#%E5%AD%97%E7%AC%A6%E9%9B%86%E5%92%8C%E6%8E%92%E5%BA%8F%E8%A7%84%E5%88%99%E7%9A%84%E6%A6%82%E5%BF%B5) ，即：TiDB 中的默认字符集是 utf8mb4，与 MySQL 8.0 及更高版本中的默认字符集行为匹配。 ## Affected Versions N/A