Create a custom function
In the process of using MySQL, MySQL's own functions may not be able to complete our business requirements, then the need for custom functions, such as the author in the development process encountered the following problem:
The MySQL table structure is as follows
Copy Code code as follows:
DROP TABLE IF EXISTS ' test ';
CREATE TABLE ' Test ' (
' ID ' int (a) unsigned not NULL auto_increment,
' pic ' varchar not NULL,
' hashcode ' varchar 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 the pic field is the picture name, Hashcode is the image of the perceptual hash code (16-encoded string, fixed length 16 bits), the user input a hashcode, how to find from the database to meet the position of the string corresponding to the number of different numbers less than 5 records it? like "11001" and "11101" corresponding to the position of different characters of the number of 1, such as user input "7F7F004F7F7F00AF", then the third and fourth record is satisfied, how to achieve it? If you simply rely on MySQL's own functions to be difficult to complete, then you need to establish a custom function to solve. The solution to this problem is thanks to Csdn's Acmain_chm,acmain_chm and Oschina's @ Liang, and @ Taobao Ding
The process of establishing a custom function is as follows:
1. Enter the MySQL command line
Mysql>
2. Use the delimiter command to change the statement delimiter from; to//. This allows you to pass the delimiter to the server in the program body, rather than being interpreted by MySQL itself.
Mysql> delimiter//
3. Create a Custom function
Copy Code code as follows:
Mysql>create FUNCTION Hashdiff (S1 varchar), S2 varchar (16))
->returns INT
->begin
->declare diff, X INT;
->set diff = 0;
->set x = 0;
->while (x <) 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;
Two creating a stored procedure
1. Enter the MySQL command line
Mysql>
2. Use the delimiter command to change the statement delimiter from; to//. This allows you to pass the delimiter to the server in the program body, rather than being interpreted by MySQL itself.
Mysql> delimiter//
3. Create a stored procedure
Copy Code code as follows:
Mysql> delimiter//
Mysql> CREATE PROCEDURE Simpleproc (out param1 INT)
-> BEGIN
-> SELECT COUNT (*) into the 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)
