Last active
April 29, 2021 14:45
-
-
Save Lubje/77535a18520d153b24966e2303409e60 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* List tables by size */ | |
select | |
table_schema as "Database", | |
table_name as "Table", | |
round(((data_length + index_length) / 1024 / 1024), 2) as "Size (MB)" | |
from | |
information_schema.`TABLES` | |
where 1 | |
and table_schema = database() | |
and table_name like "%SEARCH%" | |
order by | |
(data_length + index_length) desc | |
; | |
/* Get database size */ | |
select | |
table_schema as "Database", | |
round(sum(data_length + index_length) / 1024 / 1024, 2) as "Size (MB)" | |
from | |
information_schema.`TABLES` | |
where 1 | |
and table_schema = "DATABASENAME" | |
group by | |
table_schema | |
order by | |
`Size (MB)` desc | |
; | |
/* Matrix compare "empty" values */ | |
SELECT | |
column_0 as "=", | |
column_1 as "NULL", | |
column_2 as "''", | |
column_3 as "' '", | |
column_4 as "0", | |
column_5 as "0.0", | |
column_6 as "'0'", | |
column_7 as "'0.0'", | |
column_8 as "'00'", | |
column_9 as "'0123'", | |
column_10 as "'0abc'" | |
FROM ( | |
VALUES | |
ROW("NULL", NULL=NULL, NULL='', NULL=' ', NULL=0, NULL=0.0, NULL='0', NULL='0.0', NULL='00', NULL='0123', NULL='0abc'), | |
ROW("''", ''=NULL='', ''='', ''=' ', ''=0, ''=0.0, ''='0', ''='0.0', ''='00', ''='0123', ''='0abc'), | |
ROW("' '", ' '=NULL, ' '='', ' '=' ', ' '=0, ' '=0.0, ' '='0', ' '='0.0', ' '='00', ' '='0123', ' '='0abc'), | |
ROW("0", 0=NULL, 0='', 0=' ', 0=0, 0=0.0, 0='0', 0='0.0', 0='00', 0='0123', 0='0abc'), | |
ROW("0.0", 0.0=NULL, 0.0='', 0.0=' ', 0.0=0, 0.0=0.0, 0.0='0', 0.0='0.0', 0.0='00', 0.0='0123', 0.0='0abc'), | |
ROW("'0'", '0'=NULL, '0'='', '0'=' ', '0'=0, '0'=0.0, '0'='0', '0'='0.0', '0'='00', '0'='0123', '0'='0abc'), | |
ROW("'0.0'", '0.0'=NULL, '0.0'='', '0.0'=' ', '0.0'=0, '0.0'=0.0, '0.0'='0', '0.0'='0.0', '0.0'='00', '0.0'='0123', '0.0'='0abc'), | |
ROW("'00'", '00'=NULL, '00'='', '00'=' ', '00'=0, '00'=0.0, '00'='0', '00'='0.0', '00'='00', '00'='0123', '00'='0abc'), | |
ROW("'0123'", '0123'=NULL, '0123'='', '0123'=' ', '0123'=0, '0123'=0.0, '0123'='0', '0123'='0.0', '0123'='00', '0123'='0123', '0123'='0abc'), | |
ROW("'0abc'", '0abc'=NULL, '0abc'='', '0abc'=' ', '0abc'=0, '0abc'=0.0, '0abc'='0', '0abc'='0.0', '0abc'='00', '0abc'='0123', '0abc'='0abc') | |
/* Add another value to the comparison: */ | |
-- , ROW("'XXX'", 'XXX'=NULL, 'XXX'='', 'XXX'=' ', 'XXX'=0, 'XXX'=0.0, 'XXX'='0', 'XXX'='0.0', 'XXX'='00', 'XXX'='0123', 'XXX'='0abc') | |
) AS comparison | |
; | |
/* TablePlus multi-cursor keyboard shortcuts */ | |
-- | |
-- Add cursor | |
-- Cmd + Click | |
-- | |
-- Add cursor on same position on row above or below | |
-- Ctrl + Shift + ↑ | |
-- Ctrl + Shift + ↓ | |
-- | |
-- Select next instance (word selected or cursor at beginning) | |
-- Cmd + D | |
-- | |
-- Select all instances | |
-- Cmd + Ctrl + G | |
-- | |
-- Select block | |
-- Opt + draw | |
; | |
/* Disble 'only full group by' */ | |
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')) | |
; | |
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')) | |
; | |
/* Search for columns by name */ | |
select distinct | |
table_name, | |
column_name | |
from | |
information_schema.`COLUMNS` | |
where 1 | |
and TABLE_SCHEMA = database() | |
and column_name like '%SEARCH%' | |
; | |
/* Timezones */ | |
SET TIME_ZONE = 'Europe/Amsterdam' | |
; | |
SET TIME_ZONE = 'UTC' | |
; | |
SELECT @@global.time_zone, @@session.time_zone | |
; | |
SELECT TIMEDIFF( NOW(), UTC_TIMESTAMP() ) | |
; | |
/* Get entire row with lowest id for every user. Usefull for deduplicating */ | |
select | |
a.* | |
from | |
YourTable a | |
left join | |
YourTable b on a.user_id = b.user_id and a.id < b.id | |
where 1 | |
and b.user_id is null | |
; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment