mysql创建函数失败的解决

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/linzx0212/article/details/84884104

创建时异常日志:

Error Code: 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)

 

度娘了解决方案:

 

解决方法如下:
1. mysql> set global log_bin_trust_function_creators = 1;
2. 系统启动时 --log-bin-trust-function-creators=1
3. 在my.ini(linux下为my.conf)文件中 [mysqld] 标记后加一行内容为 log-bin-trust-function-creators=1 抄自度娘 https://zhidao.baidu.com/question/460606488.html
展开阅读全文

Mysql创建存储过程失败

06-24

delimiter //rnCREATE Procedure UpClass()rnbeginrndeclare YX_ID int;rndeclare F_ParentID varchar(100);/*父级分类*/rndeclare F_Path varchar(100);/*父级分类路径*/rndeclare F_Child int;/*父级分类的Child*/rndeclare F_PrevID int;/*父级分类的同深度分类上个ID*/rndeclare F_NextID int;/*父级分类的同深度分类下个ID*/rndeclare F_OrderID int; /*父级分类的排序ID*/rndeclare TempPath varchar(100); /*临时路径*/rndeclare TempNum int; /*临时int型*/rndeclare F_RootID int;rnselect F_ParentID=YX_ParentID,F_Path=YX_ParentPath,F_Child=YX_Child,F_RootID=YX_RootID,F_PrevID=YX_PrevID,F_NextID=YX_NextIDrn,F_OrderID=YX_OrderID from YX_productclass where YX_ID=YX_ID;rn /**找到父级分类下的所有分类数目**/rn declare F_NextClassPath varchar(100);rn set F_NextClassPath=F_Path+','+Convert(varchar(100),YX_ID);rn declare F_ChildNum int;rn select F_ChildNum=(select count(*) from YX_ProductClass where YX_ParentPath like F_NextClassPath+'%' and YX_RootID=F_RootID);rn set F_ChildNum=F_ChildNum+1;rn /******************************/rn /*找到父级分类的同深度分类的信息*/rn declare Prv_PrevID int;rn declare Prv_RootID int;rnif F_PrevID>0 thenrn /*该分类的相关信息*/rn declare NextClassPath varchar(100);/*下属分类的路径*/rn rn select NextClassPath=YX_ParentPath,Prv_RootID=YX_RootID from YX_ProductClass where YX_ID=F_PrevID;rn set NextClassPath=NextClassPath+','+Convert(varchar(100),F_PrevID);rn declare Prv_ChildNum int; /*上个分类的数目*/rn select Prv_ChildNum=(select count(*) from YX_ProductClass where YX_ParentPath like NextClassPath+'%' and YX_RootID=Prv_RootID);rn /*求出该分类的总数*/rn set Prv_ChildNum=Prv_ChildNum+1;rn rn (select Prv_PrevID=YX_PrevID from YX_ProductClass where YX_ID=F_PrevID);rn rn /*修改该分类的位置和上下相关ID*/rn update YX_ProductClass set YX_PrevID=YX_ID,YX_NextID=F_NextID,rn YX_OrderID=YX_OrderID+F_ChildNum where YX_ID=F_PrevID;rn /*修改该分类下的排序ID*/rn update YX_ProductClass set YX_OrderID=YX_OrderID+F_ChildNum rn where YX_ParentPath like NextClassPath+'%' and YX_RootID=Prv_RootID;rn /*************************/rn /*****************/rn /**修改该分类上级分类的nextID*/rn update YX_productclass set YX_NextID=YX_ID where YX_ID=Prv_PrevID;rn /*修改源分类ID*/rn set TempPath=F_Path+','+Convert(varchar(100),YX_ID);rn update YX_ProductClass set YX_PrevID=Prv_PrevID,YX_NextID=F_PrevID,rn YX_OrderID=YX_OrderID-Prv_ChildNum where YX_ID=YX_ID;rn /*修改该分类下的排序ID*/rn update YX_ProductClass set YX_OrderID=YX_OrderID-Prv_ChildNum rn where YX_ParentPath like TempPath+'%' and YX_RootID=F_RootID;rn /*************/rn if F_NextID >0 thenrn /*修改下个相临类别的PrevID*/rn Update YX_ProductClass set YX_PrevID=F_PrevID where YX_ID=F_NextID;rn end if;rn rn end if;rnend;//rnrn在rn /**找到父级分类下的所有分类数目**/rn declare F_NextClassPath varchar(100);rn set F_NextClassPath=F_Path+','+Convert(varchar(100),YX_ID);这块报错了,Mysql如何向MSsql一样使用Convertrn 论坛

oracle创建mysql的dblink 失败

08-15

两种方式:第一,①安装mysql odbc驱动,配置mysql数据源。rn ②配置listener.ora和tnsnames.ora,以及%oracle home%/hs/admin下增加对应的init.orarn 在listener.ora中添加如下代码rn (SID_DESC =rn (GLOBAL_DBNAME =psms) rn (SID_NAME = psms)rn (ORACLE_HOME = E:\oracle\product\10.2.0\db_1)rn (PROGRAM = hsodbc)rn )rn 在tnsnames.ora中添加如下代码:rn psms=rn (DESCRIPTION =rn (ADDRESS_LIST =rn (ADDRESS = (PROTOCOL = TCP)(HOST = IP地址)(PORT = 端口))rn )rn (CONNECT_DATA =rn (SERVICE_NAME = psms)rn (SID=psms)rn )rn )rn 创建initpsms.ora,内容如下:rn HS_FDS_CONNECT_INFO = psmsrn HS_FDS_TRACE_LEVEL = OFFrn ③创建dblinkrn create public database link mysqllinkrn connect to "用户名" identified by "密码"rn using rn 'psms';rn ④执行查询 :select * from "m_user"@mysqllink;rn 报错:rn[img=https://img-bbs.csdn.net/upload/201408/15/1408065538_394083.png][/img]rn第二,直接创建dblinkrncreate public database link mysqllinkrn connect to "用户名" identified by "密码"rn using rn ' (DESCRIPTION =rn (ADDRESS_LIST =rn (ADDRESS = (PROTOCOL = TCP)(HOST = IP地址)(PORT = 端口))rn )rn (CONNECT_DATA =rn (SERVER_NAME = psms)rn (SID=psms)rn )rn )';rn执行select * from "m_user"@mysqllink;rn[img=https://img-bbs.csdn.net/upload/201408/15/1408065689_249661.png][/img]rnrn我的mysql跟oracle(服务)都是装在本机,哪位高手能帮忙解答下吗?还有各处输入的用户名,密码,IP,端口所指代的是mysql的还是oracle的?rn 论坛

没有更多推荐了,返回首页