Board logo

標題: [原創] My SQL 中文亂碼解決方式 [打印本頁]

作者: Min    時間: 2010-5-1 23:29     標題: My SQL 中文亂碼解決方式

本帖最後由 Min 於 2010-5-1 23:53 編輯

1.SQL SERVER設定

my.ini (mysqld-nt)設定下
  1. #This File was made using the WinMySQLAdmin 1.4 Tool
  2. #2006/9/6 下午 02:00:49

  3. #Uncomment or Add only the keys that you know how works.
  4. #Read the MySQL Manual for instructions

  5. [mysqld]
  6. basedir=C:/mysql
  7. #bind-address=10.10.10.10
  8. datadir=C:/mysql/data
  9. #language=C:/mysql/share/your language directory
  10. #slow query log#=
  11. #tmpdir#=
  12. #port=3306
  13. #set-variable=key_buffer=512M
  14. [color=Red]default-character-set=big5
  15. [WinMySQLadmin]
  16. Server=C:/mysql/bin/mysqld-nt.exe
  17. user=root
  18. password=baby
  19. [color=Red]default-character-set=big5

  20. [client]
  21. default-character-set=big5
  22. init_connect='SET NAMES big5'
複製代碼
2.ADO連線設定
  1. Cnnl.Open "Driver={MySQL ODBC 3.51 Driver};Server=SERVER_NAME;Port=3306;Database=test;CharSet=big5;Uid=root;Pwd=;Option=16387"
複製代碼
(紅色字 是需新增的!)
作者: paperbox    時間: 2013-1-8 10:02

就是把
default-character-set改成big5
default-character-set=big5
是吧!
thanks
作者: heavenweaver    時間: 2014-3-6 00:04

回復 1# Min
感謝您提供的範例,最近在WinXP上安裝mySQL5.6,預設的my.ini就只有兩行:
--------------
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
----------------
拜讀您的文章,才知道有中文亂碼的問題及解決的設定。
作者: heavenweaver    時間: 2014-3-13 01:03

不論是設成big5
  1. [mysqld]
  2. default-character-set=big5

  3. [WinMySQLadmin]
  4. default-character-set=big5

  5. [client]
  6. default-character-set=big5
複製代碼
或是設成utf8
  1. [mysqld]
  2. default-character-set=utf8

  3. [WinMySQLadmin]
  4. default-character-set=utf8

  5. [client]
  6. default-character-set=utf8
複製代碼
結果都是一樣,中文都是亂碼。
作者: heavenweaver    時間: 2014-3-14 09:27

