update测试用例（mysql）

update..left join...set...where

create table t1 (c1 int, c2 char(6), c3 int)engine=innodb;
create table t2 (c1 int, c2 char(6))engine=innodb;
insert into t1 values (1, "t1c2-1", 10), (2, "t1c2-2", 20);
update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1";
update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1" where t1.c3 = 10;
select * from t1;
select * from t2;
drop table t1, t2;

预期测试结果

mysql> select * from t1;
+------+--------+------+
| c1   | c2     | c3   |
+------+--------+------+
|    1 | t1c2-1 |   10 |
|    2 | t1c2-2 |   20 |
+------+--------+------+
2 rows in set (0.00 sec)

mysql> select * from t2;
Empty set (0.00 sec)

update...set..where

create table t1 (id int not null auto_increment primary key, id_str varchar(32));
insert into t1 (id_str) values ("test");
update t1 set id_str = concat(id_str, id) where id = last_insert_id();
select * from t1;
drop table t1;

预期测试结果：

mysql> select * from t1;
+----+--------+
| id | id_str |
+----+--------+
|  1 | test1  |
+----+--------+
1 row in set (0.00 sec)

update...set...where...order by...limit

CREATE TABLE t1 (
a INTEGER,
b INTEGER,
c INTEGER,
d INTEGER,
KEY key1 (a,b,c)
)engine=innodb;

INSERT INTO t1 (a,b,c,d) VALUES (1, 1, 1, 4), (2, 2, 2, 5), (2, 3, 4, 7),
(3, 3, 3, 9), (4, 4, 4, 0), (5, 5, 5, 1), (5, 6, 7, 3), (5, 7, 9, 9);

UPDATE t1 SET c = 72 WHERE a = 2 ORDER BY b ASC LIMIT 1;
SELECT * FROM t1;
drop table t1;

预期结果为：

mysql> SELECT * FROM t1;
+------+------+------+------+
| a    | b    | c    | d    |
+------+------+------+------+
|    1 |    1 |    1 |    4 |
|    2 |    2 |   72 |    5 |
|    2 |    3 |    4 |    7 |
|    3 |    3 |    3 |    9 |
|    4 |    4 |    4 |    0 |
|    5 |    5 |    5 |    1 |
|    5 |    6 |    7 |    3 |
|    5 |    7 |    9 |    9 |
+------+------+------+------+
8 rows in set (0.00 sec)

UPDATE DOES NOT USE INDEX

CREATE TABLE t1(
id INTEGER NOT NULL AUTO_INCREMENT,
token VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (id),
KEY token (token)
)engine=innodb;

INSERT INTO t1 VALUES (1, "abc"), (2, "def");
SELECT * FROM t1;
UPDATE t1 SET token = X'ad';
SELECT * FROM t1;

UPDATE t1 SET token = NULL WHERE token = X'ad';
SELECT * FROM t1;
DROP TABLE t1;

预期结果为：

注：2次 SELECT * FROM t1的结果相同，如下所示

mysql> SELECT * FROM t1;
+----+-------+
| id | token |
+----+-------+
|  1 |       |
|  2 |       |
+----+-------+

with update and partial key part

create table t1 (a int, b char(255), key(a, b(20)));
insert into t1 values (0, '1');
update t1 set b = b + 1 where a = 0;
select * from t1;
drop table t1;

预期测试结果为：

mysql> select * from t1;
+------+------+
| a    | b    |
+------+------+
|    0 | 2    |
+------+------+
1 row in set (0.00 sec)

Erroneous data truncation warnings on multi-table updates

create table t1 (a int, b varchar(10), key b(b(5))) engine=innodb;
create table t2 (a int, b varchar(10)) engine=innodb;
insert into t1 values ( 1, 'abcd1e');
insert into t1 values ( 2, 'abcd2e');
insert into t2 values ( 1, 'abcd1e');
insert into t2 values ( 2, 'abcd2e');
analyze table t1,t2;
update t1, t2 set t1.a = t2.a where t2.b = t1.b;
select * from t1;
select * from t2;
show warnings;
drop table t1, t2;

期望的测试结果为：

mysql> analyze table t1,t2;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status   | OK       |
| test.t2 | analyze | status   | OK       |
+---------+---------+----------+----------+
2 rows in set (0.00 sec)

mysql> select * from t1;
+------+--------+
| a    | b      |
+------+--------+
|    1 | abcd1e |
|    2 | abcd2e |
+------+--------+
2 rows in set (0.00 sec)

mysql> select * from t2;
+------+--------+
| a    | b      |
+------+--------+
|    1 | abcd1e |
|    2 | abcd2e |
+------+--------+
2 rows in set (0.00 sec)

Update with subquery with ref built with a key from the updated table crashes server

