MySQL > modify MySQL character set

  1. Find 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 - 4 g. CNF / usr/share/doc/MySQL server - community - 5.1.22 / my - large. The CNF/usr/share/doc/MySQL server - community - 5.1.22 / my - small, CNF The/usr/share/doc/MySQL server - community - 5.1.22 / my - medium. The CNF/usr/share/doc/MySQL server - community - 5.1.22 / my - huge. CNFCopy the code
  1. CNF, my-medium. CNF, my-huge. CNF, and my-innodb-heavy-4g.cnf are copied to /etc and named as

my.cnf

cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
Copy the code
  1. Modify my CNF
Run the following command to add default-character-set=utf8 under [client] : vi /etc/my. CNF Add default-character-set=utf8 under [mysqld] : vi /etc/myCopy the code

4. Restart the MySQL

[root@bogon ~]# Shutting down 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.Copy the code
  1. View character set Settings
mysql> show variables like 'collation_%'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 3 rows in the set (0.02 SEC) mysql > show variables like 'character_set_ %'; +--------------------------+----------------------------+ | 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 the set (0.02 SEC) mysql >Copy the code

Some other Settings:

Change the database character set

  mysql>use mydb
  
  mysql>alter database mydb character set utf-8;
Copy the code

Creates a database character set for the specified database

  mysql>create database mydb character set utf-8;
Copy the code

Modify through the configuration file:

Modify/var/lib/mysql/mydb/db. Opt

default-character-set=latin1

default-collation=latin1_swedish_ci
Copy the code

for

default-character-set=utf8

default-collation=utf8_general_ci
Copy the code

Restart the MySQL:

[root@bogon ~]# /etc/rc.d/init.d/mysql restart
Copy the code

MySQL > select * from ‘MySQL’ where ‘MySQL’ = ‘MySQL’;

mysql> set character_set_client=utf8; Query OK, 0 rows affected (0.00 SEC) mysql> set Character_set_connection =utf8; Query OK, 0 rows affected (0.00 SEC) mysql> set Character_set_database =utf8; Query OK, 0 rows affected (0.00 SEC) mysql> set Character_set_results =utf8; Query OK, 0 rows affected (0.00 SEC) mysql> set Character_set_server =utf8; Query OK, 0 rows affected (0.00 SEC) mysql> set Character_set_system =utf8; Query OK, 0 rows affected (0.01sec) mysql> set collation_connection= utF8; Query OK, 0 rows affected (0.01sec) mysql> set collation_database= utF8; Query OK, 0 rows affected (0.01sec) mysql> set collation_server=utf8; Query OK, 0 rows affected (0.01sec)Copy the code

To view:

mysql> show variables like 'character_set_%'; +--------------------------+----------------------------+ | 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 the set (0.03 SEC) mysql > show the variables like 'collation_%'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 3 rows in the set (0.04 SEC)Copy the code

MYSQL character set problem

MySQL Character Set Support has two aspects:

Character set and Collation.Copy the code

Support for character sets is refined to four levels:

Server, database, table and Connection.Copy the code

1.MySQL default character set

MySQL specifies which character set to use for each database, table, and column. But traditional programs don’t use complex configurations to create databases and tables. They use default configurations. So where does the default configuration come from?

(1) MySQL is compiled with a default character set, latin1; When installing MySQL, you can specify a default character set in the configuration file (my.ini). If not specified, this value is inherited from the compiled character set. (3) When mysqld is started, you can specify a default character set in the command line argument. If not, this value inherits from the configuration file configuration. In this case, character_set_server is set to the default character set. (4) When creating a new database, the default character set for the database is character_set_server unless explicitly specified. (5) When a database is selected, character_set_DATABASE is set to the default character set for the database; (6) When creating a table in the database, the default character set for the table is character_set_DATABASE, which is the default character set for the database; (7) When a column is set in a table, the default character set for this column is the default character set for the table unless explicitly specified;Copy the code

To summarize, if nothing is changed, all tables in all databases will be stored in latin1 for all fields. However, if we install MySQL, we will generally choose multi-language support. In other words, The setup program automatically sets defaulT_CHARACTER_SET to UTF-8 in the configuration file, which ensures that all fields of all tables in all databases are stored in UTF-8 by default. The default character set is latin1(ISO_8859_1). The default character set is latin1(ISO_8859_1).

mysql> SHOW VARIABLES LIKE 'character%'; +--------------------------+---------------------------------+ | Variable_name | Value | +--------------------------+---------------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | D: "mysql - 5.0.37" share "charsets" | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + mysql > SHOW the VARIABLES  LIKE 'collation_%'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci |  +----------------------+-----------------+Copy the code

3. Change the default character set

Ini file: default-character-set = utF8 Character_set_server = utf8 The service mysql restart command is used

mysql> SHOW VARIABLES LIKE 'character%'; See, Found the database code have been changed to utf8 + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Variable_name Value | | +--------------------------+---------------------------------+ | character_set_client | utf8Copy the code

Mysql-server-5.5.48-1.el6.x86_64. RPM mysql-server-5.5.48-1.el6.x86_64.

Run the following command to copy xxx.cnf from CD /usr/share/mysq/and rename it to my.cnf under [mysqld]

character_set_server = utf8

Save the exit