MySQL database Chinese garbled Problem Solution

MySQL database Chinese garbled Problem Solution

After migrating a set of internal systems (confluence), the page is opened and all the question marks are garbled. Cause analysis:

Create a database as follows:

Mysql> create database confluence character set utf8 collate utf8_bin;

Query OK, 1 row affected (0.00 sec)

Mysql> grant all on conflence. * to 'confluence '@' % 'identified by '123 ';

Query OK, 0 rows affected (0.00 sec)

Mysql> grant all on confluence. * to 'confluence '@ 'localhost' identified by '123 ';

Query OK, 0 rows affected (0.00 sec)

Mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

The character set is UTF-8. Why is it garbled.

The encoding format of the database is as follows:

Mysql> show variables like '% char % ';

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

| 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 | latin1 |

| Character_set_system | utf8 |

| Character_sets_dir |/usr/share/mysql/charsets/|

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

8 rows in set (0.00 sec)

Find the cause of the problem. character_set_server does not support UTF8. Modify the mysql configuration and reload it.

1./etc/init. d/mysqld stop

2. vi/etc/my. cnf

Add:

Character_set_server = utf8

Init_connect = 'set NAMES utf8'

3./etc/init. d/mysqld start

View the database encoding character set:

Mysql> show variables like '% char % ';

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

| 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/|

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

8 rows in set (0.00 sec)

After the data is re-imported, the interface returns to normal.

Tip:

When configuring confluence to connect to the mysql database, remember to modify the URL:

Original:

Jdbc: mysql: // localhost/confluence? SessionVariables = storage_engine % 3 DInnoDB </property>

If you have Chinese characters, you must allow utf8. Otherwise, the default value is latin1. You need to modify the name as follows:

Jdbc: mysql: // localhost/confluence? AutoReconnect = true & amp; useUnicode = true & amp; characterEncoding = utf8 & amp; sessionVariables = storage_engine % 3 DInnoDB </property>

Uses Unicode encoding, character encoding for UTF-8, automatic reconnection.

This article permanently updates the link address: