greemplum的计算内核是postgresql，所有下面总结的几点也适用于mysql迁移到postgresql

1、时间格式化

mysql: select DATE_FORMAT(now(),'%Y%m%d%H%i%s') =>20160720035809 获取一个日期的年月日小时分钟秒字符串注：%H表示24小时制

GP： to_char(now(), 'YYYYMMDDHH24MISS') =>20160720155848获取一个日期的年月日小时分钟秒字符串类型

to_date(now(), 'YYYYMMDD') =>20160720155848获取一个日期的年月日小时分钟秒日期类型

to_number(now(),'9999999999')=>20160702获取一个日期的年月日数值类型

to_number(now(),'9999999999999999999')=>20160720155848获取一个日期的年月日小时分钟秒数值类型

注：HH24表示24小时制

数值类型转换时间类型

mysql：select cast(20160501 as datetime)=>2016-05-01 00:00:00

select cast(20160501 as date)=>2016-05-01

GP： select to_timestamp('20160801','YYYYMMDDHH24MISS')=>2016-08-01 00:00:00+08

select 20160801::varchar::TIMESTAMP=>2016-08-01 00:00:00+08

select to_date('20160801','YYYYMMDDHH24MISS')=>2016-08-01

select 20160801::varchar::date=>2016-08-01

GP详细时间函数可见http://blog.csdn.net/snn1410/article/details/7741283

获取小时函数

mysql: select hour(now())=>16 获取一个日期的小时

GP: select EXTRACT(HOUR from now()) =>16 获取一个日期的小时

GP衍生：

select EXTRACT(day from now()) 日期

select EXTRACT(dow from now()) 每周的星期号

select EXTRACT(doy from now()) 一年的第几天

select EXTRACT(month from now())月份

select EXTRACT(minute from now())分钟

select EXTRACT(week from now())一年的第几周

select EXTRACT(year from now())年份

时间计算

mysql :DATE_SUB(now(),INTERVAL 5 day) 5天前 DATE_SUB(now(),INTERVAL -5 day)5天后

GP：now()-INTERVAL '5 day' 5天前 now()+INTERVAL '5 day' 5天后

now()-INTERVAL '5 year' 5年前

now()-INTERVAL '5 month' 5个月前

now()-INTERVAL '5 min' 5分钟前

注：上述的DATE_SUB函数中的5这个值可以是变量，比如case when a=1 then 5 else 0

但是如果GP要实现这样的功能需要换一种写法，比如select now()- (case when a=1 then '5' else '0' end || ' day')::INTERVAL实现当a=1的时候减5天，其他值则时间为当前时间

时间差

mysql：DATEDIFF(b.pay_time,c.value_date)

GP：（b.pay_time::date-c.value_date::date）

2、lfnull处理空字符串

mysql: ifnull(null,0.00) =>0.00 字段如果为空则为0.00

GP： COALESCE(null,0.00 )=>0.00 字段如果为空则为0.00

注意：COALESCE中的两个数据类型要一致COALESCE(1,'a')会报错，mysql的ifnull则不会

3、字符串拼接

mysql: SELECT concat('My','S','QL') =>MySQL

GP: SELECT 'My'||'S'||'QL' => MySQL

4、执行存储过程
mysql: call proc_test();
GP: select proc_test();

5、substr和substring

substr(str from pos)和substring(str from pos)都可以使用

GP不能使用substr(str from pos) 只能substring(str from pos)

6、convert函数差异

mysql convert函数可用来强制转换数据类型例：convert('20160801',date)=>2016-08-01

postgresql convert则是用来用转换字符集类型 convert_to(string text, dest_encoding name)

7、GP不能将字符字段改成数值类型

8、GP不能使用if函数

mysql:if(a=1,b=1,b=2)=>如果a=1则b=1否则b=2

GP: 只能用case when a=1 then b=1 else b=2 end实现，也可以自定义函数实现mysql的if函数功能

9、locate函数获取某个字符串在一个字符串中的位置

mysql：locate（'a','bcad'）=>3

GP： position('a' in 'bcad' )=>3

10、create table

mysql:create table t1 select * from t2;

GP create table t1 as select * from t2; GP必须加上as

11、联合更新

mysql:update CDM.cdm_product a INNER JOIN dim_product_tmp b on a.id = b.id

set a.enddate = b._date,a.is_current = 0

GP：update CDM.cdm_product a set enddate = b._date,is_current = 0 from dim_product_tmp b where a.id = b.id; GPupdate允许子查询,注意enddate = b._date不能写成a.enddate = b._date

12、主键自增

mysql:CREATE TABLE `cdm_product` ( `id` int(11) NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`))) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

GP: CREATE TABLE "cdm"."cdm_product" ( "id" SERIAL primary key)

13、

mysql：insert into value()

GP ：insert into values()

14、聚集函数

mysql:GROUP_CONCAT(date_day ORDER BY date_day asc separator ',')

GP :array_to_string(ARRAY(SELECT unnest(array_agg(date_day)) order by 1),',')

15、从表中获取数据给变量赋值

mysql:SELECT @min_date:=min(DATE_FORMAT(u.create_date, '%Y%m%d')) FROM `ODS`.ods_sys_user u;

GP :SELECT min(to_number(u.create_date, '9999999999')) into min_date FROM ODS.ods_sys_user u;

16、创建临时表

mysql:create TEMPORARY table product select

GP ：create TEMPORARY table product as select

17、join on 和 using

USING 是个一个连接条件的缩写语法：它接收一个用逗号分隔的字段名列表，这些字段必须是连接表共有的并且其值必须相同。最后，JOIN USING 会将每一对相等的输入字段输出为一个字段，其后跟着所有其它字段。因此，USING (a, b, c) 等效于 ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c) 只不过是如果使用了 ON ，那么在结果里 a, b, c 字段都会有两个，而用 USING 的时候就只会有一个。

18、\除法运算

mysql：select 250/999 =>0.2503结果保留到4位小数，被除数如果是0则返回null

GP :select 250/999 =>0，select 2500/999 =>2结果只保留整数，被除数如果是0则报错ERROR: division by zero

如果要保留小数需要int类型转换成numeric类型：select round(250::numeric/999::numeric,4)=>0.2503

常见问题：[Err] ERROR: mismatched parentheses 不匹配的括号，检查是否确实左右括号

GP不同数据类型不会做隐式转换，一定要显示转换

GP列名不允许出现特殊字符

ITPub博客

mysql迁移到greenplum sql改造总结

最新文章