K9QmjERp

1-- make me some data
2
3root@localhost [kris]> drop table if exists staff;
4Query OK, 0 rows affected (0.01 sec)
5
6root@localhost [kris]> CREATE TABLE `staff` (
7    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
8    ->   `hired` date NOT NULL,
9    ->   `salary` decimal(8,2) NOT NULL,
10    ->   `name` varchar(100) DEFAULT NULL,
11    ->   PRIMARY KEY (`id`)
12    -> ) ENGINE=InnoDB CHARSET=utf8mb4;
13Query OK, 0 rows affected (0.01 sec)
14
15root@localhost [kris]> insert into staff values (NULL, date(now() - interval rand() * 3650 day), 30000 + rand() * 60000, uuid());
16Query OK, 1 row affected, 1 warning (0.00 sec)
17Note (Code 1265): Data truncated for column 'salary' at row 1
18
19root@localhost [kris]> select * from staff;
20+----+------------+----------+--------------------------------------+
21| id | hired      | salary   | name                                 |
22+----+------------+----------+--------------------------------------+
23|  1 | 2016-12-10 | 90000.69 | c4ede66a-7b4a-11e9-b69b-e8055fcce103 |
24+----+------------+----------+--------------------------------------+
251 row in set (0.00 sec)
26
27root@localhost [kris]> insert into staff select NULL, date(now() - interval rand() * 3650 day), 30000 + rand() + 60000, uuid() from staff;
28...
29root@localhost [kris]> select count(*) from staff;
30+----------+
31| count(*) |
32+----------+
33|   131072 |
34+----------+
351 row in set (0.01 sec)
36
37root@localhost [kris]> select * from staff limit 3;
38+----+------------+----------+--------------------------------------+
39| id | hired      | salary   | name                                 |
40+----+------------+----------+--------------------------------------+
41|  1 | 2016-12-10 | 33806.59 | c4ede66a-7b4a-11e9-b69b-e8055fcce103 |
42|  2 | 2012-06-14 | 31558.27 | f92a50b2-7b4a-11e9-b69b-e8055fcce103 |
43|  3 | 2009-07-26 | 86371.59 | fabe69f4-7b4a-11e9-b69b-e8055fcce103 |
44+----+------------+----------+--------------------------------------+
453 rows in set (0.01 sec)
46
47-- IODKU
48root@localhost [kris]> start transaction read write;
49Query OK, 0 rows affected (0.00 sec)
50
51root@localhost [kris]> insert into staff values ( 3, date(now()), 10000, "cookie") on duplicate key update hired = date(now()), salary = 10000, name = "cookie";
52Query OK, 2 rows affected (0.00 sec)
53
54root@localhost [kris]> commit;
55Query OK, 0 rows affected (0.00 sec)
56
57root@localhost [kris]> select * from staff where id = 3;
58+----+------------+----------+--------+
59| id | hired      | salary   | name   |
60+----+------------+----------+--------+
61|  3 | 2019-05-21 | 10000.00 | cookie |
62+----+------------+----------+--------+
631 row in set (0.00 sec)
64
65-- IODKU manual
66root@localhost [kris]> start transaction read write;
67Query OK, 0 rows affected (0.00 sec)
68
69root@localhost [kris]> insert into staff values (3, date(now()-interval 2 day), 11000, "keks");
70ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
71root@localhost [kris]> update staff set id=3, hired=date(now() - interval 2 day), salary=11000, name="keks" where id = 3;
72Query OK, 1 row affected (0.00 sec)
73Rows matched: 1  Changed: 1  Warnings: 0
74
75root@localhost [kris]> commit;
76Query OK, 0 rows affected (0.15 sec)
77
78root@localhost [kris]> select * from staff where id = 3;
79+----+------------+----------+------+
80| id | hired      | salary   | name |
81+----+------------+----------+------+
82|  3 | 2019-05-19 | 11000.00 | keks |
83+----+------------+----------+------+
841 row in set (0.00 sec)
85
86
87-- REPLACE INTO
88root@localhost [kris]> start transaction read write;
89Query OK, 0 rows affected (0.00 sec)
90
91root@localhost [kris]> replace into staff values (3, date(now() - interval 1 day), 20000, "cookie 2");
92Query OK, 2 rows affected (0.01 sec)
93
94root@localhost [kris]> commit;
95Query OK, 0 rows affected (0.00 sec)
96
97root@localhost [kris]> select * from staff where id = 3;                        +----+------------+----------+----------+
98| id | hired      | salary   | name     |
99+----+------------+----------+----------+
100|  3 | 2019-05-20 | 20000.00 | cookie 2 |
101+----+------------+----------+----------+
1021 row in set (0.00 sec)
103
104-- REPLACE INTO, manual
105root@localhost [kris]> start transaction read write;
106Query OK, 0 rows affected (0.00 sec)
107
108root@localhost [kris]> delete from staff where id = 3;
109Query OK, 1 row affected (0.00 sec)
110
111root@localhost [kris]> insert into staff values (3, date(now() - interval 3 day), 21000, "keks 2");
112Query OK, 1 row affected (0.00 sec)
113
114root@localhost [kris]> commit;
115Query OK, 0 rows affected (0.00 sec)
116
117root@localhost [kris]> select * from staff where id =3;
118+----+------------+----------+--------+
119| id | hired      | salary   | name   |
120+----+------------+----------+--------+
121|  3 | 2019-05-18 | 21000.00 | keks 2 |
122+----+------------+----------+--------+
1231 row in set (0.00 sec)