Ubantu Modify MySQL default encoding

After starting MySQL, log in as root for MySQL

[Email protected]:~# mysql-uroot-proot

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 | /usr/share/mysql/charsets/|
+--------------------------+----------------------------+

Modify the/etc/mysql/my.cnf file under Linux

Locate the client configuration [clients] below to add
Default-character-set=utf8 default character set is UTF8
When you find [mysqld] Add
Default-character-set=utf8 default character set is UTF8
init_connect= ' Set NAMES UTF8 ' (set to use UTF8 encoding when connecting to MySQL database, so that the MySQL database runs as UTF8)

After modification, restart MySQL, check show variables like ' character% ';
+--------------------------+----------------------------+
| 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/|
+--------------------------+----------------------------+

This method is also valid for the standard MySQL version, and for/etc/my.cnf files, you need to my-large.cnf a copy of the Mysql/support-files folder from CP to/ETC/MY.CNF



Ps:

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;