MySQL-39：insert语句常见问题

浅浅的花香味﹌ 2023-01-01 15:59 128阅读 0赞

#### 39.1 insert…select 语句 ####

建表如下：

CREATE TABLE `t` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `c` int(11) DEFAULT NULL,
          `d` int(11) DEFAULT NULL,
          PRIMARY KEY (`id`),
          UNIQUE KEY `c` (`c`)
        ) ENGINE=InnoDB;
        
        insert into t values(null, 1,1);
        insert into t values(null, 2,2);
        insert into t values(null, 3,3);
        insert into t values(null, 4,4);
        
        create table t2 like t

在可重复读隔离级别下，`binlog_format = statement`执行如下语句：

insert into t2(c,d) select c,d from t;

**如下情景：**

![在这里插入图片描述][20210103220016842.png]

假设SessionB先执行，后提交，会后写入binlog，会产生如下binlog记录，就会导致日志和数据不一样的情况。

insert into t values(-1,-1,-1);
        insert into t2(c,d) select c,d from t;

所以对表t需要加锁，对所有涉及到的资源加锁。

#### 39.2 insert 循环写入 ####

情况一：查询表t的c字段最大值，加一后写入表t2.

insert into t2(c,d) (select c+1, d from t force index(c) order by c desc limit 1);

加锁范围为(3,4)，(4,max)和id = 4这一行。

情况二：查询表t的c字段最大值，加一后写入表t.

insert into t(c,d) (select c+1, d from t force index(c) order by c desc limit 1);

explain命令情况如下：

![在这里插入图片描述][20210103215903256.png]

我们可以脑补执行流程如下：

*  创建临时表，表里有两个字段c和d
 *  从索引c扫描表t，依次取到c=4,3,2,1放到临时表中
 *  由于limit的存在，取一行返回

使用到临时表的的原因：上面语句的执行流程是一边遍历数据，一边更新数据的情况，存在新插入的数据参于语句运算，这样就不符合语义。也就是上面的是要先进行全表扫描，再进行数据插入。

涉及到临时表的sql，我们都可以用内存临时表来进行优化：

create temporary table temp_t(c int,d int) engine=memory;
        insert into temp_t  (select c+1, d from t force index(c) order by c desc limit 1);
        insert into t select * from temp_t;
        drop table temp_t;

#### 39.3 insert 唯一键冲突 ####

**情景如下：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70]

**执行流程如下：**

1.  在 T1 时刻，启动 session A，并执行 insert 语句，此时在索引 c 的 c=5 上加了记录锁。注意，这个索引是唯一索引，因此退化为记录锁。
2.  在 T2 时刻，session B 要执行相同的 insert 语句，发现了唯一键冲突，加上读锁；同样地，session C 也在索引 c 上，c=5 这一个记录上，加了读锁。
3.  T3 时刻，session A 回滚。这时候，session B 和 session C 都试图继续执行插入操作，都要加上写锁。两个 session 都要等待对方的行锁，所以就出现了死锁。

**解决方法：** 使用 insert into … on duplicate key update ，在使用上面语句之后， 在碰到唯一键约束，会执行后面的更新语句。

insert into t values(11,10,10) on duplicate key update d=100;

[20210103220016842.png]: /images/20221120/db6368e39eb44c14b56129bc9a018799.png
[20210103215903256.png]: /images/20221120/e2cec92f9833424491b9df19c872c6e6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70]: /images/20221120/1f009e68e0f04fac990ecca0741c1b62.png