MySQL garbled problem summary (non-original)

MySQL encoding settings

1. Edit the MySQL configuration file

MySQL configuration file in Windows is generally in the system directory or in the MySQL installation directory named my. ini, you can search, Linux is usually/etc/My. CNF

-- Add a line under the [MySQL] tag

Default-character-set = utf8

-- Add three lines under the [mysqld] label

Default-character-set = utf8

Character-set-Server = utf8

Collation-Server = utf8_general_ci

Init_connect = 'set collation_connection = utf8_general_ci'

Init_connect = 'set names utf8'

Lower_case_table_names = 1 // The table name is case-insensitive (this is irrelevant to the encoding)

-- Add a line under the [mysql. Server] label

Default-character-set = utf8

-- Add a line under the [mysqld_safe] label

Default-character-set = utf8

-- Add a line under the [client] tag

Default-character-set = utf8

Ii. Restart the MySQL Service

For Windows, you can operate in the service manager or use the command line:

Net stop MySQL press ENTER

Net start MySQL press ENTER

The service name may not be set to MySQL. Set the service name as per your needs.

In Linux, service MySQL restart is used.

If the startup fails, check whether the configuration file is set incorrectly.

3. view the setting result

Log on to the MySQL command line Client: Open the command line

Mysql-uroot-P press ENTER

Enter Password

After entering MySQL, run: Show variables like "% char % ";

The display result should be similar to the following:

| Character_set_client | utf8 |

| Character_set_connection | utf8 |

| Character_set_database | utf8 |

| Character_set_results | utf8 |

| Character_set_server | utf8 |

| Character_set_system | utf8 |

| Character_sets_dir |/usr/share/MySQL/charsets/|

If the encoding is not utf8, check the configuration file or use the MySQL command to set it:

Set character_set_client = utf8;

Set character_set_server = utf8;

Set character_set_connection = utf8;

Set character_set_database = utf8;

Set character_set_results = utf8;

Set collation_connection = utf8_general_ci;

Set collation_database = utf8_general_ci;

Set collation_server = utf8_general_ci;

Some of the above commands are only valid for the current logon, so they are not very useful.

4. Create a database and import data

Before importing an SQL script file, make sure that the script file and content are in UTF-8 encoding format,

Log on to the MySQL command line in the same way as above, and use the database name to enter the corresponding database

Set names utf8;

Source SQL script file name;

V,ProgramConnection string (this item is irrelevant to MySQL settings and used for Program Development)

For drivers of older jdbc versions, the following similar format can be used for connection character creation:

JDBC: mysql: /// 127.0.1: 3306/test? Useunicode = true & characterencoding = UTF-8

Vi. Appendix

If you cannot change the database configuration file, you can use the following method (not necessarily all files are valid ):

1. Set the database encoding to UTF-8 during database creation

For example, create database 'test' default Character Set utf8;

2. When importing Database SQL, make sure that the SQL file is UTF-8 encoded.

Enter the MySQL command line and enter SET names utf8;

Then go to the database use test;

Import the SQL script source test. SQL;

3. The connection string is similar to the following: (development-related, non-database settings)

JDBC: mysql: /// 127.0.1: 3306/test? Useunicode = true & characterencoding = UTF-8

To sum up, garbled characters occur when writing and reading database files:

I. Whether the encoding is consistent with the database encoding when writing data at the front-end connection

<? PHP

$ Conn = @ mysql_connect ("localhost", "root", "") or die ("database connection error ");

Mysql_select_db ("millia", $ conn );

Mysql_query ('set names utf8'); // use utf8 instead of UTF-8 Chinese Encoding

?>

Ii. view the MySQL database Encoding

Mysql> show variables like 'character _ SET _ % ';

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

| Variable_name | value |

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

| Character_set_client | Latin1 |

| Character_set_connection | Latin1 |

| Character_set_database | Latin1 |

| Character_set_results | Latin1 |

| Character_set_server | Latin1 |

| Character_set_system | utf8 |

| Character_sets_dir |/usr/share/MySQL/charsets/|

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

7 rows in SET (0.00 Sec)

Mysql> show variables like 'collation _ % ';

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

| Variable_name | value |

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

| Collation_connection | latin1_swedish_ci |

| Collation_database | latin1_swedish_ci |

| Collation_server | latin1_swedish_ci |

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

3 rows in SET (0.00 Sec)

The default value is Latin1 in Sweden, which is replaced by our own encoding. For example, utf8: the problem of garbled external access data lies in the connection layer, the solution is to execute the following statement before sending the query:

Set names 'utf8 ';

It is equivalent to the following three commands:

Set character_set_client = utf8;

Set character_set_results = utf8;

Set character_set_connection = utf8;

Generally, you only need to execute thisCodeThe problem is solved. The following describes how to create a database and a data table and set it to our own encoding format.

Encoding settings when creating a database

Mysql> Create Database Name characterSet utf8;

Encoding settings when creating a table

Create Table 'type' ('id' int (10) unsigned not null auto_increment, 'flag _ deleted' Enum ('y', 'n ') character Set utf8 not null default 'n', 'flag _ type' int (5) not null default '0', 'Type _ name' varchar (50) character Set utf8 not null default '', primary key ('id '))Default charset = utf8;

Modify the database to utf8.

Mysql> alter database name Character Set utf8;

Utf8is used to modify the table by default.

Mysql> alter table type character set utf8;

Use utf8 to modify Fields

Mysql> alter table type modify type_name varchar (50) Character Set utf8;