mysql5.0,存储过程学习中...
时间：2007-02-17 来源：PHP爱好者
原来一直用mysql4.0,不过近日down了5.0的For windows试用,想学习一下新特性,主要是存储过程,不过很惭愧至今未成功 :em16:
不知,是否有兄弟也在试用且成功的,希望能交流一下 :em02:
雪中凌花 回复于：2004-07-23 01:10:11mysql 5.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根据mysql reference manual上写，5.0是支持Stored Procedures的
dragon76 回复于：2004-07-27 08:43:215.0的确已经支持存储过程了
在MySQL前几版的的开发计划中也有如此的说明,5.0将支持存储过程以及视图功能
dragon76 回复于：2004-07-27 10:55:03今天找到了一个好贴,已经可以解决上面的问题了,和兄弟们一起分享一下
[以下内容转自 程序员大本营 2004-02-02]
Peeking in MySQL5.0 alpha Enterprise Functional<Function & Procedure>
程序员大本营 2004-02-02
MySQL5.0 Alpha 發佈, 我們期待已久的 Create Function 和 Create Procedure 的功能終於有了. 詳細的資訊請參考 MySQL AB 的官方網站的 新聞. 早就想要的功能,出來了還不趕快試試....
本文以 Windows XP PRO os 為例< :) 我可不是有意來替 M$ 宣傳,只是我用的是 Windows,沒有辦法啦>來說明,其它OS 類同, 先下載 MySQL5.0, 為了免除安裝,我們直接下載 Without installer (unzip in C:) 的版本,到 http://www.mysql.com/downloads/mysql-5.0.html 下載.
1. 直接 unzip 後把其放到 C: 下, 並把 root Directory 改為 mysql(不改也可,那在 my.ini 中要把其中的 mysql 改為你的實際名稱)
2. 下面是 my.ini 的配置文件,如果你對配置不熟的話,你可以直接 Copy 過去存為 my.ini 後放到 C:windows 下即可 (我電腦的 RAM = 256)
========================================================================================================
# Example mysql config file.
# Copy this file to c:my.cnf to set global options
#
# One can use all long options that the program supports.
# Run the program with --help to get a list of available options
# This will be passed to all mysql clients
[client]
#password=my_password
port=3306
#socket=MySQL
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# The MySQL server
[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
# Uncomment the following if you want to log updates
#log-bin
# Uncomment the following rows if you move the MySQL distribution to another
# location
#basedir = d:/mysql/
#datadir = d:/mysql/data/
# Uncomment the following if you are NOT using BDB tables
#skip-bdb
# Uncomment the following if you are using BDB tables
#set-variable = bdb_cache_size=4M
#set-variable = bdb_max_lock=10000
# Uncomment the following if you are using Innobase tables
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
# Remove the next comment character if you are not familiar with SQL
#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. Create InnoDB table table space 如果咝袥]有問題,你應該可以看到如下內容:
==========================================================
C:mysqlbin>mysqld --console
InnoDB: The first specified data file C:mysqlInnoDBibdataibdata1 did not exist:
InnoDB: a new database to be created!
040113 15:12:54 InnoDB: Setting file C:mysqlInnoDBibdataibdata1 size to 400 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400
040113 15:13:19 InnoDB: Log file C:mysqlInnoDBiblogsib_logfile0 did not exist: new to be created
InnoDB: Setting log file C:mysqlInnoDBiblogsib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
040113 15:13:19 InnoDB: Log file C:mysqlInnoDBiblogsib_logfile1 did not exist: new to be created
InnoDB: Setting log file C:mysqlInnoDBiblogsib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
040113 15:13:20 InnoDB: Log file C:mysqlInnoDBiblogsib_logfile2 did not exist: new to be created
InnoDB: Setting log file C:mysqlInnoDBiblogsib_logfile2 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
040113 15:13:26 InnoDB: Started; log sequence number 0 0
mysqld: ready for connections.
Version: '5.0.0-alpha-max-debug' socket: '' port: 3306
==========================================================
安裝 Ok, 進去看看吧,沒想到一進 Mysql 馬上就來了個下馬威, 原來的 '' 這個 user 不能用 mysql 的 databases
=========================================================
C:mysqlbin>mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2 to server version: 5.0.0-alpha-max-debug
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> use mysql
ERROR 1044 (42000): Access denied for user: ''@'localhost' to database 'mysql
==========================================================
沒有辦法,只有用 root 進去:
==========================================================
C:mysqlbin>mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 5 to server version: 5.0.0-alpha-max-debug
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql>
==========================================================
:) root 的密碼還是勤快一點把它改掉吧
4. 體驗一下 Create Function 吧
CREATE FUNCTION MyFunc (s CHAR(20)) RETURNS CHAR(50) RETURN CONCAT('Hello ',s,' DLL',' !');
==========================================================
mysql> CREATE FUNCTION MyFunc (s CHAR(20)) RETURNS CHAR(50) RETURN CONCAT('Hello
',s,'.Lan',' !');
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select MyFunc('Dennis');
+--------------------+
| MyFunc('Dennis') |
+--------------------+
| Hello Dennis.Lan ! |
+--------------------+
1 row in set (0.01 sec)
mysql>
==========================================================
5. Drop Function
==========================================================
mysql> drop function MyFunc;
Query OK, 0 rows affected (0.00 sec)
mysql>
==========================================================
6. Create 一個 Procedure 試試吧!
CREATE PROCEDURE MyProc (OUT param1 INT) BEGIN SELECT COUNT(*) INTO param1 FROM sys_forms; END;
千萬記住,下面這樣子不行喲, 我試了 n 次, 每次都是錯誤, 我還以為 Mysql 有問題呢,原來不是
==========================================================
mysql> use test;
Database changed
mysql> CREATE PROCEDURE MyProc (OUT param1 INT) BEGIN SELECT COUNT(*) INTO param
1 FROM sys_forms; END;
ERROR 1064 (42000): 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 'SELE
CT COUNT(*) INTO param1 FROM sys_forms' at line 1
==========================================================
正確的做法是:
==========================================================
mysql> delimiter | # 因為你的 Procedure 或 Function中難免會用到 ";" 作為 sql statement 的結束符, 所以還是請你把這個結束符號改一下吧,不然就會出現上面的 error 1064(42000)
mysql> CREATE PROCEDURE MyProc (OUT param1 INT) BEGIN SELECT COUNT(*) INTO param1 FROM mysql.user; END;
-> |
Query OK, 0 rows affected (0.00 sec)
==========================================================
OK, 你的成功了嗎?
強烈建議你先看看這個 http://www.mysql.com/doc/en/CREATE_PROCEDURE.html
Procedure Create 成功了,測試一下吧!
Step 1:
==========================================================
mysql> CALL MyProc(@a)|
Query OK, 0 rows affected (0.01 sec)
==========================================================
Step 2:
==========================================================
mysql> select @a;
-> |
+------+
| @a |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
mysql>
==========================================================
來個完整的:
==========================================================
mysql> CREATE PROCEDURE MyProc (OUT param1 INT) BEGIN SELECT COUNT(*) INTO param
1 FROM mysql.user; END;
-> |
Query OK, 0 rows affected (0.00 sec)
mysql> call MyProc(@a)|
Query OK, 0 rows affected (0.03 sec)
mysql> select @a |
+------+
| @a |
+------+
| 4 |
+------+
1 row in set (0.00 sec)
==========================================================
7. Drop Procedures:
這樣子做怎麼又不對呀?
==========================================================
mysql> drop procedure myproc;
-> |
ERROR 1289 (42000): PROCEDURE myproc does not exist
==========================================================
原來 Windows 下的 MySQL 在 Procedure 的問題上又區分起輩份的大小了,下面這樣才對啊!
==========================================================
mysql> drop procedure MyProc|
Query OK, 0 rows affected (0.00 sec)
==========================================================
注: 如果你在和應用程式連結的時候出現
Client does not support authentication protocol requested by server; consider upgrading MySQL client
請參考 http://www.mysql.com/doc/en/Old_client.html 這里提供了詳細的解決方案.
Enjoy it yourself!
Good Luck!
okeykid 回复于：2004-07-27 11:04:43呵呵，我已經用上了，感覺挺好，不過好像還不是很成熟，用的是5.0.0beta版
dragon76 回复于：2004-07-27 11:05:28或许你还会需要在MySQL库中建立proc这个表
CREATE TABLE proc (
db char(64) binary DEFAULT '' NOT NULL,
name char(64) binary DEFAULT '' NOT NULL,
type enum('FUNCTION','PROCEDURE') NOT NULL,
specific_name char(64) binary DEFAULT '' NOT NULL,
language enum('SQL') DEFAULT 'SQL' NOT NULL,
sql_data_access enum('CONTAINS_SQL') DEFAULT 'CONTAINS_SQL' NOT NULL,
is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL,
security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL,
param_list blob DEFAULT '' NOT NULL,
returns char(64) DEFAULT '' NOT NULL,
body blob DEFAULT '' NOT NULL,
definer char(77) binary DEFAULT '' NOT NULL,
created timestamp,
modified timestamp,
sql_mode set(
'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'
) DEFAULT 0 NOT NULL,
comment char(64) binary DEFAULT '' NOT NULL,
PRIMARY KEY (db,name,type)
) comment='Stored Procedures';
okeykid 回复于：2004-07-27 12:55:36有個問題
create procedure test(isTable char)
begin
select * from isTable;
end;//
call test("MyTableName");//
但提示 databaseName.isTable不存在，MyTableName存在的啊，為什麼不能用變量來動態查詢數據庫啊
GreatMan 回复于：2004-08-02 12:50:34表名好象不能通过变量来传递吧
时代之风 回复于：2004-10-25 08:28:17我在mysql里执行这样的建表过程CREATE TABLE auto_dir (
dirid int(8) NOT NULL auto_increment,
userid int(8),
parentid int(8),
ifson char(2),
dirname char(100),
xuhao int(10),
mod char(3),
PRIMARY KEY (dirid),
KEY xuhao (xuhao)
);
可是报错为ERROR 1064 (42000): 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 'mod
char(3),
PRIMARY KEY (dirid),
KEY xuhao (xuhao)
)' at line 8
这是怎么回事呢？请各位老大帮我看看 谢谢你们哦 我是新新新来的
wangfujun 回复于：2004-11-01 16:26:32我的是安装班，装好好后，就直接把您的那个文件考到c:windows下my.ini
然后试验。不好用。
aarlex 回复于：2005-01-03 13:54:26有人嘗試在LINUX 底下用TAR BALL編譯安裝的嗎 ???
我用編譯的無法使用PROCEDURE.......必須使用LINUX 的BIN檔案才能 ??
是不是configure 的時候有特別的參數 ???
php爱好者站 http://www.phpfans.net PHP|MySQL|javascript|ajax|html.
不知,是否有兄弟也在试用且成功的,希望能交流一下 :em02:
雪中凌花 回复于：2004-07-23 01:10:11mysql 5.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根据mysql reference manual上写，5.0是支持Stored Procedures的
dragon76 回复于：2004-07-27 08:43:215.0的确已经支持存储过程了
在MySQL前几版的的开发计划中也有如此的说明,5.0将支持存储过程以及视图功能
dragon76 回复于：2004-07-27 10:55:03今天找到了一个好贴,已经可以解决上面的问题了,和兄弟们一起分享一下
[以下内容转自 程序员大本营 2004-02-02]
Peeking in MySQL5.0 alpha Enterprise Functional<Function & Procedure>
程序员大本营 2004-02-02
MySQL5.0 Alpha 發佈, 我們期待已久的 Create Function 和 Create Procedure 的功能終於有了. 詳細的資訊請參考 MySQL AB 的官方網站的 新聞. 早就想要的功能,出來了還不趕快試試....
本文以 Windows XP PRO os 為例< :) 我可不是有意來替 M$ 宣傳,只是我用的是 Windows,沒有辦法啦>來說明,其它OS 類同, 先下載 MySQL5.0, 為了免除安裝,我們直接下載 Without installer (unzip in C:) 的版本,到 http://www.mysql.com/downloads/mysql-5.0.html 下載.
1. 直接 unzip 後把其放到 C: 下, 並把 root Directory 改為 mysql(不改也可,那在 my.ini 中要把其中的 mysql 改為你的實際名稱)
2. 下面是 my.ini 的配置文件,如果你對配置不熟的話,你可以直接 Copy 過去存為 my.ini 後放到 C:windows 下即可 (我電腦的 RAM = 256)
========================================================================================================
# Example mysql config file.
# Copy this file to c:my.cnf to set global options
#
# One can use all long options that the program supports.
# Run the program with --help to get a list of available options
# This will be passed to all mysql clients
[client]
#password=my_password
port=3306
#socket=MySQL
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# The MySQL server
[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
# Uncomment the following if you want to log updates
#log-bin
# Uncomment the following rows if you move the MySQL distribution to another
# location
#basedir = d:/mysql/
#datadir = d:/mysql/data/
# Uncomment the following if you are NOT using BDB tables
#skip-bdb
# Uncomment the following if you are using BDB tables
#set-variable = bdb_cache_size=4M
#set-variable = bdb_max_lock=10000
# Uncomment the following if you are using Innobase tables
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
# Remove the next comment character if you are not familiar with SQL
#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. Create InnoDB table table space 如果咝袥]有問題,你應該可以看到如下內容:
==========================================================
C:mysqlbin>mysqld --console
InnoDB: The first specified data file C:mysqlInnoDBibdataibdata1 did not exist:
InnoDB: a new database to be created!
040113 15:12:54 InnoDB: Setting file C:mysqlInnoDBibdataibdata1 size to 400 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400
040113 15:13:19 InnoDB: Log file C:mysqlInnoDBiblogsib_logfile0 did not exist: new to be created
InnoDB: Setting log file C:mysqlInnoDBiblogsib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
040113 15:13:19 InnoDB: Log file C:mysqlInnoDBiblogsib_logfile1 did not exist: new to be created
InnoDB: Setting log file C:mysqlInnoDBiblogsib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
040113 15:13:20 InnoDB: Log file C:mysqlInnoDBiblogsib_logfile2 did not exist: new to be created
InnoDB: Setting log file C:mysqlInnoDBiblogsib_logfile2 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
040113 15:13:26 InnoDB: Started; log sequence number 0 0
mysqld: ready for connections.
Version: '5.0.0-alpha-max-debug' socket: '' port: 3306
==========================================================
安裝 Ok, 進去看看吧,沒想到一進 Mysql 馬上就來了個下馬威, 原來的 '' 這個 user 不能用 mysql 的 databases
=========================================================
C:mysqlbin>mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2 to server version: 5.0.0-alpha-max-debug
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> use mysql
ERROR 1044 (42000): Access denied for user: ''@'localhost' to database 'mysql
==========================================================
沒有辦法,只有用 root 進去:
==========================================================
C:mysqlbin>mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 5 to server version: 5.0.0-alpha-max-debug
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql>
==========================================================
:) root 的密碼還是勤快一點把它改掉吧
4. 體驗一下 Create Function 吧
CREATE FUNCTION MyFunc (s CHAR(20)) RETURNS CHAR(50) RETURN CONCAT('Hello ',s,' DLL',' !');
==========================================================
mysql> CREATE FUNCTION MyFunc (s CHAR(20)) RETURNS CHAR(50) RETURN CONCAT('Hello
',s,'.Lan',' !');
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select MyFunc('Dennis');
+--------------------+
| MyFunc('Dennis') |
+--------------------+
| Hello Dennis.Lan ! |
+--------------------+
1 row in set (0.01 sec)
mysql>
==========================================================
5. Drop Function
==========================================================
mysql> drop function MyFunc;
Query OK, 0 rows affected (0.00 sec)
mysql>
==========================================================
6. Create 一個 Procedure 試試吧!
CREATE PROCEDURE MyProc (OUT param1 INT) BEGIN SELECT COUNT(*) INTO param1 FROM sys_forms; END;
千萬記住,下面這樣子不行喲, 我試了 n 次, 每次都是錯誤, 我還以為 Mysql 有問題呢,原來不是
==========================================================
mysql> use test;
Database changed
mysql> CREATE PROCEDURE MyProc (OUT param1 INT) BEGIN SELECT COUNT(*) INTO param
1 FROM sys_forms; END;
ERROR 1064 (42000): 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 'SELE
CT COUNT(*) INTO param1 FROM sys_forms' at line 1
==========================================================
正確的做法是:
==========================================================
mysql> delimiter | # 因為你的 Procedure 或 Function中難免會用到 ";" 作為 sql statement 的結束符, 所以還是請你把這個結束符號改一下吧,不然就會出現上面的 error 1064(42000)
mysql> CREATE PROCEDURE MyProc (OUT param1 INT) BEGIN SELECT COUNT(*) INTO param1 FROM mysql.user; END;
-> |
Query OK, 0 rows affected (0.00 sec)
==========================================================
OK, 你的成功了嗎?
強烈建議你先看看這個 http://www.mysql.com/doc/en/CREATE_PROCEDURE.html
Procedure Create 成功了,測試一下吧!
Step 1:
==========================================================
mysql> CALL MyProc(@a)|
Query OK, 0 rows affected (0.01 sec)
==========================================================
Step 2:
==========================================================
mysql> select @a;
-> |
+------+
| @a |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
mysql>
==========================================================
來個完整的:
==========================================================
mysql> CREATE PROCEDURE MyProc (OUT param1 INT) BEGIN SELECT COUNT(*) INTO param
1 FROM mysql.user; END;
-> |
Query OK, 0 rows affected (0.00 sec)
mysql> call MyProc(@a)|
Query OK, 0 rows affected (0.03 sec)
mysql> select @a |
+------+
| @a |
+------+
| 4 |
+------+
1 row in set (0.00 sec)
==========================================================
7. Drop Procedures:
這樣子做怎麼又不對呀?
==========================================================
mysql> drop procedure myproc;
-> |
ERROR 1289 (42000): PROCEDURE myproc does not exist
==========================================================
原來 Windows 下的 MySQL 在 Procedure 的問題上又區分起輩份的大小了,下面這樣才對啊!
==========================================================
mysql> drop procedure MyProc|
Query OK, 0 rows affected (0.00 sec)
==========================================================
注: 如果你在和應用程式連結的時候出現
Client does not support authentication protocol requested by server; consider upgrading MySQL client
請參考 http://www.mysql.com/doc/en/Old_client.html 這里提供了詳細的解決方案.
Enjoy it yourself!
Good Luck!
okeykid 回复于：2004-07-27 11:04:43呵呵，我已經用上了，感覺挺好，不過好像還不是很成熟，用的是5.0.0beta版
dragon76 回复于：2004-07-27 11:05:28或许你还会需要在MySQL库中建立proc这个表
CREATE TABLE proc (
db char(64) binary DEFAULT '' NOT NULL,
name char(64) binary DEFAULT '' NOT NULL,
type enum('FUNCTION','PROCEDURE') NOT NULL,
specific_name char(64) binary DEFAULT '' NOT NULL,
language enum('SQL') DEFAULT 'SQL' NOT NULL,
sql_data_access enum('CONTAINS_SQL') DEFAULT 'CONTAINS_SQL' NOT NULL,
is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL,
security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL,
param_list blob DEFAULT '' NOT NULL,
returns char(64) DEFAULT '' NOT NULL,
body blob DEFAULT '' NOT NULL,
definer char(77) binary DEFAULT '' NOT NULL,
created timestamp,
modified timestamp,
sql_mode set(
'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'
) DEFAULT 0 NOT NULL,
comment char(64) binary DEFAULT '' NOT NULL,
PRIMARY KEY (db,name,type)
) comment='Stored Procedures';
okeykid 回复于：2004-07-27 12:55:36有個問題
create procedure test(isTable char)
begin
select * from isTable;
end;//
call test("MyTableName");//
但提示 databaseName.isTable不存在，MyTableName存在的啊，為什麼不能用變量來動態查詢數據庫啊
GreatMan 回复于：2004-08-02 12:50:34表名好象不能通过变量来传递吧
时代之风 回复于：2004-10-25 08:28:17我在mysql里执行这样的建表过程CREATE TABLE auto_dir (
dirid int(8) NOT NULL auto_increment,
userid int(8),
parentid int(8),
ifson char(2),
dirname char(100),
xuhao int(10),
mod char(3),
PRIMARY KEY (dirid),
KEY xuhao (xuhao)
);
可是报错为ERROR 1064 (42000): 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 'mod
char(3),
PRIMARY KEY (dirid),
KEY xuhao (xuhao)
)' at line 8
这是怎么回事呢？请各位老大帮我看看 谢谢你们哦 我是新新新来的
wangfujun 回复于：2004-11-01 16:26:32我的是安装班，装好好后，就直接把您的那个文件考到c:windows下my.ini
然后试验。不好用。
aarlex 回复于：2005-01-03 13:54:26有人嘗試在LINUX 底下用TAR BALL編譯安裝的嗎 ???
我用編譯的無法使用PROCEDURE.......必須使用LINUX 的BIN檔案才能 ??
是不是configure 的時候有特別的參數 ???
php爱好者站 http://www.phpfans.net PHP|MySQL|javascript|ajax|html.
相关阅读 更多 +
