mysql - 无法获取正确的值以使用 JDBC for MySQL 从 latin1 字段查询中文值

Question

使用 JDBC for MySQL 从 latin1 字段(varchar 或 char)查询中文值无法获得正确的值。性格无法改变。以下是测试步骤。是否有可能为这种情况获得正确的值(value)？我还应该做什么？

<强>1。创建数据库:

CREATE DATABASE TESTDB CHARACTER SET latin1 COLLATE latin1_general_ci;  

<强>2。创建表:

CREATE TABLE TB1 (
    vname varchar(50) default '',
    cname char(50) default ''
) DEFAULT CHARSET=latin1 ;

<强>3。插入数据:

set names latin1;  
insert into TB1(vname,cname) values('名字v','名字c');  

<强>4。显示字符集:

mysql> show variables like '%set%';  
+--------------------------+---------------------------------------------------------+  
| Variable_name            | Value                                                   |  
+--------------------------+---------------------------------------------------------+  
| auto_increment_offset    | 1                                                       |  
| character_set_client     | latin1                                                  |  
| character_set_connection | latin1                                                  |  
| character_set_database   | latin1                                                  |  
| character_set_filesystem | binary                                                  |  
| character_set_results    | latin1                                                  |  
| character_set_server     | utf8                                                    |  
| character_set_system     | utf8                                                    |  
| character_sets_dir       | F:\Program Files\MySQL\MySQL Server 5.5\share\charsets\ |  
+--------------------------+---------------------------------------------------------+  

<强>5。从命令控制台查询时可以得到正确的值:

mysql> select * from tb1;  
+-------+-------+  
| vname | cname |  
+-------+-------+  
| 名字v     | 名字c     |  
+-------+-------+  
1 row in set (0.00 sec)  

6.使用JDBC查询无法得到正确的值:
jdbc 网址:jdbc:mysql://192.168.5.74/testdb?characterEncoding=UTF-8

*set names utf8;  
select vname,hex(vname),length(vname),char_length(vname) from tb1;  
select cname,hex(cname),length(cname),char_length(cname) from tb1;  
select vname,cname  
    ,CONVERT(CONVERT(CONVERT(vname USING latin1) USING binary) USING utf8) as c1  
    ,CONVERT(CONVERT(CONVERT(cname USING latin1) USING binary) USING utf8) as c2  
from tb1;*  

vname           hex(vname)     length(vname) char_length(vname)   
--------------- -------------- ------------- ------------------   
??×?v           C3FBD7D676     5             5                    

cname           hex(cname)     length(cname) char_length(cname)   
--------------- -------------- ------------- ------------------   
??×?c           C3FBD7D663     5             5                    

vname             cname     c1      c2      
----------------- --------- ------- ------  
??×?v             ??×?c                 

Answer 1

我解决了。 多字节字符存储在latin1字段时被转换为字节。它需要转换回插入字符集。下面的sql可以做到这一点:

CONVERT(CONVERT(CONVERT(vname USING latin1) USING binary) USING [INSERT_CHARSET]) 

我插入的字符集是gb2312，所以sql应该是:

CONVERT(CONVERT(CONVERT(vname USING latin1) USING binary) USING gb2312)