/ Gists / MySql tricks

Gists - MySql tricks

On gists

Prepared statements and another replace tricks

MySql MySql tricks MySql - advanced

example.sql #

/*
 1/ standard way
*/
INSERT IGNORE INTO  product_transport_type (product_id, transport_type_id)
SELECT productId, transportId 
FROM (
    SELECT 
        p.id AS productId,
        t.transportId
    FROM product p
    CROSS JOIN (
        SELECT 6 AS transportId UNION 
        SELECT 7 UNION 
        SELECT 8 UNION 
        SELECT 9 UNION 
        SELECT 10 UNION 
        SELECT 13 UNION 
        SELECT 14 UNION 
        SELECT 15 UNION 
        SELECT 16 UNION 
        SELECT 17 UNION 
        SELECT 18 UNION 
        SELECT 20 UNION 
        SELECT 21 UNION 
        SELECT 23 UNION 
        SELECT 25 UNION 
        SELECT 26 UNION 
        SELECT 27 UNION 
        SELECT 28 UNION 
        SELECT 29
    ) t
    WHERE p.`code` IN (
        7230991, 9705946, 9710628, 9734100, 9734201, 9734500, 9740966, 9740967, 9740980, 
        9744605, 9744606, 9744631, 9744633, 9760507, 9760556, 9762549, 9762550, 9762551, 
        9762569, 9762570, 9762578, 9762639, 9762640, 9762641, 9762653, 9762689, 9762704, 
        9762707, 9763609, 9763617, 9763618, 9779064, 9779065, 9787016, 9787017
    )
) AS ProductsTransportsCombination;




/* 2 find in set - nepodporuje index - asi pomale / pomalejsi */
SET @transport_ids = '6,7,8,9,10,13,14,15,16,17,18,20,21,23,25,26,27,28,29';
SET @product_codes = '7230991,9705946,9710628,9734100,9734201,9734500,9740966,9740967,9740980,9744605,9744606,9744631,9744633,9760507,9760556,9762549,9762550,9762551,9762569,9762570,9762578,9762639,9762640,9762641,9762653,9762689,9762704,9762707,9763609,9763617,9763618,9779064,9779065,9787016,9787017';

INSERT IGNORE INTO product_transport_type (product_id, transport_type_id) 
SELECT p.id AS productId, t.transportId  
FROM product p 
CROSS JOIN ( 
    SELECT 6 AS transportId UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION 
    SELECT 10 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION 
    SELECT 17 UNION SELECT 18 UNION SELECT 20 UNION SELECT 21 UNION SELECT 23 UNION 
    SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 
) t 
WHERE FIND_IN_SET(p.code, @product_codes)
  AND FIND_IN_SET(t.transportId, @transport_ids);
  
  
  
/* 3 prepared stm = rychlejsi kvuli pouzivani indexu */
SET @product_codes = '7230991,9705946,9710628,9734100,9734201,9734500,9740966,9740967,9740980,9744605,9744606,9744631,9744633,9760507,9760556,9762549,9762550,9762551,9762569,9762570,9762578,9762639,9762640,9762641,9762653,9762689,9762704,9762707,9763609,9763617,9763618,9779064,9779065,9787016,9787017';
SET @transport_ids = '6,7,8,9,10,13,14,15,16,17,18,20,21,23,25,26,27,28,29';

