MySQL Character Set this thing

Today, a development colleague uses the JDBC connection database to execute a statement with no result set, but executes the same statement through Sqlyou with a return result.

The statement that executes the Where condition contains Chinese, which should be caused by the character set

View the character set of the current database

Mysql>Show variables like '%charac%';+--------------------------+----------------------------------+|Variable_name|Value|+--------------------------+----------------------------------+|Character_set_client|Utf8||Character_set_connection|Utf8||Character_set_database|Latin1||Character_set_filesystem| binary                           ||Character_set_results|Utf8||Character_set_server|Latin1||Character_set_system|Utf8||Character_sets_dir| /Usr/Local/Mysql/Share/Charsets/ |+--------------------------+----------------------------------+8Rowsinch Set(0.00Sec

Discovers that the server-side character set is the default value latin1

A parameter entry was not added in the Discovery configuration file Character-set-server=utf8

So hurry up for character set

Mysql> Selectversion ();+------------+|Version ()|+------------+| 5.6. --Log |+------------+1Rowinch Set(0.00sec) MySQL> SetGlobal Character_set_server=UTF8; Query OK,0Rows Affected (0.00sec) MySQL> SetGlobal Character_set_database=UTF8; Query OK,0Rows Affected (0.00Sec

# Specify the server-side character Set launch instance  Character-set-database will be consistent with Character-set-server
Mysqld--character-set-server=then--default-character-set=utf8# the current Settings can seen by viewing the output of SHOW VARIABLES:

MySQL>SHOW VARIABLES like 'char%';+--------------------------+----------------------------------------+|Variable_name|Value|+--------------------------+----------------------------------------+|Character_set_client|Utf8||Character_set_connection|Utf8||Character_set_database|Latin1||Character_set_filesystem| binary                                 ||Character_set_results|Utf8||Character_set_server|Latin1||Character_set_system|Utf8||Character_sets_dir| /Usr/Local/Mysql/Share/Mysql/Charsets/ |+--------------------------+----------------------------------------+8Rowsinch Set(0.01Sec

Using GDBC is still less than the result, you need to set the charset in the path of the JDBC Connection database

Jdbc:mysql://Localhost/test?useunicode=true&characterencoding=utf8

Cite blog:

The default character set used by the MySQL command line client is latin1, and if you use this to insert Chinese, it will also appear garbled. The workaround is to execute the statement set names ‘utf8’ to tell the server to use UTF-8 encoding to communicate with the client. You can also use set charset ‘utf8’ , it differs from set names only on collation. Set names and set CharSet are equivalent to executing three statements, the specific content can be seen in the MySQL document 10.4 section. I think this method is also possible when using JDBC, so if the specified database address in JDBC does not tell the character set encoding used, the same effect can be achieved by executing the above statement.

Character Set references:

Http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_character_set_connection

Http://dev.mysql.com/doc/refman/5.6/en/faqs-cjk.html

MySQL Character Set this thing