看着worlpress和sablog很火,自己想写个asp版本的调用该默认数据库,结果碰到读取UTF-8格式存储的数据出现乱码,这里搜了一下,这个列子很好:
还你好，由于时间有限，我来不及把我的原文翻译成中文。
基本上，就是 UTF8 和 utf16 编码的问题。
MySQL 支援 utf8, 但不支援utf16 (连接之可用utf8,储存可用utf8和utf16).
但假如用utf8,创造的文档名就是一堆乱码了。
I am having problem regarding utf8 (used by MySQL) and utf16 (used by WinXP)
I want to store the CJK string into both MySQL and use the string to create a new file name.
The problem here is，if the string is in utf8, MySQL can accept it, but the filename will have strange character.
If the string is in utf16 format, MySQL will store as '???', and the filename will be created correctly in WinXP.
I have attached my code. Notice that, If I set
@CodePage=65001 'UTF-8'
WinXP/IIS will convert all the UTF8 string in the asp file to UTF16 for internal processing.
MySQL can only accept utf8 for client connection, so aftｅｒ UTF16 converted to UTF8, ??? will appear.
If I set
@CodePage=1252 or simply remove this line (default is 1252)
MySQL can store and retrieve correctly, BUT filename will be some weird characters.
WinXP can display double byte Unicode, not UTF8 (Multibyte Unicode), in this case, 3 bytes per cjk character.
I have no problem when using MS Access, as it is using UTF16, but changing to MySQL drives me crazy.
Any Solution for this?
Thanks in advance.
My system:
WinXP (english version),
MySQL 5.0
The database and table I created is innodb in utf8 format.
The ASP file is saved as utf8 format.
<%'@ CodePage=1252 '65001 %>
<%
Option Explicit
Dim strConn, objConn
Dim strSQL, objRS, arrRS
Dim intRow, intRowMax, intCol, intColMax
Dim objFSO, objText
Dim strFileName
strFileName = "一人"
''''''''''''''''''''''''''''''''''''''''
'Insert CJK character into MySQL
''''''''''''''''''''''''''''''''''''''''
strConn = "Driver={MySQL ODBC 3.51 Driver}; Server=localhost; CharSet=utf8; Port=3306; Option=0; Socket=; Stmt=; Database=zzz; Uid=xxx; Pwd=yyy;"
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConn
objConn.Execute "SET names utf8;"
strSQL = "INSERT INTO tblUser SET `Name` = '" & strFileName & "';"
objConn.Execute strSQL
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "SELECT * FROM tblUser;"
arrRS = objRS.GetRows
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
''''''''''''''''''''''''''''''''''''''
'Create filename using CJK'
''''''''''''''''''''''''''''''''''''''
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
Set objText = objFSO.CreateTextFile("c:\" & strFileName & ".txt", True, -1) 'Unicode = -1 & do not use Server.MapPath
objText.Close
Set objText = Nothing
Set objFSO = Nothing
''''''''''''''''''''''''''''''''''''''
'Display the result'
''''''''''''''''''''''''''''''''''''''
Response.CodePage = 0
Response.CharSet = "utf-8"
Response.Write "<head>"
Response.Write "<meta http-equiv=""content-type"" content=""text/html; charset=UTF-8"">"
Response.Write "</head>"
Response.Write "<table border=""1"">"
For intRow = 0 To intRowMax
Response.Write "<tr>"
For intCol = 0 To intColMax
Response.Write "<td>" & arrRS(intCol, intRow) & "</td>"
Next
Response.Write "</tr>"
Next
%>
mysql> show variables like 'char%'
+--------------------------+---------------------------------------------------------+
| Variable_name | Value
+--------------------------+---------------------------------------------------------+
| character_set_client | utf8
| character_set_connection | utf8
| character_set_database | utf8
| character_set_filesystem | binary
| character_set_results | utf8
| character_set_server | latin1
| character_set_system | utf8
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\
+--------------------------+---------------------------------------------------------+
8 rows in set (0.05 sec)
mysql> show variables like 'coll%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci
| collation_database | utf8_general_ci
| collation_server | latin1_swedish_ci
+----------------------+-------------------+
3 rows in set (0.00 sec)
源自于：http://www.zaqi.net/files_view.asp?files_id=1180