mysql 查询不存在则插入

清疚 2022-04-21 15:24 299阅读 0赞

*  摘要：CREATETABLE\`test1\`(\`id\`int(10)unsignedNOTNULLAUTO\_INCREMENT,\`item\_no\`varchar(10)NOTNULLDEFAULT'',\`delete\_flag\`tinyint(3)unsignedNOTNULLDEFAULT'0',PRIMARYKEY(\`id\`))ENGINE=InnoDBAUTO\_INCREMENT=5DEFAULTCHARSET=latin1insertintotest1(item\_no,del
 *    
    创建表: 
    
        CREATE TABLE `test1` ( 
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
        `item_no` varchar(10) NOT NULL DEFAULT '', 
        `delete_flag` tinyint(3) unsigned NOT NULL DEFAULT '0', 
        PRIMARY KEY (`id`)
        ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 
    
     
    
    --------------------
    
    不存在则添加，添加则忽略
 *      insert into test1 (item_no,delete_flag)
        select 'aaaaa',0 from dual
        where not exists (
            select `item_no`,`delete_flag` from test1 where item_no='aaaaa' and delete_flag=0
        );

例:

INSERT INTO `weixin_article_test`(artID, gzh_en, title, cover, summary, oriDate, content, ori_url, read_url) 
    SELECT '203924112_1', 
    'ynluxiren', 
    '”泸西人“公众微信号正式开通啦', 'http://mmbiz.qpic.cn/mmbiz/JTA2OOtPMnjd2URJqg9niariaMHGzibZgruXqiaGYv7d8y95D15wwMlYOd41bcBqeRtb4N1uv2rKqKkSBGaGkcTqog/0', 
    '力致于为全泸西人服务的微信公众号“泸西人”开通啦', 
    '2015-03-02 21:27:53', 
    '', 
    'http://mp.weixin.qq.com/s?__biz=MzA3ODQwODM2NQ==&mid=203924112&idx=1&sn=13eb74e51bdd0669a2e06f93e8b768e8#rd', 
    '' 
    FROM dual 
    WHERE NOT EXISTS (
      Select * from `weixin_article_test` where `gzh_en`='ynluxiren' AND `artID` = '203924112_1'
    )

//使用 dual 做表名可以让 select 语句后面直接跟上要insert字段的值，即使这些值还不存在当前表中.