delphi mysql

用Delphi做过数据库编程的朋友肯定熟悉Query控件，这个控件实现的功能是执行一条SQL语句或一个SQL脚本，在我们进行数据库开发中使用的频率非常高。笔者在多年的使用过程中发现用好这个控件有两点要非常注意。 第一点是：区分好Query控件的Open方法和ExecSQL方法。这两个方法都可以实现执行SQL语句，但要根据不同情况分别使用。如果这条SQL语句将返回一个结果集，必须使用Open方法，如果不返回一个结果集，则要使用ExecSQL方法。例如： …… Query1:Tquery Query2:Tquery …… Query1.Close; Query1.SQL.Clear; Query1.SQL.Add('select * from AA'); Query1.Open; …… Query2.Close; Query2.SQL.Clear; Query2.SQL.Add('delete AA'); Query2.ExecSQL; …… 上述的例子中，Query1所执行的SQL语句将返回一个结果集，因此必须用Open方法；而Query2所执行的是一条删除表记录语句，不返回结果集，因此用ExecSQL方法。 第二点是：如果Query控件用Open方法执行SQL语句，并且所用的SQL语句访问的是一张或几张频繁使用的表，在执行完SQL语句后，一定要调用SQL的FetchAll方法，能大大地减少死锁发生的概率。例如： …… Query1:Tquery …… Query1.Close; Query1.SQL.Clear; Query1.SQL.Add('select * from AA'); Query1.Open; Query1.FetchAll; ……在上述的例子中，如果AA是一张被频繁访问的表，在对这个表执行这一条select语句的同时，如果恰好有其他人对这张表执行删除或更新操作，便有可能发生死锁。Query1.FetchAll这条语句实现的功能是释放加在表AA上的锁，这样死锁的发生概率可以大大减少。避免死锁，对我们将来进行大型数据库开发尤为重要。"

看你是使用什么组件了，现在Delphi连接使用mysql数据库，最简单的就是 Delphi连接mysql的一个三方控件很好用的，，Delphi盒子和很多关于Delphi下载的网站都有下载的你跟我遇到的问题是一样，我今天才解决了这个问题
用ADOconnection，但是ADO没有mysql的驱动，你要去下载一个mysql.dll，放在bin目录，直接use connection stringTADOConnection的ConnectionString = 'DRIVER={MySQL ODBC 3.51
Driver};SERVER=MySQL数据库服务器;DATABASE=数据库名字;USER=用户
名;PASSWORD=密码;OPTION=;'DRIVER={MySQL ODBC 3.51
Driver};SERVER=192.168.1.22;DATABASE=rule;USER=WJH;PASSWORD=;OPTION=;就可以连上了，我就是这么连上的！

