|
mysql5.0,存储过程学习中...
原来一直用mysql4.0,不过近日down了5.0的Forwindows试用,想学习一下新特性,主要是存储过程,不过很惭愧至今未成功:em16:
不知,是否有兄弟也在试用且成功的,希望能交流一下:em02:
雪中凌花 回复于：2004-07-23 01:10:11
mysql5.0支持存储过程了吗？
dragon76 回复于：2004-07-23 14:42:48
它的文档说这是5.0支持的新特性,而且在它的文档中也确实有这方面的说明,只是我的E文不太好一知半解的试了一段时间后以失败而告终!
雪中凌花 回复于：2004-07-24 07:41:04
我先去看看，
czw1413_cn 回复于：2004-07-26 13:48:14
没听说MYSQL支持SP
只见过一本书上专门说，不支持SP
henryzhou 回复于：2004-07-26 21:20:32
根据mysqlreferencemanual上写，5.0是支持StoredProcedures的
dragon76 回复于：2004-07-27 08:43:21
5.0的确已经支持存储过程了
在MySQL前几版的的开发计划中也有如此的说明,5.0将支持存储过程以及视图功能
dragon76 回复于：2004-07-27 10:55:03
今天找到了一个好贴,已经可以解决上面的问题了,和兄弟们一起分享一下
[以下内容转自程序员大本营2004-02-02]
PeekinginMySQL5.0alphaEnterpriseFunctional<Function&Procedure>
程序员大本营2004-02-02
MySQL5.0Alpha發佈,我們期待已久的CreateFunction和CreateProcedure的功能終於有了.詳細的資訊請參考MySQLAB的官方網站的新聞.早就想要的功能,出來了還不趕快試試....
本文以WindowsXPPROos為例<:)我可不是有意來替M$宣傳,只是我用的是Windows,沒有辦法啦>來說明,其它OS類同,先下載MySQL5.0,為了免除安裝,我們直接下載Withoutinstaller(unzipinC:\)的版本,到http://www.mysql.com/downloads/mysql-5.0.html
1.直接unzip後把其放到C:\下,並把rootDirectory改為mysql(不改也可,那在my.ini中要把其中的mysql改為你的實際名稱)
2.下面是my.ini的配置文件,如果你對配置不熟的話,你可以直接Copy過去存為my.ini後放到C:\windows下即可(我電腦的RAM=256)
========================================================================================================
#Examplemysqlconfigfile.
#Copythisfiletoc:\my.cnftosetglobaloptions
#
#Onecanusealllongoptionsthattheprogramsupports.
#Runtheprogramwith--helptogetalistofavailableoptions
#Thiswillbepassedtoallmysqlclients
[client]
#password=my_password
port=3306
#socket=MySQL
#Hereisentriesforsomespecificprograms
#Thefollowingvaluesassumeyouhaveatleast32Mram
#TheMySQLserver
[mysqld]
port=3306
#socket=MySQL
skip-locking
set-variable=key_buffer=16M
set-variable=max_allowed_packet=1M
set-variable=table_cache=64
set-variable=sort_buffer=512K
set-variable=net_buffer_length=8K
set-variable=myisam_sort_buffer_size=8M
server-id=1
#Uncommentthefollowingifyouwanttologupdates
#log-bin
#UncommentthefollowingrowsifyoumovetheMySQLdistributiontoanother
#location
#basedir=d:/mysql/
#datadir=d:/mysql/data/
#UncommentthefollowingifyouareNOTusingBDBtables
#skip-bdb
#UncommentthefollowingifyouareusingBDBtables
#set-variable=bdb_cache_size=4M
#set-variable=bdb_max_lock=10000
#UncommentthefollowingifyouareusingInnobasetables
innodb_data_file_path=ibdata1:400M
innodb_data_home_dir=C:/mysql/InnoDB/ibdata#InnoDB及ibdata的資料夾你要自己動手
innodb_log_group_home_dir=C:/mysql/InnoDB/iblogs#iblogs的資料夾同樣你要自己動手
innodb_log_arch_dir=C:/mysql/innodb/iblogs
set-variable=innodb_mirrored_log_groups=1
set-variable=innodb_log_files_in_group=3
set-variable=innodb_log_file_size=5M
set-variable=innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
innodb_log_archive=0
set-variable=innodb_buffer_pool_size=16M
set-variable=innodb_additional_mem_pool_size=2M
set-variable=innodb_file_io_threads=4
set-variable=innodb_lock_wait_timeout=50
[mysqldump]
quick
set-variable=max_allowed_packet=16M
[mysql]
no-auto-rehash
#RemovethenextcommentcharacterifyouarenotfamiliarwithSQL
#safe-updates
[isamchk]
set-variable=key_buffer=20M
set-variable=sort_buffer=20M
set-variable=read_buffer=2M
set-variable=write_buffer=2M
[myisamchk]
set-variable=key_buffer=20M
set-variable=sort_buffer=20M
set-variable=read_buffer=2M
set-variable=write_buffer=2M
[mysqlhotcopy]
interactive-timeout
[WinMySQLAdmin]
Server=C:/mysql/bin/mysqld-nt.exe
============================================================================
3.CreateInnoDBtabletablespace如果運行沒有問題,你應該可以看到如下內容:
==========================================================
C:\mysql\bin>mysqld--console
InnoDB:ThefirstspecifieddatafileC:\mysql\InnoDB\ibdata\ibdata1didnotexist:
InnoDB:anewdatabasetobecreated!
04011315:12:54InnoDB:SettingfileC:\mysql\InnoDB\ibdata\ibdata1sizeto400MB
InnoDB:Databasephysicallywritesthefilefull:wait...
InnoDB:ProgressinMB:100200300400
04011315:13:19InnoDB:LogfileC:\mysql\InnoDB\iblogs\ib_logfile0didnotexist:newtobecreated
InnoDB:SettinglogfileC:\mysql\InnoDB\iblogs\ib_logfile0sizeto5MB
InnoDB:Databasephysicallywritesthefilefull:wait...
04011315:13:19InnoDB:LogfileC:\mysql\InnoDB\iblogs\ib_logfile1didnotexist:newtobecreated
InnoDB:SettinglogfileC:\mysql\InnoDB\iblogs\ib_logfile1sizeto5MB
InnoDB:Databasephysicallywritesthefilefull:wait...
04011315:13:20InnoDB:LogfileC:\mysql\InnoDB\iblogs\ib_logfile2didnotexist:newtobecreated
InnoDB:SettinglogfileC:\mysql\InnoDB\iblogs\ib_logfile2sizeto5MB
InnoDB:Databasephysicallywritesthefilefull:wait...
InnoDB:Doublewritebuffernotfound:creatingnew
InnoDB:Doublewritebuffercreated
InnoDB:Creatingforeignkeyconstraintsystemtables
InnoDB:Foreignkeyconstraintsystemtablescreated
04011315:13:26InnoDB:Started;logsequencenumber00
mysqld:readyforconnections.
Version:'5.0.0-alpha-max-debug'socket:''port:3306
==========================================================
安裝Ok,進去看看吧,沒想到一進Mysql馬上就來了個下馬威,原來的''這個user不能用mysql的databases
=========================================================
C:\mysql\bin>mysql
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis2toserverversion:5.0.0-alpha-max-debug
Type'help;'or'\h'forhelp.Type'\c'toclearthebuffer.
mysql>usemysql
ERROR1044(42000):Accessdeniedforuser:''@'localhost'todatabase'mysql
==========================================================
沒有辦法,只有用root進去:
==========================================================
C:\mysql\bin>mysql-uroot-p
Enterpassword:
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis5toserverversion:5.0.0-alpha-max-debug
Type'help;'or'\h'forhelp.Type'\c'toclearthebuffer.
mysql>
==========================================================
:)root的密碼還是勤快一點把它改掉吧
4.體驗一下CreateFunction吧
CREATEFUNCTIONMyFunc(sCHAR(20))RETURNSCHAR(50)RETURNCONCAT('Hello',s,'DLL','!');
==========================================================
mysql>CREATEFUNCTIONMyFunc(sCHAR(20))RETURNSCHAR(50)RETURNCONCAT('Hello
',s,'.Lan','!');
QueryOK,0rowsaffected(0.00sec)
mysql>
mysql>selectMyFunc('Dennis');
+--------------------+
|MyFunc('Dennis')|
+--------------------+
|HelloDennis.Lan!|
+--------------------+
1rowinset(0.01sec)
mysql>
==========================================================
5.DropFunction
==========================================================
mysql>dropfunctionMyFunc;
QueryOK,0rowsaffected(0.00sec)
mysql>
==========================================================
6.Create一個Procedure試試吧!
CREATEPROCEDUREMyProc(OUTparam1INT)BEGINSELECTCOUNT(*)INTOparam1FROMsys_forms;END;
千萬記住,下面這樣子不行喲,我試了n次,每次都是錯誤,我還以為Mysql有問題呢,原來不是
==========================================================
mysql>usetest;
Databasechanged
mysql>CREATEPROCEDUREMyProc(OUTparam1INT)BEGINSELECTCOUNT(*)INTOparam
1FROMsys_forms;END;
ERROR1064(42000):YouhaveanerrorinyourSQLsyntax.Checkthemanualthat
correspondstoyourMySQLserverversionfortherightsyntaxtousenear'SELE
CTCOUNT(*)INTOparam1FROMsys_forms'atline1
==========================================================
正確的做法是:
==========================================================
mysql>delimiter|#因為你的Procedure或Function中難免會用到";"作為sqlstatement的結束符,所以還是請你把這個結束符號改一下吧,不然就會出現上面的error1064(42000)
mysql>CREATEPROCEDUREMyProc(OUTparam1INT)BEGINSELECTCOUNT(*)INTOparam1FROMmysql.user;END;
->|
QueryOK,0rowsaffected(0.00sec)
==========================================================
OK,你的成功了嗎?
強烈建議你先看看這個http://www.mysql.com/doc/en/CREATE_PROCEDURE.html
ProcedureCreate成功了,測試一下吧!
Step1:
==========================================================
mysql>CALLMyProc(@a)|
QueryOK,0rowsaffected(0.01sec)
==========================================================
Step2:
==========================================================
mysql>select@a;
->|
+------+
|@a|
+------+
|0|
+------+
1rowinset(0.00sec)
mysql>
==========================================================
來個完整的:
==========================================================
mysql>CREATEPROCEDUREMyProc(OUTparam1INT)BEGINSELECTCOUNT(*)INTOparam
1FROMmysql.user;END;
->|
QueryOK,0rowsaffected(0.00sec)
mysql>callMyProc(@a)|
QueryOK,0rowsaffected(0.03sec)
mysql>select@a|
+------+
|@a|
+------+
|4|
+------+
1rowinset(0.00sec)
==========================================================
7.DropProcedures:
這樣子做怎麼又不對呀?
==========================================================
mysql>dropproceduremyproc;
->|
ERROR1289(42000):PROCEDUREmyprocdoesnotexist
==========================================================
原來Windows下的MySQL在Procedure的問題上又區分起輩份的大小了,下面這樣才對啊!
==========================================================
mysql>dropprocedureMyProc|
QueryOK,0rowsaffected(0.00sec)
==========================================================
注:如果你在和應用程式連結的時候出現
Clientdoesnotsupportauthenticationprotocolrequestedbyserver;considerupgradingMySQLclient
請參考http://www.mysql.com/doc/en/Old_client.html
Enjoyityourself!
GoodLuck!
okeykid 回复于：2004-07-27 11:04:43
呵呵，我已經用上了，感覺挺好，不過好像還不是很成熟，用的是5.0.0beta版
dragon76 回复于：2004-07-27 11:05:28
或许你还会需要在MySQL库中建立proc这个表
CREATETABLEproc(
dbchar(64)binaryDEFAULT''NOTNULL,
namechar(64)binaryDEFAULT''NOTNULL,
typeenum('FUNCTION','PROCEDURE')NOTNULL,
specific_namechar(64)binaryDEFAULT''NOTNULL,
languageenum('SQL')DEFAULT'SQL'NOTNULL,
sql_data_accessenum('CONTAINS_SQL')DEFAULT'CONTAINS_SQL'NOTNULL,
is_deterministicenum('YES','NO')DEFAULT'NO'NOTNULL,
security_typeenum('INVOKER','DEFINER')DEFAULT'DEFINER'NOTNULL,
param_listblobDEFAULT''NOTNULL,
returnschar(64)DEFAULT''NOTNULL,
bodyblobDEFAULT''NOTNULL,
definerchar(77)binaryDEFAULT''NOTNULL,
createdtimestamp,
modifiedtimestamp,
sql_modeset(
'REAL_AS_FLOAT',
'PIPES_AS_CONCAT',
'ANSI_QUOTES',
'IGNORE_SPACE',
'NOT_USED',
'ONLY_FULL_GROUP_BY',
'NO_UNSIGNED_SUBTRACTION',
'NO_DIR_IN_CREATE',
'POSTGRESQL',
'ORACLE',
'MSSQL',
'DB2',
'MAXDB',
'NO_KEY_OPTIONS',
'NO_TABLE_OPTIONS',
'NO_FIELD_OPTIONS',
'MYSQL323',
'MYSQL40',
'ANSI',
'NO_AUTO_VALUE_ON_ZERO'
)DEFAULT0NOTNULL,
commentchar(64)binaryDEFAULT''NOTNULL,
PRIMARYKEY(db,name,type)
)comment='StoredProcedures';
okeykid 回复于：2004-07-27 12:55:36
有個問題
createproceduretest(isTablechar)
begin
select*fromisTable;
end;//
calltest("MyTableName");//
但提示databaseName.isTable不存在，MyTableName存在的啊，為什麼不能用變量來動態查詢數據庫啊
GreatMan 回复于：2004-08-02 12:50:34
表名好象不能通过变量来传递吧
时代之风 回复于：2004-10-25 08:28:17
我在mysql里执行这样的建表过程CREATETABLEauto_dir(
diridint(8)NOTNULLauto_increment,
useridint(8),
parentidint(8),
ifsonchar(2),
dirnamechar(100),
xuhaoint(10),
modchar(3),
PRIMARYKEY(dirid),
KEYxuhao(xuhao)
);
可是报错为ERROR1064(42000):YouhaveanerrorinyourSQLsyntax.Checkthemanualthat
correspondstoyourMySQLserverversionfortherightsyntaxtousenear'mod
char(3),
PRIMARYKEY(dirid),
KEYxuhao(xuhao)
)'atline8
这是怎么回事呢？请各位老大帮我看看谢谢你们哦我是新新新来的
wangfujun 回复于：2004-11-01 16:26:32
我的是安装班，装好好后，就直接把您的那个文件考到c:\windows下my.ini
然后试验。不好用。
aarlex 回复于：2005-01-03 13:54:26
有人嘗試在LINUX底下用TARBALL編譯安裝的嗎???
我用編譯的無法使用PROCEDURE.......必須使用LINUX的BIN檔案才能??
是不是configure的時候有特別的參數???
查看上一页
返回分类首页 返回96PC首页
查看下一页 |
