· 6 years ago · Aug 04, 2019, 12:10 PM
1SELECT VERSION();
2
3DROP TABLE IF EXISTS `accounts`, `cfg_conn_types`, `log_connections_`;
4
5CREATE TABLE IF NOT EXISTS `accounts` (
6 `id` INT UNSIGNED NOT NULL PRIMARY KEY
7) ENGINE=InnoDB DEFAULT CHARSET=utf8;
8
9INSERT INTO `accounts`
10VALUES (1612), (3595);
11
12CREATE TABLE IF NOT EXISTS `cfg_conn_types` (
13 `id` TINYINT(3) UNSIGNED NOT NULL PRIMARY KEY
14) ENGINE=InnoDB DEFAULT CHARSET=utf8;
15
16INSERT INTO `cfg_conn_types`
17VALUES (0), (1), (5);
18
19CREATE TABLE IF NOT EXISTS `log_connections_` (
20 `acc_id` INT UNSIGNED NOT NULL,
21 `action` TINYINT(3) UNSIGNED NOT NULL,hbolympia
22 KEY `FK_log_connections__cfg_conn_types` (`action`),
23 CONSTRAINT `FK_log_connections__accounts` FOREIGN KEY (`acc_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
24 CONSTRAINT `FK_log_connections__cfg_conn_types` FOREIGN KEY (`action`) REFERENCES `cfg_conn_types` (`id`) ON UPDATE CASCADE
25) ENGINE=InnoDB DEFAULT CHARSET=utf8;
26
27SHOW CREATE TABLE `log_connections_`;
28
29INSERT INTO `log_connections_` (`acc_id`, `action`)
30VALUES
31 (1612, 0), (3595, 0), (3595, 1), (1612, 0), (1612, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (1612, 0),
32 (1612, 1), (1612, 0), (1612, 1), (1612, 0), (1612, 1), (1612, 0), (1612, 1), (1612, 0), (1612, 1), (1612, 0),
33 (1612, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (1612, 0), (1612, 1), (3595, 0),
34 (3595, 1), (3595, 0), (3595, 1), (1612, 0), (1612, 1), (1612, 0), (1612, 1), (3595, 0), (3595, 1), (3595, 0),
35 (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (3595, 0),
36 (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (1612, 0), (1612, 1), (3595, 0),
37 (3595, 1), (1612, 0), (1612, 1), (1612, 0), (1612, 1), (1612, 0), (1612, 1), (1612, 0), (1612, 1), (3595, 0),
38 (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (3595, 0),
39 (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (3595, 0),
40 (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (3595, 0),
41 (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (3595, 0),
42 (3595, 1), (3595, 0), (3595, 1), (1612, 0), (1612, 1), (1612, 0), (1612, 1), (1612, 0), (1612, 1), (3595, 0),
43 (3595, 1), (3595, 0), (3595, 5), (3595, 0), (1612, 0), (1612, 1), (1612, 0), (1612, 1), (1612, 0), (1612, 1),
44 (3595, 0), (3595, 0), (3595, 5), (3595, 0), (1612, 0), (1612, 1), (1612, 0), (1612, 1), (3595, 0), (3595, 1),
45 (1612, 0), (1612, 1), (1612, 0), (1612, 1), (1612, 0), (1612, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1),
46 (3595, 0), (3595, 1), (1612, 0), (1612, 1), (1612, 0), (1612, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1),
47 (3595, 0), (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1),
48 (3595, 0), (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1),
49 (1612, 0), (1612, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1), (1612, 0), (1612, 1),
50 (1612, 0), (1612, 0), (1612, 1), (1612, 0), (1612, 1), (1612, 0), (1612, 1), (1612, 0), (1612, 1), (3595, 0),
51 (3595, 1), (3595, 0), (3595, 1), (3595, 0), (3595, 1);
52
53
54
55
56 SELECT
57 `acc_id`,
58 `action`
59FROM
60 `log_connections_`
61WHERE
62 `acc_id` = 3595;
63
64 EXPLAIN
65SELECT
66 `acc_id`,
67 `action`
68FROM
69 `log_connections_`
70WHERE
71 `acc_id` = 3595 AND
72 `action` NOT IN (0, 1);
73
74SELECT
75 `acc_id`,
76 `action`
77FROM
78 `log_connections_`
79WHERE
80 `acc_id` = 3595 AND
81 `action` NOT IN (0, 1);