MySQL Character set issues

First, MySQL's character set problem is mainly two concepts, one is character sets, one is collations, the former is character content
and coding, which are some of the rules for comparing operations on the former. These two parameter sets can be four levels in a DB instance, a single database, a table, a column, etc.
Do not specify.

For users, it is generally recommended to use UTF8 encoding to store data. To solve the garbled problem, is not only the MySQL data storage problem, but also
is related to how the user's program files are encoded, the user program, and how the MySQL database is connected.

First of all, MySQL has the default character set, which is determined when the installation, when compiling MySQL can be default_charset=
UTF8 and default_collation=utf8_general_ci These two parameters (MySQL5.5 version, version 5.1 with--with-charset=
UTF8--with-collation=utf8_general_ci) to specify the default character set as UTF8, which is also the most permanent way, after this designation,
The encoding of the client connecting to the database is also UTF8 by default, and the application does not require any processing.

Unfortunately, many people do not specify these two parameters when compiling and installing MySQL, most of them are installed by binary program, that
The default character set for MySQL is latin1. At this point, we can still specify MySQL's default character set, which is increased by my.cnf file
Two parameters:
1. Under [mysqld] Add
Default-character-set=utf8 (MySQL version 5.5 added Character-set-server=utf8)
2. Add under [client]
Default-character-set=utf8
So we don't have to specifically specify the UTF8 character set when we build the database. This writing in the configuration file solves the problem of data storage and comparison.
, but the connection to the client is not useful, the client usually need to specify UTF8 mode connection in order to avoid garbled. Which is the general set of Legends.
Names command.In fact, the set names UTF8 command corresponds to the following server-side commands:
SET character_set_client = UTF8;
SET character_set_results = UTF8;
SET character_set_connection = Xutf8;
However, these three parameters cannot be written in the config file my.cnf. Can only be modified dynamically by the set command. What we need is to write a job in the config file.
The way of Yong-yi.At this time, whether there is a solution to the problem on the service side, the idea is to set in the Init_connect. This command is in every
When a normal user is connected, the connection character set is triggered by adding the following line to the [mysqld] section:
Under [Mysqld], add:
init_connect = ' SET NAMES UTF8 '
summary:
1, preferred to specify two parameters when compiling and installing MySQL use UTF8 encoding.
2 , Zeshun set two parameters in the configuration file my.cnf or My.ini, and set the Init_connect parameters.
3 , third sets two parameters in the configuration file my.cnf or My.ini, and the client connection specifies the set names command.
4 ,

1-------client----2[Client]3 No-Beep45 #Pipe6 # Socket=Mysql7 Port=330689[Mysql]1011Default-Character-Set=gb23121213-----server----14 15 # The default character set that'll be used when a new schema or table is< Span style= "color: #008080;" >16 # created and no character set is Defined17 character-set< Span style= "color: #808080;" >-server=gb2312          

changing the character set of the My.ini file client, server

1.service mysqld Stop, disable MySQL.

2.cp/etc/my.cnf/etc/my.cnf.bak, it's a good habit to make a backup before modifying it.

Modify My.cnf or My.ini (only modify the configuration file and restart the server for permanent entry)
Vi/etc/my.cnf
Add under [client], client for the control of clients, have not tried, there can be no need to add.
Default-character-set=utf8
Under [mysqld] Add, mysqld for the control server side, changed, OK.
Default-character-set=utf8

3.service mysqld restart, restart.

4.show variables like '%char% '; view.

The following is the network reprint, compare whole.

//////////////////////////////////////////

View MySQL character set MySQL garbled source yes MySQL characters

Ubuntu 10.04 mySql Start, stop, restartStart: Sudo/etc/init.d/mysql start
Stop: Sudo/etc/init.d/mysql stop
Restart: sudo/etc/init.d/mysql restart

Note: Files copied from Windows cannot be directly added to the database under Linux because the character set is different so that it can be turned on even if the display is Chinese and cannot be displayed in the database, so it is possible to copy the file into another Linux file.

MySQL garbled the root of the problem is incorrect MySQL character set, this article summarizes the command to view the MySQL character set. This includes viewing the MySQL database server character set, viewing the MySQL database character set, and the character set of the data tables and fields, the character sets supported by the currently installed MySQL, and so on.

First, view the MySQL database server and database character set.

Mysql> Show variables like '%char% ';

Second, view the character set of the MySQL data Table (table).

Mysql> Show Table status from sqlstudy_db like '%countries% ';

Third, view the character set of the MySQL data column (column).

Mysql> show full columns from countries;

Iv. View the character sets supported by the currently installed MySQL.

