mariadb为方便oracle用户迁移,在 10.3版本起,支持sequence。
创建sequence
创建sequence的命令(innodb_force_primary_key开关需要关闭,我觉得是个bug):
CREATE SEQUENCE seq1 START WITH 1 INCREMENT BY 1 cache 100;
默认的使用sequence的命令:
select nextval(seq1);
开启与oracle一致的nextval语法
临时开启与oracle一致的nextval语法,永久生效需写入my.cnf文件:
SET GLOBAL sql_mode = 'ORACLE';
使用sequence的命令:
select seq1.nextval from dual;
sql_mode=ORACLE时的function语法
语法与mysql、oracle都不一致,结合了两者的语法风格,demo如下:
delimiter /
DROP function IF EXISTS change_from_oracle_date_format/
create or replace function change_from_oracle_date_format(format varchar)
return varchar
as
newformat varchar(100);
begin
IF LOCATE('%', format) > 0 THEN
return format;
END IF;
newformat := format;
newformat := REPLACE(newformat, 'YYYY', '%Y');
newformat := REPLACE(newformat, 'yyyy', '%Y');
newformat := REPLACE(newformat, 'YY', '%y');
newformat := REPLACE(newformat, 'yy', '%y');
newformat := REPLACE(newformat, 'MM', '%m');
newformat := REPLACE(newformat, 'mm', '%m');
newformat := REPLACE(newformat, 'DD', '%d');
newformat := REPLACE(newformat, 'dd', '%d');
newformat := REPLACE(newformat, 'HH24', '%H');
newformat := REPLACE(newformat, 'hh24', '%H');
newformat := REPLACE(newformat, 'HH12', '%h');
newformat := REPLACE(newformat, 'hh12', '%h');
newformat := REPLACE(newformat, 'HH', '%h');
newformat := REPLACE(newformat, 'hh', '%h');
newformat := REPLACE(newformat, 'MI', '%i');
newformat := REPLACE(newformat, 'mi', '%i');
newformat := REPLACE(newformat, 'SS', '%s');
newformat := REPLACE(newformat, 'ss', '%s');
return newformat;
end change_from_oracle_date_format;
/
delimiter ;
select change_from_oracle_date_format('yyyy-mm-dd hh24:mi:ss');