SET @sql = CONCAT('
INSERT IGNORE INTO product_transport_type (product_id, transport_type_id) 
SELECT p.id AS productId, t.transportId  
FROM product p 
CROSS JOIN ( 
    SELECT 6 AS transportId UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION 
    SELECT 10 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION 
    SELECT 17 UNION SELECT 18 UNION SELECT 20 UNION SELECT 21 UNION SELECT 23 UNION 
    SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 
) t 
WHERE p.code IN (', @product_codes, ')
');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


/* 3b, state stm, ale sestaveni UNIONu dynamicky */
SET @product_codes = '7230991,9705946,9710628,9734100,9734201,9734500,9740966,9740967,9740980,9744605,9744606,9744631,9744633,9760507,9760556,9762549,9762550,9762551,9762569,9762570,9762578,9762639,9762640,9762641,9762653,9762689,9762704,9762707,9763609,9763617,9763618,9779064,9779065,9787016,9787017';
SET @transport_ids = '6,7,8,9,10,13,14,15,16,17,18,20,21,23,25,26,27,28,29';

-- Vytvoření UNION části pro transport IDs
SET @transport_union = REPLACE(REPLACE(@transport_ids, ',', ' UNION SELECT '), ',', '');
SET @transport_union = CONCAT('SELECT ', @transport_union);

SET @sql = CONCAT('
INSERT IGNORE INTO product_transport_type (product_id, transport_type_id) 
SELECT p.id AS productId, t.transportId  
FROM product p 
CROSS JOIN (', @transport_union, ') t 
WHERE p.code IN (', @product_codes, ')
');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
On gists

Generated columns (Virtual/Stored)

•
MySql MySql tricks MySql - advanced

examples.sql #

-- Základní matematické operace
amount DECIMAL(10,2) GENERATED ALWAYS AS (quantity * price) STORED

-- Práce s textem
full_name VARCHAR(100) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL

-- Použití CASE
status_description VARCHAR(50) GENERATED ALWAYS AS (
    CASE 
        WHEN status = 1 THEN 'Active'
        WHEN status = 0 THEN 'Inactive'
        ELSE 'Unknown'
    END
) VIRTUAL

-- Práce s datumy
age INT GENERATED ALWAYS AS (TIMESTAMPDIFF(YEAR, birth_date, CURRENT_DATE)) VIRTUAL

-- Komplexnější výpočty
total_with_tax DECIMAL(10,2) GENERATED ALWAYS AS (
    CASE
        WHEN tax_rate IS NULL THEN amount
        ELSE amount * (1 + tax_rate/100)
    END
) STORED

-- Formátování a manipulace s textem
formatted_phone VARCHAR(20) GENERATED ALWAYS AS (
    CONCAT('+420 ', SUBSTR(phone, 1, 3), ' ', SUBSTR(phone, 4, 3), ' ', SUBSTR(phone, 7))
) VIRTUAL
On gists

Column check constraint

•
MySql MySql tricks MySql - advanced

examples.sql #

-- CONSTRAINT [constraint_name] CHECK (expression) [ENFORCED | NOT ENFORCED]

/*
ENFORCED - MySQL aktivně kontroluje podmínku při INSERT/UPDATE
NOT ENFORCED - MySQL podmínku nekontroluje, slouží jen jako dokumentace/metadata
*/

-- Kontrola formátu emailu pomocí REGEXP
CONSTRAINT valid_email CHECK (email REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$')

-- Kontrola, že koncová data je později než počáteční
CONSTRAINT valid_date_range CHECK (end_date > start_date)

-- Kontrola že sloupec obsahuje pouze velká písmena
CONSTRAINT uppercase_only CHECK (column_name = UPPER(column_name))

-- Kontrola že věk je v rozumném rozsahu
CONSTRAINT valid_age CHECK (age >= 0 AND age < 150)

-- Kombinace více podmínek pro plat
CONSTRAINT salary_rules CHECK (
    (role = 'Junior' AND salary BETWEEN 30000 AND 50000) OR
    (role = 'Senior' AND salary BETWEEN 60000 AND 120000)
)

-- Kontrola že skóre je buď NULL nebo v rozsahu 0-100
CONSTRAINT valid_score CHECK (score IS NULL OR (score >= 0 AND score <= 100))

-- Kontrola že status je jedna z povolených hodnot
CONSTRAINT valid_status CHECK (
    status IN ('pending', 'approved', 'rejected', 'on_hold')
)

-- Komplexní validace telefonního čísla
CONSTRAINT valid_phone CHECK (
    phone_number REGEXP '^\\+?[1-9][0-9]{7,14}$'
)

-- Kontrola že discount je menší než cena
CONSTRAINT valid_discount CHECK (discount < price)

-- Kontrola že součet procentuálních hodnot nepřesáhne 100
CONSTRAINT percentage_sum CHECK (
    value1 + value2 + value3 <= 100
)


-- Komplexní kontrola hesla (minimální délka, musí obsahovat číslo a velké písmeno)
CONSTRAINT password_strength CHECK (
    LENGTH(password) >= 8 
    AND password REGEXP '[0-9]'
    AND password REGEXP '[A-Z]'
    AND password != username
)

-- Kontrola věku na základě data narození s aktuálním datem
CONSTRAINT valid_birth_date CHECK (
    YEAR(birth_date) >= 1900
    AND birth_date <= CURRENT_DATE
    AND TIMESTAMPDIFF(YEAR, birth_date, CURRENT_DATE) <= 120
)

-- Složitá business logika s CASE
CONSTRAINT complex_pricing CHECK (
    CASE 
        WHEN customer_type = 'VIP' THEN price >= 1000 AND discount <= price * 0.3
        WHEN customer_type = 'Regular' THEN price >= 500 AND discount <= price * 0.1
        WHEN customer_type = 'New' THEN price >= 100 AND discount = 0
        ELSE price >= 0
    END
)

-- Kombinace podmínek pro rezervační systém
CONSTRAINT valid_reservation CHECK (
    (status = 'confirmed' AND payment_id IS NOT NULL AND amount > 0)
    OR (status = 'pending' AND (payment_id IS NULL OR amount = 0))
    OR (status = 'cancelled' AND cancellation_reason IS NOT NULL)
)

-- Kontrola formátu a validity dat
CONSTRAINT valid_document CHECK (
    (doc_type = 'invoice' AND doc_number REGEXP '^INV-[0-9]{6}$')
    OR (doc_type = 'order' AND doc_number REGEXP '^ORD-[0-9]{6}$')
    AND issue_date <= due_date
    AND YEAR(issue_date) >= 2020
)

-- Kontrola závislostí mezi více sloupci
CONSTRAINT project_status_rules CHECK (
    (status = 'completed' AND end_date IS NOT NULL AND progress = 100)
    OR (status = 'in_progress' AND end_date IS NULL AND progress BETWEEN 1 AND 99)
    OR (status = 'planned' AND end_date IS NULL AND progress = 0)
)

-- Sezónní ceník s různými pravidly
CONSTRAINT seasonal_pricing CHECK (
    CASE 
        WHEN MONTH(booking_date) IN (6,7,8) THEN price >= 1000  -- Letní sezóna
        WHEN MONTH(booking_date) IN (12,1,2) THEN price >= 800  -- Zimní sezóna
        WHEN DAYOFWEEK(booking_date) IN (1,7) THEN price >= 700 -- Víkendy
        ELSE price >= 500  -- Mimo sezónu
    END
)

-- Validace adresy
CONSTRAINT valid_address CHECK (
    LENGTH(street) >= 3
    AND LENGTH(city) >= 2
    AND postal_code REGEXP '^[0-9]{5}$'
    AND (
        country = 'CZ' AND phone REGEXP '^\\+420[0-9]{9}$'
        OR 
        country = 'SK' AND phone REGEXP '^\\+421[0-9]{9}$'
    )
)

-- Komplexní kontrola produktu
CONSTRAINT product_rules CHECK (
    CASE product_type
        WHEN 'physical' THEN 
            weight > 0 
            AND dimensions IS NOT NULL 
            AND digital_url IS NULL
        WHEN 'digital' THEN 
            weight = 0 
            AND dimensions IS NULL 
            AND digital_url IS NOT NULL
        WHEN 'service' THEN 
            weight = 0 
            AND dimensions IS NULL 
            AND duration > 0
    END
    AND (
        price > 0
        OR (price = 0 AND is_free_sample = TRUE)
    )
)
On gists

Insert only when @var is true

•
MySql MySql tricks MySql - advanced

insert.sql #

SET @cnt = (SELECT COUNT(1) FROM image_size WHERE folder = '840x1190');

INSERT INTO `image_size` 
    (`id`, `folder`, `quality`, `xsize`, `ysize`, 
    `image_resize_id`, `diagonal`, `watermark__image_id`, 
    `watermark_xsize`, `watermark_ysize`, 
    `watermark_xpos`, `watermark_ypos`, 
    `crop_left`, `crop_top`, `trim`, `grayscale`, 
    `filter_color`, `filter_opacity`)
    
SELECT NULL, '840x1190', 95, 840, 1190, 
       0, NULL, NULL, 
       NULL, NULL, 
       NULL, NULL, 
       NULL, NULL, 0, 0, 
       NULL, NULL
WHERE @cnt = 0;
On gists

sql table from select

MySql MySql tricks MySql - advanced

howto.sql #

-- 1

  SELECT 'Mon' 
    UNION 
    SELECT 'Tue'
    UNION 
    SELECT 'Web'
    UNION 
    SELECT 'Thu'
    UNION 
    SELECT 'Fri'
    UNION 
    SELECT 'Sat'
    UNION 
    SELECT 'Sun';



-- 2
WITH RECURSIVE days_cte AS (
  SELECT 'Mon' AS day_name
  UNION ALL
  SELECT
    CASE day_name
      WHEN 'Mon' THEN 'Tue'
      WHEN 'Tue' THEN 'Wed'
      WHEN 'Wed' THEN 'Thu'
      WHEN 'Thu' THEN 'Fri'
      WHEN 'Fri' THEN 'Sat'
      WHEN 'Sat' THEN 'Sun'
      ELSE 'Mon' -- Pro zajištění cyklického opakování
    END
  FROM days_cte
  WHERE day_name != 'Sun'
)
SELECT day_name FROM days_cte;
On gists

Check column in table if exists

MySql MySql tricks

check-column.sql #

SELECT COUNT(*) INTO @cnt FROM information_schema.columns
WHERE table_schema = DATABASE() AND table_name = 'transport_type' AND column_name = 'measure_distance';

SET @q = IF(@cnt <= 0, 
"ALTER TABLE transport_type ADD COLUMN measure_distance TINYINT(1) NULL DEFAULT NULL COMMENT 'Měřit vzdálenost' AFTER delivery_holiday",
'select \'Column exists\' status');

 PREPARE stmt from @q;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
On gists

SQL mix (all my SQL, huge library)

•
MySql tricks MySql - advanced

mix.sql #

/*
ulozeno jako 1,3,999,765 ...
*/

/*stare spojeni*/

select z.* from 
_zapasy z, _souteze s 
where find_in_set(z.id,s.zapasy) and s.id=1


/* JOIN spojeni, 2 druhy */

select * from _zapasy z join _souteze s on s.id=1 where find_in_set(z.id,s.zapasy)
select * from _zapasy z join _souteze s ON find_in_set(z.id,s.zapasy) WHERE  s.id=1
select * from _zapasy z join _souteze s ON find_in_set(z.id,s.zapasy) AND s.id=1


/* 20 latest unique records */
SELECT 
FROM 
  (
    SELECT 
      sparse, 
      MAX(ts) AS last_update 
    FROM 
      logfile 
    GROUP BY 
      sparse
  ) q 
ORDER BY 
  last_update DESC 
LIMIT



/* rank */

set @type := '', @num := 1;

select type, variety,
   @num := if(@type = type, @num + 1, 1) as row_number,
   @type := type as dummy
from fruits
order by type, variety;

+--------+------------+------------+--------+
| type   | variety    | row_number | dummy  |
+--------+------------+------------+--------+
| apple  | fuji       |          1 | apple  | 
| apple  | gala       |          2 | apple  | 
| apple  | limbertwig |          3 | apple  | 
| cherry | bing       |          1 | cherry | 
| cherry | chelan     |          2 | cherry | 
| orange | navel      |          1 | orange | 
| orange | valencia   |          2 | orange | 
| pear   | bartlett   |          1 | pear   | 
| pear   | bradford   |          2 | pear   | 
+--------+------------+------------+--------+

/* cislovani radku */

-- ve 2 SQL
set @radek=0; 
select @radek:=@radek+1 rownum, t.* FROM tabulka t ORDER BY t.body DESC;

-- by KAJMAN v 1 sql
select @radek:=@radek+1 rownum, t.* from (SELECT @radek:=0) r, tabulka t

/* Nejnovejsi komentare a clanky k nim s limitem */

SELECT clanky.nadpis, com.id, com.datum
FROM clanky, 

  (
  SELECT id, id_cl, datum
  FROM komentare
  ORDER BY id DESC
  )com
  
WHERE clanky.id = com.id_cl
GROUP BY clanky.id
ORDER BY com.datum
LIMIT 10 ;


/* concat subselect s limitem */
select group_concat(uzivatele order by registrace desc)
from (select uzivatele, registrace from uzivatele order by registrace desc limit 0,10) tmp

/* update case ..., update elt/field https://diskuse.jakpsatweb.cz/?action=vthread&forum=28&topic=143350 */

update `dostupnostKniha` join
       (select 1 id, 10 k
        union
        select 2 id, 20 k
        union
        select 3 id, 30 k
        union
        select 4 id, 40 k) dbtmp on `dostupnostKniha`.id = dbtmp.id and `dostupnostKniha`.idDostupnosti = 2
set     `dostupnostKniha`.pocetKusu = dbtmp.k, `dostupnostKniha`.datumUpdate = '$datum'


UPDATE dostupnostKniha
SET    datumUpdate = '$datum', pocetKusu = Elt(Field(id, 1, 2), 10, 20)
WHERE  id IN ( 1, 2 ) and idDostupnosti = 2


/* Náhrada za windows funkce */
SELECT id_student, id_class, grade,
   @student:=CASE WHEN @class <> id_class THEN 0 ELSE @student+1 END AS rn,
   @class:=id_class AS clset
FROM
  (SELECT @student:= -1) s,
  (SELECT @class:= -1) c,
  (SELECT *
   FROM mytable
   ORDER BY id_class, id_student
  ) t
  
  
  /* IN spojeni 2 a vice hodnot */
  select ID_osoby
from   co_si_pucil
where  (ID_polozky,druh) in ((11043,'kniha'),(9443,'film'))
group  by ID_osoby
having count(distinct ID_polozky, druh) = 2 -- pocet hledanych kombinaci


/* lepsi spojeni v subselectu nez normalne pres JOIN */
EXPLAIN SELECT gb . * , hraci.prezdivka
FROM hraci, (

SELECT *
FROM gb2
ORDER BY id DESC
LIMIT 3
)gb
WHERE hraci.id = gb.jmeno

/* limit v poddotaze */
SELECT * FROM (SELECT * FROM `tabulka` ORDER BY `poradi_vlozeni` DESC LIMIT 20) vybrane ORDER BY RAND() LIMIT 10

/* mazani duplicit, vic zpusobu */
delete from dupes
where id not in (select min(id) from dupes group by name)

-- Nezapoměňte si data zazálohovat. Pak jsou obvyklé dva způsoby.
-- 1) vytvořit si dočasný unikátní index, díky ingore se provede i na duplicitními sloupečky a další opakování vyhodí

ALTER IGNORE TABLE tabulka ADD UNIQUE INDEX docasny123 (sloupecek);
ALTER TABLE tabulka DROP INDEX docasny123;

-- 2) použít delete using s propojením tabulky se samo sebou - potřebujete ale nějaký jedinečný sloupeček, 
-- tím si můžete i určit, zda chcete např. ponechat nejstarší nebo nejmladší hodnotu (nejvyšší id jako v příkladu)

DELETE FROM t1 USING tabulka t1, tabulka t2 WHERE t1.sloupecek=t2.sloupecek AND t1.id<t2.id


/* update join */
http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php




mysql> DELETE cds 
    -> FROM cds, artists 
    -> WHERE (cds.artistID = artists.artistID) 
    -> AND (cds.artistID = '3');

To DELETE from both tables:

mysql> DELETE cds, artist 
    -> FROM cds, artist
    -> WHERE (cds.artistID = artists.artistID) 
    -> AND (cds.artistID = '3');
    
==========

mysql> UPDATE cds 
    -> LEFT JOIN artists 
    -> USING (artistID)
    -> SET cds.artists = artists.Artist
    -> WHERE (cds.cdID = '2');


UPDATE videogallery_komentare t1 JOIN videogallery t2 ON t2.code = t1.code
SET t1.videoID = t2.ID


/** tree without with recursive */
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

+-------------+----------------------+--------------+-------+
| lev1        | lev2                 | lev3         | lev4  |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
+-------------+----------------------+--------------+-------+


select l0.catID,
    concat(
      case when l5.catID is null then '' else concat(l5.category, '/') end
    , case when l4.catID is null then '' else concat(l4.category, '/') end
    , case when l3.catID is null then '' else concat(l3.category, '/') end
    , case when l2.catID is null then '' else concat(l2.category, '/') end
    , case when l1.catID is null then '' else concat(l1.category, '/') end
    , l0.category)
from catcat l0
left join catcat l1 on l0.parentID=l1.catID
left join catcat l2 on l1.parentID=l2.catID
left join catcat l3 on l2.parentID=l3.catID
left join catcat l4 on l3.parentID=l4.catID
left join catcat l5 on l4.parentID=l5.catID


/* WEBFAQ, poradi, statistiky */
SET @poradi:=0,@poradib:=0;
SELECT @poradi:=@poradi+1, @poradi as poradi, IF(id=201, @poradib:=@poradi, 0), id, name FROM table ORDER BY `name` DESC;
SELECT @poradib;
-- kajman ;)  
select count(*)+1 poradi from tabulka t where t.rok=2008 and t.golu>(select j.golu where j.rok=2008 and j.hrac=‚Jágr‘)
select count(*)+1 from (select sum(gol) golu group by hrac having golu>$goly_jagra)


