msgbartop
A chronological documentation test project, nothing serious, really!
msgbarbottom

02 Nov 2009 Howto backup mysql stored functions and stored procedures

This is how you can backup you MySQL database(s) and stored procedures

# mysqldump --routines <dbname>

Or you can backup only the stored procedures

# mysqldump --no-create-db --no-create-info --no-data --routines <dbname>

Tags: , , ,

Posted by

23 Oct 2009 mysql alternative to PHP substr_count function

The substr_count function in PHP counts the number of substring occurrences. This post describes how to create a mysql stored function to behave just like PHP’s substr_count function.

This function can be created from your mysql console

delimiter ||
DROP FUNCTION IF EXISTS substrCount||
CREATE FUNCTION substrCount(s VARCHAR(255), ss VARCHAR(255)) RETURNS TINYINT(3) UNSIGNED LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA
BEGIN
DECLARE count TINYINT(3) UNSIGNED;
DECLARE offset TINYINT(3) UNSIGNED;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s = NULL;

SET count = 0;
SET offset = 1;

REPEAT
IF NOT ISNULL(s) AND offset > 0 THEN
SET offset = LOCATE(ss, s, offset);
IF offset > 0 THEN
SET count = count + 1;
SET offset = offset + 1;
END IF;
END IF;
UNTIL ISNULL(s) OR offset = 0 END REPEAT;

RETURN count;
END;

||

delimiter ;

Usage

Example 1

SELECT substrCount('/this/is/a/path', '/') `count`;

`count` would return 4 in this case. Can be used in such cases where you might want to find the “depth” of a path, or for many other uses.
This function is great to count the content of mysql ENUM and SET field data types.

Example 2

SELECT substrcount(
                `tablename` , ','
        ) as tablename
        FROM `tablename`
        where substrcount(
                `tablename` , ','
        ); 

The content of table named tablename is a comma separated list generated from mysql ENUM datatype

2000/2001,2001/2002,2002/2003,2003/2004,2004/2005,2005/2006,2006/2007,2007/2008,2008/2009,2009/2010

In Example 2 the result from this query would be 9, telling us that there are 9 commas in this tablerow.

Source: Posted by Andrew Hanna on August 24 2006 8:04pm

Tags: , , ,

Posted by