MySQL encoding settings

MySQL encoding settings

MYSQL 2009-09-11 15:37 Read 73 comments 1 font size: Big middle and small
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 is Swedish latin1, which is replaced by our own code, such as UTF8:
The problem of garbled external access data is on this connection connection layer, the workaround is to execute the following sentence before sending the query:

1. SET NAMES ' UTF8 ';

It is equivalent to the following three-sentence instruction:
SET character_set_client = UTF8;
SET character_set_results = UTF8;
SET character_set_connection = UTF8;

This is usually done only by executing the code before the access, and the following is the creation of the database and the data table , set to our own encoding format.
2. Create a database
Mysql> CREATE database name character set UTF8;

3. Create a table
CREATE TABLE ' type ' (
' id ' int (ten) 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 (character set UTF8 not NULL default ' ',
PRIMARY KEY (' id ')
) DEFAULT Charset=utf8;

4. Modify the database into UTF8.
mysql> ALTER DATABASE name character set UTF8;

5. Modify the table by default with UTF8.
mysql> ALTER TABLE type character set UTF8;

6. Modify the field with UTF8
MySQL> ALTER TABLE type modify type_name varchar (CHARACTER SET UTF8;

===================================================================

?

Recently started using MySQL, used to be Oracle, too expensive, hehe
The code is the most difficult problem of MySQL, research, summarize the results, some of the experience from other people, if there is something wrong, please be enthusiastic ...

Setup steps:

First, edit the MySQL configuration file
MySQL configuration file under Windows generally under the system directory or in the MySQL installation directory named my.ini, can be searched, Linux is generally/etc/my.cnf

--Add three lines under the [Mysqld] tab
Default-character-set = UTF8
Character_set_server = UTF8
Lower_case_table_names = 1//table name is case insensitive (this is not encoded)

--Add a line under the [MySQL] tab
Default-character-set = UTF8

--Add a line under the [Mysql.server] tab
Default-character-set = UTF8

--Add a line under the [Mysqld_safe] tab
Default-character-set = UTF8

--Add a line under the [Client] tab
Default-character-set = UTF8

Second, restart the MySQL service
Windows can operate in Service Manager, or you can use the command line:
net stop MySQL Enter
net start MySQL return
The service name may not necessarily be MySQL, please press your own settings

Linux is now using service MySQL restart

If a startup failure occurs, check that the configuration file has no setup errors

?

?

Third, view settings results
Log on to the MySQL command line client: Open command line
Mysql–uroot–p Enter
Enter password
Enter MySQL after execution: show variables like "%char%";
The results should look 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 still not UTF8, check the configuration file or use the mysql command to set it up:
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 login, so they are not useful.

Iv. building a library to import data
Before importing the SQL script file, make sure that the script file and the content format are in UTF-8 encoded format.
Log in to MySQL command line with the above method, use the library name to enter the corresponding database
Set names UTF8;
SOURCE SQL script file name;

V. Program connection string (this item is not related to MySQL settings, and is used for program development )
For older JDBC versions of drivers, the connection character creator can use a similar format:
Jdbc:mysql://127.0.1:3306/test?useunicode=true&characterencoding=utf-8

?

?

Vi. Appendices
If you cannot change the database configuration file, you can take the following method (not guaranteed to be all valid):
1. Set database code to UTF-8 when building database
For example, create database ' test ' default character set UTF8;

2. When importing database SQL , make sure the SQL file is utf-8 encoded
Enter set names UTF8 after entering MySQL command line;
Re-enter the database use test;
In the import 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

?

?

?

MySQL encoding settings