Modify the MySQL character set under Liunx: 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 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.
5. 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 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 Set (0.02 sec) Mysql>
Some other setup methods are:
modifying the character set of a database Mysql>use MyDB Mysql>alter database mydb character Set utf-8; Create a database character set for a specified database Mysql>create database mydb character Set utf-8;
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
modified via MySQL command line: 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.01 sec) Mysql> set Collation_connection=utf8; Query OK, 0 rows affected (0.01 sec) Mysql> set Collation_database=utf8; Query OK, 0 rows affected (0.01 sec) Mysql> set Collation_server=utf8; Query OK, 0 rows affected (0.01 sec) 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 Set (0.03 sec) 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 Set (0.04 sec)
------------------------------------------------------------------------- "Informative articles reproduced" MYSQL Character Set issues
MySQL Character set support (Character set supports) has two aspects: The character set (Character set) and the Sort method (Collation). Support for character sets is refined to four levels: Servers (server), database, data tables (table), and connections (connection). 1.MySQL Default Character Set MySQL Specifies the character set that can be refined to a database, a table, a column, and what character sets should be used. However, traditional programs do not use a complex configuration when creating databases and datasheets, and they use the default configuration, so where does the default configuration come from? (1) When compiling MySQL, a default character set is specified, and this character set is latin1; (2) When installing MySQL, you can specify a default character set in the configuration file (My.ini), and if not specified, this value is inherited from the compile-time specified; (3) When starting mysqld, you can specify a default character set in the command line arguments, if not specified, this value inherits from the configuration in the configuration file, at this time Character_set_server is set to this default character set; (4) When a new database is created, the character set of the database is set to Character_set_server by default unless explicitly specified; (5) When a database is selected, Character_set_database is set to the default character set of this database; (6) When creating a table in this database, the default character set of the table is set to Character_set_database, which is the default character set of the database; (7) When a column is set in the table, the default character set of the column is the set of defaults for the table unless explicitly specified; Simply summarize, if there is no change anywhere, then all the columns of all the tables in the database are used Latin1 storage, but if we install MySQL, we will generally choose multi-language support, that is, the installer will automatically in the configuration file Default_character_set is set to UTF-8, which guarantees that all the columns of all the tables in the database are stored UTF-8 by default. 2. View the default character set (by default, MySQL's character set is Latin1 (iso_8859_1) In general, the settings for viewing the character set and ordering of the system can be set through the following two commands: 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 VARIABLES like ' collation_% '; +----------------------+-----------------+ | variable_name | Value | +----------------------+-----------------+ | collation_connection | Utf8_general_ci | | Collation_database | Utf8_general_ci | | Collation_server | Utf8_general_ci | +----------------------+-----------------+ 3. Modifying the default character set (1) The simplest method of modification is to modify the character set key values in the MySQL My.ini file, such as Default-character-set = UTF8 Character_set_server = UTF8 After the modification, restart the MySQL services, service MySQL restart Use mysql> show VARIABLES like ' character% '; view, discover that database encoding has been changed to UTF8 +--------------------------+---------------------------------+ | 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 | D: "mysql-5.0.37" Share "charsets" | +--------------------------+---------------------------------+ (2) There is also a way to modify the character set, that is, the command to use MySQL mysql> SET character_set_client = UTF8; |