MySQL 仅在不存在时才插入，否则什么也不做

Question

如果记录不存在，我将尝试向 MySQL 中插入一条记录，否则什么都不做

插入时，我正在检查 T1 ='one' AND T2 = 'two' AND T3 = 'three' AND vendor_brand_id = 7 是否不存在然后只插入或什么也不做

但这会产生语法错误，你能告诉我这有什么问题吗

这是我的模式

CREATE TABLE IF NOT EXISTS `category` (
  `category_id` int(11) NOT NULL auto_increment,
  `T1` varchar(50) default NULL,
  `T2` varchar(50) default NULL,
  `T3` varchar(50) default NULL,
  `T4` varchar(50) default NULL,
  `T5` varchar(50) default NULL,
  `T6` varchar(50) default NULL,
  `T7` varchar(50) default NULL,
  `T8` varchar(50) default NULL,
  `T9` varchar(50) default NULL,
  `T10` varchar(50) default NULL,
  `vendor_brand_id` varchar(10) default NULL,
  `created_at` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=384 DEFAULT CHARSET=latin1;

INSERT INTO `category` (`category_id`, `T1`, `T2`, `T3`, `T4`, `T5`, `T6`, `T7`, `T8`, `T9`, `T10`, `vendor_brand_id`, `created_at`) VALUES
    (377, 'one', 'two', 'three', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '7', '2016-04-11 19:32:34'),
    (378, 'one', 'two', 'three', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '7', '2016-04-11 19:32:50')

这是我的插入查询

Insert into category (T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,vendor_brand_id)
 values ('one', 'two', 'three',NULL,NULL,NULL,NULL,NULL,NULL,NULL,7) 
 select category_id from category where T1 ='one' AND T2 = 'two'  AND T3 = 'three'  AND vendor_brand_id = 7  
  FROM dual 
  WHERE NOT EXISTS
    ( SELECT *
          FROM category
         where T1 ='one' AND T2 = 'two'  AND T3 = 'three'  AND vendor_brand_id = 7  
        ) ; 

但这会产生语法错误，你能告诉我这有什么问题吗

http://sqlfiddle.com/#!9/f371e

Answer 1

您可以使用这样的查询:

INSERT INTO category (T1, T2, T3, T4, T5, T6, T7, T8, T9, T10, vendor_brand_id)
SELECT T1, T2, T3, T4, T5, T6, T7, T8, T9, T10, vendor_brand_id
FROM (
  SELECT CAST('one' AS CHAR CHARACTER SET latin1) AS T1, 
         CAST('two' AS CHAR CHARACTER SET latin1) AS T2, 
         CAST('three' AS CHAR CHARACTER SET latin1) AS T3,
         NULL AS T4, NULL AS T5, NULL AS T6, NULL AS T7, 
         NULL AS T8, NULL AS T9, NULL AS T10, 7 AS vendor_brand_id) AS t 
WHERE NOT EXISTS (SELECT *
                  FROM category AS c
                  WHERE c.T1 = t.T1 AND 
                        c.T2 = t.T2 AND 
                        c.T3 = t.T3 AND 
                        c.vendor_brand_id = t.vendor_brand_id)