- 浏览: 55960 次
文章分类
最新评论
成功解决Tomcat-JDBC-MySQL乱码
MySQL-JDBC驱动文档官方解释
Using Character Sets and Unicode
All strings sent from the JDBC driver to the server are converted automatically from native Java Unicode form to the client character encoding, including all queries sent usingStatement.execute()
,Statement.executeUpdate()
,Statement.executeQuery()
as
well as allPreparedStatement
andCallableStatement
parameters
with the exclusion of parameters set usingsetBytes()
,setBinaryStream()
,setAsciiStream()
,setUnicodeStream()
andsetBlob()
.
Number of Encodings Per Connection
In MySQL Server 4.1 and higher, Connector/J supports a single character encoding between client and server, and any number of character encodings for data returned by the server to the client inResultSets
.
Prior to MySQL Server 4.1, Connector/J supported a single character encoding per connection, which could either be automatically detected from the server configuration, or could be configured by the user through theuseUnicode
andcharacterEncoding
properties.
Setting the Character Encoding
The character encoding between client and server is automatically detected upon connection. You specify the encoding on the server using thecharacter_set_server
for
server versions 4.1.0 and newer, andcharacter_set
system
variable for server versions older than 4.1.0. The driver automatically uses the encoding specified by the server. For more information, seeSection10.1.3.1,
“Server Character Set and Collation”.
For example, to use 4-byte UTF-8 character sets with Connector/J, configure the MySQL server withcharacter_set_server=utf8mb4
,
and leavecharacterEncoding
out of the
Connector/J connection string. Connector/J will then autodetect the UTF-8 setting.
To override the automatically detected encoding on the client side, use thecharacterEncoding
property
in the URL used to connect to the server.
To allow multiple character sets to be sent from the client, use the UTF-8 encoding, either by configuringutf8
as
the default server character set, or by configuring the JDBC driver to use UTF-8 through thecharacterEncoding
property.
When specifying character encodings on the client side, use Java-style names. The following table lists MySQL character set names and the corresponding Java-style names:
Table22.26.MySQL to Java Encoding Name Translations
ascii
|
US-ASCII
|
big5
|
Big5
|
gbk
|
GBK
|
sjis
|
SJIS (or Cp932 or MS932 for MySQL Server < 4.1.11)
|
cp932
|
Cp932 or MS932 (MySQL Server > 4.1.11)
|
gb2312
|
EUC_CN
|
ujis
|
EUC_JP
|
euckr
|
EUC_KR
|
latin1
|
Cp1252
|
latin2
|
ISO8859_2
|
greek
|
ISO8859_7
|
hebrew
|
ISO8859_8
|
cp866
|
Cp866
|
tis620
|
TIS620
|
cp1250
|
Cp1250
|
cp1251
|
Cp1251
|
cp1257
|
Cp1257
|
macroman
|
MacRoman
|
macce
|
MacCentralEurope
|
utf8
|
UTF-8
|
ucs2
|
UnicodeBig
|
MySQL官方:MySQL连接字符集设置
Several character set and collation system variables relate to a client's interaction with the server. Some of these have been mentioned in earlier sections:
-
The server character set and collation are the values of the
character_set_server
andcollation_server
system variables. -
The character set and collation of the default database are the values of the
character_set_database
andcollation_database
system variables.
Additional character set and collation system variables are involved in handling traffic for the connection between a client and the server. Every client has connection-related character set and collation system variables.
A“connection”is what you make when you connect to the server. The client sends SQL statements, such as queries, over the connection to the server. The server sends responses, such as result sets or error messages, over the connection back to the client. This leads to several questions about character set and collation handling for client connections, each of which can be answered in terms of system variables:
-
What character set is the statement in when it leaves the client?
The server takes the
character_set_client
system variable to be the character set in which statements are sent by the client. -
What character set should the server translate a statement to after receiving it?
For this, the server uses the
character_set_connection
andcollation_connection
system variables. It converts statements sent by the client fromcharacter_set_client
tocharacter_set_connection
(except for string literals that have an introducer such as_latin1
or_utf8
).collation_connection
is important for comparisons of literal strings. For comparisons of strings with column values,collation_connection
does not matter because columns have their own collation, which has a higher collation precedence. -
What character set should the server translate to before shipping result sets or error messages back to the client?
The
character_set_results
system variable indicates the character set in which the server returns query results to the client. This includes result data such as column values, and result metadata such as column names and error messages.
Clients can fine-tune the settings for these variables, or depend on the defaults (in which case, you can skip the rest of this section). If you do not use the defaults, you must change the character settingsfor each connection to the server.
Two statements affect the connection-related character set variables as a group:
-
SET NAMES '
charset_name
' [COLLATE 'collation_name
']SET NAMES
indicates what character set the client will use to send SQL statements to the server. Thus,SET NAMES 'cp1251'
tells the server,“future incoming messages from this client are in character setcp1251
.”It also specifies the character set that the server should use for sending results back to the client. (For example, it indicates what character set to use for column values if you use aSELECT
statement.)A
SET NAMES '
statement is equivalent to these three statements:charset_name
'SET character_set_client =
charset_name
; SET character_set_results =charset_name
; SET character_set_connection =charset_name
;Setting
character_set_connection
tocharset_name
also implicitly setscollation_connection
to the default collation forcharset_name
. It is unnecessary to set that collation explicitly. To specify a particular collation, use the optionalCOLLATE
clause:SET NAMES '
charset_name
' COLLATE 'collation_name
' -
SET CHARACTER SET
charset_name
SET CHARACTER SET
is similar toSET NAMES
but setscharacter_set_connection
andcollation_connection
tocharacter_set_database
andcollation_database
. ASET CHARACTER SET
statement is equivalent to these three statements:charset_name
SET character_set_client =
charset_name
; SET character_set_results =charset_name
; SET collation_connection = @@collation_database;Setting
collation_connection
also implicitly setscharacter_set_connection
to the character set associated with the collation (equivalent to executingSET character_set_connection = @@character_set_database
). It is unnecessary to setcharacter_set_connection
explicitly.
ucs2
,utf16
,
andutf32
cannot be used as a client character
set, which means that they do not work forSET
NAMES
orSET CHARACTER SET
.
The MySQL client programsmysql
,mysqladmin
,mysqlcheck
,mysqlimport
,
andmysqlshow
determine the default character
set to use as follows:
-
In the absence of other information, the programs use the compiled-in default character set, usually
latin1
. -
The programs can autodetect which character set to use based on the operating system setting, such as the value of the
LANG
orLC_ALL
locale environment variable on Unix systems or the code page setting on Windows systems. For systems on which the locale is available from the OS, the client uses it to set the default character set rather than using the compiled-in default. For example, settingLANG
toru_RU.KOI8-R
causes thekoi8r
character set to be used. Thus, users can configure the locale in their environment for use by MySQL clients.The OS character set is mapped to the closest MySQL character set if there is no exact match. If the client does not support the matching character set, it uses the compiled-in default. For example,
ucs2
is not supported as a connection character set.C applications can use character set autodetection based on the OS setting by invoking
mysql_options()
as follows before connecting to the server:mysql_options(mysql, MYSQL_SET_CHARSET_NAME, MYSQL_AUTODETECT_CHARSET_NAME);
-
The programs support a
--default-character-set
option, which enables users to specify the character set explicitly to override whatever default the client otherwise determines.
Before MySQL 5.5, in the absence of other information, the MySQL client programs used the compiled-in default character set, usuallylatin1
.
An implication of this difference is that if your environment is configured to use a non-latin1
locale,
MySQL client programs will use a different connection character set than previously, as though you had issued an implicitSET
NAMES
statement. If the previous behavior is required, start the client with the--default-character-set=latin1
option.
When a client connects to the server, it sends the name of the character set that it wants to use. The server uses the name to set thecharacter_set_client
,character_set_results
,
andcharacter_set_connection
system
variables. In effect, the server performs aSET
NAMES
operation using the character set name.
With themysqlclient,
to use a character set different from the default, you could explicitly executeSET
NAMES
every time you start up. To accomplish the same result more easily, add the--default-character-set
option
setting to yourmysqlcommand
line or in your option file. For example, the following option file setting changes the three connection-related character set variables set tokoi8r
each
time you invokemysql:
[mysql] default-character-set=koi8r
If you are using themysqlclient
with auto-reconnect enabled (which is not recommended), it is preferable to use thecharset
command
rather thanSET NAMES
. For example:
mysql> charset utf8
Charset changed
Thecharset
command issues aSET
NAMES
statement, and also changes the default character set thatmysqluses
when it reconnects after the connection has dropped.
Example: Suppose thatcolumn1
is defined
asCHAR(5) CHARACTER SET latin2
. If you
do not saySET NAMES
orSET
CHARACTER SET
, then forSELECT column1
FROM t
, the server sends back all the values forcolumn1
using
the character set that the client specified when it connected. On the other hand, if you saySET
NAMES 'latin1'
orSET CHARACTER SET latin1
before
issuing theSELECT
statement,
the server converts thelatin2
values tolatin1
just
before sending results back. Conversion may be lossy if there are characters that are not in both character sets.
If you want the server to perform no conversion of result sets or error messages, setcharacter_set_results
toNULL
orbinary
:
SET character_set_results = NULL;
To see the values of the character set and collation system variables that apply to your connection, use these statements:
SHOW VARIABLES LIKE 'character_set%'; SHOW VARIABLES LIKE 'collation%';
You must also consider the environment within which your MySQL applications execute. SeeSection10.1.5, “Configuring the Character Set and Collation for Applications”.
For more information about character sets and error messages, seeSection10.1.6, “Character Set for Error Messages”.
数据库的字符编码和表内字段的编码
在MySQL中数据库的字符编码和表内字段的编码的要指定为utf8(utf8_general_ci)
jsp中
pageEncoding="UTF-8"
request.setCharacterEncoding("UTF-8");
(十分重要)JDBC获取Connection时
设置url为jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
一般设置完前三步就基本没问题了。如果还不行,就修改mysql的配置文件吧。
MySQL配置文件 my.ini中
## UTF 8 Settings #init-connect=\'SET NAMES utf8\' #collation_server=utf8_unicode_ci #character_set_server=utf8 #skip-character-set-client-handshake #character_sets-dir="D:/xampp/mysql/share/charsets"
把前面的注释去掉。
重启MySQL,重启Tomcat。
以下为转载
1.服务器级
查看设置:show global variables like 'character_set_server'; 和 show global variables like 'collation_server';
修改设置:在OPTION FILE (/etc/mysql/my.cnf)里设置:
[mysqld]
character_set_server=utf8
collation_server=utf8_general_ci
2. 数据库级
查看设置:select * from information_schema.schemata where schema_name = 'cookbook';
设置:
1.若没有显式设置,则自动使用服务器级的配置
2.显式设置:在创建库时指定
create database playUtf8 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
3.表级
查看设置:show create table course;
设置:
1.若没有显式设置,则自动使用数据库级的配置
2.显式设置:在创建表时指定
create table utf ( id int ) default charset=utf8 default collate=utf8_bin;
4.列级
查看设置:show create table course;
设置:
1.若没有显式设置,则自动使用表级的配置
2.显式设置:
CREATE TABLE Table1(column1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci);
5.连接级别
查看设置:
show variables like 'character_set_client'; # 服务端使用这个编码来理解客户端发来的statements
show variables like 'character_set_connection' ; # 我还不知道什么意思,等看了mysql源码再说
show variables like 'character_set_results'; # 服务端使用这个编码回送结果集和错误信息
设置:
客户端在连接时可以指定这些参数;同时,服务端也提供了一个Global范围的值,客户端未指定这些参数时,服务端就使用这个Global值。这个global值怎么设置的? 我查遍了很多文档,似乎还没看到设置的办法 (有人说通过my.cnf,或者在启动mysqld时指定命令行参数,其实都是错的)
附:connector/j传输SQL时用什么编码?
答案: "The character encoding between client and server is automatically detected upon connection. The encoding used by the driver is specified on the server using the character_set_server system variable for server versions 4.1.0 and newer."
也就是说,是在连接时查询服务器端的character_set_server值,再确定连接将使用的编码。
不过,官方文档还说,"要想覆盖客户端上的自动检测编码功能,可在用于连接到服务器的URL中使用“characterEncoding”属性。"
版权声明:本文为博主原创文章,未经博主允许不得转载。
相关推荐
项目亲自测试,绝对可以正常运行 JavaWeb家居电子商城管理系统,没有spring框架,纯servlet,线上...1、修复保存成功后提示信息乱码的问题 2、修复商品管理时产品图片不能上传功能的问题 3、修复评价模块的问题等等
个人笔记平台环境要求1、jdk1.6+2、mysql5.0+3、apache-tomcat-6.0+安装方法1、将压缩包中的Anynote.war文件拷贝到Tomcat的webapps目录下, 启动Tomcat后,会自动生成Anynote文件夹;2、如果使用Mysql,创建UTF-8...
2 jdbc.url=jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=UTF-8 3 jdbc.username=test 4 jdbc.password=12345 现在, 我们来编写 Java 代码吧. 1 /*******************************************...
Tomcat JSP Web 开发中的乱码问题小结 164 第九章 开发Struts 1.x应用 166 9.1 介绍 166 9.2 创建Struts项目 168 9.2.1 创建Web项目 169 9.2.2 加入 Struts开发功能 169 9.3 使用Struts工具 171 9.3.1 Struts配置...
17.2. 安装logstash-input-jdbc插件步骤: 65 17.3. 启动logstash 66 18. HDFS 67 18.1. 退出安全模式: 67 19. Kafka 67 19.1. 优化 67 20. Spark2.x 68 20.1. 示例执行 68 21. YARN 68 21.1. 错误处理 68 22. ...
3.4.2 解决中文乱码 3.4.3 获取客户端数据 3.5 response回应对象的应用 3.5.1 HTTP头信息的操作 3.5.2 设置MIME类型 3.5 13页面重定向操作 3.6 Session会话对象的应用 3.6.1 session的生命周期 3.6.2 会话的创建与...
全书一共被压缩为5个rar,这是第二个!...21.2 中文乱码问题的解决方案 614 21.3 使用过滤器解决中文问题 616 21.4 让tomcat支持中文文件名 620 21.5 国际化与本地化 621 21.5.1 locale 621.. 21.5.2 资源包 623 ...
全书一共被压缩为5个rar,这是第五个!...21.2 中文乱码问题的解决方案 614 21.3 使用过滤器解决中文问题 616 21.4 让tomcat支持中文文件名 620 21.5 国际化与本地化 621 21.5.1 locale 621.. 21.5.2 资源包 623 ...
全书一共被压缩为5个rar,这是第四个!...21.2 中文乱码问题的解决方案 614 21.3 使用过滤器解决中文问题 616 21.4 让tomcat支持中文文件名 620 21.5 国际化与本地化 621 21.5.1 locale 621.. 21.5.2 资源包 623 ...
全书一共被压缩为5个rar,这是第三个!...21.2 中文乱码问题的解决方案 614 21.3 使用过滤器解决中文问题 616 21.4 让tomcat支持中文文件名 620 21.5 国际化与本地化 621 21.5.1 locale 621.. 21.5.2 资源包 623 ...
1.2 Tomcat的安装及测试 1.3 MyEclipse的安装及配置 1.4 MySQL的安装及配置 第2章 HTML及其应用 2.1 网页设计流程 2.2 HTML的基础知识 2.3 HTML文档结构 2.4 HTML常用标记 第3章 CSS及其应用 3.1 CSS的基础...
14、JSP乱码如何解决? 36 15、session 和 application的区别? 36 16、jsp有哪些内置对象?作用分别是什么? 36 17、Jsp有哪些动作?作用分别是什么? 37 18、JSP中动态INCLUDE与静态INCLUDE的区别? 37 19、JSP和...
1. jpivot加入其它应用中 解压jpivot.war 文件,在你的应用当中你需要下面的这些文件:... jdbcUrl="jdbc:mysql://localhost/foodmart" catalogUri="/WEB-INF/test/FoodMart.xml"> select {[Measures].[Unit Sales]...
{13.5.1}MySQL}{198}{subsection.13.5.1} {13.6}连接池}{199}{section.13.6} {13.6.1}Wrapper}{199}{subsection.13.6.1} {13.7}DAO}{199}{section.13.7} {13.8}java.util.Date与java.sql.Date比较}{200}{...
常见问题 读取Properties文件出现中文乱码 182 第9章 Java异常处理与反射机制 183 实例73 运用throws、throw、try与catch 183 实例74 throws声明异常的实例 185 实例75 自定义异常类 187 实例76 使用finally...