首頁猿問在mysql中創(chuàng)建函數(shù)不成功（確定...

在mysql中創(chuàng)建函數(shù)不成功（確定能創(chuàng)建函數(shù)）?

MySQL

30秒到達(dá)戰(zhàn)場(chǎng) 2019-02-07 11:07:21

在mysql中創(chuàng)建函數(shù)不成功（確定能創(chuàng)建函數(shù)）CREATE function SplitString( String varchar(2048), SplitChar char)returns res table( Value varchar(128), vindex int)begin declare index int,unit varchar(128),inext int,len int,i int; set index=1; set i=1; set len=len(String); while index<=len begin set inext=charindex(SplitChar,String,index); if inext=0 set inext=len+1; if inext>index begin set unit=ltrim(rtrim(substring(String,index,inext-index))); if unit<>'' begin insert into res (value,vindex) values (unit,i); set i=i+1; end; end; set index=inext+1; end; return;end;源代碼來自于http://www.cnblogs.com/lucc/archive/2009/02/14/1390384.html截個(gè)圖給大俠們看看，，

查看完整描述

2 回答

暮色呼如

TA貢獻(xiàn)1853條經(jīng)驗(yàn) 獲得超9個(gè)贊

在使用MySQL數(shù)據(jù)庫時(shí)，有時(shí)會(huì)遇到MySQL函數(shù)不能創(chuàng)建的情況。

出錯(cuò)信息大致類似：

ERROR 1418 (HY000): 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)ERROR 1418 (HY000): 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)

MySQL函數(shù)不能創(chuàng)建，是未開啟功能：

123456789101112131415161718 mysql> show variables like '%func%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin_trust_function_creators | OFF | +---------------------------------+-------+ 1 row in set (0.00 sec) 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>

反對(duì) 回復(fù) 2019-02-27

繁花不似錦

TA貢獻(xiàn)1851條經(jīng)驗(yàn) 獲得超4個(gè)贊

你那個(gè)是 SQL Server 特有的表值函數(shù)。

也就是一個(gè)函數(shù)，返回一個(gè)結(jié)果集合的。

MySQL 好像是不支持表值函數(shù)的樣子。（現(xiàn)在最新的版本支持不支持，你需要去看看文檔了）

你可以嘗試修改成存儲(chǔ)過程返回結(jié)果集的處理。

1234567891011121314151617 DELIMITER //CREATE DEFINER=`root`@`%` PROCEDURE testProc()BEGIN SELECT 'Hello 1' AS A, 'World 1' AS B UNION ALL SELECT 'Hello 2' AS A, 'World 2' AS B;END //DELIMITER ; mysql> call testProc();+---------+---------+| A | B |+---------+---------+| Hello 1 | World 1 || Hello 2 | World 2 |+---------+---------+2 rows in set (0.00 sec)Query OK, 0 rows affected (0.01 sec)