条件插入的 Mysql 查询语法

Question

In following example I want to insert in table only if there is no data otherwise it is giving PK violation. How to do conditional insert in mysql. Please note I am using mysql db.

create table if not exists visa_amt_conversion (
  last_char char(1) not null,
  last_digit char(1) not null,
  tran_sign char(1) not null,
  primary key (last_char)
 );

insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('{', '0', '+');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('A', '1', '+');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('B', '2', '+');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('C', '3', '+');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('D', '4', '+'); 
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('E', '5', '+');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('F', '6', '+'); 
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('G', '7', '+');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('H', '8', '+'); 
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('I', '9', '+');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('}', '0', '-'); 
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('J', '1', '-');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('K', '2', '-'); 
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('L', '3', '-');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('M', '4', '-');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('N', '5', '-');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('O', '6', '-');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('P', '7', '-');
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('Q', '8', '-'); 
insert into visa_amt_conversion (last_char, last_digit, tran_sign) VALUES ('R', '9', '-');

Answer 1

标准的做法是这样的:

insert into visa_amt_conversion (last_char, last_digit, tran_sign)
    VALUES ('{', '0', '+')
    on duplicate key update last_char = values(last_char);

如果尝试插入违反唯一键或主键约束的值，则会更新该行。 last_char = values(last_char)) 是空操作。因为值没有改变，所以该行甚至没有更新。

较短的语法:

insert ignore into visa_amt_conversion (last_char, last_digit, tran_sign)
    VALUES ('{', '0', '+');

在这种情况下做同样的事情。但是，不推荐这样做，因为它会忽略所有错误。最好使用第一个版本，因为它只忽略您想要忽略的特定错误。