cool-mysql-stuff.md

Cool MySQL Query Stuff

This gist is a way of collecting cool, more unusual MySQL query techniques and tips and tricks.

ifnull()

use this to convert column values to more useful defaults if they are null. can be used in formulas, order by etc.

in this example, using ifnull() avoids sums using columns that have null values ending up as null

  select *, ifnull(avg, 0) + ifnull(chase_bonus,0) as final
  from game_team_chase_totals
  where game_id = 51
  and chase = 1
  order by final desc;

you can even use it within other functions

  select sum(ifnull(avg, 0) + ifnull(chase_bonus,0)) as total_sum
  from game_team_chase_totals

in-query comments

sometimes it can be handy to embed a comment in a query to explain a certain line, or just to remove a condition without deleting it from your code

  select *, ifnull(avg, 0) + ifnull(chase_bonus,0) as final
  from game_team_chase_totals
  -- we need to limit to a particular game
  where game_id = 51
  -- and chase = 1
  order by final desc;

insert or update existing

handle insert/updating in one query

  insert into game_team_chase_badges_totals(team_id, game_id, total)
  values(101, 54, 98.7)
  on duplicate key update total = 98.7;