LIBMYSQL.DLL------------You can also access a MySQL server using the libmysql.dll library. Thisfile doesn't come with the standard MySQL installation, but you candownload it from: http://www.fichtner.net/delphi/mysql.delphi.phtmlYou should also download the mysql.pas file which is a Delphi a unitthat contains the type declarations for using this library. You need toinclude this unit in the uses clause of any unit from which you want tocall the functions contained in the library.The following example opens a connection to MySql server, opens adatabase, performs a query and stores the result in a StringGrid, andfinally closes the connection. uses ..., mysql; procedure TForm1.Button1Click(Sender: TObject); var mysqlcon: TMySQL; // MySQL-connection structure presults: pmysql_res; // Pointer to a results structure prow: pmysql_row; // Pointer to a row structure pfields: PMYSQL_FIELDS; // Pointer to a fields array i, j: Integer; // Counters begin // Connect to the server mysql_connect(@mysqlcon, 'localhost', 'root', ''); if mysqlcon.net.last_errno <> 0 then begin ShowMessage (Trim(mysqlcon.net.last_error)); exit; end; // Open the mysql database if mysql_select_db(@mysqlcon, 'mysql') <> 0 then begin mysql_close(@mysqlcon); // Disconnect ShowMessage('Couldn''t open mysql database'); exit; end; presults:= nil; try // Send the query to the server and get the results mysql_query(@mysqlcon, 'SELECT * FROM user'); presults := mysql_store_result(@mysqlcon); // Set the size of the grid StringGrid1.ColCount := presults^.field_count; StringGrid1.RowCount := presults^.row_count + 1; // Fill the grid header with the fields' names pfields := presults^.fields; for j := to presults^.field_count - do StringGrid1.Cells[j, ] := pfields^[j].name; // Fill the grid for i := 1 to presults^.row_count do begin prow := mysql_fetch_row(presults); for j := 0 to presults^.field_count -1 do StringGrid1.Cells[j, i]:= prow^[j]; end; finally mysql_free_result(presults); // Release memory mysql_close(@mysqlcon); // Disconnect end; end;As you can see, it's quite complicated, and this constitutes the main(and I would say only, but very important) disadvantage of using thelibmysql.dll library. The advantages are that you have full control,that it's faster than using ODBC, and that it doesn't requiere the BDE.It would be nice to have a TDataset descendant that encapsulates thelibmysql.dll API...
LIBMYSQL.DLL -----------您也可以访问MySQL服务器使用的libmysql.dll库。 Thisfile不标准的MySQL安装，但你candownload从http://www.fichtner.net/delphi/mysql.delphi.phtmlYou还应该下载mysql.pas的文件，这是一个Delphi一个unitthat的包含使用这个库的类型声明。您需要toinclude，这个单位的使用条款，任何单位要从其中tocall包含的功能库。下面的例子中，打开一个连接到MySQL服务器，打开adatabase，执行查询和存储的StringGrid的结果在，andfinally关闭连接。用途，mysql的程序TForm1.Button1Click（发件人TObject的）VAR mysqlcon：TMySQL的的/ / MySQL的连接结构presults：pmysql_res; / /指针业绩结构船头：pmysql_row / /行结构的指针如果mysqlcon.net.last_errno的，J：整数pfields：PMYSQL_FIELDS; / /指针的领域阵列I / /计数器开始/ /连接到服务器MYSQL_CONNECT（@ mysqlcon，'localhost'的'根'，''）; <> 0，然后开始'绂诲紑镞堕棿搴斿ぇ浜庡叆浣忔椂闂达紒（修剪（mysqlcon.net.last_error））;退出;结束; / /打开mysql数据库mysql_select_db（@ mysqlcon，'mysql的'）<> 0，则mysql_close（@ mysqlcon）; / /开始不能公开的mysql数据库'断开'绂诲紑镞堕棿搴斿ぇ浜庡叆浣忔椂闂达紒（）退出;结束; presults：=零; / /查询发送到服务器，并得到的结果，请求mysql_query（@ mysqlcon，“SELECT * FROM用户）; presults = mysql_store_result（@ mysqlcon）; / /设置电网StringGrid1.ColCount的大小：= presults ^。field_count的StringGrid1.RowCount：= presults ^。ROW_COUNT + 1 / /填充的网格头字段的名称pfields的：对于j = presults ^领域;：= 0到presults ^ -1。field_count的做的StringGrid1.Cells [J，0]：= pfields ^ [J]。名; / /填充i的网格：= 1 presults的^。ROW_COUNT开始船头：= mysql_fetch_row的（presults）在J：= 0到presults ^ -1。field_count的做StringGrid1.Cells，[J]：=船头^ [J];结束;终于了mysql_free_result（presults）; / /释放内存则mysql_close（@ mysqlcon）在/ /断开年底结束，正如你可以看到，它是相当复杂，这构成了主要的（我会说，但很重要的）的缺点使用thelibmysql.dll库的。其优点是可以完全控制它的速度比使用ODBC，，它不requiere的BDE.It将是不错的一个封装thelibmysql.dll API的的TDataSet的传人，...

