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;
 DEPTNO |   dname    |   loc    
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
(4 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 where dname = 'sales';
 DEPTNO | dname | loc 
--------+-------+-----
(0 rows)

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)

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