create table t1(f1 int, f2 int)engine=innodb;
create table t2(f3 int, f4 int)engine=innodb;
create index idx on t2(f3);
insert into t1 values(1,0),(2,0);
insert into t2 values(1,1),(2,2);
UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1);
select * from t1;
drop table t1,t2;

预期测试结果为：

mysql> select * from t1;
+------+------+
| f1   | f2   |
+------+------+
|    1 |    1 |
|    2 |    2 |
+------+------+
2 rows in set (0.00 sec)

sometimes server accepts sum func in update/delete where condition

create table t1(f1 int);
select DATABASE();
update t1 set f1=1 where count(*)=1;
select DATABASE();
delete from t1 where count(*)=1;
drop table t1;

预期测试结果为：

mysql> update t1 set f1=1 where count(*)=1;
ERROR 1111 (HY000): Invalid use of group function

mysql> delete from t1 where count(*)=1;
ERROR 1111 (HY000): Invalid use of group function

Optimize "DELETE|UPDATE ... ORDER BY ... LIMIT n" to use an index

create table t1 ( a int, b int default 0, index (a) )engine=innodb;
insert into t1 (a) values (0),(0),(0),(0),(0),(0),(0),(0);

flush status;
select a from t1 order by a limit 1;
show status like 'handler_read%';

flush status;
update t1 set a=9999 order by a limit 1;
update t1 set b=9999 order by a limit 1;
show status like 'handler_read%';

flush status;
delete from t1 order by a limit 1;
show status like 'handler_read%';

flush status;
delete from t1 order by a desc limit 1;
show status like 'handler_read%';

alter table t1 disable keys;

flush status;
delete from t1 order by a limit 1;
show status like 'handler_read%';

select * from t1;
update t1 set a=a+10,b=1 order by a limit 3;
update t1 set a=a+11,b=2 order by a limit 3;
update t1 set a=a+12,b=3 order by a limit 3;
select * from t1 order by a;

预期的测试结果为：

mysql> select a from t1 order by a limit 1;
+------+
| a    |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+------+
| a    | b    |
+------+------+
|    0 |    0 |
|    0 |    0 |
|    0 |    0 |
|    0 |    0 |
|    0 |    0 |
+------+------+
5 rows in set (0.00 sec)

mysql> select * from t1 order by a;
+------+------+
| a    | b    |
+------+------+
|   11 |    2 |
|   21 |    2 |
|   22 |    3 |
|   22 |    3 |
|   23 |    3 |
+------+------+
5 rows in set (0.00 sec)

select datefield is null not updated

create table t1 (f1 date not null) engine=innodb;
insert into t1 values('2000-01-01'),('0000-00-00');
update t1 set f1='2002-02-02' where f1 is null;
select * from t1;
drop table t1;

预期结果为：

mysql> select * from t1;
+------------+
| f1         |
+------------+
| 2000-01-01 |
| 2002-02-02 |
+------------+
2 rows in set (0.00 sec)

Updating field named like '*name'

create table t1(f1 int, `*f2` int)engine=innodb;
insert into t1 values (1,1);
update t1 set `*f2`=1;
select * from t1;
drop table t1;

期望测试结果为：

mysql> select * from t1;
+------+------+
| f1   | *f2  |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

INSERT INTO ... on unique constraint with data

CREATE TABLE t1 (
  a INT(11),
  quux decimal(17, 10),
  UNIQUE KEY bar (a),
  KEY quux (quux)
)engine=innodb;

INSERT INTO
 t1 ( a, quux )
VALUES
    ( 1,    1 ),
    ( 2,  0.1 );

INSERT INTO t1( a )
  SELECT @newA := 1 + a FROM t1 WHERE quux <= 0.1;

SELECT * FROM t1;
DROP TABLE t1;

预期结果为：

mysql> SELECT * FROM t1;
+------+--------------+
| a    | quux         |
+------+--------------+
|    1 | 1.0000000000 |
|    2 | 0.1000000000 |
|    3 |         NULL |
+------+--------------+
3 rows in set (0.01 sec)

UPDATE ON VARCHAR AND TEXT COLUMNS

CREATE TABLE t1 (a VARCHAR(50), b TEXT, c CHAR(50)) ENGINE=innodb;
INSERT INTO t1 (a, b, c) VALUES ('start trail', '', 'even longer string');
UPDATE t1 SET b = a, a = 'inject';
SELECT a, b FROM t1;
UPDATE t1 SET b = c, c = 'inject';
SELECT c, b FROM t1;
DROP TABLE t1;

预期结果为：

mysql> SELECT a, b FROM t1;
+--------+-------------+
| a      | b           |
+--------+-------------+
| inject | start trail |
+--------+-------------+
1 row in set (0.00 sec)

mysql> SELECT c, b FROM t1;
+--------+--------------------+
| c      | b                  |
+--------+--------------------+
| inject | even longer string |
+--------+--------------------+
1 row in set (0.00 sec)