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
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
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
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
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