WITH RECURSIVE
cte AS ( SELECT *, 1 level
FROM category
WHERE id = $category_id
UNION ALL
SELECT cat.*, cte.level + 1
FROM category cat
JOIN cte ON cat.id = cte.parent_id )
SELECT *
FROM cte
ORDER BY level;SELECT CONCAT_WS('=>', c1.id, c2.id, c3.id, c4.id, c5.id) path
FROM category c1
LEFT JOIN category c2 ON c1.parent_id = c2.id
LEFT JOIN category c3 ON c2.parent_id = c3.id
LEFT JOIN category c4 ON c3.parent_id = c4.id
LEFT JOIN category c5 ON c4.parent_id = c5.id
WHERE c1.id = $category_idSELECT id, CAST(array_test AS CHAR) array_test
FROM test
WHERE JSON_OVERLAPS(array_test, CAST('[2]' AS JSON));запрос для выборки всех записей у которых в поле 'condition' есть "action": "6"
SELECT DISTINCT action.*
FROM action
CROSS JOIN JSON_TABLE(action.`condition`,
'$[*].action' COLUMNS (action INT PATH '$')) jsontable
WHERE jsontable.action = 6WITH
cte1 AS ( SELECT t1.ts, t1.val,
t2.ts ts_before, t2.val val_before,
t3.ts ts_after, t3.val val_after
FROM test t1
JOIN test t2 ON t1.ts >= TIMESTAMP(t2.ts, @delta)
JOIN test t3 ON t1.ts <= TIMESTAMP(t3.ts, @delta) ),
cte2 AS ( SELECT *,
ROW_NUMBER() OVER (PARTITION BY ts ORDER BY ts_before DESC) rn_before,
ROW_NUMBER() OVER (PARTITION BY ts ORDER BY ts_after ASC) rn_after
FROM cte1 )
SELECT ts,
val,
ts_before,
val_before,
ts_after,
val_after,
CASE WHEN val_after = val_before
THEN val_before
ELSE val_before + (val_after - val_before) / TIMESTAMPDIFF(SECOND, ts_after, ts_before) * TIMESTAMPDIFF(SECOND, ts, TIMESTAMP(ts_before, @delta))
END val_approximated
FROM cte2
WHERE ts > '2021-01-02'
AND rn_before = 1
AND rn_after = 1Не имел дела раньше с оконными функциями в MySQL.
WITH cte AS (SELECT 1 id, CAST('[12, 13, {"12": 123} ]' AS JSON) sort)
SELECT cte.id, JSON_ARRAYAGG(jsontable.element) sort
FROM cte
CROSS JOIN JSON_TABLE(cte.sort,
'$[*]' COLUMNS (element JSON PATH '$')) jsontable
WHERE jsontable.element != CAST('{"12": 123}' AS JSON)
GROUP BY cte.idWITH
cte AS ( SELECT *,
ROW_NUMBER() OVER (PARTITION BY sender, receiver ORDER BY created_at DESC) rn
FROM message )
SELECT u1.username sendername,
u2.username receivername,
cte.text_message,
cte.created_at
FROM cte
JOIN users u1 ON cte.sender = u1.id
JOIN users u2 ON cte.receiver = u2.id
WHERE cte.rn = 1[[:<:]], [[:>:]]
These markers stand for word boundaries.
The Spencer library supports word-beginning and word-end boundary markers ([[:<:]] and [[:>:]] notation). ICU does not. For ICU, you can use \b to match word boundaries; double the backslash because MySQL interprets it as the escape character within strings.
ALTER TABLE tablename
MODIFY COLUMN date_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
MODIFY COLUMN date_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;INSERT INTO tablename (active, user_id, value)
WITH RECURSIVE
cte AS ( SELECT 1 num
UNION ALL
SELECT num + 1 FROM cte WHERE num < 9999 )
SELECT 1, 1, num
FROM cte;SET SESSION cte_max_recursion_depth = 10000;INSERT INTO tablename (active, user_id, value)
SELECT 1, 1, 1 + t1.num + t2.num * 10 + t3.num * 100 + t4.num * 1000 AS value
FROM (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1
CROSS JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2
CROSS JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3
CROSS JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4;
HAVING value <= 9999;Вопрос: как мне правильно спроектировать таблицу для связей между этими товарами. Чтобы каждый из этих товаров ссылался друг на друга?
CREATE TABLE groups_of_goods (
group_id BIGINT UNSIGNED NOT NULL,
product_id BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (group_id, product_id),
FOREIGN KEY fk_product (product_id) REFERENCES product (product_id)
);CREATE TABLE users (
...
phone VARCHAR(255),
....
);ALTER TABLE users ALGORITHM = INPLACE
ADD COLUMN phone_num BIGINT UNSIGNED AS (REGEXP_REPLACE(phone, '[^0-9], ''')) VIRTUAL,
INDEX idx_phone_num (phone_num);Error number: 1452; Symbol: ER_NO_REFERENCED_ROW_2; SQLSTATE: 23000
Message: Cannot add or update a child row: a foreign key constraint fails (%s)
InnoDB reports this error when you try to add a row but there is no parent row, and a foreign key constraint fails. Add the parent row first.
Или перед вставкой нужно создать запрос на проверку существования такой записи в связующей таблице?
Есть ли разница джоинов этих двух строк, с точки зрения нагрузки на БД?