MySQL insert Chinese error: Com.mysql.jdbc.MysqlDataTruncation:Data truncation:incorrect datetime value ...

Summary written in front,

Summary:

When Java through the JDBC link MySQL inserted in Chinese, to ensure that the program can execute normally, and the inserted Chinese is not garbled,

1. MySQL server side, the data table (not the database) encoding settings, to ensure that the support of Chinese, such as GBK, gb2312, Utf-8
2. JDBC Connection configuration, to turn on Useunicode=true, and to set up a Chinese-language encoding, do not need to be consistent with the MySQL table encoding, only need to support the Chinese line. such as Characterencoding=utf8
3. The encoding of the Java file itself needs to support Chinese

=============================================================================

Just learning JDBC, today in the debugging code, when clearly in the company when the normal execution of the INSERT statement, back to the point, the test code is like this.

Database configuration file Mysql.ini

1 driver=com.mysql.jdbc.Driver2 url=jdbc:mysql://127.0.0.1:3306/dedecms  3 user=root4 pass=

Test code

1  Packagedb;2 3 ImportJava.io.FileInputStream;4 Importjava.io.FileNotFoundException;5 Importjava.io.IOException;6 Importjava.sql.Connection;7 ImportJava.sql.DriverManager;8 Importjava.sql.SQLException;9 Importjava.sql.Statement;Ten Importjava.util.Properties; One  A  Public classTest { -     PrivateString driver; -     PrivateString URL; the     PrivateString user; -     PrivateString Pass; -      Public voidInitparam (String paramfile)throwsFileNotFoundException, IOException, classnotfoundexception { -Properties prop =NewProperties (); +Prop.load (NewFileInputStream (Paramfile)); -Driver = Prop.getproperty ("Driver"); +url = prop.getproperty ("url"); Auser = Prop.getproperty ("User"); atpass = Prop.getproperty ("Pass"); - Class.forName (driver); -     } -      -      Public intInsertData (String sql)throwsSQLException { -         Try ( inConnection conn =drivermanager.getconnection (URL, user, pass); -Statement stmt =conn.createstatement ()) { to             returnstmt.executeupdate (SQL); +         } -     } the      *      Public Static voidMain (string[] args)throwsFileNotFoundException, ClassNotFoundException, IOException, SQLException { $Test T =NewTest ();Panax NotoginsengT.initparam ("Mysql.ini"); -T.insertdata ("INSERT into Jdbc_test (Jdbc_name, Jdbc_desc) VALUES (' Test title ', ' Test content ');"); theSystem.out.println ("OK"); +     } A  the}

Originally in the company can also carry out, take home new database, it is not, the error is as follows,

1Exception in thread "main" Com.mysql.jdbc.MysqlDataTruncation:Data Truncation:incorrect string value: ' \xce\xe4\xba\ Xba\xb5\xd8 ... ' forColumn ' Jdbc_name ' at row 12At Com.mysql.jdbc.MysqlIO.checkErrorPacket (mysqlio.java:3513)3At Com.mysql.jdbc.MysqlIO.checkErrorPacket (mysqlio.java:3447)4At Com.mysql.jdbc.MysqlIO.sendCommand (mysqlio.java:1951)5At Com.mysql.jdbc.MysqlIO.sqlQueryDirect (mysqlio.java:2101)6At Com.mysql.jdbc.ConnectionImpl.execSQL (connectionimpl.java:2548)7At Com.mysql.jdbc.StatementImpl.executeUpdate (statementimpl.java:1605)8At Com.mysql.jdbc.StatementImpl.executeUpdate (statementimpl.java:1524)9At DB. Executesql.insertdata (executesql.java:47)TenAt DB. Executesql.main (executesql.java:119)

Considering the insertion of Chinese, the web search for MySQL's JDBC URL configuration method

1 url=jdbc:mysql://127.0.0.1:3306/dedecms?  Useunicode=true&characterencoding=utf8 

That is, you need to turn on useunicode=true and set up a character set, but you will still see the error after setting, and then try to modify the character set of the eclipse document.

That is, under Preferences->general->workspace->text file encoding, select UTF-8 to make the Java file consistent with the JDBC link configuration.

However, after the discovery of such a setup, there are already problems, think about the MySQL database itself is not set up, and then change the following configuration (with the phpMyAdmin do MySQL client)

Find my current database and change the collation to utf8_unicode_ci

Execute the program again, found that the problem is still there, and then toss a half day, found that not only the database can modify the collation, a single table can also modify the collation,

Change the collation of the table to utf8_unicode_ci , and tick the changes all column collations,

Execute the program again and finally you can see that the data is plugged in properly!

Program test results,

Table content

But then another interesting thing is that regardless of the MySQL server table settings why the code, as long as the JDBC link in the same time to choose the same encoding, the program can execute the success,

Only if the encoding does not support Chinese, the data table will appear garbled, for example,

MySQL data sheet is a Latin code

MySQL link strings are also coded in Latin

1 url=jdbc:mysql://127.0.0.1:3306/dedecms?useunicode=true&characterencoding=  Latin1 

Java file encoding default

And then found that can be executed,

But the data table is all garbled,

And once the code in the data table is modified to support Chinese encoding, such as gb2312

and the MySQL JDBC link is also modified to support the Chinese encoding, gb2312, GBK, utf8 any of the lines, do not need to be consistent with the data table encoding,

1 url=jdbc:mysql://127.0.0.1:3306/dedecms?useunicode=true&characterencoding=utf8

Of course, Eclipse's file encoding must also support Chinese (otherwise, it is impossible to save files containing Chinese), such as Utf-8

In this way, the program can be executed normally, and the Chinese inserted in the data table will not be garbled.

Summary:

When Java through the JDBC link MySQL inserted in Chinese, to ensure that the program can execute normally, and the inserted Chinese is not garbled,

1. MySQL server side, the data table encoding settings, to ensure that the support of Chinese, such as GBK, gb2312, Utf-8
2. JDBC Connection configuration, to turn on Useunicode=true, and to set up a Chinese-language encoding, do not need to be consistent with the MySQL table encoding, only need to support the Chinese line. such as Characterencoding=utf8
3. The encoding of the Java file itself needs to support Chinese

MySQL insert Chinese error: Com.mysql.jdbc.MysqlDataTruncation:Data truncation:incorrect datetime value ...