One, Windows
1 suspended MySQL service
2, under the MySQL installation directory to find my.ini If you do not put my-medium.ini replication for a my.ini can
Open the my.ini in the [client] and [mysqld] below plus default-character-set = utf8, save and close
4, start the MySQL service
Second, Linux
1, stop the MySQL service (bin / mysqladmin-u root shutdown)
2, my.cnf, if not the support-files directory under the MySQL installation directory put the my-medium.cnf under copied to / etc / under and renamed my.cnf can be found in / etc / under
Open the my.cnf [client] and [mysqld] below plus default-character-set = utf8, save and close
4, start the MySQL service (bin / mysqld_safe &)
If you are unfortunate enough to export the whole there are a lot of different encoded data table database, ok. The original variety of encoding replaced by the purpose of encoding you want to use the replace tool. I have here the original utf8 and latin1, so just need to Latin1 Replace All to utf8.
After the new database you want to import data:
view plaincopy to clipboardprint?
mysql> create database `TARGET_DB` default character set utf8 collate utf8_general_ci
mysql> create database `TARGET_DB` default character set utf8 collate utf8_general_ci
OK, now you can import the data sheet:
view plaincopy to clipboardprint?
mysql-uroot-p TARGET_DB – default-character-set = utf8 </ home / test / sends.sql
mysql-uroot-p TARGET_DB – default-character-set = utf8 </ home / test / sends.sql
Finally, with mysql the operation character set will be used when commands are collected from the Internet:
view plaincopy to clipboardprint?
Modify the MySQL database default character set (mysql database default character set)
alter database testdb default character set = gb2312;
Modify MySQL table default character set (mysql table default character set)
alter table ip_to_country default character set = gb2312;
Note: Modifying MySQL’s default character set, character data already stored at the database level, or data table level, no change. Just new table or column, start using the new character set.
Convert MySQL table character set (mysql table character set)
MySQL has a data table ip_to_country the previous character column of the table data are stored in utf8 encoding format, now want to convert GB2312 encoding format storage. Need to use the MySQL character set conversion command: convert to character set
alter table ip_to_country convert to character set gb2312;
Tip: convert to the data table ip_to_country the default character set will change from utf8 to gb2312.
Conversion MySQL table a column character set (mysql column character set)
alter table ip_to_country modify country_name varchar (50) character set gb2312;
This statement the a single out country_name the character set conversion for gb2312. This usage is relatively rare in practice, because in most cases, are changing the entire character set of the table.
From:
Modify the MySQL database default character set (mysql database default character set)
alter database testdb default character set = gb2312;
Modify MySQL table default character set (mysql table default character set)
alter table ip_to_country default character set = gb2312;
Note: Modifying MySQL’s default character set, character data already stored at the database level, or data table level, no change. Just new table or column, start using the new character set.
Convert MySQL table character set (mysql table character set)
MySQL has a data table ip_to_country the previous character column of the table data are stored in utf8 encoding format, now want to convert GB2312 encoding format storage. Need to use the MySQL character set conversion command: convert to character set
alter table ip_to_country convert to character set gb2312;
Tip: convert to the data table ip_to_country the default character set will change from utf8 to gb2312.
Conversion MySQL table a column character set (mysql column character set)
alter table ip_to_country modify country_name varchar (50) character set gb2312;
This statement the a single out country_name the character set conversion for gb2312. This usage is relatively rare in practice, because in most cases, are changing the entire character set of the table.
From:
view plaincopy to clipboardprint?
Create a database named test, and set the character set is UTF8:
create database `test` default character set utf8 collate utf8_general_ci
View ta similar test data table in the database:
show table status from ta like ‘% test%’;
Check the MySQL database server and database character set.
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 | ……
In | character_set_results | utf8 | ……
| The, character_set_server | utf8 | …… – server character set
At | character_set_system | utf8 | ……
| Character_sets_dir | D :/ MySQL Server 5.0/share/charsets / | ……
View the the MySQL data sheet (table) character set.
show table status from sqlstudy_db like ‘% countries%’;
+ ———– + ——– + ——— + ———— + —– – + —————– + ——
| Name | Engine | Version | ROW_FORMAT | Rows | Collation | ……
+ ———– + ——– + ——— + ———— + —– – + —————– + ——
| Countries | InnoDB | 10 | Compact | 11 | utf8_general_ci | ……
View the the MySQL data columns (column) character set.
show full columns from countries;
+ ———————- + ————- + ———— —– + ——–
| Field | Type | Collation | …….
+ ———————- + ————- + ———— —– + ——–
| Countries_id | int (11) | NULL | …….
| Countries_name | varchar (64) | utf8_general_ci | …….
The | countries_iso_code_2 | char (2) | utf8_general_ci | …….
The | countries_iso_code_3 | char (3) | utf8_general_ci | …….
| Address_format_id | int (11) | NULL | …….
View the current character set supported by the installation of MySQL.
Show charset; or
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 | U.S. 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 |
Modify the character set of tables and fields
/ / Modify the table
alter table table name convert to character set gbk;
/ / Modify the field
alter table table name modify Column ‘field name’ varchar (30) character set GBK NOT NULL;
Data references:
