Home
search
Collections
Sign in or create an account
Medium site navigation
Collection edited
by
Brad Bloomfield
Follow
2
MySQL — Interesting queries for reporting and analysis
Tips and tricks for MySQL
Latest
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 Bloomfield
1 min read
Latest
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 Bloomfield
3 min read
Latest
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 Bloomfield
2 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 Bloomfield
2 min read
Latest
MySQL date functions
Here is a little collection of my favourite MySQL functions for manipulating dates.
Brad Bloomfield
1 min read
More stories