首页 > 代码库 > 谈谈MySQL的函数问题!

谈谈MySQL的函数问题!

问题:

中秋提前下班,说数据库函数无法创建。数据库没的说,指定用户指定ip访问指定数据库全部权限。

。。。。。。


排查一下,发现数据库函数功能没有打开。函数这个问题吧,其实自己手动开启就行,比较简单。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

1、查看数据库函数功能是否开启:

mysql> show variables like ‘%func%‘;

+---------------------------------+-------+

| Variable_name                   | Value |

+---------------------------------+-------+

| log_bin_trust_function_creators | OFF   |

+---------------------------------+-------+

1 row in set (0.00 sec)


注意:OFF,说明并没有开启;

mysql>


2、开启和关闭函数功能:


开启:

SET GLOBAL log_bin_trust_function_creators=1;

mysql> SET GLOBAL log_bin_trust_function_creators=1;

Query OK, 0 rows affected (0.00 sec)


mysql> show variables like ‘%func%‘;

+---------------------------------+-------+

| Variable_name                   | Value |

+---------------------------------+-------+

| log_bin_trust_function_creators | ON    |

+---------------------------------+-------+

1 row in set (0.00 sec)


mysql>

技术分享

关闭:

SET GLOBAL log_bin_trust_function_creators=0;


问题:

上面命令行修改只是临时生效,数据库重启后仍然失效。

永久解决方案:

windows下my.ini

[mysqld]加上log_bin_trust_function_creators=1  

 

linux下/etc/my.cnf下my.cnf

[mysqld]加上log_bin_trust_function_creators=1

保存 ,重启服务器。

# service mysqld restart

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

3、谈谈命令行函数如何创建:(用工具的话指定简单许多)


查看数据库中所有的函数:

mysql> SHOW FUNCTION STATUS;

Empty set (0.00 sec)

mysql>


语法:(尊重原创,摘自:)

http://blog.csdn.net/jssg_tzw/article/details/39694489

CREATE FUNCTION func_name ( [func_parameter] ) //括号是必须的,参数是可选的  

RETURNS type  

[ characteristic ...] routine_body  


CREATE FUNCTION 用来创建函数的关键字;

func_name 表示函数的名称;

func_parameters为函数的参数列表,参数列表的形式为:[IN|OUT|INOUT] param_name type

IN:表示输入参数;

OUT:表示输出参数;

INOUT:表示既可以输入也可以输出;

param_name:表示参数的名称;

type:表示参数的类型,该类型可以是MySQL数据库中的任意类型;

RETURNS type:语句表示函数返回数据的类型;

characteristic: 指定存储函数的特性,取值与存储过程时相同,详细请访问-MySQL存储过程使用;


eg:

mysql> create database hr;  

Query OK, 1 row affected (0.00 sec)

mysql> use hr;  

Database changed

mysql> create table employees  

    -> (  

    ->     employee_id int(11) primary key not null auto_increment,  

    ->     employee_name varchar(50) not null,  

    ->     employee_sex varchar(10) default ‘男‘,  

    ->     hire_date datetime not null default current_timestamp,  

    ->     employee_mgr int(11),  

    ->     employee_salary float default 3000,  

    ->     department_id int(11)  

    -> ); 

Query OK, 0 rows affected (1.17 sec)


mysql> insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (‘David Tian‘,‘男‘,10,7500,1);  

‘,‘女‘,10,5300,1);  

insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (‘Sunshine Ma‘,‘女‘,10,6500,2);  

insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) valuesQuery OK, 1 row affected (0.00 sec)


mysql> insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (‘Black Xie‘,‘男‘,10,6600,1);  

Query OK, 1 row affected (0.00 sec)


mysql> insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (‘Moses Wang‘,‘男‘,10,4300,1);  

Query OK, 1 row affected (0.03 sec)


mysql> insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (‘Rena Ruan‘,‘女‘,10,5300,1);  

Query OK, 1 row affected (0.00 sec)


mysql> insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (‘Sunshine Ma‘,‘女‘,10,6500,2);  

Query OK, 1 row affected (0.00 sec)


mysql> insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (‘Scott Gao‘,‘男‘,10,9500,2);  

Query OK, 1 row affected (0.01 sec)


mysql> insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (‘Warren Si‘,‘男‘,10,7800,2);  

Query OK, 1 row affected (0.00 sec)


mysql> insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (‘Kaishen Yang‘,‘男‘,10,9500,3);  

Query OK, 1 row affected (0.00 sec)


mysql> insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (‘Simon Song‘,‘男‘,10,5500,3);  

Query OK, 1 row affected (0.00 sec)


mysql> insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (‘Brown Guan‘,‘男‘,10,5000,3);  

Query OK, 1 row affected (0.01 sec)


mysql> insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (‘Eleven Chen‘,‘女‘,10,3500,2);  

