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: backup, howto, MySQL, stored functions
Posted by Hans-Henry Jakobsen
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: howto, MySQL, PHP, stored functions
Posted by Hans-Henry Jakobsen
06 Sep 2009 Howto combine two columns into one in mysql
This is how you can combine (or concatenate or merge) data from two table columns into one column using a mysql SQL query.
SELECT CONCAT(firstname,lastname) AS 'name' FROM tablename
The result would be “firstname lastname”
If you would like to insert this data into a new column
INSERT INTO tablename (full_name) SELECT CONCAT(firstname,lastname) AS 'name' FROM tablename
Posted by Hans-Henry Jakobsen
15 Dec 2008 MySQL search and replace
This is a simple SQL query to perform search and replace in a MySQL table
update tablename set fieldname = replace(fieldname,'search_for_this','replace_with_this');
Tags: MySQL
Posted by Hans-Henry Jakobsen
08 Oct 2008 mysql on a nondefault socket
It is sometimes necessary to run two instances of mysql, like in my case. I need a mysql database in addition to the one Zimbra uses. One solution to this problem is to run the mysql database on a non default socket. This can be done by changing the following line in my.cnf
my.cnf
[client] ... socket = /var/run/mysqld/mysqld2.sock
Restart the mysql daemon afterwards to activate the change.
In PHP it’s possible to avoid programming the nondefault socket info whenever we use mysql
php.ini
[MySQL] ... mysql.default_socket = /var/run/mysqld/mysqld2.sock
If you don’t have access to php.ini you have to program this in your PHP code
$dbcnx = @mysql_connect('localhost:/var/run/mysqld/mysqld2.sock',$username, $password);
Posted by Hans-Henry Jakobsen
