mysql_snippets.sql

/* 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
;