回復 4# heavenweaver
上網找個 AutoIt的MySQL UDFs,測試utf8及big5,結果是用big5才可以正確顯示。
測試程式如下:
  1. #include "mysql.au3"
  2. #include <Array.au3>

  3. ;===================================================================================
  4. ; This is a demo script that show you how to use MySQL UDFs with AutoIT
  5. ; Script:   MySQL UDFs working with libmysql.dll
  6. ; Version:  1.0.0.2
  7. ; Author:   Prog@ndy
  8. ;
  9. ; You can dowload MySQL UDFs from this URL
  10. ; http://progandy.de/downloads/summary/3-autoit-udfs/4-mysql-udfs.html
  11. ; Remarks by : Heavenweaver Lu
  12. ;===================================================================================
  13. ; MYSQL Initiate
  14. _MySQL_InitLibrary()
  15. If @error Then Exit MsgBox(0, '', "Could not Initiate MySQL")
  16. Global $MysqlConn = _MySQL_Init()
  17. Local $res

  18. Database()
  19. ShowTableContent()

  20. ; Query releasing
  21. _MySQL_Free_Result($res)
  22. ; Ending a connection
  23. _MySQL_Close($MysqlConn)
  24. ; MYSQL Ending
  25. _MySQL_EndLibrary()


  26. Func DATABASE()
  27.         Local $strHost = "127.0.0.1", $strUID = "root", $strPass = "http://forum.twbts.com", $strDB = "test"
  28.         $connected = _MySQL_Real_Connect($MysqlConn,$strHost, $strUID ,$strPass,$strDB )
  29.         If $connected = 0 Then
  30.                 $errno = _MySQL_errno($MysqlConn)
  31.                 MsgBox(0, "Error:", $errno & @LF & _MySQL_error($MysqlConn))
  32.                 If $errno = $CR_UNKNOWN_HOST Then MsgBox(0, "Error:", "$CR_UNKNOWN_HOST" & @LF & $CR_UNKNOWN_HOST)
  33.                 _MySQL_Close($MysqlConn)
  34.                 _MySQL_EndLibrary()
  35.                 Exit
  36.         EndIf

  37.         _MySQL_Set_Character_Set($MysqlConn, "big5")
  38.         _MySQL_Real_Query($MysqlConn, "CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET big5")
  39.         _MySQL_Real_Query($MysqlConn, "use test;")

  40.         Local $timedate = "now()"
  41.         Local $my = "00:F0:00:FF:00:FF"
  42.         Local $Name = "testname"
  43.         Local $IDname = "s448920"
  44.         Local $User = "麻辣家族"
  45.         Local $code = "11111"
  46.         Local $alltest = "PASS"
  47.         Local $time1 = "232"
  48.         Local $time2 = "234"

  49.                 $sTable = "test"
  50.         $strSQLcmd = "DROP TABLE IF EXISTS " & $sTable & ";"
  51.                 _MySQL_Real_Query($MysqlConn, $strSQLcmd)
  52.         $sMySqlStatement = "CREATE TABLE IF NOT EXISTS test (" & _
  53.                         "TimeDate TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP," & _
  54.                         "Name TEXT NOT NULL ," & _
  55.                         "My TEXT NOT NULL," & _
  56.                         "IDname TEXT NOT NULL ," & _
  57.                         "User TEXT NOT NULL ," & _
  58.                         "Code TEXT NOT NULL ," & _
  59.                         "Result TEXT NOT NULL ," & _
  60.                         "Time1 TEXT NOT NULL ," & _
  61.                         "Time2 TEXT NOT NULL );"

  62.         if _MySQL_Real_Query($MysqlConn, $sMySqlStatement,StringLen($sMySqlStatement) * 2) <> $MYSQL_SUCCESS Then
  63.                 ConsoleWrite("Error: " &  _MySQL_errno($MysqlConn) & " " & _MySQL_error($MysqlConn) & @LF)
  64.                 Return
  65.         EndIf

  66.         Local $sMySqlStatement = "INSERT INTO test (TimeDate, Name, My, IDname," & _
  67.                         "User,Code,Result,Time1,Time2) VALUES (NOW()," & _
  68.                         "'" & $my & "'," & _
  69.                         "'" & $Name & "'," & _
  70.                         "'" & $IDname & "'," & _
  71.                         "'" & $User & "'," & _
  72.                         "'" & $code & "'," & _
  73.                         "'" & $alltest & "'," & _
  74.                         "'" & $time1 & "'," & _
  75.                         "'" & $time2 & "');"

  76.     ConsoleWrite("$sMySqlStatement " & $sMySqlStatement & @LF)
  77.         If _MySQL_Real_Query($MysqlConn,$sMySqlStatement,StringLen($sMySqlStatement) * 2) <> $MYSQL_SUCCESS Then
  78.                 ConsoleWrite("error: " & _MySQL_errno($MysqlConn) & " " & _MySQL_error($MysqlConn) & @LF)
  79.                 MsgBox(16, "error: " & _MySQL_errno($MysqlConn), _MySQL_error($MysqlConn))
  80.                 Return
  81.         Else
  82.                                 ConsoleWrite("!mysql> Congradulation! Data Insert Compeleted" & @CRLF)
  83.         EndIf
  84.         EndFunc   ;==>DATABASE

  85. Func ShowTableContent()

  86. $query = "SELECT * FROM test"
  87. _MySQL_Real_Query($MysqlConn, $query)
  88. $res = _MySQL_Store_Result($MysqlConn)
  89. $fields = _MySQL_Num_Fields($res)
  90. $rows = _MySQL_Num_Rows($res)
  91. ;MsgBox(0, "", $rows & "-" & $fields)
  92. ConsoleWrite("!mysql> " & "Rows = " & $rows & " x " & "Columns = " & $fields & @CRLF)

  93. ; Access 1
  94. ;MsgBox(0, '', "Access Methode 1- manual work")
  95. Dim $array[$rows][$fields]
  96. For $k = 1 To $rows
  97.         $mysqlrow = _MySQL_Fetch_Row($res,$fields)
  98.         $lenthsStruct = _MySQL_Fetch_Lengths($res)
  99.         For $i = 1 To $fields
  100.                 $length = DllStructGetData($lenthsStruct, 1, $i)
  101.                 $fieldPtr = DllStructGetData($mysqlrow, 1, $i)
  102.                 $data = DllStructGetData(DllStructCreate("char[" & $length & "]", $fieldPtr), 1)
  103.                 $array[$k - 1][$i - 1] = $data
  104.         Next
  105. Next
  106. _ArrayDisplay($array)

  107. ; Access 2
  108. ;MsgBox(0, '', "Access Methode 2 - Series")
  109. _MySQL_Data_Seek($res, 0) ; only for to the beginning of the query
  110. Do
  111. $row1 = _MySQL_Fetch_Row_StringArray($res)
  112. If @error Then ExitLoop
  113. _ArrayDisplay($row1)
  114. Until @error

  115. ;MsgBox(0, '', "Access Method 3 - all in a 2D array")
  116. $array = _MySQL_Fetch_Result_StringArray($res)
  117. _ArrayDisplay($array)

  118. ; Field information
  119. ;MsgBox(0, '', "Field information")

  120. Dim $arFields[$fields][3]
  121. For $i = 0 To $fields - 1
  122.         $field = _MySQL_Fetch_Field_Direct($res, $i)
  123.         $arFields[$i][0] = _MySQL_Field_ReadValue($field, "name")
  124.         $arFields[$i][1] = _MySQL_Field_ReadValue($field, "table")
  125.         $arFields[$i][2] = _MySQL_Field_ReadValue($field, "db")
  126. Next
  127. _ArrayDisplay($arFields)
  128. EndFunc   ;==>ShowTableContent
複製代碼

作者: heavenweaver    時間: 2014-3-14 09:31

回復 5# heavenweaver
有圖有真相!
[attach]17763[/attach]
[attach]17764[/attach]
作者: heavenweaver    時間: 2014-3-14 11:49

AutoIt的EzMySql.au3 UDFs,不用設定utf8或big5,中文可以正確顯示。
[attach]17766[/attach]




歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)