Writing MySQL storage storage functions

This blog summarizes the process of writing MySQL storage functions (function), debugging ideas, summaries, and references.

0. Preparation

Build a test database

CREATE DATABASE IF not EXISTS ' Funcdemo ';

1. Write

First write the function framework and define the parameters and return value types :

Use Funcdemo;

DELIMITER;D elimiter $ $CREATE FUNCTION Hello (S CHAR ()) RETURNS CHAR (a) BEGIN    RETURN CONCAT (' Hello, ', S, '! '); end$$

Command line login MySQL

Mysql-uroot-proot;

Import Scripts on the MySQL command line funcdemo.sql

SOURCE C:/sql/funcdemo.sql

Note: Errors in the script may be reported here, modified and then re-imported.

The import success results are as follows:

Mysql> source C:/sql/funcdemo.sql; Query OK, 1 row affected, 1 Warning (0.00 sec) Database changedquery OK, 0 rows affected, 1 Warning (0.00 sec) Query OK, 0 R OWS affected, 1 Warning (0.00 sec) Query OK, 0 rows Affected (0.00 sec)

View the function you have created

mysql> use Funcdemo;database changedmysql> show function status;

You can see the functions that have been created

| Funcdemo                 | Hello                  | FUNCTION | [Email protected] |

Call to see return results

mysql> Select Hello (' functest '); +-------------------+| Hello (' functest ') |+-------------------+| Hello, functest!  | +-------------------+1 row in Set (0.00 sec)

2. Write business logic

Writing business logic, such as adding string length judgments

BEGIN    IF LENGTH (s) > ten then      RETURN CONCAT (' Input string too long! ');    ELSE      RETURN CONCAT (' Hello ', S, '! '); end$$

Re-import with the source command and execute again:

Mysql> source C:/sql/funcdemo.sql; Query OK, 1 row affected, 1 Warning (0.00 sec) Database changedquery OK, 0 rows affected, 1 Warning (0.00 sec) Query OK, 0 R oWS Affected (0.00 sec) Query OK, 0 rows Affected (0.00 sec) mysql> Select Hello (' Functestverylonglong '); +-------------- -----------------+| Hello (' functestverylonglong ') |+-------------------------------+| Input string too long!        | +-------------------------------+1 row in Set (0.00 sec)

3. Commissioning

Storage functions cannot use select VarName as stored functions; To view the parameters, but can be viewed through a temporary table.

Create a temporary table and insert the parameters you want to view:

CREATE temporary TABLE IF not EXISTS ' tmp ' (  value CHAR ($) CHARACTER SET UTF8 COLLATE utf8_bin);D elete from Tmp;inser T into TMP (value) VALUES (s);

After re-importing, execute the function and then view the staging table:

mysql> Select Hello (' functest '); +-------------------+| Hello (' functest ') |+-------------------+| hellofunctest!    | +-------------------+1 row in Set (0.01 sec) mysql> select * from tmp;+----------+| Value    |+----------+| functest |+----------+1 row in Set (0.00 sec)

4. Summary

The first time you write a storage function has encountered many problems:

-with NAVICAT clients are often encountered because the syntax is not missing content issues, and later adopted the command line to resolve.
-Source Command Import script, encounter path problem, report script does not exist: in the Windows environment, you need to replace the default \ as the path delimiter.
-The delimiter DELIMITER is defined before and after the storage function; To change to $$ before storing the function, the import end should be changed;

DELIMITER;D Elimiter $ $BEGIN--store function definition end$ $DELIMITER;

-How to type-convert a defined parameter, find a string, date, and other functions that are in MySQL's own.
-In the terminal view of the Chinese garbled: set names gb2312;
-View defined databases, tables, and functions with the show command:

Show databases;show tables;show function status;

-View the creation with the show command, and add the Create parameter after the show command:

Mysql> Show CREATE Database funcdemo;+----------+-------------------------------------------------------------- -----+| Database | Create Database |+----------+------------------------------------------- ------------------------+| Funcdemo | CREATE DATABASE ' Funcdemo '/*!40100 DEFAULT CHARACTER SET UTF8 */|+----------+---------------------------------------- ---------------------------+1 Row in Set (0.00 sec) mysql> Show create table t;+-------+----------------------------- --------------------------------------------------------------------------------------------+| Table | Create Table |+ -------+------------------------------------------------------------------------------------------------------- ------------------+| T | CREATE TABLE ' t ' (' C ' char () CHARACTER SET UTF8 COLLATE utf8_bin default NULL) Engine=innodb default Charset=utf8 |+-------+---------------------------------------------------------------------------------------- ---------------------------------+1 Row in Set (0.00 sec) mysql> Show create function hello;

-Export database and storage functions with mysqldump with--opt-r parameters

>mysqldump-proot-uroot--opt-r Funcdemo > C:/sql/backup.sql

Read more, write more, more debugging, more summary.

The sample script for this article can be downloaded here.

The next section describes how to use the MySQL official documentation to view SQL syntax, function-Express examples.

Writing MySQL storage storage functions