View mysql Character Set and modify table structure

View mysql Character Set and modify table structure

(09:00:36)

The root cause of MySQL garbled characters is that the MySQL character set is improperly set. This article summarizes the commands for viewing the MySQL character set. Including viewing the character set of the MySQL database server, viewing the character set of the MySQL database, the character set of the data table and field, and the character set supported by the currently installed MySQL.

1. 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 \ | ......
+ -------------------------- + ------------------------------------- + ------

2. view the character set of the MySQL DATA 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.

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 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 |
+ ---------- + ----------------------------- + --------------------- + -------- +

The preceding MySQL character set command is applicable to Windows and Linux.

4. Modify the character set of tables and fields
// Modify the database
Mysql> alter database name character set utf8;
// Modify the table
Alter table name convert to character set gbk;
// Modify the field
Alter table name modify column 'field name' varchar (30) character set gbknot null;
// Add Table Fields
Alter table name add column 'field name' varchar (20) character setgbk;
Note: Field names are not enclosed in quotation marks during command execution.