你知道mysql的insert有几种写法吗

991 阅读2分钟

INSERT INTO

  • 这是最常用的插入方式。

INSERT INGORE INTO

  • 主键或唯一索引冲突时忽略插入的数据。

REPLACE INTO

  • 主键或唯一索引冲突时删除原有数据并插入新的数据。

INSERT ON DUPLICATE KEY UPDATE

  • 主键或唯一索引冲突时更新部分字段,是常用的insertOrUpdate语句。
  • 与replace into的区别是可以自定义更新的字段,replace into是删除原有数据并全部插入。

INSERT INTO SELECT

  • 可以把数据从一张表复制到另一张表,具体语法为
INSERT INTO table2 (column_name) SELECT column_name FROM table1;
  • 相对于先select然后再insert速度快不少。

使用insertOrUpdate语句很方便,一条sql既可以插入又可以更新。但它不符合单一职责,如果不注意可能会引发难以察觉的bug。

1.在mybatis3.3.1版本之后,使用批量插入语句可以返回每条记录自动生成的主键。

<insert id="insertUsers" parameterType="List" useGeneratedKeys="true" keyProperty="userId" keyColumn="user_id">
    INSERT INTO user(
        user_name,
        identity_card,
        phone
    )VALUES 
    <foreach collection="list" item="user" separator=",">
        (
        #{user.userName},
        #{user.identityCard},
        #{user.phone}
        ) 
    </foreach> 
</insert>
但这仅限于insert语句,如果使用INSERT INGORE INTO,REPLACE INTO,INSERT ON DUPLICATE KEY UPDATE插入时,获取不到自己期望的结果。

2.使用insertOrUpdate语句时,需要额外小心,因为可能产生意料之外的结果。

user_id user_name phone
1 张三 1523333
2 李四 1524444
3 麻子 1525555
设置字段phone为唯一索引,如果张三修改自己的手机号为1524444。
INSERT INTO user(
    user_name,
    phone
    ) VALUES(
    '张三',
    1524444
    ) 
ON DUPLICATE KEY UPDATE
    phone = 1524444,
    user_name = '张三';
结果把李四的名字改成张三了。
user_id user_name phone
1 张三 1523333
2 张三 1524444
3 麻子 1525555

3.还有一个问题是,使用insertOrUpdate语句会产生大量不连续的自增id。具体原因可以查看这两篇博客

MySQL实战45讲----自增主键不是连续的原因

MySQL innodb_autoinc_lock_mode详解