CREATE PROCEDURE delete_rows()
BEGIN
SELECT @@autocommit INTO @autocommit;
SET SESSION autocommit = ON;
REPEAT
DELETE FROM bigtable WHERE state=2 LIMIT 10000;
SELECT SLEEP(1) INTO @tmp;
UNTIL NOT ROW_COUNT() END REPEAT;
SET SESSION autocommit = @autocommit;
END
SELECT string_agg(concat(substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ', ceil(random() * 26)::integer, 1),
substring('abcdefghijklmnopqrstuvwxyz', ceil(random() * 26)::integer, 1),
substring('0123456789', ceil(random() * 10)::integer, 1),
substring('!#$%&()*+,-./:;<=>?@[]^', ceil(random() * 23)::integer, 1)
),
'' ORDER BY RANDOM()
)
FROM generate_series(1,8);
SELECT UserID, t1.CountMessage-t2.CountMessage
FROM daily_exp_snapshots t1
JOIN daily_exp_snapshots t2 USING (UserID)
WHERE t1.date_added >= CURRENT_DATE
AND t1.date_added < CURRENT_DATE + INTERVAL 1 DAY
AND t2.date_added >= CURRENT_DATE - INTERVAL 1 DAY
AND t2.date_added < CURRENT_DATE
можно порт сделать так чтобы,порт был и tagged (тегированным -определенным vlan-ом) и аксессом ?
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_id
SELECT id, CAST(array_test AS CHAR) array_test
FROM test
WHERE JSON_OVERLAPS(array_test, CAST('[2]' AS JSON));
в каком виде лучше хранить этот массив в бд..