【日常总结】mybatis-plus WHERE BINARY 中文查不出来
目录
一、场景
-
mysql 5.7.28
-
mybatis-plus
-
spring boot 2.5.4
-
navicate 15
二、问题
英文查询正常，中文查询结果集为0
三、原因
mybatis-plus 使用 WHERE BINARY查询 ，字符集不统一（数据库，表，字段），导致中文无法查询出来
四、解决方案
-
需要统一为: utf8mb4
-
排序为： utf8mb4_general_ci
# 说明，替换下面3个参数即可
# database_name ：数据库名
# table_name：表名
# column_name：字段名
# 修改库字符集
ALTER DATABASE `database_name` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
# 修改表
ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
# 修改表中字段
ALTER TABLE `table_name` MODIFY COLUMN `column_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;-
注意：如果修改字符集后，任然查不出来，需要将中文字符复制进去保存改条数据，再查询验证
五、拓展（全表全字段修改字符集一键更改）
准备工作：做好整个库备份
1. 全表一键修改
Stage 1：运行如下查询
SELECT
DISTINCT
table_schema,
table_name,
character_set_name,
collation_name,
CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' CONVERT TO CHARACTER SET \'', character_set_name, '\' COLLATE \'', collation_name, '\';') '表原字符集SQL',
CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' CONVERT TO CHARACTER SET \'utf8mb4\' COLLATE \'utf8mb4_general_ci\';') '表需修改字符集SQL'
FROM
information_schema.COLUMNS
WHERE TABLE_SCHEMA NOT IN ('mysql','performance_schema','sys','information_schema','mysql_ha','mysql_db_monitor')
AND COLLATION_NAME IS NOT NULL
AND COLLATION_NAME != 'utf8mb4_general_ci';
Stage 2：复制sql语句
Stage 3：执行即可
2. 全字段一键修改
Stage 1：运行如下查询
SELECT
TABLE_SCHEMA '数据库',
data_type '数据类型',
TABLE_NAME '表',
COLUMN_NAME '字段',
CHARACTER_SET_NAME '原字符集',
COLLATION_NAME '原排序规则',
CONCAT(
'ALTER TABLE ',
TABLE_SCHEMA, '.', TABLE_NAME,
' MODIFY COLUMN ',
COLUMN_NAME,
' ',
COLUMN_TYPE,
' CHARACTER SET ', CHARACTER_SET_NAME, ' COLLATE ', COLLATION_NAME,
( CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END ),
( CASE WHEN COLUMN_COMMENT = '' THEN ' ' ELSE concat( ' COMMENT''', COLUMN_COMMENT, '''' ) END ),
';'
) '字段原字符集SQL',
CONCAT(
'ALTER TABLE ',
TABLE_SCHEMA, '.', TABLE_NAME,
' MODIFY COLUMN ',
COLUMN_NAME,
' ',
COLUMN_TYPE,
' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci',
( CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END ),
( CASE WHEN COLUMN_COMMENT = '' THEN ' ' ELSE concat( ' COMMENT''', COLUMN_COMMENT, '''' ) END ),
';'
) '字段需修正字符集SQL'
FROM information_schema.`COLUMNS`
WHERE 1=1
-- AND COLLATION_NAME != 'utf8mb4_general_ci'
AND COLUMN_NAME != 'id'
AND data_type not in('bigint','int','datetime','decimal','tinyint','double','float','json')
AND TABLE_SCHEMA NOT IN ('mysql','performance_schema','sys','information_schema','mysql_ha','mysql_db_monitor');Stage 2：复制sql语句
Stage 3：执行即可
注意事项：
1. 有些字段是关键字，无法执行，报错到该行注释即可
2. 有点字段类型不能设置字符集，报错到该行注释即可