/* CASE or ELT */
UPDATE `table` SET uid = ELT(id, 2952, 4925, 1592) WHERE id IN (1,2,3)
-- or
UPDATE `table` SET `uid` = CASE
    WHEN id = 1 THEN 2952
    WHEN id = 2 THEN 4925
    WHEN id = 3 THEN 1592
    ELSE `uid`
    END
WHERE id  in (1,2,3)


/* Nejvyssi clanky v kateg. */
select * from clanky where (kategorie, datum) in (select kategorie, max(datum) from clanky group by kategorie)

SELECT 
t1.id, 
t1.nadpis,
t2.datum,
t1.datum,
t2.kategorie
FROM clanky t1
JOIN  ( SELECT t2.kategorie, MAX(t2.datum) datum FROM clanky t2 GROUP BY t2.kategorie) t2
ON t1.kategorie = t2.kategorie
AND t1.datum = t2.datum


select * from clanky t1
where t1.id in (select max(t2.id) id from clanky t2 group by t2.kategorie)


/* rank, fotbalova tabulka */
http://diskuse.jakpsatweb.cz/index.php?action=vthread&topic=97602&forum=28&page=-1



1/ uzivatelske promenne


SET @minule := 0;
SET @poradi := 0;
SET @radek := 1;
UPDATE 
    poradi, 
    (
        SELECT 
            tym,
            body,
            @poradi := IF(@minule = body, @poradi, @radek) AS poradi,
            @radek := @radek + 1 AS radek,
            @minule := body AS tmp
        FROM poradi
        ORDER BY body DESC 
    ) AS vypocet
