mysql innodb myisam 混合_Mysql数据库InnoDB表和MyISAM表混用后，事务回滚测试。

测试环境CENTOS7.3+MYSQL5.7

一张表使用InnoDB引擎，表名称 innodb_test

另外一张表使用MyISAM引擎，表名称 innodb_test

在事务操作的时候，看看到底会发生什么情况。

表结构相同只有3个字段id，name, money

+-------+---------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------------+------+-----+---------+----------------+

| id | int(10) | NO | PRI | NULL | auto_increment |

| name | char(30) | YES | | NULL | |

| money | decimal(11,2) | YES | | NULL | |

+-------+---------------+------+-----+---------+----------------+

两张表都只有一条数据

+----+----------+---------+

| id | name | money |

+----+----------+---------+

| 1 | colinshi | 1000.00 |

+----+----------+---------+

先关闭了自动事务提交

mysql> SET AUTOCOMMIT =0;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'autocommit';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | OFF |

+---------------+-------+

mysql> begin;

开始事务操作，这次测试成功commit

mysql> update innodb_test set money=500 WHERE id = 1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> update myisam_test set money=500 WHERE id = 1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from innodb_test;

+----+----------+--------+

| id | name | money |

+----+----------+--------+

| 1 | colinshi | 500.00 |

+----+----------+--------+

1 row in set (0.00 sec)

mysql> select * from myisam_test;

+----+----------+--------+

| id | name | money |

+----+----------+--------+

| 1 | colinshi | 500.00 |

+----+----------+--------+

1 row in set (0.00 sec)

在成功commit的情况下数据库没有问题。都成了500，那么如果提交错误需要回滚会是什么情况呢。

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> update myisam_test set money=300 WHERE id = 1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> update innodb_test set money=300 WHERE id = 1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> rollback;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from myisam_test;

+----+----------+--------+

| id | name | money |

+----+----------+--------+

| 1 | colinshi | 300.00 |

+----+----------+--------+

1 row in set (0.00 sec)

mysql> select * from innodb_test;

+----+----------+--------+

| id | name | money |

+----+----------+--------+

| 1 | colinshi | 500.00 |

+----+----------+--------+

1 row in set (0.00 sec)

发现MyISAM表并没有进行回滚操作，而InnoDB表进行了回滚操作。

也就是说一个事务如果同时处理了MyISAM引擎表，InnoDB引擎表，那么回滚会出现数据不一致的情况。

我还直接使用了python的mysql对数据库使用了事务操作结果和mysql客户端是相同情况

import pymysql

import time

def delSql(dzp_id):

conn = pymysql.connect(host='192.168.1.71', user='root', passwd='!QAZ2wsx', db='colinshitop')

cur = conn.cursor()

cur.execute('use colinshitop')

try:

cur.execute("update innodb_test set money='{}' WHERE id = 1".format(dzp_id))

cur.execute("update myisam_test set money='{}' WHERE id = 1".format(dzp_id))

cur.execute("INSERT into name ('name', 'fdjaskfjsa') VALUES ('fdkjakf', '值2')")

conn.commit()

print('提交commit')

except:

conn.rollback()

print('回滚rollback')

finally:

cur.close()

conn.close()

if __name__ == '__main__':

dzp_id = input('请输入金额：',)

delSql(dzp_id)

print('5秒后程序终止')

time.sleep(5)

数据库中尽量不要同时用InnoDB表和MyISAM表混用。如果真的迫不得已需要混用，一定不能同一个事务同时对两种表引擎操作。