employee_salary,department_id) values (‘Cherry Zhou‘,‘女‘,10,5500,4);  

insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (‘Klause He‘,‘男‘,10,4500,5);  

insert into employees(employee_name,employee_sex,eQuery OK, 1 row affected (0.00 sec)


mysql> insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (‘Cherry Zhou‘,‘女‘,10,5500,4);  

Query OK, 1 row affected (0.01 sec)


mysql> insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (‘Klause He‘,‘男‘,10,4500,5);  

mployee_mgr,employee_salary,department_id) values (‘Maven Ma‘,‘男‘,10,4500,6);  

insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (‘Stephani Wang‘,‘女‘,10,5500,7);  

insert into employees(employee_name,eQuery OK, 1 row affected (0.00 sec)


mysql> insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (‘Maven Ma‘,‘男‘,10,4500,6);  

Query OK, 1 row affected (0.00 sec)


mysql> insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (‘Stephani Wang‘,‘女‘,10,5500,7);  

Query OK, 1 row affected (0.01 sec)


mysql> insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (‘Jerry Guo‘,‘男‘,10,8500,1);  

Query OK, 1 row affected (0.00 sec)


mysql> insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (‘Gerardo Garza‘,‘男‘,10,25000,8);  

Query OK, 1 row affected (0.01 sec)


mysql> insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (‘Derek Wu‘,‘男‘,10,5500,5);  

Query OK, 1 row affected (0.03 sec)


mysql> select * from employees; 

+-------------+---------------+--------------+---------------------+--------------+-----------------+---------------+

| employee_id | employee_name | employee_sex | hire_date           | employee_mgr | employee_salary | department_id |

+-------------+---------------+--------------+---------------------+--------------+-----------------+---------------+

|           1 | David Tian    | 男           | 2016-06-03 13:50:28 |           10 |            7500 |             1 |

|           2 | Black Xie     | 男           | 2016-06-03 13:50:28 |           10 |            6600 |             1 |

|           3 | Moses Wang    | 男           | 2016-06-03 13:50:28 |           10 |            4300 |             1 |

|           4 | Rena Ruan     | 女           | 2016-06-03 13:50:28 |           10 |            5300 |             1 |

|           5 | Sunshine Ma   | 女           | 2016-06-03 13:50:28 |           10 |            6500 |             2 |

|           6 | Scott Gao     | 男           | 2016-06-03 13:50:28 |           10 |            9500 |             2 |

|           7 | Warren Si     | 男           | 2016-06-03 13:50:28 |           10 |            7800 |             2 |

|           8 | Kaishen Yang  | 男           | 2016-06-03 13:50:28 |           10 |            9500 |             3 |

|           9 | Simon Song    | 男           | 2016-06-03 13:50:28 |           10 |            5500 |             3 |

|          10 | Brown Guan    | 男           | 2016-06-03 13:50:28 |           10 |            5000 |             3 |

|          11 | Eleven Chen   | 女           | 2016-06-03 13:50:28 |           10 |            3500 |             2 |

|          12 | Cherry Zhou   | 女           | 2016-06-03 13:50:28 |           10 |            5500 |             4 |

|          13 | Klause He     | 男           | 2016-06-03 13:50:28 |           10 |            4500 |             5 |

|          14 | Maven Ma      | 男           | 2016-06-03 13:50:28 |           10 |            4500 |             6 |

|          15 | Stephani Wang | 女           | 2016-06-03 13:50:28 |           10 |            5500 |             7 |

|          16 | Jerry Guo     | 男           | 2016-06-03 13:50:28 |           10 |            8500 |             1 |

|          17 | Gerardo Garza | 男           | 2016-06-03 13:50:28 |           10 |           25000 |             8 |

|          18 | Derek Wu      | 男           | 2016-06-03 13:50:32 |           10 |            5500 |             5 |

+-------------+---------------+--------------+---------------------+--------------+-----------------+---------------+

18 rows in set (0.00 sec)

技术分享


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

创建函数-根据ID获取员工姓名与员工工资

mysql> DELIMITER //  

mysql> CREATE FUNCTION GetEmployeeInformationByID(id INT)  

    -> RETURNS VARCHAR(300)  

    -> BEGIN  

    ->     RETURN(SELECT CONCAT(‘employee name:‘,employee_name,‘---‘,‘salary: ‘,employee_salary) FROM employees WHERE employee_id=id);  

    -> END//  

Query OK, 0 rows affected (0.00 sec)



查看:

mysql> select GetEmployeeInformationByID(1);

+-----------------------------------------+

| GetEmployeeInformationByID(1)           |

+-----------------------------------------+

| employee name:David Tian---salary: 7500 |

+-----------------------------------------+

1 row in set (0.00 sec)


mysql>



本文出自 “renzhiyuan@chinacfsc.com” 博客,谢绝转载!

谈谈MySQL的函数问题!