|
SET SQL_MODE=CONCAT(@@sql_mode,',STRICT_ALL_TABLES');
|
|
CREATE OR REPLACE TABLE t (a INT, b INT, c INT, KEY(a));
|
|
INSERT INTO t VALUES (1,1,1),(2,2,2);
|
|
UPDATE t SET c = 10 WHERE a = 'foo';
|
UPDATE t SET c = 20 WHERE b = 'foo';
|
|
DELETE FROM t WHERE a = 'foo';
|
DELETE FROM t WHERE b = 'foo';
|
|
INSERT INTO t VALUES (0,0,0);
|
|
UPDATE t SET c = 10 WHERE a = 'foo';
|
UPDATE t SET c = 20 WHERE b = 'foo';
|
|
DELETE FROM t WHERE a = 'foo';
|
|
INSERT INTO t VALUES (0,0,0);
|
|
DELETE FROM t WHERE b = 'foo';
|
|
|
DROP TABLE t;
|
|
10.2 ceb40ef4
|
MariaDB [test]> INSERT INTO t VALUES (1,1,1),(2,2,2);
|
Query OK, 2 rows affected (0.024 sec)
|
Records: 2 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]>
|
MariaDB [test]> UPDATE t SET c = 10 WHERE a = 'foo';
|
Query OK, 0 rows affected (0.020 sec)
|
Rows matched: 0 Changed: 0 Warnings: 0
|
|
MariaDB [test]> UPDATE t SET c = 20 WHERE b = 'foo';
|
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'foo'
|
MariaDB [test]>
|
MariaDB [test]> DELETE FROM t WHERE a = 'foo';
|
Query OK, 0 rows affected (0.000 sec)
|
|
MariaDB [test]> DELETE FROM t WHERE b = 'foo';
|
Query OK, 0 rows affected, 1 warning (0.000 sec)
|
|
MariaDB [test]>
|
MariaDB [test]> INSERT INTO t VALUES (0,0,0);
|
Query OK, 1 row affected (0.008 sec)
|
|
MariaDB [test]>
|
MariaDB [test]> UPDATE t SET c = 10 WHERE a = 'foo';
|
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'foo'
|
MariaDB [test]> UPDATE t SET c = 20 WHERE b = 'foo';
|
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'foo'
|
MariaDB [test]>
|
MariaDB [test]> DELETE FROM t WHERE a = 'foo';
|
Query OK, 1 row affected, 1 warning (0.007 sec)
|
|
MariaDB [test]>
|
MariaDB [test]> INSERT INTO t VALUES (0,0,0);
|
Query OK, 1 row affected (0.007 sec)
|
|
MariaDB [test]>
|
MariaDB [test]> DELETE FROM t WHERE b = 'foo';
|
Query OK, 1 row affected, 1 warning (0.007 sec)
|
So, first updates and deletes are performed when there is no matching row.
- UPDATE/DELETE with search by indexed column doesn't produce warnings or errors;
- UPDATE with search by non-indexed column produces an error;
- DELETE with search by non-indexed column produces a warning;
Then updates and deletes are performed when there is a matching row.
- UPDATE produces an error, regardless whether the search column is indexed or not;
- DELETE produces a warning, regardless whether the search column is indexed or not.
|