Home > Database > Mysql Tutorial > How is postgresql compatible with MySQL if function

How is postgresql compatible with MySQL if function

PHPz
Release: 2023-06-01 19:52:12
forward
2777 people have browsed it

    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;
    Copy after login

    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
    Copy after login

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

    select (select * from table_name where age = 20) t
    Copy after login

    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
    Copy after login

    4. String value passing

    pgsql and oracle only support single quotes

    select * from table_name where name = 'abc'
    Copy after login

    mysql supports both single and double quotes

    select * from table_name where name = "abc"
    Copy after login

    5. Batch insert

    mysql, pgsql batch insert

    insert into table_name() values()
    Copy after login

    oracle batch insert

    insert all into table_name() values()
    Copy after login

    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>
    Copy after login

    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!

    Related labels:
    Statement of this Website
    The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
    Latest Issues
    MySQL stops process
    From 1970-01-01 08:00:00
    0
    0
    0
    Error when installing mysql on linux
    From 1970-01-01 08:00:00
    0
    0
    0
    phpstudy cannot start mysql?
    From 1970-01-01 08:00:00
    0
    0
    0
    Popular Tutorials
    More>
    Latest Downloads
    More>
    Web Effects
    Website Source Code
    Website Materials
    Front End Template