SET poradi.poradi = vypocet.poradi 
WHERE poradi.tym = vypocet.tym;




2/ Kajman , nejdrive nastavit pozice klasicky 1,2,3, .. a pak vybrat ty co jsou vicekrat a znova update 

update tabulka,
       (select body1, body2, body3, min(poradi) poradi
        from   tabulka
        group  by body1, body2, body3
        having count(*) > 1) as vypocet
set    tabulka.poradi = vypocet.poradi
where  tabulka.body1 = vypocet.body1
       and tabulka.body2 = vypocet.body2
       and tabulka.body3 = vypocet.body3



3/ za behu korelovany dotaz 

SELECT SQL_NO_CACHE
   (SELECT COUNT(*) + 1 FROM poradi as t1 WHERE t1.body > t2.body) AS rank, t2.tym, t2.body
    FROM poradi as t2


4/ Moje procedura :)

CREATE  PROCEDURE `SP_poradi`()
BEGIN

DECLARE s_poradi INT(5);
DECLARE s_body INT(5);
DECLARE temp_body INT(5) DEFAULT 0;
DECLARE s_tym VARCHAR(50);
DECLARE my_rank INT(5) DEFAULT 0;
DECLARE done TINYINT(1) DEFAULT 0;

DECLARE position TINYINT DEFAULT 0;