Mysql> Show CharSet;

Mysql> show Char set;

View the MySQL character set command above for Windows & Linux.

1. Find the location of the cnf file for MySQL
Find/-iname ' *.cnf '-print

/usr/share/mysql/my-innodb-heavy-4g.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-small.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-huge.cnf
/usr/share/texmf/web2c/texmf.cnf
/usr/share/texmf/web2c/mktex.cnf
/usr/share/texmf/web2c/fmtutil.cnf
/usr/share/texmf/tex/xmltex/xmltexfmtutil.cnf
/usr/share/texmf/tex/jadetex/jadefmtutil.cnf
/usr/share/doc/mysql-server-community-5.1.22/my-innodb-heavy-4g.cnf
/usr/share/doc/mysql-server-community-5.1.22/my-large.cnf
/usr/share/doc/mysql-server-community-5.1.22/my-small.cnf
/usr/share/doc/mysql-server-community-5.1.22/my-medium.cnf
/usr/share/doc/mysql-server-community-5.1.22/my-huge.cnf

2. Copy small.cnf, my-medium.cnf, MY-HUGE.CNF, my-innodb-heavy-4g.cnf one to/etc, named MY.CNF
Cp/usr/share/mysql/my-medium.cnf/etc/my.cnf

3. Modify MY.CNF or My.ini (version 5.6)
Vi/etc/my.cnf
Add under [client]
Default-character-set=utf8
Add under [mysqld]
Default-character-set=utf8

4. Restart MySQL
[Email protected] ~]#/etc/rc.d/init.d/mysql restart
Shutting down MySQL [OK]
Starting MySQL. Determine
[Email protected] ~]# mysql-u root-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 1
Server version:5.1.22-rc-community-log MySQL Community Edition (GPL)
Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the buffer.


Some other setup methods are:
Modify by configuration file:
Modify/var/lib/mysql/mydb/db.opt
Default-character-set=latin1
Default-collation=latin1_swedish_ci
For
Default-character-set=utf8
Default-collation=utf8_general_ci
Re-starting MySQL:
[Email protected] ~]#/etc/rc.d/init.d/mysql restart

1. Change the command for the client, server, database character set:

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 Character_set_system=utf8;
mysql> set Collation_connection=utf8;
mysql> set Collation_database=utf8;
mysql> set Collation_server=utf8;

2. View the character set:

Show variables like ' collation_% ';

Show variables like ' character_set_% ';

3. Setting the database character set:

#修改数据库字符集
ALTER DATABASE mini default character set = gb2312;
#创建数据库设置字符集
Create DATABASE mydb character Set gb2312;

4. Modify a single table character set:

ALTER TABLE pub_logs default character set = gb2312;
ALTER TABLE Pub_logs convert to character set gb2312;

5. Generate a batch change table character set:

SELECT A.table_type,concat (' Alter TABLE ', A.table_name, ' default character set = gb2312; ') From INFORMATION_SCHEMA. TABLES A
WHERE a.table_schema= ' MINI ' and a.table_type= ' BASE TABLE ';

6. Generate Batch change column character set

SELECT CONCAT (CONCAT (CONCAT (' Alter TABLE ', C.table_name, ' Modify '), CONCAT (', C.column_name, '), C.column_type), ', ' Character Set gb2312 COLLATE gb2312_chinese_ci; ') As Clou
From
Information_schema. COLUMNS C,information_schema. TABLES A
WHERE c.table_schema= ' MINI '
and A.table_name=c.table_name
and a.table_type= ' BASE TABLE '
and c.data_type= ' varchar ';

Character set issues for MySQL stored procedures

MySQL stored procedures often occur in the character set problem, which requires you to master the MySQL stored procedures in the process of character set problems, the following will give you to resolve the MySQL stored procedures in the occurrence of character set problems.

MySQL storage is a very important work in MySQL database operation, it is also used in the work of the normal people. Because the character set cannot be defined when a parameter is defined in a stored procedure, the global variable character_set_server is read by default when the stored procedure is called. It also reads only the value of the global variable at mysqld startup as the default transport character set in the stored procedure. Therefore, if the data table/field uses the system's default character set (such as Latin1), the call to the stored procedure to update some non-English string fields does not occur, but if the data table/field's character set is not the system default character set (for example, Latin1 is the default), The data table uses UTF8), there will be a problem.

Workaround:

In MY.CNF (under Windows is My.ini), add a line:

Default-character-set=utf8

Or

Character-set-server=utf8

Or you can. It does not work if it is updated with the SET syntax on the MySQL command line only after Mysqld is started.

MySQL Character set issues