【LightDB】MySQL迁移LightDB笔记

group by 迁移

LightDB 22.3提供了any_value函数，方便从mysql迁移到LightDB。以select a,b from test group by a order by b;为例，修改步骤如下：

1. 将select后不在group by 子句中的列加上any_value。

select a,any_value(b) from test group by a order by b;

2. 将order by后不在group by 子句中的列加上any_value。

select a,any_value(b) from test group by a order by any_value(b); 

效果如下：

lightdb@test=# select * from test;
 a | b 
---+---
 1 | 2
 1 | 3
 2 | 3
(3 rows)

lightdb@test=# select a,b from test group by a  order by b;
ERROR:  column "test.b" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select a,b from test group by a  order by b;
                 ^
lightdb@test=# select a, any_value(b) from test group by a  order by any_value(b);
 a | any_value 
---+-----------
 1 |         2
 2 |         3
(2 rows)

也可以使用lightdb_only_full_group_by参数：
会话级别设置：

set lightdb_only_full_group_by = false;

数据库级别设置

alter database test set lightdb_only_full_group_by  = false; -- test是库名

distinct 迁移

结合any_value ,discinct 也可以很方便的迁移。下面以select distinct a from test order by b为例介绍详细修改步骤。

1. 将distinct改成group by 语句。去掉distinct关键字，将disctinct后面的列名作为group by子句的列。

select a from test group by a order by b;

2. 将非group by的列加上any_value函数修饰

select a from test group by a order by any_value(b).

效果如下：

lightdb@test=# select * from test;
 a | b 
---+---
 1 | 2
 1 | 3
 2 | 3
(3 rows)

lightdb@test=# select distinct a from test order by b;
ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: select distinct a from test order by b;
                                            ^
lightdb@test=# select a from test group by a  order by any_value(b);
 a 
---
 1
 2
(2 rows)

group_concat

lightdb group_concat只支持两个参数（包含分隔符），如果碰到三个参数需要使用concat修改。示例如下：

select group_concat(oid,proname) from pg_proc group by proname;
修改为
select group_concat(concat(oid,proname)) from pg_proc group by proname;

示例中使用默认分割符逗号，加上oid,proname就有三个参数，所以报错。

!=

lightdb中!=-是一个合法的运算符名称。所以以下sql会报错：

lightdb@mysql=# select 1 !=-1;
ERROR:  operator does not exist: integer !=- integer
LINE 1: select 1 !=-1;
                 ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

未避免语法解析器错误识别，可以在!=后加空格或者将!=改成<>,示例如下：

lightdb@mysql=# select 1 != -1;
 ?column? 
----------
 t
(1 row)

lightdb@mysql=# select 1<>-1;
 ?column? 
----------
 t
(1 row)

字符和数值转换问题

MySQL字符转数字时，忽略错误。例如：1a转数字后变成1。特殊情况：空串转数字编程0。
LightDB数字转字符时，遇到不合法字符会报错。

lightdb@mysql=# select cast('' as signed);
ERROR:  invalid input syntax for type bigint: ""
LINE 1: select cast('' as signed);

mysql字符字面量

mysql字符串的字面量可以是"也可以是’（分ANSI模式下）。
lightdb字符串的字面量只能使用’。”用于修饰列名，表示列名区分大小写（不会统一转成小写）。

select "a" ;

改成lightdb的语句如下：

select 'a'; 

mysql boolean类型

mysql boolean类型和c/c++一样，是使用0或非0表示。
lightdb有真正的boolean类型，且不能和int/bigint,smallint隐式互转。
例如：

select * from test where 1;   -- 返回所有记录
select * from test where 0;  -- 返回0条记录
select * from test where 1 and 1; -- 返回所有记录

改写

select * form test where 1 != 0;
select * from test where 0 != 0;
select * from test where 1 != 0 and 1 !=0 ;