/
Gists
/
MySql tricks
Gists - MySql tricks
On gists
MySql
MySql tricks
Functions - examples
Functions.sql #
Basic MySQL CREATE FUNCTION statement
A very basic CREATE FUNCTION example which will produced the famed 'Hello World' output:
DELIMITER $$
CREATE FUNCTION hello_world()
RETURNS TEXT
LANGUAGE SQL
BEGIN
RETURN 'Hello World';
END;
$$
DELIMITER ;
Execute this function as follows:
mysql> SELECT hello_world();
+---------------+
| hello_world() |
+---------------+
| Hello World |
+---------------+
1 row in set (0.00 sec)
MySQL function with a parameter
Customize your 'Hello World' output with input from a parameter:
DROP FUNCTION IF EXISTS hello_world;
DELIMITER $$
CREATE FUNCTION hello_world(addressee TEXT)
RETURNS TEXT
LANGUAGE SQL -- This element is optional and will be omitted from subsequent examples
BEGIN
RETURN CONCAT('Hello ', addressee);
END;
$$
DELIMITER ;
Execute as follows:
mysql> SELECT hello_world('Earth');
+----------------------+
| hello_world('Earth') |
+----------------------+
| Hello Earth |
+----------------------+
1 row in set (0.00 sec)
MySQL function with a local variable
Use a local variable to perform calculations inside your function:
DROP FUNCTION IF EXISTS hello_world;
DELIMITER $$
CREATE FUNCTION hello_world(addressee TEXT)
RETURNS TEXT
BEGIN
DECLARE strlen INT;
SET strlen = LENGTH(addressee);
RETURN CONCAT('Hello ', addressee, ' - your parameter has ', strlen, ' characters');
END;
$$
DELIMITER ;
Output:
mysql> SELECT hello_world('Earth');
+-----------------------------------------------+
| hello_world('Earth') |
+-----------------------------------------------+
| Hello Earth - your parameter has 5 characters |
+-----------------------------------------------+
1 row in set (0.00 sec)
MySQL function with a loop
DROP FUNCTION IF EXISTS looptest;
DELIMITER $$
CREATE FUNCTION looptest()
RETURNS INT READS SQL DATA
BEGIN
DECLARE v_total INT;
SET v_total = 0;
count_loop: LOOP
SET v_total = v_total + 1;
IF v_total = 10 THEN
LEAVE count_loop;
END IF;
END LOOP;
RETURN v_total;
END;
$$
DELIMITER ;
MySQL function with a loop and cursor
It is possible - if somewhat inelegant - to iterate through a CURSOR using a loop:
DROP FUNCTION IF EXISTS looptest;
DELIMITER $$
CREATE FUNCTION looptest()
RETURNS INT READS SQL DATA
BEGIN
DECLARE v_total INT;
DECLARE v_counter INT;
DECLARE done INT DEFAULT FALSE;
DECLARE csr CURSOR FOR
SELECT counter FROM items;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET v_total = 0;
OPEN csr;
read_loop: LOOP
FETCH csr INTO v_counter;
IF done THEN
LEAVE read_loop;
END IF;
SET v_total = v_total + v_counter;
END LOOP;
CLOSE csr;
RETURN v_total;
END;
$$
DELIMITER ;
On gists
MySql
MySql tricks
Dense rank / rank (joins)
dense.sql #
SELECT COUNT(id) + 1 AS rank, COUNT(DISTINCT age) + 1 AS dense_rank
FROM testdata
WHERE gender = (SELECT gender FROM testdata WHERE id = 6)
AND age < (SELECT age FROM testdata WHERE id = 6)
SELECT testdata.id, COUNT(lesser.id) + 1 AS rank, COUNT(DISTINCT lesser.age) + 1 AS dense_rank
FROM testdata
LEFT JOIN testdata AS lesser ON lesser.age < testdata.age AND lesser.gender = testdata.gender
GROUP BY testdata.id
SELECT a.first_name,
a.age,
a.gender,
count(b.age)+1 as rank
FROM person a left join person b on a.age>b.age and a.gender=b.gender
group by a.first_name,
a.age,
a.gender
On gists
MySql
MySql tricks
Ranks - many cases
ranks.sql #
DROP TABLE IF EXISTS users;
CREATE TABLE users (user_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), start_date DATE, team_id INT);
INSERT INTO users (name, start_date, team_id) VALUES ('Matt', '2017-01-01', 1);
INSERT INTO users (name, start_date, team_id) VALUES ('John', '2017-01-02', 2);
INSERT INTO users (name, start_date, team_id) VALUES ('Sara', '2017-01-02', 2);
INSERT INTO users (name, start_date, team_id) VALUES ('Tim', '2017-01-02', 3);
INSERT INTO users (name, start_date, team_id) VALUES ('Bob', '2017-01-03', 3);
INSERT INTO users (name, start_date, team_id) VALUES ('Bill', '2017-01-04', 3);
INSERT INTO users (name, start_date, team_id) VALUES ('Kathy', '2017-01-04', 3);
INSERT INTO users (name, start_date, team_id) VALUES ('Anne', '2017-01-05', 3);
+---------+-------+------------+---------+
| user_id | name | start_date | team_id |
+---------+-------+------------+---------+
| 1 | Matt | 2017-01-01 | 1 |
| 2 | John | 2017-01-02 | 2 |
| 3 | Sara | 2017-01-02 | 2 |
| 4 | Tim | 2017-01-02 | 3 |
| 5 | Bob | 2017-01-03 | 3 |
| 6 | Bill | 2017-01-04 | 3 |
| 7 | Kathy | 2017-01-04 | 3 |
| 8 | Anne | 2017-01-05 | 3 |
+---------+-------+------------+---------+
# Ranked by start date
SELECT *
FROM users
ORDER by start_date ASC
# Ranked by start date with ties broken by user id
SELECT *
FROM users
ORDER by start_date ASC, user_id ASC
# First employee by start date with ties broken by user id
SELECT *
FROM users
ORDER by start_date ASC, user_id ASC
LIMIT 1
# First employee by start date with ties
SELECT *
FROM users
WHERE start_date = (SELECT MIN(start_date) FROM users);
# Second employee by start date with ties broken by user id
SELECT *
FROM users
ORDER by start_date ASC, user_id ASC
LIMIT 1
OFFSET 1
# Second employee by start date with ties
SELECT *
FROM users
WHERE start_date = (
SELECT DISTINCT start_date
FROM users
ORDER BY start_date ASC
LIMIT 1
OFFSET 1
)
# Ranked by start date using variable
SET @rank := 0;
SELECT
*,
@rank := @rank + 1 AS rank
FROM users
ORDER BY start_date ASC
+---------+-------+------------+---------+------+
| user_id | name | start_date | team_id | rank |
+---------+-------+------------+---------+------+
| 1 | Matt | 2017-01-01 | 1 | 1 |
| 2 | John | 2017-01-02 | 2 | 2 |
| 3 | Sara | 2017-01-02 | 2 | 3 |
| 4 | Tim | 2017-01-02 | 3 | 4 |
| 5 | Bob | 2017-01-03 | 3 | 5 |
| 6 | Bill | 2017-01-04 | 3 | 6 |
| 7 | Kathy | 2017-01-04 | 3 | 7 |
| 8 | Anne | 2017-01-05 | 3 | 8 |
+---------+-------+------------+---------+------+
# Ranked by start date using a variable
# Based on this Stack Overflow comment:
SELECT
*,
@rank := @rank + 1 AS rank
FROM users, (SELECT @rank := 0) r
ORDER BY start_date ASC
+---------+-------+------------+---------+------------+------+
| user_id | name | start_date | team_id | @rank := 0 | rank |
+---------+-------+------------+---------+------------+------+
| 1 | Matt | 2017-01-01 | 1 | 0 | 1 |
| 2 | John | 2017-01-02 | 2 | 0 | 2 |
| 3 | Sara | 2017-01-02 | 2 | 0 | 3 |
| 4 | Tim | 2017-01-02 | 3 | 0 | 4 |
| 5 | Bob | 2017-01-03 | 3 | 0 | 5 |
| 6 | Bill | 2017-01-04 | 3 | 0 | 6 |
| 7 | Kathy | 2017-01-04 | 3 | 0 | 7 |
| 8 | Anne | 2017-01-05 | 3 | 0 | 8 |
+---------+-------+------------+---------+------------+------+
# First employee by start date using by setting a variable
SET @rank := 0;
SELECT *
FROM (
SELECT
*,
@rank := @rank + 1 AS rank
FROM users
ORDER BY start_date ASC
) ranked
WHERE rank = 1
# Ranked by start date with ties
# Based on this Stack Overflow comment:
SET @prev_start_date = NULL;
SET @rank := 0;
SELECT
*,
CASE
WHEN @prev_start_date = start_date THEN @rank
-- Note that the assignment here will always be true
WHEN @prev_start_date := start_date THEN @rank := @rank + 1
END AS rank
FROM users
ORDER BY start_date ASC
+---------+-------+------------+---------+------+
| user_id | name | start_date | team_id | rank |
+---------+-------+------------+---------+------+
| 1 | Matt | 2017-01-01 | 1 | 1 |
| 2 | John | 2017-01-02 | 2 | 2 |
| 3 | Sara | 2017-01-02 | 2 | 2 |
| 4 | Tim | 2017-01-02 | 3 | 2 |
| 5 | Bob | 2017-01-03 | 3 | 3 |
| 6 | Bill | 2017-01-04 | 3 | 4 |
| 7 | Kathy | 2017-01-04 | 3 | 4 |
| 8 | Anne | 2017-01-05 | 3 | 5 |
+---------+-------+------------+---------+------+
# Ranked by user id within each team
SELECT
a.*,
COUNT(*) AS rank
FROM users a
INNER JOIN users b
ON a.team_id = b.team_id AND a.user_id >= b.user_id
GROUP BY a.team_id, a.user_id
# or, based on this Stack Overflow comment:
SELECT
a.*,
(
SELECT COUNT(*)
FROM users b
WHERE a.team_id = b.team_id AND a.user_id >= b.user_id
) AS ranked
FROM users a
+---------+-------+------------+---------+--------+
| user_id | name | start_date | team_id | ranked |
+---------+-------+------------+---------+--------+
| 1 | Matt | 2017-01-01 | 1 | 1 |
| 2 | John | 2017-01-02 | 2 | 1 |
| 3 | Sara | 2017-01-02 | 2 | 2 |
| 4 | Tim | 2017-01-02 | 3 | 1 |
| 5 | Bob | 2017-01-03 | 3 | 2 |
| 6 | Bill | 2017-01-04 | 3 | 3 |
| 7 | Kathy | 2017-01-04 | 3 | 4 |
| 8 | Anne | 2017-01-05 | 3 | 5 |
+---------+-------+------------+---------+--------+
# Note that both of these techniques require that there be a column without duplicates that we can rank on within the partition. For example, we can’t use start_date due to the duplicates within team 2 (2017-01-02) and team 3 (2017-01-14):
SELECT
a.*,
COUNT(*) AS rank
FROM users a
INNER JOIN users b
ON a.team_id = b.team_id AND a.start_date >= b.start_date
GROUP BY a.team_id, a.user_id
+---------+-------+------------+---------+------+
| user_id | name | start_date | team_id | rank |
+---------+-------+------------+---------+------+
| 1 | Matt | 2017-01-01 | 1 | 1 |
| 2 | John | 2017-01-02 | 2 | 2 |
| 3 | Sara | 2017-01-02 | 2 | 2 |
| 4 | Tim | 2017-01-02 | 3 | 1 |
| 5 | Bob | 2017-01-03 | 3 | 2 |
| 6 | Bill | 2017-01-04 | 3 | 4 |
| 7 | Kathy | 2017-01-04 | 3 | 4 |
| 8 | Anne | 2017-01-05 | 3 | 5 |
+---------+-------+------------+---------+------+
# Return the last person to join within each team based on user id
# Based on this Stack Overflow comment:
SELECT a.*
FROM users a
LEFT JOIN users b
ON a.team_id = b.team_id AND a.user_id < b.user_id
WHERE b.team_id IS NULL
or
SELECT a.*
FROM users a
WHERE user_id IN (
SELECT MAX(user_id)
FROM users
GROUP BY team_id
)
+---------+------+------------+---------+
| user_id | name | start_date | team_id |
+---------+------+------------+---------+
| 1 | Matt | 2017-01-01 | 1 |
| 3 | Sara | 2017-01-02 | 2 |
| 8 | Anne | 2017-01-05 | 3 |
+---------+------+------------+---------+
# Return the last people to join within each team based on start date
SELECT a.*
FROM users a
LEFT JOIN users b
ON a.team_id = b.team_id AND a.start_date < b.start_date
WHERE b.team_id IS NULL
# or, based on this groupwise max post:
SELECT a.*
FROM users a
INNER JOIN (
SELECT team_id, MAX(start_date) AS max_start_date
FROM users b
GROUP BY team_id
) max_start_dates
ON a.team_id = max_start_dates.team_id AND a.start_date = max_start_dates.max_start_date
+---------+------+------------+---------+
| user_id | name | start_date | team_id |
+---------+------+------------+---------+
| 1 | Matt | 2017-01-01 | 1 |
| 2 | John | 2017-01-02 | 2 |
| 3 | Sara | 2017-01-02 | 2 |
| 8 | Anne | 2017-01-05 | 3 |
+---------+------+------------+---------+
# Ranked with gaps
# Based on this Stack Overflow comment:
SELECT
user_id,
name,
start_date,
team_id,
rank
FROM (
SELECT
*,
IF(start_date = @_last_start_date, @cur_rank := @cur_rank, @cur_rank := @_sequence) AS rank,
@_sequence := @_sequence + 1,
@_last_start_date := start_date
FROM users, (SELECT @cur_rank := 1, @_sequence := 1, @_last_start_date := NULL) r
ORDER BY start_date
) ranked
# Notice that after the three tied for second earliest start date, the next one jumps to 5 (not 3):
+---------+-------+------------+---------+------+
| user_id | name | start_date | team_id | rank |
+---------+-------+------------+---------+------+
| 1 | Matt | 2017-01-01 | 1 | 1 |
| 2 | John | 2017-01-02 | 2 | 2 |
| 3 | Sara | 2017-01-02 | 2 | 2 |
| 4 | Tim | 2017-01-02 | 3 | 2 |
| 5 | Bob | 2017-01-03 | 3 | 5 |
| 6 | Bill | 2017-01-04 | 3 | 6 |
| 7 | Kathy | 2017-01-04 | 3 | 6 |
| 8 | Anne | 2017-01-05 | 3 | 8 |
+---------+-------+------------+---------+------+
On gists
MySql
MySql tricks
Ranks - djpw
ranks.sql #
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
On gists
MySql
MySql tricks
Nejblizsi vyssi / nizsi zaznam
example.sql #
SELECT a.id AS nejblizsi_nizsi, b.id AS nejblizsi_vyssi
FROM `tabulka` a JOIN `tabulka` b ON (a.id < $id AND b.id>$id)
ORDER BY (b.id-a.id) ASC LIMIT 1
On gists
MySql
MySql tricks
Nejvyšší články v kategoriích
max.sql #
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)
On gists
MySql
MySql tricks
Equal rank with same values
example.sql #
id game_id game_rank score team_id
-------------------------------------------
5 1 1 15 1
4 1 2 25 2
1 1 3 40 3
3 1 3 40 4
2 1 4 55 5
7 2 0 0 1
6 2 0 0 2
#1
SET @lastscore = 0;
SET @ordering = 0;
UPDATE game_scores
SET
game_rank = IF(score = @lastscore, @lastscore, (@ordering := @ordering + 1))
, score = (@lastscore := score)
WHERE game_id = 1
ORDER BY score;
#2
UPDATE game_scores
CROSS JOIN ( SELECT @lastscore:=0, @ordering:=0) AS parameter
SET
game_rank = IF(score = @lastscore, @lastscore, (@ordering := @ordering + 1))
, score = (@lastscore := score)
WHERE game_id = 1
ORDER BY score;
#3
SET @lastscore := 0;
SET @ordering := 0;
UPDATE game_scores SET
IF(@lastscore = score, @ordering, @ordering := @ordering + 1),
game_rank = @ordering,
@lastscore := score
WHERE game_id = 1
ORDER BY score;
On gists
MySql
MySql tricks
Multiple update with THEN / ELT
update.sql #
#1
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)
#2
On gists
MySql
MySql tricks
Subcategories (limited level)
example.sql #
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
On gists
MySql
MySql tricks
Update/Delete with JOIN
example.sql #
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