DECLARE i INT;

DECLARE rank CURSOR FOR
SELECT tym, body, poradi FROM poradi
ORDER BY body DESC;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

SET i = 0;
OPEN rank;
rank_loop:  LOOP

FETCH rank INTO s_tym, s_body, s_poradi;
IF done THEN LEAVE rank_loop; END IF;

SET i=i+1;

IF (temp_body <> s_body)
  THEN SET position = i;
ELSE
  SET position = position;
END IF;


UPDATE poradi SET poradi = position WHERE tym = s_tym;

SET temp_body = s_body;  


END LOOP rank_loop;
CLOSE rank;

END


/* poradi nad a pod konkretnim poradim */
SELECT a . * 
FROM tabulky a, tabulky b
WHERE b.tym =11  /* nahodny tym, ktery ma pozici 6 */
AND (
a.poradi +1 = b.poradi
OR a.poradi = b.poradi
OR a.poradi = b.poradi +1
)
ORDER BY poradi



/* rank with FIND_IN_SET */
Id  Name    Score
1   Ida     100
2   Boo     58
3   Lala    88
4   Bash    102
5   Assem   99

SELECT id, name, score, FIND_IN_SET( score, (
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM scores )
) AS rank
FROM scores

id name  score rank
1  Ida   100   2
2  Boo    58   5
3  Lala   88   4
4  Bash  102   1
5  Assem  99   3

Getting a single person score:

SELECT id, name, score, FIND_IN_SET( score, (    
SELECT GROUP_CONCAT( score
ORDER BY score DESC ) 
FROM scores )
) AS rank
FROM scores
WHERE name =  'Assem'
Gives this result:

id name score rank
5 Assem 99 3


/* GW ladder, rank similar values */
# za vcerejsek
SELECT 
  * 
FROM 
  vysledky 
WHERE 
  date_format(datum, '%Y-%m-%d') = (
    CURDATE() - INTERVAL 1 DAY
  ) # za dnesek
SELECT 
  * 
FROM 
  vysledky 
WHERE 
  date_format(datum, '%Y-%m-%d') = CURDATE() # starsi nez 2 dny od dneska
SELECT 
  * 
FROM 
  vysledky 
WHERE 
  date_format(datum, '%Y-%m-%d') < (
    CURDATE() - INTERVAL 1 DAY
  ) 
order by 
  id_zapasu DESC 
limit 
  10 # vytah 
SELECT 
  count(*) + 1 
FROM 
  `tabulka_tymu_ladder` l 
  LEFT JOIN `tabulka_tymu_ladder` a ON (
    a.id_hrace = 1 
    AND a.id_souteze = 7
  ) 
