Dig into mysql to create custom functions and stored procedures

Create custom functions
In the process of using mysql, the built-in functions of mysql may not be able to fulfill our business requirements, so we need to customize the functions. For example, the author encountered the following problem in the development process:
The mysql table structure is as follows

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pic` varchar(50) NOT NULL,
  `hashcode` varchar(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES ('1', '2012120910403250c3fa209bf48.jpg', 'bf8f83818080c0f1');
INSERT INTO `test` VALUES ('2', '2012120620430750c092db26557.JPG', 'ff9880f0f680ceff');
INSERT INTO `test` VALUES ('3', '2012120619582550c08861eb062.jpg', '7f7f004f7f7f7c7f');
INSERT INTO `test` VALUES ('4', '2012112911072650b6d16e7f21f.jpg', '7f7f004f7f7f007f');

Where pic field is the image name, hashcode is the image's perceptual hash encoding (hexadecimal encoding string with fixed length of 16 bits), and the user enters one hashcode. How can we find out from the database that the number of different characters that satisfy the corresponding position of the string is less than 5? Like "11001" and "11101" position corresponding to the different characters of different number is 1, such as user input "7 f7f004f7f7f00af", then the article 3 and article 4 records is to meet, how to implement? If it is difficult to rely solely on mysql's built-in functions, then it is necessary to establish a custom function to solve the problem. We would like to thank acmain_chm, acmain_chm and oschina's @liang xiaogang and @taobaodinqi for the solution of this problem

The process of creating a custom function is as follows:
1. Enter the mysql command line
mysql >
2. Use the delimiter command to set the statement delimiter from; / /. This allows it to be used in the program body; The delimiter is passed to the server rather than interpreted by mysql itself.
mysql > delimiter //
3. Create custom functions

mysql>CREATE FUNCTION hashDiff( s1 varchar(16), s2 varchar(16)) 
->RETURNS INT
->BEGIN
->DECLARE diff, x INT;
->SET diff =0;
->SET x = 0;
->WHILE (x  < 16 )  DO
->SET x = x+1;
->if SUBSTRING(s1, x,1)<>SUBSTRING(s2, x,1) then
->set diff=diff+ 1;
->end if;
->END WHILE;
->RETURN diff;
->END
->//
mysql>select * from test t where  hashDiff(t.hashcode,'ff9880f0f680ceff')  < 5;

Create stored procedures
1. Enter the mysql command line
mysql >
2. Use the delimiter command to set the statement delimiter from; / /. This allows it to be used in the program body; The delimiter is passed to the server rather than interpreted by mysql itself.
mysql > delimiter //
3. Create stored procedures

mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)