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

This article mainly introduces the character set handouts of MySQL database, hoping to supplement and update some knowledge for you. If you have any other questions you need to know, you can continue to follow my updated articles in industry information.

1. View MySQL database, cloud server and database character set

Mysql > show variables like'% char%'

+-

| Variable_name | Value |.

+-

| character_set_client | utf8 |. -- client character set

| character_set_connection | utf8 |.

| character_set_database | utf8 |. -- Database character set

| character_set_filesystem | binary |.

| character_set_results | utf8 |.

| character_set_server | utf8 |. -- Server character set

| character_set_system | utf8 |.

| character_sets_dir | D:\ MySQL Server 5.0\ share\ charsets\ |.

+-

Second, view the character set of the MySQL data Table (table)

Mysql > show table status from sqlstudy_db like'% countries%'

+-

| Name | Engine | Version | Row_format | Rows | Collation |.

+-

| countries | InnoDB | 10 | Compact | 11 | utf8_general_ci |.

+-

3. View the character set of the MySQL data column (column)

Mysql > show full columns from countries

+-

| Field | Type | Collation |.

+-

| countries_id | int (11) | NULL |.

| countries_name | varchar (64) | utf8_general_ci |.

| countries_iso_code_2 | char (2) | utf8_general_ci |.

| countries_iso_code_3 | char (3) | utf8_general_ci |.

| address_format_id | int (11) | NULL |.

+-

4. View the character sets supported by the currently installed MySQL

Mysql > show charset

+-- +

| | Charset | Description | Default collation | Maxlen | |

+-- +

| | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |

| | dec8 | DEC West European | dec8_swedish_ci | 1 |

| | cp850 | DOS West European | cp850_general_ci | 1 |

| | hp8 | HP West European | hp8_english_ci | 1 |

| | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |

| | latin1 | cp1252 West European | latin1_swedish_ci | 1 |

| | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |

| | swe7 | 7bit Swedish | swe7_swedish_ci | 1 |

| | ascii | US ASCII | ascii_general_ci | 1 |

| | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |

| | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |

| | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |

| | tis620 | TIS620 Thai | tis620_thai_ci | 1 |

| | euckr | EUC-KR Korean | euckr_korean_ci | 2 |

| | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |

| | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |

| | greek | ISO 8859-7 Greek | greek_general_ci | 1 |

| | cp1250 | Windows Central European | cp1250_general_ci | 1 |

| | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |

| | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |

| | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |

| | utf8 | UTF-8 Unicode | utf8_general_ci | 3 |

| | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |

| | cp866 | DOS Russian | cp866_general_ci | 1 |

| | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |

| | macce | Mac Central European | macce_general_ci | 1 |

| | macroman | Mac West European | macroman_general_ci | 1 |

| | cp852 | DOS Central European | cp852_general_ci | 1 |

| | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |

| | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |

| | cp1256 | Windows Arabic | cp1256_general_ci | 1 |

| | cp1257 | Windows Baltic | cp1257_general_ci | 1 |

| | binary | Binary pseudo charset | binary | 1 |

| | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |

| | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |

| | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |

+-- +

The above command to view the MySQL character set applies to Windows & Linux

Modify the MySQL character set under Liunx:

1. Find the location of the cnf file for MySQL

Find /-iname'* .cnf'- print

/ 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

/ 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

two。 Copy small.cnf, my-medium.cnf, my-huge.cnf

My-innodb-heavy-4G.cnf, one of which is named my.cnf under / etc

Cp / usr/share/mysql/my-medium.cnf / etc/my.cnf

3. Modify my.cnf

Vi / etc/my.cnf

Add under [client]

Default-character-set=utf8

Add under [mysqld]

Default-character-set=utf8

4. Restart MySQL

[root@bogon ~] # / etc/rc.d/init.d/mysql restart

Shutting down MySQL [OK]

Starting MySQL. [OK]

[root@bogon] # mysql-u root-p

Enter 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.

Some other setting methods:

Modify the character set of the database

Mysql > use mydb

Mysql > alter database mydb character set utf-8

Create the character set of the specified database

Mysql > create database mydb character set utf-8

Modify through the configuration file:

Modify / var/lib/mysql/mydb/db.opt

Default-character-set=latin1

Default-collation=latin1_swedish_ci

For

Default-character-set=utf8

Default-collation=utf8_general_ci

Restart MySQL:

[root@bogon ~] # / etc/rc.d/init.d/mysql restart

Modify through the MySQL command line:

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 affected (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)

Check whether the character set of the fields in the database table is utf8, if not, also set the fields to utf8 format!

Read the above handouts on the character set of MySQL database, hoping to bring some help to you in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.