本文共 8671 字,大约阅读时间需要 28 分钟。
本文以 Windows XP PRO os 為例< :) 我可不是有意來替 M$ 宣傳,只是我用的是 Windows,沒有辦法啦>來說明,其它OS 類同, 先下載 MySQL5.0, 為了免除安裝,我們直接下載 Without installer (unzip in C:) 的版本,到 http://www.my.com/downloads/my-5.0.html 下載.
1. 直接 unzip 後把其放到 C: 下, 並把 root Directory 改為 my(不改也可,那在 my.ini 中要把其中的 my 改為你的實際名稱)
2. 下面是 my.ini 的配置文件,如果你對配置不熟的話,你可以直接 Copy 過去存為 my.ini 後放到 C:windows 下即可 (我電腦的 RAM = 256)
========================================================================================================# Example my 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 my clients
[client]#password=my_passwordport=3306#socket=MySQL# Here is entries for some specific programs
# The following values assume you have at least 32M ram# The MySQL server
[myd]port=3306#socket=MySQLskip-lockingset-variable = key_buffer=16Mset-variable = max_allowed_packet=1Mset-variable = table_cache=64set-variable = sort_buffer=512Kset-variable = net_buffer_length=8Kset-variable = myisam_sort_buffer_size=8Mserver-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:/my/#datadir = d:/my/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:400Minnodb_data_home_dir = C:/my/InnoDB/ibdata # InnoDB 及 ibdata 的資料夾你要自己動手innodb_log_group_home_dir = C:/my/InnoDB/iblogs # iblogs 的資料夾同樣你要自己動手innodb_log_arch_dir = C:/my/innodb/iblogsset-variable = innodb_mirrored_log_groups=1set-variable = innodb_log_files_in_group=3set-variable = innodb_log_file_size=5Mset-variable = innodb_log_buffer_size=8Minnodb_flush_log_at_trx_commit=1innodb_log_archive=0set-variable = innodb_buffer_pool_size=16Mset-variable = innodb_additional_mem_pool_size=2Mset-variable = innodb_file_io_threads=4set-variable = innodb_lock_wait_timeout=50[mydump]
quickset-variable = max_allowed_packet=16M[my]
no-auto-rehash# Remove the next comment character if you are not familiar with SQL#safe-updates[isamchk]
set-variable = key_buffer=20Mset-variable = sort_buffer=20Mset-variable = read_buffer=2Mset-variable = write_buffer=2M[myisamchk]
set-variable = key_buffer=20Mset-variable = sort_buffer=20Mset-variable = read_buffer=2Mset-variable = write_buffer=2M[myhotcopy]
interactive-timeout[WinMySQLAdmin]Server=C:/my/bin/myd-nt.exe============================================================================3. Create InnoDB table table space 如果咝袥]有問題,你應該可以看到如下內容:
==========================================================C:myin>myd --console
InnoDB: The first specified data file C:myInnoDBibdataibdata1 did not exist:InnoDB: a new database to be created!040113 15:12:54 InnoDB: Setting file C:myInnoDBibdataibdata1 size to 400 MBInnoDB: Database physically writes the file full: wait...InnoDB: Progress in MB: 100 200 300 400040113 15:13:19 InnoDB: Log file C:myInnoDBiblogsib_logfile0 did not exist: new to be createdInnoDB: Setting log file C:myInnoDBiblogsib_logfile0 size to 5 MBInnoDB: Database physically writes the file full: wait...040113 15:13:19 InnoDB: Log file C:myInnoDBiblogsib_logfile1 did not exist: new to be createdInnoDB: Setting log file C:myInnoDBiblogsib_logfile1 size to 5 MBInnoDB: Database physically writes the file full: wait...040113 15:13:20 InnoDB: Log file C:myInnoDBiblogsib_logfile2 did not exist: new to be createdInnoDB: Setting log file C:myInnoDBiblogsib_logfile2 size to 5 MBInnoDB: Database physically writes the file full: wait...InnoDB: Doublewrite buffer not found: creating newInnoDB: Doublewrite buffer createdInnoDB: Creating foreign key constraint system tablesInnoDB: Foreign key constraint system tables created040113 15:13:26 InnoDB: Started; log sequence number 0 0myd: ready for connections.Version: '5.0.0-alpha-max-debug' socket: '' port: 3306==========================================================安裝 Ok, 進去看看吧,沒想到一進 My 馬上就來了個下馬威, 原來的 '' 這個 user 不能用 my 的 databases
=========================================================C:myin>myWelcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 2 to server version: 5.0.0-alpha-max-debugType 'help;' or 'h' for help. Type 'c' to clear the buffer.
my> use my
ERROR 1044 (42000): Access denied for user: ''@'localhost' to database 'my==========================================================沒有辦法,只有用 root 進去:
==========================================================
C:myin>my -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-debugType 'help;' or 'h' for help. Type 'c' to clear the buffer.
my>
==========================================================
:) root 的密碼還是勤快一點把它改掉吧
4. 體驗一下 Create Function 吧
CREATE FUNCTION MyFunc (s CHAR(20)) RETURNS CHAR(50) RETURN CONCAT('Hello ',s,' DLL',' !');
==========================================================
my> CREATE FUNCTION MyFunc (s CHAR(20)) RETURNS CHAR(50) RETURN CONCAT('Hello',s,'.Lan',' !');Query OK, 0 rows affected (0.00 sec)my>
my> select MyFunc('Dennis');
+--------------------+| MyFunc('Dennis') |+--------------------+| Hello Dennis.Lan ! |+--------------------+1 row in set (0.01 sec)my>
==========================================================
5. Drop Function==========================================================
my> drop function MyFunc;Query OK, 0 rows affected (0.00 sec)my>
==========================================================
6. Create 一個 Procedure 試試吧!
CREATE PROCEDURE MyProc (OUT param1 INT) BEGIN SELECT COUNT(*) INTO param1 FROM sys_forms; END;
千萬記住,下面這樣子不行喲, 我試了 n 次, 每次都是錯誤, 我還以為 My 有問題呢,原來不是
==========================================================
my> use test;
Database changedmy> CREATE PROCEDURE MyProc (OUT param1 INT) BEGIN SELECT COUNT(*) INTO param1 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 'SELECT COUNT(*) INTO param1 FROM sys_forms' at line 1==========================================================
正確的做法是:
==========================================================my> delimiter | # 因為你的 Procedure 或 Function中難免會用到 ";" 作為 statement 的結束符, 所以還是請你把這個結束符號改一下吧,不然就會出現上面的 error 1064(42000)my> CREATE PROCEDURE MyProc (OUT param1 INT) BEGIN SELECT COUNT(*) INTO param1 FROM my.user; END;
-> |Query OK, 0 rows affected (0.00 sec)==========================================================
OK, 你的成功了嗎?
強烈建議你先看看這個 http://www.my.com/doc/en/CREATE_PROCEDURE.html
Procedure Create 成功了,測試一下吧!
Step 1:==========================================================my> CALL MyProc(@a)|
Query OK, 0 rows affected (0.01 sec)==========================================================Step 2:
==========================================================my> select @a;
-> |+------+| @a |+------+| 0 |+------+1 row in set (0.00 sec)my>
==========================================================
來個完整的:
==========================================================
my> CREATE PROCEDURE MyProc (OUT param1 INT) BEGIN SELECT COUNT(*) INTO param
1 FROM my.user; END;-> |Query OK, 0 rows affected (0.00 sec)my> call MyProc(@a)|
Query OK, 0 rows affected (0.03 sec)my> select @a |
+------+| @a |+------+| 4 |+------+1 row in set (0.00 sec)==========================================================
7. Drop Procedures:
這樣子做怎麼又不對呀?==========================================================
my> drop procedure myproc;
-> |ERROR 1289 (42000): PROCEDURE myproc does not exist==========================================================
原來 Windows 下的 MySQL 在 Procedure 的問題上又區分起輩份的大小了,下面這樣才對啊!
==========================================================
my> drop procedure MyProc|
Query OK, 0 rows affected (0.00 sec)==========================================================
注: 如果你在和應用程式連結的時候出現
Client does not support authentication protocol requested by server; consider upgrading MySQL client
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10294527/viewspace-122826/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10294527/viewspace-122826/