MySQL批量操作语句，会部分成功吗？

2020-08-24

加入收藏

今天就讨论以下几个数据批量操作的问题

1. 在一个批量操作的sql中，如果一个失败，其他的会怎么样呢

2. 对于大数据表，线上更新而不影响用户使用

3. 事务与锁的关系

建立测试表member表

表结构

1. 在一个批量操作的sql中，如果一个失败，其他的会怎么样呢

第一种情况：先看一下，多条语句没有事务控制的代码

$conn = Yii::$App->db1;
    $sql1 = 'insert into member (name,password) values ("yang","vincent")';
	$sql2 = 'insert into member (name,password,gender) values ("yang","vincent1")';
	    $conn->createCommand($sql1)->execute();
	    $conn->createCommand($sql2)->execute();

执行结果怎么呢，下面我们来看看，结果大家也猜得到，报错了

SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row.....

字段不匹配，但是，数据表第一条已经插入成功，只是第二条语句出错了！

第二种情况：多条语句，有事务控制

测试代码如下：

$conn = Yii::$app->db1;
		$transaction = $conn->beginTransaction();
		try {
			$sql1 = 'insert into member (name,password) values ("yang","vincent")';
			$sql2 = 'insert into member (name,password,gender) values ("yang","vincent1")';
		    $conn->createCommand($sql1)->execute();
		    $conn->createCommand($sql2)->execute();
		    // ... executing other SQL statements ...
		    $transaction->commit();
		} catch (Exception $e) {
		    $transaction->rollBack();
		}

结果同样出错，错误信息和第一种情况一致，但是这次数据表一条记录也没有，不难理解，我们都知道，在一个事务中，多个操作，要么都失败，要么都成功。

第三种情况：一条语句，批量插入，有事务控制

测试代码如下：

$sql1 = 'insert into member (name,password) values ("yang","vincent"),("yang","lily",1)';
		    $conn->createCommand($sql1)->execute();
		    $transaction->commit();

执行代码出错，错误和上面情况一致，数据表一条也没有写入成功，所以批量操作的sql语句是原子性的操作，相当于事务控制。

总结：

在进行批量插入时，要么都成功，要么都失败，比如：在一个有唯一性索引的表批量插入时，只要有一个重复，整个操作都会失败，并不是部分成功，部分失败，在实际开发中要注意！

2. 大数据表，如何做到线上更新而不影响用户使用

我们的应用上线以后，功能在不停的迭代更新，数据表难免会修改，当数据量很大时，进行table更新，如修改表结构、增加字段、修改字段值、加索引，这些操作肯定会对用户有影响的。

比如增加索引，一张表千万数量级，可能会卡住几十分钟或长达几个小时。

常用的方法是：

在测试环境测试修改的影响，如果是几分钟，可以直接在线修改，如果时间长则不行

选一个用户使用最少的时间，停服更新；这应该是最常用的方法，这也是程序猿苦逼的原因，经常要熬夜功能上线。

业界的其他方案，也只有大公司能玩得转：

fecebook自己开发的工具，地址

http://bazaar.launchpad.net/~MySQLatfacebook/mysqlatfacebook/tools/files/head:/osc

也是8年前的版本，没有更新过了，只适用于mysql5.0，5.1 ，用php写的脚步，有兴趣的可以看一下，也许有借鉴意义；

像BAT这些大公司内部应该都有自己研发的工具，毕竟他们更有这些需求，技术大牛也多！

在线表结构的在线修改是一个业界难题！

3. 事务与锁的关系

事务中会不会加锁？

事务加不加锁，和事务的隔离级别有关，隔离级别中的可重复读和串行化，本身事务已经完全隔离了，所以事务内操作就没必要加锁了。

事务本身并不加锁，加不加锁取决于执行的语句，一旦加锁直到事务提交或回滚才会释放。

事务的作用是什么？

事务保证操作的完整性，如订单和库存的操作，但不保证数据的正确性，如 update goods set num=num-1 where goods_id=1,所以这样不能保证超卖情况！还需要锁机制。

锁的作用是什么？

锁是避免并发的操作导致数据破坏，导致结果不是预期，常用在一些数据敏感的场合！