MySQL — Interesting queries for reporting and analysis — Medium

Collection edited by Brad Bloomfield

MySQL CONCAT — a really handy function

I often use the MySQL string concatenation function concat. It is really awesome and makes my life easier. So I’ll take you through a…couple of uses that I have found for it. Making a full name out of a first and last name CONCAT(firstname,” “,lastname) AS `full name` Making a set of INSERT statements from the results of a SELECT Sometimes when doing data wrangling it is easier to run a query that generates a bunch of inserts from an existing table. You can use INSERT … SELECT syntax or if you need to transfer the data to a different database server you can use a SELECT with a CONCAT line and generate the INSERT.

Brad Bloomfield1 min read

MyISAM — An old but still really useful storage engine

It’s been that INNODB has been the default MySQL storage engine for some years. For good reason, foreign keys and index level locking make…it a lot more advanced. All of that functionality comes at a cost which is performance. Sadly, you don’t get functionality for free. I have seen this first hand at work a couple of years ago. We had a table that was pretty simple, just four integers per record but was about 600 Million records long. It was also badly designed because someone felt it was necessary to index each field uniquely. It was using the INNODB storage engine.

Brad Bloomfield3 min read

MySQL Prepared Statements

I recently developed a PHP script to export some denormalised data out of a set of MySQL tables. The original authors of the system we…storing PHP serialised arrays in the database. The data inside that array was useful to me for the queries that I was performing. Now I could have done some tricky stuff taking data from MySQL into PHP and back again but I decided to hack my way through deserialising the array using MySQL string manipulation functions and CASE statements all intermingled. I’m the first to admit, it’s not that maintainable and looking at the SQL you have no idea what it does.

Brad Bloomfield2 min read

MySQL Conditional Statements

Brief overview of how I have been known to use (abuse) MySQL conditional statements to achieve a beneficial outcome.IF IF is a nice and simple conditional statement. I use if to convert boolean values to something that the user understands, to display meaningful values when using left/right joins when the default would have been null or in a more complicated example to run a different subquery depending on the type of record that I’m dealing with. Making things human readable is IF( genderVal=1,’Male’,’Female’) AS `gender` Making left/right joins easier for humans to understand in reports IF ( someValue…

Brad Bloomfield2 min read

MySQL date functions

Here is a little collection of my favourite MySQL functions for manipulating dates.

Brad Bloomfield1 min read