Ever wished you could run a dangerous query like DROP TABLE or TRUNCATE against every table in a database in a single query?

No?

Well, how about something like CHECK or REPAIR table, then?

If you ever find yourself in the rare situation where you need to run the same query across every table in a database, this procedure might make your life easier:

DELIMITER $$
CREATE PROCEDURE `p_run_for_each_table`(IN strDatabase TEXT, IN strOperation TEXT)
    DETERMINISTIC
BEGIN
	DECLARE strQuery TEXT;
	DECLARE strTable VARCHAR(255);
	DECLARE bDone INT DEFAULT 0;
	DECLARE TableCursor CURSOR FOR
		SELECT `TABLE_NAME`
		FROM `information_schema`.`TABLES`
		WHERE `TABLE_SCHEMA` = strDatabase AND TABLE_TYPE = 'BASE TABLE';
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
 
	OPEN TableCursor;
 
	REPEAT
		FETCH TableCursor INTO strTable;
 
		SET @QueryThatWasPassedIn := REPLACE(
			REPLACE(strOperation, '{?database}', strDatabase)
			, '{?table}', strTable);
 
		PREPARE Statement FROM @QueryThatWasPassedIn;
		EXECUTE Statement;
	UNTIL bDone END REPEAT;
 
	CLOSE TableCursor;
	DEALLOCATE PREPARE Statement;
END$$
DELIMITER ;

Useage

The procedure takes two parameters. The first one is the name of the database whose tables you want to run the query for.

The second is the query you would like to run. The strings "{?database}" and "{?table}" will be replaced with the database and table names.

CALL p_run_for_each_table('databasename', 'SELECT * FROM {?database}.{?table}');

What queries you can run

You should be able to use any queries that can be run in a prepared statement – you can find the list about two-thirds of the way down this page.

As of MySQL 5.1, you can run these queries: "ALTER TABLE, CALL, COMMIT, CREATE INDEX, CREATE TABLE, DELETE, DO, DROP INDEX, DROP TABLE, INSERT, RENAME TABLE, REPLACE, SELECT, SET, UPDATE, and most SHOW statements."

During my career developing database-driven software (teehee, I'm a professional) I've noted that the most horrific query errors are the logical ones – queries that parse correctly, and return reasonable-looking data, but make wrong assumptions about how different parts of the query relate to each other.

One particular error that I've seen time and time again (even from people who have been writing queries for a while) can occur when summarizing data from multiple tables that have a one-to-many relationship.

…In other words, it could occur in queries written for most database-driven software.

Solution: fix the problem by writing about it!

I wrote a page documenting the cause of the logical error, doing my best to warn people against letting it slip into their own code.

I attempted to write it so it would be easy to read, possibly even entertaining (a lofty goal for a manual on writing database queries, perhaps) – there is some colorful language and plenty of juvenile humor mixed with the tech-speak.

The doc itself is part of the wiki of the company where I work. I don't write a ton of documentation for our developers or customers (certainly not as much as I should), but whenever I do, I get this awesome feeling of usefulness. Oh, and pride. Sometimes, I feel so proud, that I feel compelled to link other people (who have no relationship to my company) to what I wrote! Ridiculous, I know.

Remember: if you write queries, it is your responsibility to guarantee that they return true and accurate data!

You know what I hate? Paragraphs of capital (or all lowercase) letters.

The other day a coworker was looking to beautify a large quantity of data spread across some MySQL tables.  I created this function to make his life easier:

DELIMITER $$
CREATE FUNCTION `f_sentence_case`(strInput TEXT, nMinimumLength INT) RETURNS TEXT
DETERMINISTIC
BEGIN
 
	DECLARE result TEXT;
	DECLARE LastSpace INT;
	DECLARE NextSpace INT;
	DECLARE NextSlash INT;
	DECLARE Word TEXT;
	DECLARE NewSentence INT;
	DECLARE PreviousCharacter CHAR(1);
	DECLARE TrimmedWord TEXT;
	DECLARE NumberOfSpaces INT;
	DECLARE Swap INT;
 
	SET strInput := CONCAT(strInput, ' ');
	SET result := '';
	SET LastSpace := 1;
	SET NextSpace := LOCATE(' ', strInput, LastSpace + 1);
	SET NextSlash := LOCATE('/', strInput, LastSpace + 1);
 
	SET NextSpace := IF(LEAST(NextSlash, NextSpace) = 0, GREATEST(NextSlash, NextSpace), LEAST(NextSlash, NextSpace));
 
	label1: WHILE NextSpace DO
 
		SET Word := SUBSTR(strInput, LastSpace, NextSpace - LastSpace);
		SET PreviousCharacter := SUBSTR(strInput, LastSpace - 1, 1);
		SET NewSentence := LastSpace = 1 OR (NewSentence AND PreviousCharacter = ' ') OR PreviousCharacter IN('.', '!', '?');
 
		SET TrimmedWord := LTRIM(Word);
		SET NumberOfSpaces := LENGTH(Word) - LENGTH(TrimmedWord);
		SET Word := TrimmedWord;
 
		# Make it lowercase if it is all uppercase
		SET Word := IF(LENGTH(Word) >= nMinimumLength AND Word NOT REGEXP '[0-9]', 
			IF(NewSentence,
				CONCAT(UCASE(SUBSTR(Word, 1, 1)), LCASE(SUBSTR(Word, 2, LENGTH(Word) - 1))),
				LCASE(Word)
			), 
			Word);
 
		SET result := CONCAT(result, REPEAT(' ', NumberOfSpaces), Word);
 
		SET Swap := LastSpace;
		SET LastSpace := NextSpace;
		SET NextSpace := LOCATE(' ', strInput, Swap + 1);
		SET NextSlash := LOCATE('/', strInput, Swap + 1);
		SET NextSpace := IF(LEAST(NextSlash, NextSpace) = 0, GREATEST(NextSlash, NextSpace), LEAST(NextSlash, NextSpace));
 
	END WHILE label1;
 
RETURN result;
 
END$$
DELIMITER ;

What it does

It seems to perform generally as I hoped it would; which is to say that it formats text to be sentence case.

More specifically, it alters all the "words" (a set of non-numeric, non-whitespace characters) that are longer than the specified minimum length.

It changes the words to be all lowercase, unless they happen to be the first word after a punctuation mark (in which case the first character of the word is made uppercase).

Useage

To clean up a field so that it is formatted in sentence case (ignoring all words with less than 3 characters), simply run this query:

UPDATE `table` SET `field` = f_sentence_case(`field`, 3);

Other than that, my only specs for the query were for it to be functional and hopefully not break my brain when I went back to read it later. If anyone has any significant improvements to it, let me know!

Some background: a "quine" is a program that outputs it's complete source code when run.

Some more background: I work at a company where I work with MySQL (a database engine) very often.

I was lounging around in the MySQL chat room on irc.freenode.net, and someone suggested that I try to write a MySQL quine – a database query that would return the text of the query itself.

After about half an hour of screwing around, I got it! It may not be a fantastic achievement, but I feel pretty leet about writing my first quine in a database query language.

SELECT REPLACE(@v:='SELECT REPLACE(@v:=\'2\',1+1,REPLACE(REPLACE(@v,\'\\\\\',\'\\\\\\\\\'),\'\\\'\',\'\\\\\\\'\'));',1+1,REPLACE(REPLACE(@v,'\\','\\\\'),'\'','\\\''));

Only 167 characters. Hah!