首页 > 代码库 > 谈谈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的函数问题!