Occasionally you’ll have a need for a query that doesn’t quite fall under the standard range of MySQL statements. A few examples:

Random Rows

SELECT * FROM your_table ORDER RAND() LIMIT 1

The query selects a single random row; more rows can be added by raising the integer at the end. This query is only usable if the database table remains relatively small: above 10,000 rows or so, it doesn’t scale efficiently.

Finding gaps in numbered sequences

Useful if you’ve generated rows with an auto-numbered index that has been thrown out of sequence by the deletions of rows.

SELECT a.number + 1 FROM your_table AS a WHERE NOT EXISTS 
	(SELECT b.number FROM your_table AS b WHERE a.number + 1 = b.number) 
	GROUP BY a.number;

This will deliver missed numbers for a particular field, allowing you to backfill them with new articles and retain a complete auto-numbered sequence. The next entry number, after the most recent entry, will also be included.

Find and replace in MySQL

Very useful for databases of all kinds, but perhaps most particularly CMS’s, when updating content or references that have been made across multiple entries. Be sure to make a backup of your database tables before running a query such as this; the ability to change multiple rows at the same moment should be used very carefully.

UPDATE your_table SET a_field = REPLACE(a_field,'string_to_find', 'string_to_replace');

Word Count

SELECT SUM( LENGTH(column) - LENGTH(REPLACE(column, ' ', ''))+1) 
	FROM tables

Counts the number of spaces in the content for all the rows in a column for a database table. Adding 1 gives the total number of words.

If you have other MySQL queries that you find useful, please feel free to add them in the comments below!

Photograph by photosteve101 used under a Creative Commons Attribution 2.0 Generic Attribution license

Enjoy this piece? I invite you to follow me at twitter.com/dudleystorey to learn more.