WHERE 
  l.id_souteze = 7 
  AND (
    l.koeficient > a.koeficient 
    OR (
      l.koeficient = a.koeficient 
      AND (
        l.pts > a.pts 
        OR (
          l.pts = a.pts 
          AND (
            (l.gf - l.ga) > (a.gf - a.ga) 
            OR (
              (l.gf - l.ga) = (a.gf - a.ga) 
              AND (
                l.pz > a.pz 
                OR (
                  l.pz = a.pz 
                  AND l.gf > a.gf
                )
              )
            )
          )
        )
      )
    )
  ) 
SELECT 
  count(*) + 1 
FROM 
  `tabulka_tymu_ladder` l, 
  `tabulka_tymu_ladder` a 
WHERE 
  a.id_hrace = 1 
  AND a.id_souteze = 8 
  AND l.id_souteze = 8 
  AND (
    l.koeficient > a.koeficient 
    OR (
      l.koeficient = a.koeficient 
      AND (
        l.pts > a.pts 
        OR (
          l.pts = a.pts 
          AND (
            (l.gf - l.ga) > (a.gf - a.ga) 
            OR (
              (l.gf - l.ga) = (a.gf - a.ga) 
              AND (
                l.pz > a.pz 
                OR (
                  l.pz = a.pz 
                  AND l.gf > a.gf
                )
              )
            )
          )
        )
      )
    )
  )


/* prioritni order by */
SELECT * FROM staty order by (nazev='Česká republika') DESC,
(nazev='Slovenská republika') DESC, nazev 

/* vnoreny dotaz v insert select */
INSERT INTO tab1 (id,desription) VALUES((SELECT id FROM tab2 WHERE name='neco'),'blablalba')

/*
https://stackoverflow.com/questions/12992793/efficient-ways-to-calculate-ranks-for-over-350-000-users?noredirect=1&lq=1
*/ 
UPDATE `Members`
SET `RankNum` = (@newRank := @newRank + 1)
ORDER BY `Rank` DESC, `Id` ASC, @newRank := 0


/*
http://explainextended.com/2009/04/07/keeping-rows/
mazani s limitem
*/
DELETE  d.*
FROM    t_deleter d
LEFT JOIN
        (
        SELECT  id
        FROM    t_deleter
        LIMIT   10
        ) q
ON      d.id = q.id
WHERE   q.id IS NULL


/* ANY, ALLL */
Zkuste si udělat tabulku tabulka se sloupcem sloupec a v ní hodnoty 1 a 2.

select * from tabulka where sloupec >= any (select sloupec from tabulka)
vrátí oba řádky ale
select * from tabulka where sloupec >= all (select sloupec from tabulka)
vrátí jen řádek s dvojkou, protože 1 není větší než všechny hodnoty z poddotazu


/* Nalezení položek mající všechny hledané vlastnosti */
select id_polozky
from   vazebni_tabulka
where  id_vlastnosti in (1, 5, 7)
group  by id_polozky
having count(distinct id_vlastnosti) = 3 -- počet jedinečných hodnot ze závorky


-- update when then
UPDATE categories
    SET display_order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END,
    title = CASE id
        WHEN 1 THEN 'New Title 1'
        WHEN 2 THEN 'New Title 2'
        WHEN 3 THEN 'New Title 3'
    END
WHERE id IN (1,2,3)


-- Multiple updates
-- with insert or duplicate key
INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12)
ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2);

-- or with union
UPDATE my_table m
JOIN (
    SELECT 1 as id, 10 as _col1, 20 as _col2
    UNION ALL
    SELECT 2, 5, 10
    UNION ALL
    SELECT 3, 15, 30
) vals ON m.id = vals.id
SET col1 = _col1, col2 = __col2;

-- next solutions
UPDATE `table` SET `uid` = CASE
    WHEN id = 1 THEN 2952
    WHEN id = 2 THEN 4925
    WHEN id = 3 THEN 1592
    ELSE `uid`
    END
WHERE id  in (1,2,3)

UPDATE table SET uid = ELT(id, 2952, 4925, 1592) WHERE id IN (1,2,3)


/* left join with correlated and dynamic cond */
-- https://explainextended.com/2010/10/20/correlated-left-join-in-a-subquery/
SELECT  g.id, g.name, GROUP_CONCAT(p.name SEPARATOR ', ') AS players
FROM    game g
LEFT JOIN
player_team pt
ON      g.type = 'team'
AND pt.team = g.player1
JOIN    player p
ON      p.id = CASE g.type WHEN 'player' THEN g.player1 WHEN 'team' THEN pt.player END
GROUP BY
g.id
LIMIT 50


/* El dragon stats */
SELECT tmp.prezdivka, tmp.prijmeni, tmp.pocet_vysledky, tmp.pocet_clanky, tmp.pocet_forum, (
tmp.pocet_forum + tmp.pocet_vysledky + tmp.pocet_clanky
) AS CELKEM
FROM (

SELECT a.prezdivka, a.prijmeni, (

SELECT COUNT( * ) 
FROM forum_prispevky
WHERE registrovany_uzivatel = a.ID
) AS pocet_forum, (

SELECT COUNT( * ) 
FROM komentare_clanky
WHERE registrovany_uzivatel = a.ID
) AS pocet_clanky, (

SELECT COUNT( * ) 
FROM komentare_vysledky
WHERE registrovany_uzivatel = a.ID
) AS pocet_vysledky
FROM uzivatele AS a
) AS tmp

ORDER BY CELKEM DESC
On gists

