/
Gists
/
MySql tricks
Gists - MySql tricks
On gists
Pivot table via prepared statements
•
MySql
MySql tricks
MySql - advanced
pivot.sql #
create table tbl_values (
id int unsigned not null primary key,
`Name` varchar(10),
`Group` varchar(10),
`Value` int
);
insert into tbl_values values
(1, 'Pete', 'A', 10),
(2, 'Pete', 'B', 20),
(3, 'John', 'A', 10);
-- 1. Create an expression that builds the columns
set @sql = (
select group_concat(distinct
concat(
"sum(case when `Group`='", `Group`, "' then `Value` end) as `", `Group`, "`"
)
)
from tbl_values
);
-- 2. Complete the SQL instruction
set @sql = concat("select Name, ", @sql, " from tbl_values group by `Name`");
-- 3. Create a prepared statement
prepare stmt from @sql;
-- 4. Execute the prepared statement
execute stmt;
On gists
MySql
MySql tricks
MySql - advanced
Update table col by random date / string
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
MySql
MySql tricks
Insert into ... Select ... (cross join)
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
MySql
MySql tricks
MySql - advanced
Swap two columns values
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
MySql tricks
MySql - advanced
AW
Novato - ranks
rank-model.php #
public function triggerEvent(array $events = [], Nette\Database\Table\ActiveRow $section = null, $debug = false)
{
if($debug)
{
$logFile = INDEX_DIR . '/trigger-event.log';
$logData = [];
foreach ($events as $event)
{
$logData[] = sprintf('%s | %s | sectionId: %d',
date('Y-m-d H:i:s'),
$event,
($section !== null) ? $section->id : $section
)
. PHP_EOL;
}
file_put_contents('nette.safe://' . $logFile, $logData, FILE_APPEND);
}
$sectionIndependentEvents = ['onAny'];
$whereOrParams = $whereOr = [];
foreach($events as $event)
{
$whereOr[] = 'FIND_IN_SET(?, event)';
$whereOrParams[] = $event;
}
$presenters = $this->connection->table('catalogue_presenter_config')
->where(implode(' OR ', $whereOr), ...$whereOrParams);
$pagesToInvalidate = [];
foreach ($presenters as $presenterRow)
{
$pages = $this->connection
->table('catalogue_page')
->where('catalogue_section.catalogue_id', $section->catalogue_id)
->where('presenter_id', $presenterRow->presenter_id);
$invalidateIndependent = FALSE;
foreach(explode(',', $presenterRow->event) as $event)
if(in_array($event, $sectionIndependentEvents))
$invalidateIndependent = TRUE;
if(!$invalidateIndependent)
$page->where('catalogue_section_id', $section->id);
foreach ($pages as $page)
$pagesToInvalidate[$page->id] = $page;
}
foreach ($pagesToInvalidate as $pageId => $page)
$this->invalidPdf($page);
}
public function recountPageNumbersInCatalogue($catalogueId)
{
$this->connection->query('
UPDATE
catalogue_page
JOIN (
SELECT
tmp2.*,
@totalRank := @totalRank + tmp2.prev as lastPageNumberInCatalogue
FROM
(SELECT @totalRank := 0) as tempTotalRank,
(
SELECT
tmp.*,
@prev as prev,
@prev := tmp.pdf_pages_count
FROM
(
SELECT
@prev := 0,
t2.name AS sectionName,
t2.rank AS sectionRank,
t1.rank AS pageRank,
t1.id,
t1.pdf_pages_count
FROM
catalogue_page t1
JOIN catalogue_section t2 ON t2.id = t1.catalogue_section_id
WHERE t2.catalogue_id = ?
ORDER BY sectionRank ASC, pageRank ASC
) AS tmp
) AS tmp2
) tmpJoin ON tmpJoin.id = catalogue_page.id
SET page_number = lastPageNumberInCatalogue + 1
', $catalogueId);
}
On gists
MySql
MySql tricks
MySql - advanced
SQL select only rows with max value on a column (2 ways) - universal
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
•
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
•
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;
On gists
MySql
MySql tricks
Insert A Random String from List in MySQL
gistfile1.txt #
UPDATE tablename
SET columnname = ELT(0.5 + RAND() * 6, 'value 1','value 2','value 3','value 4','value 5','value 6')
On gists
MySql
MySql tricks
Rand number between (start, end)
random-number-between.sql #
-- 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 )