MySQL迁移到MogDB后查询数据大小写敏感问题

原作者：师庆栋

适用范围

MySQL适配MogDB

问题概述

MySQL中文本默认不区分大小写，而MogDB中默认区分大小写。迁移后可能因大小写敏感，原语句查询不到数据。

问题示例

MySQL

解释mysql> select * from scott.dept where dname = 'sales';
+--------+-------+---------+
| DEPTNO | DNAME | LOC |
+--------+-------+---------+
| 30 | SALES | CHICAGO |
+--------+-------+---------+
1 row in set (0.00 sec)


MogDB

解释omm@mysql=# select * from scott.dept where dname = 'sales';
DEPTNO | dname | loc
--------+-------+-----
(0 rows)

omm@mysql=# select * from scott.dept where dname = 'SALES';
DEPTNO | dname | loc
--------+-------+---------
30 | SALES | CHICAGO
(1 row)


解决方案

可以更改schema、table、columns编码

更改schema编码

schema编码更改后对新建的表有效果

解释omm@mysql=# set dolphin.b_compatibility_mode = on;
SET
omm@mysql=# alter schema scott character set utf8mb4 collate utf8mb4_unicode_ci;
ALTER SCHEMA


可以看到现有表仍然区分大小写，新建表后查询不区分大小写

解释omm@mysql=# create table scott.dept_bak(DEPTNO number(2),dname varchar2(14) ,loc varchar2(13) );
CREATE TABLE
omm@mysql=# insert into scott.dept_bak select * From scott.dept;
INSERT 0 4

omm@mysql=# select * from scott.dept_bak where dname = 'sales';
DEPTNO | dname | loc
--------+-------+---------
30 | SALES | CHICAGO
(1 row)


更改table编码

更改后对当前表生效

先检查当前表的编码，更改后的编码会在属性里显示

解释omm@mysql=# \d+
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+----------+-------+-------+------------+-----------------------------------------------+-------------
scott | bonus | table | omm | 8192 bytes | {orientation=row,compression=no} |
scott | dept | table | omm | 8192 bytes | {orientation=row,compression=no} |
scott | dept_bak | table | omm | 8192 bytes | {orientation=row,compression=no,collate=1538} |
scott | emp | table | omm | 8192 bytes | {orientation=row,compression=no} |
scott | salgrade | table | omm | 16 kB | {orientation=row,compression=no} |
(5 rows)
解释select a.schema_name,a.table_name,pc.collname
from
(
select n.nspname schema_name,
c.relname table_name,c.reloptions,
regexp_replace(c.reloptions::text,'^.+collate=(\d+).+$','\1')::int AS collate_oid
from pg_class c
join pg_namespace n on c.relnamespace = n.oid
where n.nspname = 'scott'
)a
inner join pg_collation pc on pc.oid = a.collate_oid;

schema_name | table_name | collname
-------------+------------+--------------------
scott | dept_bak | utf8mb4_unicode_ci
(1 row)


更改scott下面所有表的编码

解释declare
v_sql text;
begin
for cur in (
select table_schema, table_name
from information_schema.tables t
where table_catalog = 'mysql'
and table_schema = 'scott')
loop
v_sql := concat('alter table `',cur.table_schema,'`.`',cur.table_name, '` convert to charset utf8mb4
collate utf8mb4_unicode_ci;');
raise info '%',v_sql;
execute immediate v_sql;
end loop;
end;


查看更改效果

解释 schema_name | table_name | collname
-------------+------------+--------------------
scott | dept_bak | utf8mb4_unicode_ci
scott | bonus | utf8mb4_unicode_ci
scott | emp | utf8mb4_unicode_ci
scott | salgrade | utf8mb4_unicode_ci
scott | dept | utf8mb4_unicode_ci
(5 rows)


查看是否区分大小写

解释omm@mysql=# select * from scott.dept where dname = 'sales';
DEPTNO | dname | loc
--------+-------+---------
30 | SALES | CHICAGO
(1 row)


不再区分大小写，修改成功