唉，看到網(wǎng)上這么多的關(guān)于MySQL中文編碼的問題。今天自己碰到了，網(wǎng)上沒有找到我的完整答案，發(fā)現(xiàn)真的是好暈啊！~
??? 不過幸好弄出來了，沒有讓整個(gè)下午的時(shí)候白費(fèi)哦。
就今天所看到的，和自己所操作的一起總結(jié)下哦。
我用的工具有：
MyEclipse 5.1.0
JDK 1.6.0
mysql
-
connection
-
java
-
3.0
.
2
MySQL?
5.0
.
27
這里工具的版本是很重要的，呆會(huì)一點(diǎn)一點(diǎn)把它講來，所以把全部環(huán)境都列出來了！~
1、data too long for column問題
問題描述：在MySQL下面輸入select, insert等SQL語句，對(duì)于漢字均出現(xiàn)此種情況
????字段為char或varchar型，長(zhǎng)度為255，絕對(duì)不會(huì)是真的字段長(zhǎng)度不夠的問題。請(qǐng)大家查看這篇貼子：http://blog.sina.com.cn/u/53b0d5dc0100097v，有此問題的詳解。我后來的問題也基本上是在此篇基本上解決的。重要的是：--default-character-set=utf8參數(shù)
2、語句在MySQL CMD下面輸入正確，但是在Java中執(zhí)行同樣的SQL語句，卻無法工作。
問題描述：如果SQL語句中帶中文的話，就會(huì)出現(xiàn)下面的異常，
java.sql.SQLException:?Syntax?error?or?access?violation,??message?from?server:?
"
You?have?an?error?in?your?SQL?syntax;?check?the?manual?that?corresponds?to?your?MySQL?server?version?for?the?right?syntax?to?use?near?''中??at?line?1
"
?at?com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:
1962
)
?at?com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:
1163
)
?at?com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:
1257
)
?at?com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:
1218
)?
進(jìn)行好上面第一點(diǎn)的配置以后還是無濟(jì)于事。
??? 通過細(xì)心觀察，總覺得不應(yīng)該是MySQL的配置問題了，因?yàn)樵贛ySQL CMD下面能夠很正常地輸入，所以懷疑是mysql connector的問題。
??? 查閱了mysql connector的docs后，找到了一些內(nèi)容：
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?via?Statement.execute(),?Statement.executeUpdate(),?Statement.executeQuery()?as?well?as?all?PreparedStatement?and?CallableStatement?parameters?with?the?exclusion?of?parameters?set?using?setBytes(),?setBinaryStream(),?setAsiiStream(),?setUnicodeStream()?and?setBlob().
意思就是說：所有從JDBC驅(qū)動(dòng)器到服務(wù)器的字符串都將會(huì)自動(dòng)地從本地JAVA Unicode編碼形式轉(zhuǎn)換為客戶端的字符編碼。
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?the?useUnicode?and?characterEncoding?properties.
Starting?with?MySQL?Server?4.1,?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?in?ResultSets.
在這里就涉及到了版本的問題：在MySQL 4.1以前的版本中，連接器對(duì)每一個(gè)connection支持單個(gè)的字符編碼；而在MySQL 4.1開始以后的版本中，連接器在客戶端和服務(wù)器端之間支持單個(gè)的字符編碼。
重要的就是在這里了，不知道因?yàn)槭鞘裁丛颍业倪B接器客戶端的編碼總是與服務(wù)器的對(duì)接不上，因此在這里將采用手動(dòng)指定客戶端編碼。如下所述：
To?override?the?automatically-detected?encoding?on?the?client?side,?use?the?characterEncoding?property?in?the?URL?used?to?connect?to?the?server.
對(duì)于useUnicode和characterEncoding兩個(gè)連接屬性，下面介紹一點(diǎn)點(diǎn)：
| useUnicode | Should the driver use Unicode character encodings when handling strings? Should only be used when the driver can't determine the character set mapping, or you are trying to 'force' the driver to use a character set that MySQL either doesn't natively support (such as UTF-8), true/false, defaults to 'true' |
| characterEncoding | If 'useUnicode' is set to true, what character encoding should the driver use when dealing with strings? (defaults is to 'autodetect') |
因此，在連接中，我把連接的URL 從原來的：
jdbc:mysql://localhost:3306/[DBName]","[username]","[password]" 改成為：jdbc:mysql://localhost:3306/[DBname]?useUnicode=true&characterEncoding=UTF-8","[username]","[password]"。
經(jīng)過這樣子的改動(dòng)以后，程序能夠正常地輸入中文值或作為條件參數(shù)。
下面還有關(guān)于連接器的一點(diǎn)點(diǎn)內(nèi)容：
To?allow?multiple?character?sets?to?be?sent?from?the?client,?the?"UTF-8"?encoding?should?be?used,?either?by?configuring?"utf8"?as?the?default?server?character?set,?or?by?configuring?the?JDBC?driver?to?use?"UTF-8"?through?the?characterEncoding?property.
總結(jié)：
此問題可能還在其它地方有問題，但是到現(xiàn)在為止，中文不能出現(xiàn)在SQL語句中的問題已經(jīng)解決。如果有網(wǎng)友能夠有進(jìn)一步地突破，還希望不吝指教。謝謝了！~? 當(dāng)然，如果本篇有一些不足之處，也請(qǐng)指出哦！