MySQL garbled problem in Linux

First, the operation

MySQL The default character set is latin1 , but the character set used by most of our programs is UTF8, We just need to change MySQL 's character set.

1 ) View the default encoding

Showvariables like ' character% ';

+--------------------------+----------------------------+

| variable_name | Value |

+--------------------------+----------------------------+

|character_set_client | Latin1 |

|character_set_connection | Latin1 |

|character_set_database | Latin1 |

|character_set_filesystem | binary |

|character_set_results | Latin1 |

|character_set_server | Latin1 |

|character_set_system | UTF8 |

|character_sets_dir | /usr/share/mysql/charsets/|

+--------------------------+----------------------------+

Mysql The default encoding is latin1

2) View the default sort method

Showvariables like ' collation_% ';

+----------------------+-----------------+

| variable_name | Value |

+----------------------+-----------------+

|collation_connection | Latin1_swedish_ci |

|collation_database | Latin1_swedish_ci |

|collation_server | Latin1_swedish_ci |

+----------------------+-----------------+

3 ) to modify the default character set

Modify a file /etc/my.cnf content

in the [Client] Add the following statement below

Default-character-set=utf8

in the [Mysqld] under Add

Default-character-set=utf8

init_connect= ' SET NAMES UTF8 ' # # Set Connection MySQL is to use UTF8 Coding

after you've modified it, restart it . MySQL Services can

4 another method of modifying character encoding

Login MySQL , enter the following:

Mysql>set character_set_client = UTF8;

Mysql>set character_set_connection = UTF8;

Mysql>set character_set_database = UTF8;

Mysql>set character_set_results = UTF8;

Mysql>set character_set_server = UTF8;

Mysql>set collation_connection = UTF8;

Mysql>set collation_database = UTF8;

Mysql>set collation_server = UTF8;

5 ) to re-view

Showvariables like ' character% ';

+--------------------------+---------------------------------+

| variable_name | Value |

+--------------------------+---------------------------------+

|character_set_client | UTF8 |

|character_set_connection | UTF8 |

|character_set_database | UTF8 |

|character_set_filesystem | binary |

|character_set_results | UTF8 |

|character_set_server | UTF8 |

|character_set_system | UTF8 |

|character_sets_dir |/usr/share/mysql/charsets/|

+--------------------------+---------------------------------+

Showvariables like ' collation_% ';

+----------------------+-----------------+

| variable_name | Value |

+----------------------+-----------------+

|collation_connection | Utf8_general_ci |

|collation_database | Utf8_general_ci |

|collation_server | Utf8_general_ci |

+----------------------+-----------------+

6 ) using JDBC Connection MySQL you need to change the connection string to the following way:

Jdbc:mysql://localhost/mysql?useunicode=true&characterencoding=utf-8

have been UTF8 the way to connect

Second, the concept

Character-set-server/default-character-set : The server character set, which is used by default.

Character-set-database : Database character set.

character-set-table : Database table character Set.

character-set-client : The character set of the client. The client default character set. When a client sends a request to the server, the request is encoded in that character set.

Character-set-results : Result character Set. When the server returns a result or information to the client, the result is encoded in that character set.

To set the character set encoding using statements uniformly: Set names UTF8

Third, import and export to prevent garbled

1. Export Database using commands (input in cmd )

Mysqldump-u User name - P Password - H Server Address database name --default-character-set=utf8--hex-blob > Export Database . SQL

--default-character-set=utf8 represents the Use Urf8 encode Export Data

--hex-blob representatives to - Binary Export Database

2. Import Database using commands (input in cmd )

Mysql-u User name - P Password - H Server Address database name --default-character-set=utf8--hex-blob < Export database . SQL

In addition to using the commands above to import the database, you can also log in MySQL , using Source Command Import

mysql> Source Export Database . SQL

Note:blobThe field holds binary data, so it has no character set encoding,windowand theLinuxuse the above method or there will be garbled problems, it is recommended toblobtype is modified totexttype, beforeno garbled characters are present. blobtype data towardtexttype conversion, write yourself a simple code, fromblobThe read data is stored as a stringtextfield.

MySQL garbled problem in Linux