peeking in mysql 5.0 enterprise functional
© by dennis dll(丹舟) 2004.01
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:/mysql/bin>mysqld --console
innodb: the first specified data file c:/mysql/innodb/ibdata/ibdata1 did not exist:
innodb: a new database to be created!
040113 15:12:54 innodb: setting file c:/mysql/innodb/ibdata/ibdata1 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:/mysql/innodb/iblogs/ib_logfile0 did not exist: new to be created
innodb: setting log file c:/mysql/innodb/iblogs/ib_logfile0 size to 5 mb
innodb: database physically writes the file full: wait...
040113 15:13:19 innodb: log file c:/mysql/innodb/iblogs/ib_logfile1 did not exist: new to be created
innodb: setting log file c:/mysql/innodb/iblogs/ib_logfile1 size to 5 mb
innodb: database physically writes the file full: wait...
040113 15:13:20 innodb: log file c:/mysql/innodb/iblogs/ib_logfile2 did not exist: new to be created
innodb: setting log file c:/mysql/innodb/iblogs/ib_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:/mysql/bin>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:/mysql/bin>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!
