Shulou(Shulou.com)06/01 Report--

MYSQL database character set includes character set (CHARACTER) and collation rules (COLLATION) two concepts.

The character sets and collation rules supported by MYSQL can be viewed with the command showcharacter set;.

Character Set Related Variables

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 |/application/mysql-5.5.32/share/charsets/ |

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

8 rows in set (0.00 sec)

These character sets have the following meanings:

character_set_client #client character set

character_set_connectio #link character set

character_set_database #Database character set, specified by profile or database table.

character_set_filesystem #file system character set

character_set_results #Returns the resulting character set

character_set_server #Server character set, configuration file specified or library table specified.

character_set_system #system character set

To solve the MYSQL garbled problem, you need to do 6 unified character sets

1. The unified client character set can be modified by the set names command, or the following variables can be modified separately

Method 1: set names gbk controls the client's character set

Method 2: Modify the following three parameters individually

set character_set_client gbk;

set character_set_connectio gbk;

set character_set_results gbk;

Method 3: You can also add the--default-character-set=gbk parameter when entering MYSQL.

mysql-uroot -p123456 -S /data/3306/mysql.sock --default-character-set=gbk;

Method 4: Permanently take effect You need to modify the my.cnf configuration file Add the following parameters to the [client] module

[client]

default-character-set=gbk If it is a multi-instance configuration, modify/etc/my.cnf

2. Unified MYSQL server-side character set

Method 1: Change the my.cnf parameter as follows

[mysqld]

default-character-set=latin1 for versions prior to 5.1

character-set-server=latin1 Fit 5.5

Corresponding to the following two parameters in the variable:

character_set_database

character_set_server

Method 2: Specify server-side character set at compile time:

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \

3. The character set of fields, tables and libraries should also be unified.

Specify character set to build library:

create database oldboy_utf8 DEFAULT CHARACTER SETUTF8 COLLATE utf8_general_ci;

Modify Database Character Set Command:

alter database oldboy character set utf8 collate utf8_general_ci;

Specify character set to create table:

CREATE TABLE `test` (

`id` int(4)NOT NULL AUTO_INCREMENT,

`name`char(20) NOT NULL,

PRIMARY KEY(`id`),

KEY`index_name` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

Modify Data Table Character Set command:

Change table default character set and all character columns (CHAR,VARCHAR,TEXT) to new character set

alter table test CONVERT TO character set utf8 collate utf8_general_ci;

Default character set for table revision only

alter table test DEFAULT character set utf8 collate utf8_general_ci;

Modify the character set of a field

alter table test CHANGE name name varchar(16) character set utf8 collate utf8_general_ci;

Related View Command

show create database oldboy;

show create table test;

show FULL COLUMNS from test;

4. The character set used by the program should be unified with the database table fields

5, LINUX system character set should also be unified

cat /etc/sysconfig/i18n

LANG="en_US.UTF-8"

SYSFONT="latarcyrheb-sun16"

6, LINUX client such as CRT XSHELL character set should also be unified can be set in the software properties.

If the database has random codes, you can check them one by one according to the above 6 points.

How do I choose the right character set?

1, if it is to deal with a variety of text, published to different language countries and regions, the UNICODE character set should be selected, for MYSQL is UTF-8 (three bytes per Chinese character), if the application needs to process English, only a small number of Chinese characters UTF-8 is better.

2. If you only need to support Chinese, and the data volume is very large, the performance requirements are also very high, GBK (fixed length each Chinese character occupies two bytes, English also occupies two bytes), if you need a large number of operations, comparison sorting, etc., fixed length character set, faster, higher performance.

3. To handle mobile Internet services, it may be necessary to use the utf8mb4 character set.

Recommendation: No special requirements, please select UTF8.

Many open source programs have multiple character set versions of the program.

How to execute sql statements in a database

1) Operating habits: try not to insert data into MYSQL command line (SSH client impact)

2) The format of sql file is unified with "utf8 unsigned".

3) How to import files

a. SQL file can be executed with source in MYSQL command line.

b. Command import data mysql -uroot-p123456oldboyalltable.sql

--no-create-info Do not export table structure

4) Modify my.cnf configuration to adjust client and server character sets, restart takes effect

5) Create a library from a new character set (optional)

Delete the original database and create database dbname default charset utf8;

6) Import table structure (table structure with changed character set)

mysql-uroot -p dbname

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.