1. 当不存在才插入，防止主键重复错误
insert into TABLE
(openId)
select
#{openId}
where not exists
(select openId from TABLE where openId=#{openId})
多字段
insert into stock_quantity(id, product_id, stock_id, quantity, lock_quantity, version, created_by, created_time, updated_by, updated_time, del_flag) select
#{id}, #{productId}, #{stockId}, #{quantity}, #{lockQuantity}, #{version}, #{createdBy}, #{createdTime}, #{updatedBy}, #{updatedTime}, #{delFlag}
FROM DUAL
WHERE NOT EXISTS (select id from stock_quantity where product_id = #{productId} and stock_id = #{stockId})
但是只在5.7以上才有用，对于5.6可以用
INSERT INTO TABLE (openId) VALUES (#{openId}) ON DUPLICATE KEY UPDATE openId = #{openId}
2. 类型为char的时候 - 20180824
在用jpa的时候查询遇到StringIndexOutOfBoundsException: String index out of range: 0 被这个错误浪费了好几个小时。
后来发现当字段类型为char当时候而且字段值为空，就会报这个错误，猜测是因为char类型会自动用空格填充，改用varchar问题解决
3. select中使用前一个字段别名
select column1+10 as c1,（select c1）+10 as c2 from table1;
4. 更新表，把本表作为条件的时候报错
报错sql:
UPDATE order SET creator_id = '2' where order_id in (SELECT order_id FROM order where customer_id = 'A' )
应该再嵌一层
UPDATE order SET creator_id = '2' where order_id in (SELECT order_id FROM (SELECT order_id FROM order where customer_id = 'A' ) temp)
