How to Correctly Write UTF-8 Strings into MySQL Through JDBC Interface

MySQL configuration is crucial for handling UTF-8 character encoding correctly. Ensure that the MySQL server and client are configured appropriately to avoid character garbling.

Ensuring Correct Configuration

To verify your MySQL configuration, execute these commands:

show variables like 'character%';
show variables like 'collation%';

Configuration Modifications

For MySQL versions 5.1.nn and later (including 5.5.29):

[mysqld]
character-set-server = utf8
character-set-filesystem = utf8

For MySQL versions 5.0.nn and older:

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
default-character-set=utf8
character-set-server=utf8

Connecting to MySQL with Java

Establish a connection to MySQL in your Java code using the JDBC driver and specifying the character encoding explicitly:

con = DriverManager.getConnection("jdbc:mysql://localhost:3306/myDatabase?useUnicode=true&characterEncoding=UTF-8","user","passwd");

Additional Tips

• Use UTF-8 encoding throughout your program, including database queries, result sets, and string literals.
• Consider using a JDBC wrapper library that handles character encoding automatically, such as Spring JDBC.
• Verify that the database tables and columns are created with the correct character set and collation.
• In your specific case, removing the explicit character set setting (query = "set character set utf8";) may have resolved the issue as it might have caused a conflict.