Modifying the character set of a MySQL database table

MySQL garbled the root of the problem is incorrect MySQL character set, this article summarizes the command to view the MySQL character set. This includes viewing the MySQL database server character set, viewing the MySQL database character set, and the character set of the data tables and fields, the character sets supported by the currently installed MySQL, and so on.

First, view the MySQL database 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 |
+-----------+--------+---------+------------+------+-----------------+------

Third, 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 | .......
+----------------------+-------------+-----------------+--------

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

Mysql> Show CharSet;
Mysql> show Char set;
+----------+-----------------------------+---------------------+--------+
| 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 |
+----------+-----------------------------+---------------------+--------+

View the MySQL character set command above for Windows & Linux.

Four. Modifying the character set of tables and fields
Modify Database
mysql> ALTER DATABASE name character set UTF8;
Modify Table
ALTER TABLE name convert to character set GBK;
modifying fields
ALTER TABLE name modify column ' field name ' varchar (character set GBK not null;
Add a table field
ALTER TABLE name add column ' field name ' varchar (character) set GBK;
Note: Field names are not quoted during command execution

--------------------------------------------------------------------------------------------------------------- ------------------------

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

2. Copy small.cnf, my-medium.cnf, MY-HUGE.CNF, my-innodb-heavy-4g.cnf one to/etc, named MY.CNF
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
[Email protected] ~]#/etc/rc.d/init.d/mysql restart
Shutting down MySQL [OK]
Starting MySQL. Determine
[Email protected] ~]# 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.

5. View Character Set settings

Mysql> Show variables like ' collation_% ';
+----------------------+-----------------+
| variable_name | Value |
+----------------------+-----------------+
| collation_connection | Utf8_general_ci |
| Collation_database | Utf8_general_ci |
| Collation_server | Utf8_general_ci |
+----------------------+-----------------+
3 Rows in Set (0.02 sec)

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 | /usr/share/mysql/charsets/|
+--------------------------+----------------------------+
8 rows in Set (0.02 sec)

Mysql>

Some other setup methods are: modifying the character set of a database
Mysql>use mydb Mysql>alter Database mydb character Set utf-8;

Create a database character set for a specified databaseMysql>create database mydb character Set utf-8;

Modify by 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

Re-starting MySQL:
[Email protected] ~]#/etc/rc.d/init.d/mysql restart

Modified via 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)

View:
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 | /usr/share/mysql/charsets/|
+--------------------------+----------------------------+
8 rows in Set (0.03 sec)

Mysql> Show variables like ' collation_% ';
+----------------------+-----------------+
| variable_name | Value |
+----------------------+-----------------+
| collation_connection | Utf8_general_ci |
| Collation_database | Utf8_general_ci |
| Collation_server | Utf8_general_ci |
+----------------------+-----------------+
3 rows in Set (0.04 sec)

Modifying the character set of a MySQL database table