Gists - MySql

On gists

Update table col by random date / string

20.5.2020

MySql MySql tricks MySql - advanced

update.sql #

-- datetime
SET @MIN = '2019-06-29 00:53:27';
SET @MAX = '2019-06-29 13:53:27';

UPDATE tablename
SET columnname = TIMESTAMPADD(SECOND, FLOOR(RAND() * TIMESTAMPDIFF(SECOND, @MIN, @MAX)), @MIN)

-- https://stackoverflow.com/questions/24378490/update-table-with-random-values-from-given-set-of-string-values
-- string
update table t
    set col = elt(floor(rand()*3) + 1, 'value1', 'value2', 'value3');
    
-- or
UPDATE `table`
SET `column`=(CASE CEIL(RAND()*3)
              WHEN 1 THEN 'value1'
              WHEN 2 THEN 'value2'
              WHEN 3 THEN 'value3'
          END);
          
          
-- random numbet between N a M

-- For range (min..max( (min inclusive, max exclusive) it is:
FLOOR( RAND() * (max-min) + min )
 
-- For range (min..max) (min+max inclusive) it is:
FLOOR( RAND() * (max-min+1) + min )

On gists

SQL array - NDB - args

19.5.2020

Nette MySql

args.php #

<?php
// DIBI
$q = []
array_push('SELECT * FROM %n', $table);
array_push('WHERE id > %d', $id);

$res = dibi::fetchAll($q);


// NDB
$q = $args = [];
$q[] = "SELECT * FROM ?";
$args[] = $table;

$q[] = "WHERE id > ?";
$args[] = $id;

$res = $this->db->fetchAll(implode(' ', $q), ...$args);

On gists

Stored Procedures - LS

14.11.2019

MySql MySql - advanced

sp1.sql #


set character set utf8;

-- deprecated (deleted)
DROP PROCEDURE IF EXISTS odds_old_1x2;
DROP PROCEDURE IF EXISTS odds_old_moneyline;
DROP PROCEDURE IF EXISTS odds_old_underover;
DROP PROCEDURE IF EXISTS odds_old_handicap;
DROP PROCEDURE IF EXISTS split_string1;
DROP PROCEDURE IF EXISTS split_string2;

--correct_url (inc/functions.inc.php)
DELIMITER |
DROP PROCEDURE IF EXISTS correct_url;
CREATE PROCEDURE correct_url(game_id INT, lang_id INT)
MODIFIES SQL DATA
BEGIN
	DROP TABLE IF EXISTS BE_TMP_Game;
	CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Game ENGINE=MEMORY
	SELECT m.URL AS aLeagueURL, s.URL AS aSportURL, c.URL AS aCountryURL
	FROM
		BE_Game AS g,
		BE_Match AS m,
		BE_Sport AS s,
		BE_League AS l,
		BE_Country AS c
	WHERE
		g.GameID = game_id
		AND g.MatchID = m.MatchID AND m.LangID = lang_id
		AND m.SportID = s.SportID AND s.LangID = lang_id
		AND m.LeagueID = l.LeagueID
		AND l.CountryID = c.CountryID AND c.LangID = lang_id;

	IF((SELECT COUNT(*) FROM BE_TMP_Game) = 0) THEN
		CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Game ENGINE=MEMORY
		SELECT m.URL AS aLeagueURL, s.URL AS aSportURL, c.URL AS aCountryURL
		FROM
			BE_GameArchive AS g,
			BE_Match AS m,
			BE_Sport AS s,
			BE_League AS l,
			BE_Country AS c
		WHERE
			g.GameID = game_id
			AND g.MatchID = m.MatchID AND m.LangID = lang_id
			AND m.SportID = s.SportID AND s.LangID = lang_id
			AND m.LeagueID = l.LeagueID
			AND l.CountryID = c.CountryID AND c.LangID = lang_id;
	END IF;

	SELECT * FROM BE_TMP_Game;

	DROP TABLE BE_TMP_Game;
END|
DELIMITER ;

--get_mutual_team (mutual)
DELIMITER |
DROP PROCEDURE IF EXISTS get_mutual_team;
CREATE PROCEDURE get_mutual_team(league_id INT, lang_id INT)
MODIFIES SQL DATA
BEGIN
	DROP TABLE IF EXISTS BE_TMP_Game;

	CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Game ENGINE=MEMORY
	SELECT tg1.TeamID, tg1.Name
	FROM
		BE_Game AS g,
		BE_TeamGroup AS tg1 
	WHERE
		g.MatchID = league_id
		AND g.HomeID = tg1.TeamID AND tg1.LangID = lang_id
	GROUP BY tg1.Name;

	IF((SELECT COUNT(*) FROM BE_TMP_Game) = 0) THEN
		CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Game ENGINE=MEMORY
		SELECT tg1.TeamID, tg1.Name
		FROM
			BE_GameArchive AS g,
			BE_TeamGroup AS tg1 
		WHERE
			g.MatchID = league_id
			AND g.HomeID = tg1.TeamID AND tg1.LangID = lang_id
		GROUP BY tg1.Name;
	END IF;

	SELECT * FROM BE_TMP_Game ORDER BY Name;

	DROP TABLE BE_TMP_Game;
END|
DELIMITER ;

--odds_1x2 (nextmatch)
DELIMITER |
DROP PROCEDURE IF EXISTS odds_1x2;
CREATE PROCEDURE odds_1x2(league_id INT, lang_id INT, project_id INT, timezone CHAR(6))
MODIFIES SQL DATA
BEGIN
	DROP TABLE IF EXISTS BE_TMP_Odds;

	SET @ldb = (SELECT DBPostfix FROM BE_Project WHERE idProject = project_id LIMIT 1);

	SET @query = CONCAT("CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Odds ENGINE=MEMORY
							SELECT
								g.idCourse, o.OfficeID, o.Name AS aBettingOffice, d.Description, o.OriginID, u.URL,
								g.BettingOfficeId, g.K1, g.K2, g.K0, g.K10, g.K02,
								DATE_FORMAT(CONVERT_TZ(g.Created, '+1:00', '", timezone, "'), '%d/%m %H:%i')  AS aCreated,
								IF(og.K1 IS NULL OR og.K1 = 0, g.K1, og.K1) AS aOldK1, IF(og.K2 IS NULL OR og.K2 = 0, g.K2, og.K2) AS aOldK2, IF(og.K0 IS NULL OR og.K0 = 0, g.K0, og.K0) AS aOldK0,
								IF(og.K10 IS NULL OR og.K10 = 0, g.K10, og.K10) AS aOldK10, IF(og.K02 IS NULL OR og.K02 = 0, g.K02, og.K02) AS aOldK02,
								DATE_FORMAT(IF(og.Created IS NULL OR og.Created = 0, CONVERT_TZ(g.Created, '+1:00', '", timezone, "'), CONVERT_TZ(og.Created, '+1:00', '", timezone, "')), '%d/%m %H:%i')  AS aOldCreated
							FROM
								BE_Course AS g
									LEFT JOIN BE_CourseOld AS og ON (g.idCourse = og.CourseID),
								BE_Office AS o
									LEFT JOIN BE_OfficeDescription AS d ON (o.idOffice = d.OfficeID AND d.LangID = ", lang_id, ")
									LEFT JOIN betexplorer_", @ldb, ".BettingOfficeURL AS u ON (u.OfficeID = o.OfficeID)
							WHERE
								g.GameID = ", league_id, "
								AND g.BettingOfficeID = o.OfficeID
							GROUP BY
								g.idCourse
							ORDER BY
								og.idCourse");

	PREPARE course FROM @query;
	EXECUTE course;
	DEALLOCATE PREPARE course;

--	DROP TABLE IF EXISTS BE_TMP_OddsOld;

--	CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_OddsOld ENGINE=MEMORY
--	SELECT o.idCourse, o.CourseID, o.K1, o.K0, o.K2, o.Created, DATE_FORMAT(CONVERT_TZ(o.Created, '+1:00', '", timezone, "'), '%d/%m %H:%i') AS aCreated
--	FROM
--		BE_TMP_Odds AS tmp,
--		BE_CourseOld AS o
--	WHERE
--		tmp.idCourse = o.CourseID;
END|
DELIMITER ;

--odds_underover (nextmatch)
DELIMITER |
DROP PROCEDURE IF EXISTS odds_underover;
CREATE PROCEDURE odds_underover(league_id INT, lang_id INT, project_id INT, timezone CHAR(6))
MODIFIES SQL DATA
BEGIN
	DROP TABLE IF EXISTS BE_TMP_Odds;

	SET @ldb = (SELECT DBPostfix FROM BE_Project WHERE idProject = project_id LIMIT 1);

	SET @query = CONCAT("CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Odds ENGINE=MEMORY
							SELECT
								g.idCourse, g.`Value`, o.OfficeID, o.Name AS aBettingOffice, d.Description, o.OriginID, u.URL,
								g.BettingOfficeId, g.K1, g.K2,
								DATE_FORMAT(CONVERT_TZ(g.Created, '+1:00', '", timezone, "'), '%d/%m %H:%i')  AS aCreated,
								IF(og.K1 IS NULL OR og.K1 = 0, g.K1, og.K1) AS aOldK1, IF(og.K2 IS NULL OR og.K2, g.K2, og.K2) AS aOldK2,
								DATE_FORMAT(IF(og.Created IS NULL OR og.Created = 0, CONVERT_TZ(g.Created, '+1:00', '", timezone, "'), CONVERT_TZ(og.Created, '+1:00', '", timezone, "')), '%d/%m %H:%i')  AS aOldCreated
							FROM
								BE_Course2 AS g
									LEFT JOIN BE_Course2Old AS og ON (g.idCourse = og.CourseID),
								BE_Office AS o
									LEFT JOIN BE_OfficeDescription AS d ON (o.idOffice = d.OfficeID AND d.LangID = ", lang_id, ")
									LEFT JOIN betexplorer_", @ldb, ".BettingOfficeURL AS u ON (u.OfficeID = o.OfficeID)
							WHERE
								g.GameID = ", league_id, "
								AND g.BettingOfficeID = o.OfficeID
							GROUP BY
								g.idCourse
							ORDER BY
								og.idCourse;");

	PREPARE course FROM @query;
	EXECUTE course;
	DEALLOCATE PREPARE course;

	DROP TABLE IF EXISTS BE_TMP_OddsOld;

	CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_OddsOld ENGINE=MEMORY
	SELECT o.idCourse, o.CourseID, o.K1, o.K2, o.Created, DATE_FORMAT(CONVERT_TZ(o.Created, '+1:00', '", timezone, "'), '%d/%m %H:%i') AS aCreated
	FROM
		BE_TMP_Odds AS tmp,
		BE_Course2Old AS o
	WHERE
		tmp.idCourse = o.CourseID;

END|
DELIMITER ;

--odds_handicap (nextmatch)
DELIMITER |
DROP PROCEDURE IF EXISTS odds_handicap;
CREATE PROCEDURE odds_handicap(league_id INT, lang_id INT, project_id INT, timezone CHAR(6))
MODIFIES SQL DATA
BEGIN
	DROP TABLE IF EXISTS BE_TMP_Odds;

	SET @ldb = (SELECT DBPostfix FROM BE_Project WHERE idProject = project_id LIMIT 1);

	SET @query = CONCAT("CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Odds ENGINE=MEMORY
							SELECT
								g.idCourse, g.`Value`, o.OfficeID, o.Name AS aBettingOffice, d.Description, o.OriginID, u.URL,
								g.BettingOfficeId, g.K1, g.K2,
								DATE_FORMAT(CONVERT_TZ(g.Created, '+1:00', '", timezone, "'), '%d/%m %H:%i')  AS aCreated,
								IF(og.K1 IS NULL OR og.K1 = 0, g.K1, og.K1) AS aOldK1, IF(og.K2 IS NULL OR og.K2 = 0, g.K2, og.K2) AS aOldK2,
								DATE_FORMAT(IF(og.Created IS NULL OR og.Created = 0, CONVERT_TZ(g.Created, '+1:00', '", timezone, "'), CONVERT_TZ(og.Created, '+1:00', '", timezone, "')), '%d/%m %H:%i')  AS aOldCreated
							FROM
								BE_Course3 AS g
									LEFT JOIN BE_Course3Old AS og ON (g.idCourse = og.CourseID),
								BE_Office AS o
									LEFT JOIN BE_OfficeDescription AS d ON (o.idOffice = d.OfficeID AND d.LangID = ", lang_id, ")
									LEFT JOIN betexplorer_", @ldb, ".BettingOfficeURL AS u ON (u.OfficeID = o.OfficeID)
							WHERE
								g.GameID = ", league_id, "
								AND g.BettingOfficeID = o.OfficeID
							GROUP BY
								g.idCourse
							ORDER BY
								og.idCourse;");

	PREPARE course FROM @query;
	EXECUTE course;
	DEALLOCATE PREPARE course;

	DROP TABLE IF EXISTS BE_TMP_OddsOld;

	CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_OddsOld ENGINE=MEMORY
	SELECT o.idCourse, o.CourseID, o.K1, o.K2, o.Created, DATE_FORMAT(CONVERT_TZ(o.Created, '+1:00', '", timezone, "'), '%d/%m %H:%i') AS aCreated
	FROM
		BE_TMP_Odds AS tmp,
		BE_Course3Old AS o
	WHERE
		tmp.idCourse = o.CourseID;

END|
DELIMITER ;

--odds_moneyline (nextmatch)
DELIMITER |
DROP PROCEDURE IF EXISTS odds_moneyline;
CREATE PROCEDURE odds_moneyline(league_id INT, lang_id INT, project_id INT, timezone CHAR(6))
MODIFIES SQL DATA
BEGIN
	DROP TABLE IF EXISTS BE_TMP_Odds;

	SET @ldb = (SELECT DBPostfix FROM BE_Project WHERE idProject = project_id LIMIT 1);

	SET @query = CONCAT("CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Odds ENGINE=MEMORY
							SELECT
								g.idCourse, o.OfficeID, o.Name AS aBettingOffice, d.Description, o.OriginID, u.URL,
								g.BettingOfficeId, g.K1, g.K2,
								DATE_FORMAT(CONVERT_TZ(g.Created, '+1:00', '", timezone, "'), '%d/%m %H:%i')  AS aCreated,
								IF(og.K1 IS NULL OR og.K1 = 0, g.K1, og.K1) AS aOldK1, IF(og.K2 IS NULL OR og.K2 = 0, g.K2, og.K2) AS aOldK2,
								DATE_FORMAT(IF(og.Created IS NULL OR og.Created = 0, CONVERT_TZ(g.Created, '+1:00', '", timezone, "'), CONVERT_TZ(og.Created, '+1:00', '", timezone, "')), '%d/%m %H:%i')  AS aOldCreated
							FROM
								BE_Course4 AS g
									LEFT JOIN BE_Course4Old AS og ON (g.idCourse = og.CourseID),
								BE_Office AS o
									LEFT JOIN BE_OfficeDescription AS d ON (o.idOffice = d.OfficeID AND d.LangID = ", lang_id, ")
									LEFT JOIN betexplorer_", @ldb, ".BettingOfficeURL AS u ON (u.OfficeID = o.OfficeID)
							WHERE
								g.GameID = ", league_id, "
								AND g.BettingOfficeID = o.OfficeID
							GROUP BY
								g.idCourse
							ORDER BY
								og.idCourse;");

	PREPARE course FROM @query;
	EXECUTE course;
	DEALLOCATE PREPARE course;

	DROP TABLE IF EXISTS BE_TMP_OddsOld;

	CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_OddsOld ENGINE=MEMORY
	SELECT o.idCourse, o.CourseID, o.K1, o.K2, o.Created, DATE_FORMAT(CONVERT_TZ(o.Created, '+1:00', '", timezone, "'), '%d/%m %H:%i') AS aCreated
	FROM
		BE_TMP_Odds AS tmp,
		BE_Course4Old AS o
	WHERE
		tmp.idCourse = o.CourseID;

END|
DELIMITER ;

--teaminfo1 (teaminfo.php)
DELIMITER |
DROP PROCEDURE IF EXISTS teaminfo1;
CREATE PROCEDURE teaminfo1(league_id INT, team_id INT, lang_id INT, timezone CHAR(6))
MODIFIES SQL DATA
BEGIN
	DROP TABLE IF EXISTS BE_TMP_Game;

	CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Game ENGINE=MEMORY
	SELECT
		g.GameID, DATE_FORMAT(CONVERT_TZ(g.`Date`, '+1:00', timezone), '%d.%m.%Y') AS aDate, g.`Date`, g.Round, g.HomeID, g.GuestID,
		tg1.Name AS aHomeName, tg2.Name AS aGuestName, tg1.URL AS aHomeURL, tg2.URL AS aGuestURL
	FROM
		BE_Game AS g,
		BE_Match AS m,
		BE_TeamGroup AS tg1,
		BE_TeamGroup AS tg2
	WHERE
		g.MatchID = league_id
		AND g.Played = 'n'
		AND g.MatchID = m.MatchID AND m.LangID = lang_id
		AND (g.HomeID = team_id OR g.GuestID = team_id)
		AND g.HomeID = tg1.TeamID AND tg1.LangID = lang_id
		AND g.GuestID = tg2.TeamID AND tg2.LangID = lang_id;

	CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Game ENGINE=MEMORY
	SELECT
		g.GameID, DATE_FORMAT(CONVERT_TZ(g.`Date`, '+1:00', timezone), '%d.%m.%Y') AS aDate, g.`Date`, g.Round, g.HomeID, g.GuestID,
		tg1.Name AS aHomeName, tg2.Name AS aGuestName, tg1.URL AS aHomeURL, tg2.URL AS aGuestURL
	FROM
		BE_GameArchive AS g,
		BE_Match AS m,
		BE_TeamGroup AS tg1,
		BE_TeamGroup AS tg2
	WHERE
		g.MatchID = league_id
		AND g.Played = 'n'
		AND g.MatchID = m.MatchID AND m.LangID = lang_id
		AND (g.HomeID = team_id OR g.GuestID = team_id)
		AND g.HomeID = tg1.TeamID AND tg1.LangID = lang_id
		AND g.GuestID = tg2.TeamID AND tg2.LangID = lang_id;

	SELECT * FROM BE_TMP_Game ORDER BY `Date` DESC;
	DROP TABLE IF EXISTS BE_TMP_Game;
END|
DELIMITER ;

On gists

Insert into ... Select ... (cross join)

6.11.2019

MySql MySql tricks

insert-select-join.sql #

INSERT INTO `article_tag_lang` (article_tag_id, language_id, tag)
(
    SELECT i.id, l.id, i.tag
    FROM `language` AS l
    JOIN `article_tag` AS i
);

On gists

Swap two columns values

25.10.2019

MySql MySql tricks MySql - advanced

swap.sql #

UPDATE swap_test s1, swap_test s2 SET s1.x=s1.y, s1.y=s2.x WHERE s1.id=s2.id;

-- tipliga
UPDATE
 _tipliga_tipy_hracu t1, 
 _tipliga_tipy_hracu t2  
SET t1.g1=t1.g2, t1.g2=t2.g1 WHERE t1.id=t2.id
AND (t1.id_zapasu = 1462)

On gists

Tipliga - SQL - multiplevariables - ranks

24.10.2019

MySql

tipliga.sql #

	INSERT INTO
		_tipliga_poradi
		(
			poradi,
			id_hrace,
			liga,
			rocnik,
			pz,
			v,
			r,
			p,
			nz,
			score,
			pts
		)
		
		SELECT
		rank,
		hrac,
		liga,
		rocnik,
		pz,
		v,
		r,
		p, 
		NETIPOVANO,
		SKORE,
		PTS
		FROM
		
		(
		
			SELECT
			*,
			IF(SKORE = @lastScore AND @lastPts = PTS, @currentRank := @currentRank, @currentRank := @i) as rank,
			@i := @i + 1,
			@lastScore := SKORE,
			@lastPts := PTS
			FROM
			(
			
			
			SELECT
							hrac, 
							h.prezdivka,
							SUM(PZ) as PZ,
							SUM(V) as V,
							SUM(R) as R,
							SUM(P) as P,
							SUM(NETIPOVANO) as NETIPOVANO,
							SUM(PTS) as SKORE,
							SUM(V)*3 + SUM(R)*1 + SUM(NETIPOVANO)*-1 as PTS,
							liga, 
							rocnik
			
							FROM
							(
										SELECT
										domaci as hrac,
										COUNT(*) as PZ,
										SUM(IF (domaci_body>host_body,1,0)) as V,
										SUM(IF (domaci_body=host_body AND domaci_tipoval = 1 AND host_tipoval = 1, 1,0)) as R,
										SUM(IF (domaci_body<host_body AND domaci_tipoval = 1, 1,0)) as P,
										SUM(IF (domaci_tipoval = 0, 1, 0)) as NETIPOVANO,
										SUM(domaci_body) as PTS,
										liga,
										rocnik
										
										FROM _tipliga_rozpisy
										WHERE 
										liga=%s
										AND rocnik=%s
										AND kolo <= %i
										GROUP BY hrac
								
										UNION ALL
										
										SELECT
										host as hrac,
										COUNT(*) as PZ,
										SUM(IF (domaci_body<host_body,1,0)) as V,
										SUM(IF (domaci_body=host_body AND domaci_tipoval = 1 AND host_tipoval = 1, 1,0)) as R,
										SUM(IF (domaci_body>host_body AND host_tipoval = 1, 1,0)) as P,
										SUM(IF (host_tipoval = 0, 1, 0)) as NETIPOVANO,
										SUM(host_body) as PTS,
										liga,
										rocnik
										
										FROM _tipliga_rozpisy
										WHERE 
										liga=%s
										AND rocnik=%s
										AND kolo <= %i
										GROUP BY hrac
							) as TMP
							
							JOIN hraci h ON h.id = TMP.hrac
							GROUP BY hrac
							ORDER BY PTS DESC, SKORE DESC, NETIPOVANO ASC, V DESC, h.prezdivka ASC
							
				) tmp,
				(SELECT @currentRank := 1, @i := 1, @lastPts:= -999, @lastScore := -999) as config
			) finalTable

On gists

Nejvyssi zaznamy v kategoriích

20.10.2019

MySql

example.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

SQL select only rows with max value on a column (2 ways) - universal

15.10.2019

MySql MySql tricks MySql - advanced

solution.sql #

-- @link: https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1

-- Joining with simple group-identifier, max-value-in-group Sub-query
SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev


-- Left Joining with self, tweaking join conditions and filters
SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

On gists

Dynamic SQL via prepared statements

12.10.2019 • 12.3.2024 (aktualizováno)

MySql MySql tricks MySql - advanced

example.sql #

-- 1
SET @table = "pexeso";
SET @sql:= CONCAT('SELECT * FROM',' ', @table);
PREPARE stmt FROM @sql;
EXECUTE stmt;
-- DEALLOCATE PREPARE stmt;  # neni povinne


-- 1b, lze dosadit parametry pres ?
SET @id = 1;
PREPARE stmt FROM 'SELECT * FROM category WHERE id = ?';
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt; 




-- 2, Dynamically Building SQL based on Parameters
CREATE DEFINER=`root`@`localhost` 
  PROCEDURE `GetRecentActivity`(input VARCHAR(15))
BEGIN
SET @input = input;

if @input="asc" then
    SET @sort = " order by activity_log_key asc";
elseif @input = "desc" then
    SET @sort = " order by activity_log_key desc";
else
    SET @sort ="";
end if;

SET @query = CONCAT('select * from activitylog ',@sort,' limit 0, 5');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END


-- 3, more than one column
SELECT GROUP_CONCAT(COLUMN_NAME) FROM information_schema.`COLUMNS` C 
WHERE table_name = 'MyTb' AND COLUMN_NAME LIKE '%whatever%' INTO @COLUMNS;

SET @table = 'MyTb';
SET @s = CONCAT('SELECT ',@columns,' FROM ', @table);

PREPARE stmt FROM @s;
EXECUTE stmt;

On gists

Triggers

11.10.2019 • 26.10.2019 (aktualizováno)

MySql MySql tricks MySql - advanced

clanky.sql #

CREATE TRIGGER diskuse_ai AFTER INSERT ON diskuse FOR EACH ROW UPDATE clanky SET diskuse_pocet = diskuse_pocet + 1 WHERE id = NEW.clanek;
CREATE TRIGGER diskuse_ad AFTER DELETE ON diskuse FOR EACH ROW UPDATE clanky SET diskuse_pocet = diskuse_pocet - 1 WHERE id = OLD.clanek;
CREATE TRIGGER diskuse_au AFTER UPDATE ON diskuse FOR EACH ROW UPDATE clanky SET diskuse_pocet = diskuse_pocet + IF(id = NEW.clanek, 1, -1) WHERE id = OLD.clanek XOR id = NEW.clanek;