Mysql character set modification and viewing _ MySQL

Mysql character set modification and viewing problems bitsCN.com

Modification and viewing of Mysql character set

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 _ % ';

3. modify the default character set

(1) the simplest modification method is to modify the character set key value in mysql's my. ini file,

For example, default-character-set = utf8

Character_set_server = utf8

After modification, restart the mysql service, service mysql restart

Use mysql> show variables like 'character % '; check that the database encoding has been changed to utf8

(2) another way to modify the character set is to use mysql commands.

Mysql> SET character_set_client = utf8;

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 |

+ -------------------------- + ---------------------------- +

| Character_set_client | latin1 |

| Character_set_connection | latin1 |

| Character_set_database | utf8 |

| Character_set_filesystem | binary |

| Character_set_results | latin1 |

| Character_set_server | utf8 |

| Character_set_system | utf8 |

| Character_sets_dir |/usr/share/mysql/charsets/|

+ -------------------------- + ---------------------------- +

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 % ';

+ -------------------------- + ---------------------------- +

| 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.00 sec)

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;

BitsCN.com