July 5, 2012

Concatenating values into a single column with MySQL group_concat

How many times have you wanted to return a list of grouped values from a database, in a single column, per row of your recordset? Actually I’ve wanted to do that a few times! Most of the time I’ll let the application layer handle that, but sometimes letting the database do the hard work fits the bill. My not-so-theoretical example is course subjects (think English, Maths etc) that have 1-n “stages” (think categories) attached to them. ... Read more

March 15, 2012

Error restoring MySQL database when USING BTREE on indexes

Occasionally when I’m restoring a database that was backed up from a different server than the one I’m restoring on I get an error along the lines of: >[Error] Script lines: 5-17 ------------------------- You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8' at line 11 Warnings: ---> W (1): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8' at line 11 Hmm, the docs suggest that BTREE is a valid index type for both MyISAM and InnoDB. ... Read more

March 3, 2011

MySQL alternative to MSSQL''s isNull()

I was looking for a way to do isNull() in MySQL the other day, for those that don’t know this means you can do something like: SELECT isNull(mycolumn, 'blah') FROM myTable; If the value in mycolumn is NULL, then ‘blah’ is returned, this can of course be any string literal/numeric value you want. MySQL doesn’t have isNull() but it does have ifNull() which is basically the same. SELECT ifNull(mycolumn, 'blah') FROM myTable;

February 8, 2011

SQL for finding duplicate values

Quick post to remind me how to check a table column for duplicate values next time I’m looking for it (when my memory fails!) SELECT id, count(id) FROM mytable GROUP BY id HAVING count(id) > 1

November 14, 2010

Importing a csv into MySQL using the command line

UPDATE 16th Nov 2010: I had some issues with the import which I noted in the first code example below Today I had to import csv into a single mysql table. The destination table only had 5 columns and the csv was around 22MB (around 400,000 records). The csv also contained more columns that I needed for the import. Sometimes I’ll use a GUI to do this as I usually have one open, but it never ceases to amaze me how much slower GUI tools are compared to the command line. ... Read more

October 4, 2010

Case sensitive development on Mac OS X

At work most of the developers are on Mac OS X but our staging and production environments are Linux based. This means development is case insensitive but stage/production is case sensitive. Although we have development guidelines/standards which dictate file and database naming conventions etc, human error can still occur (in terms of referencing files with a different “case”). We also use a few open source packages where we obviously don’t have control over the naming of files or the references to database tables within those files. ... Read more

September 8, 2010

Tip when adding "allowMultiQueries=true" to your MySQL DSN in ColdFusion

Scenario…I’m using ColdFusion 9 and MySQL 5. I already had my DSN setup but had a requirement to get the last inserted id from a MySQL INSERT statement (I’m using auto-incrementing integer’s). As most people know, the best way to do this (in MySQL) is using the last_insert_id() function. I wanted to attach this to the end of my INSERT statement for performance reasons, as I’d only be sending one query to the server instead of separate ones. ... Read more

December 13, 2008

Development configuration tips for Ubuntu

I just setup my machine at home with Ubuntu 8.10 (desktop) which was so easy think I saved hours off the time it would normally take with Windows. So much is already installed and ready to go and with apt (or the Synaptic package manager) it’s super quick to download and install extra things things like MySQL, PHP, Apache, Java etc Here are some notes which helped me out when configuring some of these development tools. ... Read more

December 3, 2008

Querying multiple databases within one cfquery

In discussing a possible caching solution at work a suggestion was made to use a secondary database (on the same server) to store some key application data. It’s not really important to describe why we wanted to do this, suffice to say that my concern was if and when the application would need to perform a SQL “JOIN” between the primary and secondary (caching) database as I wasn’t sure if it was technically possible within our environment (ColdFusion 8, MySQL). ... Read more

November 22, 2008

cfqueryparam does not work in ORDER BY, what are my options?

For those that don’t know, ColdFusion’s cfqueryparam won’t work on a SQL ORDER BY clause, so the following will not work: <cfquery name="qGetUsers" datasource="mydsn"> SELECT FirstName, LastName FROM Users ORDER BY <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.order#" /> </cfquery> This is really nothing new, people have been talking about this for years but with the spate of recent SQL injection attacks (note that I meant to publish this around sept 08 but got sidetracked! ... Read more

© Michael Sharman 2017