Two functions that work just like FIND_IN_SET, but support the % wildcard.
SELECT FIND_IN_WILD_SET('true', 'this,returns,%true%');
SELECT FIND_IN_WILD_SET('true', 'this,returns,%false%');
SELECT FIND_WILD_IN_SET('tr%e', 'this,returns,true');
SELECT FIND_WILD_IN_SET('tr%e', 'this,returns,false');

DROP FUNCTION IF EXISTS FIND_IN_WILD_SET;
DROP FUNCTION IF EXISTS FIND_WILD_IN_SET;

DELIMITER //

CREATE FUNCTION FIND_IN_WILD_SET(theString varchar(65535), theSet varchar(65535))
RETURNS boolean
DETERMINISTIC
BEGIN

DECLARE delimiterCount int;
DECLARE pos int DEFAULT 0;
DECLARE setElement varchar(65535);
DECLARE returnValue boolean DEFAULT FALSE;

SET delimiterCount := CHARACTER_LENGTH(theSet) - CHARACTER_LENGTH(REPLACE(theSet, ',', ''));

WHILE (pos <= delimiterCount) DO
    BEGIN
        SET setElement := SUBSTRING_INDEX(SUBSTRING_INDEX(theSet, ',', pos+1), ',', -1);
        SET returnValue := returnValue OR (theString LIKE setElement); …

A stored function to guess which line terminator ('\r' or '\r\n') to use with LOAD DATA INFILE

CREATE FUNCTION whichLineTerminator(fileName varchar(255))
RETURNS varchar(20)
NOT DETERMINISTIC
BEGIN
  DECLARE cr_count int;
  DECLARE lf_Count int;
  DECLARE f text;

  SET f := LOAD_FILE(fileName);

  IF f IS NULL THEN RETURN 'Cannot read file.'; END IF;

  SET cr_count := CHARACTER_LENGTH(f) - CHARACTER_LENGTH(REPLACE(f, '\r', ''));
  SET lf_count := CHARACTER_LENGTH(f) - CHARACTER_LENGTH(REPLACE(f, '\n', ''));

  IF cr_count >= lf_count
  THEN RETURN '\\r\\n';
  ELSE RETURN '\\n';
  END IF;
END

Find the greatest common factor of two positive integers.

CREATE FUNCTION gcd(x int, y int) RETURNS int DETERMINISTIC
BEGIN
  DECLARE dividend int;
  DECLARE divisor int;
  DECLARE remainder int;
  SET dividend := GREATEST(x, y);
  SET remainder := LEAST(x, y);

  WHILE remainder != 0 DO
    SET divisor = remainder;
    SET remainder = MOD(dividend, divisor);
    SET dividend = divisor;
  END WHILE;

  RETURN divisor;
END

Explode a string around a delimiter without a loop.

DROP PROCEDURE IF EXISTS explode;

DELIMITER |

CREATE PROCEDURE explode( pDelim VARCHAR(32), pStr TEXT)
BEGIN
   DROP TABLE IF EXISTS temp_explode;
   CREATE TEMPORARY TABLE temp_explode (id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, word VARCHAR(40));
   SET @sql := CONCAT('INSERT INTO temp_explode (word) VALUES (', REPLACE(QUOTE(pStr), pDelim, '\'), (\''), ')');
   PREPARE myStmt FROM @sql;
   EXECUTE myStmt;
END |   

DELIMITER ;

-- example call:
SET @str  = "The quick brown fox jumped over the lazy dog";
SET @delim = " "; 

CALL explode(@delim,@str);
SELECT id,word FROM temp_explode;

Extract the first, longest pattern that matches a regular expression.
You could add a parameter or write a similar function to get the shortest match instead (by starting with e = s and then incrementing e, instead of starting with e = LENGTH(string) and decrementing).

CREATE FUNCTION REGEXP_EXTRACT(string TEXT, exp TEXT)
-- Extract the first longest string that matches the regular expression
-- If the string is 'ABCD', check all strings and see what matches: 'ABCD', 'ABC', 'AB', 'A', 'BCD', 'BC', 'B', 'CD', 'C', 'D'
-- It's not smart enough to handle things like (A)|(BCD) correctly in that it will return the whole string, not just the matching token.

RETURNS TEXT
DETERMINISTIC
BEGIN
  DECLARE s INT DEFAULT 1;
  DECLARE e INT;
  DECLARE adjustStart TINYINT DEFAULT 1;
  DECLARE adjustEnd TINYINT DEFAULT 1;

  -- Because REGEXP matches anywhere in the string, and we only want the part that matches, adjust the expression to add '^' and '$'
  -- Of course, …

Support nightmare: a customer reports a random PHP MySQL error. As a support expert you have the strong feeling that it is down to some suspicious SQL sequence. How to proof? 25 lines of PECL/mysqlnd_uh swiss-army knife magic…

prepend.php

class __mysqlnd_logger extends MysqlndUhConnection {
 private $protocol;
        
 public function query($conn, $query) {
  $ret = parent::query($conn, $query);
  if ($errno = $this->getErrorNumber($conn)) {
   $this->protocol[] = array(
    "query" => $query,
    "error" => sprintf("[%d] %s",
     $errno, $this->getErrorString($conn)),
    "bt" => debug_backtrace()
   );
  } else {
   $this->protocol[] = $query;
  }
  return $ret;
 }
        
 public function getProtocol() {
  return $this->protocol;
 }
        
}
$__logger = new __mysqlnd_logger();
mysqlnd_uh_set_connection_proxy($__logger);

The …

This week we talk with Giuseppe Maxia (pronouncing his name correctly!) about new features in Tungsten replicator, and go over a lot of news.

News:
There was a lot of news this week! There were three really big announcements at Oracle OpenWorld that relate to the MySQL community:

Oracle released a NoSQL database. http://www.oracle.com/technetwork/database/nosqldb/overview/index.html
It’s an ACID-compliant key-value data store, with a Java API for put, delete and get operations and includes replication. The data partitioning and distribution is automatic and based on a hash function.

…

I've already described Justin Swanhart's Flexviews project as something I think is cool. Since then Justin appears to have been working more on Shard-Query which I also think is cool, perhaps even more so than Flexviews.

On the page linked above, Shard-Query is described using the following statements :

"Shard-Query is a distributed parallel query engine for MySQL"
"ShardQuery is a PHP class which is intended to make working with a partitioned dataset easier""ParallelPipelining - MPP distributed query engines runs fragments of queries in parallel, combining the results at the end. Like map/reduce except it speaks SQL directly."

The things I like from the above description :

• Distributed
…

Its good to announce that Monty Program will have a presence at Percona Live London (October 24 & 25 2011), and we’re very excited about it. There will be two talks (no tutorials this time around):

1. Sergei Golubchik will present on Pluggable Authentication in MySQL and MariaDB. I’ve seen Serg give tutorials to this effect, so expect a lot within half an hour!
2. Colin Charles will present on Why MariaDB? which focuses on what new features are available today in MariaDB and how they benefit users.

Will we see you there? Registrations are still open and we would love to talk to you then.

Monty Program is also …

Want to learn more about the exciting news we recently shared at Oracle OpenWorld?

Oracle is driving MySQL Innovation and there’s a lot to talk about!

Join us at the following dates & locations:

October 25th: Düssseldorf

October 26th: Berlin

November 8th: Dreieich

November 9th: Munich

You will learn …