SQL使用小技巧

instr 用法

SELECT sd.`value2`
FROM `v_account_info` vai
LEFT JOIN sys_dict sd ON sd.`name` = 'DEVICE_CATEGORY' AND instr(vai.`category_list`,concat(',',sd.`param`,','))>
WHERE vai.`id` = ;
           

group傳回多條記錄

SELECT
    art.`id`
    ,sec.name AS section_name
    ,art.section_id
    ,art.`create_at`
    ,art.`subject`
    ,art.`head_img`
    FROM `article` art
    LEFT JOIN `section` sec ON art.`section_id` = sec.`id`
    WHERE =
          AND #{rows} > (SELECT COUNT(*) FROM article WHERE section_id = art.`section_id` AND create_at > art.`create_at`)
          AND sec.`section_type` = #{sectionType}
    ORDER BY section_id ,art.`create_at` DESC
           

修改主鍵下标

ALTER TABLE

user

AUTO_INCREMENT=1200001;