我想编写一个查询来接收来自任何子节点的所有父节点直到根节点，顺序为：
这是图表：
我找到了一些解决方案，但结果有一些重复或节点未排序。提前致谢。
我使用分区和大量数据对 posgtres 12 进行性能测试。每个分区仅包含一个具有 400k 行的站点，并且我生成了大约 1k 个分区表；
对于第一个测试套件，我使用 UUID 作为 id，但我认为如果我将 id 类型更改为 bigint，将使用更少的空间，因此性能更高。填充表格后，我使用不同的数据运行以下选择一百次
SELECT SUM(amount),
FROM test_table
WHERE date >= '2021-02-06'
AND date <= '2021-02-07'
AND site IN ('c3b3771c-4b48-41a9-88eb-4c47d1630644', 'cbb11cdc-cd31-4da2-b14e-9ef878ce03c5', '2609ac86-995b-4320-a3b7-46ba175aa5e2') // randomly picked from site pool
GROUP BY site
ORDER BY site;
UUID 测试套件无日期索引：
CREATE TABLE public.test_table
(
id UUID NOT NULL,
site UUID,
archive UUID,
location UUID,
col_1 UUID,
col_2 UUID,
col_3 UUID,
amount numeric(8,2)
date timestamp with time zone,
....
) PARTITION BY LIST (site);
CREATE TABLE test_table_${site} PARTITION OF test_table FOR VALUES IN ('${site}');
One table size: "265 MB"
BIGINT 测试套件无日期索引：
CREATE TABLE public.test_table
(
id bigint NOT NULL,
site bigint,
archive bigint,
location bigint,
col_1 bigint,
col_2 bigint,
col_3 bigint,
amount numeric(8,2)
date timestamp with time zone,
...
) PARTITION BY LIST (site);
CREATE TABLE test_table_${site} PARTITION OF test_table FOR VALUES IN ('${site}');
One table size: "118 MB"
试验结果
UUID test results (ms) for 100 serial selects
median 1,425.00
percentile 95% 1,930.05
BIGINT test results (ms) for 100 serial selects
median 4,456.00
percentile 95% 9,037.50
同解释：
UUID
"GroupAggregate (cost=61944.56..61947.03 rows=90 width=88)"
" Group Key: test_table_c3b3771c_4b48_41a9_88eb_4c47d1630644.site"
" -> Sort (cost=61944.56..61944.78 rows=90 width=48)"
" Sort Key: test_table_c3b3771c_4b48_41a9_88eb_4c47d1630644.site"
" -> Gather (cost=1000.00..61941.63 rows=90 width=48)"
" Workers Planned: 3"
" -> Parallel Append (cost=0.00..60932.63 rows=30 width=48)"
" -> Parallel Seq Scan on test_table_c3b3771c_4b48_41a9_88eb_4c47d1630644 (cost=0.00..20311.16 rows=10 width=48)"
" Filter: ((date_fiscal >= '2021-02-06 00:00:00+00'::timestamp with time zone) AND (date_fiscal <= '2021-02-07 00:00:00+00'::timestamp with time zone) AND (site = ANY ('{c3b3771c-4b48-41a9-88eb-4c47d1630644,cbb11cdc-cd31-4da2-b14e-9ef878ce03c5,2609ac86-995b-4320-a3b7-46ba175aa5e2}'::uuid[])))"
" -> Parallel Seq Scan on test_table_cbb11cdc_cd31_4da2_b14e_9ef878ce03c5 (cost=0.00..20311.16 rows=10 width=48)"
" Filter: ((date_fiscal >= '2021-02-06 00:00:00+00'::timestamp with time zone) AND (date_fiscal <= '2021-02-07 00:00:00+00'::timestamp with time zone) AND (site = ANY ('{c3b3771c-4b48-41a9-88eb-4c47d1630644,cbb11cdc-cd31-4da2-b14e-9ef878ce03c5,2609ac86-995b-4320-a3b7-46ba175aa5e2}'::uuid[])))"
" -> Parallel Seq Scan on test_table_2609ac86_995b_4320_a3b7_46ba175aa5e2 (cost=0.00..20310.16 rows=10 width=48)"
" Filter: ((date_fiscal >= '2021-02-06 00:00:00+00'::timestamp with time zone) AND (date_fiscal <= '2021-02-07 00:00:00+00'::timestamp with time zone) AND (site = ANY ('{c3b3771c-4b48-41a9-88eb-4c47d1630644,cbb11cdc-cd31-4da2-b14e-9ef878ce03c5,2609ac86-995b-4320-a3b7-46ba175aa5e2}'::uuid[])))"
大整数
"Finalize GroupAggregate (cost=47951.35..47954.22 rows=21 width=80)"
" Group Key: test_table_121.site"
" -> Gather Merge (cost=47951.35..47953.63 rows=18 width=80)"
" Workers Planned: 3"
" -> Partial GroupAggregate (cost=46951.31..46951.48 rows=6 width=80)"
" Group Key: test_table_121.site"
" -> Sort (cost=46951.31..46951.33 rows=6 width=40)"
" Sort Key: test_table_121.site"
" -> Parallel Append (cost=0.00..46951.24 rows=6 width=40)"
" -> Parallel Seq Scan on test_table_121 (cost=0.00..15651.09 rows=2 width=40)"
" Filter: ((date_fiscal >= '2021-02-06 00:00:00+00'::timestamp with time zone) AND (date_fiscal <= '2021-02-07 00:00:00+00'::timestamp with time zone) AND (site = ANY ('{121,122,242}'::bigint[])))"
" -> Parallel Seq Scan on test_table_242 (cost=0.00..15651.09 rows=2 width=40)"
" Filter: ((date_fiscal >= '2021-02-06 00:00:00+00'::timestamp with time zone) AND (date_fiscal <= '2021-02-07 00:00:00+00'::timestamp with time zone) AND (site = ANY ('{121,122,242}'::bigint[])))"
" -> Parallel Seq Scan on test_table_122 (cost=0.00..15649.02 rows=2 width=40)"
" Filter: ((date_fiscal >= '2021-02-06 00:00:00+00'::timestamp with time zone) AND (date_fiscal <= '2021-02-07 00:00:00+00'::timestamp with time zone) AND (site = ANY ('{121,122,242}'::bigint[])))"
怎么可能在数据量较小的情况下，在选择时间上有如此大的差异？或者我在测试过程中犯了一个错误。
提前致谢！
IN我想选择在where 子句中找不到的所有行
CREATE TABLE actions
(
id integer
);
INSERT INTO actions (id)
VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9);
SELECT * FROM actions
WHERE id in (1,2,3, 12, 14)
ORDER BY id DESC
在此示例中，我只想获取 12 和 14 值，因为它们列在 where 子句中，但不存在于表中。
我有一个在数据库中进行一些操作的服务，在我尝试并行发出两个请求之前，一切都正常工作。
下面看sql调试日志：
Executing (2c4cb493-7090-4a83-942a-1b3078a96a7b): START TRANSACTION;
Executing (f49986af-9741-4f20-94ba-0644fbafea05): START TRANSACTION;
Executing (2c4cb493-7090-4a83-942a-1b3078a96a7b): INSERT INTO `history` (`id`,`value`) VALUES (DEFAULT,'value data');
Executing (f49986af-9741-4f20-94ba-0644fbafea05): INSERT INTO `history` (`id`,`value`) VALUES (DEFAULT,'value data');
Executing (2c4cb493-7090-4a83-942a-1b3078a96a7b): UPDATE `table_2` SET `count`=count + 1 WHERE `id` = 1
Executing (f49986af-9741-4f20-94ba-0644fbafea05): UPDATE `table_2` SET `count`=count + 1 WHERE `id` = 1
Executing (2c4cb493-7090-4a83-942a-1b3078a96a7b): COMMIT;
Executing (f49986af-9741-4f20-94ba-0644fbafea05): ROLLBACK;
Deadlock found when trying to get lock; try restarting transaction
我不明白为什么一个事务不等待另一个事务结束，而是抛出死锁异常？
谢谢。
我有一张桌子：
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`status` enum('new','paid') NOT NULL DEFAULT 'new',
`createdAt` datetime NOT NULL,
`updatedAt` datetime NOT NULL,
`can_be_closed` tinyint(1) NOT NULL DEFAULT '1',
`close_reason` varchar(255) DEFAULT NULL,
`interval_before_close` int(11) NOT NULL DEFAULT '10',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
然后选择：
SELECT `id`, `status`, `close_reason` AS `closeReason`, `createdAt`, `updatedAt`
FROM `t1` as t
WHERE (
`t`.`status` = 'new'
AND `t`.`updatedAt` < '2018-06-22 10:14:36' + INTERVAL `interval_before_close` MINUTE
AND `t`.`can_be_closed` = true
)
对于此查询，解释返回全表扫描。
我尝试添加以下索引：
ALTER TABLE `t1`
ADD INDEX `updatedAt` (`updatedAt` ASC);
和：
ALTER TABLE `t1`
ADD INDEX `updatedAt` (`updatedAt` ASC, `interval_before_close` ASC);
但解释没有任何变化。
文件示例：链接
我有这个带有数据的模式：
CREATE TABLE `ticket` (
`id` INT NOT NULL,
`name` VARCHAR(45) NULL,
`type` INT(11) NULL,
PRIMARY KEY (`id`));
CREATE TABLE `task` (
`id` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL,
`ticket_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_task_1_idx` (`ticket_id`),
CONSTRAINT `fk_task_1` FOREIGN KEY (`ticket_id`) REFERENCES `ticket` (`id`)
) ENGINE=InnoDB;
CREATE TABLE `planing` (
`id` int(11) NOT NULL,
`date` datetime DEFAULT NULL,
`task_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_planing_1_idx` (`task_id`),
CONSTRAINT `fk_planing_1` FOREIGN KEY (`task_id`) REFERENCES `task` (`id`)
) ENGINE=InnoDB ;
INSERT INTO `ticket` (`id`, `name`, `type`) VALUES (1, 'ticket_1', 1);
INSERT INTO `task` (`id`, `name`, `ticket_id`) VALUES (1, 'task_1', 1);
INSERT INTO `task` (`id`, `name`, `ticket_id`) VALUES (2, 'task_2', 1);
INSERT INTO `planing` (`id`, `date`, `task_id`) VALUES (1, '2016-06-01 05:00', 2);
INSERT INTO `ticket` (`id`, `name`, `type`) VALUES (2, 'ticket_2', 1);
INSERT INTO `task` (`id`, `name`, `ticket_id`) VALUES (3, 'task_3', 2);
INSERT INTO `task` (`id`, `name`, `ticket_id`) VALUES (4, 'task_4', 2);
INSERT INTO `planing` (`id`, `date`, `task_id`) VALUES (2, '2016-06-01 05:00', 3);
INSERT INTO `planing` (`id`, `date`, `task_id`) VALUES (3, '2016-07-01 05:00', 4);
INSERT INTO `ticket` (`id`, `name`, `type`) VALUES (3, 'ticket_3', 1);
当我执行选择时：
SELECT ticket.id AS ticket_id, task.id AS task_id, planing.date as due_date
FROM ticket
LEFT JOIN task ON task.ticket_id = ticket.id
LEFT JOIN planing ON (task.id = planing.task_id AND planing.date IS NOT NULL)
WHERE ticket.type = 1
GROUP BY ticket.id
ORDER BY planing.date ASC
;
在 MySQL 5.5 上我得到
ticket_id | task_id | due_date
1 |1 |null
3 |null |null
2 |3 |2016-06-01 05:00:00
在 MySQL 5.6 上我得到
ticket_id | task_id | due_date
3 |null |null
1 |2 |2016-06-01 05:00:00
2 |3 |2016-06-01 05:00:00
如何在 MySQL 5.5 上获得与 MySQL 5.6 相同的行为（应用服务器使用 MySQL 5.5 版本）？
我有 3 个表，其中一个是连接表：
用户表：
CREATE TABLE `user` (
`id`,
`name`)
媒体表：
CREATE TABLE `media` (
`id`,
`value`,
`type` enum('M', 'T'))
用户媒体表：
CREATE TABLE `user_media` (
`user_id`,
`media_id`)
我想创建一个选择，让我可以搜索 user.name 和 media.value 并返回格式化值。前任：
INSERT INTO `user` (`id`, `name`) VALUES
(1, 'Luke'),
(2, 'Vader'),
(3, 'Gregory');
INSERT INTO `media` (`id`, `value`, 'type') VALUES
(1, '[email protected]', 'M'),
(2, '[email protected]', 'M'),
(3, '123456789', 'T'),
(4, '123', 'T');
INSERT INTO `user_media` (`user_id`, `media_id`) VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 4);
search: [email protected]
output:
userId |email |tel
1 |[email protected] |123456789
search: 123
output:
userId |email |tel
1 |[email protected] |123456789
2 |null |123
search: Gregory
output:
userId |email |tel
3 |null |null
更新
SELECT u.id AS user_id,
mm.value AS mail,
mt.value AS tel
FROM USER u
LEFT JOIN user_media umt ON u.id = umt.user_id
LEFT JOIN media mt ON mt.id = umt.media_id
LEFT JOIN user_media umm ON u.id = umm.user_id
LEFT JOIN media mm ON mm.id = umm.media_id
WHERE mm.value LIKE "%[email protected]%"
OR mt.value LIKE "%[email protected]%"
OR u.name LIKE "%[email protected]%"
结果：
user_id mail tel
1 [email protected] [email protected]
1 [email protected] [email protected]
1 123456789 [email protected]
1 [email protected] [email protected]
1 [email protected] 123456789
二
SELECT u.id AS user_id,
mm.value AS mail,
mt.value AS tel
FROM USER u
LEFT JOIN user_media umt ON u.id = umt.user_id
LEFT JOIN media mt ON mt.id = umt.media_id AND mt.type="T"
LEFT JOIN user_media umm ON u.id = umm.user_id
LEFT JOIN media mm ON mm.id = umm.media_id AND mm.type="M"
WHERE mm.value LIKE "%[email protected]%"
OR mt.value LIKE "%[email protected]%"
OR u.name LIKE "%[email protected]%"
结果：
user_id mail tel
1 [email protected] null
1 [email protected] null
1 [email protected] 123456789
三
SELECT u.id AS user_id,
mm.value AS mail,
mt.value AS tel
FROM USER u
LEFT JOIN user_media umt ON u.id = umt.user_id
LEFT JOIN media mt ON mt.id = umt.media_id
LEFT JOIN user_media umm ON u.id = umm.user_id
LEFT JOIN media mm ON mm.id = umm.media_id
WHERE mt.type="T"
AND mm.type="M"
AND (mm.value LIKE "%Gregory%"
OR mt.value LIKE "%Gregory%"
OR u.name LIKE "%Gregory%")
结果：0