Modify and view the character set of the mysql database

Modify the MySQL character set in Liunx:
1. find/-iname '*. cnf'-print the cnf File Location of MySQL.
/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 -www.2cto.com-
/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, one of the my-innodb-heavy-4G.cnf to/etc, named my. cnfcp/usr/share/mysql/ My-medium.cnf/etc/my. cnf 3. modify my. cnfvi/etc/my. cnf add default-character-set = utf8 under [client] and add default-character-set = utf8 4 under [mysqld. restart MySQL [root @ bogon ~] #/Etc/rc. d/init. d/mysql restartShutting down MySQL [OK] Starting MySQL. [OK] [root @ bogon ~] # Mysql-u root-pEnter 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 'colation _ % '; + rows + --------------- + | Variable_name | Value | + rows + --------------- + | collation_connection | rows | collation_database | rows | collation_server | rows | + rows + 3 rows in set (0.02 sec) mysql> show variables like 'character _ set _ % '; + bytes + | Variable_name | Value | + bytes + | character_set_client | utf8 | character_set_connection | utf8 | character_set_database | utf8 | bytes | binary | utf8 | | character_set_server | utf8 | character_set_system | utf8 | character_sets_dir |/usr/share/mysql/charsets/| + ---------------------- + rows + 8 rows in set (0.02 sec) mysql> other settings: Modify the character set of the database mysql> use mydb mysql> alter database mydb character set UTF-8; create a character set of the specified database mysql> create database mydb character set UTF-8; modify the configuration file:/var/lib/mysql/mydb/db. optdefault-character-set = latin1default-collation = latin1_swedish_ci is default-character-set = utf8default-collation = utf8_general_ci -www.2cto.com-
Restart MySQL: [root @ bogon ~] #/Etc/rc. d/init. d/mysql restart: 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 af Fected (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 | + bytes + | character_set_client | utf8 | character_set_connection | utf8 | character_set_database | utf8 | bytes | binary | bytes | utf8 | bytes | utf8 | character_set_system | utf8 | character_sets_dir |/usr/share/mysql/charsets/| + ---------------------- +- --------------------------- + 8 rows in set (0.03 sec) mysql> show variables like 'collation _ % '; + rows + --------------- + | Variable_name | Value | + rows + --------------- + | collation_connection | rows | collation_database | rows | collation_server | rows | + rows + 3 rows in set (0.04 sec) ----------- ---------------------------------------------------------- [Repost knowledge] MYSQL Character Set Problem MySQL Character set Support (Character Set Support) has two aspects: Character set and Collation ). The support for character sets is refined to four levels: server, database, table, and connection ). 1. MySQL Default Character Set MySQL specifies the character set to be refined to a database, a table, a column, and the character set to use. However, traditional programs do not use such complex configurations when creating databases and data tables. They use the default configuration. So where does the default configuration come from?
(1) when MySQL is compiled, a default character set is specified, which is latin1; (2) when MySQL is installed, you can. ini) specifies a default character set. If not specified, this value inherits from the value specified during compilation. (3) When mysqld is started, you can specify a default character set in the command line parameter. If not specified, this value inherits from the configuration in the configuration file. In this case, character_set_server is set to the default character set. (4) when creating a new database, unless explicitly specified, the character set of this database is set to character_set_server by default; (5) When a database is selected, character_set_database is set to the default Character Set of this database; (6) When a table is created in this database, the default Character Set of the table is set to character_set_database, which is the default Character Set of this database; (7) When you set a column in the table, unless explicitly specified, this The default character set in the column is the default Character Set of the table. To sum up, if no modification is made, all the column spaces of all the tables in the database are stored in latin1, however, if we install MySQL, we usually choose multi-language support, that is, the installer will automatically set default_character_set to UTF-8 in the configuration file, which ensures that by default, all columns of all tables in all databases are stored in UTF-8. 2. check the default character set (by default, mysql uses latin1 (ISO_8859_1). Generally, you can run the following two commands to view the character set and sorting method of the system: mysql> show variables like 'character % '; + metrics + | Variable_name | Value | + metrics + | character_set_client | latin1 | character_set_connection | latin1 | character_set_database | latin1 | metrics | binary | latency | latin1 | | character_set_server | latin1 | character_set_system | utf8 | character_sets_dir | D: "mysql-5.0.37" share "charsets" | + -------------------------- + --------------------------------- + mysql> show variables like 'collation _ % '; + bytes + --------------- + | Variable_name | Value | + bytes + --------------- + | collation_connection | bytes | collation_database | bytes | collation_server | bytes | + bytes + --------------- + 3. modify the default character set (1) the simplest modification method is to modify mysql's my. the character set key value in the INI file, such as default-character-set = utf8 character_set_server = after utf8 is modified, restart the mysql service. service mysql restart uses mysql> show variables like 'character % '; view, the database encoding has been changed to utf8 + encoding + | Variable_name | Value | + encoding + | character_set_client | utf8 | character_set_connection | utf8 | character_set_database | utf8 | bytes | 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, is to use the mysql Command mysql> SET character_set_client = utf8;
Author jayghost