Advanced CTE - Tree

MySql MySql tricks MySql - advanced

tree.sql #

https://akki.ca/blog/mysql-adjacency-list-model-for-hierarchical-data-using-cte/


CREATE TABLE category (
  id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL,
  parent_id int(10) UNSIGNED DEFAULT NULL,
  is_active int(1) UNSIGNED DEFAULT 1,
  PRIMARY KEY (id),
  FOREIGN KEY (parent_id) REFERENCES category (id)
    ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO category (name, parent_id) VALUES ("Electronics", null);
INSERT INTO category (name, parent_id) VALUES ("TV", 1);
INSERT INTO category (name, parent_id) VALUES ("Smart", 2);
INSERT INTO category (name, parent_id) VALUES ("4K Ultra HD", 2);
INSERT INTO category (name, parent_id) VALUES ("Curved", 2);
INSERT INTO category (name, parent_id) VALUES ("Camera", 1);
INSERT INTO category (name, parent_id) VALUES ("Computer", 1);
INSERT INTO category (name, parent_id) VALUES ("Desktop", 7);
INSERT INTO category (name, parent_id) VALUES ("Laptops", 7);
INSERT INTO category (name, parent_id) VALUES ("Work", 9);
INSERT INTO category (name, parent_id) VALUES ("Travel", 9);
INSERT INTO category (name, parent_id) VALUES ("All Around", 9);
INSERT INTO category (name, parent_id) VALUES ("Gaming", 9);
INSERT INTO category (name, parent_id) VALUES ("Tablet", 7);


/* Tree */
WITH RECURSIVE shoppingCategories AS
(
  SELECT id, name, parent_id, 1 AS depth, name AS path
    FROM category
    WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, sc.depth + 1, CONCAT(sc.path, ' > ', c.name)
    FROM shoppingCategories AS sc 
      JOIN category AS c ON sc.id = c.parent_id
)
SELECT * FROM shoppingCategories;


+------+-------------+-----------+-------+-----------------------------------------------+
| id   | name        | parent_id | depth | path                                          |
+------+-------------+-----------+-------+-----------------------------------------------+
|    1 | Electronics |      NULL |     1 | Electronics                                   |
|    2 | TV          |         1 |     2 | Electronics > TV                              |
|    6 | Camera      |         1 |     2 | Electronics > Camera                          |
|    7 | Computer    |         1 |     2 | Electronics > Computer                        |
|    3 | Smart       |         2 |     3 | Electronics > TV > Smart                      |
|    4 | 4K Ultra HD |         2 |     3 | Electronics > TV > 4K Ultra HD                |
|    5 | Curved      |         2 |     3 | Electronics > TV > Curved                     |
|    8 | Desktop     |         7 |     3 | Electronics > Computer > Desktop              |
|    9 | Laptops     |         7 |     3 | Electronics > Computer > Laptops              |
|   14 | Tablet      |         7 |     3 | Electronics > Computer > Tablet               |
|   10 | Work        |         9 |     4 | Electronics > Computer > Laptops > Work       |
|   11 | Travel      |         9 |     4 | Electronics > Computer > Laptops > Travel     |
|   12 | All Around  |         9 |     4 | Electronics > Computer > Laptops > All Around |
|   13 | Gaming      |         9 |     4 | Electronics > Computer > Laptops > Gaming     |
+------+-------------+-----------+-------+-----------------------------------------------+






/* ancestors, tree */
SET @catID = 9;
WITH RECURSIVE
  ancestorCategories  AS
  (
    SELECT id, name, parent_id, 1 AS depth
      FROM category
      WHERE id = @catID
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ac.depth - 1
      FROM ancestorCategories AS ac
        JOIN category AS c ON ac.parent_id = c.id
  ),
  ancestorDeptOneCategories  AS
  (
    SELECT id, name, parent_id, 1 AS depth
      FROM category
      WHERE parent_id IS null
    UNION ALL
    SELECT c.id, c.name, c.parent_id, adoc.depth + 1
      FROM ancestorDeptOneCategories AS adoc
        JOIN ancestorCategories ac ON ac.id = adoc.id
        JOIN category AS c ON adoc.id = c.parent_id
  )
SELECT * FROM ancestorDeptOneCategories
ORDER BY depth;


+------+-------------+-----------+-------+
| id   | name        | parent_id | depth |
+------+-------------+-----------+-------+
|    1 | Electronics |      NULL |     1 |
|    2 | TV          |         1 |     2 |
|    6 | Camera      |         1 |     2 |
|    7 | Computer    |         1 |     2 |
|    8 | Desktop     |         7 |     3 |
|    9 | Laptops     |         7 |     3 |
|   14 | Tablet      |         7 |     3 |
|   10 | Work        |         9 |     4 |
|   11 | Travel      |         9 |     4 |
|   12 | All Around  |         9 |     4 |
|   13 | Gaming      |         9 |     4 |
+------+-------------+-----------+-------+
On gists

With recursive - CTE

•
MySql MySql tricks MySql - advanced

cte.sql #

WITH RecursiveSequence AS (
    SELECT 1 AS number
    UNION ALL
    SELECT number + 1
    FROM RecursiveSequence
    WHERE number < 10
)
SELECT * FROM RecursiveSequence;

+--------+
| number |
+--------+
| 1      |
| 2      |
| 3      |
| ...    |
| 10     |
+--------+


/*
 numbers, days interval
*/

WITH RECURSIVE cte(n, dt)  AS  (
	SELECT 10, '2023-01-01'
	UNION ALL
	SELECT n + 10, dt + INTERVAL 1 day 
	FROM cte WHERE n < 1000
)

/*
nebo takto kdyz nechci uvadet za cte(parametry): 

WITH RECURSIVE cte AS (
SELECT 10 AS n, '2023-01-01' AS dt
UNION ALL
SELECT n + 10, dt + INTERVAL 1 day
FROM cte WHERE n < 1000
)

*/


SELECT * FROM cte;


/* tree, path, level */

# create the table
CREATE TABLE orgchart(
id INT PRIMARY KEY,
name VARCHAR(20),
role VARCHAR(20),
manager_id INT,
FOREIGN KEY (manager_id) REFERENCES orgchart(id));
 
# insert the rows
INSERT INTO orgchart VALUES(1,'Matthew','CEO',NULL), 
(2,'Caroline','CFO',1),(3,'Tom','CTO',1),
(4,'Sam','Treasurer',2),(5,'Ann','Controller',2),
(6,'Anthony','Dev Director',3),(7,'Lousie','Sys Admin',3),
(8,'Travis','Senior DBA',3),(9,'John','Developer',6),
(10,'Jennifer','Developer',6),(11,'Maria','Junior DBA',8);
 
# let's see the table, The CEO has no manager, so the manager_id is set to NULL
SELECT * FROM orgchat;
+----+----------+--------------+------------+
| id | name     | role         | manager_id |
+----+----------+--------------+------------+
|  1 | Matthew  | CEO          |       NULL |
|  2 | Caroline | CFO          |          1 |
|  3 | Tom      | CTO          |          1 |
|  4 | Sam      | Treasurer    |          2 |
|  5 | Ann      | Controller   |          2 |
|  6 | Anthony  | Dev Director |          3 |
|  7 | Lousie   | Sys Admin    |          3 |
|  8 | Travis   | Senior DBA   |          3 |
|  9 | John     | Developer    |          6 |
| 10 | Jennifer | Developer    |          6 |
| 11 | Maria    | Junior DBA   |          8 |
+----+----------+--------------+------------+
 


WITH RECURSIVE reporting_chain(id, name, PATH, PATH2,  level) AS 
( 
       SELECT 
		 	id, name, 
			CAST(name AS CHAR(100)), 
			CAST(name AS CHAR(100)), 
			1  
       FROM orgchart 
		 WHERE manager_id IS NULL 
       
		 UNION ALL 
       
		 SELECT 
			oc.id, 
			oc.name, 
			CONCAT(rc.path,' -> ',oc.name), 
			CONCAT(oc.name,' -> ', rc.path2), 
			rc.level+1 
      FROM reporting_chain rc 
		JOIN orgchart oc ON rc.id=oc.manager_id
) 
   
SELECT * FROM reporting_chain ORDER BY level;


+------+----------+---------------------------------------+-------+
| id   | name     | path                                  | level |
+------+----------+---------------------------------------+-------+
|    1 | Matthew  | Matthew                               |     1 |
|    2 | Caroline | Matthew -> Caroline                   |     2 |
|    3 | Tom      | Matthew -> Tom                        |     2 |
|    4 | Sam      | Matthew -> Caroline -> Sam            |     3 |
|    5 | Ann      | Matthew -> Caroline -> Ann            |     3 |
|    6 | Anthony  | Matthew -> Tom -> Anthony             |     3 |
|    7 | Lousie   | Matthew -> Tom -> Lousie              |     3 |
|    8 | Travis   | Matthew -> Tom -> Travis              |     3 |
|    9 | John     | Matthew -> Tom -> Anthony -> John     |     4 |
|   10 | Jennifer | Matthew -> Tom -> Anthony -> Jennifer |     4 |
|   11 | Maria    | Matthew -> Tom -> Travis -> Maria     |     4 |
+------+----------+---------------------------------------+-------+




/* node paths */
WITH RECURSIVE
cte ( node, path )
AS
( SELECT node, cast ( 1 as char(30) )
FROM bst WHERE parent IS NULL
UNION ALL
SELECT bst.node, CONCAT ( cte.path, '-->', bst.node )
FROM cte JOIN bst ON cte.node = bst.parent
)
SELECT * FROM cte ORDER BY node;


/* next tree */
CREATE TABLE
    categories (
        id int,
        cat_name varchar(100),
        parent_category_id int DEFAULT NULL
    );

INSERT INTO
    categories
VALUES
    (1, 'Mens', NULL),
    (2, 'Tops', 1),
    (3, 'Jerseys', 2),
    (4, 'England', 3);
    
    
    WITH RECURSIVE
    category_tree AS (
        SELECT
            id,
            cat_name,
            parent_category_id,
            cat_name AS full_name
        FROM
            categories
        WHERE
            parent_category_id IS NULL
        UNION ALL
        SELECT
            c.id,
            c.cat_name,
            c.parent_category_id,
            CONCAT (ct.full_name, ' > ', c.cat_name)
        FROM
            categories c
            JOIN category_tree ct ON c.parent_category_id = ct.id
    )
SELECT
    full_name
FROM
    category_tree;
On gists

MySQL can't specify target table for update in FROM clause

MySql MySql tricks MySql - advanced

super-workaround.sql #

/*
  does not work!
*/

update Users set Valid = 0
where Id in (
  select Id from Users where Id < 100
)


/*
  workaround
  + one subselect more like a wrapper ;-)
*/

update Users set Valid = 0
where Id in (
  select Id from (
    select Id from Users where Id < 100
  ) as t
)