MySQL存储函数错误[Err] 1064的调试记录
来源:互联网 发布:java软件编程培训 编辑:程序博客网 时间:2024/04/28 10:19
一,朋友创建存储函数报错。
CREATE DEFINER=`root`@`localhost` FUNCTION `stuff`(
f_old varchar(1000),f_start int,f_length int,f_replace varchar(1000)
) RETURNS varchar(2000) CHARSET utf8
BEGIN
return replace(f_old,substring(f_old,f_start,f_length),f_replace);
END
[SQL] create FUNCTION f_Int2IP (ip bigint) RETURNS varchar(15)
BEGIN
DECLARE re varchar(15) default '';
SELECT concat('.',CAST(ip/id as char)),ip%id into re,ip
from(
SELECT 16777216 as id
UNION ALL SELECT 65536
UNION ALL SELECT 256
UNION ALL SELECT 1) a;
set
RETURN STUFF(re,1,1,'');
END
报错如下:
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar)),ip%id into re,ip
from(
SELECT 16777216 as id
UNION ALL SEL' at line 4
二、查看创建函数的功能是否开启:
先看下,log_bin_trust_function_creators有没有开启
mysql> show variables like '%func%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
1 row in set (0.00 sec)
如果Value处值为OFF,则需将其开启。
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>
三,分析
mysql存储函数,需要特殊的间隔符来区分,还要用DELIMITER来标示,还有select ... into ... 已经赋值了,set就是多余的,所以修改如下:
DELIMITER $$
DROP FUNCTION IF EXISTS test.stuff$$
CREATE FUNCTION test.`stuff`(
f_old VARCHAR(1000),f_start INT,f_length INT,f_replace VARCHAR(1000)
) RETURNS VARCHAR(2000)
BEGIN
RETURN REPLACE(f_old,SUBSTRING(f_old,f_start,f_length),f_replace);
END$$
DELIMITER $$
DROP FUNCTION IF EXISTS test.f_Int2IP$$
CREATE FUNCTION `test`.`f_Int2IP`(ip BIGINT)
RETURNS VARCHAR(2000)
BEGIN
DECLARE re VARCHAR(2000) DEFAULT '';
SELECT CONCAT('.',CAST(ip/id AS CHAR)) AS restr,ip%id INTO re ,ip
FROM(
SELECT 16777216 AS id
UNION ALL SELECT 65536
UNION ALL SELECT 256
UNION ALL SELECT 1) a;
RETURN stuff(re,1,1,',');
END$$
DELIMITER ;
四,执行如下,都OK。
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS test.stuff$$
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE FUNCTION test.`stuff`(
-> f_old VARCHAR(1000),f_start INT,f_length INT,f_replace VARCHAR(1000)
-> ) RETURNS VARCHAR(2000)
-> BEGIN
-> RETURN REPLACE(f_old,SUBSTRING(f_old,f_start,f_length),f_replace);
-> END$$
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS test.f_Int2IP$$
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE FUNCTION `test`.`f_Int2IP`(ip BIGINT)
-> RETURNS VARCHAR(2000)
-> BEGIN
-> DECLARE re VARCHAR(2000) DEFAULT '';
-> SELECT CONCAT('.',CAST(ip/id AS CHAR)) AS restr,ip%id INTO re ,ip
-> FROM(
-> SELECT 16777216 AS id
-> UNION ALL SELECT 65536
-> UNION ALL SELECT 256
-> UNION ALL SELECT 1) a;
-> RETURN stuff(re,1,1,',');
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql>
后补一下带int的函数
delimiter $$
CREATE FUNCTION first_func(param1 varchar(5),parmam2 varchar(5),param3 varchar(10))
RETURNS TINYINT
BEGIN
RETURN 1;
END
PS:mysql的存储函数或者存储过程么有直接可以调试的工具,所以需要手工仔细分析查看,应用中尽量少用存储过程或者存储函数。
CREATE DEFINER=`root`@`localhost` FUNCTION `stuff`(
f_old varchar(1000),f_start int,f_length int,f_replace varchar(1000)
) RETURNS varchar(2000) CHARSET utf8
BEGIN
return replace(f_old,substring(f_old,f_start,f_length),f_replace);
END
[SQL] create FUNCTION f_Int2IP (ip bigint) RETURNS varchar(15)
BEGIN
DECLARE re varchar(15) default '';
SELECT concat('.',CAST(ip/id as char)),ip%id into re,ip
from(
SELECT 16777216 as id
UNION ALL SELECT 65536
UNION ALL SELECT 256
UNION ALL SELECT 1) a;
set
RETURN STUFF(re,1,1,'');
END
报错如下:
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar)),ip%id into re,ip
from(
SELECT 16777216 as id
UNION ALL SEL' at line 4
二、查看创建函数的功能是否开启:
先看下,log_bin_trust_function_creators有没有开启
mysql> show variables like '%func%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
1 row in set (0.00 sec)
如果Value处值为OFF,则需将其开启。
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>
三,分析
mysql存储函数,需要特殊的间隔符来区分,还要用DELIMITER来标示,还有select ... into ... 已经赋值了,set就是多余的,所以修改如下:
DELIMITER $$
DROP FUNCTION IF EXISTS test.stuff$$
CREATE FUNCTION test.`stuff`(
f_old VARCHAR(1000),f_start INT,f_length INT,f_replace VARCHAR(1000)
) RETURNS VARCHAR(2000)
BEGIN
RETURN REPLACE(f_old,SUBSTRING(f_old,f_start,f_length),f_replace);
END$$
DELIMITER $$
DROP FUNCTION IF EXISTS test.f_Int2IP$$
CREATE FUNCTION `test`.`f_Int2IP`(ip BIGINT)
RETURNS VARCHAR(2000)
BEGIN
DECLARE re VARCHAR(2000) DEFAULT '';
SELECT CONCAT('.',CAST(ip/id AS CHAR)) AS restr,ip%id INTO re ,ip
FROM(
SELECT 16777216 AS id
UNION ALL SELECT 65536
UNION ALL SELECT 256
UNION ALL SELECT 1) a;
RETURN stuff(re,1,1,',');
END$$
DELIMITER ;
四,执行如下,都OK。
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS test.stuff$$
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE FUNCTION test.`stuff`(
-> f_old VARCHAR(1000),f_start INT,f_length INT,f_replace VARCHAR(1000)
-> ) RETURNS VARCHAR(2000)
-> BEGIN
-> RETURN REPLACE(f_old,SUBSTRING(f_old,f_start,f_length),f_replace);
-> END$$
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS test.f_Int2IP$$
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE FUNCTION `test`.`f_Int2IP`(ip BIGINT)
-> RETURNS VARCHAR(2000)
-> BEGIN
-> DECLARE re VARCHAR(2000) DEFAULT '';
-> SELECT CONCAT('.',CAST(ip/id AS CHAR)) AS restr,ip%id INTO re ,ip
-> FROM(
-> SELECT 16777216 AS id
-> UNION ALL SELECT 65536
-> UNION ALL SELECT 256
-> UNION ALL SELECT 1) a;
-> RETURN stuff(re,1,1,',');
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql>
后补一下带int的函数
delimiter $$
CREATE FUNCTION first_func(param1 varchar(5),parmam2 varchar(5),param3 varchar(10))
RETURNS TINYINT
BEGIN
RETURN 1;
END
PS:mysql的存储函数或者存储过程么有直接可以调试的工具,所以需要手工仔细分析查看,应用中尽量少用存储过程或者存储函数。
0 0
- MySQL存储函数错误[Err] 1064的调试记录
- MySQL存储函数错误[Err] 1064的调试记录
- mysql 存储过程错误调试记录
- 【数据库-MySql】函数 [err- 1064]
- MySQL err[1005]错误的解决总结
- MySql错误:[Err] 1292
- MySql 存储过程 [Err] 1270
- MySQL里面的错误——[Err] 1113
- [Err]1064-数据库错误
- 记录每次调试的错误
- mysql xxx.err文件内容记录
- ml.err 错误的解决方案
- MYSQL ERR 1055 ONLY_FULL_GROUP_BY 错误解决
- mysql导出到文件错误(err 1290)
- my sql里面的1064错误——[Err] 1064
- MySQL 数据批量恢复时 [Err] 2006 - MySQL server has gone away 错误的解决
- MySQL 数据批量恢复时 [Err] 2006 - MySQL server has gone away 错误的解决
- 记录MySQL遇到的错误
- poj 1606 bfs方法
- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- TextView属性大全
- Emoji表情符号录入MySQL数据库报错的解决方案
- 游承超:手机钢化玻璃膜是手机屏幕的保护神(13P)
- MySQL存储函数错误[Err] 1064的调试记录
- Net框架引用关系,Bll,Dal
- On iPad, UIImagePickerController must be presented via UIPopoverController
- 主对话框,上面有四个属性页
- 持续集成2---优化邮件格式
- 浩易南:少用信用卡,少逛淘宝,多用现金
- 用MySQL里的Rand()生成 不连续重复 的随机数年龄以及随机姓名字符串
- 第八周项目3(1)——分数类中的运算符重载
- laravel sql操作