mysql - 事务MySQL

Question

如何在SQL语句出现错误时启动事务，系统会自动回滚更改？

我有以下 SQL 语句

START TRANSACTION;

BEGIN;

INSERT INTO `users`(id,name,gender,email,age)
    VALUES(11121,'sss',0,'ssss',22);

INSERT INTO `teachers`(`UserId`,`teachingSubject`)
    VALUES(11121,300);

COMMIT;

当第二条语句发生错误时，它不会回滚。为什么？ 'teachingSubject' 是 TINYINT(2)，第二条语句将超出其范围。我想回滚所有语句。

Below is my procedure.

mysql> source d:\s.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

ERROR 1264 (22003): Out of range value for column 'te
Query OK, 0 rows affected (0.09 sec)

mysql> SELECT * FROM teachers
    -> ;
+--------+-----------------+
| UserId | teachingSubject |
+--------+-----------------+
|  11111 |               1 |
|  11112 |               9 |
|  11113 |             100 |
+--------+-----------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM users;
+-------+--------+--------+----------------+------+
| id    | name   | gender | email          | age  |
+-------+--------+--------+----------------+------+
| 11111 | Killer |       | ssss@sss       |   12 |
| 11112 | sss    |        | ssss           |   22 |
| 11113 | sss    |        | ssss           |   22 |
| 11114 | sss    |        | ssss           |   22 |
| 11115 | sss    |        | ssss           |   22 |
| 11116 | sss    |        | ssss           |   22 |
| 11117 | sss    |        | ssss           |   22 |
| 11118 | sss    |        | ssss           |   22 |
| 11119 | sss    |        | ssss           |   22 |
| 11120 | sss    |        | ssss           |   22 |
| 11121 | sss    |        | ssss           |   22 |
| 12345 | Sefler |       | sefler@125.com |   12 |
+-------+--------+--------+----------------+------+
12 rows in set (0.00 sec)

在 teachers 表中没有 id=11121 的记录，但在 users 表中有。

Answer 1

当 int 溢出时，MySQL 不会抛出错误。相反，它只会将其截断为可能的最高值。在 unsigned tinyint 的情况下是 255。但不会抛出实际错误。