如何在delphi中设计mysql的数据连接
-- :使用delphi连接mysql做成一个二级服务管理系统应该是中小数据管理模式的一种比较理想的应用，
.使用delphi的客户端用户界面比采用浏览器方式的界面更可以增强客户端的易用性，功能更全面，
.使用mysql的服务器端软件可以节约开支（对不能使用盗版软件而言），功能也较强大，是现在流行的数据库管理软件，会使用的人很多。由于delphi本身提供的mysql连接方式只能支持mysql3.以下版本的mysql，故对于现在普遍使用的5.以上的版本无法提供支持，在使用进将会得到“unable to load libmysql”的错误提示。对于该问题的解决有几种方式，目前我使用的是一种用dbxopenmysql50.dll替换掉delphi本身自带的dbexpmysql.dll。进行替换的两种方式
１。直接将下载的dbxopenmysql50.dll改名为dbexpmysql.dll存到C:\Program Files\Borland\Delphi7\Bin，对原文件进行替换，然后在程序设计中还要修改connction下的的GetDriverFunc改为getSQLDriverMYSQL50。.将dbxopenmysql50.dll拷贝到用户设计目录，然后在程序设计中将connection下的libraryname改为dbxopenmysql50.dll，GetDriverFunc改为getSQLDriverMYSQL50。示例程序及源代码：
先在程序中添加控件simpledataset1、datasource1、dbgrid1
edit1 输入入主机名
edit2 输入端口（此外此功能无效，因为无法对端口进行更改）
edit3 数据库名
edit4 连接mysql数据库的用户名
edit5 连接mysql数据库的密码
edit6 表名设置button1 的caption为退出
设置button2 的caption为连接
设置datasource1的dataset为simpledataset1
设置dbgrid1的datasource为datasource1双击连接button2输入以下代码
with simpledataset1.Connection do
begin
DriverName := 'dbxmysql';
GetDriverFunc := 'getSQLDriverMYSQL50';
LibraryName := 'dbxopenmysql50.dll';
VendorLib := 'libmysql.dll';
Params.Append('HostName=localhost');// Params.Append('HostName='+edit1.Text+':'+edit2.Text);
// Params.Append('port='+edit2.Text );
Params.Append('Database='+edit3.Text );
Params.Append('User_Name='+edit4.Text );
Params.Append('Password='+edit5.Text ); end;
simpledataset1.DataSet.CommandText :='select * from '+edit6.Text ;
simpledataset1.Open;
双击退出button1输入以下代码
simpledataset1.close;
close;完成后的程序代码总清单为：unit Unit1;interfaceuses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, DBXpress, FMTBcd, DB, Grids, DBGrids, SqlExpr, DBClient,
SimpleDS, StdCtrls;type
TForm1 = class(TForm)
DBGrid1: TDBGrid;
SimpleDataSet1: TSimpleDataSet;
DataSource1: TDataSource;
Button1: TButton;
Label1: TLabel;
Label2: TLabel;
Label3: TLabel;
Label4: TLabel;
Edit1: TEdit;
Edit2: TEdit;
Edit3: TEdit;
Edit4: TEdit;
Button2: TButton;
Label5: TLabel;
Edit5: TEdit;
Edit6: TEdit;
Label6: TLabel;
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure FormCreate(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;var
Form1: TForm1;
Connection: TSQLConnection;
implementation{$R *.dfm}procedure TForm1.Button2Click(Sender: TObject);
begin
with simpledataset1.Connection do
begin
DriverName := 'dbxmysql';
GetDriverFunc := 'getSQLDriverMYSQL50';
LibraryName := 'dbxopenmysql50.dll';
VendorLib := 'libmysql.dll';
Params.Append('HostName=localhost');// Params.Append('HostName='+edit1.Text+':'+edit2.Text);
// Params.Append('port='+edit2.Text );
Params.Append('Database='+edit3.Text );
Params.Append('User_Name='+edit4.Text );
Params.Append('Password='+edit5.Text ); end;
simpledataset1.DataSet.CommandText :='select * from '+edit6.Text ;
simpledataset1.Open;
end;procedure TForm1.Button1Click(Sender: TObject);
begin
simpledataset1.close;
close;end;
procedure TForm1.FormCreate(Sender: TObject);
beginend;end.转载请注作者:nathen.zhang Email:nathen.zhang@gmail.com

http://www.audio-data.de/mysql.htmlDeutsch
mysql.pas (Version --)
Client API for MySQL AB`s SQL Database Server using LibMySql.dll or LibMySqld.dll (Embeeded MYSQL Server). It is a Pascal translation of mysql.h and some other C header files needed for writing clients for the MySQL database server.
The unit is tested with:
Delphi Versions: (), (), , , , , .
LibMySql.dll Versions: 3.23, 4.0, 4.1, 5.0, 5.1, 6.0
MySQL-Server Versions: 3.23, 4.0, 4.1, 5.0, 5.1Download mysql.pasThe contents of the file mysql.pas are used with permission, subject to the Mozilla Public License Version 1.1 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.mozilla.org/MPL/MPL-1.1.html
Other components: SimpleXML Change log -- (so) Page created -- (so) Demo application -- (so) Improved dynamic loading -- (so) Demo Delphi Added functions: mysql_autocommit, mysql_set_character_set,
mysql_commit, mysql_rollback, mysql_set_server_option,
mysql_sqlstate, mysql_warning_count, MySql_StrLen,
CharSetNameToCodepage, CodepageToCharSetName, MySqlToUTF16,
UTF16ToMySql -- (so) Added functions: mysql_server_init, mysql_server_end,
Support for prepared statements -- (so) Added records: TNET501, TMYSQL501 -- (so) Added functions mysql_thread_init, mysql_thread_end -- (so) Added functions mysql_more_results, mysql_next_result, FormatIdentifier -- (so) Added functions EscapeString, EscapeForLike, QuoteString, FullFieldname
Change FormatIdentifier to QuoteName -- (so) Bug in GetVersion fixed -- (so) ThreadDemo addedLIBMYSQL.DLL ------------ You can also access a MySQL server using the libmysql.dll library. This file doesn't come with the standard MySQL installation, but you can download it from: http://www.dlldll.com/libmysql.dll_download.html You should also download the mysql.pas file which is a Delphi a unit that contains the type declarations for using this library. You need to include this unit in the uses clause of any unit from which you want to call the functions contained in the library. The following example opens a connection to MySql server, opens a database, performs a query and stores the result in a StringGrid, and finally closes the connection. uses ..., mysql; procedure TForm1.Button1Click(Sender: TObject); var mysqlcon: TMySQL; // MySQL-connection structure presults: pmysql_res; // Pointer to a results structure prow: pmysql_row; // Pointer to a row structure pfields: PMYSQL_FIELDS; // Pointer to a fields array i, j: Integer; // Counters begin // Connect to the server mysql_connect(@mysqlcon, 'localhost', 'root', ''); if mysqlcon.net.last_errno <> 0 then begin ShowMessage (Trim(mysqlcon.net.last_error)); exit; end; // Open the mysql database if mysql_select_db(@mysqlcon, 'mysql') <> 0 then begin mysql_close(@mysqlcon); // Disconnect ShowMessage('Couldn''t open mysql database'); exit; end; presults:= nil; try // Send the query to the server and get the results mysql_query(@mysqlcon, 'SELECT * FROM user'); presults := mysql_store_result(@mysqlcon); // Set the size of the grid StringGrid1.ColCount := presults^.field_count; StringGrid1.RowCount := presults^.row_count + 1; // Fill the grid header with the fields' names pfields := presults^.fields; for j := to presults^.field_count - do StringGrid1.Cells[j, ] := pfields^[j].name; // Fill the grid for i := 1 to presults^.row_count do begin prow := mysql_fetch_row(presults); for j := 0 to presults^.field_count -1 do StringGrid1.Cells[j, i]:= prow^[j]; end; finally mysql_free_result(presults); // Release memory mysql_close(@mysqlcon); // Disconnect end; end; As you can see, it's quite complicated, and this constitutes the main (and I would say only, but very important) disadvantage of using the libmysql.dll library. The advantages are that you have full control, that it's faster than using ODBC, and that it doesn't requiere the BDE. It would be nice to have a TDataset descendant that encapsulates the libmysql.dll API...