Modification and viewing of Mysql Character Set

Mysql Character Set modification and viewing problem modification and viewing mysql database character set modification my. cnfvi/etc/my. cnf add default-character-set = utf8 under [client] and add default-character-set = utf8 5 under [mysqld. view character set settings mysql> show variables like 'collation _ % '; mysql> show variables like 'character _ set _ % '; 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 it through the configuration file: modify/var/lib/mysql/mydb/db. opt Default-character-set = latin1default-collation = latin1_swedish_ci is default-character-set = utf8default-collation = utf8_general_ci modified via MySQL command line: 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_connecti On = utf8; mysql> set collation_database = utf8; mysql> set collation_server = utf8; view: mysql> show variables like 'character _ set _ % '; mysql> show variables like 'collation _ % '; Generally, you can run the following two commands to view the character set and sorting method settings of the system: mysql> show variables like 'character % '; mysql> show variables like 'collation _ % '; 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 mys. Ql service, service mysql restart use mysql> show variables like 'character % '; view, it is found that the database encoding has been changed to utf8 (2) there is a way to modify the character set, mysql Command> SET character_set_client = utf8; mysql command line to modify character encoding 1. Modify database character encoding mysql> alter database mydb character set utf8; 2. When creating a database, specify the database character encoding mysql> create database mydb character set utf8; 3. view the mysql database character encoding mysql> show variables like 'character % '; // query the character encoding of all attributes of the current mysql database + ----------- --------------- + Percent + | Variable_name | Value | + percent + | percent | latin1 | character_set_connection | latin1 | character_set_database | utf8 | percent | binary | percent | latin1 | character_set_server | utf8 | character_set_system | utf8 | character_sets_dir |/usr/share/mys Ql/charsets/| + encoding + 4. Modify the character encoding of the mysql database mysql> set character_set_client = utf8; mysql> set character_set_connection = utf8; mysql> set character_set_database = 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> show variables like' Character % '; + 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_di R |/usr/share/mysql/charsets/| + ---------------------------- + -------------------------- + 8 rows in set (0.00 sec) mySQL uses Load Data local infile to import part of the Data, and Chinese characters are garbled. Today, Data is imported between two MySQL servers, because the other MySQL server is used for testing and the Data is less than one month old, select a file from an existing MySQL server. The specific statement is: select * from news where ine_time> = '2014-02-01 'and ine_time <'2014-03-01' into outfile'/tmp/newsdata. SQL; then scp to another MySQL Server to import to the corresponding table, the specific statement is as follows: load data local infile '/Home/lsanotes/newsdata. SQL 'into table news; then refresh the web page for accessing this database and find that the data just imported in this month is garbled, while the data in other months is normal, use show create table news; check and find that the news tables on both servers are utf8. It is strange that the exported data is converted to utf8, and the import problem persists. Later, when I checked the data of the just-imported month in the database, I did not execute set names utf8; then I could normally view Chinese characters without garbled characters, in other months, set names utf8 must be executed first to read Chinese characters without garbled characters. However, when set names utf8 is executed; the data imported in the previous month is garbled. It seems that the imported data is not in utf8 format. The final solution is: load data local infile '/home/lsanotes/newsdata. SQL' into table news character set utf8;