[已解决]----Autoit+MYSQL中文支持范例(见15楼源代码）
本帖最后由 kingfirekkk 于 2010-12-16 08:21 编辑问题描述：
本以为是因为USB WebServer的问题，后面安装了MYSQL SERVER V5.0的，还是一样的现象，具体表现为，使用以下代码，将其中的$GetUser值改为中文，就无法写入数据库了。
数据库统一有将字符集设置为UTF8，而且各个字段也均为UTF8，为啥就是不行呢？错误贴图如下：
操作数据库源代码如下：#include "EzMySql.au3"
#include <Array.au3>
Database()
Func DATABASE()
If Not _EzMySql_Startup() Then
MsgBox(0, "Error Starting MySql", "Error: " & @error & @CR & "Error string: " & _EzMySql_ErrMsg())
Return
Else
MsgBox(0, "恭喜！", "Startup OK")
EndIf
$Pass = "andrew"
If Not _EzMySql_Open("", "root", $Pass, "", "3306") Then
MsgBox(0, "Error opening Database", "Error: " & @error & @CR & "Error string: " & _EzMySql_ErrMsg())
Return
Else
MsgBox(0, "恭喜！", "open OK")
EndIf
If Not _EzMySql_Exec("CREATE DATABASE IF NOT EXISTS testresult DEFAULT CHARSET UTF8") Then
MsgBox(0, "Error opening Database", "Error: " & @error & @CR & "Error string: " & _EzMySql_ErrMsg())
Return
Else
MsgBox(0, "恭喜！", "Create Database OK!")
EndIf
If Not _EzMySql_SelectDB("testresult") Then
MsgBox(0, "Error setting Database to use", "Error: " & @error & @CR & "Error string: " & _EzMySql_ErrMsg())
Return
Else
MsgBox(0, "恭喜！", "SelectDB OK!")
EndIf
$macaddr = "00:A0:55:03:11:17"
$GetID = "74732015"
$getUser = '李三' ;此处如改为任意非中文字符，如字母数字，均可正常写入数据库，但中文就不行！
$sMySqlStatement = "CREATE TABLE IF NOT EXISTS testresult (" & _
"TimeDate TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP," & _
"$macaddr TEXT NOT NULL," & _
"GetID TEXT NOT NULL ," & _
"GetUser TEXT NOT NULL);"
If Not _EzMySql_Exec($sMySqlStatement) Then
MsgBox(0, "Error Creating Database Table", "Error: " & @error & @CR & "Error string: " & _EzMySql_ErrMsg())
Return
Else
MsgBox(0, "恭喜！", "Creating Databas OK!")
EndIf
Local $sMySqlStatement = "INSERT INTO testresult (TimeDate,$macaddr,GetID,GetUser) VALUES (NOW()," & _
"'" & $macaddr & "'," & _
"'" & $GetID & "'," & _
"'" & $getUser & "');"
If Not _EzMySql_Exec($sMySqlStatement) Then
MsgBox(0, "写数据到数据库", "Error: " & @error & @CR & "Error string: " & _EzMySql_ErrMsg())
Return 0
Else
MsgBox(0, "恭喜！", "写数据到数据库OK!")
EndIf
_EzMySql_Close()
_EzMySql_ShutDown()
Return 1
EndFunc ;==>DATABASE
补充说明：
后面安装了MySQL Server 5.1 英文版FOR WIN32的版本，在SQL CMD模式下面，不使用变量的话OK，使用变量，不使用中文也OK，但用变量，变量中有中文就不行了... 应该还是你的mysql版本问题吧。 回复 2# 绿色风
中文支持肯定是没有问题的，应该是哪个地方被我忽略了 后来下载了MySQL Server 5.1的版本，SQL CMD下面，添加记录成功，但用AU3+变量（中文）总是不行，是不是符号处理的问题？------
各位大佬都冬眠了不成？？不要吝啬指导一二呀~
{:face (229):}
Local $sMySqlStatement = "INSERT INTO mbtestresult (TimeDate,MB_Name,MyMac,GetID," & _
"GetUser,ErrorCode,TestResult,Time1,Time2) VALUES (NOW()," & _
"'" & $mac & "'," & _
"'" & $Name & "'," & _
"'" & $ID & "'," & _
"'" & $User & "'," & _
"'" & $error & "'," & _
"'" & $result & "'," & _
"'" & $time1 & "'," & _
"'" & $time2 & "');"
If Not _EzMySql_Exec($sMySqlStatement) Then
MsgBox(0, "写数据到数据库", "Error: " & @error & @CR & "Error string: " & _EzMySql_ErrMsg())
Return 0
Else
MsgBox(0, "恭喜！", "写数据到数据库OK!")
EndIf
请谷歌一下什么叫字符集. 回复 5# ceoguang
Google了一些文章，大概都是那么几招....
设置my.ini里面的字符集----无效；
设置"CHARACTER SET GBK
CHARACTER SET UTF8
CHARACTER SET GB2312
GBK,GB2312,UTF8三种字符，每种都试过，还是不行，觉得很奇怪....
然后建表的时候，也使用以上三种字符集，也是一样，直接在SQLCMD里面可以添加中文字段，使用非变量也可以添加记录，只要将变量内容弄成中文的，就写不进去.. 查看了一下你所使用的UDF,里面并没有字符设置,不过已经封装在DLL里面的DllCall($hEzMySql_Dll, "int", "mysql_set_character_set", "ptr", $hEzMySql_Ptr, "str", $name)其中$hEzMySql_Ptr为连接指针(即句柄),$name为你要设置的字符集.如:GBK 或者直接_EzMySql_Exec也是可以的 回复 8# ceoguang
试了。还是不行，不知道与系统有没有关系，真希望哪位有试验成功的中文例子瞄瞄，那样心里就有底了....SQLCMD OK，AUTOIT 变量使用中文汉字以后，就OVER了。 你是怎么试的?
发代码上来 首先谢谢C版的关注：
下面是我的源代码：
#include "EzMySql.au3"
#include <Array.au3>
Database()
Func DATABASE()
If Not _EzMySql_Startup() Then
MsgBox(0, "Error Starting MySql", "Error: " & @error & @CR & "Error string: " & _EzMySql_ErrMsg())
Return
Else
; MsgBox(0, "恭喜！", "Startup OK")
EndIf
$Pass = "andrew"
If Not _EzMySql_Open("", "root", $Pass, "", "3306") Then
MsgBox(0, "Error opening Database", "Error: " & @error & @CR & "Error string: " & _EzMySql_ErrMsg())
Return
Else
; MsgBox(0, "恭喜！", "open OK")
EndIf
If Not _EzMySql_Exec("set names 'gbk';") Then
MsgBox(0, "Error Creating Database Table", "Error: " & @error & @CR & "Error string: " & _EzMySql_ErrMsg())
Return
Else
MsgBox(0, "恭喜！", "设置字符集成功!")
EndIf
If Not _EzMySql_Exec("set character set gbk;") Then
MsgBox(0, "Error opening Database", "Error: " & @error & @CR & "Error string: " & _EzMySql_ErrMsg())
Return
Else
; MsgBox(0, "恭喜！", "Create Database OK!")
EndIf
If Not _EzMySql_Exec("CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET gbk") Then
MsgBox(0, "Error opening Database", "Error: " & @error & @CR & "Error string: " & _EzMySql_ErrMsg())
Return
Else
; MsgBox(0, "恭喜！", "Create Database OK!")
EndIf
If Not _EzMySql_SelectDB("test") Then
MsgBox(0, "Error setting Database to use", "Error: " & @error & @CR & "Error string: " & _EzMySql_ErrMsg())
Return
Else
; MsgBox(0, "恭喜！", "SelectDB OK!")
EndIf
Local $timedate = "now()"
Local $my = "00:F0:00:FF:00:FF"
Local $Name = "testname"
Local $IDname = "s448920"
Local $User = "右地震荡"
Local $code = "11111"
Local $alltest = "PASS"
Local $time1 = "232"
Local $time2 = "234"
$sMySqlStatement = "CREATE TABLE IF NOT EXISTS test (" & _
"TimeDate TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP," & _
"Name TEXT NOT NULL ," & _
"My TEXT NOT NULL," & _
"IDname TEXT NOT NULL ," & _
"User TEXT NOT NULL ," & _
"Code TEXT NOT NULL ," & _
"Result TEXT NOT NULL ," & _
"Time1 TEXT NOT NULL ," & _
"Time2 TEXT NOT NULL );"
If Not _EzMySql_Exec($sMySqlStatement) Then
MsgBox(0, "Error Creating Database Table", "Error: " & @error & @CR & "Error string: " & _EzMySql_ErrMsg())
Return
Else
MsgBox(0, "恭喜！", "Creating Databas OK!")
EndIf
Local $sMySqlStatement = "INSERT INTO test (TimeDate,Name,My,IDname," & _
"User,Code,Result,Time1,Time2) VALUES (NOW()," & _
"'" & $my & "'," & _
"'" & $Name & "'," & _
"'" & $IDname & "'," & _
"'" & $User & "'," & _
"'" & $code & "'," & _
"'" & $alltest & "'," & _
"'" & $time1 & "'," & _
"'" & $time2 & "');"
If Not _EzMySql_Exec($sMySqlStatement) Then
MsgBox(0, "写数据到数据库", "Error: " & @error & @CR & "Error string: " & _EzMySql_ErrMsg())
Return 0
Else
MsgBox(0, "恭喜！", "写数据到数据库OK!")
EndIf
_EzMySql_Close()
_EzMySql_ShutDown()
Return 1
EndFunc ;==>DATABASE
首先，贴上My.ini里面的内容，仅部分：
port=3306
default-character-set=gbk
default-character-set=gbk
port=3306
default-character-set=gbk
然后，直接打开MySQL Command Line Client,往里面添加数据，可以正常添加有中文字符的记录，见下图图片：
再贴上status;的结果（默认的）
我的系统是Win7旗舰版的，Au3版本:3.3.6.1-2.
不知道以上现象在C版你们的机器上是否能够复制下来，因为条件限制，目前没有办法使用其它系统及环境测试以上代码..... 回复 10# ceoguang
请见11楼，调试过程，错误图片如1图所示。。 本帖最后由 ceoguang 于 2010-12-14 11:29 编辑
.....
很抱歉没条件帮你测试.
从你图片可以看出服务器默认字符集就是GBK
而你在建库的时候没有指定编码,但是libmysql.dll默认的是UFT8
所以你要执行的应该是set names UTF8或者DllCall("libmysql.dll", "int", "mysql_set_character_set", "ptr", $hEzMySql_Ptr, "str", "utf8")建议你在建库的时候就指定编码,like this:CREATE DATABASE IF NOT EXISTS test DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_cibtw:
EzMySql封装很不完整,DLL里面有很多函数没在UDF里面,而且语法多少有点不够严谨
同样是使用libmysql.dll的情况下建议你使用附件中的这个 回复 13# ceoguang
谢谢C版....我再研究研究，有结果了再PASS出来。
#include "mysql.au3"
#include <Array.au3>
__MySQL_ExtractEmbeddedDLL()
_MySQL_InitLibrary()
If @error Then Exit MsgBox(0, '', "could nit init MySQL")
Global $MysqlConn = _MySQL_Init()
Database()
_MySQL_Close($MysqlConn)
_MySQL_EndLibrary()
Func DATABASE()
$Pass = "andrew"
$connected = _MySQL_Real_Connect($MysqlConn, "111.111.111.111", "root", $Pass)
If $connected = 0 Then
$errno = _MySQL_errno($MysqlConn)
MsgBox(0, "Error:", $errno & @LF & _MySQL_error($MysqlConn))
If $errno = $CR_UNKNOWN_HOST Then MsgBox(0, "Error:", "$CR_UNKNOWN_HOST" & @LF & $CR_UNKNOWN_HOST)
_MySQL_Close($MysqlConn)
_MySQL_EndLibrary()
Exit
EndIf
_MySQL_Set_Character_Set($MysqlConn, "GBK")
_MySQL_Real_Query($MysqlConn, "CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET gbk")
_MySQL_Real_Query($MysqlConn, "use test;")
Local $timedate = "now()"
Local $my = "00:F0:00:FF:00:FF"
Local $Name = "testname"
Local $IDname = "s448920"
Local $User = "右地震荡"
Local $code = "11111"
Local $alltest = "PASS"
Local $time1 = "232"
Local $time2 = "234"
$sMySqlStatement = "CREATE TABLE IF NOT EXISTS test (" & _
"TimeDate TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP," & _
"Name TEXT NOT NULL ," & _
"My TEXT NOT NULL," & _
"IDname TEXT NOT NULL ," & _
"User TEXT NOT NULL ," & _
"Code TEXT NOT NULL ," & _
"Result TEXT NOT NULL ," & _
"Time1 TEXT NOT NULL ," & _
"Time2 TEXT NOT NULL );"
if _MySQL_Real_Query($MysqlConn, $sMySqlStatement,StringLen($sMySqlStatement) * 2) <> $MYSQL_SUCCESS Then
ConsoleWrite("Error: " &_MySQL_errno($MysqlConn) & " " & _MySQL_error($MysqlConn) & @LF)
Return
EndIf
Local $sMySqlStatement = "INSERT INTO test (TimeDate,Name,My,IDname," & _
"User,Code,Result,Time1,Time2) VALUES (NOW()," & _
"'" & $my & "'," & _
"'" & $Name & "'," & _
"'" & $IDname & "'," & _
"'" & $User & "'," & _
"'" & $code & "'," & _
"'" & $alltest & "'," & _
"'" & $time1 & "'," & _
"'" & $time2 & "');"
ConsoleWrite("$sMySqlStatement " & $sMySqlStatement & @LF)
If _MySQL_Real_Query($MysqlConn,$sMySqlStatement,StringLen($sMySqlStatement) * 2) <> $MYSQL_SUCCESS Then
ConsoleWrite("error: " & _MySQL_errno($MysqlConn) & " " & _MySQL_error($MysqlConn) & @LF)
MsgBox(16, "error: " & _MySQL_errno($MysqlConn), _MySQL_error($MysqlConn))
Return
Else
MsgBox(64, "恭喜！", "写数据到数据库OK!")
EndIf
EndFunc ;==>DATABASE