How is postgresql compatible with MySQL if function

postgresql is compatible with MySQL if function

if function description

The usage of the if() function in mysql is similar to the three in java The purpose expression has many uses. The specific syntax is as follows:

IF(expr1,expr2,expr3), if the value of expr1 is true, then the value of expr2 is returned. If the value of expr1 is false, then Return the value of expr3

Postgresql custom if function compatible

create or replace function if(bln boolean,inValue1 anyelement,inValue2 anyelement)
returns anyelement as
$$
begin
if bln=true then
   return inValue1;
else
   return inValue2;
end if;
end;
$$
language plpgsql;

create or replace function if(bln boolean,inValue1 numeric,inValue2 numeric)
returns numeric as
$$
begin
if bln=true then
   return inValue1;
else
   return inValue2;
end if;
end;
$$
language plpgsql;

create or replace function if(bln boolean,inValue1 numeric,inValue2 text)
returns text as
$$
begin
if bln=true then
   return inValue1;
else
   return inValue2;
end if;
end;
$$
language plpgsql;

Mysql, oracle, postgresql compatible adaptation

SQL usage difference

1 . dual table

Oracle unique table, the purpose is to limit the complete sql statement structure

select (select * from table_name where age = 20) t from dual

mysql and pgsql do not have this table, you can directly remove it

select (select * from table_name where age = 20) t

2. Boolean type

oracle and mysql do not have boolean type, you can use number (int) or char instead

pgsql has bool type, numbers and characters are automatically converted to boolean type (0→f, 1→t, no→f, yes→t)

3. update table alias

pgsql is not applicable, supported by mysql and oracle

update table_name t set t.name = 'abc' where id = 1

4. String value passing

pgsql and oracle only support single quotes

select * from table_name where name = 'abc'

mysql supports both single and double quotes

select * from table_name where name = "abc"

5. Batch insert

mysql, pgsql batch insert

insert into table_name() values()

oracle batch insert

insert all into table_name() values()

mybatis is compatible with different databases

Use the if tag to determine _databaseId and adapt to different databases respectively. The specific code is as follows:

<insert id="insertBatch" parameterType="java.util.List">
    <if test="_databaseId==&#39;mysql&#39; or _databaseId==&#39;postgresql&#39;">
        insert into table_name 
        (<include refid="insertBatchColumn"></include>)
        values
        <foreach collection="list" item="item" index="index" separator="," >
            (<include refid="insertBatchValue"></include>)
        </foreach>
    </if>
    <if test="_databaseId==&#39;oracle&#39;">
        insert all
        <foreach collection="list" item="item" index="index" separator="">
            into table_name 
            (<include refid="insertBatchColumn"></include>)
            values (<include refid="insertBatchValue"></include>)
        </foreach>
        select * from dual
    </if>
</insert>
 
<sql id="insertBatchColumn">
    id,name,age,gender
</sql>
<sql id="insertBatchValue">
    #{item.id,jdbcType=VARCHAR}, #{item.name,jdbcType=VARCHAR}, 
    #{item.age,jdbcType=INTEGER},#{item.gender,jdbcType=INTEGER}
</sql>

The above is the detailed content of How is postgresql compatible with MySQL if function. For more information, please follow other related articles on the PHP Chinese website!