Mysql encoding settings _ MySQL

Mysql encoding settings MYSQL2009-09-1115: 37 read 73 comments 1 font size: Large and medium small mysqlSHOWVARIABLESLIKEcharacter_set _ %; 43; -------------------------- 43; -------------------------- mysql encoding settings

MYSQL read 73 comments 1 font size: Large, Medium, 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 value is latin1 in Sweden, which is replaced with our own encoding, such as 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:

1. 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, the problem is solved only by executing this code before access. The following describes how to create a database and a data table and set it 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(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;

4. modify the database to utf8.
Mysql> alter database name character set utf8;

5. use utf8to modify the table by default.
Mysql> alter table type character set utf8;

6. use utf8 to modify fields
Mysql> alter table type modify type_name varchar (50) character set utf8;

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

I recently started using MySql. I used Oracle in the past. it's too expensive.
Encoding is the most difficult problem for MySql. I have studied it and summarized some of the results from other people's experience. if there is anything wrong with coding, please join us...

Procedure:

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 three rows of default-character-set = utf8character_set_server = utf8lower_case_table_names = 1 to the [mysqld] label. The table name is case insensitive (this is irrelevant to the encoding) -- add a line of default-character-set = utf8 under the [mysql] tag -- in [mysql. add a default-character-set = utf8 under the server] label -- add a default-character-set = utf8 under the [mysqld_safe] label -- add a 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;

5. program connection 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