mysql自定义函数示例

oracle数据库向mysql数据库迁移时，遇到了一个oracle函数: instr(str, '|', pos), 在字符串str中pos的位置开始，查找字符'|'，如果存在，则返回其位置。而mysql中有函数instr只有两个参数：instr(str, substr)。于是自动动手写了个函数来代替：

DELIMITER $$
CREATE FUNCTION myinstr(str VARCHAR(255), str2 VARCHAR(255), pos INT)
RETURNS INT NO SQL
BEGIN
    DECLARE newstr VARCHAR(255);
    DECLARE j INT;
    DECLARE r INT;

    IF(pos >= 1 && pos <= LENGTH(str)) THEN        -- 开始查找的位置必须是：[1, length(str)]
        SET newstr = SUBSTRING(str, pos);
        SET j = INSTR(newstr, str2);

        IF(j <= 0) THEN        -- 没有查找到str2
            SET r = 0;
        ELSE
            SET r = pos - 1 + j;
        END IF;
    ELSE
        SET r = 0;
    END IF;

    RETURN r;
END
$$

测试结果：

mysql> select myinstr('aaa|bbb', '|', 0);
+----------------------------+
| myinstr('aaa|bbb', '|', 0) |
+----------------------------+
| 0 |
+----------------------------+
1 row in set (0.00 sec)

mysql> select myinstr('aaa|bbb', '|', 1);
+----------------------------+
| myinstr('aaa|bbb', '|', 1) |
+----------------------------+
| 4 |
+----------------------------+
1 row in set (0.00 sec)

mysql> select myinstr('aaa|bbb', '|', 2);
+----------------------------+
| myinstr('aaa|bbb', '|', 2) |
+----------------------------+
| 4 |
+----------------------------+
1 row in set (0.00 sec)

mysql> select myinstr('aaa|bbb', '|', 3);
+----------------------------+
| myinstr('aaa|bbb', '|', 3) |
+----------------------------+
| 4 |
+----------------------------+
1 row in set (0.00 sec)

mysql> select myinstr('aaa|bbb', '|', 4);
+----------------------------+
| myinstr('aaa|bbb', '|', 4) |
+----------------------------+
| 4 |
+----------------------------+
1 row in set (0.01 sec)

mysql> select myinstr('aaa|bbb', '|', 5);
+----------------------------+
| myinstr('aaa|bbb', '|', 5) |
+----------------------------+
| 0 |
+----------------------------+
1 row in set (0.00 sec)

mysql> select myinstr('aaa|bbb', '|', 9);
+----------------------------+
| myinstr('aaa|bbb', '|', 9) |
+----------------------------+
| 0 |
+----------------------------+

在创建函数时，如果返回类型说明：RETURNS INT NO SQL 的后面没有带NO SQL时，会报下面的错误：
错误代码： 1418
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
产生该错误的原因如下：
因为我们开启了日志 bin_log，那么如果我们创建的函数在执行时，就涉及到在master和slave上执行的结果是否一样的问题，
也就是函数是否是确定性的(DETERMINISTIC)，是否是可重复的，或者是否改变数据库的数据。不然会导致master和
slave两者的数据不一致，所以mysql强制要求：
在master上，除非子程序被声明为确定性的或者不更改数据库中的数据，否则创建或者替换子程序将被拒绝。
这意味着当创建一个子程序的时候，必须要么声明它是确定性的，要么它不改变数据。

所以解决方法有两种：
1）声明我们创建的函数是确定的
    这里要说明的是:使用NOW() 函数（或它的同义）或者RAND() 函数不会使一个子程序变成非确定性的。
    对NOW()而言，二进制日志包括时间戳并会被正确的执行。RAND()只要在一个子程序内被调用一次也可以被正确的复制。
    所以，可以认为时间戳和随机数种子是子程序的确定性输入，它们在主服务器和从服务器上是一样的。
2）声明函数是否改变数据库中的数据
    CONTAINS SQL, NO SQL, READS SQL DATA, MODIFIES SQL用来指出子程序是读还是写数据的。
    无论NO SQL还是READS SQL DATA都指出，子程序没有改变数据，但是必须明确地指定其中一个。

所以该错误的解决方法有如下几种：
1）RETURNS INT NO SQL ：明确说明，我们创建的函数中不包含sql语句，所以肯定不会修改数据库中的数据；
2）RETURNS INT READS SQL DATA ：说明我们的函数仅仅只是读取数据库，不会修改数据库；
3）RETURNS INT DETERMINISTIC：明确说明，我们创建的函数是确定的。

注：后来发现其实mysql中的函数：locate(substr, str, pos) 正好可以实现oracle中instr函数的功能，只是需要调换一下第一个和第二个参数的位置，所以其实没有必要自己定义一个函数了。