Mysql相关sql自留

自定义排序-ORDER BY FIELD

<select id="pageByQuery" parameterType="com.wekj.ministore.model.query.MiniProductPageQuery"  resultType="com.wekj.ministore.dao.entity.MinistoreProduct">
        select *
        from ministore_product as a
        where deleted = 0
        <if test="query.cliqueId != null and query.cliqueId != ''" >
            AND clique_id = #{query.cliqueId}
        </if>
        <if test="query.status != null and query.status != ''" >
            AND status = #{query.status}
        </if>
        <if test="query.keyword != null and query.keyword != ''">
            AND ( title like concat ('%', #{query.keyword}, '%')
            or out_product_id = #{query.keyword}
            <if test="query.keywordNumber != null">
                or product_id = #{query.keywordNumber}
            </if>
            )
        </if>
        <if test="query.productIds != null and query.productIds.size() > 0 ">
            and product_id in
            <foreach collection="query.productIds" item="productId" open="(" close=")" separator=",">
                #{productId}
            </foreach>
        </if>

        <choose>
            <when test="query.productIds != null and  query.productIds.size() > 0 and query.productIdsSort != null and query.productIdsSort == true">
                ORDER BY FIELD  ( a.product_id,
                <foreach collection="query.productIds" item="item" index="index" separator=",">
                    ${item}
                </foreach>
                )
            </when>
            <when test="query.sort != null">
                ORDER BY
                <foreach collection="query.sort" item="item" index="index" separator=",">
                    ${item.field} ${item.type.code}
                </foreach>
            </when>
            <otherwise>
                ORDER BY
                ID
                DESC
            </otherwise>
        </choose>
        LIMIT #{query.startRow} ,#{query.pageSize}
    </select>

查询指定一行排序到第一行

select * from 库名.表名 ORDER BY id = 7 desc limit 0, 5;
select * from 库名.表名 ORDER BY id <> 7 limit 5, 5;
select * from 库名.表名 ORDER BY id in (7,8,9) desc limit 0, 5;
select * from 库名.表名 ORDER BY id not in (7,8,9) limit 5, 5;

DDL相关SQL

-- alter表
alter table 库名.表名 add column org_tag_type int DEFAULT '-1' comment '签' AFTER id;
alter table 库名.表名 modify column org_tag_type int DEFAULT '-1' comment '签';
alter table 库名.表名 drop column certificate_source;

-- 直接删除整张表
DROP TABLE IF EXISTS 库名.表名;

-- 修改 update
update 库名.表名 set login_host = 'h' where code = '8';

-- 自定义排序ORDER BY FIELD
SELECT * from 库名.表名 WHERE `code` IN  (500103,500105,500108,500106,500107,500104) 
ORDER BY FIELD (code, 500103,500105,500108,500106,500107,500104);

-- 指定数据在第一行
select * from 库名.表名 ORDER BY id = 7 desc limit 0, 5;
select * from 库名.表名 ORDER BY id <> 7 limit 5, 5;
select * from 库名.表名 ORDER BY id in (7,8,9) desc limit 0, 5;
select * from 库名.表名 ORDER BY id not in (7,8,9) limit 5, 5;

-- 查询逗号分割的字符串数组
select org_service_category, out_org_id, full_name, social_credit_code, settle_type from 库名.表名 where settle_type = 1 and (find_in_set('project', `org_service_category`) or find_in_set('service', `org_service_category`));

索引相关

-- 加索引
create index 索引名一般前缀idx_ on 表名 (列名); 
    

备份表

INSERT INTO paas_XXX_bak (id, status, created_at, updated_at, ext_fields)
select id,
       status,
       created_at,
       updated_at,
       ext_fields
from paas_XXX
where XXX = '~'
  and XXX = '~'
on duplicate key update id             = VALUES(id),
                        status         = VALUES(status),
                        created_at     = VALUES(created_at),
                        updated_at     = VALUES(updated_at),
                        ext_fields     = VALUES(ext_fields);
                        

mapper文件的写法

<resultMap id="bus" type="cn.dto.Bus">
        <id column="bus_id" property="id" jdbcType="BIGINT" />
        <result column="type" property="type" jdbcType="TINYINT" />
        <result column="tag_type" property="tagType" jdbcType="INTEGER" />
        <result column="bus_status" property="status" jdbcType="TINYINT" />
        <result column="description" property="description" jdbcType="VARCHAR" />

        <association property="baseInfo" javaType="cn.dto.Ins">
            <id column="ins_id" property="id" jdbcType="BIGINT" />
            <result column="name" property="name" jdbcType="VARCHAR" />
            <result column="code" property="code" jdbcType="VARCHAR" />
            <result column="biz_attaches" property="bizAttaches" jdbcType="VARCHAR" typeHandler="cn.converter.JacksonStringArrayHandler" />
            <result column="ins_status" property="status" jdbcType="TINYINT" />
            <result column="created_at" property="createdAt" jdbcType="TIMESTAMP" />
            <result column="updated_at" property="updatedAt" jdbcType="TIMESTAMP" />
        </association>

        <collection property="relations" column="unique_key_" ofType="cn.dto.BusRelation">
            <id column="id" property="id" jdbcType="BIGINT" />
            <result column="ins_id" property="insId" jdbcType="BIGINT" />
            <result column="type" property="type" jdbcType="TINYINT" />
            <result column="created_at" property="createdAt" jdbcType="TIMESTAMP" />
            <result column="updated_at" property="updatedAt" jdbcType="TIMESTAMP" />
        </collection>
    </resultMap>

返回主键id

  <insert id="insertSelective" parameterType="com.XXX.AuthenticationDO" useGeneratedKeys="true" keyProperty="id">