MySQL Character Set Problems

Modify and view the character set of the MySQL database
Modify my. CNF
VI/etc/My. CNF
Add under [client]
Default-character-set = utf8
Add under [mysqld]
Default-character-set = utf8

5. View Character Set settings
Mysql> show variables like 'collation _ % ';
Mysql> show variables like 'character _ SET _ % ';

Modify the character set of a Database
Mysql> Use mydb
Mysql> alter database mydb Character Set UTF-8;
Creates a database and specifies the character set of the database.
Mysql> Create Database mydb Character Set UTF-8;

Modify the configuration file:
Modify/var/lib/MySQL/mydb/DB. Opt
Default-character-set = Latin1
Default-collation = latin1_swedish_ci
Is
Default-character-set = utf8
Default-collation = utf8_general_ci
Use the MySQL command line to modify:
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_connection = 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 of the system:
Mysql> show variables like 'character % ';

Mysql> show variables like 'collation _ % ';
MySQL command line character encoding Modification
1. Modify the character encoding of a Database

Mysql> alter database mydb Character Set utf8;

2. Specify the character encoding when creating a database

Mysql> Create Database mydb Character Set utf8;

3. view the character encoding of the MySQL database

Mysql> show variables like 'character % '; // query the character encoding of all attributes of the current MySQL database

+ -------------------------- + ---------------------------- +
| Variable_name | value |

MySQL uses load data local infile to import some data and garbled Chinese Characters
Import data between two MySQL servers today, because the other MySQL server is used for testing and the data is less than one month. Select data from an existing MySQL server to a file. 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;